JDBC provides three statement interface which is used to send an SQL statement to the database server.
1. Statement
2. PreparedStatement which extends Statement
3. CallableStatement which extends PreparedStatement
Vendors of JDBC Driver provide classes that implement the above interfaces. Without a JDBC driver, you cannot create objects based on these interfaces. Database connection is required to to create statement object. This tutorial explains about CallableStatement objects and also how to call a DB2 stored procedure from a java application. Please go through my earlier post on
how to create stored procedure in db2. Stored procedure can be called using the SQL CALL statement in DB2. Now how to call the stored procedures located on the database server from your Java application?
Using CallableStatement interface in java:
CallableStatement object enable you to call and execute stored procedures stored on the database server from your Java application. Three types of JDBC parameters are there. They are IN, OUT, and INOUT
1. IN - parameters used for input. You can set values to IN parameters with the setXXX() methods.
2. OUT - result parameter used for output which returns output value of the stored procedure.
3. INOUT - parameter used for both input and output values parameters
A question mark (?) symbol serves as a placeholder for a parameter. The call to invoke the Stored procedure is written in an JDBC escape syntax that may take the followings forms
1. {call procedure_name[(?, ?, ...)]} - which accepts input parameters but no result parameter
2. {? = call procedure_name[(?, ?, ...)]} which returns a result parameter
3. {call procedure_name} - for no input / output parameters
A CallableStatement can return one or multiple ResultSet objects. Multiple ResultSet objects are handled using operations inherited from Statement.
cstmt.getMoreResults(); -used to point to the second or next result set.
Now let us see the steps to create a CallableStatement Object. Before creating it, let us create a stored procedure in DB2. In our example, I have created two Stored procedures in db2.
Ist Stored Procedure (SP) example : to return all records matching with a given salesman_id and date of sale
input - salesmanid, sales_date
output - result set (salesman_id, salesman, item_name , sales_date, sales_amt)
Stored Procedure for the above problem is given below
Stored Procedure I:
CREATE PROCEDURE ItemSalesBy (salesmanid varchar(5), dateofsale date) SPECIFIC sp10 DYNAMIC RESULT SETS 1
P1: BEGIN
DECLARE cursor1 CURSOR WITH RETURN FOR select salesman_id, (select c.name from salesman c where c.salesman_id=b.salesman_id) SalesMan, (select a.item_name from item_master a where a.item_code=b.item_code) ItemName, sales_date, sales_amt from salesmantxn b where b.salesman_id=salesmanid and b.sales_date=dateofsale;
OPEN cursor1;
END P1
@
We can create and call (execute) the above stored procedure using command line processor (CLP) and through java program.
To create stored procedure using CLP, store above stored procedure in a file . for example salesman.sql . Now run the following commands
db2 connect to test
db2 -td@ -vf salesman.sql
to call the above SP through CLP
db2 call ItemSalesBy('101','2012-02-25'), where 101 is the salesman id, 2012-02-25 is the date of sale
Now let us create and call (execute) the above stored procedure using java program
To create CallableStatement object, the following statements are used
CallableStatement cstmt = null;
cstmt = conn.prepareCall ("{ call ItemSalesBy(?,?)}"); // Callablestatement object to call the stored procedure
where ItemSalesBy is the SP name and two question mark (?,?) is used to pass input parametters. In our example, salesman_id , sales_date. The following java program drops the existing SP named ItemSalesBy and creates the same SP and executes with the given input parameters. The statement
ResultSet rs = cstmt.executeQuery(); returns the result set returned by the SP.
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Scanner;
public class CSPRS2 {
static Connection conn;
public static void main(String[] args) {
try {
//Class.forName("COM.ibm.db2.jdbc.app.DB2Driver");
Class.forName("com.ibm.db2.jcc.DB2Driver");
} catch (ClassNotFoundException e)
{
e.printStackTrace();
return;
}
try
{
conn = DriverManager.getConnection("jdbc:db2:test");
}
catch( Exception e )
{
e.printStackTrace();
System.exit(1);
}
// dropSP(); // to drop procedure if already exists
createSP(); // to create stored procedure (SP)
callSP("101", "2012-02-25"); // to execute SP
}
private static void dropSP() {
String str=null;
Statement stmt = null;
try
{
stmt = conn.createStatement();
str="drop procedure ItemSalesBy";
stmt.executeUpdate (str);
}
catch (SQLException e1)
{ e1.printStackTrace();}
}
private static void createSP() {
String strSP=null;
Statement stmt = null;
strSP="CREATE PROCEDURE ItemSalesBy (salesmanid varchar(5), dateofsale date) SPECIFIC sp10 DYNAMIC RESULT SETS 1 \n" +
"P1: BEGIN \n" +
"DECLARE cursor1 CURSOR WITH RETURN FOR select salesman_id, (select c.name from salesman c where c.salesman_id=b.salesman_id) SalesMan, (select a.item_name from item_master a where a.item_code=b.item_code) ItemName, sales_date, sales_amt from salesmantxn b where b.salesman_id=salesmanid and b.sales_date=dateofsale; \n" +
"OPEN cursor1; \n" +
"END P1 \n";
try
{
stmt = conn.createStatement();
stmt.executeUpdate (strSP);
System.out.println("Stored Procedure created successfully\n");
}
catch (SQLException e)
{
System.out.println("Error in creating SP: " + e.toString());
System.exit(1);
}
}
private static void callSP(String sid, String sdate)
{
CallableStatement cstmt = null;
try
{
cstmt = conn.prepareCall ("{ call ItemSalesBy(?,?)}"); // CallableStatement object to call the stored procedure
cstmt.setString(1,sid);
cstmt.setDate(2, java.sql.Date.valueOf(sdate));
ResultSet rs = cstmt.executeQuery();
System.out.println("SALESMAN_ID SALESMAN ITEMNAME SALES_DATE SALES_AMT\n");
System.out.println("----------- ---------------- ------------------- ---------- ------------\n");
if (rs != null) {
while (rs.next())
{
System.out.println(rs.getInt(1) + " " + rs.getString(2) + " " + rs.getString(3) + " " + rs.getString(4) +" "+rs.getString(5));
}
}
}
catch (SQLException e)
{
e.printStackTrace();
}
}
}
Output :
Stored Procedure II:
To calculate wages for a salesman on a particular date based on sales_amt. wages is calculated with the following formula
wages = total_sales_amt * 0.5 /100 + bonus ; and Rs. 100 bonus is added if the sales_amt >10000 and sales_amt<=200000, Rs. 200 bonus if sales_amt>200000 and sales_amt<=300000 , Rs. 300/- bonus if sales_amt>300000
IN Parameter : salesman_id, Sales_date
OUT parameter : wages
Stored Procedure for the above problem is given below
CREATE PROCEDURE wagesCalc(IN salesmanid varchar(5),IN SalesDate date , OUT wages double) LANGUAGE SQL
BEGIN
DECLARE bonus double;
DECLARE sumsales double;
DECLARE wages_temp double;
DECLARE cursor1 CURSOR FOR SELECT SUM(SALES_AMT), SUM(SALES_AMT)*0.5/100 FROM SALESMANTXN where SALESMAN_ID=salesmanid and SALES_DATE=SalesDate;
SET bonus= 0;
OPEN cursor1;
FETCH FROM cursor1 INTO sumsales, wages_temp;
IF (sumsales>300000) THEN
set bonus=300;
ELSEIF (sumsales>200000 and sumsales<=300000) THEN
set bonus=300;
ELSEIF (sumsales>100000 and sumsales<=200000) THEN
set bonus=100;
END IF;
CLOSE cursor1;
SET wages = wages_temp+ bonus;
END%
to create above SP using CLP , store above stored procedure in a file . eg. wages.sql . Now run the following commands
db2 connect to test
db2 -td% -vf wages.sql
to call the above SP through CLP
db2 call wagesCalc('101', '2012-02-25', ?), which returns wages for the saleman_id=101 and sales_date='2012-02-25'
Now let us see how to call (execute) the above stored procedure using java program
private static void callSP(String sid, String sdate)
{
Double wages=0.0;
CallableStatement cstmt = null;
try
{
cstmt = conn.prepareCall ("{ call wagesCalc(?,?,?)}"); // CallableStatement object to call the stored procedure
cstmt.setString(1,sid);
cstmt.setDate(2, java.sql.Date.valueOf(sdate));
cstmt.registerOutParameter(3, Types.DOUBLE);
cstmt.execute();
wages = cstmt.getDouble(3);
System.out.println("Sales Man Id = " + sid + " Wages= " + wages);
}
catch (SQLException e)
{
e.printStackTrace();
}
}
Output of the above program :
D:\as2\JF5>java CSPRS4
Sales Man Id = 101 Wages= 650.0
In the above program, registerOutParameter(3, Types.DOUBLE); Registers the OUT parameter in ordinal position parameterIndex to the JDBC type sqlType
The execute() returns boolean value. if it returns false means, first result is an update count or there is no result; true means, the first result is a ResultSet object
