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.ex…

Accepted Answer

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

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

Solution

Problem