MySql存储过程及调用

存储过程：

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)BEGINDECLARE 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 ''; -- 拼接SQLDECLARE sqlStr2 VARCHAR(4000) DEFAULT '';DECLARE $start INTEGER DEFAULT 0;-- START TRANSACTION;-- 开启事务IF p_id=1 THEN-- year_start ==> 2017-02-13set 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) THENSET 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 THENloop_num:LOOPset 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) THENset 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 THENLEAVE 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 THENIF 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;}