首页 > 代码库 > jdbc 处理mysql procedure返回的多个结果集
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:30id:5 name:test3 age:24=================id:1 name:里斯 age:25id:2 name:王五 age:26成功构建 (总时间: 0 秒)
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。