13.1 Overview of JDBC Callable Statement
In earlier chapters we discussed how to execute the Queries on a database using Statement and Prepared Statements. In database we can write stored procedures and if we would want to execute stored procedure, JDBC provides a third type of object known as Callable Statement. In this chapter we will discuss the details of Callable Statement using examples.
Stored procedure is similar to a methods where we can put a bunch of logic and can fire SQLs with in it and can perform logic on it. Stored Procedures resides and compiled at a database level.
13.2 JDBC Callable Statement API
Callable Statement is an interface (child of PreparedStatement) and is available under java.sql package. To get the CallableStatement object , we need to use the Connection object and call “prepareCall(String )” method like below
CallableStatement stmt = conn.prepareCall(String signature);
It is always suggested to close the CallableStatement Object to release all the resources occupied by the statement object once it is done with its processing. To close the CallableStatement object, we can use “close()” method on statement object like below
CallableStatement stmt = conn.prepareCall(String signature);
stmt.close();
prepareCall() method takes one String and it is in the form of
{call <<stored-proc-name>>(??....)}
Where ? will be as many times as Input and Output params
Below are the most commonly used methods of the Statement Object.
- void close() throws SQLException - This method is used to close the connection object so that all the resources occupied by the statement object will be release immediately.
- void registerOutParameter(int parameterIndex, int sqlType) – This method is used to register the output parameter with the SQL data types. Basically we specify which index is OUT param in string signature that we pass in prepareCall() method.
- int executeUpdate (): As it name suggest, this method is used to fire SQL Queries which updates the database statement so to execute INSERT, DELETE, UPDATE queries, we should use this method. This method returns an int which is the number of rows affected by this execution like number of rows deleted/ updated or inserted.
- ResultSet executeQuery ():Usually this method is used to execute store procedure that returns resultsets similar to select statement.
- Connection getConnection() – getConnection() API can be used to get back the connection object which was used to create the statement Object.
- boolean isClosed()- isClosed() method can be used to verify if the Statement object is closed or not.
You might be wondering that if executeQuery returns a ResultSet that what is the use of out params. So the answer is sometimes we might not need to return the complete rows or resultset or we might need to output extra param in addition to ResultSet.
13.3 JDBC Callable Statement Examples
We will use the MySQL database .
a. Use the below sql statement to create a schema
Create schema JDBCTutorial;
b. Create table User with 4 columns using below SQL
CREATE TABLE `User` ( `id` INT NOT NULL AUTO_INCREMENT , `name` VARCHAR(45) NULL , `age` VARCHAR(45) NULL , `email` VARCHAR(45) NULL , PRIMARY KEY (`id`) );
c. Create one Stored which returns the record from user table based on name as an OUT params.
CREATE PROCEDURE `jdbctutorial`.`getUser` (IN uname Varchar(45),OUT out_age Varchar(45),OUT out_email Varchar(45))
BEGIN
select age,email into out_age,out_email from User where name=uname;
END
d. Create one Stored which will insert new user.
CREATE PROCEDURE `jdbctutorial`.`addUser` (IN uname VARCHAR(45), IN age VARCHAR(45), IN email VARCHAR(45) )
BEGIN
insert into USER(`name`,`age`,`email`) values(uname,age,email);
END
13.3.1 – Write and Example to insert the new Users using addUser stored procedure.
Solution –
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
public class CallableStmtExample {
public static void main(String args[])
{
try
{
Scanner scanner = new Scanner(System.in);
System.out.println("Enter name:");
String name = scanner.nextLine();
System.out.println("Enter age:");
String age = scanner.nextLine();
System.out.println("Enter email:");
String email = scanner.nextLine();
Connection conn= getConnection();
String sql = "{call addUser(?,?,?) }";
CallableStatement stmt = conn.prepareCall(sql);
stmt.setString(1, name);
stmt.setString(2, age);
stmt.setString(3, email);
stmt.executeUpdate();
stmt.close();
conn.close();
}
catch(Exception e)
{
e.printStackTrace();
}
}
private static Connection getConnection() {
Connection con = null;
String dbDriver = "com.mysql.jdbc.Driver";
String dbUsername = "root";
String dbPassword="password";
String dbHostname="localhost";
String dbPort="3306";
String schema="JdbcTutorial";
try {
String url = "jdbc:mysql://"+dbHostname+":"+dbPort+"/"+schema;
Class.forName(dbDriver);
con = DriverManager.getConnection(url, dbUsername, dbPassword);
}
catch (ClassNotFoundException ex1)
{
System.out.println("Failed to find driver class " + ex1.getMessage());
System.exit(1);
}
catch (SQLException ex2) {
System.out.println("Connection failed " + ex2.getMessage());
System.exit(2);
}
return con;
}
}
On running the above program, it will ask user to enter name, age and email. Refer below
13.3.2 – Write and Example to select the new Users using getUser stored procedure.
Solution –
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;
public class CallableStmtExample {
public static void main(String args[])
{
try
{
Scanner scanner = new Scanner(System.in);
System.out.println("Enter name:");
String name = scanner.nextLine();
Connection conn= getConnection();
String sql = "{call getUser(?,?,?) }";
CallableStatement stmt = conn.prepareCall(sql);
stmt.setString(1, name);
stmt.registerOutParameter(2,java.sql.Types.VARCHAR);
stmt.registerOutParameter(3,java.sql.Types.VARCHAR);
stmt.executeQuery();
String age = stmt.getString(2);
String email = stmt.getString(3);
System.out.println("Age="+age + " email =" + email);
stmt.close();
conn.close();
}
catch(Exception e)
{
e.printStackTrace();
}
}
private static Connection getConnection() {
Connection con = null;
String dbDriver = "com.mysql.jdbc.Driver";
String dbUsername = "root";
String dbPassword="password";
String dbHostname="localhost";
String dbPort="3306";
String schema="JdbcTutorial";
try {
String url = "jdbc:mysql://"+dbHostname+":"+dbPort+"/"+schema;
Class.forName(dbDriver);
con = DriverManager.getConnection(url, dbUsername, dbPassword);
catch (ClassNotFoundException ex1)
{
System.out.println("Failed to find driver class " + ex1.getMessage());
System.exit(1);
}
catch (SQLException ex2) {
System.out.println("Connection failed " + ex2.getMessage());
System.exit(2);
}
return con;
}
}
On Running the above program, it will ask user to enter name and will get back the age and email. Refer below
