Easy Java Persistence and Stored Procedures (MySql and Oracle examples)

NOTE: The following examples use the class definitions: Customer.java, Order.java, Support.java.

EJP even makes working with stored procedures and functions easy to handle. Simply use the JDBC stored procedure syntax:

       {?= call procedureName [(?, ?, ?, ...)]}
       {call procedureName [(?, ?, ?, ...)]}

and call ejp.Database’s storedProcedure with one or more of InParameter, OutParameter, and InOutParameter to pass data to and from the procedure.

storedprocedure() returns an ejp.Result that may have one or more results and/or update counts that can be accessed with getMoreResults(), isUpdateCount(), getUpdateCount(). When done, you can access the out parameters by retrieving the CallableStatement with getStatement(). In the case of databases that only use out parameters (Oracle) ejp.Result will not have any result sets in the result, but the result will provide access to the CallableStatement used for the stored procedure, so that the out parameters can be accessed.

The following stored procedure examples demonstrate everything you’ll need to access stored procedures with any database that supports them:

MySql Stored Procedure:

CREATE PROCEDURE getCustomerOrdersAndSupport(in id varchar(20), out total decimal(5,2))
BEGIN
    select sum(quantity * price) into total from orders where customer_id = id;
    select * from orders where customer_id = id;
    select * from support where customer_id = id;
END

The following is how you access the MySql stored procedure:

Result result = db.storedProcedure("{call getCustomerOrdersAndSupport(?,?)}", 
                                   new InParameter(1, "deisenhower"),
                                   new OutParameter(2, Types.DOUBLE, 2));

And then you can process the results and out parameters with:

// Map orders from the orders cursor
for (Order order : (Result)result.getResultSetWithClass(Order.class))
  System.out.println(order);
            
result.getMoreResults();

// Map support from the support cursor
for (Support s : (Result)result.getResultSetWithClass(Support.class))
  System.out.println(s);

System.out.println("Order total: " + ((CallableStatement)result.getStatement()).getDouble(2));

See the source for this example: StoredProcedure.java

MySql and Most databases return result cursors in the result set returned. You can then use getMoreResults(), isUpdateCount(), getUpdateCount() to access them. Oracle is a little different, as it passes result cursors as out parameters, so the following is how you would handle this case:

Oracle Procedure:

create or replace procedure getCustomerOrdersAndSupport
    (id IN varchar, total OUT decimal, orders_cursor OUT SYS_REFCURSOR, support_cursor OUT SYS_REFCURSOR) is
begin
    select sum(quantity * price) into total from orders where customer_id = id;
    OPEN orders_cursor FOR select * from orders where customer_id = id;
    OPEN support_cursor FOR select * from support where customer_id = id;
end getCustomerOrdersAndSupport;

NOTE: This example requires EJP 3.6.5, which was just released with a small change to the way ejp.Result handles next and previous, as Oracle returns a forward only resultSet.

You can access the Oracle stored procedure with:

Result result = db.storedProcedure("{call getCustomerOrdersAndSupport(?,?,?,?)}", 
                                   new InParameter(1, "deisenhower"),
                                   new OutParameter(2, Types.DOUBLE, 2),
                                   new OutParameter(3, OracleTypes.CURSOR),
                                   new OutParameter(4, OracleTypes.CURSOR));

And then you can process the out parameters with:

/*
 * Map orders from the orders cursor.
 * 
 * Oracle returns cursors as out parameters, so we have to handle the out parameters in the following way.
 */
Result orders = new Result(db, (ResultSet)((CallableStatement)result.getStatement()).getObject(3), Order.class);
            
for (Order order : orders)
  System.out.println(order);

// Map support from the support cursor
Result support = new Result(db, (ResultSet)((CallableStatement)result.getStatement()).getObject(4), Support.class);

for (Support s : support)
  System.out.println(s);

System.out.println("Order total: " + ((CallableStatement)result.getStatement()).getDouble(2));

See the source for this example: StoredProcedureOracle.java

Notice the difference? Instead of the ejp.Result having multiple result sets. We have to get the result sets as out parameters. Not a big deal, we can simply create an instance of ejp.Result and pass the java.sql.ResultSet, along with the class for mapping information, and then process the result in the same manner as the MySql example.

That’s it. With this functionality you can access your stored procedures and get whatever values and/or cursors you require.

About these ads