Calling database functions and stored procedures from Java
In this short article I describe how to easily call functions and stored procedures defined in a database package from Java code, using plain jdbc with Hibernate. I am leaving it more for personal reference. It is not interesting to read, unless you want to do the exact same thing.
Introduction
On one project, I was not allowed to use the usual sql statements on some of the tables in an Oracle database. Instead, I had to call the database functions and stored procedures they defined in their database packages.
A typical declaration of such a package looks like this:
CREATE OR REPLACE PACKAGE INTEGRATED_STUFF.TEST_PKG AS
TYPE rc_Query_Recs IS REF CURSOR;
PROCEDURE Select_TestData(csData OUT rc_Query_Recs,
p_title IN TEST_TABLE.TITLE%TYPE);
FUNCTION Fn_Insert_TestRecord(p_title IN TEST_TABLE.TITLE%TYPE,
p_desc IN TEST_TABLE.DESCRIPTION%TYPE,
p_userid IN VARCHAR2) RETURN VARCHAR2;
END TEST_PKG;
After that you would declare the package body with the actual implementation of the functions and procedures, but that is not what this article is about.
Calling a database function
In Hibernate, the database function in the package would be called like this:
final String title = "Test record";
final String description = "The description of the test record.";
final String userid = "admin";
Work work = new Work() {
public void execute(Connection connection) throws SQLException {
String query = "{? = call INTEGRATED_STUFF.TEST_PKG.Fn_Insert_TestRecord(?, ?, ?)}";
CallableStatement statement = connection.prepareCall(query);
statement.registerOutParameter(1,java.sql.Types.VARCHAR);
statement.setString(2, title );
statement.setString(3, description);
statement.setString(4, userid);
statement.execute();
String result = statement.getString(1);
log.info("Result of insert function: " + result );
statement.close();
}
};
hibernateSession.doWork(work);
The org.hibernate.jdbc.Work class is part of the Hibernate framework and lets you easily execute native sql.
The only special thing in the above example is the binding of the out parameter.
Notice how the out parameter is filled in after the statement execution.
Calling a stored procedure
Calling a procedure is very similar:
final String title = "Test record";
final List titles = new ArrayList();
Work work = new Work() {
public void execute(Connection connection) throws SQLException {
String query = "call PACKAGE INTEGRATED_STUFF.TEST_PKG.Select_TestData(?,?)";
CallableStatement statement = connection.prepareCall(query);
statement.registerOutParameter(1, OracleTypes.CURSOR);
statement.setString(2, title);
statement.execute();
ResultSet cursor = (ResultSet) statement.getObject(1);
while (cursor.next()) {
titles.add(cursor.getString(1));
}
statement.close();
}
};
hibernateSession.doWork(work);
Again, we bind the out parameter, and fetch its contents after statement execution. This time, its position is just among the other parameters in the query.
Notice that we had to register a very specific Oracle datatype, coming with the Oracle jdbc driver: OracleTypes.CURSOR.
In this blog, Steffen Luypaert gives tutorials on how to integrate Java frameworks and third-party APIs with each other.
Hello,
Thanks for the very usefull example. I am having a problem however reassigning a final value in the execute method of work. Typically java does not allow you reassign final value (it will give compilation error) so please how are you able to achieve this.
Thanks
Extend Work, give the subclass a property you assign the value to.
Assign the Work to a variable, do the work and then get the value from the Work object.
Was this answer helpful?
LikeDislikeThanks that is what i ended up doing. But was not sure from your example if it would be possible without implementing Work.
Was this answer helpful?
LikeDislikeI use java jdbc api getProcedures() to list all the stored procedures in the database.But how to identify whether a stored procedure is packaged stored procedure or not ? (note: though dot separtes the stored procedure name from a package,a stored procedure itself can have dot in its name )Please help asap!
Was this answer helpful?
LikeDislike