一、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();
}
}
}
}
如果觉得《mysql存储过程返回查询结果集_MYSQL存储过程查询返回结果处理方法》对你有帮助，请点赞、收藏，并留下你的观点哦！
