{"id":674,"date":"2012-12-26T01:48:56","date_gmt":"2012-12-26T01:48:56","guid":{"rendered":"http:\/\/codeatelier.wordpress.com\/?p=410"},"modified":"2012-12-26T01:48:56","modified_gmt":"2012-12-26T01:48:56","slug":"how-to-get-values-from-mysql-stored-procedure-in-java-that-returns-more-than-one-resultset","status":"publish","type":"post","link":"https:\/\/codethataint.com\/blog\/how-to-get-values-from-mysql-stored-procedure-in-java-that-returns-more-than-one-resultset\/","title":{"rendered":"How to Get values from MySQL Stored Procedure in Java that returns more than one resultset"},"content":{"rendered":"<p>Stored Procedure<\/p>\n<pre>\n DROP PROCEDURE IF EXISTS getEmpDetails;\nCREATE PROCEDURE getEmpDetails(IN Salary INT, OUT total INT)\nBEGIN   \n   SELECT count(*) into total\n    FROM employee\n   WHERE sal &lt; Salary;\n  SELECT empid, empname, sal\n    FROM employee\n   WHERE sal &lt; Salary;\nEND;\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\"));\t\t\t\n     String strSQL  = \"{CALL getEmpDetails(?, ?)}\";\n     conn           = incDB.getConnection();\n     java.sql.CallableStatement stmt = conn.prepareCall(strSQL, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.FETCH_FORWARD);\t\t\t\n     stmt.setInt(1, salary);\n     stmt.registerOutParameter(\"total\", Types.INTEGER);\n\t\t\t\n     rs        = stmt.executeQuery();\n     int nRows = stmt.getInt(\"total\");\n\t\t\t\n     System.out.println(\"Total no of Records :\"+nRows);\n     display.getRecords(rs);\n  } \n  catch (SQLException e) \n  {\n     incDB.getException(e);\n  }\n}\t\n<\/pre>\n<p>Utility Functions<\/p>\n<pre>\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\");\t\t\n}\n<\/pre>\n","protected":false},"excerpt":{"rendered":"<p>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 &lt; Salary; 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&hellip; <a href=\"https:\/\/codethataint.com\/blog\/how-to-get-values-from-mysql-stored-procedure-in-java-that-returns-more-than-one-resultset\/\">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-674","post","type-post","status-publish","format-standard","hentry","category-database","category-java"],"_links":{"self":[{"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/posts\/674","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=674"}],"version-history":[{"count":0,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/posts\/674\/revisions"}],"wp:attachment":[{"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/media?parent=674"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/categories?post=674"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/codethataint.com\/blog\/wp-json\/wp\/v2\/tags?post=674"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}