mybatis 调用存储过程包含输入输出参数多结果集

本文转载自：http://blog.csdn.net/ge_zhiqiang/article/details/38065629

 

一、首先定义存储过程，数据库环境mysql

 

CREATE  PROCEDURE queryTotalNum(OUT totalNum INT , IN tableName varchar(40), IN conditions varchar(300))  
 
begin      
 
    declare stmt varchar(2000);     
 
    declare num int;  
 
    if LENGTH(conditions)>1 then    
 
    begin  
 
        set @sqlstr=concat('SELECT COUNT(*) INTO @num from ',tableName,' where ',conditions);  
 
    end;  
 
    else  
 
    begin  
 
         set @sqlstr=concat('SELECT COUNT(*) INTO @num from ',tableName);  

 
    end;  
 
    end if;    
 
    prepare stmt from @sqlstr;      
 
    execute stmt;    
 
    deallocate prepare stmt;  
 
    set totalNum = @num;  
 
	select * from hdx_order_info LIMIT 0, 10 ;
  	select * from hdx_proxy_distributor LIMIT 0, 10 ;

    end; 

 二、配置mybatis文件

 

 

<!--Map作为存储过程输入输出参数	-->
  <select id="getNamesAndItems" statementType="CALLABLE" parameterType="Map"  resultMap="HdxOrderInfoMap,TestMap">  
    {call queryTotalNum(#{totalNum,jdbcType=INTEGER,mode=OUT},#{tableName,jdbcType=INTEGER,mode=IN},#{conditions,jdbcType=INTEGER,mode=IN})}  
  </select> 

 三、DAO类

 

 

public interface IHdxOrderInfoDao {
	
	public List<List<?>> getNamesAndItems(Map<String,Object> map);
	
	
}

 

 

四、services接口

public interface IHdxOrderInfoService  {
 
 public List<List<?>> getNamesAndItems(Map<String,Object> map);
}

 

五、services实现

public class HdxOrderInfoServiceImpl implements IHdxOrderInfoService{
	private IHdxOrderInfoDao hdxOrderInfoDao = null;

	
	public IHdxOrderInfoDao getHdxOrderInfoDao() {
		return hdxOrderInfoDao;
	}


	public void setHdxOrderInfoDao(IHdxOrderInfoDao hdxOrderInfoDao) {
		this.hdxOrderInfoDao = hdxOrderInfoDao;
	}


	public List<List<?>> getNamesAndItems(Map<String, Object> map) {
		
		List<List<?>> list = this.hdxOrderInfoDao.getNamesAndItems(map);
		
                //totalNum是存储过程中的输出参数
		System.out.println(map.get("totalNum"));
		
		//List<HdxOrderInfo> list0 = (List<HdxOrderInfo>)list.get(0);
		//List<HdxProxyDistributor> list1 = (List<HdxProxyDistributor>)list.get(1);
		
		return list 
	}
}