mybatis 存储过程

BEGIN
CREATE TEMPORARY TABLE tmp_table_business(column_x_y VARCHAR(10));
IF (rUid IS null) and (businessLcuId IS null) THEN
  insert INTO tmp_table_business(
   SELECT
  	a.business_lcu_id AS column_x_y
  FROM
  	business_lcu a
  WHERE
  	a.is_delete = 1
  AND a.project_id = projectId
  );
END IF;
IF ((rUid IS NOT NULL) AND ( businessLcuId is NULL)) THEN 
insert INTO tmp_table_business(
SELECT
	a.business_lcu_id AS column_x_y
FROM
	business_lcu a
WHERE
	a.is_delete = 1
AND a.rtu_id = rUid);
END IF;
IF (businessLcuId IS not NULL) THEN
  insert INTO tmp_table_business( column_x_y) VALUES( businessLcuId);
  END IF; 
 SELECT
        COUNT(a.workorder_id) INTO undisposed
        FROM
        workorder a
        WHERE
        is_deleted = 0 AND a.workorder_device_id IN(SELECT * FROM tmp_table_business) AND
        a.workorder_status_id IN(1,2);
 SELECT
        COUNT(a.workorder_id) INTO disposing
        FROM
        workorder a
        WHERE
        is_deleted = 0 AND a.workorder_device_id IN(SELECT * FROM tmp_table_business) AND
        a.workorder_status_id IN(3,4,5);
SELECT
        COUNT(a.workorder_id) INTO disposed
        FROM
        workorder a
        WHERE
        is_deleted = 0 AND a.workorder_device_id IN(SELECT * FROM tmp_table_business) AND
        a.workorder_status_id =6;
SELECT
        COUNT(a.workorder_id) INTO total
        FROM
        workorder a
        WHERE
        is_deleted = 0 AND a.workorder_device_id IN(SELECT * FROM tmp_table_business);
        //这里建议删除一下：  临时表有两种级别：1、会话级别；2、缓存级别。  其中会话级别的会在回话结束时自行删除（这里用的
        //是会话级别的，安全起见还是进行了删除）。缓存级别的在声明时有一个 TYPE = HEAP 实例：CREATE TEMPORARY TABLE 
        //tmp_table_business(column_x_y VARCHAR(10)) TYPE = HEAP；具体使用方式自行百度
   DROP TEMPORARY TABLE IF EXISTS tmp_table_business;
      //将数据返回给java  没有它是行不通的
SELECT total ,undisposed ,disposing ,disposed ;
END

经过改进的：把IN换成了 EXISTS

BEGIN
CREATE TEMPORARY TABLE tmp_table_business(column_x_y VARCHAR(10));
IF (rUid IS null) and (businessLcuId IS null) THEN
  insert INTO tmp_table_business(
   SELECT
  	a.business_lcu_id AS column_x_y
  FROM
  	business_lcu a
  WHERE
  	a.is_delete = 1
  AND a.project_id = projectId
  );
END IF;
IF ((rUid IS NOT NULL) AND ( businessLcuId is NULL)) THEN 
insert INTO tmp_table_business(
SELECT
	a.business_lcu_id AS column_x_y
FROM
	business_lcu a
WHERE
	a.is_delete = 1
AND a.rtu_id = rUid);
END IF;
IF (businessLcuId IS not NULL) THEN
  insert INTO tmp_table_business( column_x_y) VALUES( businessLcuId);
  END IF; 
 SELECT
        COUNT(a.workorder_id) INTO undisposed
        FROM
        workorder a
        WHERE
        is_deleted = 0  AND
        a.workorder_status_id IN(1,2) AND  EXISTS(SELECT * FROM tmp_table_business WHERE businessLcuId_x_y=a.workorder_device_id) ;
 SELECT
        COUNT(a.workorder_id) INTO disposing
        FROM
        workorder a
        WHERE
        is_deleted = 0  AND
        a.workorder_status_id IN(3,4,5) AND  EXISTS(SELECT * FROM tmp_table_businessWHERE column_x_y =a.workorder_device_id);
