返回多数据集写法：
第一种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 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);}