MyBatis - 07多表查询

MyBatis - 07多表查询

（1）一对一查询

用户表和订单表： 一个用户可以有多个订单，一个订单只属于一个用户。

需求： 查询一个订单，及订单所属的用户信息

（1.1）实体类User、Order

public class User {
    private int id;
    private String username;
    private String password;
    private Date birthday;// 省略其他代码
}

public class Order {
    private int id;
    private String ordertime;
    private double total;

    private User user;
    // 省略其他代码
}

（1.2）核心配置文件

    <!--加载映射文件-->
    <mappers>
        <mapper resource="mapper/UserMapper.xml"></mapper>
        <mapper resource="mapper/OrderMapper.xml"></mapper>
    </mappers>

（1.3）映射文件 OrderMapper.xml

    <select id="findAll" resultType="order">   // 能够查询出所有数据，却不能映射到Order类的User属性上
        select *,o.id oid from orders o, user u where o.uid = u.id;
    </select>

 

    <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>
    </resultMap>

    <select id="findAll" resultMap="orderMap">
        select *,o.id oid from orders o, user u where o.uid = u.id;
    </select>

 

    <resultMap id="orderMap" type="order">
        <id column="oid" property="id"></id>
        <result column="ordertime" property="ordertime"></result>
        <result column="total" property="total"></result>
        // 方式二
        <association property="user" javaType="user">   // order实例类中的属性名称user  属性类型User(private User user)
            <result column="uid" property="id"></result>
            <result column="username" property="username"></result>
            <result column="password" property="password"></result>
            <result column="birthday" property="birthday"></result>
        </association>
    </resultMap>

 

Order{id=1, ordertime=2020-11-14 21:58:18, total=3000.0, user=User{id=1, username='zhangsan', password='123', birthday=null}}
Order{id=2, ordertime=2020-11-14 21:58:29, total=5800.0, user=User{id=1, username='zhangsan', password='123', birthday=null}}
Order{id=3, ordertime=2020-11-14 21:58:44, total=323.0, user=User{id=2, username='lisi', password='456', birthday=null}}
Order{id=4, ordertime=2020-11-14 21:58:47, total=2345.0, user=User{id=1, username='zhangsan', password='123', birthday=null}}
Order{id=5, ordertime=2020-11-14 21:58:56, total=100.0, user=User{id=2, username='lisi', password='456', birthday=null}}
Order{id=6, ordertime=2020-11-14 21:59:07, total=2009.0, user=User{id=5, username='firefly', password='222222', birthday=null}}

 

（2）一对多查询

需求： 查询用户信息，同时查询出用户的订单。  一个用户，多个订单。

public class User {
    private int id;
    private String username;
    private String password;
    private Date birthday;

    private List<Order> orderList;
    // 省略其他代码
}

 

    <resultMap id="userMap" type="user">
        <result column="uid" property="id"></result>
        <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="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>

 

public class MapperTest {

    private UserMapper userMapper;

    @Before
    public void before() throws IOException {
        InputStream resourceAsStream = Resources.getResourceAsStream("sqlMapConfig.xml");
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(resourceAsStream);
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        userMapper = sqlSession.getMapper(UserMapper.class);
    }

    @Test
    public void testFindUserAndOrderAll(){
        List<User> all = userMapper.findAll();
        for (User user: all) {
            System.out.println(user);
        }
    }
}

 

（3）多对多查询

用户表和角色表的关系： 一个用户有多个角色，一个角色被多个用户使用。 数据库表： 用户表 、 角色表、  用户角色绑定表

需求：查询所有用户，并同时查询用户所具有的角色信息

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<Role> roleList;
    // 省略其他代码
}

    <resultMap id="userMap" type="user">
        <result column="uid" property="id"></result>
        <result column="username" property="username"></result>
        <result column="password" property="password"></result>
        <result column="birthday" property="birthday"></result>

        <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="userMap">
        select *,u.id uid from user u, sys_user_role ur, sys_role r where u.id = ur.userId and ur.roleId = r.id
    </select>

 

List<User> userList = userMapper.findUserAndRoleAll();
for(User user:userList){
    System.out.println(user);
}

 

（4）小结

一对一配置： 使用<resultMap>

一对多配置： 使用<resultMap> + <collectiion>

多对多配置： 使用<resultMap> + <collectiion>