jdbc 处理mysql procedure返回的多个结果集

1：测试数据库表user

mysql> desc user$$
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | YES  |     | NULL    |                |
| age   | int(11)     | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

2：存储过程，返回id>n_id 的与id<n_id的两个结果集

delimiter $$
create procedure p_get_user_list(in n_id int)
begin
    select id, name, age from user where id > n_id;
    select id, name, age from user where id < n_id;
end $$

3：JDBC操作

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package jdbctest;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author y
 */
public class Jdbctest {

    /**
     * @param args the command line arguments
     */
    public static void main(String[] args) {
        // TODO code application logic here
        funtest();
    }
    
    
    public static void funtest(){
        Connection conn = null;
        CallableStatement calState = null;
        ResultSet rs = null;
        
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = (Connection)DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "");
            
            calState = conn.prepareCall("{call p_get_user_list(?)}");
            calState.setInt(1, 3);
            
            boolean oprFlag = calState.execute();
            //使用外循环来控制结果集的个数，内循环控制每个结果集的记录
            while(oprFlag){
                rs = calState.getResultSet();
                System.out.println("=================");
                while(rs.next()){
                    System.out.println("id:"+rs.getInt("id")+"	"+
                                "name:"+rs.getString("name")+"	"+
                                "age:" +rs.getInt("age"));
                }
                oprFlag = calState.getMoreResults();
            }
        }catch (ClassNotFoundException | SQLException ex) {
            Logger.getLogger(Jdbctest.class.getName()).log(Level.SEVERE, null, ex);
        }finally{
            if(null !=rs ){
                try {
                    rs.close();
                } catch (SQLException ex) {
                    Logger.getLogger(Jdbctest.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
            if(null != calState){
                try {
                    calState.close();
                } catch (SQLException ex) {
                    Logger.getLogger(Jdbctest.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
            if(null != conn){
                try {
                    conn.close();
                } catch (SQLException ex) {
                    Logger.getLogger(Jdbctest.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }
        
    }
    
}

4：测试结果

run:
=================
id:4    name:test2    age:30
id:5    name:test3    age:24
=================
id:1    name:里斯    age:25
id:2    name:王五    age:26
成功构建 (总时间: 0 秒)