Java JPA 本机查询设置空参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21731343/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
JPA Native Query set null parameter
提问by Alex Kartishev
Here is my code part:
这是我的代码部分：
Query q = em.createNativeQuery("insert into table_name (value_one, value_two, value_three) values (?,?,?)");
q.setParameter(1, value1);
q.setParameter(2, value2);
q.setParameter(3, value3);
q.executeUpdate();
value3sometimes can be null (Date class object). And if it is null the following exception is thrown:
value3有时可以为空（Date 类对象）。如果它为空，则抛出以下异常：
Caused by: org.postgresql.util.PSQLException: ERROR: column "value_three" is of type timestamp without time zone but expression is of type bytea
Hint: You will need to rewrite or cast the expression.
Position: 88
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2102)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1835)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:500)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:388)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:334)
at org.hibernate.engine.query.NativeSQLQueryPlan.performExecuteUpdate(NativeSQLQueryPlan.java:189)
... 11 more
How is it possible to get this code working and to persist null value into database?
如何让这段代码工作并将空值持久化到数据库中？
采纳答案by Emmanuel Touzery
You are using postgresql (already the stack is telling that), and likely Hibernate, and almost certainly hitting this problem: PostgreSQL JDBC Null String taken as a bytea
您正在使用 postgresql（堆栈已经说明了这一点），并且可能是 Hibernate，并且几乎肯定会遇到这个问题：PostgreSQL JDBC Null String take as a bytea
I used this particular solution: https://stackoverflow.com/a/23501509/516188
我使用了这个特殊的解决方案：https: //stackoverflow.com/a/23501509/516188
So that means escaping to the Hibernate API so you can give the type of the expression.
所以这意味着转义到 Hibernate API，以便您可以提供表达式的类型。
In my case it was a nullable Short so I used:
在我的情况下，它是一个可为空的 Short 所以我使用了：
.setParameter("short", shortValue, ShortType.INSTANCE);
.setParameter("short", shortValue, ShortType.INSTANCE);
shortValue being of type Short.
shortValue 类型为Short。
回答by Matteo
if you are not using Hibernate, but you are using EclipseLink you can use the query Hint: https://wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/Query_Hints
如果您不使用 Hibernate，但使用 EclipseLink，则可以使用查询提示：https: //wiki.eclipse.org/EclipseLink/UserGuide/JPA/Basic_JPA_Development/Querying/Query_Hints
Example query:
示例查询：
String insert = "INSERT INTO mytable VALUES ( ?, ?, ?, ?)";
em = getEntityManager();
em.getTransaction().begin();
Query u = em.createNativeQuery(insert);
u.setHint(QueryHints.BIND_PARAMETERS, HintValues.FALSE);//<--the hint
u.setParameter(1, "value1");
u.setParameter(2, "value2");
u.setParameter(4, "value4");//just skipped the null element
u.executeUpdate();
em.getTransaction().commit();
and the result will be an insertion:
结果将是一个插入：
mytable
column1 column2 column3 column4
value1 value2 value4
of course if "column3" is nullable in the db...
当然，如果“column3”在数据库中可以为空...
I don't know if works also with Hibernate, but it could. I used PostgreSQL for my test.
我不知道是否也适用于 Hibernate，但可以。我使用 PostgreSQL 进行测试。
回答by J. Doe
I have faced the same issue when use EntityManager.createNamedQuery(guess the same issue with createNativeQuery).
我在使用时遇到了同样的问题EntityManager.createNamedQuery（猜测 createNativeQuery 也有同样的问题）。
In case you are going to pass nullableparameter to Query then use TypedParameterValue which allows to pass the type.
如果您要将nullable参数传递给 Query，请使用允许传递类型的 TypedParameterValue。
For instance:
例如：
setParameter("paramName", new TypedParameterValue(StandardBasicTypes.LONG, paramValue));
Here you explicitly set the type of passed value and when you pass null value as processor know the exact type.
在这里，您明确设置了传递值的类型，并且当您传递空值时，处理器知道确切的类型。
回答by Luigi Masiero
In my case, using Oracle 12 and jboss 7.3, I solved using an empty String as param value. I don't understand why, but it works. This is my code:
就我而言，使用 Oracle 12 和 jboss 7.3，我解决了使用空字符串作为参数值的问题。我不明白为什么，但它有效。这是我的代码：
String sql = "insert into customer (id,name,age) values (?1,?2,?3);
em = getEntityManager();
Query query = em.createNativeQuery(sql);
query.setParameter(1, getId());
query.setParameter(2, getName());
if (getAge() != null) {//getAge() return BigDecimal and map a NUMBER column type
query.setParameter(3, getAge());
} else {
query.setParameter(3, "");
}
