Mybatis技术原理详解之：使用Mapper形式和注解驱动的复杂映射开发
Mapper形式的复杂映射开发
一对一查询
一对一查询的模型
⽤户表和订单表的关系为，⼀个⽤户有多个订单，⼀个订单只从属于⼀个⽤户
⼀对⼀查询的需求：查询⼀个订单，与此同时查询出该订单所属的⽤户
建表语句
create table user
(
`id` int(11),
`username` varchar(30),
`password` varchar(30),
`birthday` varchar(30)
)
insert into user values (1, 'lisi', '123', '2023-11-01');
insert into user values (2, 'zhangsan', '123', '2023-11-01');
create table `order`
(
`id` int(11),
`order_time` datetime,
`total` float,
`uid` int
)
insert into `order` values (1, '2023-11-03 12:00:00', 10.0, 1);
insert into `order` values (2, '2023-11-03 12:00:00', 20.0, 1);
insert into `order` values (3, '2023-11-03 12:00:00', 20.0, 2);
构造查询语句
select *, o.id oid from `user` u left join `order` o on u.id = o.uid
查询结果如下所示:
!
创建Order和User实体
@ToString
@Data
public class Order {
private int id;
private Date orderTime;
private double total;
// 标识每个订单对应的唯一用户
private User user;
}
@ToString
@Data
public class User {
private int id;
private String username;
private String password;
private Date birthday;
}
创建OrderMapper接口和OrderMapper.xml
创建OrderMapper接口：
public interface UserMapper {
List<Order> oneToOneQuery();
}
创建OrderMapper.xml文件：
<mapper namespace="com.netease.mail.data.dao.UserMapper">
<resultMap id="orderMap" type="com.netease.mail.data.pojo.Order">
<result column="id" javaType="java.lang.Integer" property="id"></result>
<result column="order_time" javaType="java.util.Date" property="orderTime"></result>
<result column="total" javaType="java.lang.Double" property="total"></result>
<association property="user" javaType="com.netease.mail.data.pojo.User">
<result column="uid" javaType="java.lang.Integer" property="id"></result>
<result column="username" javaType="java.lang.String" property="username"></result>
<result column="password" javaType="java.lang.String" property="password"></result>
<result column="birthday" javaType="java.util.Date" property="birthday"></result>
</association>
</resultMap>
<select id="oneToOneQuery" resultMap="orderMap">
select *, u.id uid from `order` o left join `user` u on o.uid = u.id
</select>
</mapper>
测试结果
public class MybatisDemo {
public static void main(String[] args) throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
System.out.println("---------一对一级联查询-----------");
// 一对一级联查询
oneToOneMapping(userMapper);
}
public static void oneToOneMapping(UserMapper userMapper) {
// 一对一关联查询
List<Order> orders = userMapper.oneToOneQuery();
orders.forEach(System.out::println);
}
}
执行结果如下所示：
可以看到，结果打印出了每个订单与相应用户的关联信息。
一对多查询
⽤户表和订单表的关系为，⼀个⽤户有多个订单，⼀个订单只从属于⼀个⽤户
⼀对多查询的需求：查询⼀个⽤户，与此同时查询出该⽤户具有的订单
构造查询语句
select *, o.id oid from `user` u left join `order` o on u.id = o.uid
查询结果如下所示:
修改User实体
@ToString
@Data
public class Order {
private int id;
private Date orderTime;
private double total;
// 标识每个订单对应的唯一用户
private User user;
}
@ToString
@Data
public class User {
private int id;
private String username;
private String password;
private Date birthday;
// 标识当前用户具有哪些订单
private List<Order> orderList;
}
创建UserMapper接口和xml语句
UserMapper.java
public interface UserMapper {
List<Order> oneToOneQuery();
List<User> oneToMultiQuery();
}
UserMapper.xml
<mapper namespace="com.netease.mail.data.dao.UserMapper">
<resultMap id="userMap" type="com.netease.mail.data.pojo.User">
<result column="id" javaType="java.lang.Integer" property="id"></result>
<result column="username" javaType="java.lang.String" property="username"></result>
<result column="password" javaType="java.lang.String" property="password"></result>
<result column="birthday" javaType="java.util.Date" property="birthday"></result>
<collection property="orderList" ofType="com.netease.mail.data.pojo.Order">
<result column="oid" javaType="java.lang.Integer" property="id"></result>
<result column="order_time" javaType="java.util.Date" property="orderTime"></result>
<result column="total" javaType="java.lang.Double" property="total"></result>
</collection>
</resultMap>
<select id="oneToMultiQuery" resultMap="userMap">
select *, o.id oid from `user` u left join `order` o on u.id = o.uid
</select>
</mapper>
测试结果
测试代码如下：
public static void main(String[] args) throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
System.out.println("---------一对多级联查询-----------");
// 一对多级联关系查询
oneToMultiMapping(userMapper);
}
public static void oneToMultiMapping(UserMapper userMapper) {
// 一对多级联关系查询
List<User> users = userMapper.oneToMultiQuery();
users.forEach( user -> {
System.out.println(user.getUsername());
List<Order> orderList = user.getOrderList();
orderList.forEach(System.out::println);
});
}
代码执行打印信息如下所示：
从上面的打印信息中可以看到，一对多的映射查询，在UserMapper.xml中设置了<collection>标签后，会将查询结果中<result>标签字段相同的行进行合并，并将合并后的<collection>标签下的<result>字段转换映射到设置的实体类属性List集合中， 从而实现一对多的映射查询。
多对多映射查询
多对多查询的模型
⽤户表和⻆⾊表的关系为，⼀个⽤户有多个⻆⾊，⼀个⻆⾊被多个⽤户使⽤
多对多查询的需求：查询⽤户同时查询出该⽤户的所有⻆⾊
构造查询语句
select u.*,r.*,r.id rid from user u left join user_role ur on u.id=ur.user_id
left join role r on ur.role_id=r.id
查询结果如下所示:
创建Role实体、修改User实体
@Data
@ToString
public class Role {
private int id;
private String roleName;
}
@ToString
@Data
public class User {
private int id;
private String username;
private String password;
private Date birthday;
// 标识当前用户具有哪些订单
private List<Order> orderList;
// 标识当前用户具有哪些角色
private List<Role> roleList;
}
添加UserMapper接口方法和xml语句
UserMapper.java
public interface UserMapper {
List<Order> oneToOneQuery();
List<User> oneToMultiQuery();
List<User> multiToMulti();
}
UserMapper.xml
<mapper namespace="com.netease.mail.data.dao.UserMapper">
<resultMap id="orderMap" type="com.netease.mail.data.pojo.Order">
<result column="id" javaType="java.lang.Integer" property="id"></result>
<result column="order_time" javaType="java.util.Date" property="orderTime"></result>
<result column="total" javaType="java.lang.Double" property="total"></result>
<association property="user" javaType="com.netease.mail.data.pojo.User">
<result column="uid" javaType="java.lang.Integer" property="id"></result>
<result column="username" javaType="java.lang.String" property="username"></result>
<result column="password" javaType="java.lang.String" property="password"></result>
<result column="birthday" javaType="java.util.Date" property="birthday"></result>
</association>
</resultMap>
<resultMap id="userMap" type="com.netease.mail.data.pojo.User">
<result column="id" javaType="java.lang.Integer" property="id"></result>
<result column="username" javaType="java.lang.String" property="username"></result>
<result column="password" javaType="java.lang.String" property="password"></result>
<result column="birthday" javaType="java.util.Date" property="birthday"></result>
<collection property="orderList" ofType="com.netease.mail.data.pojo.Order">
<result column="oid" javaType="java.lang.Integer" property="id"></result>
<result column="order_time" javaType="java.util.Date" property="orderTime"></result>
<result column="total" javaType="java.lang.Double" property="total"></result>
</collection>
</resultMap>
<resultMap id="roleMap" type="com.netease.mail.data.pojo.User">
<result column="id" javaType="java.lang.Integer" property="id"></result>
<result column="username" javaType="java.lang.String" property="username"></result>
<result column="password" javaType="java.lang.String" property="password"></result>
<result column="birthday" javaType="java.util.Date" property="birthday"></result>
<collection property="roleList" ofType="com.netease.mail.data.pojo.Role">
<result column="rid" javaType="java.lang.Integer" property="id"></result>
<result column="role_name" javaType="java.lang.String" property="roleName"></result>
</collection>
</resultMap>
<select id="oneToOneQuery" resultMap="orderMap">
select *, u.id uid from `order` o left join `user` u on o.uid = u.id
</select>
<select id="oneToMultiQuery" resultMap="userMap">
select *, o.id oid from `user` u left join `order` o on u.id = o.uid
</select>
<select id="multiToMulti" resultMap="roleMap">
select u.*,r.*,r.id rid from user u left join user_role ur on u.id=ur.user_id
inner join role r on ur.role_id=r.id
</select>
</mapper>
测试结果
测试代码如下所示：
public class MybatisDemo {
public static void main(String[] args) throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
System.out.println("---------一对一级联查询-----------");
// 一对一级联查询
oneToOneMapping(userMapper);
System.out.println("---------一对多级联查询-----------");
// 一对多级联关系查询
oneToMultiMapping(userMapper);
System.out.println("---------多对多级联查询-----------");
multiToMultiMapping(userMapper);
}
public static void oneToOneMapping(UserMapper userMapper) {
// 一对一关联查询
List<Order> orders = userMapper.oneToOneQuery();
orders.forEach(System.out::println);
}
public static void oneToMultiMapping(UserMapper userMapper) {
// 一对多级联关系查询
List<User> users = userMapper.oneToMultiQuery();
users.forEach( user -> {
System.out.println(user.getUsername());
List<Order> orderList = user.getOrderList();
orderList.forEach(System.out::println);
});
}
// 多对多级联关系查询
public static void multiToMultiMapping(UserMapper userMapper) {
List<User> users = userMapper.multiToMulti();
users.forEach(user -> {
System.out.println(user.getUsername());
List<Role> roleList = user.getRoleList();
roleList.forEach(System.out::println);
});
}
}
上面展示了一对一、一对多、多对多的整体测试代码，单独执行多对多的级联关系查询结果如下所示：
根据上面的查询结果看分析得到，多对多的查询结果封装与一对多的原理类似。因为在关联查询时，无论是left join还是right join，在单次查询中，实际上都是一对多的查询逻辑。比如在上面的查询案例中，以user表出发做left join，此时在上面的多对多模型中，相当于是一个用户对应多个角色，所以查询结果仍然是一对多模型的查询原理。
使用注解的形式完成复杂映射开发
这⼏年来注解开发越来越流⾏，Mybatis也可以使⽤注解开发⽅式，这样我们就可以减少编写Mapper映射⽂件了。常用的Mybatis注解：
@Insert：实现新增
@Update：实现更新
@Delete：实现删除
@Select：实现查询
@Result：实现结果集封装
@Results：可以与@Result ⼀起使⽤，封装多个结果集
@One：实现⼀对⼀结果集封装
@Many：实现⼀对多结果集封装
实现复杂关系映射之前我们可以在映射⽂件中通过配置来实现，使⽤注解开发后，我们可以使⽤@Results注解，
@Result注解，@One注解，@Many注解组合完成复杂关系的配置
在使用Mybatis注解的方式对上面采用Mapper的方式进行改造之前，由于我们不需要UserMapper.xml文件了，因此，需要修改MyBatis的核⼼配置⽂件，加载使⽤了注解的Mapper接⼝即可。
<mappers>
<mapper resource="mapper/UserMapper.xml"/>
</mappers>
替换为
<mappers>
<mapper class="com.netease.mail.data.dao.UserMapper"/>
</mappers>
或者指定扫描包含映射关系的接⼝所在的包也可以：
<mappers>
<package name="com.netease.mail.data.dao"/>
</mappers>
接下来，我们将使用Mybatis注解的方式来实现上面采用Mapper方式的功能，用实际案例来介绍Mybatis注解开发的基本原理和过程
一对一查询
一对一查询模型和User、Order实体类与上面案例中的一致。需要修改的是创建UserMapper.java和OrderMapper.java两个接口类：
使用注解配置Mapper
编写OrderMapper接口方法查询order表
public interface OrderMapper {
@Select("select * from `order`")
@Results({
@Result(id=true, property = "id", column = "id"),
@Result(property = "orderTime", column = "order_time"),
@Result(property = "total", column = "total"),
@Result(property = "user", column = "uid",
javaType = User.class,
one=@One(select = "com.netease.mail.data.dao.UserMapper.selectUserById"))
})
List<Order> findAll();
}
@Results注解代替的标签就相当于是<resultMap>，@Result注解代替的标签就相当于是<result>。需要注意的就是@One注解，该注解代替了<assocation>标签，利用select语句来查询关联表的信息。这里@One注解里的column属性的uid，是作为关联参数传递到UserMapper.selectUserById的sql方法中，用于替换占位符的。
编写UserMapper接口方法，根据传入的uid，查询用户信息并返回
public interface UserMapper {
@Select("select * from user where id = #{id}")
User selectUserById(@Param("id") int id);
}
编写测试方法
public class MybatisDemo {
private static OrderMapper orderMapper;
private static UserMapper userMapper;
public static void main(String[] args) throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
userMapper = sqlSession.getMapper(UserMapper.class);
orderMapper = sqlSession.getMapper(OrderMapper.class);
System.out.println("---------注解驱动的一对一级联查询-----------");
annotationOneToOneQuery();
}
public static void annotationOneToOneQuery() {
List<Order> orders = orderMapper.findAll();
orders.forEach(System.out::println);
}
}
执行得到输出结果如下所示：
可以看到，我们在OrderMapper里的findAll方法中，使用注解地方式将需要关联的uid字段传入到了UserMapper的对应方法中，替换了占位符的值。
一对多查询
模型仍然以上面一对多例子中的用户和订单的关联关系为例展开。
⽤户表和订单表的关系为，⼀个⽤户有多个订单，⼀个订单只从属于⼀个⽤户
⼀对多查询的需求：查询⼀个⽤户，与此同时查询出该⽤户具有的订单
User实体与Order实体的配置与上面Mapper形式中的一对多查询的实体类属性保持一致。
使用注解配置Mapper
public interface OrderMapper {
@Select("select * from `order` where uid = #{id}")
List<Order> findByUid();
}
public interface UserMapper {
@Select("select * from user")
@Results(value = {
@Result(id=true, property = "id", column = "id"),
@Result(property = "username", column = "username"),
@Result(property = "password", column = "password"),
@Result(property = "birthday", column = "birthday"),
@Result(property = "orderList", column = "id",
javaType = List.class,
many=@Many(select = "com.netease.mail.data.dao.OrderMapper.findByUid"))
})
List<User> findUserAndOrder();
}
测试结果
从上图可以看到，同样利用注解的方式，将关联属性在两个方法中进行传递，实现了一对多的级联查询。只是这里用的注解是@Many注解
多对多查询
多对多查询的模型同样体现在，⽤户表和⻆⾊表的关系为，⼀个⽤户有多个⻆⾊，⼀个⻆⾊被多个⽤户使⽤
多对多查询的需求：查询⽤户同时查询出该⽤户的所有⻆⾊
数据表与实体类的修改与上面Mapper形式中的保持一致。
使用注解配置Mapper
public interface UserMapper {
@Select("select * from user")
@Results(value = {
@Result(id=true, property = "id", column = "id"),
@Result(property = "username", column = "username"),
@Result(property = "password", column = "password"),
@Result(property = "birthday", column = "birthday"),
@Result(property = "roleList", column = "id",
javaType = List.class,
many=@Many(select = "com.netease.mail.data.dao.RoleMapper.findByUserId"))
})
List<User> findUserAndRole();
}
public interface RoleMapper {
@Select("select * from role r left join user_role ur on r.id = ur.role_id where ur.user_id = #{id}")
@Results({
@Result(id=true, property = "id", column = "id"),
@Result(property = "roleName", column = "role_name")
})
List<Role> findByUserId();
}
测试结果
至此，我们实现了利用Mapper.xml文件的方式和注解驱动的两种方式，来实现Mybatis复杂映射开发的过程。利用案例来更加清晰地展示复杂映射开发的原理和过程，便于日后忘记了，可以回过头来借鉴。
2077
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
