有时候在程序开发中,需要用到存储过程,这里讲解了在Springboot的项目下,Mybatis如何调用存储过程,以及接收存储过程返回的多个结果集
Exam表结构
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for exam
-- ----------------------------
DROP TABLE IF EXISTS `exam`;
CREATE TABLE `exam` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`ordernum` int(11) NULL DEFAULT NULL,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL ,
`starttime` datetime(0) NULL DEFAULT NULL,
`endtime` datetime(0) NULL DEFAULT NULL ,
`createtime` timestamp(0) NULL DEFAULT CURRENT_TIMESTAMP(0),
`passfs` decimal(6, 2) NULL DEFAULT NULL ,
`fscount` decimal(6, 2) NULL DEFAULT 0.00 ,
`timucount` int(11) NULL DEFAULT 0 ,
`isenabled` int(11) NULL DEFAULT NULL ,
`kaoshitime` int(11) NULL DEFAULT 0,
`ownerid` int(11) NULL DEFAULT NULL ,
`password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`isck` int(3) NULL DEFAULT 1 ,
`cknum` int(11) NULL DEFAULT 0 ,
`examtype` int(3) NULL DEFAULT NULL ,
`istry` int(3) NULL DEFAULT 2 ,
`isbackup` int(3) NULL DEFAULT 0 ,
PRIMARY KEY (`ID`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;
SET FOREIGN_KEY_CHECKS = 1;
Jigou表结构
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for jigou
-- ----------------------------
DROP TABLE IF EXISTS `jigou`;
CREATE TABLE `jigou` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`jgname` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`address` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`lxname` varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`phone` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`password` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`ID`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
SET FOREIGN_KEY_CHECKS = 1;
写一个简单的测试用的存储过程,这个存储过程有一个入参,还有一个出参,并且还会返回两个结果集
CREATE DEFINER=`root`@`localhost` PROCEDURE `mytest`(IN in_ownerid INT,OUT examcount INT)
BEGIN
select * from exam where ownerid=in_ownerid ;
select count(*) into examcount from exam where ownerid=in_ownerid;
select * from jigou;
END
TestMapper.java
@Repository
public interface TestMapper {
List<List<?>> protest(Map<String,Object> map);
}
TestMapper.xml
<?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.model.proceduredemo.dao.TestMapper">
<resultMap id="ExamMap" type="com.model.proceduredemo.Pojo.Exam" >
<id column="ID" property="id" jdbcType="INTEGER" />
<result column="ordernum" property="ordernum" jdbcType="INTEGER" />
<result column="name" property="name" jdbcType="VARCHAR" />
<result column="starttime" property="starttime" jdbcType="TIMESTAMP" />
<result column="endtime" property="endtime" jdbcType="TIMESTAMP" />
<result column="createtime" property="createtime" jdbcType="TIMESTAMP" />
<result column="passfs" property="passfs" jdbcType="DECIMAL" />
<result column="fscount" property="fscount" jdbcType="DECIMAL" />
<result column="timucount" property="timucount" jdbcType="INTEGER" />
<result column="isenabled" property="isenabled" jdbcType="INTEGER" />
<result column="kaoshitime" property="kaoshitime" jdbcType="INTEGER" />
<result column="ownerid" property="ownerid" jdbcType="INTEGER" />
<result column="password" property="password" jdbcType="VARCHAR" />
<result column="isck" property="isck" jdbcType="INTEGER" />
<result column="cknum" property="cknum" jdbcType="INTEGER" />
<result column="examtype" property="examtype" jdbcType="INTEGER" />
<result column="istry" property="istry" jdbcType="INTEGER" />
<result column="isbackup" property="isbackup" jdbcType="INTEGER" />
</resultMap>
<resultMap id="JigouMap" type="com.model.proceduredemo.Pojo.Jigou" >
<id column="ID" property="id" jdbcType="INTEGER" />
<result column="jgname" property="jgname" jdbcType="VARCHAR" />
<result column="address" property="address" jdbcType="VARCHAR" />
<result column="lxname" property="lxname" jdbcType="VARCHAR" />
<result column="phone" property="phone" jdbcType="VARCHAR" />
<result column="password" property="password" jdbcType="VARCHAR" />
</resultMap>
<select id="protest" resultMap="ExamMap,JigouMap" parameterType="Map"
statementType="CALLABLE" >
{CALL mytest(#{ownerid,mode=IN,jdbcType=INTEGER},#{examcount,mode=OUT,jdbcType=INTEGER})}
</select>
</mapper>
TestServiceImpl.java(TestService接口略)
@Service
public class TestServiceImpl implements TestService {
@Autowired
private TestMapper testMapper;
@Override
public void mytest() throws JsonProcessingException {
Map<String,Object> map=new HashMap<>();
map.put("ownerid",1);
List<List<?>> list= testMapper.protest(map);
ObjectMapper objectMapper=new ObjectMapper();
System.out.println("结果集一:"+objectMapper.writeValueAsString(list.get(0)));
System.out.println("结果集二:"+objectMapper.writeValueAsString(list.get(1)));
System.out.println("出参参数值:"+map.get("examcount"));
return RetResponse.makeOkRsp(list);
}
}
MyController.java
@RestController
public class MyController {
@Autowired
private TestService testService;
@RequestMapping("/test")
public void test() throws JsonProcessingException {
testService.mytest();
}
}
请求的结果如下图所示(因为返回的查询结果转换成JSON字符串显示在控制台,字符串太长了这里只显示了部分):
拓展:
从上面可以看出,在Mapper.xml的映射文件定义了两个resultMap标签,是因为在这里存储过程返回了两个结果集,一个是根据入参参数查询exam表返回的查询结果,还有一个是查询Jigou表返回的查询结果,所以要写两个相应的resultMap标签来接收存储过程返回的结果集。
但是这样编写resultMap标签十分麻烦,因为要把JAVA对象里的属性和MySql表中的字段一一对应映射起来,所以接下来介绍一种简便的方法来接收返回的结果集
其他代码基本不变,只修改Mapper.xml映射文件
TestMapper.xml
<?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.model.proceduredemo.dao.TestMapper">
<resultMap id="Map1" type="java.util.HashMap">
</resultMap>
<resultMap id="Map2" type="java.util.HashMap">
</resultMap>
<select id="protest" resultMap="Map1,Map2" parameterType="Map"
statementType="CALLABLE" >
{CALL mytest(#{ownerid,mode=IN,jdbcType=INTEGER},
#{examcount,mode=OUT,jdbcType=INTEGER})}
</select>
</mapper>
然后测试查看结果:
结果没变化,只是字段的顺序产生了变化,因为HashMap类型是无序的。
由上可知,返回的结果集直接使用HashMap接收方便很多,不用进行JAVA对象属性跟Mysql表的字段映射
总结:
- 调用存储过程时,Mapper.xml映射文件中select标签中的statementType属性必须声明为CALLABLE。
- 因为存储过程有出参和入参,所以在Mapper接口中参数最好使用Map<String,Object>类型,并且要在select标签中的parameterType属性声明为Map,因为这样不仅能够传入入参的参数,还能够获得出参的参数。
- 有几个结果集,那就定义几个resultMap标签来接收,然后在select标签中resultMap属性声明为上面定义的resultMap标签,多个之间用逗号分开。并且在Mapper接口中把返回值声明为List<List<?>>的泛型来接收。
(这里解释一下List<List<?>>类型,最外面的List表明有几个结果集,里面的List表明该结果集有多少条数据。最后里面这个?表示泛型,也就是用Object接收,如果采取拓展方法来接收结果集的话,那么这个?可以替换成Map<String,Object>) - 在Mapper.xml映射文件中,调用存储过程时,参数名不用写的和存储过程定义的参数名一样,只要顺序对上即可。