Hibernate调用mysql存储过程，返回多个参数的方法

最新推荐文章于 2022-11-16 14:03:54 发布

波哥的技术积累

最新推荐文章于 2022-11-16 14:03:54 发布

阅读量5.3k

点赞数

分类专栏： MySql 文章标签： hibernate 存储 mysql session

本文链接：https://blog.csdn.net/woshisap/article/details/6953639

版权

MySql 专栏收录该内容

37 篇文章 0 订阅

订阅专栏

1：创建存储过程的语句

DELIMITER $;

DROP PROCEDURE IF EXISTS `xx`.`findUVCountByTime`$

CREATE PROCEDURE `xx`.`findUVCountByTime` (in querySql varchar(255), out startUpNum int(11), out closeDownNum int(11), out detailNum int(11), 
out listNum int(11), out takePartNum int(11), out toastNum int(11), out shareGoodsNum int(11), out loginNum int(11), out tradeNum int(11))
BEGIN
     
      declare startUpNumSql varchar(255) default '';
      declare closeDownNumSql varchar(255) default '';
      declare detailNumSql varchar(255) default '';
      declare listNumSql varchar(255) default '';
      declare takePartNumSql varchar(255) default '';
      declare toastNumSql varchar(255) default ''; 
      declare shareGoodsNumSql varchar(255) default '';
      declare loginNumSql varchar(255) default '';
      declare tradeNumSql varchar(255) default '';

     
      set startUpNumSql=concat(querySql, ' and keyValue=0) as a ');
      set closeDownNumSql=concat(querySql, ' and keyValue=1) as a ');
      set detailNumSql=concat(querySql, ' and keyValue=2) as a ');
      set listNumSql=concat(querySql, ' and keyValue=3) as a ');
      set takePartNumSql=concat(querySql, ' and keyValue=4) as a ');
      set toastNumSql=concat(querySql, ' and keyValue=5) as a ');
      set shareGoodsNumSql=concat(querySql, ' and keyValue=6) as a ');
      set loginNumSql=concat(querySql, ' and keyValue=7) as a ');
      set tradeNumSql=concat(querySql, ' and keyValue=8) as a ');

      set @startUpNumTranc = concat('select count(*) into @startUpNumS from' , startUpNumSql);
      set @closeDownNumTranc = concat('select count(*) into @closeDownNumS from' , closeDownNumSql);
      set @detailNumTranc = concat('select count(*) into @detailNumS from ', detailNumSql);
      set @listNumTranc = concat('select count(*) into @listNumS from ', listNumSql);
      set @takePartNumTranc = concat('select count(*) into @takePartNumS from ', takePartNumSql);
      set @toastNumTranc = concat('select count(*) into @toastNumS from ', toastNumSql);
      set @shareGoodsNumTranc = concat('select count(*) into @shareGoodsNumS from ', shareGoodsNumSql);
      set @loginNumTranc = concat('select count(*) into @loginNumS from ', loginNumSql); 
      set @tradeNumTranc = concat('select count(*) into @tradeNumS from ', tradeNumSql);
     
      PREPARE startUpNumTrancS FROM @startUpNumTranc;
      EXECUTE startUpNumTrancS; 
      set startUpNum = @startUpNumS;

      PREPARE closeDownNumTrancS FROM @closeDownNumTranc;
      EXECUTE closeDownNumTrancS; 
      set closeDownNum = @closeDownNumS;
    
      PREPARE detailNumTrancS FROM @detailNumTranc;
      EXECUTE detailNumTrancS; 
      set detailNum = @detailNumS;
  
      PREPARE listNumTrancS FROM @listNumTranc;
      EXECUTE listNumTrancS; 
      set listNum = @listNumS; 

      PREPARE takePartNumTrancS FROM @takePartNumTranc;
      EXECUTE takePartNumTrancS; 
      set takePartNum = @takePartNumS;

      PREPARE toastNumTrancS FROM @toastNumTranc;
      EXECUTE toastNumTrancS; 
      set toastNum = @toastNumS;

      PREPARE shareGoodsNumTrancS FROM @shareGoodsNumTranc;
      EXECUTE shareGoodsNumTrancS; 
      set shareGoodsNum = @shareGoodsNumS;

      PREPARE loginNumTrancS FROM @loginNumTranc;
      EXECUTE loginNumTrancS; 
      set loginNum = @loginNumS;

      PREPARE tradeNumTrancS FROM @tradeNumTranc;
      EXECUTE tradeNumTrancS; 
      set tradeNum = @tradeNumS;

     
END$

DELIMITER ;$

2：在Hibernate中调用存储过程并获得返回值

session = this.hibernateTemplate.getSessionFactory().openSession();
			CallableStatement cs = session.connection().prepareCall("
              { call findUVCountByTime(?,?,?,?,?,?,?,?,?,?)}");
			cs.setString(1, queryString);
			cs.registerOutParameter("startUpNum", Types.INTEGER);
			cs.registerOutParameter("closeDownNum", Types.INTEGER);
			cs.registerOutParameter("detailNum", Types.INTEGER);
			cs.registerOutParameter("startUpNum", Types.INTEGER);
			cs.registerOutParameter("startUpNum", Types.INTEGER);
			cs.registerOutParameter("startUpNum", Types.INTEGER);
			cs.registerOutParameter("startUpNum", Types.INTEGER);
			cs.registerOutParameter("startUpNum", Types.INTEGER);
			cs.registerOutParameter("startUpNum", Types.INTEGER);
			
			cs.execute();
			
			uvCount.setStartUpNum(cs.getInt("startUpNum"));    
			uvCount.setCloseDownNum(cs.getInt("closeDownNum"));  
			uvCount.setDetailNum(cs.getInt("detailNum"));        
			uvCount.setListNum(cs.getInt("listNum"));            
			uvCount.setTakePartNum(cs.getInt("takePartNum"));    
			uvCount.setToastNum(cs.getInt("toastNum"));          
			uvCount.setShareGoodsNum(cs.getInt("shareGoodsNum"));     
			uvCount.setLoginNum(cs.getInt("loginNum"));          
			uvCount.setTradeNum(cs.getInt("tradeNum"));          
			
			return uvCount;