mybatis多表关联查询

多表关联查询

在表关系中,大致分为三类

1.一对一

每个人与自己的身份证之间属于一对一关系,这个很好理解

2.一对多

一个人可以有很多张银行卡,你的多张银行卡对应你自己,这种关系可以理解为一对多,多对一

3.多对多

在学校,你有好几个老师,每个老师又教很多学生,这种关系可以看作为多对多

XML配置

1.一对一

1.1 一对一查询的模型

以用户与订单表关系为例,一个用户可以有多张订单,一个订单只属于一个人

一对一查询需求:查询一个订单,并且查询出订单所属用户

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-23Kb2eA3-1571446878161)(C:\Users\Administrator\Desktop\mybatis\多表关系映射\一对一关系表.jpg)]

1.2一对一查询语句

​ sql语句:

​

SELECT *  FROM orders o,USER u WHERE o.uid=u.id

​

1.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;

}

1.4 创建OrderMapper接口

public interface OrderMapper {
    List<Order> findAll();
}

1.5 配置OrderMapper.xml

<mapper namespace="com.itheima.mapper.OrdersMapper">
    <!--orders与user为别名-->
    <resultMap id="ordersMap" type="orders">
        <id property="id" column="oid"/>
        <result property="ordertime" column="ordertime"/>
        <result property="total" column="total"/>
        <association javaType="user" property="user">
            <id property="id" column="uid"/>
            <result property="username" column="username"/>
            <result property="password" column="password"/>
            <result property="birthday" column="birthday"/>
        </association>
    </resultMap>
    <select id="findAll" resultMap="ordersMap">
      SELECT * ,o.id oid,u.id uid FROM orders o,USER u WHERE o.uid=u.id
    </select>
</mapper>

1.4测试结果

  List<Orders> ordersList = mapper.findAll();
        for (Orders orders : ordersList) {
            System.out.println(orders);
        }

2.一对多

2.1 一对多查询的模型

在用户与订单的关系中,一个人可以对于多张表

2.2 一对多查询的语句

select *,o.id oid from user u left join orders o on u.id=o.uid;

2.3 修改User实体

    private Integer id;
    private String username;
    private String password;
    private String birthday;
    
    private List<Orders> ordersList;

2.4 创建UserMapper接口

public interface UserMapper {
    List<User> findAll();
}

2.5 配置UserMapper.xml

<mapper namespace="com.itheima.mapper.UserMapper">
    <resultMap id="userMap" type="user">
        <id property="id" column="id"/>
        <result property="username" column="username"/>
        <result property="password" column="password"/>
        <result property="birthday" column="birthday"/>

        <collection javaType="java.util.List" ofType="orders" property="ordersList">
            <result column="oid" property="id"/>
            <result column="ordertime" property="ordertime"/>
            <result column="total" property="total"/>
            <result property="uid" column="uid"/>
        </collection>
    </resultMap>
    <select id="findAll" resultMap="userMap">
        select *,o.id oid from user u left join orders o on u.id=o.uid
    </select>
</mapper>

2.6 测试结果

  UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        List<User> userList = mapper.findAll();
        for (User user : userList) {
            System.out.println(user);
        }

3.多对多

3.1多对多查询模型

用户与角色之间为多对多关系,一个用户可以有多个角色,一个角色可以被多个用户拥有

3.2多对多查询语句

SELECT * FROM USER LEFT JOIN sys_user_role ON user.id=sys_user_role.userid INNER JOIN sys_role ON sys_user_role.roleid=sys_role.id

3.3 创建Role实体，修改User实体

public class Role {
    private Integer id;
    private String  rolename;
    
public class User {
    private Integer id;
    private String username;
    private String password;
    private String birthday;

    //代表当前用户具备哪些订单
    private List<Orders> ordersList;

    //代表当前用户具备哪些角色
    private List<Role> roleList;

3.4 添加UserMapper接口方法

List<User> findAllUserAndRole();

3.5 配置UserMapper.xml

    <!--多对多-->
    <resultMap id="userRoleMap" type="user">
        <id column="id" property="id"/>
        <result column="username" property="username"/>
        <result property="password" column="password"/>
        <result property="birthday" column="birthday"/>
        <collection property="roleList" ofType="com.itheima.domain.Role">
            <result property="id" column="roleId"/>
            <result property="rolename" column="roleName"/>
        </collection>
    </resultMap>
    <select id="findAllUserAndRole" resultMap="userRoleMap">
      SELECT *,sys_role.rolename roleName,sys_role.id roleId FROM USER LEFT JOIN sys_user_role ON user.id=sys_user_role.userid INNER JOIN sys_role ON sys_user_role.roleid=sys_role.id
    </select>
</mapper>

3.6 测试结果