是否可以将空参数传递给 Java JPA 2.1 中的存储过程？

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

提问by Pool

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

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

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 cis nullit will fail with an error from the (PostgreSQL) JDBC driver.

此工程时，所有的参数，但是，当c是null它会失败，并从（PostgreSQL的）JDBC驱动程序错误。

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.

我猜是因为它需要一种可以映射到 SQL 类型的类型。

Is there a way to pass a null parameter?

有没有办法传递空参数？

采纳答案by Shaan

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

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

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

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

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

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

回答by John Strickler

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

这是我对Hibernate 4.3 的发现，它们与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'ssource for binding the parameter to the underlying CallableStatement:

从Hibernate 的源代码绑定参数到底层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 ifthe 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.

用户未将值绑定到正在处理的参数。这可能是好的，如果在数据库中定义的程序定义了该参数的默认值。不幸的是，没有一种方法可以通过 JDBC 元数据可靠地知道过程参数是否定义了默认值。因此，假设如果未设置给定参数绑定值是一个错误，数据库将适当地抱怨，我们只是允许过程执行发生。

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:

我将其解释为 JPA（特别是 Hibernate）根本不支持设置空参数。看起来他们正在努力支持默认参数值而不是在适当的时候替换空值。他们选择支持前者。看起来那些需要支持后者（可为空值）的人必须使用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;drwe 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.

tl;dr我们仍然被迫处理低级 JDBC，因为 JPA 或 Hibernate 似乎都没有解决可空参数。他们支持过程参数默认值而不是替换空值。

回答by Christopher Parker

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.Stringand then substituting an empty String("") where it would have been appropriate to use a NULLvalue. 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.

但是，因为我使用的数据库是 Oracle 数据库，所以我能够通过将存储过程参数 (in orm.xml)的数据类型更改为java.lang.String，然后在适合使用的地方替换为空String( "")来解决此问题NULL价值。由于 Oracle 对空Strings ( "") 和NULLs 的处理方式相同，因此我能够有效地诱使 Hibernate 将“空”值传递给 Oracle 存储过程。

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.BigDecimalin my case), and wait until you're setting the parameter value to convert to java.lang.String.

如果您不想不得不求助于编写低级 JDBC 代码，您的存储过程不可修改，并且您的数据库是基于 Oracle 的，请尝试这种方法。只需确保您的所有实体管理器代码都将参数视为预期的数据类型（java.math.BigDecimal在我的情况下），然后等到您将参数值设置为转换为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.
}

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>

爪哇：

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.
}

回答by Aayush

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.

为了解决这个问题，如果我们没有很多可能的空参数到 sproc，我们可以有多个 @NamedStoredProcedureQuery 映射到 DB 中的同一个 sproc。然后我们可以准备适当的查询。

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

现在对“nullableparam”进行空检查，我们可以在存储库中创建适当的命名过程。类似的东西

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

回答by Rashmi singh

We can use Empty string, that will also work as null. eg. select NVL('','XXX') from dual; returns XXX Try to pass StringUtils.EMPTY as parameter.

我们可以使用 Empty 字符串，它也可以用作 null。例如。从双中选择 NVL('','XXX'); 返回 XXX 尝试将 StringUtils.EMPTY 作为参数传递。

回答by Alexei Samoukin

Set property hibernate.proc.param_null_passing=true

设置属性 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>

回答by RaviPrakash

This worked for me

这对我有用

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

回答by eskualo

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

这是我执行查询的方法，在 Postgresql 中使用 setNull(i, Type.NULL) 的“解决方法”。

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

解决方案是捕获特定的异常并遍历所有可能的“java.sql.Types”类型，直到找到某种不会抛出异常的类型

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

回答by Sandeep Jain

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

在 springboot 休眠环境中的属性文件中全局设置一个参数为 spring.jpa.properties.hibernate.proc.param_null_passing=true

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

用于允许存储过程在休眠 java 代码中传递 null。

回答by Nguyen Minh Hien

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

您可以创建一个方法来积极启用所有调用参数，如下面的代码，如果您StoredProcedureQuery包含out参数，它也可以正常工作。这有助于您不会在某处破坏空参数检查。

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