„Hibernate may not be the best solution for data-centric applications that only use stored-procedures to implement the business logic in the database, it is most useful with object-oriented domain models and business logic in the Java-based middle-tier

Call Database Stored Procedures using POJO

Author: Marius Gligor

Abstract:

Today almost all Java database applications are designed using the JPA (Java Persistence API) or one of the existing ORM frameworks like Hibernate, TopLink, etc. However on the Hibernate Reference Manual preface we are advised that Hibernate may not be the best solution when we have to work with stored procedures.

“Hibernate may not be the best solution for data-centric applications that only use stored-procedures to implement the business logic in the database, it is most useful with object-oriented domain models and business logic in the Java-based middle-tier.”

Presentation:

If you have to call stored procedures on your applications you have to use the standard JDBC API working with CallableStatement objects. But now you can use a more simple and efficient solution that use annotated POJO classes and a ProcedureManager instance. Designed from scratch the pojo-sp library is offering the best object oriented solution to use stored procedures on your applications by hiding the JDBC SQL programming artifacts. For each stored procedure or function call you have to design an annotated POJO class, a Java Bean class with annotations. The POJO class must be decorated with a @StoredProcedure annotation and you have to specify the name of the stored procedure or function that is mapped by your class. If the procedure or the function is from an Oracle package you have to specify also the name of the package: <package_name>.<procedure_name>

Next you have to specify if the entity you are calling is a procedure or a function. This attribute is by default true for stored procedures and must be set to false if the entity you are calling is a function. The difference between a procedure and a function is that the function always has a return value. Inside your POJO class you have to define the stored procedure parameters call and to decorate the fields with @StoredProcedureParameter annotation. You have to specify the index of the parameter starting with 1 for the first parameter. The names of the parameters are not important because the parameters are accessed by index not by names. If the entity is a function the first parameter (index = 1) is always the return value. The next attribute is the SQL type of the parameter. Here you have to be carefully because you are just did a mapping of a SQL type to a Java type and this mapping must match. The last parameter is the direction attribute for this parameter which can be IN, OUT or INOUT. For a function result parameter you must specify always OUT as a direction attribute.

Example for a function:

@StoredProcedure(name = "HELLO", procedure = false)

public class Hello {

@StoredProcedureParameter(index = 1, type = Types.VARCHAR, direction = Direction.OUT)

private String result;

@StoredProcedureParameter(index = 2, type = Types.VARCHAR, direction = Direction.IN)

private String name;

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public String getResult() {

return result;

}

public void setResult(String result) {

this.result = result;

}

The next step is to create a ProcedureManager instance, set the input parameters on your POJO class and finally call the stored procedure. The ProcedureManager instance is created using the ProcedureManagerFactory.createInstance() method using a JDBC Connection object as parameter or a class decorated with @JDBC annotation.

If you need to use transactions a ProcedureTransaction manager interface is available to use. Also all “checked” exceptions are converted to “unchecked” exceptions and it’s not mandatory to use a try catch block on your code.

Example:

@JDBC(driver = "oracle.jdbc.OracleDriver", url = "jdbc:oracle:thin:@127.0.0.1:1521:XE", username = "HR", password = "hr")

public class Main {

public void test() {

ProcedureManager pm = ProcedureManagerFactory.createInstance(this.getClass());

try {

pm.getTransaction().begin();

// BALANCE

Balance bal = new Balance();

bal.setPrice(BigDecimal.valueOf(23.45f));

bal.setQuantity(BigDecimal.valueOf(123));

pm.call(bal);

System.out.println(bal.getVal());

// HELLO

Hello hello = new Hello();

hello.setName("Marius");

pm.call(hello);

System.out.println(hello.getResult());

pm.getTransaction().commit();

} catch (Exception e) {

pm.getTransaction().rollback();

} finally {

pm.close();

}

The sample code is designed to use an Oracle XE database connection and two simple stored procedures HELLO and BALANCE

CREATE OR REPLACE

FUNCTION HELLO (NAME IN VARCHAR2) RETURN VARCHAR2 AS

BEGIN

RETURN 'HELLO ' || NAME;

END HELLO;

CREATE OR REPLACE

PROCEDURE BALANCE (PARAM1 IN NUMBER, PARAM2 IN NUMBER, PARAM3 OUT NUMBER) AS

BEGIN

PARAM3 := PARAM1 * PARAM2;

END BALANCE;

The pojo-sp library is distributed under the GNU GENERAL PUBLIC LICENSE. You are welcome to send any questions, improvements ideas, and impressions to the author.