mysql procedure返回多資料集

標籤：android   style   blog   color   java   使用   os   io   

返回多資料集寫法：
第一種
 

DROP PROCEDURE IF EXISTS `p_query_user` ; DELIMITER //      CREATE PROCEDURE p_query_user(  p_inout_user_id  INT   )      begin    declare c varchar(100) default ‘0‘;         SELECT * FROM T_SD_USER WHERE USER_ID = p_inout_user_id ;         SET p_inout_user_id =2222;end // DELIMITER ; set @uId = 3000 ;CALL p_query_user(@uId );SELECT @uId ;

 

第二種

mysql>delimiter //mysql>drop procedure if exists proc;mysql>create procedure proc()begincreate temporary table tmp1 like t;insert into tmp1 select * from t;create temporary table tmp2 like t;insert into tmp2 select * from t;end;//mysql>delimiter ;mysql>select * from tmp1;mysql>select * from tmp2; 

註：使用暫存資料表在session級一直可用，再次調用預存程序需要先DROP暫存資料表。

 

hibernate call 

@Test    public void test0() {        try {            String procedureSql = "CALL p_query_user( :userId)";            SQLQuery sqlQuery = t.getSession().createSQLQuery(procedureSql)                    .addEntity(TSdUser.class);            Object userId = 3000;            sqlQuery.setParameter("userId", userId);            List<TSdUser> result = sqlQuery.list();            for (TSdUser tSdUser : result) {                System.out.println(tSdUser);            }            System.out.println(userId);        } catch (Exception e) {            e.printStackTrace();        }    }

結果如下:

Hibernate: CALL p_query_user( ?)
TSdUser [userSysMlb=100.0, userUseredMlb=633137.0, userDoDiscoverCount=0, userDoCommonsSayCount=0, createTime=2014-05-08 12:20:35.0, lastActivateSystemMsgTime=2014-07-17 12:10:00.0, lastActivateSusCommentMsgTime=2014-05-08 12:20:35.0, lastActivateNegativeMsgTime=2014-06-17 10:27:37.0, lastActivatePraiseMsgTime=2014-05-08 12:20:35.0, lastActiveTime=2014-08-11 18:45:52.0, priMsgTime=2014-08-09 14:30:27.0, lockTime=null, userMark=null, userSubMark=null, userRemark=null, userInfo=null, userLoginIp=127.0.0.1, userLogginPhoneCode=13724770797, userRegisterInfo=null, userRealName=null, userRegisterPhoneCode=13724770797, userRegistrationCity=null, userPhoneCity=湛江, userDeviceType=Android, userLoginQqId=1, userLoginWeiXinId=null, userLoginSinaBlogId=null, userWeiXinOpenId=111, cteateType=1, restaurantWeiXinOpenId=222, weiXinSubscribeTime=2014-05-17 13:29:28.0, weiXinSubscribeStatus=SUBSCRIBE, weiXinUnSubscribeTime=null, userId=3000, userName=MJorcen, userTotalMlb=1.1111111E7, userDoAttentionCount=0, userReAttentionCount=0, userDoPraiseCount=0, userRePraiseCount=11, userDoNegativeCount=0, userReNegativeCount=6, userDoShowDetailCount=38, userDoGetVoucherCount=5, userDoUsedVoucherCount=2, userPhone=13724770797, userGender=G, userGrade=0, userHeader=null]

或者:

    @Test    public void test1() {        try {            String procedureSql = "CALL p_query_user(?)";            Connection connection = t.getSession().connection();            Object userId = 3000;            java.sql.CallableStatement cs = connection                    .prepareCall(procedureSql);            cs.setInt(1, 3000);                        cs.registerOutParameter("p_inout_user_id", Types.INTEGER);            cs.execute();            int patams1 = cs.getInt(1);            ResultSet resultSet = cs.getResultSet();            while (resultSet.next()) {                System.out.println(resultSet.getInt("USER_ID"));            }            System.out.println(patams1);        } catch (Exception e) {            e.printStackTrace();        }    }

 

 一個網上的例子;

public String callFirstProcedures(final long qyid, final int pdys) {    String procedureSql = "{call get_qyaqdcpdjb(?,?,?)}";    Object pj = jdbcTemplate.execute(procedureSql,            new CallableStatementCallback() {                public Object doInCallableStatement(CallableStatement cs)                        throws SQLException, DataAccessException {                    cs.setLong(1, qyid);                    cs.setInt(2, pdys);                    //你需要註冊一個輸出參數                    cs.registerOutParameter(3, Types.VARCHAR);                    cs.execute();                    return cs.getString(3);                }            });    return HtmsUtils.formatObject(pj);}