03
Oct

working with result sets of a “Stored Procedure” in JDBC

Callablestatement + resultset example in JDBC

This examples shows how to call a stored procedure from a java program using the JDBC API.

Following example will demonstrate how to get single ResultSet and multiple ResultSet from a CallableStatement object.

CallableStateme + resultset we will use following stored procedure

CREATE PROCEDURE PRODCUT_SINGLE_RS()

BEGIN

select ID,NAME,PRICE from product;

END

The following program demonstrates how to call the stored procedure “PRODCUT_SINGLE_RS”
(with single result set)
and generate a resultset object.

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

/**

* @author SR

*/

public class CallableStmtResultSetExample {

public static void main(String[] args) {

String jdbcUrl = “jdbc:mysql://10.9.121.6:3306/DBName”;

String username = “username”;

String password = “pwd”;

String sql = “call PRODCUT_SINGLE_RS()”;

try (Connection conn = DriverManager.getConnection(jdbcUrl, username, password);

CallableStatement stmt = conn.prepareCall(sql);

ResultSet rs = stmt.executeQuery();) {

while (rs.next()) {

System.out.println(“ID = “ + rs.getInt(1) + “, NAME = “ + rs.getString(2) +

“, PRICE = “ + rs.getDouble(3));

}

} catch (SQLException e) {

e.printStackTrace();

}

}

}

Output:

ID = 1, NAME = Pencil, PRICE = 15.15

ID = 2, NAME = Pen, PRICE = 20.0

ID = 3, NAME = Color Box, PRICE = 12.45

#. CallableStatement + Multiple ResultSet example

Consider the following stored procedure having multiple select statements.

CREATE PROCEDURE PRODUCT_MULTI_RS()

BEGIN

select distinct name from product;

— Select total Price

select sum(price) from product;

— Select Max and Min price

select max(price), min(price) from product;

END

The following program demonstrates how to call the stored procedure “PRODCUT_SINGLE_RS”
(with Multiple result set)
and generate a resultset object.

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

/**

* @author SR

*/

public class CallableStmtResultSetExample {

public static void main(String[] args) {

String jdbcUrl = “jdbc:mysql://10.9.121.6:3306/DBName”;

String username = “username”;

String password = “pwd”;

String sql = “call PRODCUT_SINGLE_RS()”;

try (Connection conn = DriverManager.getConnection(jdbcUrl, username, password);

CallableStatement stmt = conn.prepareCall(sql);

ResultSet rs = stmt.executeQuery();) {

while (rs.next()) {

System.out.println(“ID = “ + rs.getInt(1) + “, NAME = “ + rs.getString(2) +

“, PRICE = “ + rs.getDouble(3));

}

} catch (SQLException e) {

e.printStackTrace();

}

}

}

Output:

NAME = Pencil

NAME = Pen

NAME = Color Box

Total Price = 47.6

Max Price = 20.0

Min Price = 12.45