Вызов хранимой процедуры из Java/JPA

Я пишу простое веб-приложение для вызова хранимой процедуры и получения некоторых данных. Это очень простое приложение, которое взаимодействует с клиентской базой данных. Мы передаем идентификатор сотрудника и идентификатор компании, а хранимая процедура возвращает данные о сотрудниках.

Веб-приложение не может обновлять/удалять данные и использует SQL Server.

Я развертываю свое веб-приложение в Jboss AS. Должен ли я использовать JPA для доступа к хранимой процедуре или CallableStatement. Любое преимущество использования JPA в этом случае.

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

Вот хранимая процедура:

CREATE procedure getEmployeeDetails (@employeeId int, @companyId int)
as
begin
    select firstName, 
           lastName, 
           gender, 
           address
      from employee et
     where et.employeeId = @employeeId
       and et.companyId = @companyId
end

Update:

Для всех, у кого есть проблема, вызов хранимой процедуры с использованием JPA.

Query query = em.createNativeQuery("{call getEmployeeDetails(?,?)}",
                                   EmployeeDetails.class)           
                                   .setParameter(1, employeeId)
                                   .setParameter(2, companyId);

List<EmployeeDetails> result = query.getResultList();

Что я заметил:

Имена параметров для меня не работали, поэтому попробуйте использовать параметр index.
Корректный оператор sql {call sp_name(?,?)} вместо call sp_name(?,?)
Если хранимая процедура возвращает набор результатов, даже если вы знаете только одну строку, getSingleResult не работает
Передайте resultSetMapping имя или результирующий класс.

Ответы

Ответ 1

JPA 2.1 теперь поддерживает хранимую процедуру, прочитайте документ Java здесь.

Пример:

StoredProcedureQuery storedProcedure = em.createStoredProcedureQuery("sales_tax");
// set parameters
storedProcedure.registerStoredProcedureParameter("subtotal", Double.class, ParameterMode.IN);
storedProcedure.registerStoredProcedureParameter("tax", Double.class, ParameterMode.OUT);
storedProcedure.setParameter("subtotal", 1f);
// execute SP
storedProcedure.execute();
// get result
Double tax = (Double)storedProcedure.getOutputParameterValue("tax");

См. подробный пример здесь.

Ответ 2

Я развертываю свое веб-приложение в Jboss AS. Должен ли я использовать JPA для доступа к хранимой процедуре или CallableStatement. Любое преимущество использования JPA в этом случае.

Это не поддерживается JPA, но выполнимо. Тем не менее я бы не пошел так:

использование JPA только для сопоставления результата вызова хранимой процедуры в некотором beans действительно переполняется,
особенно учитывая, что JPA не подходит для вызова хранимой процедуры (синтаксис будет довольно подробным).

Поэтому я предпочел бы использовать Spring поддержку доступа к данным JDBC или устройство сопоставления данных, например MyBatis или, учитывая простоту вашего приложения, необработанные JDBC и CallableStatement. Собственно, JDBC, вероятно, будет моим выбором. Вот пример базового запуска:

CallableStatement cstmt = con.prepareCall("{call getEmployeeDetails(?, ?)}");
cstmt.setInt("employeeId", 123);
cstmt.setInt("companyId", 456);
ResultSet rs = cstmt.executeQuery();

Ссылка

Документация JDBC: Java SE 6

Ответ 3

Вам нужно передать параметры хранимой процедуре.

Он должен работать следующим образом:

    List result = em
      .createNativeQuery("call getEmployeeDetails(:employeeId,:companyId)")
      .setParameter("emplyoyeeId", 123L)
      .setParameter("companyId", 456L)
      .getResultList();

Update:

Или, может быть, не стоит.

В книге EJB3 в действии на странице 383 указано, что JPA не поддерживает хранимые процедуры (страница - это только предварительный просмотр, вы не получаете полный текст, вся книга доступна в виде загрузки в нескольких местах, включая этот, я не знаю, законно ли это).

В любом случае, текст таков:

JPA и хранимые процедуры базы данных

Если вы большой поклонник SQL, вы можете быть желая использовать силу хранимые процедуры базы данных. К сожалению, JPA не поддерживает хранимые процедуры, и вы должны зависят от проприетарной особенности ваш поставщик постоянства. Однако, вы можете использовать простые хранимые функции (без параметров) с родным SQL-запрос.

Ответ 4

Как получить выходной параметр хранимой процедуры с помощью JPA (требуется 2.0 EclipseLink import и 2.1 не)

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

