表与表之间的关系:
用户表和订单表的关系为,一个用户有多个订单,一个订单从属于一个用户。
用户表和角色表的关系为,一个用户有多个角色,一个角色被多个用户使用。
一、一对一查询
1. 一对一查询的模型
一对一查询的需求:查询一个订单,于此同时查询出该订单所属的用户
2. 一对一查询的语句
SELECT *,o.id oid FROM orders o,USER u WHERE o.uid=u.id
3. 创建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; }
4. 创建OrderMapper 接口
public interface OrderMapper { //查询全部的方法 public List<Order> findAll(); }
5. 配置OrderMapper.xml
<mapper namespace="com.itheima.mapper.OrderMapper"> <resultMap id="orderMap" type="com.itheima.domain.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> </resultMap> <select id="findAll" resultMap="orderMap"> SELECT *,o.id oid FROM orders o,USER u WHERE o.uid=u.id </select> </mapper>
或者
<mapper namespace="com.itheima.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> <!-- property: 当前实体(order)中的属性名称(private User user) javaType: 当前实体(order)中的属性的类型(User) --> <association property="user" javaType="com.itheima.domain.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. 一对一查询的语句
select *,o.id oid from user u left join orders o on u.id=o.uid;
3. 修改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.itheima.mapper.OrderMapper"> <resultMap id="userMap" type="com.itheima.domain.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="com.itheima.domain.Order"> <!--封装order的数据--> <id column="oid" property="id"></id> <result column="ordertime" property="ordertime"></result> <result column="total" property="total"></result> </collection> </resultMap> <select id="findAll" resultMap="userMap"> SELECT *,o.id oid FROM USER u,orders o WHERE u.id=o.uid </select> </mapper>
三、多对多查询
1. 一对多查询的模型
多对多查询的需求:查询用户同时查询出该用户的所有角色
2. 一对一查询的语句
SELECT * FROM USER u,sys_user_role ur,sys_role r WHERE u.id=ur.userId AND ur.roleId=r.id
3. 创建Role实体、修改User 实体
public class Role { private int id; private String roleName; private String roleDesc; }
public class User { private int id; private String username; private String password; private Date birthday; //描述的是当前用户存在哪些订单 private List<Order> orderList; //描述的是当前用户具备哪些角色 private List<Role> roleList; }
4. 添加UserMapper接口方法
public interface UserMapper { public List<User> findAll(); public List<User> findUserAndRoleAll(); }
5. 配置UserMapper.xml
<mapper namespace="com.itheima.mapper.UserMapper"> <resultMap id="userRoleMap" type="user"> <!--user的信息--> <id column="userId" property="id"></id> <result column="username" property="username"></result> <result column="password" property="password"></result> <result column="birthday" property="birthday"></result> <!--user内部的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="findUserAndRoleAll" 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>
四、知识小结
MyBatis 多表配置方式:
一对一配置:使用<resultMap>做配置
一对多配置:使用<resultMap>+<collection>做配置
多对多配置:使用<resultMap>+<collection>做配置