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