Mybatis复杂映射开发
一对一
⽤户表和订单表的关系为，⼀个⽤户有多个订单，⼀个订单只从属于⼀个⽤户
⼀对⼀查询的需求：查询⼀个订单，与此同时查询出该订单所属的⽤户
用户表
订单表 uid表示用户id
⼀对⼀查询的语句
对应的sql语句：
select * from orders o,user u where o.uid=u.id;
查询结果
实际开发需要将其封装在Java对象里
创建Order和User实体
Order
public class Order {
private Integer id;
private String orderTime;
private Double total;
// 表明该订单属于哪个用户
private User user;
public User getUser() {
return user;
}
//get set 省略。。
User
public class User implements Serializable {
private Integer id;
private String username;
// get set 省略。。。
}
创建OrderMapper接⼝
public interface OrderMapper {
List<Order> findAll();
}
IOrderMapper.xml
<mapper namespace="com.lagou.mapper.IOrderMapper">
<!-- 1对1 查询结果映射集-->
<resultMap id="orderMap1" type="com.lagou.pojo.Order">
<result property="id" column="id"></result>
<result property="orderTime" column="orderTime"></result>
<result property="total" column="total"></result>
<!-- 当前类型-->
<!-- 配置user属性的映射 uid 实际就是 用户id-->
<association property="user" javaType="com.lagou.pojo.User">
<result property="id" column="uid"></result>
<result property="username" column="orderTime"></result>
</association>
</resultMap>
<select id="findOrder2User" resultMap="orderMap1">
select * from orders o,user u where o.uid = u.id
</select>
</mapper>
测试
@Test
public void findOrder2User() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
IOrderMapper mapper = sqlSession.getMapper(IOrderMapper.class);
List<Order> orderAndUser = mapper.findOrder2User();
for (Order order : orderAndUser) {
System.out.println(order);
}
}
可以看到结果
一对多映射
⽤户表和订单表的关系为，⼀个⽤户有多个订单，⼀个订单只从属于⼀个⽤户
对应的sql语句：
select *,o.id oid from user u left join orders o on u.id=o.uid;
可以看到用户id为1 的有两个订单
User实体
我们用 orderList 来表示接收多个的订单集合即一个用户对应多个订单
public class User {
private int id;
private String username;
private String password;
private Date birthday;
//代表当前⽤户具备哪些订单
private List<Order> orderList;
}
创建UserMapper接⼝
public interface UserMapper {
List<User> findAll();
}
UserMapper.xml
<mapper namespace="com.mapper.IUserMapper">
<resultMap id="userMap2" type="com.lagou.pojo.User">
<result column="id" property="id"></result>
<result column="username" property="username"></result>
<!-- 一对多 属性类型需要用 ofType 来指定-->
<collection property="orderList" ofType="com.lagou.pojo.Order">
<result property="id" column="oid"></result>
<result property="orderTime" column="ordertime"></result>
<result property="total" column="total"></result>
</collection>
</resultMap>
<select id="findAll2" resultMap="userMap2">
select u.*,o.id oid,o.ordertime,o.total,o.uid from user u left join orders o on o.uid = u.id
</select>
</mapper>
测试
@Test
public void findAll2() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
IUserMapper mapper = sqlSession.getMapper(IUserMapper.class);
List<User> users = mapper.findAll2();
for (User user : users) {
System.out.println(user.getUsername());
System.out.println(user.getOrderList());
System.out.println("============");
}
}
可以看到lucy 有两个订单，tom有一个
多对多
⽤户表和⻆⾊表的关系为，⼀个⽤户有多个⻆⾊，⼀个⻆⾊被多个⽤户使⽤
多对多查询的需求：查询⽤户同时查询出该⽤户的所有⻆⾊
用户表
用户角色关联表
角色表
对应的sql语句：
SELECT
u.*,
r.rolename,
r.id as rid
FROM
USER u
LEFT JOIN sys_user_role ur ON u.id = ur.userid
INNER JOIN sys_role r ON ur.roleid = r.id;
添加UserMapper接⼝⽅法
public List<User> manyUser2ManyRole();
xml
<mapper namespace="com.lagou.mapper.IUserMapper">
<resultMap id="userMap2" type="com.lagou.pojo.User">
<id property="id" column="id"></id>
<result property="username" column="username"></result>
<collection property="roleList" ofType="com.lagou.pojo.Role">
<id property="id" column="rid"/>
<result column="rolename" property="rolename"></result>
</collection>
</resultMap>
<select id="manyUser2ManyRole" resultMap="userMap2" >
SELECT
u.*,
r.rolename,
r.id as rid
FROM
USER u
LEFT JOIN sys_user_role ur ON u.id = ur.userid
INNER JOIN sys_role r ON ur.roleid = r.id;
</select>
</mapper>
测试
@Test
public void test3() throws IOException {
InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
IUserMapper mapper = sqlSession.getMapper(IUserMapper.class);
List<User> users = mapper.manyUser2ManyRole();
for (User user : users) {
for (Role role : user.getRoleList()) {
System.out.println(user.getUsername() + " --> " + role.getRolename());
}
System.out.println("============");
}
}
可以看到结果
