Mysql存储过程——多结果集返回与java获取

Mysql存储过程——多结果集返回与java获取

delimiter $$

 

    CREATE PROCEDURE demoSp(IN inputParam VARCHAR(255), INOUTinOutParam varchar(255))

    BEGIN

        SELECT CONCAT('zyxw---', inputParam)into inOutParam;

    END $$

 

delimiter ;

 

java代码：

Connection conn = null;

    CallableStatement st = null; //list

    ResultSet rs = null;

    try{

   conn = DBManager.getConnection();

       st = conn.prepareCall("{call demoSp(?,?)}");

       st.setString(1, "aaaaaaaaaaaa");

       st.registerOutParameter(2, Types.VARCHAR);

      

       st.execute();

       System.out.println(st.getString(2));

      

    }catch (Exception e) {

       throw new RuntimeException(e);

    }finally{

       DBManager.release(conn, st, rs);

    }

//第二种情况

存储过程经常需要返回多个结果集。 Mysql 中直接用 select 即可返回结果集。而 oracle 则需要使用游标来返回结 果集。这一点 Mysql 相对比较方便，如下代码即可实现输出两个结果集：

CREATE PROCEDURE test_proc_multi_select()  

BEGIN  

         select * from testproc;  

         select * from testproc where id=1;  

END;  

Java 代码

package com.hbsi.test;

 

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Types;

 

import com.hbsi.utils.DBManager;

 

public class DemoMutilresult {

 

       /**

        * @param args

        */

       publicstatic void main(String[] args) {

              //TODO Auto-generated method stub

              Connectioncon=null;

              //用于执行sql存储过程的借口对象

              CallableStatementst=null;

              ResultSetrs=null;

             

              try{

                     con=DBManager.getConnection();

                     //java中利用循环，即可获取结果际数据

                     Stringsql="{call test_mutil_proc()}";

                     st=con.prepareCall(sql);

                     //

                     booleanflag=st.execute();

                     inti=0;

                     while(flag){

                            System.out.println("第几个结果集"+(++i));

                            rs=st.getResultSet();

                            while(rs.next()){

                                   System.out.println(rs.getInt(1));

                                  

                            }

                            flag=st.getMoreResults();

                     }

                    

                    

                    

              }catch (SQLException e) {

                     //TODO Auto-generated catch block

                     e.printStackTrace();

              }finally{

                     DBManager.release(con,st, rs);

              }

 

       }

 

}