Mysql存储过程返回列表，java进行调用

最新推荐文章于 2024-09-17 22:59:30 发布

hunheidaode

最新推荐文章于 2024-09-17 22:59:30 发布

阅读量3.4k

点赞数 1

分类专栏： mysql java

本文链接：https://blog.csdn.net/hunheidaode/article/details/89024429

版权

java 同时被 2 个专栏收录

13 篇文章 2 订阅

订阅专栏

mysql

7 篇文章 0 订阅

订阅专栏

-- auto-generated definition
create table t_user
(
  t_id      int auto_increment comment '编号'
    primary key,
  t_name    varchar(30)  null comment '名称',
  t_age     int(10)      null comment '年龄',
  t_address varchar(100) null comment '家庭住址',
  t_pwd     varchar(100) null
)
  engine = MyISAM
  charset = latin1;

INSERT INTO test.t_user (t_id, t_name, t_age, t_address, t_pwd) VALUES (5, '1', 22, '1', null);
INSERT INTO test.t_user (t_id, t_name, t_age, t_address, t_pwd) VALUES (6, 'admin', 22, 'jinan', '123');



select * from t_user;
-- 创建存储过程
CREATE PROCEDURE test()
BEGIN
         select * from t_user;
END;
-- 调用
call  test();

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;

public class TestMysql_proc {
    //连接mysql数据库
    public static final String DRIVER_CLASS = "com.mysql.jdbc.Driver";
    public static final String URL = "jdbc:mysql://localhost/test";
    public static final String USERNAME = "root";
    public static final String PASSWORD = "root";
    public static void main(String[] args) throws Exception {
        test1();
      /*  test1();
        test2();*/
    }
    public static void test1() throws Exception
    {
        Class.forName(DRIVER_CLASS);
        Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
        String sql = "{CALL test()}"; //调用存储过程
        CallableStatement cs = connection.prepareCall(sql);
        boolean hadResults = cs.execute();
        int i=0;
        while (hadResults) {
            ResultSet rs = cs.getResultSet();
            while (rs != null && rs.next()) {
                String name = rs.getString(1);
                System.out.println("id:"+rs.getString(1)+",name:"+rs.getString(2)+
                        ",age:"+rs.getString(3)+",adress:"+rs.getString(4)
                        +",pwd:"+rs.getString(5));
            }
            hadResults = cs.getMoreResults(); //检查是否存在更多结果集
        }
    }
}
执行结果：
        id:5,name:1,age:22,adress:1,pwd:null
        id:6,name:admin,age:22,adress:jinan,pwd:123