How to Call a Stored Procedure by JDBC Java Class?

--
A Stored procedure can return result sets, you can use getResultSet method in the CallableStatement class to retrieve return result sets. When a procedure has return value for an OUT parameter, you must tell the JDBC driver what SQL type the value will be, with the registerOutParameter method. To call stored procedures, you invoke methods in the CallableStatement class. The basic steps are:

• Creating a CallableStatement object by calling the Connection.prepareCall method.
• Using the CallableStatement.setXXX methods to pass values to the input (IN) parameters.
• Using the CallableStatement.registerOutParameter method to indicate which parameters are output-only (OUT) parameters, or input and output (INOUT) parameters.
• Invoke one of the following methods to call the stored procedure:
  • CallableStatement.executeUpdate method, if the stored procedure does not return result sets.
  • CallableStatement.executeQuery method, if the stored procedure returns one result set.
  • CallableStatement.execute method, if the stored procedure returns multiple result sets.
• Calling the CallableStatement.getResultSet method to obtain the result set (which is in a ResultSet object), if the stored procedure returns one result set. But if the stored procedure returns result sets, retrieve the result sets by combining CallableStatement.getResultSet and CallableStatement.getMoreResults methods.
• Using the CallableStatement.getXXX methods to retrieve values from the OUT parameters or INOUT parameters.
• Calling the CallableStatement.close method to close the CallableStatement object when you have finished using that object.

This example demonstrates how to call stored procedures with IN and OUT parameters in JDBC.

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;

public class JDBCCallProcedure {

    private static final String DBURL = "jdbc:mysql://localhost:3306/mydb?" +
                    "user=usr&password=sql&useUnicode=true" +
                   "&characterEncoding=UTF-8";"
    private static final String DBDRIVER = "org.gjt.mm.mysql.Driver";
  
    static {
        try {
            Class.forName(DBDRIVER).newInstance();
        } catch (Exception e){
            e.printStackTrace();
        }
    }

    private static Connection getConnection() 
    {
        Connection connection = null;
        try {
            connection = DriverManager.getConnection(DBURL);
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        return connection;
    }

    public static void createEmployees()
    {
        Connection con = getConnection();
        Statement stmt =null;
        String createString;
        createString = "CREATE TABLE  `mydb`.`employees` ("+
         "`EmployeeID` int(10) unsigned NOT NULL default '0',"+
         "`Name` varchar(45) collate utf8_unicode_ci NOT NULL default '',"+
         "`Office` varchar(10) collate utf8_unicode_ci NOT NULL default '',"+
         "`CreateTime` timestamp NOT NULL default CURRENT_TIMESTAMP,"+
         "PRIMARY KEY  (`EmployeeID`)"+
         ") ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;";            
        try {
            stmt = con.createStatement();
            stmt.executeUpdate(createString);
        } catch(SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.err.println("SQLException: " + e.getMessage());
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    System.err.println("SQLException: " + e.getMessage());
                }
            }
        }
    }
    private static void dropEmployees()
    {
        Connection con = getConnection();
        Statement stmt =null;
        String createString;
        createString = "DROP TABLE IF EXISTS `mydb`.`employees`;";            
        try {
            stmt = con.createStatement();
            stmt.executeUpdate(createString);
        } catch(SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.err.println("SQLException: " + e.getMessage());
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    System.err.println("SQLException: " + e.getMessage());
                }
            }
        }
    }
    
    private static void insertEmployee() 
    {
        Connection con = getConnection();
        PreparedStatement ps = null;

        try {
            ps = con.prepareStatement("INSERT INTO Employees(
                    EmployeeID, name, Office) VALUES (?, ?, ?)");
            ps.setInt(1, 1000);
            ps.setString(2, "David Walker");
            ps.setString(3, "HQ101");
            ps.executeUpdate();
            ps.clearParameters();
            ps.setInt(1, 1001);
            ps.setString(2, "Paul Martin");
            ps.setString(3, "HQ101");
            ps.executeUpdate();
        } catch (SQLException e) {
            System.err.println("SQLException: " + e.getMessage());
        }
        finally {
            if (ps != null) {
                try {
                    ps.close();
                } catch (SQLException e) {
                    System.err.println("SQLException: " + e.getMessage());
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    System.err.println("SQLException: " + e.getMessage());
                }
            }
        }
    }
    
    private    static void createProcedure() {
        Connection con = getConnection();
        Statement stmt =null;
        try {
            stmt = con.createStatement();
            stmt.execute("CREATE PROCEDURE `WhoAreThey`(" +
                       "OUT error VARCHAR(128)," +
                       "IN office VARCHAR(10)) " +
                       "BEGIN "+
                       "SET error = NULL; "+
                       "IF office IS NULL THEN "+
                       "SET error = 'You need to pass in an office number'; 
                       + "ELSE "+
                       "  SELECT EmployeeID, Name " + 
                             " FROM employees WHERE office = office; "+
                       "END IF; "+
                       "END");

           } catch(SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.err.println("SQLException: " + e.getMessage());
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    System.err.println("SQLException: " + e.getMessage());
                }
            }
        }
        
    }
    private    static void dropProcedure() {
        Connection con = getConnection();
        Statement stmt =null;
        try {
            stmt = con.createStatement();
            stmt.execute(
                 "DROP PROCEDURE IF EXISTS `mydb`.`WhoAreThey`");
           } catch(SQLException ex) {
            System.err.println("SQLException: " + ex.getMessage());
        }
        finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    System.err.println("SQLException: " + e.getMessage());
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    System.err.println("SQLException: " + e.getMessage());
                }
            }
        }
        
    }
    private    static void callProcedure(String office) {
        Connection con = getConnection();
        CallableStatement cs = null;
        try {
            cs = con.prepareCall("{call WhoAreThey(?,?)}");
            cs.registerOutParameter(1, Types.VARCHAR);
            cs.setString(2, office);
            cs.execute();
            String str = cs.getString(1);
            if (str != null) {
                System.out.println(str);
            }
            else {
                ResultSet rs = cs.getResultSet();
                while (rs.next()) {
                    System.out.println("Name : " + rs.getString(2));
                }
            }
        } catch (SQLException e) {
            System.err.println("SQLException: " + e.getMessage());
        }
        finally {
            if (cs != null) {
                try {
                    cs.close();
                } catch (SQLException e) {
                    System.err.println("SQLException: " + e.getMessage());
                }
            }
            if (con != null) {
                try {
                    con.close();
                } catch (SQLException e) {
                    System.err.println("SQLException: " + e.getMessage());
                }
            }
        }
    }
    public static void main(String[] args) {
        createEmployees();
        insertEmployee();
        createProcedure();
        System.out.println("The first Call ...");
        callProcedure("HQ101");
        System.out.println("The second Call ...");
        callProcedure(null);
        dropProcedure();
        dropEmployees();
    }

}