以Oracle数据库为例，通过两个示例演示在MyBatis中如果调用存储过程。
示例1：事务处理型
比如某个存储过程的事务处理是更新订单状态：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
| CREATE OR REPLACE PROCEDURE PRO_UPDATE_ORDER_STATUS (
O_RET_CODE OUT NUMBER
O_RET_MSG OUT VARCHAR2,
I_ORDER_ID IN NUMBER,
I_ORDER_STATUS IN VARCHAR2
)
BEGIN
UPDATE T_ORDER SET ORDER_STATUS=I_ORDER_STATUS WHERE ID=I_ORDER_ID;
COMMIT;
O_RET_CODE := 1;
O_RET_MSG := '执行成功';
EXCEPTION
WHEN OTHERS THEN
O_RETCODE := -1;
O_RET_MSG := '执行失败:'||SQLERRM;
END;
|
Mapper文件中的写法：
1
2
3
4
5
6
7
8
9
|
@Mapper
public interface OrderDao {
void updateOrderStatus(Map<String, Object> map);
}
|
Service中的写法：
1
2
3
4
5
6
7
8
9
|
public void updateOrderStatus() {
Map<String, Object> procedureParam = new HashMap<>();
procedureParam.put("I_ORDER_ID", 1000);
procedureParam.put("I_ORDER_STATUS", "已支付");
orderDao.updateOrderStatus(procedureParam);
log.info("订单更新存储过程执行结果：{}", JSONUtil.toJsonStr(procedureParam));
}
|
XML文件中的写法：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
<?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.lzumetal.springboot.mybatis.dao.OrderDao">
<parameterMap id="updateOrderStatusMap" type="java.util.Map">
<parameter javaType="java.lang.Integer" property="O_RET_CODE" mode="OUT" jdbcType="INTEGER"/>
<parameter javaType="java.lang.String" property="O_RET_MSG" mode="OUT" jdbcType="VARCHAR"/>
<parameter javaType="java.lang.Integer" property="I_ORDER_ID" mode="IN" jdbcType="INTEGER"/>
<parameter javaType="java.lang.String" property="I_ORDER_STATUS" mode="IN" jdbcType="VARCHAR"/>
</parameterMap>
<select id="updateOrderStatus" statementType="CALLABLE" parameterMap="updateOrderStatusMap">
{ CALL PRO_UPDATE_ORDER_STATUS(?,?,?,?) }
</select>
</mapper>
|
示例2：查询型
比如某个存储过程是查询用户的所有订单
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
| CREATE OR REPLACE PROCEDURE PRO_QUERY_ORDER_BY_USER (
CUR_RECORD OUT TYPES.CURSORTYPE,
I_USER_ID IN NUMBER
)
BEGIN
O_RET_MSG VARCHAR2(500);
OPEN CUR_RECORD FOR
SELECT * FROM T_ORDER WHERE USER_ID=I_USER_ID;
RET_MSG := '查询成功';
EXCEPTION
WHEN OTHERS THEN
O_RET_MSG := SQLERRM;
OPEN CUR_RECORD FOR
SELECT '查询失败-' || O_RET_MSG AS 错误 FROM DUAL;
END;
|
Mapper文件中的写法：
1
2
3
4
5
6
7
8
9
|
@Mapper
public interface OrderDao {
void queryByUser(Map<String, Object> map);
}
|
Service中的写法：
1
2
3
4
5
6
7
8
|
public void queryByUser() {
Map<String, Object> procedureParam = new HashMap<>();
procedureParam.put("I_USER_ID", 999);
orderDao.queryByUser(procedureParam);
log.info("查询用户订单存储过程执行结果：{}", JSONUtil.toJsonStr(procedureParam.get("CUR_RECORD")));
}
|
XML文件中的写法：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
<resultMap id="queryByUserResultMap" type="com.lzumetal.springboot.mybatis.entity.Order">
<result column="ID" property="id" />
<result column="USER_ID" property="userId" />
<result column="ORDER_STATUS" property="orderStatus" />
<result column="CREATE_TIME" property="createTime" />
</resultMap>
<parameterMap id="queryByUserMap" type="java.util.Map">
<parameter javaType="ResultSet" property="CUR_RECORD" mode="OUT" jdbcType="CURSOR" resultMap="queryByUserResultMap"/>
<parameter property="I_USER_ID" mode="IN" jdbcType="INTEGER"/>
</parameterMap>
<select id="queryByUser" statementType="CALLABLE" parameterMap="queryByUserMap">
{CALL queryByUser(?,?)}
</select>
|