JDBC - CallableStatement + ResultSet example




Technologies used:   JDK 1.8.0_121 | MySQL 5.7.12 | Eclipse Mars.2 (4.5.2)

In our previous post, we have learned how to call a stored procedure from a java program using the JDBC API.

In this post, we will show you how to get single ResultSet and multiple ResultSet from a CallableStatement object.

 

CallableStatement + Single ResultSet example

Consider the following MySQL stored procedure.

DELIMITER $$
CREATE PROCEDURE PRODCUT_SINGLE_RS()
BEGIN
	select ID,NAME,PRICE from product;
END$$
DELIMITER ;

 

The following program demonstrates how to call  the PRODCUT_SINGLE_RS stored procedure and generate a single result set.

CallableStmtResultSetExample.java

package com.boraji.tutorial.jdbc;

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

/**
 * @author imssbora
 */
public class CallableStmtResultSetExample {
   public static void main(String[] args) {
      String jdbcUrl = "jdbc:mysql://localhost:3306/BORAJI";
      String username = "root";
      String password = "admin";
      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 MySQL stored procedure having multiple select statements.

DELIMITER $$
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$$
DELIMITER ;

 

The following program demonstrates how to call the PRODUCT_MULTI_RS stored procedure and get multiple result set.

CallableStmtMultResultSetExample.java

package com.boraji.tutorial.jdbc;

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

/**
 * @author imssbora
 */
public class CallableStmtMultResultSetExample {
   public static void main(String[] args) {
      String jdbcUrl = "jdbc:mysql://localhost:3306/BORAJI";
      String username = "root";
      String password = "admin";
      String sql = "{call PRODUCT_MULTI_RS}";

      try (Connection conn = DriverManager.getConnection(jdbcUrl, username, password); 
            CallableStatement stmt = conn.prepareCall(sql);) {

         boolean hasRs = stmt.execute();

         System.out.println();
         // Get Product Names
         if (hasRs) {
            try (ResultSet rs = stmt.getResultSet()) {
               while (rs.next()) {
                  System.out.println("NAME = " + rs.getString(1));
               }
            }
         }

         // Get Total Price
         if (stmt.getMoreResults()) {
            try (ResultSet rs = stmt.getResultSet()) {
               if (rs.next()) {
                  System.out.println("Total Price = " + rs.getDouble(1));
               }
            }
         }

         // Get Max/Min Price
         if (stmt.getMoreResults()) {
            try (ResultSet rs = stmt.getResultSet()) {
               if (rs.next()) {
                  System.out.println("Max Price = " + rs.getDouble(1));
                  System.out.println("Min Price = " + rs.getDouble(2));
               }
            }
         }

      } 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