Мое приложение использовало Eclipselink-2.3.1, но я буду принудительно обновлять до Eclipselink-2.5.0, поскольку JPA 2.1 имеет гораздо лучшую поддержку хранимых процедур.

Использование EclipseLink-2.3.1/JPA-2.0: зависимый от реализации

Этот метод требует импорта классов EclipseLink из "org.eclipse.persistence", поэтому он специфичен для реализации Eclipselink.

Я нашел его на "http://www.yenlo.nl/en/calling-oracle-stored-procedures-from-eclipselink-with-multiple-out-parameters".

StoredProcedureCall storedProcedureCall = new StoredProcedureCall();
storedProcedureCall.setProcedureName("mypackage.myprocedure");
storedProcedureCall.addNamedArgument("i_input_1"); // Add input argument name.
storedProcedureCall.addNamedOutputArgument("o_output_1"); // Add output parameter name.
DataReadQuery query = new DataReadQuery();
query.setCall(storedProcedureCall);
query.addArgument("i_input_1"); // Add input argument names (again);
List<Object> argumentValues = new ArrayList<Object>();
argumentValues.add("valueOf_i_input_1"); // Add input argument values.
JpaEntityManager jpaEntityManager = (JpaEntityManager) getEntityManager();
Session session = jpaEntityManager.getActiveSession();
List<?> results = (List<?>) session.executeQuery(query, argumentValues);
DatabaseRecord record = (DatabaseRecord) results.get(0);
String result = String.valueOf(record.get("o_output_1")); // Get output parameter

Использование EclipseLink-2.5.0/JPA-2.1: независимость от реализации (документирована уже в этом потоке)

Этот метод является независимым от реализации (им не нужен импорт Eclipslink).

StoredProcedureQuery query = getEntityManager().createStoredProcedureQuery("mypackage.myprocedure");
query.registerStoredProcedureParameter("i_input_1", String.class, ParameterMode.IN);
query.registerStoredProcedureParameter("o_output_1", String.class, ParameterMode.OUT);
query.setParameter("i_input_1", "valueOf_i_input_1");
boolean queryResult = query.execute();
String result = String.valueOf(query.getOutputParameterValue("o_output_1"));

Ответ 5

Для меня работали только с Oracle 11g и Glassfish 2.1 (Toplink):

Query query = entityManager.createNativeQuery("BEGIN PROCEDURE_NAME(); END;");
query.executeUpdate();

Вариант с фигурными скобками привел к ORA-00900.

Ответ 6

Если вы используете EclipseLink, вы можете использовать @NamedStoredProcedureQuery или StoreProcedureCall для выполнения любой хранимой процедуры, в том числе с выходными параметрами или с помощью курсоров. Также доступна поддержка хранимых функций и типов данных PLSQL.

См, http://en.wikibooks.org/wiki/Java_Persistence/Advanced_Topics#Stored_Procedures

Ответ 7

Для простой хранимой процедуры, которая использует параметры IN/OUT, как это

CREATE OR REPLACE PROCEDURE count_comments (  
   postId IN NUMBER,  
   commentCount OUT NUMBER )  
AS 
BEGIN 
    SELECT COUNT(*) INTO commentCount  
    FROM post_comment  
    WHERE post_id = postId; 
END;

Вы можете позвонить из JPA следующим образом:

StoredProcedureQuery query = entityManager
    .createStoredProcedureQuery("count_comments")
    .registerStoredProcedureParameter(1, Long.class, 
        ParameterMode.IN)
    .registerStoredProcedureParameter(2, Long.class, 
        ParameterMode.OUT)
    .setParameter(1, 1L);

query.execute();

Long commentCount = (Long) query.getOutputParameterValue(2);

Для хранимой процедуры, которая использует параметр SYS_REFCURSOR OUT:

CREATE OR REPLACE PROCEDURE post_comments ( 
   postId IN NUMBER, 
   postComments OUT SYS_REFCURSOR ) 
AS 
BEGIN
    OPEN postComments FOR
    SELECT *
    FROM post_comment 
    WHERE post_id = postId; 
END;

Вы можете назвать это следующим образом:

StoredProcedureQuery query = entityManager
    .createStoredProcedureQuery("post_comments")
    .registerStoredProcedureParameter(1, Long.class, 
         ParameterMode.IN)
    .registerStoredProcedureParameter(2, Class.class, 
         ParameterMode.REF_CURSOR)
    .setParameter(1, 1L);

query.execute();

List<Object[]> postComments = query.getResultList();

Для функции SQL, которая выглядит следующим образом:

CREATE OR REPLACE FUNCTION fn_count_comments ( 
    postId IN NUMBER ) 
    RETURN NUMBER 