SELECT
        COUNT(a.workorder_id) INTO disposed
        FROM
        workorder a
        WHERE
        is_deleted = 0  AND
        a.workorder_status_id =6
       AND  EXISTS(SELECT * FROM tmp_table_business WHERE column_x_y =a.workorder_device_id);
SELECT
        COUNT(a.workorder_id) INTO total
        FROM
        workorder a
        WHERE
        is_deleted = 0 AND  EXISTS(SELECT * FROM tmp_table_business WHERE column_x_y =a.workorder_device_id);
   DROP TEMPORARY TABLE IF EXISTS column_x_y ;
   //将数据返回给java  没有它是行不通的
 SELECT total ,undisposed ,disposing ,disposed ;
END

java调用。这里用的是的mybatis注释方式，在dao接口上

/**
			 * @Author:XiaoXiaoHou
			 * @Date:2018/12/14
			 * @Description: 	调用存储过程获取统计值
			 * @Return ：
			 */
	@Options(statementType = StatementType.CALLABLE)
	@Select("call statiscStatusNum(" +
			"#{rUid,mode=IN,jdbcType = CHAR},  " +
			"#{businessLcuId,mode=IN,jdbcType=CHAR}," +
			"#{total ,jdbcType=BIGINT}, " +
			"#{undisposed, jdbcType=BIGINT}," +
			"#{disposing,jdbcType=BIGINT}," +
			"#{disposed, jdbcType=BIGINT},"+
			"#{projectId,mode=IN,jdbcType=BIGINT})")
	Map<String ,Long> callProcedure(Map<String,Object> map);

@Mapper 、@Options、@Select在（import org.apache.ibatis.annotations.*; ）里
statementType 在（import org.apache.ibatis.mapping.StatementType;）里

import org.apache.ibatis.annotations.*; 
import org.apache.ibatis.mapping.StatementType;
@Mapper
public interface WorkorderDao {
/**
			 * @Author:XiaoXiaoHou
			 * @Date:2018/12/14
			 * @Description: 	调用存储过程获取统计值
			 * @Return ：
			 */
	@Options(statementType = StatementType.CALLABLE)
	@Select("call statiscStatusNum(" +
			"#{rUid,mode=IN,jdbcType = CHAR},  " +
			"#{businessLcuId,mode=IN,jdbcType=CHAR}," +
			"#{total ,jdbcType=BIGINT}, " +
			"#{undisposed, jdbcType=BIGINT}," +
			"#{disposing,jdbcType=BIGINT}," +
			"#{disposed, jdbcType=BIGINT},"+
			"#{projectId,mode=IN,jdbcType=BIGINT})")
	Map<String ,Long> callProcedure(Map<String,Object> map);
	}

servece调用

/**
     * @Author:XiaoXiaoHou
     * @Date:2018/12/12
     * @Description: 统计工单已处理disposed（workroder_status_id = 6）、处理中disposing（workroder_status_id IN (3，4，5)）、
     * 未处理undisposed（workroder_status_id IN （1,2））以及工单总数total
     * @Return ：
     */
    public ModelAndView statiscStatusNum(Integer projectId, String rUid, String businessLcuId) {
        ModelAndView mav = getFail();
        Map<String, Object> params = new HashMap<>(16);
        //把过程中类型为IN的参数传递进去 类型为OUT的不用传递
        params.put("projectId", projectId);
        params.put("rUid", rUid);
        params.put("businessLcuId", businessLcuId);
        WorkorderStatusPercent wsp;
        Map<String, Long> result;
        try {
        //从result里获取从数据库查询得出的数据
            result = workorderDao.callProcedure(params);
            wsp = getPercent(result);
            mav = getMavWithAttributes(wsp);
        } catch (Exception e) {
            mav = getMavOnException();
            e.printStackTrace();
        }
        return mav;
    }