Retrieving multiple result sets from a stored procedure in a JDBC application

Hello,

Retrieving multiple result sets and OUT Parameters by passing IN Parameter from a stored procedure in a JDBC application.

Connection File:

package com.jit;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
/**
 * @author Vinay Guntaka
 * Retrieving multiple result sets from a stored procedure in a JDBC application
 * All Connections are Configured in this Class
 */
public class ConnectionUtil {
  

    public static Connection closeConnection(Connection conn){
        try {
            if(conn != null){
                conn.close();
                conn = null;
            }
        } catch (SQLException se) {
            System.out.println(se.getMessage());
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
        return conn;
    }

    public static CallableStatement closeCallableStatement(CallableStatement clst){
        try{
            if(clst !=null){
                clst.close();
                clst = null;
            }
            }catch (SQLException e) {
                System.err.println(e.getMessage());
            }
            return clst;
        }
      
  

    public static ResultSet closeResultSet(ResultSet resultSet){
        try {
            if(resultSet != null){
                resultSet.close();
                resultSet = null;
            }
        } catch (SQLException se) {
            System.out.println(se.getMessage());
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }

        return resultSet;
    }

  

    public static Connection getLocalConnection() {
        Connection conn = null;
        try {
          
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            String url = "jdbc:mysql://localhost:3306/sample";
            conn = DriverManager.getConnection(url, "root", "root");

            if (conn != null) {
                System.out.println("Successfully Obtained Connection From ConnectionPool");
            }
            return conn;
        } catch (SQLException se) {
            System.out.println(se.getMessage());
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
        return conn;
    }
}


Main Program To Execute Procedure:

package com.jit;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Types;
/**
 * @author Vinay Guntaka
 * We are Fetching the Results from MySQL Procedure by passing
 * one IN Param and fetching One OUT Param with Multiple Result sets.
 */
public class CallProcedure {
    public static void main(String[] args) {
        Connection conn =null;
        CallableStatement callStmt = null;
        ResultSet rs =null;
      
        try {
            conn = ConnectionUtil.getLocalConnection();
            callStmt = conn.prepareCall("call proc_getData(?,?)");
            //Passing param to Execute First Query
            callStmt.setInt(1, 22);
            //Registering the Out Parameter
            callStmt.registerOutParameter(2,Types.VARCHAR);
            //Fetching the First RS of OUT Param
            callStmt.execute();
            String outParam = callStmt.getString(2);           ; // OUT parameter
            System.out.println("First Result Set Out Param:-"+outParam);
            //Fetching the Second Result Set from Procedure
            rs = callStmt.executeQuery();
            while(rs.next()){
                System.out.println("Second Query Result Set");
                System.out.println(rs.getString(1));
                System.out.println(rs.getString(2));
                System.out.println(rs.getString(3));
                System.out.println(rs.getString(4));
                System.out.println(rs.getString(5));
            }
            //Checking is there any more result sets
            if(callStmt.getMoreResults())
            {
                System.out.println("One More Result Set is there");
                //Fetching the Thrid Result Set from the Procedure
                rs = callStmt.executeQuery();
                while(rs.next()){
                    System.out.println("Thrid Query Result Set");
                    System.out.println(rs.getString(1));
                    System.out.println(rs.getString(2));
                    System.out.println(rs.getString(3));
                    System.out.println(rs.getString(4));
                    System.out.println(rs.getString(5));
                }
            }else
            {
                System.out.println("No More Results Sets");
            }
        } catch (Exception e) {
            System.err.println(e.getMessage());
        }finally
        {  
            //Closing all connections
            ConnectionUtil.closeConnection(conn);
            ConnectionUtil.closeCallableStatement(callStmt);
            ConnectionUtil.closeResultSet(rs);
        }
      
    }
}

Here is the procedure:

DELIMITER $$

DROP PROCEDURE IF EXISTS `sample`.`proc_getData`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_getData`(in user_id int,out user_name varchar(64))
BEGIN
    select user_name from users where id=user_id into user_name ;
    select * from employee;
    select * from users;
   
    END$$

DELIMITER ;