Можно ли передать нулевой параметр в хранимую процедуру в Java JPA 2.1?

Ответ 1

Да, можно передавать нулевые параметры в хранимые процедуры при использовании JPA StoredProcedureQuery.

Вам необходимо добавить следующее свойство в файл application.properties и зарегистрировать параметры с их именем.

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

Пример:

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

Ответ 2

Вот мои результаты для Hibernate 4.3, которые относятся к JPA 2.1.

Это вызовет исключение, если БД не поддерживает параметры по умолчанию:

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

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

// execute
procedure.getOutputs();

От источника 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() );
      }
    }
  }

  ..
}

Вышеупомянутый комментарий гласит:

Пользователь не привязывал значение к обрабатываемому параметру. Что может быть нормально, если процедура, определенная в базе данных, определяет значение по умолчанию для этого параметра. К сожалению, нет способа достоверно знать через метаданные JDBC, есть ли параметр процедуры определяет значение по умолчанию. Поэтому мы просто разрешаем выполнение процедуры чтобы предположить, что база данных будет жаловаться надлежащим образом, если не задавая значение привязки данного параметра, является ошибкой.

Я интерпретирую, что, поскольку JPA (в частности, Hibernate) НЕ поддерживает установку нулевых параметров вообще. Похоже, что они борются с поддержкой значений параметров по умолчанию вместо подстановки нулевого значения, когда это необходимо. Они предпочитают поддерживать первое. Похоже, что те, кому нужна поддержка последних (значения NULL), должны использовать 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 мы по-прежнему вынуждены иметь дело с низкоуровневым JDBC, потому что ни JPA, ни Hibernate, похоже, не обращаются к параметрам с нулевым значением. Они поддерживают значения параметров параметров процедуры, заменяя нулевое значение.

Ответ 3

Я столкнулся с этой проблемой. У меня не было контроля над хранимой процедурой, поэтому я не смог изменить его, чтобы принимать значения по умолчанию.

Однако, поскольку используемая мной база данных была базой данных Oracle, мне удалось обойти эту проблему, изменив тип данных моего параметра хранимой процедуры (в orm.xml) на java.lang.String, а затем заменив пустой String (""), где было бы целесообразно использовать значение NULL. Поскольку Oracle обрабатывает пустые String ("") и NULL тождественно, мне удалось эффективно обмануть Hibernate в передаче "нулевого" значения в хранимую процедуру Oracle.

Если вы не хотите прибегать к написанию низкоуровневого кода JDBC, ваша хранимая процедура не модифицируется, а ваша база данных основана на Oracle, попробуйте этот подход. Просто убедитесь, что весь код менеджера объектов обрабатывает параметр, как если бы это был предполагаемый тип данных (java.math.BigDecimal в моем случае), и дождитесь, пока вы установите значение параметра для преобразования в java.lang.String.

Например:

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

Ответ 4

Чтобы обойти проблему, если у нас нет много возможных нулевых параметров для sproc, мы можем иметь несколько сопоставлений @NamedStoredProcedureQuery для одного и того же sproc в БД. Затем мы можем подготовить соответствующий запрос.

Например,

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

Теперь, выполняя нулевую проверку "nullableparam", мы можем создать соответствующий именованный proc в репозитории. Что-то похожее на

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

Ответ 5

Установить свойство hibernate.proc.param_null_passing = истина

Пример:

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

Ответ 6

это мой mehtod для выполнения запросов с "обходным путем" для setNull (i, Type.NULL) в Postgresql.

Решение состоит в том, чтобы поймать конкретное исключение и перебрать все возможные типы "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 required set DataType in NULLs
    {
        try
        {
            rs =  stmt.executeQuery();
            break infinite_loop;
        }
        catch (SQLException e)
        {
            // fix for postgresql --> stmt.setNull(i, Types.NULL); // it 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;
}

Ответ 7

Мы можем использовать пустую строку, которая также будет работать как null. например. выберите NVL ('', 'XXX') из dual; возвращает XXX Попробуйте передать параметр StringUtils.EMPTY в качестве параметра.

Ответ 8

Это сработало для меня

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