前言

Github:https://github.com/HealerJean

博客:http://blog.healerjean.com

准备数据

CREATE TABLE `demo_entity` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `age` bigint(20) DEFAULT '0',
  `cdate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `udate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

1、存储过程的调用

存储过程主要返回三类

1、返回数值得的存储过程,其执行完后返回一个值,例如数据库中执行一个有返回值的函数或命令。

2、返回记录集的存储过程:执行结果是一个记录集,例如,从数据库中检索出符合某一个或几个条件的记录。

3、行为存储过程,用来实现数据库的某个功能,而没有返回值,例如在数据库中的更新和删除操作。

1、接收一个返回值

1.1、创建存储过程

1.1、返回out变量


DROP PROCEDURE IF EXISTS pro;
CREATE PROCEDURE pro (
  IN userId INT,
  OUT userCount INT
)
  BEGIN
    DECLARE user_name VARCHAR (64);
    SELECT d.name FROM demo_entity d WHERE d.id = userId INTO user_name;
    INSERT INTO demo_entity (name) VALUES (user_name);
    SELECT COUNT(*) FROM demo_entity INTO userCount;
end;

call pro(1,@userCount);
select @userCount ;


1.2、返回集合和out变量



CREATE PROCEDURE pro_one_list (
  IN p_name varchar(20),
  OUT userCount INT
)
  BEGIN
    select count(*) from demo_entity d where d.name = p_name into userCount ;
    SELECT * FROM demo_entity d  where  d.name = p_name;
  end;


call pro_one_list('HealerJean',@userCount);
select @userCount ;


1.3、返回多列集合和变量


drop procedure  pro_many_list ;
CREATE PROCEDURE pro_many_list (
  IN one_name varchar(20),
  IN two_name varchar(20),
  OUT userCount INT
)
  BEGIN
    select count(*) from demo_entity d where d.name = one_name into userCount ;
    SELECT d.* FROM demo_entity d  where  d.name = one_name;
    SELECT e.* FROM demo_entity e  where  e.name = two_name;
  end;


call pro_many_list('HealerJean','Healer',@userCount);
select @userCount ;

1.2、mybatis xml 配置

out 中必须制定jdbc类型,因为存储过程会对它进行赋值操作


   <?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.hlj.dao.mybatis.demo.DemoEntityMapper">




    <!--out 中必须制定jdbc类型,因为存储过程会对它进行赋值操作-->
    <select id="procedureGetOut" parameterType="java.util.Map" statementType="CALLABLE" >
      call pro (
              #{userId,   mode=IN},
              #{userCount,mode=OUT,jdbcType=INTEGER}
      )
  </select>



    <select id="procedureGetOneList" parameterType="java.util.Map" statementType="CALLABLE" resultType="com.hlj.entity.db.demo.DemoEntity" >
      call pro_one_list (
              #{userName, mode=IN},
              #{userCount,mode=OUT,jdbcType=INTEGER}
      )
  </select>




    <resultMap id="twoList" type="com.hlj.entity.db.demo.DemoEntity">
        <result column="id" property="id"></result>
        <result column="name" property="name"></result>
    </resultMap>

    <resultMap id="oneList" type="com.hlj.entity.db.demo.DemoEntity">
        <result column="id" property="id"></result>
        <result column="name" property="name"></result>
    </resultMap>

    <!--resultMap中会提示报错,但是实际上启动项目不会报错-->
    <select id="procedureGetManyList" parameterType="java.util.Map" statementType="CALLABLE" resultMap="oneList,twoList" >
      call pro_many_list (
              #{oneName, mode=IN},
              #{twoName, mode=IN},
              #{userCount,mode=OUT,jdbcType=INTEGER}
      )
   </select>




</mapper>  

1.3、mybatis混


package com.hlj.dao.mybatis.demo;

import com.hlj.entity.db.demo.DemoEntity;
import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.Map;

/**
 * 作者 :HealerJean
 * 日期 :2018/11/14  下午12:54.
 * 类描述:
 */
public interface DemoEntityMapper {



    /**
     * 1、获取 存储过程out参数值
     * @param map
     * @return
     */
    void procedureGetOut(Map map);


    /**
     * 2、获取 存储过程的结果集合-只有一个
     * @param map
     * @return
     */
    List<DemoEntity> procedureGetOneList(Map map) ;



    /**
     * 3、获取 存储过程的 获取多个集合 使用了泛型,有可能集合中是不同的
     * @param
     * @return
     */
    List<List<?>> procedureGetManyList(Map map);


}

