java - salida - procedimiento almacenado spring

Llamar al procedimiento almacenado desde Java/JPA

Question

Estoy escribiendo una aplicación web simple para llamar a un procedimiento almacenado y recuperar algunos datos. Es una aplicación muy simple, que interactúa con la base de datos del cliente. Pasamos la identificación del empleado y la identificación de la compañía y el procedimiento almacenado devolverá los detalles del empleado.

La aplicación web no puede actualizar / eliminar datos y está usando SQL Server.

Estoy implementando mi aplicación web en Jboss AS. ¿Debo usar JPA para acceder al procedimiento almacenado o CallableStatement ? Cualquier ventaja de usar JPA en este caso.

Además, ¿cuál será la instrucción sql para llamar a este procedimiento almacenado? Nunca antes había usado procedimientos almacenados y estoy luchando con este. Google no fue de mucha ayuda.

Aquí está el procedimiento almacenado:

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

Actualizar:

Para cualquier otra persona que tenga problemas al hacer un procedimiento almacenado usando JPA .

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

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

Cosas que he notado:

1. Los nombres de los parámetros no me funcionaron, así que intente usar el parámetro index.
2. Corregir instrucción SQL {call sp_name(?,?)} lugar de call sp_name(?,?)
3. Si el procedimiento almacenado devuelve un conjunto de resultados, incluso si conoce una sola fila, getSingleResult no funcionará
4. Pase un nombre de resultSetMapping o detalles de la clase de resultado

Answer 1

Estoy implementando mi aplicación web en Jboss AS. ¿Debo usar JPA para acceder al procedimiento almacenado o CallableStatement? Cualquier ventaja de usar JPA en este caso.

Realmente no es compatible con JPA, pero es doable . Todavía no iría de esta manera:

• usar JPA solo para mapear el resultado de una llamada a procedimiento almacenado en algunos beans es realmente exagerado,
• especialmente dado que JPA no es realmente apropiado para llamar al procedimiento almacenado (la sintaxis será bastante detallada).

Por lo tanto, preferiría considerar el uso de Spring para el acceso a datos JDBC , o un mapeador de datos como MyBatis o, dada la simplicidad de su aplicación, JDBC sin procesar y CallableStatement . En realidad, JDBC probablemente sea mi elección. Aquí hay un ejemplo básico de inicio:

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

Referencia

• Documentación de JDBC: Java SE 6

Answer 2

Cómo recuperar el parámetro de salida de Procedimiento almacenado utilizando JPA (2.0 necesita importaciones de EclipseLink y 2.1 no)

A pesar de que esta respuesta detalla el regreso de un conjunto de registros de un procedimiento almacenado, estoy publicando aquí, porque me llevó años descubrirlo y este hilo me ayudó.

Mi aplicación estaba usando Eclipselink-2.3.1, pero forzaré una actualización a Eclipselink-2.5.0, ya que JPA 2.1 tiene un soporte mucho mejor para los procedimientos almacenados.

Usando EclipseLink-2.3.1 / JPA-2.0: Depende de la implementación

Este método requiere importar clases de EclipseLink desde "org.eclipse.persistence", por lo que es específico de la implementación de Eclipselink.

Lo encontré en " 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

Usando EclipseLink-2.5.0 / JPA-2.1: Implementación-Independiente (documentado ya en este hilo)