IS
    commentCount NUMBER; 
BEGIN
    SELECT COUNT(*) INTO commentCount 
    FROM post_comment 
    WHERE post_id = postId; 
    RETURN( commentCount ); 
END;

Вы можете назвать это так:

BigDecimal commentCount = (BigDecimal) entityManager
.createNativeQuery(
    "SELECT fn_count_comments(:postId) FROM DUAL"
)
.setParameter("postId", 1L)
.getSingleResult();

По крайней мере, при использовании Hibernate 4.x и 5.x, потому что JPA StoredProcedureQuery не работает для функций SQL.

Подробнее о том, как вызывать хранимые процедуры и функции при использовании JPA и Hibernate, читайте в следующих статьях.

Ответ 8

Для меня работает следующее:

Query query = em.createNativeQuery("BEGIN VALIDACIONES_QPAI.RECALC_COMP_ASSEMBLY('X','X','X',0); END;");
query.executeUpdate();

Ответ 9

Может быть, это не одно и то же для Sql Srver, но для людей, использующих oracle и eclipslink, он работает для меня.

ex: процедура, имеющая один параметр IN (тип CHAR) и два параметра OUT (NUMBER и VARCHAR)

в файле persistence.xml объявите блок persistence:

<persistence-unit name="presistanceNameOfProc" transaction-type="RESOURCE_LOCAL">
    <provider>org.eclipse.persistence.jpa.PersistenceProvider</provider>
    <jta-data-source>jdbc/DataSourceName</jta-data-source>
    <mapping-file>META-INF/eclipselink-orm.xml</mapping-file>
    <properties>
        <property name="eclipselink.logging.level" value="FINEST"/>
        <property name="eclipselink.logging.logger" value="DefaultLogger"/>
        <property name="eclipselink.weaving" value="static"/>
        <property name="eclipselink.ddl.table-creation-suffix" value="JPA_STORED_PROC" />
    </properties>
</persistence-unit>

и объявите структуру proc в файле eclipselink-orm.xml

<?xml version="1.0" encoding="UTF-8"?><entity-mappings version="2.0"
xmlns="http://java.sun.com/xml/ns/persistence/orm" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/persistence/orm orm_2_0.xsd">
<named-stored-procedure-query name="PERSIST_PROC_NAME" procedure-name="name_of_proc" returns-result-set="false">
    <parameter direction="IN" name="in_param_char" query-parameter="in_param_char" type="Character"/>
    <parameter direction="OUT" name="out_param_int" query-parameter="out_param_int" type="Integer"/>
    <parameter direction="OUT" name="out_param_varchar" query-parameter="out_param_varchar" type="String"/>
</named-stored-procedure-query>

в коде, который вы просто должны назвать своим proc следующим образом:

try {
        final Query query = this.entityManager
                .createNamedQuery("PERSIST_PROC_NAME");
        query.setParameter("in_param_char", 'V'); 
        resultQuery = (Object[]) query.getSingleResult();

    } catch (final Exception ex) {
        LOGGER.log(ex);
        throw new TechnicalException(ex);
    }

чтобы получить два выходных параметра:

Integer myInt = (Integer) resultQuery[0];
String myStr =  (String) resultQuery[1];

Ответ 10

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

