Mysql存储过程调用
前言
博主github
博主个人博客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());
}
}
}
感兴趣的，欢迎添加博主微信
哈，博主很乐意和各路好友交流，如果满意，请打赏博主任意金额，感兴趣的在微信转账的时候，备注您的微信或者其他联系方式。添加博主微信哦。
请下方留言吧。可与博主自由讨论哦
| 微信 | 微信公众号 | 支付宝 |
|---|---|---|
 |
 |
 |
