How to call a stored procedure and map resultset to entity with Derby and EclipseLink

Assume you have a stored procedure in your Derby/JavaDB instance with following signature:

APP.PICK_AIRLINES(AIRLINE CHAR(2))

This stored procedure returns a resultset that you want to map with your Entity class, say, Airline.

Firstly I should let you know that, till date, Apache Derby [my version is 10.5] does not support Named Parameter in Callable Statements.

So, we cannot simply use @NamedStoredProcedureQuery Annotation with @StoredProcedureParameter , rather we need to use StoredProcedureCall method.

Here is the sample:

StoredProcedureCall storedProcedureCall = new StoredProcedureCall();
storedProcedureCall.setProcedureName("APP.PICK_AIRLINES");
storedProcedureCall.addUnamedArgumentValue("US");
ReadAllQuery readAllQuery = new ReadAllQuery(Airline.class,
storedProcedureCall);
EntityManagerFactory entityManagerFactory = Persistence
.createEntityManagerFactory("aa");
@SuppressWarnings("unchecked")
List airLines = (List) getSession().executeQuery(readAllQuery);
for (Iterator iterator = airLines.iterator(); iterator
.hasNext();) {
Airline airline = (Airline) iterator.next();
//Now you have your Airline Entity
}


 

Points to Note:

1.        “UnamedArgumentValue” to set the parameter value at line 3 as Derby/JavaDB  does not support Named Parameter in Callable Statements

2.       At line 4 We are passing the Entity Type we are expecting to get populated.

This document could also be helpful if you are getting error message like:

1.   Internal Exception: java.sql.SQLSyntaxErrorException: Syntax error: Encountered “=” at line 1, column

Advertisement