Executando Stored Procedures ORACLE via SPRING 4 + JPA 2.1

Boa noite pessoal,

Estou tentando criar um método que execute uma Stored Procedure no Oracle. Estou utilizando Spring 4 e JPA 2.1. Basicamente minha necessidade é criar um método
que me retorna uma lista de uma classe especifica. Como o mapeamento dessa classe envolve muitos JOINS, resolvi criar uma Procedure no Oracle para facilitar.

Basicamente minha aplicação ficou:

Model:

@NamedStoredProcedureQueries({
@NamedStoredProcedureQuery(name = "ERROLISTA", procedureName = "ERROLISTA",
        resultClasses = Erro.class, parameters = { 
                @StoredProcedureParameter(mode=ParameterMode.IN, type=Long.class, name="I_IDERRO"),
                @StoredProcedureParameter(mode=ParameterMode.IN, type=String.class, name="I_CDSERVICO"),
                @StoredProcedureParameter(mode=ParameterMode.IN, type=String.class, name="I_CDOPERACAO"),
                @StoredProcedureParameter(mode=ParameterMode.IN, type=String.class, name="I_CDCAMADA"),
                @StoredProcedureParameter(mode=ParameterMode.IN, type=String.class, name="I_CDSISTEMATAM"),
                @StoredProcedureParameter(mode=ParameterMode.IN, type=Long.class, name="I_IDAPI"),
                @StoredProcedureParameter(mode=ParameterMode.IN, type=String.class, name="I_CDERRO"),
                @StoredProcedureParameter(mode=ParameterMode.IN, type=String.class, name="I_DSERRO"),
                @StoredProcedureParameter(mode = ParameterMode.REF_CURSOR, type = void.class,name = "O_CURSOR")            
        }        
    )
})
@Entity
@NamedQuery(name = "Erro.findAll", query = "SELECT e FROM Erro e")
public class Erro implements Serializable {
    private static final long serialVersionUID = 1L;

    @Id
    @SequenceGenerator(name = "ERRO_IDERRO_GENERATOR", sequenceName = "ERROSQ")
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "ERRO_IDERRO_GENERATOR")
    private long iderro;

    private String cderro;

    private String dserro;

    // bi-directional many-to-one association to Api
    @ManyToOne
    @JoinColumn(name = "IDAPI")
    private Api api;

    // bi-directional many-to-one association to Camada
    @ManyToOne
    @JoinColumn(name = "CDCAMADA")
    private Camada camada;

    // bi-directional many-to-one association to Operacao
    @ManyToOne
    @JoinColumns({ @JoinColumn(name = "CDOPERACAO", referencedColumnName = "CDOPERACAO"),
            @JoinColumn(name = "CDSERVICO", referencedColumnName = "CDSERVICO") })
    private Operacao operacao;

    // bi-directional many-to-one association to Sistematam
    @ManyToOne
    @JoinColumn(name = "CDSISTEMATAM")
    private Sistematam sistematam;

    public Erro() {
    }
    
    //Getters and Setters
}

DAO

public List<Erro> buscarErro(long idErro, String cdServico, String cdOperacao, String cdCamada, String cdSistemaTAM, long idApi ){
        
        Query query = this.em.createNamedStoredProcedureQuery("ERROLISTA");
        query.setParameter("I_IDERRO", idErro);
        query.setParameter("I_CDSERVICO", cdServico);
        query.setParameter("I_CDOPERACAO",cdOperacao);
        query.setParameter("I_CDCAMADA",cdCamada);
        query.setParameter("I_CDSISTEMATAM",cdSistemaTAM);
        query.setParameter("I_IDAPI", idApi);
        query.setParameter("I_CDERRO", null);
        query.setParameter("I_DSERRO", null);
        
         return query.getResultList();
    }

Procedure:

create or replace PROCEDURE          ERROLISTA
(
   I_IDERRO                        IN  NUMBER,
   I_CDSERVICO                     IN  VARCHAR2,
   I_CDOPERACAO                    IN  VARCHAR2,
   I_CDCAMADA                      IN  VARCHAR2,
   I_CDSISTEMATAM                  IN  VARCHAR2,
   I_IDAPI                         IN  NUMBER,
   I_CDERRO                        IN  VARCHAR2,
   I_DSERRO                        IN  VARCHAR2,
   O_CURSOR                        OUT SYS_REFCURSOR,
   I_NRPAGINA                      IN  NUMBER,
   I_QTREGISTROS                   IN  NUMBER
)
AS

V_REGISTROINICIAL NUMBER;
V_REGISTROFINAL NUMBER;

