Callable Statement - Calling Stored Procedures

Callable statements are used to execute stored procedures from Java application. Calling stored procedure is another simple thing that sounds tough. Me and My friend Gaurv (A Great Technical Person) were really confused with Callable statement in our initial days as there were no easy-to-go source was there.So lets come to the point. CallableStatement is another inteface extends from Statements just like PreparedStatement. The way of using Callable statement is same as Prepared statement, the only difference is,instead of query string, we define SP (Stored Procedure) calling syntax.

CallableStatement cs = conn.prepareCall("{call spMySpName(?, ?, ?)}");

As you can see, except call spMySPName, everything is similar to prepared statement.

Something you might know about SP

• IN param are passed to SP as input.
• OUT param is returned by SP as output parameter.
• OUT param is usually kept as last param in order.
• INOUT param behave as both In and OUT params
• We need to register the OUT param type before calling SP.

We will look at following scenarios

1. Executing SP with no OUT param and four
2. Executing SP with OUT param and one param
3. Executing SP returning Result Set

Case 1:Four In Param and No Out Param ***************************************************************

import java.sql.*;
class AddEmployee{
public static void main (String args[]) throws SQLException{
Connection conn = null;
CallableStatement cs = null;
try {
Class.forName ("oracle.jdbc.driver.OracleDriver");
String conStr = "jdbc:oracle:thin:@localhost:1521:xe";
conn = DriverManager.getConnection(conStr, "scott","tiger");
cs = conn.prepareCall("{call spAddEmployee(?,?,?,?)}");
cs.setInt(1, 1001);
cs.setString(2, "Mohit");
cs.setFloat(3, 25000.0f);
cs.setString(4, "Noida");
cs.executeUpdate();
}
catch (ClassNotFoundException e) {
e.printStackTrace();
}
catch(SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
}
finally {
conn.close();
ps.close();
}
}
}

***************************************************************

You can see from the code, callable statement are 90% similar to prepared statement. The only difference is in syntax. before and after that, everything is same. We assume that we have a SP named spAddEmployee which take four params and add a new employee in db. we just need to pass IN params and execute SP by calling executeUpdate(). This will call the SP and your task will be done...Amused, how simple it was.

Case 2: SP with one In and OUT Param

Now we will deal with out param. Suppose we need to call a procedure spGetSalary which take empCode as In param and returns salary of the employee in OUT param.

***************************************************************
import java.sql.*;
class SpGetSalary {
public static void main (String args[]) throws SQLException
{
Connection conn = null;
CallableStatement cs = null;
try {
Class.forName ("oracle.jdbc.driver.OracleDriver");
String conStr = "jdbc:oracle:thin:@localhost:1521:xe";
conn = DriverManager.getConnection(conStr, "scott","tiger");
cs = conn.prepareCall("{call spGetSalary(?,?)}");
cs.setInt(1, 1001);
cs.registerOutParameter(2, Types.FLOAT);
cs.executeQuery();
float empSalary = cs.getFloat(2);
System.out.println("Employee Salary : " + empSalary);
}
catch (ClassNotFoundException e) {
e.printStackTrace();
}
catch(SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
}
finally {
conn.close();
ps.close();
}
}
}
***************************************************************

Here we are registering the type of Out param we are expecting. Type is an enumeration which define most SQL types for mapping between java and SQL. After executing callable statement, we are getting the Out param value using getFloat() method. we need to specify the out param index to get the value.

Case 3: SP Returning Result Set

Before we proceed, I just wanna share this with you,Once I need to use this in my project and I spent one entire day on finding this and got no success. Ultimately I need to ask for help from a DB+Java Geek in my company. Well I don't want you to jump in same situation so I am intentionally providing this case.

Suppose we need to call a SP which returns all the employee in the company (must be using some query like select * from employee). This SP take no argument as there is no in out params. so let conquer it,

***************************************************************
import java.sql.*;
class SpGetAllEmp {
public static void main (String args[]) throws SQLException
{
Connection conn = null;
CallableStatement cs = null;
try {
Class.forName ("oracle.jdbc.driver.OracleDriver");
String conStr = "jdbc:oracle:thin:@localhost:1521:xe";
conn = DriverManager.getConnection(conStr, "scott","tiger");
cs = conn.prepareCall("{call spGetAllEmp}");
ResultSet rset = cs.executeQuery();
while (rset.next())
{
System.out.println (rset.getString(1)); // empName
System.out.println (rset.getInt(2)); // empCode
System.out.println (rset.getInt(3)); // empSalary
System.out.println (rset.getString(4)); // empAddress
}
}
catch (ClassNotFoundException e) {
e.printStackTrace();
}
catch(SQLException ex) {
System.err.println("SQLException: " + ex.getMessage());
}
finally {
conn.close();
ps.close();
}
}
}

***************************************************************

Just look at text in green. Thats it. Just take the result set returned by execute query method and process it as you usually do with statement or prepared statements.

Well, I think we have discuss a lot about SP and how to call them from Java. Even if you feel any difficulty, feel free to comment

Thanks,
The Interpreter