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

迷南。 2022-02-24 15:28 424阅读 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