存储过程及调用

CREATE PROCEDURE `select_Appraise_Panel`(IN p_id INT,IN year_start VARCHAR(20),IN pageNo INTEGER,IN pageSize INTEGER,OUT totalSize INTEGER)  
BEGIN  
    DECLARE idx INT DEFAULT 0;  -- 表名标号  
    DECLARE t_name VARCHAR(50); -- 拼接后的表名  
    DECLARE t_tabYoN VARCHAR(10);   -- 是否有这张表  
    --  DECLARE error_num INTEGER DEFAULT 0; -- 是否有错误发生  
    --  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET error_num=1;  
    DECLARE sqlStr varchar(4000) default ''; -- 拼接SQL  
    DECLARE sqlStr2 VARCHAR(4000) DEFAULT '';  
    DECLARE $start INTEGER DEFAULT 0;  
    --  START TRANSACTION;  -- 开启事务  

    -- 拼接SQL
    IF p_id=3 THEN  
        loop_num:LOOP  
            set idx=idx+1;  
            set t_name = CONCAT('t_ace_appraise_',year_start,LPAD(idx,2,'0'));  
            IF EXISTS (select 1 FROM information_schema.`TABLES` WHERE table_name=t_name) THEN        
                set sqlStr = CONCAT(sqlStr," select CONCAT('",year_start,"-',LPAD(T.stime,2,'0')) as stime",  
                            ",T.description,count(T.pk_id) as counts from t_setup_unit as T group by t.u_pk,T.stime");   
                set sqlStr = CONCAT(sqlStr,' UNION ALL');  
            END IF;  
            IF idx>12 THEN  
                LEAVE loop_num;  
            END IF;  
        END LOOP;  
        set sqlStr = LEFT(sqlStr,LENGTH(sqlStr)-9);  
        set sqlStr = CONCAT('select Y.stime,Y.description,Y.counts from (',sqlStr,') as Y order by Y.description,Y.stime asc');  
    END IF;  
    
    -- 计算总数  
    SET sqlStr2 = CONCAT("select count(*) into @totalSize from (",sqlStr,") as tc");  
    set @sql2 = sqlStr2;  
    PREPARE stat2 FROM @sql2;  
    EXECUTE stat2;  
    DEALLOCATE PREPARE stat2;  
  
    -- 设置分页  
    IF pageNo IS NOT NULL AND pageSize IS NOT NULL AND pageSize!=-1 THEN  
        IF pageNo < 0 THEN SET pageNo = 0; END IF;  
        IF pageSize < 0 THEN SET pageSize = 10; END IF;  
        SET $start =  (pageNo-1)*pageSize;  
        SET sqlStr = CONCAT(sqlStr," limit ",$start,",",pageSize);  
    END IF;  
    -- 获取列表   
    set @sql1 = sqlStr;  
    PREPARE stat FROM @sql1;  
    EXECUTE stat; 
    DEALLOCATE PREPARE stat;  
    SET totalSize = @totalSize;  
    
    -- 判断中途是否出错  
    --  IF error=1 THEN  
    --      ROLLBACK;  
    --  ELSE  
    --      COMMIT;  
    --  END IF;  
END 

 调用：

public PageResult<List<Map<String, Object>>> callProcedureForPagedata1(String procName, Map<String, Object> inParams,  
        Map<String, Integer> outParams) throws SQLException {  
    Connection con = SessionFactoryUtils.getDataSource(getSessionFactory()).getConnection();  
    ResultSet rs = null;  
    CallableStatement call = con.prepareCall("{CALL " + procName + "}");  
    // 设置输入参数  
    if (inParams != null && !inParams.isEmpty()) {  
        for (String one : inParams.keySet()) {  
            call.setObject(one, inParams.get(one));  
        }  
    }  
    // 注册输出参数  
    outParams.put("totalSize", Types.INTEGER);  
    if (outParams != null && !outParams.isEmpty()) {  
        for (String one : outParams.keySet()) {  
            call.registerOutParameter(one, outParams.get(one));  
        }  
    }  
    call.execute();  
    rs = call.getResultSet();// 获取查询结果集  
    List<Object> list = new ArrayList<Object>();  
    // 将结果集封装出List类型的集合  
    while (rs != null && rs.next()) {  
        Map<String, Object> rowData = new HashMap<String, Object>();  
        ResultSetMetaData md = (ResultSetMetaData) rs.getMetaData();  
        int columnCount = md.getColumnCount();  
        for (int i = 1; i <= columnCount; i++) {  
            rowData.put(md.getColumnLabel(i), rs.getObject(i));  
        }  
        list.add(rowData);  
    }  
    long totalSize = call.getLong("totalSize");// 总记录数的输出参数  
    int pageNo = inParams.get("pageNo") == null ? 0 : (int) inParams.get("pageNo");  
    int pageSize = (int) (inParams.get("pageSize") == null ? totalSize : (int) inParams.get("pageSize"));  
    PageResult<List<Map<String, Object>>> pageResult = new PageResult(pageNo, totalSize, pageSize, list);  
  
    // 释放资源  
    call.close();  
    con.close();  
  
    return pageResult;  
}