Stored Procedure

 DROP PROCEDURE IF EXISTS getEmpDetails;
CREATE PROCEDURE getEmpDetails(IN Salary INT, OUT total INT)
BEGIN   
   SELECT count(*) into total
    FROM employee
   WHERE sal < Salary;
  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 getEmpDetails(?, ?)}";
     conn           = incDB.getConnection();
     java.sql.CallableStatement stmt = conn.prepareCall(strSQL, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.FETCH_FORWARD);			
     stmt.setInt(1, salary);
     stmt.registerOutParameter("total", Types.INTEGER);
			
     rs        = stmt.executeQuery();
     int nRows = stmt.getInt("total");
			
     System.out.println("Total no of Records :"+nRows);
     display.getRecords(rs);
  } 
  catch (SQLException e) 
  {
     incDB.getException(e);
  }
}	

Utility Functions

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");		
}

Leave a reply