How to call a stored procedure from Java and JPA

Question

I am writing a simple web application to call a stored procedure and retrieve some data  Its a very simple application  which interacts with client s database  We pass employee id and company id and the stored procedure will return employee details   Web application cannot update delete data and is using SQL Server   I am deploying my web application in Jboss AS  Should I use JPA to access the stored procedure or CallableStatement  Any advantage of using JPA in this case   Also what will be the sql statement to call this stored procedure  I have never used stored procedures before and I am struggling with this one  Google was not much of a help   Here is the stored procedure   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   For anyone else having problem calling stored procedure using JPA   Query query   em createNativeQuery   call getEmployeeDetails                                            EmployeeDetails class                                                 setParameter 1  employeeId                                      setParameter 2  companyId    List lt EmployeeDetails gt  result   query getResultList      Things I have noticed    Parameter names didn t work for me  so try using parameter index  Correct sql statement  call sp name       instead of  call sp name      If stored procedure is returning a result set  even if you know with only one row  getSingleResult wont work Pass a resultSetMapping name or result class details

User · Answer

To call stored procedure we can use Callable Statement in java sql package

User · Answer

How to retrieve Stored Procedure output parameter using JPA  2 0 needs EclipseLink imports and 2 1 does not   Even though this answer does elaborate on returning a recordset from a stored procedure  I am posting here  because it took me ages to figure it out and this thread helped me   My application was using Eclipselink-2 3 1  but I will force an upgrade to Eclipselink-2 5 0  as JPA 2 1 has much better support for stored procedures   Using EclipseLink-2 3 1 JPA-2 0  Implementation-Dependent  This method requires imports of EclipseLink classes from  org eclipse persistence   so it is specific to Eclipselink implementation   I found it at  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 lt Object gt  argumentValues   new ArrayList lt Object gt     argumentValues add  valueOf i input 1       Add input argument values  JpaEntityManager jpaEntityManager    JpaEntityManager  getEntityManager    Session session   jpaEntityManager getActiveSession    List lt   gt  results    List lt   gt   session executeQuery query  argumentValues   DatabaseRecord record    DatabaseRecord  results get 0   String result   String valueOf record get  o output 1        Get output parameter   Using EclipseLink-2 5 0 JPA-2 1  Implementation-Independent  documented already in this thread   This method is implementation independent  don t need Eclipslink imports    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

User · Answer

My solution was  Create a FUNCTION calling PROCEDURE   So  inside JAVA code you execute a NATIVE QUERY calling the oracle FUNCTION

User · Answer

JPA 2 1 now support Stored Procedure  read the Java doc here   Example   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      See detailed example here

User · Answer

persistence xml    lt persistence-unit name  PU2  transaction-type  RESOURCE LOCAL  gt   lt non-jta-data-source gt jndi ws2 lt  non-jta-data-source gt   lt exclude-unlisted-classes gt false lt  exclude-unlisted-classes gt   lt properties  gt      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

User · Answer

The following works for me   Query query   em createNativeQuery  BEGIN VALIDACIONES QPAI RECALC COMP ASSEMBLY  X   X   X  0   END     query executeUpdate

User · Answer

I am deploying my web application in Jboss AS  Should I use JPA to access the stored procedure or CallableStatement  Any advantage of using JPA in this case    It is not really supported by JPA but it s doable  Still I wouldn t go this way     using JPA just to map the result of a stored procedure call in some beans is really overkill  especially given that JPA is not really appropriate to call stored procedure  the syntax will be pretty verbose     I would thus rather consider using Spring support for JDBC data access  or a data mapper like MyBatis or  given the simplicity of your application  raw JDBC and CallableStatement  Actually  JDBC would probably be my choice  Here is a basic kickoff example    CallableStatement cstmt   con prepareCall   call getEmployeeDetails           cstmt setInt  employeeId   123   cstmt setInt  companyId   456   ResultSet rs   cstmt executeQuery      Reference   JDBC documentation  Java SE 6

User · Answer

For me  only the following worked with Oracle 11g and Glassfish 2 1  Toplink    Query query   entityManager createNativeQuery  BEGIN PROCEDURE NAME    END     query executeUpdate      The variant with curly braces resulted in ORA-00900

User · Answer

For a simple stored procedure that using IN OUT parameters like this  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    You can call it from JPA as follows   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    For a stored procedure which uses a SYS REFCURSOR OUT parameter   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    You can call it as follows   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 lt Object   gt  postComments   query getResultList     For a SQL function that looks as follows   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    You can call it like this   BigDecimal commentCount    BigDecimal  entityManager  createNativeQuery       SELECT fn count comments  postId  FROM DUAL     setParameter  postId   1L   getSingleResult      At least when using Hibernate 4 x and 5 x because the JPA StoredProcedureQuery does not work for SQL FUNCTIONS    For more details about how to call stored procedures and functions when using JPA and Hibernate  check out the following articles   How to call Oracle stored procedures and functions with JPA and Hibernate  How to call SQL Server stored procedures and functions with JPA and Hibernate  How to call PostgreSQL functions  stored procedures  with JPA and Hibernate  How to call MySQL stored procedures and functions with JPA and Hibernate

