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>