BEGIN

   V_REGISTROINICIAL := (NVL(I_NRPAGINA, 1) * NVL(I_QTREGISTROS, 10) + 1) - NVL(I_QTREGISTROS, 10);
   V_REGISTROFINAL   := NVL(I_NRPAGINA, 1) * NVL(I_QTREGISTROS, 10);

   OPEN O_CURSOR
    FOR SELECT X.IDERRO,
               X.CDSERVICO,
               X.CDOPERACAO,
               X.CDCAMADA,
               X.CDSISTEMATAM,
               X.IDAPI,
               X.CDERRO,
               X.DSERRO
          FROM (SELECT ROWNUM AS NRREGISTRO,
                       ERRO.IDERRO,
                       ERRO.CDSERVICO,
                       ERRO.CDOPERACAO,
                       ERRO.CDCAMADA,
                       ERRO.CDSISTEMATAM,
                       ERRO.IDAPI,
                       ERRO.CDERRO,
                       ERRO.DSERRO
                  FROM TRADUTOR.ERRO
                 WHERE (ERRO.IDERRO = I_IDERRO OR I_IDERRO = -1)
                   AND (ERRO.CDSERVICO LIKE '%' || I_CDSERVICO || '%' OR I_CDSERVICO IS NULL)
                   AND (ERRO.CDOPERACAO LIKE '%' || I_CDOPERACAO || '%' OR I_CDOPERACAO IS NULL)
                   AND (ERRO.CDCAMADA LIKE '%' || I_CDCAMADA || '%' OR I_CDCAMADA IS NULL)
                   AND (ERRO.CDSISTEMATAM LIKE '%' || I_CDSISTEMATAM || '%' OR I_CDSISTEMATAM IS NULL)
                   AND (ERRO.IDAPI = I_IDAPI OR I_IDAPI = -1)
                   AND (ERRO.CDERRO LIKE '%' || I_CDERRO || '%' OR I_CDERRO IS NULL)
                   AND (ERRO.DSERRO LIKE '%' || I_DSERRO || '%' OR I_DSERRO IS NULL)
                   AND ROWNUM <= V_REGISTROFINAL) X
         WHERE X.NRREGISTRO >= V_REGISTROINICIAL;

END ERROLISTA;

Quando executo a aplicação, recebo o seguinte erro:

GRAVE: Servlet.service() for servlet [dispatcher] in context with path [/tradutor] threw exception [Request processing failed; nested exception is java.lang.UnsupportedOperationException: org.hibernate.dialect.Oracle10gDialect does not support resultsets via stored procedures] with root cause
java.lang.UnsupportedOperationException: org.hibernate.dialect.Oracle10gDialect does not support resultsets via stored procedures

Verifiquei neste link qual seria o dialeto correto para configurar no JPA: Hibernate Documentação, e verifiquei que estou utilizando o correto.

Abaixo, esta a configuração que fiz no Spring:

public class JPAConfiguration {

    @Bean
    public LocalContainerEntityManagerFactoryBean entityManagerFactory(){
        LocalContainerEntityManagerFactoryBean localContainerEntityManagerFactoryBean = new LocalContainerEntityManagerFactoryBean();
        JpaVendorAdapter jpaVendorAdapter = new HibernateJpaVendorAdapter();
        
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setUsername("system");
        dataSource.setPassword("manager");
        dataSource.setUrl("jdbc:oracle:thin:@127.0.0.1:1521/XE");
        dataSource.setDriverClassName("oracle.jdbc.driver.OracleDriver");
        
        localContainerEntityManagerFactoryBean.setJpaVendorAdapter(jpaVendorAdapter);
        localContainerEntityManagerFactoryBean.setDataSource(dataSource);
        
        Properties properties = new Properties();
        properties.setProperty("hibernate.archive.autodetection", "class");
        properties.setProperty("hibernate.dialect", "org.hibernate.dialect.Oracle10gDialect");
        properties.setProperty("hibernate.show_sql", "true");
        properties.setProperty("hibernate.hbm2ddl.auto", "validate");
        
        localContainerEntityManagerFactoryBean.setJpaProperties(properties);
        localContainerEntityManagerFactoryBean.setPackagesToScan("br.com.test");
        return localContainerEntityManagerFactoryBean;
    }
}

Preciso de ajuda para avaliar se estou utilizando o JPA da forma correta para executar uma procedure no Oracle, ou se estou cometendo algum erro ao trabalhar o retorno via SYS_REFCURSOR .

Agradecido.

Com certeza é melhor usar diretamente JDBC do que JPA/Hibernate, muito mais leve e sem mistérios. Onde notar que o código fica repetitivo, é só criar uma biblioteca que centralize comportamentos.

Sobre seu motivo de usar stored procedure só pra retornar Select, é ilusão pensar dessa forma. Não tem como fugir, se é no banco ou em classes DAO, de uma forma ou outra você vai ter que se preocupar em mexer. Imagina no dia a dia, toda hora que precisar criar ou alterar uma query ter que ir no banco. Na minha opinião só atrapalha isso ficar fora do mesmo ambiente de desenvolvimento. Mas se você se sente confortável assim, tem que seguir o que for melhor pra você mesmo, só acho que o motivo deveria ser outro, como por exemplo deixar os selects a cargo de especialistas de banco de dados, se este for o real cenário.

4 Respostas

Topicos relacionados