Monday, April 18, 2011

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 ;

6 comments:

  1. Not working your example.

    1) First result set is giving null.
    2)2nd & 3rd Result set is giving same value.

    ReplyDelete
  2. Solution Would be :

    use this "callStmt.getResultSet();" line insteadof callStmt.executeQuery();
    then only your program will work.
    Keep smile.

    ReplyDelete
  3. And for the 1 proble out parameter user_name must not your table name so change uesr_name to username to get null value.

    ReplyDelete
  4. And for the 1 proble out parameter user_name must not your table colum name so change your out parametr name user_name to username.

    ReplyDelete
  5. Rest of the example is very good , I appretiate you.
    Thanks.

    ReplyDelete
  6. http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/ad/tjvjdmlt.htm

    rs = cstmt.getResultSet(); will fetch the next one

    ReplyDelete

Java 1.7 New Features Over 1.6

Automatic Resource Management Description: A proposal to support scoping of resource usage in a block with automatic resource cleanup. T...