存储过程:
CREATE PROCEDURE `select_AppraiseShow_Panel3_2`(IN p_id INT,IN year_start VARCHAR(20),IN year_over VARCHAR(20),IN selUnit varchar(50),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; -- 开启事务
IF p_id=1 THEN
-- year_start ==> 2017-02-13
set t_name = CONCAT('t_ace_appraise_',LEFT(year_start,4),SUBSTR(year_start,6,2));
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","(",
" select o.pk_id,hour(o.stime) as stime,o.u_pk,seu.description",
" from t_ace_appraise_",LEFT(year_start,4),SUBSTR(year_start,6,2)," as o RIGHT JOIN t_setup_unit as seu on o.u_pk=seu.pk_id and seu.id LIKE '%",selUnit,"%'",
" where o.stime<='",year_over," 23:59:59' and o.stime>='",year_start," 00:00:00' and o.ace15Ld4 > 0 and o.remission = 0 ",
" ) as T",
" group by t.u_pk,T.stime ORDER BY t.u_pk,t.stime");
END IF;
END IF;
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"," (select o.pk_id,month(o.stime) as stime,o.u_pk,tsu.description"," from ",t_name," as o"," RIGHT JOIN t_setup_unit tsu on o.u_pk = tsu.pk_id AND tsu.id like '%",selUnit,"%'",
" where o.stime<='",year_over,"-",LPAD(idx,2,'0'),"-31 23:59:59' and o.stime>='",year_start,"-",LPAD(idx,2,'0'),"-01 00:00:00' and o.ace15Ld4 > 0 and o.remission = 0 ",
" ) 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 $start = pageNo;
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;
}