Mybatis(三) 一对一,一对多,多对多查询

订单：

public class Order{
   private int id;
    private Date ordertime;
    private User user;
    private int total;

<?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.dao.OrderMapper">


    <resultMap id="orderMap" type="order">
        <!--数据表字段        实体类属性-->
        <id column="OrderID" property="id"></id>
        <result column="ordertime" property="ordertime"/>
        <result column="total" property="total"/>
        <!--一对一的方式  两种-->
        <!--<result column="UserID" property="user.id"/>-->
        <!--<result column="name" property="user.name"/>-->
        <!--<result column="password" property="user.password"/>-->


        <!--实体类属性名称   实体类（order）属性的类型       -->
        <association property="user" javaType="user">
            <id column="UserID" property="id"/>
            <result column="name" property="name"/>
            <result column="password" property="password"/>
        </association>
    </resultMap>
                            <!--查询完毕后将结果封装到orderMap里-->
    <select id="findAll" resultMap="orderMap">
        select *,o.uid as UserID ,o.id as OrderID from user as u inner join orders as o on o.uid=u.id order by u.id
    </select>

</mapper>

用户：

public class User {
    private int id;
    private String name;
    private String password;

    private List<Order> orderList;

<?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.dao.UserMapper">
<resultMap id="userMap" type="user">
    <id column="UserID" property="id"/>
    <result column="name" property="name"/>
    <result column="password" property="password"/>
           <!-- 实体类属性中的集合名称        集合中的数据类型-->
    <collection property="orderList" ofType="order">
        <id column="OrderID" property="id"/>
        <result column="total" property="total"/>
        <result column="ordertime" property="ordertime"/>
    </collection>
</resultMap>

<select id="findAll" resultMap="userMap">
    select * , u.id as UserID ,o.id as OrderID from user as u inner join orders as o on o.uid=u.id order by UserID
</select>

</mapper>

订单的Mapper查询结果为一对一，主要是在Order里如何配置一个User对象
用户的Mapper查询结果为一对多，主要是在User里如何配置一个Order的集合

多对多==========

有三张表，user、role、role_user，查询的目的是吧每个人的职业都显示出来，所以需要在user里添加一个role的集合，配置文件操作与一对多相同，主要是 sql语句不同。

userMapper.xml

 <resultMap id="userAndRoleMap" type="user">
        <id column="UserID" property="id"/>
        <result column="name" property="name"/>
        <result column="password" property="password"/>
        <collection property="RoleList" ofType="role">
            <result column="RoleID" property="id"/>
            <result column="roleName" property="roleName"/>
            <result column="roleDesc" property="roleDesc"/>
        </collection>
    </resultMap>


    <select id="findUserAndRole" resultMap="userAndRoleMap">
      select u.id as UserID,u.name,u.password,r.id as RoleID,r.roleName,r.roleDesc
      from user as u ,role as r,role_user as ru
      where u.id=ru.userid
      and ru.roleid=r.id
      order by UserID
    </select>

查询的结果为

若要查询每个职业的人员，同理需建立RoleMapper.xml，以及在Role实体类中加入一个User的集合。