Mybatis 的几个奇技淫巧
2025-01-19 09:46:49 # Technical # Notes #Mybatis

表结构准备

准备一张任务表 task

1
2
3
4
5
6
7
create table task (
id int primary key auto_increment,
task_name varchar(255) not null,
task_status int default 0 not null,
task_create_time datetime DEFAULT CURRENT_TIMESTAMP not null,
task_update_time datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP not null
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT ='任务表';

再准备一张任务详情表 task_detail

1
2
3
4
5
create table task_detail (
id int primary key auto_increment,
task_id int not null,
task_info JSON not null
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT ='任务详情表';

往任务表内写入一些模拟数据

1
2
3
INSERT INTO task (id, task_name, task_status, task_create_time, task_update_time) VALUES (1, '任务001', 1, '2024-01-08 15:04:10', '2024-01-08 15:04:10');
INSERT INTO task (id, task_name, task_status, task_create_time, task_update_time) VALUES (2, '任务002', 1, '2024-01-08 15:04:10', '2024-01-08 15:04:10');
INSERT INTO task (id, task_name, task_status, task_create_time, task_update_time) VALUES (3, '任务003', 2, '2024-01-08 15:04:10', '2024-01-08 15:04:10');

往任务详情表内写入一些模拟数据

1
2
3
4
5
6
INSERT INTO task_detail (id, task_id, task_info) VALUES (1, 1, '{"taskId": 1, "taskInfo": "子任务1", "taskName": "任务001-01"}');
INSERT INTO task_detail (id, task_id, task_info) VALUES (2, 1, '{"taskId": 1, "taskInfo": "子任务2", "taskName": "任务001-02"}');
INSERT INTO task_detail (id, task_id, task_info) VALUES (3, 1, '{"taskId": 1, "taskInfo": "子任务3", "taskName": "任务001-03"}');
INSERT INTO task_detail (id, task_id, task_info) VALUES (4, 2, '{"taskId": 2, "taskInfo": "子任务1", "taskName": "任务002-01"}');
INSERT INTO task_detail (id, task_id, task_info) VALUES (5, 2, '{"taskId": 2, "taskInfo": "子任务2", "taskName": "任务002-02"}');
INSERT INTO task_detail (id, task_id, task_info) VALUES (6, 3, '{"taskId": 3, "taskInfo": "子任务1", "taskName": "任务003-01"}');

任务表中的一个任务可能会对应多个任务详情，创建对应的 POJO 类

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
@Data
public class TaskVO {

    private Long id;

    private String taskName;

    private Integer taskStatus;

    private List<TaskDetailVO> taskDetailList;

    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private LocalDateTime taskCreateTime;

    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private LocalDateTime taskUpdateTime;
}

任务详情中存在一个类型比较特殊字段，这里就直接用静态内部类来表示了

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
@Data
public class TaskDetailVO {

    private Long id;

    private Long taskId;

    private TaskInfo taskInfo;

    @Data
    public static class TaskInfo {
        private Long taskId;
        private String taskName;
        private String taskInfo;
    }
}

结果集嵌套

collection 指定列嵌套

Mapper 接口

1
List<TaskVO> selectTaskList();

Mapper xml

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
<resultMap id="BaseResultMap" type="com.venom.model.vo.TaskVO">
    <id column="id" property="id" jdbcType="INTEGER" />
    <result column="task_name" property="taskName" jdbcType="VARCHAR" />
    <result column="task_status" property="taskStatus" jdbcType="INTEGER" />
    <result column="task_create_time" property="taskCreateTime" jdbcType="TIMESTAMP" />
    <result column="task_update_time" property="taskUpdateTime" jdbcType="TIMESTAMP" />
    <collection property="taskDetailList" ofType="com.venom.model.vo.TaskDetailVO">
        <id column="d_id" property="id" jdbcType="INTEGER" />
        <result column="task_id" property="taskId" jdbcType="INTEGER" />
        <result column="task_info" property="taskInfo" typeHandler="com.venom.model.TaskInfoTypeHandler" />
    </collection>
</resultMap>

<select id="selectTaskList" resultMap="BaseResultMap">
    select
        t1.id, t1.task_name, t1.task_status,t1.task_create_time, t1.task_update_time,
        t2.id d_id, t2.task_id, t2.task_info
    from task t1 left join task_detail t2 on t1.id = t2.task_id
</select>

sql 日志：

1
2
3
4
5
6
7
8
9
10
==>  Preparing: select t1.id, t1.task_name, t1.task_status,t1.task_create_time, t1.task_update_time, t2.id d_id, t2.task_id, t2.task_info from task t1 left join task_detail t2 on t1.id = t2.task_id
==> Parameters: 
<==    Columns: id, task_name, task_status, task_create_time, task_update_time, d_id, task_id, task_info
<==        Row: 1, 任务001, 1, 2024-01-08 15:04:10, 2024-01-08 15:04:10, 3, 1, <<BLOB>>
<==        Row: 1, 任务001, 1, 2024-01-08 15:04:10, 2024-01-08 15:04:10, 2, 1, <<BLOB>>
<==        Row: 1, 任务001, 1, 2024-01-08 15:04:10, 2024-01-08 15:04:10, 1, 1, <<BLOB>>
<==        Row: 2, 任务002, 1, 2024-01-08 15:04:10, 2024-01-08 15:04:10, 5, 2, <<BLOB>>
<==        Row: 2, 任务002, 1, 2024-01-08 15:04:10, 2024-01-08 15:04:10, 4, 2, <<BLOB>>
<==        Row: 3, 任务003, 2, 2024-01-08 15:04:10, 2024-01-08 15:04:10, 6, 3, <<BLOB>>
<==      Total: 6

查询结果：

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
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
[
    {
        "id": 1,
        "taskName": "任务001",
        "taskStatus": 1,
        "taskDetailList": [
            {
                "id": 3,
                "taskId": 1,
                "taskInfo": {
                    "taskId": 1,
                    "taskName": "任务001-03",
                    "taskInfo": "子任务3"
                }
            },
            {
                "id": 2,
                "taskId": 1,
                "taskInfo": {
                    "taskId": 1,
                    "taskName": "任务001-02",
                    "taskInfo": "子任务2"
                }
            },
            {
                "id": 1,
                "taskId": 1,
                "taskInfo": {
                    "taskId": 1,
                    "taskName": "任务001-01",
                    "taskInfo": "子任务1"
                }
            }
        ],
        "taskCreateTime": "2024-01-08 15:04:10",
        "taskUpdateTime": "2024-01-08 15:04:10"
    },
    {
        "id": 2,
        "taskName": "任务002",
        "taskStatus": 1,
        "taskDetailList": [
            {
                "id": 5,
                "taskId": 2,
                "taskInfo": {
                    "taskId": 2,
                    "taskName": "任务002-02",
                    "taskInfo": "子任务2"
                }
            },
            {
                "id": 4,
                "taskId": 2,
                "taskInfo": {
                    "taskId": 2,
                    "taskName": "任务002-01",
                    "taskInfo": "子任务1"
                }
            }
        ],
        "taskCreateTime": "2024-01-08 15:04:10",
        "taskUpdateTime": "2024-01-08 15:04:10"
    },
    {
        "id": 3,
        "taskName": "任务003",
        "taskStatus": 2,
        "taskDetailList": [
            {
                "id": 6,
                "taskId": 3,
                "taskInfo": {
                    "taskId": 3,
                    "taskName": "任务003-01",
                    "taskInfo": "子任务1"
                }
            }
        ],
        "taskCreateTime": "2024-01-08 15:04:10",
        "taskUpdateTime": "2024-01-08 15:04:10"
    }
]

这里需要注意的是，task 与 task_detail 中都存在同名列 id，所以 task_detail 中的 id 需要使用别名

collection 指定 ResultMap 嵌套

除此之外，还有一种情况是，直接嵌套另外已存在的 ResutlMap

Mapper 接口与上相同

xml 如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
<resultMap id="BaseResultMap" type="com.venom.model.vo.TaskVO">
    <id column="id" property="id" jdbcType="INTEGER" />
    <result column="task_name" property="taskName" jdbcType="VARCHAR" />
    <result column="task_status" property="taskStatus" jdbcType="INTEGER" />
    <result column="task_create_time" property="taskCreateTime" jdbcType="TIMESTAMP" />
    <result column="task_update_time" property="taskUpdateTime" jdbcType="TIMESTAMP" />
    <collection property="taskDetailList" ofType="com.venom.model.vo.TaskDetailVO" resultMap="TaskDetailResultMap" columnPrefix="d_" />
</resultMap>

<resultMap id="TaskDetailResultMap" type="com.venom.model.vo.TaskDetailVO">
    <id column="id" property="id" jdbcType="INTEGER" />
    <result column="task_id" property="taskId" jdbcType="INTEGER" />
    <result column="task_info" property="taskInfo" typeHandler="com.venom.model.TaskInfoTypeHandler" />
</resultMap>

<select id="selectTaskList" resultMap="BaseResultMap">
    select
        t1.id, t1.task_name, t1.task_status,t1.task_create_time, t1.task_update_time,
        t2.id d_id, t2.task_id d_task_id, t2.task_info d_task_info
    from task t1 left join task_detail t2 on t1.id = t2.task_id
</select>

resultMap 指定嵌套的 resultMap，columnPrefix 添加别名前缀

查询结果与上相同

association 嵌套

这里顺带提以下 association 嵌套，不同于 collection 嵌套的一对多，association 嵌套针对于一对一，主要用于映射单个对象，collection 可以理解为映射多个对象的集合

先准备一张与 task 一对一关联的 task_name_detail 表

1
2
3
4
5
6
create table task_name_detail(
id int primary key auto_increment,
task_name varchar(255) not null,
description varchar(255) not null,
UNIQUE KEY `uidx_task_name` (`task_name`) USING BTREE
) ENGINE = InnoDB DEFAULT CHARSET = utf8mb4 COMMENT ='任务名详情表';

插入几条数据：

1
2
3
INSERT INTO db1.task_name_detail (task_name,description) VALUES ('任务001','第一条任务');
INSERT INTO db1.task_name_detail (task_name,description) VALUES ('任务002','第二条任务');
INSERT INTO db1.task_name_detail (task_name,description) VALUES ('任务003','第三条任务');

创建对应 POJO

1
2
3
4
5
6
7
8
9
@Data
public class TaskNameDetailVO {

    private Long id;

    private String taskName;

    private String description;
}

引入到 TaskVO 中：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
@Data
public class TaskVO {

    private Long id;

    private String taskName;

    private Integer taskStatus;

    private List<TaskDetailVO> taskDetailList;

    private TaskNameDetailVO taskNameDetailVO;

    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private LocalDateTime taskCreateTime;

    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss", timezone = "GMT+8")
    private LocalDateTime taskUpdateTime;
}

新增 Mapper 接口：

1
List<TaskVO> selectTaskList3();

新增对应的 xml 查询：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
<resultMap id="BaseResultMap3" type="com.venom.model.vo.TaskVO">
    <id column="id" property="id" jdbcType="INTEGER" />
    <result column="task_name" property="taskName" jdbcType="VARCHAR" />
    <result column="task_status" property="taskStatus" jdbcType="INTEGER" />
    <result column="task_create_time" property="taskCreateTime" jdbcType="TIMESTAMP" />
    <result column="task_update_time" property="taskUpdateTime" jdbcType="TIMESTAMP" />
    <!-- 使用 association 时，必须指定 javaType -->
    <association property="taskNameDetailVO" javaType="com.venom.model.vo.TaskNameDetailVO">
        <result column="d_id" property="id" jdbcType="INTEGER" />
        <result column="d_task_name" property="taskName" jdbcType="VARCHAR" />
        <result column="description" property="description" jdbcType="VARCHAR" />
    </association>
</resultMap>

<select id="selectTaskList3" resultMap="BaseResultMap3">
    select
        t1.id, t1.task_name, t1.task_status,t1.task_create_time, t1.task_update_time,
        t2.id d_id, t2.task_name d_task_name, t2.description
    from task t1 left join task_name_detail t2 on t1.task_name = t2.task_name
</select>

与 collection 标签不同的是，association 标签没有 ofType 属性，并且必须指定 javaType 属性

子查询嵌套

与上面的结果集嵌套类似，也是用于关系的映射，写起来较结果集嵌套也会更加简单，不过 存在性能损耗，会导致 1+N 问题，所以通常不建议使用

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
<resultMap id="BaseResultMap2" type="com.venom.model.vo.TaskVO">
    <id column="id" property="id" jdbcType="INTEGER" />
    <result column="task_name" property="taskName" jdbcType="VARCHAR" />
    <result column="task_status" property="taskStatus" jdbcType="INTEGER" />
    <result column="task_create_time" property="taskCreateTime" jdbcType="TIMESTAMP" />
    <result column="task_update_time" property="taskUpdateTime" jdbcType="TIMESTAMP" />
    <!-- 子查询，通过 column 属性指定关联字段: {prop1=col1,prop2=col2} -->
    <collection property="taskDetailList" ofType="com.venom.model.vo.TaskDetailVO" 
                select="selectTaskDetailByTaskId" column="{id=id}">
    </collection>
</resultMap>

<resultMap id="TaskDetailResultMap" type="com.venom.model.vo.TaskDetailVO">
    <id column="id" property="id" jdbcType="INTEGER" />
    <result column="task_id" property="taskId" jdbcType="INTEGER" />
    <result column="task_info" property="taskInfo" typeHandler="com.venom.model.TaskInfoTypeHandler" />
</resultMap>

<select id="selectTaskList2" resultMap="BaseResultMap2">
    select * from task
</select>

<select id="selectTaskDetailByTaskId" resultMap="TaskDetailResultMap">
    select * from task_detail where task_id = #{id}
</select>

主查询为 selectTaskList2 子查询为 selectTaskDetailByTaskId，主查询没查询一条结果就会将指定的参数传递给子查询，子查询再查出与之对应的记录，所以查询的次数会是 1 条主查询 + N 条主查询记录对应的子查询

JSON 类型的映射

前面在创建示例表结构和数据的时候就能发现，里面有特殊的 JSON 字段，JSON 字段在增删改查的过程中的对象关系映射是比较特殊的。原理先不分析，先弄清楚使用方式

先创建一个基础的 JSON 对象处理抽象类：

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
29
public abstract class AbstractJsonTypeHandler<T> extends BaseTypeHandler<T> {

    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, T parameter, JdbcType jdbcType) throws SQLException {
        ps.setString(i, this.toJson(parameter));
    }

    @Override
    public T getNullableResult(ResultSet rs, String columnName) throws SQLException {
        String json = rs.getString(columnName);
        return StringUtils.isBlank(json) && rs.wasNull() ? null : this.parse(json);
    }

    @Override
    public T getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        String json = rs.getString(columnIndex);
        return StringUtils.isBlank(json) && rs.wasNull() ? null : this.parse(json);
    }

    @Override
    public T getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        String json = cs.getString(columnIndex);
        return StringUtils.isBlank(json) && cs.wasNull() ? null : this.parse(json);
    }

    protected abstract T parse(String jsonStr);

    protected abstract String toJson(T obj);
}

