Call Stored Procedure with JPA 2.0

Question

I need to call the following process below, only the company’s projects use JPA 2.0 and all the examples I find are for JPA 2.1 +.

prc_performance_vendas_obter(vdataini => :vdataini,
                           vdatafim => :vdatafim,
                           vrefcursor => :vrefcursor)

My biggest problem is what I should pass as a parameter to the vrefcursor, in 2.1 there is an Enum Paramethermode where there are types IN, OUT... to be selected, only here I found nothing for the 2.0.

Answer 1

Running Store Procedure on JPA 2.0 is not possible through ready methods, as you can see at this link.

JPA 2.0 does not have any direct support for stored procedures.

What you can do is try to run the Store Procedure through Native Query, in this way in an Oracle database, as in the link example:

Query query = em.createNativeQuery("BEGIN prc_performance_vendas_obter(vdatain => ?, vdatafim => ?, vrefcursor => ?); END;");
query.setParameter(1, dataIni);
query.setParameter(2, dataFim);
query.setParameter(3, refCursor);
query.executeUpdate();

Or to get results:

Query query = em.createNativeQuery("BEGIN prc_performance_vendas_obter(vdatain => ?, vdatafim => ?, vrefcursor => ?); END;", Vendas.class);
query.setParameter(1, dataIni);
query.setParameter(2, dataFim);
query.setParameter(3, refCursor);
List<Venda> result = query.getResultList();

Answer 2

JPA 2.0 has no explicit support for stored procedures (JPA 2.1 has). An alternative solution is to use native queries (such as {CALL APURARCAMPANHASBRINDES.PROC_APURARCAMPANHA(?1,?2, ?3, ?4, ?5, ?6, ?7)}), but this does not work when Procedure has external parameters. Here is an example implementation that uses the Hibernate Work interface.

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;

import org.hibernate.Session;
import org.hibernate.jdbc.Work;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

@Service
public class CampanhaBrindesStoredProcedure {

    @PersistenceContext
    private EntityManager entityManager;

    private String mensagem;
    private String geroubrinde;

    @Transactional
    public void apurarCampamha(Long numeroPedido, String codigoFilialNF, String cgcEntCGCENT, Long numeroSequencia,
            String integradora)  {
        try {
            MyStoredProc storedProc = new MyStoredProc(numeroPedido, codigoFilialNF, cgcEntCGCENT, numeroSequencia,
                    integradora);
            entityManager.unwrap(Session.class).doWork(storedProc);
            setGeroubrinde(storedProc.getGeroubrinde());
            setMensagem(storedProc.getMensagem());
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public String getGeroubrinde() {
        return geroubrinde;
    }

    public void setGeroubrinde(String geroubrinde) {
        this.geroubrinde = geroubrinde;
    }

    public String getMensagem() {
        return mensagem;
    }

    public void setMensagem(String mensagem) {
        this.mensagem = mensagem;
    }

    private static final class MyStoredProc implements Work {

        private final Long numeroPedido;
        private final String codigoFilialNF;
        private final String cgcEntCGCENT;
        private final Long numeroSequencia;
        private final String integradora;
        private String mensagem;
        private String geroubrinde;

        private MyStoredProc(Long numeroPedido, String codigoFilialNF, String cgcEntCGCENT, Long numeroSequencia,
                String integradora) {
            this.numeroPedido = numeroPedido;
            this.codigoFilialNF = codigoFilialNF;
            this.cgcEntCGCENT = cgcEntCGCENT;
            this.numeroSequencia = numeroSequencia;
            this.integradora = integradora;
        }

        @Override
        public void execute(Connection conn) throws SQLException {
            try (CallableStatement stmt = conn
                    .prepareCall("{CALL APURARCAMPANHASBRINDES.PROC_APURARCAMPANHA(?1, ?2, ?3,  ?4, ?5, ?6, ?7)}")) {
                stmt.setLong(1, numeroPedido);
                stmt.setString(2, codigoFilialNF);
                stmt.setString(3, cgcEntCGCENT);
                stmt.setLong(4, numeroSequencia);
                stmt.setString(5, integradora);
                stmt.registerOutParameter(6, Types.VARCHAR);
                stmt.registerOutParameter(7, Types.VARCHAR);
                stmt.executeUpdate();
                mensagem = stmt.getString(6);
                geroubrinde = stmt.getString(7);
                if (stmt.wasNull()) {
                    geroubrinde = null;
                    mensagem = null;
                }
            }
        }

        public String getMensagem() {
            return mensagem;
        }

        public String getGeroubrinde() {
            return geroubrinde;
        }
    }
}