Call Stored Procedure with JPA 2.0

Running the Store Procedure in JPA 2.0 is not possible through ready methods, as you can see 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, this way in an Oracle database, as in the example of the link:

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

JPA 2.0 does not have explicit support for stored procedures (JPA 2.1 has). A workaround is to use native queries (such as {CALL SETTINGS}, {CALL SETTINGS}, {CALL SETTINGS, but this does not work when the 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;
        }
    }
}