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")+"\t"+ "name:"+rs.getString("name")+"\t"+ "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 秒)