How to resolve this error ? You need to call ParameterRegistration#enablePassingNulls(true) in order to pass null parameters [duplicate]

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

Answer 2

Here are my findings for Hibernate 4.3 which are relevant to JPA 2.1.

This will throw an exception if the DB does not support default parameters:

ProcedureCall procedure = getSession().createStoredProcedureCall("my_procedure");

procedure.registerParameter("my_nullable_param", String.class, ParameterMode.IN)
         .bindValue(null);

// execute
procedure.getOutputs();

From Hibernate's source for binding the parameter to the underlying CallableStatement:

public abstract class AbstractParameterRegistrationImpl {

  ..

  @Override
  public void prepare(CallableStatement statement, int startIndex) throws SQLException {

    if ( mode == ParameterMode.INOUT || mode == ParameterMode.IN ) {
      if ( bind == null || bind.getValue() == null ) {
        // the user did not bind a value to the parameter being processed.  That might be ok *if* the
        // procedure as defined in the database defines a default value for that parameter.
        // Unfortunately there is not a way to reliably know through JDBC metadata whether a procedure
        // parameter defines a default value.  So we simply allow the procedure execution to happen
        // assuming that the database will complain appropriately if not setting the given parameter
        // bind value is an error.
        log.debugf("Stored procedure [%s] IN/INOUT parameter [%s] not bound; assuming procedure defines default value", procedureCall.getProcedureName(), this);
      } else {
         typeToUse.nullSafeSet( statement, bind.getValue(), startIndex, session() );
      }
    }
  }

  ..
}

The above comment reads:

The user did not bind a value to the parameter being processed. That might be ok if the procedure as defined in the database defines a default value for that parameter. Unfortunately there is not a way to reliably know through JDBC metadata whether a procedure parameter defines a default value. So we simply allow the procedure execution to happen assuming that the database will complain appropriately if not setting the given parameter bind value is an error.

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:

getSession().doWork(new Work() {

  @Override
  public void execute(Connection conn) throws SQLException {

    CallableStatement stmt = conn.prepareCall("{ call my_prodecure(:my_nullable_param) }");

    if(stringVariableThatIsNull != null) {
       stmt.setString("my_nullable_param", stringVariableThatIsNull);
    } else {
       stmt.setNull("my_nullable_param", Types.VARCHAR);
    }

    stmt.execute();
    stmt.close();

  }    
});

tl;dr we are still forced to deal with low-level JDBC because neither JPA or Hibernate seem to address nullable parameters. They are supporting procedure parameter default values over substituting a null value.

Answer 3

Set property hibernate.proc.param_null_passing=true

example:

 <bean id="entityManagerFactory"
    class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
    <property name="packagesToScan">
        <array>
            <value>my.entity.store.package</value>
        </array>
    </property>
    <property name="persistenceUnitName" value="mainPersistenceUnit" />
    <property name="dataSource" ref="dataSource" />
    <property name="jpaVendorAdapter" ref="jpaVendorAdapter" />
    <property name="jpaDialect" ref="jpaDialect" />

    <property name="jpaPropertyMap">
        <map>
            <entry key="hibernate.hbm2ddl.auto" value="validate" />
            <entry key="hibernate.show_sql" value="true" />
            <entry key="hibernate.format_sql" value="true" />
            <entry key="hibernate.proc.param_null_passing" value="true" />
        </map>
    </property>
</bean>

Answer 4

This below step resolves the issue :

set globally a parameter in properties file in springboot hibernate environment as spring.jpa.properties.hibernate.proc.param_null_passing=true

for allowing stored procedure to pass null in hibernate java code.

Answer 5

You can create a method to positively enable all the calling parameters as the code below, it also works fine in case your StoredProcedureQuery contains out parameters. This helps you don't break the null parameter checking somewhere.

