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>