mysql存储过程返回查询结果集_MYSQL存储过程查询返回结果处理方法

一、MYSQL表创建语句

1.1  创建表

SET FOREIGN_KEY_CHECKS=0;

DROP TABLE IF EXISTS `testproc`;

CREATE TABLE `testproc` (

`id` int(4) NOT NULL,

`name` varchar(100) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

1.2  插入数据

INSERT INTO `testproc` VALUES ('1', '第一条记录');

INSERT INTO `testproc` VALUES ('2', 'efgh');

INSERT INTO `testproc` VALUES ('3', 'ijklmn');

INSERT INTO `testproc` VALUES ('4', 'zxvb');

INSERT INTO `testproc` VALUES ('5', 'uiop');

INSERT INTO `testproc` VALUES ('6', '222');

INSERT INTO `testproc` VALUES ('7', '8888');

INSERT INTO `testproc` VALUES ('9', '第9条记录');

INSERT INTO `testproc` VALUES ('10', '第10条记录');

INSERT INTO `testproc` VALUES ('11', '第11条记录');

INSERT INTO `testproc` VALUES ('12', '第12条记录');

1.3  创建存储过程

CREATE PROCEDURE test_proc_multi_select()

BEGIN

select * from testproc;

select * from testproc where id=1;

END;

二、JAVA相关代码

注意要导入MYSQL包

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.ResultSet;

import java.sql.SQLException;

import com.mysql.jdbc.CallableStatement;

public class Test1 {

public static final String DBDRIVER = "com.mysql.jdbc.Driver";

public static final String DBURL = "jdbc:mysql://127.0.0.1:3306/test1";

public static final String DBUSER = "root";

public static final String DBPASS = "123456";

public static void main(String[] args) {

Connection con = null;

CallableStatement cs;

try {

Class.forName(DBDRIVER);

con = DriverManager.getConnection(DBURL, DBUSER, DBPASS);

String sql = "{call test_proc_multi_select()}";

cs = (CallableStatement) con.prepareCall(sql);

boolean hadResults = cs.execute();

int i = 0;

while (hadResults) {

System.out.println("result No:----" + (++i));

ResultSet rs = cs.getResultSet();

while (rs != null && rs.next()) {

int id1 = rs.getInt(1);

String name1 = rs.getString(2);

System.out.println(id1 + ":" + name1);

}

hadResults = cs.getMoreResults(); // 检查是否存在更多结果集

}

} catch (ClassNotFoundException e) {

e.printStackTrace();

} catch (SQLException e) {

e.printStackTrace();

} finally {

try {

con.close();

} catch (SQLException e) {

e.printStackTrace();

}

}

}

}