How to call Oracle Stored Procedure in JPA 2.0 with Eclipse link 2.5 +

Hey everyone, hope you all are doing great ūüôā It’s been a long time (actually couple of months) since I wrote any blog , sorry about that.

So, on brighter side I’ve been lately working on Eclipse link ¬†2.6.3 (previously 2.3) and JPA 2.0. It’s been challenging but yes having lot of fun working on them.Coming to the point, like every enterprise application I came across consisted of calling stored procedures or functions to execute certain heavy / complex queries to perform CRUD operations.

Now comes the real challenge , JPA 2.0 umm doesn’t really support calling procedures in traditional way, somehow I’ve managed to call procedures which return nothing but execute some update/insert statement.But I couldn’t figure out how to call procedures which return some result by taking cursor as IN OUT parameter and several other types.

You can say to me .. Are you fucking stupid .? Eclipselink 2.5 ¬†above supports JPA 2.1 why don’t you use it ? Duh ? ¬†Okay I agree but unfortunately my IBM WebSphere 8.5.5.8 server doesn’t support ¬†JPA 2.1 ūüė¶ ¬†So this blog is for all the unfortunate dev’s like me who are stuck with JPA 2.0 and want to flex it !

1.Calling simple procedure :

CREATE OR REPLACE PROCEDURE update_comments ( 
   postId IN NUMBER, 
   comment IN VARCHAR2 ) 
IS
BEGIN
    UPDATE posts SETcomment=comment 
    WHERE post_id = postId;
END;

In your DAO class

@Repository
public class   PostsDAO {
@Transactional
public void updateComment(String comment, Integer postId) {

//create a sql statement to execute procedure..
String sql = "{call update_comments (?1, ?2)}";

try {
//call entity manager native query by passing the sql block
Query query = entityManger.createNativeQuery(sql)
.setParameter(1, postId)
.setParameter(2, comment);


//execute the query..
query.executeUpdate();
} catch (Exception ex) {}
}
}

2.Calling procedure outputting a simple value (OUT parameter):

CREATE OR REPLACE PROCEDUREcount_comments ( 
   postId INNUMBER, 
   commentCount OUTNUMBER ) 
AS
BEGIN
    SELECTCOUNT(*) INTOcommentCount 
    FROMpost_comment 
    WHEREpost_id = postId;
END;
In your DAO class :
@Repository
public class   PostsDAO {
@Transactional
public void readCommentsCount(Integer postId) {

StoredProcedureCall storedProcedureCall = new StoredProcedureCall();
storedProcedureCall.setProcedureName("count_comments");
storedProcedureCall.addNamedArgument("postId"); // Add input argument name.
// Add output parameter name.
storedProcedureCall.addNamedOutputArgument("commentCount ");
DataReadQuery query = new DataReadQuery();
query.setCall(storedProcedureCall);
query.addArgument("postId"); // Add input argument names
List argumentValues = new ArrayList();
argumentValues.add(postId); // Add input argument values.
Session session = JpaHelper.getEntityManager((EntityManager)em.getDelegate()).getActiveSession();
List results = (List) session.executeQuery(query, argumentValues);
DatabaseRecord record = (DatabaseRecord) results.get(0);
// Get output parameter
String result = String.valueOf(record.get("commentCount"));
}
}

3.Calling procedure outputting cursor (IN OUT Cursor):

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;
In your DAO class :
@Repository
public class   PostsDAO {
@Transactional
public void readCommentsCount(Integer postId) {

StoredProcedureCall storedProcedureCall = new StoredProcedureCall();
storedProcedureCall.setProcedureName("count_comments");
storedProcedureCall.addNamedArgument("postId"); // Add input argument name.
// Add cursor output parameter name.
storedProcedureCall.useNamedCursorOutputAsResultSet(postComments);
storedProcedureCall.setIsCursorOutputProcedure(true);
DataReadQuery query = new DataReadQuery();
query.setCall(storedProcedureCall);
query.addArgument("postId"); // Add input argument names
List argumentValues = new ArrayList();
argumentValues.add(postId); // Add input argument values.
Session session = JpaHelper.getEntityManager((EntityManager)em.getDelegate()).getActiveSession();
List results = (List) session.executeQuery(query, argumentValues);

}
}
The change from above calling method is below two lines which indicate procedure has cursor as parameter to output some result.

storedProcedureCall.useNamedCursorOutputAsResultSet(postComments);
storedProcedureCall.setIsCursorOutputProcedure(true);

Hope these implementations help you out in calling stored procedures with JPA 2.0 .Special thanks to VLAD MIHALCEA for the proc examples.
Keep coding and keep cheering !! ūüôā
Advertisements