{"id":675,"date":"2012-12-26T02:00:41","date_gmt":"2012-12-26T02:00:41","guid":{"rendered":"http:\/\/codeatelier.wordpress.com\/?p=414"},"modified":"2012-12-26T02:00:41","modified_gmt":"2012-12-26T02:00:41","slug":"stored-procedure-which-returns-records-from-select-statement","status":"publish","type":"post","link":"https:\/\/codethataint.com\/blog\/stored-procedure-which-returns-records-from-select-statement\/","title":{"rendered":"Stored procedure which returns records from select statement"},"content":{"rendered":"<p>Stored Procedure<\/p>\n<pre>\n  DROP PROCEDURE IF EXISTS getEmpDetail;\n  CREATE PROCEDURE getEmpDetail(IN Salary INT)\n  BEGIN   \n   SELECT empid, empname, sal\n     FROM employee\n    WHERE sal &lt; Salary;\n  END;\n<\/pre>\n<p>Java Code<\/p>\n<pre>\npublic static void main(String args[]) throws SQLException \n{\t\n  try\t\n  {\n    Connection conn = null;\t\t\n    ResultSet  rs   = null;\n\t\t\t\n    Integer salary = Integer.parseInt(display.getInput(\"Enter the Salary Amount\"));\n\t\t\t\n    String strSQL = \"{CALL getEmpDetail(?)}\";\n    conn = incDB.getConnection();\n    PreparedStatement stmt = (PreparedStatement) conn.prepareStatement(strSQL, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.FETCH_FORWARD);\t\t\t\n    \n    stmt.setInt(1, salary);\n    rs = stmt.executeQuery();\n\t\t\n    display.getRecords(rs);\n  } \n  catch (SQLException e) \n  {\n    incDB.getException(e);\n  }\n}\t\n<\/pre>\n<p>Utility Function<\/p>\n<pre>\n public class incDB\n{\t\n\tprivate static String USER_NAME = \"root\";\n\tprivate static String USER_PASS = \"pass\";\n\tprivate static String CONN_STRING = \"jdbc:mysql:\/\/localhost\/test\";\n\t\n\tpublic static Connection getConnection() throws SQLException\n\t{\n\t\treturn DriverManager.getConnection(CONN_STRING, USER_NAME, USER_PASS);\n\t}\n\t\n\tpublic static void getException(SQLException e)\n\t{\n\t\tSystem.out.println(\"Message :\"+e.getMessage());\n\t\tSystem.out.println(\"Error Code :\"+e.getErrorCode());\n\t\tSystem.out.println(\"State : \"+e.getSQLState());\n\t}\n}\n\npublic static void getRecords(ResultSet rs) throws SQLException \n{\t\n  while(rs.next())\n  {\n    StringBuffer buffer =  new StringBuffer();\n    buffer.append(\"Emp Id :\"+ rs.getString(\"empid\")+ \" Emp Name :\"+rs.getString(\"empname\"));\n    System.out.println(buffer.toString());\n  }\n\t\t\n  if(rs.isBeforeFirst())\n    System.out.println(\"Finished Printing\");\n }\n\t\n public static String getInput(String prompt)\n {\n   BufferedReader stdin = new BufferedReader(new InputStreamReader(System.in));\n   System.out.print(prompt);\n   System.out.flush();\n\t\n   try \n   {\n     return stdin.readLine();\n   } \n   catch (Exception e) \n   {\n     return \"Error :\"+e.getMessage();\n   }\n  }\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Stored Procedure DROP PROCEDURE IF EXISTS getEmpDetail; CREATE PROCEDURE getEmpDetail(IN Salary INT) BEGIN SELECT empid, empname, sal FROM employee WHERE sal &lt; 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(&#8220;Enter the Salary Amount&#8221;)); String strSQL = &#8220;{CALL&hellip; <a href=\"https:\/\/codethataint.com\/blog\/stored-procedure-which-returns-records-from-select-statement\/\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[4,3],"tags":[],"class_list":["post-675","post","type-post","status-publish","format-standard","hentry","category-database","category-java"],"_links":{"self":[{"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/posts\/675","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/comments?post=675"}],"version-history":[{"count":0,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/posts\/675\/revisions"}],"wp:attachment":[{"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/media?parent=675"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/categories?post=675"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/tags?post=675"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}