Hibernate本机查询可选参数抛出“运算符不存在:bigint = bytea" [英] 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)

param 参数是可选的，因此它可以为 null

The param parameter is optional, therefore it can be null

1. 我创建了一个 javax.persistance.Query
2. 然后我要 setParameter("param"，null)
3. 当我调用 getResultList()时，出现以下错误:

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:

由以下原因引起:org.hibernate.exception.SQLGrammarException:错误:运算符不存在:bigint = bytea

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

我该如何处理?

推荐答案

HQL和条件"仅在您指定实际的Entity属性/表列时才起作用，因此不起作用:

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

:param IS NULL

如果id_or_smth是Table1列，那么查询应该是这样的:

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();

并且paramValue不能为null.

And paramValue must not be null.

在SQL中，您必须始终使用IS NULL/IS NOT NULL，因为这样的查询:

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

SELECT id FROM table1 WHERE id_or_smth = NULL

即使存在满足id_or_smth IS NULL的行，也将始终返回空结果

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

这篇关于Hibernate本机查询可选参数抛出“运算符不存在:bigint = bytea"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！