By user431514


2010-08-26 06:42:33 8 Comments

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<EmployeeDetails> result = query.getResultList();

Things I have noticed:

  1. Parameter names didn't work for me, so try using parameter index.
  2. Correct sql statement {call sp_name(?,?)} instead of call sp_name(?,?)
  3. If stored procedure is returning a result set, even if you know with only one row, getSingleResult wont work
  4. Pass a resultSetMapping name or result class details

18 comments

@H.Ostwal 2019-03-15 10:31:12

From JPA 2.1 , JPA supports to call stored procedures using the dynamic StoredProcedureQuery, and the declarative @NamedStoredProcedureQuery.

@Amit 2018-03-22 18:47:05

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

@Vlad Mihalcea 2018-01-08 10:03:15

  1. 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);
    
  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<Object[]> postComments = query.getResultList();
    
  3. 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

@Ahmet 2019-01-04 15:32:55

I kept getting "wrong number or types of arguments in call to ..." error message. I realized that I was calling createNativeQuery. I switched to createStoredProcedureQuery. Then, voila!

@Musashi Miyamoto 2017-03-16 16:32:05

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.

@Musashi Miyamoto 2017-03-16 16:28:40

My solution was. Create a FUNCTION calling PROCEDURE.

So, inside JAVA code you execute a NATIVE QUERY calling the oracle FUNCTION.

@Elohim Julio Cesar C 2015-03-31 20:27:47

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

@ivan.mylyanyk 2015-03-31 20:49:25

please, don't hesitate to add any comment to your answer (other than pure code).

@Everton Oliveira 2014-09-22 19:34:08

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.

@Réda 2013-06-24 18:29:23

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 & VARCHAR)

in the persistence.xml declare the persistence-unit :

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

and declare the structure of the proc in the 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>

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

@Malcolm Boekhoff 2013-10-01 03:40:03

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

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

@Lukas Eder 2013-11-20 10:30:58

Aah, my eyes hurt. This isn't really that much better than JDBC, is it?

@Malcolm Boekhoff 2014-02-17 05:36:53

Haha, yes point taken. However the benefit to using these things is that you don't have to type a load of code in to get the data object class and you don't have to do the bit where you transfer all the data from the recordSet into your data class. There's still a data object (Entity), but the Eclipse wizard generates it for you.

@Lukas Eder 2014-02-17 07:48:53

Yes, you could. But I'm saying this as the developer of jOOQ, where everything is generated. Only thing left to do is to actually call the procedure / function.

@Roland 2015-02-27 10:38:03

Did you actually try the bottom example(implementation independent)? I tried it with the difference that the procedure was defined in an xml file and it didn't work. I can't read the OUT parameter.

@Pau Kiat Wee 2013-09-01 06:10:20

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.

@Patricio Dominguez 2013-04-13 13:33:39

Try this code:

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

@tosha Shah 2013-02-08 19:53:18

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

@Constantine Gladky 2013-06-14 14:36:18

executeUpdate() return int. Are sure that you a receiving output of sproc?

@roberto quevedo 2012-11-28 20:25:05

The following works for me:

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

@Valentin Jacquemin 2013-11-06 13:31:43

OUT and INOUT parameters won't work using this API. See en.wikibooks.org/wiki/Java_Persistence/…

@James 2011-03-31 13:22:17

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

@Mircea Ion 2017-03-15 21:38:01

Which version of EclipseLink has the EntityManager.createNamedStoredProcedureQuery()?

@user3973283 2018-10-31 18:21:38

If using ANY JPA 2.1 provider ...

@Dmitry Chornyi 2011-03-01 10:31:52

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.

@Akvel 2012-07-10 04:51:56

Thanks! It's really work for me too (Tomcat+Oracle11g)

@David Mann 2013-04-09 21:01:47

Works for me on Oracle 11g, hibernate JPA provider.

@Abdullah Khan 2017-11-29 07:34:01

This got us out from an extremely huge trouble. We were using java6, oracle11g, Jboss6, Hibernate. Thanks @Chornyi.

@Pascal Thivent 2010-08-26 08:37:24

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

@user431514 2010-08-26 09:41:50

Hey thanks a lot for your suggestion and example, worked like a charm

@Mr_and_Mrs_D 2014-02-24 11:25:18

As stated in the answer below it is supported - you might want to edit

@Sean Patrick Floyd 2010-08-26 07:59:26

You need to pass the parameters to the stored procedure.

It should work like this:

    List result = em
      .createNativeQuery("call getEmployeeDetails(:employeeId,:companyId)")
      .setParameter("emplyoyeeId", 123L)
      .setParameter("companyId", 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.

@user431514 2010-08-26 08:07:30

I tried and getting this error message: java.sql.SQLException: Incorrect syntax near '@P0'.

@Vedran 2012-08-24 12:54:13

It should be "{call getEmployeeDetails(:employeeId,:companyId)}", for SQL server it has to have curly braces.

@Sean Patrick Floyd 2012-08-24 12:55:56

@Vedran true. I was only interested in the parameter setting part

@vinoth 2010-08-26 06:49:29

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

@user431514 2010-08-26 06:56:45

Thanks for your reply. So the sql for callable statement will be { ? = call getEmployeeDetails (?,?) } or need to specify all output parameters

@vinoth 2010-08-26 07:29:59

@user431514 2010-08-26 07:42:57

Thanks I will give this a try now

Related Questions

Sponsored Content

32 Answered Questions

[SOLVED] Create ArrayList from array

60 Answered Questions

[SOLVED] How do I read / convert an InputStream into a String in Java?

64 Answered Questions

[SOLVED] How do I generate random integers within a specific range in Java?

  • 2008-12-12 18:20:57
  • user42155
  • 3785968 View
  • 3203 Score
  • 64 Answer
  • Tags:   java random integer

31 Answered Questions

[SOLVED] When to use LinkedList over ArrayList in Java?

45 Answered Questions

[SOLVED] How do I convert a String to an int in Java?

79 Answered Questions

[SOLVED] Is Java "pass-by-reference" or "pass-by-value"?

26 Answered Questions

19 Answered Questions

[SOLVED] How do I call one constructor from another in Java?

  • 2008-11-12 20:10:19
  • ashokgelal
  • 758491 View
  • 2048 Score
  • 19 Answer
  • Tags:   java constructor

33 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

54 Answered Questions

[SOLVED] Creating a memory leak with Java

Sponsored Content