[FIXED] Hibernate native query optional parameter throws 'operator does not exist: bigint = bytea'

Issue

I have a query as follows:

SELECT id FROM table1 WHERE (:param IS NULL OR id_or_smth = :param)

The param parameter is optional, therefore it can be null

1. I createed a javax.persistance.Query
2. To which I then setParameter("param", null)
3. And when I called getResultList() I got the following error:

Caused by: org.hibernate.exception.SQLGrammarException: ERROR: operator does not exist: bigint = bytea

how can i handle this?

Solution

HQL and Criteria can only work when you specify an actual Entity property/Table column, so this doesn't work:

:param IS NULL

If id_or_smth is a Table1 column, then this is how your query should look like:

Query q = entityManager.createNativeQuery("SELECT id FROM table1 WHERE id_or_smth IS NULL or id_or_smth = :param");
q.setParameter("param", paramValye);
q.getResultList();

And paramValue must not be null.

In SQL, you must always use IS NULL and IS NOT NULL because a query like this:

SELECT id FROM table1 WHERE id_or_smth = NULL

will always return an empty result, even if there are rows satisfying id_or_smth IS NULL

Answered By - Vlad Mihalcea
Answer Checked By - David Marino (JavaFixing Volunteer)