1.4、服务层



    /**
     * 1、获取 存储过程out参数值
     * @param id
     * @return
     */
    @Override
    public Integer procedureGetOut(Long id) {
        Map<String, Object> param = new HashMap<>();
        param.put("userId", id) ;
        //执行完存储过程会自动更新这个map值
        demoEntityMapper.procedureGetOut(param);
        return Integer.valueOf(param.get("userCount").toString());
    }

    /**
     * 2、获取 存储过程的结果集合-只有一个
     * @param name
     * @return
     */
    @Override
    public List<DemoEntity> procedureGetOneList(String name) {
        Map<String, Object> param = new HashMap<>();
        param.put("userName", name) ;

        //获取结果集
        List<DemoEntity> list  = demoEntityMapper.procedureGetOneList(param);
        System.out.println(Integer.valueOf(param.get("userCount").toString()));

        return list;
    }

    /**
     * 3、获取 存储过程的 获取多个集合 使用了泛型,有可能集合中是不同的
     * @param
     * @return
     */
    @Override
    public List<List<?>> procedureGetManyList(String oneName, String twoName) {
        Map<String, Object> param = new HashMap<>();
        param.put("oneName", oneName) ;
        param.put("twoName", twoName) ;
        List<List<?>> lists =   demoEntityMapper.procedureGetManyList(param) ;
        System.out.println("数组大小"+lists.size());
        System.out.println("某个数量"+Integer.valueOf(param.get("userCount").toString()));
        return lists;
    }
    
    

1.5、controller测试


package com.hlj.moudle.produce;


import com.hlj.data.general.AppException;
import com.hlj.data.general.ResponseBean;
import com.hlj.entity.db.demo.DemoEntity;
import com.hlj.moudle.produce.service.ProcedureService;
import com.hlj.utils.ExceptionLogUtils;
import io.swagger.annotations.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.MediaType;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

/**
 * @Description
 * @Author HealerJean
 * @Date 2018/3/22  上午10:22.
 */
@ApiResponses(value = {
        @ApiResponse(code = 200, message = "访问正常"),
        @ApiResponse(code = 301, message = "逻辑错误"),
        @ApiResponse(code = 500, message = "系统错误"),
        @ApiResponse(code = 401, message = "未认证"),
        @ApiResponse(code = 403, message = "禁止访问"),
        @ApiResponse(code = 404, message = "url错误")
})
@Api(description = "存储过程控制器")
@Controller
@RequestMapping("demo")
public class ProcedureController {


    @Autowired
    private ProcedureService procedureService;

    @ApiOperation(notes = "存储过程out结果获取",
            value = "存储过程out结果获取",
            consumes = MediaType.APPLICATION_FORM_URLENCODED_VALUE,
            produces = MediaType.APPLICATION_JSON_VALUE,
            response = DemoEntity.class)
    @ApiImplicitParams({
            @ApiImplicitParam(name = "id",value = "demo主键",required = true,paramType = "query",dataType = "long"),
    })
    @GetMapping("procedureGetOut")
    @ResponseBody
    public ResponseBean  procedureGetOut(Long id){
        try {
            return ResponseBean.buildSuccess(procedureService.procedureGetOut(id));
        }catch (AppException e){
            ExceptionLogUtils.log(e,this.getClass() );
            return  ResponseBean.buildFailure(e.getCode(),e.getMessage());
        }
        catch (Exception e){
            ExceptionLogUtils.log(e,this.getClass() );
            return  ResponseBean.buildFailure(e.getMessage());
        }
    }


    @ApiOperation(notes = "获取返回结果集",
            value = "获取返回结果集",
            consumes = MediaType.APPLICATION_FORM_URLENCODED_VALUE,
            produces = MediaType.APPLICATION_JSON_VALUE,
            response = DemoEntity.class)
    @ApiImplicitParams({
            @ApiImplicitParam(name = "name",defaultValue = "HealerJean", value = "比如 HealerJean",required = true,paramType = "query",dataType = "long"),
    })
    @GetMapping("procedureGetOneList")
    @ResponseBody
    public ResponseBean  procedureGetOneList(String name){
        try {
            return ResponseBean.buildSuccess(procedureService.procedureGetOneList(name));
        }catch (AppException e){
            ExceptionLogUtils.log(e,this.getClass() );
            return  ResponseBean.buildFailure(e.getCode(),e.getMessage());
        }
        catch (Exception e){
            ExceptionLogUtils.log(e,this.getClass() );
            return  ResponseBean.buildFailure(e.getMessage());
        }
    }


    @ApiOperation(notes = "获取返回结果集(多个)",
            value = "获取返回结果集(多个)",
            consumes = MediaType.APPLICATION_FORM_URLENCODED_VALUE,
            produces = MediaType.APPLICATION_JSON_VALUE,
            response = DemoEntity.class)
    @ApiImplicitParams({
            @ApiImplicitParam(name = "oneName",defaultValue = "HealerJean", value = "比如 HealerJean",required = true,paramType = "query",dataType = "long"),
            @ApiImplicitParam(name = "twoName",defaultValue = "Healer", value = "比如 HealerJean",required = true,paramType = "query",dataType = "long"),
    })
    @GetMapping("procedureGetManyList")
    @ResponseBody
    public ResponseBean  procedureGetManyList(String oneName,String twoName){
        try {
            return ResponseBean.buildSuccess(procedureService.procedureGetManyList(oneName,twoName));
        }catch (AppException e){
            ExceptionLogUtils.log(e,this.getClass() );
            return  ResponseBean.buildFailure(e.getCode(),e.getMessage());
        }
        catch (Exception e){
            ExceptionLogUtils.log(e,this.getClass() );
            return  ResponseBean.buildFailure(e.getMessage());
        }
    }



}

ContactAuthor