public void setStoreProcedureEnableNullParameters(StoredProcedureQuery storedProcedureQuery) {
    if (storedProcedureQuery == null || storedProcedureQuery.getParameters() == null)
        return;

    for (Parameter parameter : storedProcedureQuery.getParameters()) {
        ((ProcedureParameterImpl) parameter).enablePassingNulls(true);
    }
}

Then call

StoredProcedureQuery storedProcedure = entityManager.createStoredProcedureQuery("your_store_procedure")
    .registerStoredProcedureParameter("Param_1", String.class, ParameterMode.OUT)
    .registerStoredProcedureParameter("Param_2", String.class, ParameterMode.IN)
    .registerStoredProcedureParameter("Param_3", String.class, ParameterMode.IN);

// Remember you must call before setting the value for the parameters
setStoreProcedureEnableNullParameters(storedProcedure);

storedProcedure
    .setParameter("Param_2", null)
    .setParameter("Param_3", "Some value");
storedProcedure.execute();

String outValue = (String) storedProcedure.getOutputParameterValue("Param_1");

Answer 6

I ran into this same issue. I didn't have control over the stored procedure, so I couldn't modify it to accept default values.

However, because the database I was using was an Oracle database, I was able to work around this issue by changing the datatype of my stored procedure parameter (in orm.xml) to java.lang.String and then substituting an empty String ("") where it would have been appropriate to use a NULL value. Since Oracle treats empty Strings ("") and NULLs identically, I was able to effectively trick Hibernate into passing a "null" value to the Oracle stored procedure.

If you don't want to have to resort to writing low-level JDBC code, your stored procedure is not modifiable, and your database is Oracle-based, try this approach. Just be sure that all of your entity manager code treats the parameter as though it were the intended data type (java.math.BigDecimal in my case), and wait until you're setting the parameter value to convert to java.lang.String.

For example:

orm.xml:

<named-stored-procedure-query name="storedProcName" procedure-name="STORED_PROC_PACKAGE.STORED_PROC_NAME">
    <!-- String so that empty string can be used for NULL value by Hibernate JPA. -->
    <parameter name="my_nullable_in_param" mode="IN" class="java.lang.String" />
    
    <!-- was:
    <parameter name="my_nullable_in_param" mode="IN" class="java.math.BigDecimal" />
    -->
    
    <!-- other IN and OUT params -->
</named-stored-procedure-query>

Java:

public void callStoredProc(java.math.BigDecimal myNullableInParam) {
    EntityManager entityManager = EMF.createEntityManager();
    
    StoredProcedureQuery query = entityManager.createNamedStoredProcedureQuery("storedProcName");
    
    query.setParameter("my_nullable_in_param", (myNullableInParam == null ? "" : myNullableInParam.toString()));
    
    // set more parameters, execute query, commit transaction, etc.
}

Answer 7

To go around the issue, if we dont have many possible null parameters to the sproc we can have multiple @NamedStoredProcedureQuery mapping to same sproc in DB. We can then prepare appropriate query.

