Get ResultSet By Calling StoredProcedure Using CallableStatement

In this post we will learn how map number of rows return by calling stored procedure in JDBC Result set.

Project structure in eclipse:

SQL Query to create employee_table in MySQL (jdbcdb schema)
(Note:Make sure you created employee_table and Stored procedure(getEmployeeById) in jdbcdb database schema)

CREATE TABLE `employee_table` (
  `employee_id` int(11) NOT NULL auto_increment,
  `employee_name` varchar(60) NOT NULL,
  `email` varchar(45) NOT NULL,
  `salary` double default NULL,
  `date_of_joining` datetime default NULL,
  `bonus` decimal(10,0) default NULL,
  PRIMARY KEY  (`employee_id`)
) ;

DROP PROCEDURE IF EXISTS getEmployeeById;
CREATE PROCEDURE `getEmployeeById`(IN empId INT)
BEGIN
	SELECT *FROM employee_table WHERE employee_id =empId;
END;

CREATE TABLE `employee_table` (

`employee_id` int(11) NOT NULL auto_increment,

`employee_name` varchar(60) NOT NULL,

`email` varchar(45) NOT NULL,

`salary` double default NULL,

`date_of_joining` datetime default NULL,

`bonus` decimal(10,0) default NULL,

PRIMARY KEY (`employee_id`)

) ;

DROP PROCEDURE IF EXISTS getEmployeeById;

CREATE PROCEDURE `getEmployeeById`(IN empId INT)

BEGIN

SELECT *FROM employee_table WHERE employee_id =empId;

END;

Insert some records in employee_table as below

DBUtil.java class which is responsible to connect with MySQL database.
this util class uses getConnection() method of java.sql.DriverManager class
public static Connection getConnection(String url,String user, String password) throws SQLException
This method takes three parameters database URL,username and password.
Here database username =”root”,password = “root” what password had supplied during MYSQL database installation time and finally database URL =“jdbc:mysql://localhost:3306/jdbcdb” Where jdbc is the API, mysql is the database, localhost is the server name on which mysql database server is running, we may also use IP address instead machine name, 3306 is the port number and jdbcdb(Make you you have created this schema in MySQL database) is the database name. You may use any database name, in that case, You need to replace the jdbcdb with your database name.

package com.infotech.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class DBUtil {
	private static final String DB_DRIVER_CLASS="com.mysql.jdbc.Driver";
	private static final String DB_USERNAME="root";
	private static final String DB_PASSWORD="root";
	private static final String DB_URL ="jdbc:mysql://localhost:3306/jdbcdb";
	
	private static Connection connection = null;
	static{
		try {
			Class.forName(DB_DRIVER_CLASS);
			connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);
		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
		}
	}
	
	public static Connection getConnection(){
		return connection;
	}
}

package com.infotech.util;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.SQLException;

public class DBUtil {

private static final String DB_DRIVER_CLASS="com.mysql.jdbc.Driver";

private static final String DB_USERNAME="root";

private static final String DB_PASSWORD="root";

private static final String DB_URL ="jdbc:mysql://localhost:3306/jdbcdb";

private static Connection connection = null;

static{

try {

Class.forName(DB_DRIVER_CLASS);

connection = DriverManager.getConnection(DB_URL, DB_USERNAME, DB_PASSWORD);

} catch (ClassNotFoundException | SQLException e) {

e.printStackTrace();

}

public static Connection getConnection(){

return connection;

}

Now let’s run our ClientTest.java class to read an employee record from stored proc and map into jdbc ResultSet.

package com.infotech.client;

import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.util.Scanner;

import com.infotech.util.DBUtil;

public class ClientTest {

	public static void main(String[] args) {
		String SQL = "CALL getEmployeeById(?)";
		try(Connection connection = DBUtil.getConnection();CallableStatement cs = 
				connection.prepareCall(SQL);Scanner scanner = new Scanner(System.in)) {
			System.out.println("Enter Employee ID::");
			int empId = scanner.nextInt();
			
			cs.setInt(1, empId);
			
			ResultSet rs = cs.executeQuery();
			while (rs.next()) {
				int empId2 = rs.getInt("employee_id");
				String eName = rs.getString("employee_name");
				String email = rs.getString("email");
				Double salary = rs.getDouble("salary");
				BigDecimal bonus = rs.getBigDecimal("bonus");

				System.out.println(empId2 + "\t" + eName + "\t" + salary + "\t" + email + "\t" + bonus);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

}

package com.infotech.client;

import java.math.BigDecimal;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.ResultSet;

import java.util.Scanner;

import com.infotech.util.DBUtil;

public class ClientTest {

public static void main(String[] args) {

String SQL = "CALL getEmployeeById(?)";

try(Connection connection = DBUtil.getConnection();CallableStatement cs =

connection.prepareCall(SQL);Scanner scanner = new Scanner(System.in)) {

System.out.println("Enter Employee ID::");

int empId = scanner.nextInt();

cs.setInt(1, empId);

ResultSet rs = cs.executeQuery();

while (rs.next()) {

int empId2 = rs.getInt("employee_id");

String eName = rs.getString("employee_name");

String email = rs.getString("email");

Double salary = rs.getDouble("salary");

BigDecimal bonus = rs.getBigDecimal("bonus");

System.out.println(empId2 + "\t" + eName + "\t" + salary + "\t" + email + "\t" + bonus);

}

} catch (Exception e) {

e.printStackTrace();

}

if you run this program it will ask you to provide employee Id.

Here i have provided employee as Id 2 so stored procedure fetching employee whose id is 2 and same is mapping with JDBC ResultSet

Enter Employee ID::2
2	Sean	80000.0	sean.cs2@gamil.comDate of joing:null		400

1 2	Enter Employee ID::2 2 Sean 80000.0 sean.cs2@gamil.comDate of joing:null 400

That’s all for the topic Get ResultSet By Calling StoredProcedure Using CallableStatement

You May Also Like:

Calling StoredProcedure Using CallableStatement
Calling database custom Function Using CallableStatement
JDBC batch update with Statement
JDBC batch update with PreparedStatement
JDBC batch update with CallableStatement
How to retrieve data from JDBC resultset in java
How to update a Row in a Database Table Using an updatable ResultSet
How to insert a Row in a Database Table Using an updatable ResultSet
JDBC ResultSet navigation methods example in java

If you have any feedback or suggestion please feel free to drop in below comment box.

Leave a Reply Cancel reply