Este método es independiente de la implementación (no necesita importaciones de 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"));

Answer 3

1. Para un procedimiento almacenado simple que usa parámetros IN / OUT como este

   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;

   Puede llamarlo desde JPA de la siguiente manera:

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

2. Para un procedimiento almacenado que utiliza un parámetro 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;

   Puedes llamarlo de la siguiente manera:

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

3. Para una función SQL que se ve de la siguiente manera:

   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;

   Puedes llamarlo así:

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

   Al menos cuando se utiliza Hibernate 4.x y 5.x porque JPA StoredProcedureQuery no funciona para FUNCIONES SQL.

Para obtener más información sobre cómo llamar a los procedimientos y funciones almacenados cuando utiliza JPA e Hibernate, consulte los siguientes artículos

• Cómo llamar a procedimientos y funciones almacenados de Oracle con JPA e Hibernate
• Cómo llamar a procedimientos y funciones almacenados de SQL Server con JPA e Hibernate
• Cómo llamar a las funciones de PostgreSQL (procedimientos almacenados) con JPA e Hibernate
• Cómo llamar a procedimientos y funciones almacenados de MySQL con JPA e Hibernate

Answer 4

Debe pasar los parámetros al procedimiento almacenado.

Debería funcionar así:

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

Actualizar:

O tal vez no debería.

En el libro EJB3 en acción , dice en la página 383, que JPA no admite procedimientos almacenados (la página es solo una vista previa, no se obtiene el texto completo, todo el libro está disponible como descarga en varios lugares, incluido este , No sé si esto es legal, sin embargo).

De todos modos, el texto es este:

JPA y procedimientos almacenados en la base de datos

Si eres un gran admirador de SQL, puedes estar dispuesto a explotar el poder de los procedimientos almacenados en la base de datos. Desafortunadamente, JPA no es compatible con los procedimientos almacenados, y usted tiene que depender de una característica propietaria de su proveedor de persistencia. Sin embargo, puede usar funciones simples almacenadas (sin parámetros) con una consulta SQL nativa.

Answer 5

Esta respuesta puede ser útil si tienes administrador de entidad

Tenía un procedimiento almacenado para crear el siguiente número y en el lado del servidor tengo el marco de costura.

Lado del cliente

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

Lado de la base de datos (servidor SQL) He almacenado el procedimiento llamado getNextNmber

Answer 6

Esto funcionó para mí.

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

Nota: en el futuro, si decides usar la versión predeterminada de findOne, solo comenta la anotación NamedNativeQueries y JPA cambiará a la predeterminada

Answer 7

JPA 2.0 no admite valores de RETORNO, solo llamadas.

Mi solución fue Crea una FUNCIÓN llamando al PROCEDIMIENTO.

Entonces, dentro del código JAVA ejecutas una CONSULTA NATIVA llamando a la FUNCIÓN DE ORACIÓN.

Answer 8

JPA 2.1 ahora es compatible con Stored Procedure, lea el documento de Java here .

Ejemplo:

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

Ver ejemplo detallado here .

Answer 9

Lo siguiente funciona para mí:

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

Answer 10

Mi solución fue Crea una FUNCIÓN llamando al PROCEDIMIENTO.

Entonces, dentro del código JAVA ejecutas una CONSULTA NATIVA llamando a la FUNCIÓN DE ORACIÓN.

Answer 11

Para llamar al procedimiento almacenado, podemos usar la declaración de llamada en el paquete java.sql.

Answer 12

Para mí, solo lo siguiente funcionó con Oracle 11g y Glassfish 2.1 (Toplink):

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

La variante con llaves dio como resultado ORA-00900.

Answer 13

Prueba este código:

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

Answer 14

Puede ser que no sea lo mismo para Sql Srver, pero para las personas que usan Oracle y eclipslink funciona para mí

ex: un procedimiento que tiene un param IN (tipo CHAR) y dos params OUT (NUMBER & VARCHAR)

en persistence.xml declare la unidad de persistencia:

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

y declarar la estructura del proceso en el 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>

en el código solo tienes que llamar a tu proc así:

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

para obtener los dos parámetros de salida:

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

Answer 15

Puede usar @Query(value = "{call PROC_TEST()}", nativeQuery = true) en su repositorio. Esto funcionó para mí.

Atención: use ''{'' y ''}'' o de lo contrario no funcionará.

Answer 16

Si usa EclipseLink, puede usar @NamedStoredProcedureQuery o StoreProcedureCall para ejecutar cualquier procedimiento almacenado, incluidos los que tienen parámetros de salida o los cursores. El soporte para funciones almacenadas y tipos de datos PLSQL también está disponible.

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

Answer 17

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