JPA 2.1 - @NamedStoredProcedureQuery - ParameterMode.IN - null value gives error

Question

I am trying to call a stored procedure using JPA 2.1 @NamedStoredProcedureQuery. I have the below annotations in my Entity class.

The problem is I am not able to pass null values if I declare the mode as ParameterMode.IN. And able to pass null value only if I declare it as ParameterMode.INOUT.

Entity

@NamedStoredProcedureQuery(name = "employee", procedureName = "myProcedure", resultClasses = Employee.class,  parameters = {
        @StoredProcedureParameter(mode = ParameterMode.IN, name = "EMPLOYEE_PHONE", type = String.class),
        @StoredProcedureParameter(mode = ParameterMode.INOUT, name = "CITY", type = String.class)

Repository

        public Long createOrUpdateEmployee(Employee employee) throws EbaDataException {

            StoredProcedureQuery query = entityManager.createNamedStoredProcedureQuery("employee");

            query.setParameter("EMPLOYEE_PHONE", employee.getName());
            query.setParameter("CITY", employee.getCity());         
            query.execute();

        }

For example if I pass null with ParameterMode.IN for index 2 (CITY), then I get this exception.

Caused by: java.sql.SQLException: Missing IN or OUT parameter at index:: 2

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

According to this topic you cannot pass null value as prameter.

I am interpreting that as JPA (specifically Hibernate) DOES NOT support setting null parameters at all. It looks like they are in a struggle with supporting default parameter values versus substituting a null value when appropriate. They choose to support the former. It looks like those who need support for the latter (nullable values) must use java.sql.CallableStatement

You can always as well try to put empty String instead of null and I think it should work.

JPA 2.1 - @NamedStoredProcedureQuery - ParameterMode.IN - null value gives error

1 Answers1