For example,

  @NamedStoredProcedureQuery(name = "MyEntity.GetWithoutNullParam", procedureName = "dbo.GetProc", parameters = {
            @StoredProcedureParameter(mode = ParameterMode.IN, name = "id", type = Integer.class)},
            resultClasses = MyEntity.class),
    @NamedStoredProcedureQuery(name = "MyEntity.GetWithNullParam", procedureName = "dbo.GetProc", parameters = {
            @StoredProcedureParameter(mode = ParameterMode.IN, name = "id", type = Integer.class),
            @StoredProcedureParameter(mode = ParameterMode.IN, name = "nullableparam", type = String.class), 
resultClasses = MyEntity.class)

Now doing a null check on "nullableparam" we can create appropriate named proc in repository. Something similar to

if(MyEntity.nullableparam == null){StoredProcedureQuery storedProcedureQuery = em.createNamedStoredProcedureQuery("MyEntity.GetWithoutNullParam");}
else{StoredProcedureQuery storedProcedureQuery = em.createNamedStoredProcedureQuery("MyEntity.GetWithNullParam");}

Answer 8

this is my mehtod to execute queries, with a "workaround" for setNull(i, Type.NULL) in Postgresql.

The solution is to catch the specific exception and iterate through all possible types of "java.sql.Types" until you find some type that it doesn't throw exception

private Connection rawConnection;
public ResultSet executeQuery(String sql, ArrayList<Object> params) throws SQLException
{
    PreparedStatement stmt = null;
    Iterator<Object> it;
    Object itemParam;
    ResultSet rs = null;
    int i = 1;

    Log.core.debug("Execute query sql:\n" + sql);

    stmt = rawConnection.prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

    if ( params != null && params.size() > 0 )
    {
        it = params.iterator();

        while ( it.hasNext() )
        {
            itemParam = it.next();

            if      ( itemParam == null )                   { stmt.setNull(i, Types.NULL); }
            else if ( itemParam instanceof String )         { stmt.setString(i, (String)itemParam); }
            else if ( itemParam instanceof Boolean )        { stmt.setBoolean(i, (Boolean)itemParam); }
            else if ( itemParam instanceof Integer )        { stmt.setInt(i, (Integer)itemParam); }
            else if ( itemParam instanceof Long )           { stmt.setLong(i, (Long)itemParam); }
            else if ( itemParam instanceof Float )          { stmt.setFloat(i, (Float)itemParam); }
            else if ( itemParam instanceof Double )         { stmt.setDouble(i, (Double)itemParam); }
            else if ( itemParam instanceof BigDecimal )     { stmt.setBigDecimal(i, (BigDecimal)itemParam); }

            else if ( itemParam instanceof Object[] ) // for postgresql, adapt for other dbs ??
            {
                Class<?> type = itemParam.getClass().getComponentType();

                if      ( type.equals(String.class) )       { stmt.setArray(i, rawConnection.createArrayOf("varchar", (Object[]) itemParam)); }
                else if ( type.equals(Boolean.class) )      { stmt.setArray(i, rawConnection.createArrayOf("bool", (Object[]) itemParam)); }
                else if ( type.equals(Integer.class) )      { stmt.setArray(i, rawConnection.createArrayOf("int4", (Object[]) itemParam)); }
                else if ( type.equals(Long.class) )         { stmt.setArray(i, rawConnection.createArrayOf("int8", (Object[]) itemParam)); }
                else if ( type.equals(Float.class) )        { stmt.setArray(i, rawConnection.createArrayOf("float4", (Object[]) itemParam)); }
                else if ( type.equals(Double.class) )       { stmt.setArray(i, rawConnection.createArrayOf("float8", (Object[]) itemParam)); }
                else if ( type.equals(BigDecimal.class) )   { stmt.setArray(i, rawConnection.createArrayOf("numeric", (Object[]) itemParam)); }
            }

            i++;
        }
    }

    infinite_loop:
    while ( true ) // fix for postgresql --> stmt.setNull(i, Types.NULL); // it's required set DataType in NULLs
    {
        try
        {
            rs =  stmt.executeQuery();
            break infinite_loop;
        }
        catch (SQLException e)
        {
            // fix for postgresql --> stmt.setNull(i, Types.NULL); // it's required set DataType in NULLs
            if ( IS_POSTGRESQL ) // adapt for other dbs ??
            {
                String regexParNumber = "\\$([0-9]+)", sqlState = "42P18";
                PSQLException pe = ((PSQLException)e), pe1;
                Pattern r, r1;
                Matcher m, m1;
                Field[] types;
                int paramNumber;

                if ( pe.getErrorCode() == 0 && sqlState.equals(pe.getSQLState()) )
                {
                    r = Pattern.compile(regexParNumber);
                    m = r.matcher(pe.getMessage());

                    if ( m.find() )
                    {
                        paramNumber = Integer.parseInt(m.group(1));
                        types = Types.class.getDeclaredFields();

                        Log.core.trace("Fix type for null sql argument[" + paramNumber + "] ...");

                        for ( Field type : types )
                        {
                            if ( !"NULL".equals(type.getName()) )
                            {
                                Log.core.trace("Fix type for null sql argument[" + paramNumber + "], trying type '" + type.getName() + "' ...");

                                try { stmt.setNull(paramNumber, type.getInt(null)); }
                                catch (IllegalArgumentException | IllegalAccessException e1) { continue; }

                                try
                                {
                                    rs =  stmt.executeQuery();
                                    break infinite_loop;
                                }
                                catch (SQLException e1)
                                {
                                    pe1 = ((PSQLException)e1);

                                    if ( pe1.getErrorCode() == 0 && sqlState.equals(pe1.getSQLState()) )
                                    {
                                        r1 = Pattern.compile(regexParNumber);
                                        m1 = r1.matcher(pe1.getMessage());

                                        if ( m1.find() )
                                        {
                                            if ( paramNumber == Integer.parseInt(m1.group(1)) ) { continue; }
                                            else { continue infinite_loop; }
                                        }                                       
                                    }

                                    throw e1;
                                }
                            }
                        }
                    }
                }
            }

            throw e;
        }
        finally
        {   
            SQLWarning warns;
            Iterator<Throwable> itWarn;
            Throwable raise;

            try
            {
                warns = stmt.getWarnings();

                if ( warns != null )
                {
                    itWarn = warns.iterator();

                    while ( itWarn.hasNext() )
                    {
                        raise = itWarn.next();
                        Log.core.debug("<DbMain Log> --> " + raise.getMessage());
                    }
                }
            }
            catch (SQLException e1) {}
        }
    }

    return rs;
}

Answer 9

This worked for me

if (columnname.length() > 0) {
    fSignUp.setString(3, columnname); 
} else {
    fSignUp.setNull(<position>, Types.NULL);
}

Answer 10

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

There are few ways to achieve this. Mainly the solution depends on the hibernate version you are using.

Hibernate 4.3+

Session session = em.unwrap(Session.class);
ProcedureCall procedure = session.createStoredProcedureCall(Constants.MY_PROCEDURE_NAME);

q.registerStoredProcedureParameter("Patient_ID", Long.class, ParameterMode.IN).enablePassingNulls(true);
q.registerStoredProcedureParameter("Param2", String.class, ParameterMode.IN).enablePassingNulls(true);
q.registerStoredProcedureParameter("Param3", Long.class, ParameterMode.IN).enablePassingNulls(true);
q.registerStoredProcedureParameter("Param4", Integer.class, ParameterMode.OUT);

q.setParameter("Param1", patientId);
q.setParameter("Param2", null);//passing null value to Param2
q.setParameter("Param3", null);//passing null value to Param3

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

Older Versions

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(Constants.MY_PROCEDURE_NAME, ResultData.class);
q.registerStoredProcedureParameter("Patient_ID", Long.class, ParameterMode.IN);
q.registerStoredProcedureParameter("Param2", String.class, ParameterMode.IN);
q.registerStoredProcedureParameter("Param3", Long.class, ParameterMode.IN);
q.registerStoredProcedureParameter("Param4", Integer.class, ParameterMode.OUT);
q.setParameter("Param1", patientId);
q.setParameter("Param2", null);//passing null value to Param2
q.setParameter("Param3", null);//passing null value to Param3

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

Answer 11

spring.jpa.properties.hibernate.proc.param_null_passing=true does not work when you're defining the stored procedure query using @NamedStoredProcedureQuery. In this case the only way to input a null value is to define the optional parameter as INOUT instead of IN. Example:

@NamedStoredProcedureQueries({
        @NamedStoredProcedureQuery(name = "nameOfStoredProcedureCallingJpaMethod",
            procedureName = "nameOfStoredProcedure",
            parameters = {
                        @StoredProcedureParameter(name = "someParameter", type = String.class, mode = ParameterMode.IN),
                        @StoredProcedureParameter(name = "myOptionalParameter", type = String.class, mode = ParameterMode.INOUT)
                })
)