如果项目中使用了 Mybatis-plus 的话，可以不用创建这个抽象类，直接使用 Mybatis-plus 提供的 com.baomidou.mybatisplus.extension.handlers.AbstractJsonTypeHandler

然后针对具体的 JSON 对象创建对应的处理类，继承上面的抽象类，实现其中抽象的序列化与反序列化方法即可：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
public class TaskInfoTypeHandler extends AbstractJsonTypeHandler<TaskDetailVO.TaskInfo> {

    @Override
    protected TaskDetailVO.TaskInfo parse(String json) {
        try {
            if (json == null || json.isEmpty()) {
                return null;
            }
            return JsonUtil.parseObject(json, TaskDetailVO.TaskInfo.class);
        } catch (Exception e) {
            throw new RuntimeException("Error parsing JSON to TaskDetailVO.TaskDetail: " + e.getMessage(), e);
        }
    }

    @Override
    protected String toJson(TaskDetailVO.TaskInfo obj) {
        try {
            return JsonUtil.toJsonString(obj);
        } catch (Exception e) {
            throw new RuntimeException("Error converting TaskDetailVO.TaskDetail to JSON: " + e.getMessage(), e);
        }
    }
}

这里使用的是 Jackson，也可以使用 Fastjson，后面再附上一个 Jackson 的工具类：

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
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
@Slf4j
public final class JsonUtil {

