CODEWITHSUNDEEP
javasqlhibernatejpaorm
Duracel
Duracel

Reputation: 134

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

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?

Upvotes: 4

Views: 3313

Answers (2)

Vlad Mihalcea
Vlad Mihalcea

Reputation: 153970

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

Upvotes: 1

isnot2bad
isnot2bad

Reputation: 24454

Maybe you could use criteria queries instead:

Criteria crit = sess.createCriteria(Table1.class);
crit.setProjection(Restrictions.id());
if (param != null) crit.add(Restrictions.eq("id_or_smth", param));

List result = crit.list();

Upvotes: 0

Related Questions