Mybatis的多表操作
1.Mybatis多表查询
1.1一对一查询
1.一对一查询的模型
用户表和订单表的关系为，一个用户有多个订单，一个订单只从属于一个用户
一对一查询的需求：查询一个订单，与此同时查询出该订单所属的用户
2.创建Order和User实体
public class Order {private int id ;private Date ordertime ;private double total ;//代表当前订单从属于哪一个客户private User user ;}
public class User {private int id ;private String username ;private String password ;private Date birthday ;}
3.创建OrderMapp接口
public interface OrderMapper {List<Order> findAll();}
4.配置OrderMapper.xml
<mapper namespace="com.lin.mapper.OrderMapper">
<resultMap id="orderMap" type="order">
<!--
手动指定字段与实体属性的映射关系
column：表明字段
property：实体属性
-->
<id column="oid" property="id"></id>
<result column="ordertime" property="ordertime"></result>
<result column="total" property="total"></result>
<result column="uid" property="user.id"></result>
<!--<result column="username" property="user.username"></result>
<result column="password" property="user.password"></result>
<result column="birthday" property="user.birthday"></result>-->
<!--
property:当前实体属性（order）的属性名称
javaType：当前实体（order）中的属性类名称
-->
<association property="user" javaType="user">
<id column="uid" property="id"></id>
<result column="username" property="username"></result>
<result column="password" property="password"></result>
<result column="birthday" property="birthday"></result>
</association>
</resultMap>
<select id="findAll" resultMap="orderMap">
SELECT *,o.id oid FROM orders o,USER u WHERE o.uid=u.id
</select>
</mapper>1.2 一对多查询
1.一对多查询的模型
用户表和订单表的关系为，一个用户有多个订单，一个订单只从属于一个用户
一对多查询的需求：查询一个用户，与此同时查询出该用户具有的订单
2.修改User实体
public class Order {private int id ;private Date ordertime ;private double total ;//代表当前订单从属于哪一个客户private User user ;}
public class User {private int id ;private String username ;private String password ;private Date birthday ;//代表当前用户具备哪些订单private List<Order> orderList ;}
4.创建UserMapper接口
public interface UserMapper {List<User> findAll();}
5.配置UserMapper.xml
<mapper namespace="com.lin.mapper.UserMapper">
<resultMap id="resultMap" type="user">
<id column="uid" property="id"></id>
<result column="username" property="username"></result>
<result column="password" property="password"></result>
<result column="birthday" property="birthday"></result>
<!--配置集合信息
property:集合名称
ofType: 代表当前集合的数据类型
-->
<collection property="orderList" ofType="order">
<id column="oid" property="id"></id>
<result column="total" property="total"></result>
<result column="ordertime" property="ordertime"></result>
</collection>
</resultMap>
<select id="findAll" resultMap="resultMap">
SELECT*,o.id oid FROM orders o,USER u WHERE u.id=o.uid
</select>
</mapper>1.3 多对多查询
1.多对多查询的模型
用户表和角色表的关系为，一个用户有多个角色，一个角色被多个用户使用
多对多查询的需求：查询用户同时查询出该用户的所有角色
2.创建Role实体，修改User实体
public class User {private int id ;private String username ;private String password ;private Date birthday ;//代表当前用户具备哪些订单private List<Order> orderList ;//代表当前用户具备哪些角色private List<Role> roleList ;}
public class Role {private int id ;private String rolename ;}
3.添加UserMapper接口方法
List<User> findAllUserAndRole();
5.配置UserMapper.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.lin.mapper.UserMapper">
<resultMap id="userRoleMap" type="user">
<!--封装user的信息-->
<id column="userid" property="id"></id>
<result column="birthday" property="birthday"></result>
<result column="username" property="username"></result>
<result column="password" property="password"></result>
<!--封装roleList的信息-->
<collection property="roleList" ofType="Role">
<id column="roleid" property="id"></id>
<result column="rolename" property="roleName"></result>
<result column="roleDesc" property="roleDesc"></result>
</collection>
</resultMap>
<select id="findUserAndRoles" resultMap="userRoleMap">
SELECT*FROM user u,sys_user_role ur,sys_role r WHERE u.id=ur.userid AND ur.roleid=r.id
</select>
</mapper>