是否可以将null参数传递给Java JPA 2.1中的存储过程？ [英] Is it possible to pass a null parameter to a stored procedure in Java JPA 2.1?

问题描述

使用新的JPA 2.1 存储过程调用，有没有办法传递空参数？

Using the new JPA 2.1 stored procedure call, is there any way to pass a null parameter?

以下是一个示例用法：

StoredProcedureQuery storedProcedure = em.createStoredProcedureQuery("get_item", Item.class);
storedProcedure.registerStoredProcedureParameter(0, String.class, ParameterMode.IN);
storedProcedure.registerStoredProcedureParameter(1, String.class, ParameterMode.IN);
storedProcedure.registerStoredProcedureParameter(2, Timestamp.class, ParameterMode.IN);

storedProcedure.setParameter(0, a);
storedProcedure.setParameter(1, b);
storedProcedure.setParameter(2, c);

storedProcedure.execute();

当给出所有参数时，这是有效的，但是当 c 是 null 它会因（PostgreSQL）JDBC驱动程序出错而失败。

This works when all parameters are given, but when c is null it will fail with an error from the (PostgreSQL) JDBC driver.

Caused by: org.postgresql.util.PSQLException: No value specified for parameter 2
at org.postgresql.core.v3.SimpleParameterList.checkAllParametersSet(SimpleParameterList.java:216) [postgresql-9.3-1101.jdbc41.jar:]
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:244) [postgresql-9.3-1101.jdbc41.jar:]
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:559) [postgresql-9.3-1101.jdbc41.jar:]
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:417) [postgresql-9.3-1101.jdbc41.jar:]
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:410) [postgresql-9.3-1101.jdbc41.jar:]
    at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.execute(WrappedPreparedStatement.java:404)
    at org.hibernate.result.internal.OutputsImpl.<init>(OutputsImpl.java:69) [hibernate-core-4.3.1.Final.jar:4.3.1.Final]
    ... 244 more

我也考虑过使用自己的用于传递参数的类，例如：

I have also considered using my own class for passing the parameters, eg:

storedProcedure.registerStoredProcedureParameter(0, InputParameters.class, ParameterMode.IN);
storedProcedure.setParameter(0, inputParameters);

此操作失败：

Caused by: java.lang.IllegalArgumentException: Type cannot be null

I猜测因为它需要一个可以映射到SQL类型的类型。

I guess because it needs a type that can be mapped to an SQL type.

有没有办法传递空参数？

Is there a way to pass a null parameter?

推荐答案

是的，当使用JPA
StoredProcedureQuery时，可以将null参数传递给存储过程。

Yes, it is possible to pass null params to stored procedures when using JPA StoredProcedureQuery.

您必须在application.properties文件中添加以下属性，并使用其名称注册参数。

You have to add the following property in application.properties file and register the parameters with their name.

spring.jpa.properties.hibernate.proc.param_null_passing = true

示例：

StoredProcedureQuery q = em.createStoredProcedureQuery(Globals.SPROC_PROBLEM_COMMENT2, ProblemCommentVO.class);
q.registerStoredProcedureParameter("Patient_ID", Long.class, ParameterMode.IN);
q.registerStoredProcedureParameter("Param2", Long.class, ParameterMode.IN);
q.registerStoredProcedureParameter("Param3", Long.class, ParameterMode.IN);
q.registerStoredProcedureParameter("Param4", Integer.class, ParameterMode.OUT);
q.setParameter("Patient_ID", patientId);
q.setParameter("Param2", null);//passing null value to Param2
q.setParameter("Param3", null);

List<ProblemCommentVO> pComments = q.getResultList();
Integer a = (Integer) q.getOutputParameterValue("Param4");

这篇关于是否可以将null参数传递给Java JPA 2.1中的存储过程？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！