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”
and generate a resultset object.
(with single result set)
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”
and generate a resultset object.
(with Multiple result set)
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
0 comments