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