User · Answer

If using EclipseLink you can use the  NamedStoredProcedureQuery or StoreProcedureCall to execute any stored procedure  including ones with output parameters  or out cursors   Support for stored functions and PLSQL data-types is also available   See  http   en wikibooks org wiki Java Persistence Advanced Topics Stored Procedures

User · Answer

JPA 2 0 doesn t support RETURN values  only calls   My solution was  Create a FUNCTION calling PROCEDURE   So  inside JAVA code you execute a NATIVE QUERY calling the oracle FUNCTION

User · Answer

This worked for me    Entity  Table name  acct    NamedNativeQueries     NamedNativeQuery callable true  name  Account findOne   query  call sp get acct     resultClass Account class    public class Account      Code      Note   in future if you decide to use default version of findOne then just comment the NamedNativeQueries annotation and JPA will switch to default

User · Answer

Try this code   return em createNativeQuery   call getEmployeeDetails                                        EmployeeDetails class                                             setParameter 1  employeeId                                  setParameter 2  companyId  getResultList

User · Answer

From JPA 2 1   JPA supports to call stored procedures using the dynamic StoredProcedureQuery  and the declarative  NamedStoredProcedureQuery

User · Answer

This answer might be helpful if you have entity manager  I had a stored procedure to create next number and on server side I have seam framework   Client side   Object on   entityManager createNativeQuery  EXEC getNextNmber   executeUpdate            log info  New order id      on toString       Database Side  SQL server  I have stored procedure named getNextNmber

User · Answer

You need to pass the parameters to the stored procedure  It should work like this      List result   em        createNativeQuery  quot call getEmployeeDetails  employeeId  companyId  quot          setParameter  quot emplyoyeeId quot   123L         setParameter  quot companyId quot   456L         getResultList      Update  Or maybe it shouldn t  In the Book EJB3 in Action  it says on page 383  that JPA does not support stored procedures  page is only a preview  you don t get the full text  the entire book is available as a download in several places including this one  I don t know if this is legal though   Anyway  the text is this  JPA and database stored procedures  If you   re a big fan of SQL  you may be willing to exploit the power of database stored procedures  Unfortunately  JPA doesn   t support stored procedures  and you have to depend on a proprietary feature of your persistence provider  However  you can use simple stored functions  without out parameters  with a native SQL query

User · Answer

May be it s not the same for Sql Srver but for people using oracle and eclipslink it s working for me   ex  a procedure that have one IN param  type CHAR  and two OUT params  NUMBER  amp  VARCHAR   in the persistence xml  declare the persistence-unit     lt persistence-unit name  presistanceNameOfProc  transaction-type  RESOURCE LOCAL  gt       lt provider gt org eclipse persistence jpa PersistenceProvider lt  provider gt       lt jta-data-source gt jdbc DataSourceName lt  jta-data-source gt       lt mapping-file gt META-INF eclipselink-orm xml lt  mapping-file gt       lt properties gt           lt property name  eclipselink logging level  value  FINEST   gt           lt property name  eclipselink logging logger  value  DefaultLogger   gt           lt property name  eclipselink weaving  value  static   gt           lt property name  eclipselink ddl table-creation-suffix  value  JPA STORED PROC    gt       lt  properties gt   lt  persistence-unit gt    and declare the structure of the proc in the eclipselink-orm xml   lt  xml version  1 0  encoding  UTF-8   gt  lt 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  gt   lt named-stored-procedure-query name  PERSIST PROC NAME  procedure-name  name of proc  returns-result-set  false  gt       lt parameter direction  IN  name  in param char  query-parameter  in param char  type  Character   gt       lt parameter direction  OUT  name  out param int  query-parameter  out param int  type  Integer   gt       lt parameter direction  OUT  name  out param varchar  query-parameter  out param varchar  type  String   gt   lt  named-stored-procedure-query gt      in the code you just have to call your proc like this    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           to get the two output params    Integer myInt    Integer  resultQuery 0   String myStr     String  resultQuery 1

User · Answer

You can use  Query value     call PROC TEST      nativeQuery   true  in your repository  This worked for me   Attention  use     and     or else it will not work

[java] How to call a stored procedure from Java and JPA

Examples related to java

Examples related to sql

Examples related to stored-procedures

Examples related to jpa

Examples related to jboss