java调用mysql存储过程返回多个结果集，与.net不同

￡神魔★判官ぃ 2022-01-17 06:35 256阅读 0赞

说明：mysql存储过程返回两个结果集，即两个table，用java的ResultSet接收并处理，  
使用comm.getResultSet()，如果返回不为null，则表示还有结果集，  
与.net中的DataSet不同（ds.table\[0\]   ds.table\[1\]）。

一、java代码如下：  
import java.sql.CallableStatement;  
import java.sql.Connection;  
import java.sql.DriverManager;  
import java.sql.ResultSet;  
import java.sql.SQLException;

public class MainClass \{  
 public static void main(String\[\] args) throws SQLException \{  
  Connection conn = null;  
  CallableStatement comm = null;  
  ResultSet ds = null;  
  String commStr = "";  
  String content = "";

String dbUrl = "jdbc:mysql://localhost:3306/mydb1";  
  String theUser = "root";  
  String thePw = "bxdz";

try \{  
   Class.forName("com.mysql.jdbc.Driver").newInstance();

conn = (Connection) DriverManager.getConnection(dbUrl, theUser,  
     thePw);  
   commStr = "call my\_proc('2011-08-01')";  
   comm = ((java.sql.Connection) conn).prepareCall(commStr);  
   comm.execute();  
   ds = comm.getResultSet();  
   while (ds.next()) \{  
    if (content == "") \{  
     content += "结果集1：\\nC1\\tC2\\tC3";  
    \}  
    content += "\\n" + ds.getString(1) + "\\t" + ds.getInt(2) + "\\t"  
      + ds.getDate(3);

if (comm.getMoreResults() == true) \{

content += "\\n\\n结果集2：\\nC1\\tC3";  
    ds = comm.getResultSet();

while (ds.next()) \{  
     content += "\\n" + ds.getString(1) + "\\t" + ds.getDate(3);  
    \}  
   \}  
   System.out.println(content);  
  \} catch (Exception e) \{  
  \} finally \{  
   if (ds != null)  
    ds.close();  
   if (comm != null)  
    comm.close();  
   if (conn != null)  
    conn.close();  
  \}  
 \}  
\}

结果集1：  
C1 C2 C3  
zhao 10 2011-08-15  
zhao 2 2011-09-16

结果集2：  
C1 C3  
zhenlong 2011-08-05  
zhenlong 2011-09-30

二、mysql存储过程如下：  
DROP PROCEDURE IF EXISTS mydb1.my\_proc;  
CREATE PROCEDURE mydb1.\`my\_proc\`(pDate Date)  
BEGIN  
 select \* from table1 where c1 = 'zhao' and C3 > pDate;  
 select \* from table1 where c1 = 'zhenlong' and C3 > pDate;  
END;

c1 C2 C3  
zhao 10 2011/8/15 0:00:00  
zhenlong 5 2011/8/5 0:00:00  
zhao 2 2011/9/16 0:00:00  
zhenlong 6 2011/9/30 0:00:00