小开

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

小开

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.

小开

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

小开

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.

小开

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

小开

The following works for me:

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

小开

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

小开

Try this code:

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

小开

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

小开

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.

小开

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

小开

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.

小开

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

小开

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.

小开

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<Object[]> 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

小开

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

小开

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

小开

the simplest way is to use JpaRepository

1- Create a stored procedure
CREATE PROCEDURE dbo.getEmployeeDetails
(
@employeeId         int,
@companyId          int
)  AS
BEGIN
SELECT firstName,lastName,gender,address
FROM employee et
WHERE et.employeeId = @employeeId and et.companyId = @companyId
END




2- Create Entity
@Getter
@Setter
@ToString
@NoArgsConstructor
@AllArgsConstructor
@Entity
public class EmployeeDetails {
@Id
private String firstName;
private String lastName;
private String gender;
private String address;
}




3- Create Repository
public interface EmployeeDetailsRepository extends
JpaRepository<EmployeeDetails,String> {
@Query(value = "EXEC dbo.getEmployeeDetails @employeeId=:empId,
@companyId=:compId",nativeQuery =true)
List<EmployeeDetails> getEmployeeList(@Param("employeeId") Integer empId,
@Param("companyId") Integer compId);
}


4- create Controller
@CrossOrigin(origins = "*")
@RestController
@RequestMapping(value = "/api/employee")
public class EmployeeController {


@Autowired
private EmployeeDetailsRepository empRepo;


@GetMapping(value = "/details")
public ResponseEntity<List<EmployeeDetails>> getEmployeeDetails(@RequestParam
String empId, @RequestParam String compId) {
try {
List<EmployeeDetails> result = empRepo.getEmployeeList(
Integer.valueOf(empId),Integer.valueOf(compId));
return ResponseEntity.status(HttpStatus.OK).body(result);
}
catch (Exception ex)
{
return ResponseEntity.status(HttpStatus.EXPECTATION_FAILED).body(null);
}
}
}

you can now call http://localhost:8080/api/employee/details?empId=1&compId=25

小开

If you're not too attached to calling this particular procedure with JPA or JDBC, you could use jOOQ, a third party library that generates stubs for all of your stored procedures to simplify calling them, and making the calls type safe.

Calling procedures returning unspecified cursors

In your particular case, the procedure returns an untyped, undeclared cursor (it could return several cursors and interleaved update counts). So, you could call the procedure like this with jOOQ:

GetEmployeeDetails proc = new GetEmployeeDetails();
proc.setEmployeeId(1);
proc.setCompanyId(2);
proc.execute(configuration);


// Iterate over potentially multiple results
for (Result<?> result : proc.getResults()) {


// Print the first result set (your employee query)
System.out.println(result);


// Use your implicit knowledge of the content of the query
// Without type safety
for (Record record : result) {


// All tables / columns are also generated
System.out.println("First name: " + record.get(EMPLOYEE.FIRSTNAME));
System.out.println("Last name: " + record.get(EMPLOYEE.LASTNAME));
System.out.println("Gender: " + record.get(EMPLOYEE.GENDER));
System.out.println("Address: " + record.get(EMPLOYEE.ADDRESS));
}
}

Using an actual table valued function, instead

Personally, I don't really like that feature of a few RDBMS (including SQL Server, MySQL) of returning arbitrary untyped cursors. Why not just declare the result type? SQL Server has powerful table valued functions. E.g. just use this syntax here:

CREATE FUNCTION getEmployeeDetails (@employeeId int, @companyId int)
RETURNS TABLE
AS RETURN
SELECT
firstName,
lastName,
gender,
address
FROM employee et
WHERE et.employeeId = @employeeId
AND et.companyId = @companyId

Now, you have the full type information associated with this function in your catalog, and if you're still using jOOQ, that information will be available to the code generator, so you can call the function like this:

for (GetEmployeeDetailsRecord record : ctx.selectFrom(getEmployeeDetails(1, 2))) {
System.out.println("First name: " + record.getFirstName());
System.out.println("Last name: " + record.getLastName());
System.out.println("Gender: " + record.getGender());
System.out.println("Address: " + record.getAddress());
}

Disclaimer: I work for the company behind jOOQ

如何从 Java 和 JPA 调用存储过程