Is it possible to pass a null parameter to a stored procedure in Java JPA 2.1?

Question

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

Here is an example usage:

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

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

This fails with:

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

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

Is there a way to pass a null parameter?

Answer 1

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

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

Example:

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