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>