Stored Procedure

  DROP PROCEDURE IF EXISTS getEmpDetail;
  CREATE PROCEDURE getEmpDetail(IN Salary INT)
  BEGIN   
   SELECT empid, empname, sal
     FROM employee
    WHERE sal < Salary;
  END;

Java Code

public static void main(String args[]) throws SQLException 
{	
  try	
  {
    Connection conn = null;		
    ResultSet  rs   = null;
			
    Integer salary = Integer.parseInt(display.getInput("Enter the Salary Amount"));
			
    String strSQL = "{CALL getEmpDetail(?)}";
    conn = incDB.getConnection();
    PreparedStatement stmt = (PreparedStatement) conn.prepareStatement(strSQL, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.FETCH_FORWARD);			
    
    stmt.setInt(1, salary);
    rs = stmt.executeQuery();
		
    display.getRecords(rs);
  } 
  catch (SQLException e) 
  {
    incDB.getException(e);
  }
}	

Utility Function

 public class incDB
{	
	private static String USER_NAME = "root";
	private static String USER_PASS = "pass";
	private static String CONN_STRING = "jdbc:mysql://localhost/test";
	
	public static Connection getConnection() throws SQLException
	{
		return DriverManager.getConnection(CONN_STRING, USER_NAME, USER_PASS);
	}
	
	public static void getException(SQLException e)
	{
		System.out.println("Message :"+e.getMessage());
		System.out.println("Error Code :"+e.getErrorCode());
		System.out.println("State : "+e.getSQLState());
	}
}

public static void getRecords(ResultSet rs) throws SQLException 
{	
  while(rs.next())
  {
    StringBuffer buffer =  new StringBuffer();
    buffer.append("Emp Id :"+ rs.getString("empid")+ " Emp Name :"+rs.getString("empname"));
    System.out.println(buffer.toString());
  }
		
  if(rs.isBeforeFirst())
    System.out.println("Finished Printing");
 }
	
 public static String getInput(String prompt)
 {
   BufferedReader stdin = new BufferedReader(new InputStreamReader(System.in));
   System.out.print(prompt);
   System.out.flush();
	
   try 
   {
     return stdin.readLine();
   } 
   catch (Exception e) 
   {
     return "Error :"+e.getMessage();
   }
  }

Leave a reply