    private JsonUtil() {}

    private static final ObjectMapper OBJECT_MAPPER = Jackson2ObjectMapperBuilder.json()
            .modulesToInstall(new JavaTimeModule())
        	// 针对 LocalDate、LocalDatetime 处理
            .featuresToDisable(SerializationFeature.WRITE_DATES_AS_TIMESTAMPS)
            .build();

    public static <T> T parseObject(String jsonStr, Class<T> clazz) {
        if (!StringUtils.hasText(jsonStr)) return null;
        try {
            return OBJECT_MAPPER.readValue(jsonStr, clazz);
        } catch (JsonProcessingException e) {
            throw new RuntimeException(e);
        }
    }

    public static <T> List<T> parseArray(String jsonStr) {
        if (!StringUtils.hasText(jsonStr)) return new ArrayList<>();
        try {
            return OBJECT_MAPPER.readValue(jsonStr, new TypeReference<List<T>>(){});
        } catch (JsonProcessingException e) {
            throw new RuntimeException(e);
        }
    }

    public static Map<String, Object> parseMap(String jsonStr) {
        if (!StringUtils.hasText(jsonStr)) return new HashMap<>();
        try {
            return OBJECT_MAPPER.readValue(jsonStr, new TypeReference<Map<String, Object>>(){});
        } catch (JsonProcessingException e) {
            throw new RuntimeException(e);
        }
    }

    public static String toJsonString(Object obj) {
        if (Objects.isNull(obj)) return "";
        try {
            return OBJECT_MAPPER.writeValueAsString(obj);
        } catch (JsonProcessingException e) {
            throw new RuntimeException(e);
        }
    }
}

然后就是 Mybatis 的 xml 中对 JSON 对象指定我们创建的处理类：

1
2
3
4
5
<resultMap id="TaskDetailResultMap" type="com.venom.model.vo.TaskDetailVO">
    <id column="id" property="id" jdbcType="INTEGER" />
    <result column="task_id" property="taskId" jdbcType="INTEGER" />
    <result column="task_info" property="taskInfo" typeHandler="com.venom.model.TaskInfoTypeHandler" />
</resultMap>

特别提示：这里有个容易踩坑的地方，在 resultMap 中指定 typeHandler 属性时需要加引号，但是在注入值的时候指定是不需要引号的！

1
#{taskInfo,typeHandler=com.venom.model.TaskInfoTypeHandler}
Prev
2025-01-19 09:46:49 # Technical # Notes #Mybatis
Next