@Entity
@Table(name="acct")
@NamedNativeQueries({
 @NamedNativeQuery(callable=true, name="Account.findOne", query="call sp_get_acct(?), resultClass=Account.class)})
public class Account{
 // Code 
}

Примечание: в будущем, если вы решите использовать версию findOne по умолчанию, просто прокомментируйте аннотацию NamedNativeQueries, и JPA переключится на значение по умолчанию

Ответ 11

Этот ответ может быть полезен, если у вас есть менеджер сущностей

У меня была хранимая процедура для создания следующего номера, а на стороне сервера у меня была структура шва.

Клиентская сторона

 Object on = entityManager.createNativeQuery("EXEC getNextNmber").executeUpdate();
        log.info("New order id: " + on.toString());

Сторона базы данных (SQL-сервер) У меня есть хранимая процедура с именем getNextNmber

Ответ 12

JPA 2.0 не поддерживает значения RETURN, только вызовы.

Мое решение было. Создайте ПРОЦЕДУРУ FUNCTION.

Итак, внутри JAVA-кода вы выполняете NATIVE QUERY, вызывающий функцию oracle FUNCTION.

Ответ 13

Для вызова хранимой процедуры мы можем использовать оператор Callable в пакете java.sql.

Ответ 14

Попробуйте этот код:

return em.createNativeQuery("{call getEmployeeDetails(?,?)}",
                               EmployeeDetails.class)           
                               .setParameter(1, employeeId)
                               .setParameter(2, companyId).getResultList();

Ответ 15

Вы можете использовать @Query(value = "{call PROC_TEST()}", nativeQuery = true) в своем репозитории. Это сработало для меня.

Внимание: используйте '{' и '}', иначе это не сработает.

Ответ 16

persistence.xml

 <persistence-unit name="PU2" transaction-type="RESOURCE_LOCAL">
<non-jta-data-source>jndi_ws2</non-jta-data-source>
<exclude-unlisted-classes>false</exclude-unlisted-classes>
<properties/>

codigo java

  String PERSISTENCE_UNIT_NAME = "PU2";
    EntityManagerFactory factory2;
    factory2 = Persistence.createEntityManagerFactory(PERSISTENCE_UNIT_NAME);

    EntityManager em2 = factory2.createEntityManager();
    boolean committed = false;
    try {

        try {
            StoredProcedureQuery storedProcedure = em2.createStoredProcedureQuery("PKCREATURNO.INSERTATURNO");
            // set parameters
            storedProcedure.registerStoredProcedureParameter("inuPKEMPRESA", BigDecimal.class, ParameterMode.IN);
            storedProcedure.registerStoredProcedureParameter("inuPKSERVICIO", BigDecimal.class, ParameterMode.IN);
            storedProcedure.registerStoredProcedureParameter("inuPKAREA", BigDecimal.class, ParameterMode.IN);
            storedProcedure.registerStoredProcedureParameter("isbCHSIGLA", String.class, ParameterMode.IN);
            storedProcedure.registerStoredProcedureParameter("INUSINCALIFICACION", BigInteger.class, ParameterMode.IN);
            storedProcedure.registerStoredProcedureParameter("INUTIMBRAR", BigInteger.class, ParameterMode.IN);
            storedProcedure.registerStoredProcedureParameter("INUTRANSFERIDO", BigInteger.class, ParameterMode.IN);
            storedProcedure.registerStoredProcedureParameter("INTESTADO", BigInteger.class, ParameterMode.IN);
            storedProcedure.registerStoredProcedureParameter("inuContador", BigInteger.class, ParameterMode.OUT);

            BigDecimal inuPKEMPRESA = BigDecimal.valueOf(1);
            BigDecimal inuPKSERVICIO = BigDecimal.valueOf(5);
            BigDecimal inuPKAREA = BigDecimal.valueOf(23);
            String isbCHSIGLA = "";
            BigInteger INUSINCALIFICACION = BigInteger.ZERO;
            BigInteger INUTIMBRAR = BigInteger.ZERO;
            BigInteger INUTRANSFERIDO = BigInteger.ZERO;
            BigInteger INTESTADO = BigInteger.ZERO;
            BigInteger inuContador = BigInteger.ZERO;

            storedProcedure.setParameter("inuPKEMPRESA", inuPKEMPRESA);
            storedProcedure.setParameter("inuPKSERVICIO", inuPKSERVICIO);
            storedProcedure.setParameter("inuPKAREA", inuPKAREA);
            storedProcedure.setParameter("isbCHSIGLA", isbCHSIGLA);
            storedProcedure.setParameter("INUSINCALIFICACION", INUSINCALIFICACION);
            storedProcedure.setParameter("INUTIMBRAR", INUTIMBRAR);
            storedProcedure.setParameter("INUTRANSFERIDO", INUTRANSFERIDO);
            storedProcedure.setParameter("INTESTADO", INTESTADO);
            storedProcedure.setParameter("inuContador", inuContador);

            // execute SP
            storedProcedure.execute();
            // get result

            try {
                long _inuContador = (long) storedProcedure.getOutputParameterValue("inuContador");
                varCon = _inuContador + "";
            } catch (Exception e) {
            } 
        } finally {

        }
    } finally {
        em2.close();
    }

Ответ 17

Начиная с JPA 2.1, JPA поддерживает вызов хранимых процедур с использованием динамического StoredProcedureQuery и декларативного @NamedStoredProcedureQuery.

Ответ 18

Пожалуйста, найдите ниже ссылки: Как родной sql: https://viousts-on-java.org/call-stored-procedures-jpa/

Как JPA https://www.baeldung.com/jpa-stored-procedures

Ответ 19

Мое решение было. Создайте ПРОЦЕДУРУ FUNCTION.

Итак, внутри JAVA-кода вы выполняете NATIVE QUERY, вызывающий функцию oracle FUNCTION.