MyBatis多对多映射查询 Day12 2018-11-30

7 <collection>一对多查询

<collection>元素的解释:

• column 表示当前查询数据库表的列名DEPARTMENT_ID
• property 表示返回类型PhoneUserIdAndDepartment属性名department
• javaType 表示该属性对于的类型名称，本例是一个ArrayList集合
• select 表示执行的查询语句，将查询到的数据封装到property所代表的类型对象当中
• ofType 表示集合当中的类型

7.1 基本应用

7.1.1 java bean

• 同 6.1.1

7.1.2 映射文件

<mapper namespace="com.zhougl.web.dao.WebClassDao">
    <resultMap id="WebClassMap"
        type="com.zhougl.web.beans.WebClass">
        <id column="ID" jdbcType="DECIMAL" property="id" />
        <result column="CODE" jdbcType="VARCHAR" property="code" />
        <result column="NAME" jdbcType="VARCHAR" property="name" />
        <!-- 一对多 -->
        <collection property="students" column="id"
            javaType="ArrayList" ofType="com.zhougl.web.beans.Student"
            select="selectStudentByClassId"
            fetchType="lazy" />
    </resultMap>
    
    <select id="selectStudentByClassId" parameterType="int" resultType="com.zhougl.web.beans.Student">
    select * from STUDENT  where class_id = #{classId,jdbcType=DECIMAL}
  </select>
  <select id="selectClass" parameterType="int" resultMap="WebClassMap">
    select * from WEB_CLASS
  </select>

</mapper>

7.1.3 测试代码

SqlSession session = SqlSessionFactoryUtil.getSession();
        List<WebClass> classes = session.selectList("com.zhougl.web.dao.WebClassDao.selectClass");
        classes.forEach(classe ->{
            System.out.println(classe);
            List<Student> students = classe.getStudents();
            students.forEach(student -> System.out.println(student));
        });
        session.commit();
        session.close();

• 结果

==>  Preparing: select * from WEB_CLASS 
==> Parameters: 
<==      Total: 2
==>  Preparing: select * from STUDENT where class_id = ? 
==> Parameters: 2(Integer)
<==      Total: 2
WebClass [id=2, code=C002, name=无限流战斗班]
Student [id=2, name=王怡, sex=女, age=24]
Student [id=4, name=王多燕, sex=女, age=26]
==>  Preparing: select * from STUDENT where class_id = ? 
==> Parameters: 1(Integer)
<==      Total: 2
WebClass [id=1, code=C001, name=大乱斗指导班]
Student [id=1, name=王一倩, sex=女, age=22]
Student [id=3, name=王二赞, sex=男, age=28]

7.2 一对多映射

7.2.1 mybatis配置

• mybatis-config.xml添加如下配置

<settings>
    <!-- 延迟加载的全局开关 -->
    <setting name="lazyLoadingEnabled" value="true"/>
    <!-- true 使带有延迟加载的属性对象立即加载 ，false-按需加载-->
    <setting name="aggressiveLazyLoading" value="false"/>
</settings>

7.2.2 java bean

• 同 6.1.1

7.2.3 mapper映射文件

• WebClassMapper.xml

<mapper namespace="com.zhougl.web.dao.mapper.WebClassMapper">
    <resultMap id="WebClassMap"
        type="com.zhougl.web.beans.WebClass">
        <id column="ID" jdbcType="DECIMAL" property="id" />
        <result column="CODE" jdbcType="VARCHAR" property="code" />
        <result column="NAME" jdbcType="VARCHAR" property="name" />
        <!-- 一对多 -->
        <collection property="students" column="id"
            javaType="ArrayList" ofType="com.zhougl.web.beans.Student"
            select="com.zhougl.web.dao.mapper.StudentMapper.selectStudentByClassId"
            fetchType="lazy" >
            <id column="ID" jdbcType="DECIMAL" property="id" />
            <result column="NAME" jdbcType="VARCHAR" property="name" />
            <result column="SEX" jdbcType="VARCHAR" property="sex" />
            <result column="AGE" jdbcType="DECIMAL" property="age" />
        </collection>
    </resultMap>

    <sql id="Base_Column_List">
        ID, CODE, NAME
    </sql>
    <select id="selectWebClassById"
        parameterType="int" resultMap="WebClassMap">
        select
        <include refid="Base_Column_List" />
        from WEB_CLASS
        where ID = #{id,jdbcType=DECIMAL}
    </select>

</mapper>

7.2.4 mapper接口

• WebClassMapper.xml

public interface WebClassMapper {
  
    WebClass selectWebClassById(int i);
}

7.2.5 测试类

public class OneToManyTest {

    public static void main(String[] args) {
        SqlSession session = SqlSessionFactoryUtil.getSession();
        OneToManyTest test = new OneToManyTest();
        //测试一对多
        test.testOneToMany(session);
        
        //测试多对一
        //test.testManyToOne(session);
        session.commit();
        session.close();

    }
    //测试一对多，查询班级（一）级联查询学生（多）
    public void testOneToMany(SqlSession session) {
        WebClassMapper mapper = session.getMapper(WebClassMapper.class);
        WebClass webClass = mapper.selectWebClassById(1);
        System.out.println(webClass.getId()+" "+webClass.getCode()+" "+webClass.getName());
        System.out.println(webClass.toString());
        List<Student> students = webClass.getStudents();
        for (Student student : students) {
            System.out.println(student.toString());
        }
    }

}

==>  Preparing: select ID, CODE, NAME from WEB_CLASS where ID = ? 
==> Parameters: 1(Integer)
<==      Total: 1
1 C001 大乱斗指导班
==>  Preparing: select ID as studentId, NAME as studentName, SEX, AGE, CLASS_ID from STUDENT where CLASS_ID = ? 
==> Parameters: 1(Integer)
<==      Total: 4
WebClass [id=1, code=C001, name=大乱斗指导班]
Student [id=1, name=王一倩, sex=女, age=22]
Student [id=2, name=王怡, sex=女, age=24]
Student [id=3, name=王二赞, sex=男, age=28]
Student [id=4, name=王多燕, sex=女, age=26]

7.3 多对一映射

7.3.1 java bean

• 同 6.1.1

7.3.2 mapper配置

• StudentMapper.xml

<mapper namespace="com.zhougl.web.dao.mapper.StudentMapper">
  <resultMap id="StudentMap" type="com.zhougl.web.beans.Student">
    <id column="studentId" jdbcType="DECIMAL" property="id" />
    <result column="studentName" jdbcType="VARCHAR" property="name" />
    <result column="SEX" jdbcType="VARCHAR" property="sex" />
    <result column="AGE" jdbcType="DECIMAL" property="age" />
    <!-- 多对一 -->
    <association property="webClass" javaType="com.zhougl.web.beans.WebClass">
        <id column="ID" jdbcType="DECIMAL" property="id" />
        <result column="CODE" jdbcType="VARCHAR" property="code" />
        <result column="NAME" jdbcType="VARCHAR" property="name" />
    </association>
  </resultMap>
 
  <sql id="Base_Column_List">
    ID as studentId, NAME as studentName, SEX, AGE, CLASS_ID
  </sql>
  <sql id="student_List">
    ${student}.ID as studentId, ${student}.NAME as studentName, ${student}.SEX, ${student}.AGE, ${student}.CLASS_ID
  </sql>
  <sql id="Web_Class_Column_List">
        ${webClass}.ID , ${webClass}.CODE, ${webClass}.NAME 
  </sql>
 <!-- 多表连接 -->
 <!-- 查出来的列同名时需要使用别名区分 -->
  <select id="selectStudentById" parameterType="int" resultMap="StudentMap">
    select 
    <include refid="student_List" >
        <property name="student" value="s"/>
    </include>,
    <include refid="Web_Class_Column_List" >
        <property name="webClass" value="c"/>
    </include>
    from STUDENT s,WEB_CLASS c
    where s.class_id=c.id and s.ID = #{id,jdbcType=DECIMAL}
  </select>
  <select id="selectStudentByClassId" parameterType="int" resultMap="StudentMap">
    select 
    <include refid="Base_Column_List" />
    from STUDENT
    where CLASS_ID = #{classId,jdbcType=DECIMAL}
  </select>
 
</mapper>

7.3.3 mapper接口

• StudentMapper.java

public interface StudentMapper {
   
    Student selectStudentById(int id);
    List<Student> selectStudentByClassId(int classId);

}

7.3.4 测试类

public class OneToManyTest {
    //测试多对一，查询学生（多）级联查询班级（一）
    public void testManyToOne(SqlSession session) {
        StudentMapper studentMapper = session.getMapper(StudentMapper.class);
        Student student = studentMapper.selectStudentById(1);
        System.out.println(student);
        System.out.println(student.getWebClass().toString());
    }

• 结果

==>  Preparing: select s.ID as studentId, s.NAME as studentName, s.SEX, s.AGE, s.CLASS_ID , c.ID , c.CODE, c.NAME from STUDENT s,WEB_CLASS c where s.class_id=c.id and s.ID = ? 
==> Parameters: 1(Integer)
<==      Total: 1
Student [id=1, name=王一倩, sex=女, age=22]
WebClass [id=1, code=C001, name=大乱斗指导班]

7.4 多对多映射

7.4.1 java bean

public class WebOrder {
    private BigDecimal id;
    private String code;
    private BigDecimal total;
    private BigDecimal userId;
    //订单和用户是多对一关系
    private WebUser user;
    //订单和商品是多对多关系
    private List<WebArticle> articles;
}

public class WebUser {
    private BigDecimal id;
    private String username;
    private String loginname;
    private String password;
    private String phone;
    private String address;
    
    //用户和订单是一对多关系
    private List<WebOrder> orders;
}

public class WebArticle {
    private BigDecimal id;
    private String name;
    private BigDecimal price;
    private String remark;
}

7.4.2 mapper配置

• WebOrderMapper.xml

<mapper namespace="com.zhougl.web.dao.mapper.WebOrderMapper">
  <resultMap id="BaseResultMap" type="com.zhougl.web.beans.WebOrder">
    <id column="oId" jdbcType="DECIMAL" property="id" />
    <result column="CODE" jdbcType="VARCHAR" property="code" />
    <result column="TOTAL" jdbcType="DECIMAL" property="total" />
    <!-- 多对一关联 -->
    <association property="user" javaType="com.zhougl.web.beans.WebUser">
         <id column="ID" jdbcType="DECIMAL" property="id" />
        <result column="USERNAME" jdbcType="VARCHAR" property="username" />
        <result column="LOGINNAME" jdbcType="VARCHAR" property="loginname" />
        <result column="PASSWORD" jdbcType="VARCHAR" property="password" />
        <result column="PHONE" jdbcType="VARCHAR" property="phone" />
        <result column="ADDRESS" jdbcType="VARCHAR" property="address" />
    </association>
    <!-- 多对多关联 -->
    <collection property="articles" javaType="ArrayList"
        column="oId" ofType="com.zhougl.web.beans.WebArticle"
        select="com.zhougl.web.dao.mapper.WebArticleMapper.selectArticleByOrderId"
        fetchType="lazy">
        <id column="ID" jdbcType="DECIMAL" property="id" />
        <result column="NAME" jdbcType="VARCHAR" property="name" />
        <result column="PRICE" jdbcType="DECIMAL" property="price" />
        <result column="REMARK" jdbcType="VARCHAR" property="remark" />
    </collection>
  </resultMap>
    <!-- 有同名列，需要使用别名 -->
  <select id="selectOrderById" parameterType="int" resultMap="BaseResultMap">
    select 
    o.ID as oId, o.CODE, o.TOTAL, u.*
    from WEB_ORDER o,WEB_USER u
    where o.user_id = u.id and o.ID = #{id,jdbcType=DECIMAL}
  </select>
  <select id="selectOrderByUserId" parameterType="int" resultType="com.zhougl.web.beans.WebOrder">
    select * from WEB_ORDER where user_id = #{userId,jdbcType=DECIMAL}
  </select>
</mapper>

<mapper namespace="com.zhougl.web.dao.mapper.WebUserMapper">
  <resultMap id="BaseResultMap" type="com.zhougl.web.beans.WebUser">
    <id column="ID" jdbcType="DECIMAL" property="id" />
    <result column="USERNAME" jdbcType="VARCHAR" property="username" />
    <result column="LOGINNAME" jdbcType="VARCHAR" property="loginname" />
    <result column="PASSWORD" jdbcType="VARCHAR" property="password" />
    <result column="PHONE" jdbcType="VARCHAR" property="phone" />
    <result column="ADDRESS" jdbcType="VARCHAR" property="address" />
    <!-- 一对多关联 -->
    <collection property="orders" javaType="ArrayList" 
        ofType="com.zhougl.web.beans.WebOrder" 
        column="id" select="com.zhougl.web.dao.mapper.WebOrderMapper.selectOrderByUserId" 
        fetchType="lazy">
        <id column="ID" jdbcType="DECIMAL" property="id" />
        <result column="CODE" jdbcType="VARCHAR" property="code" />
        <result column="TOTAL" jdbcType="DECIMAL" property="total" />
    </collection>
  </resultMap>
 
  <sql id="Base_Column_List">
    ID, USERNAME, LOGINNAME, PASSWORD, PHONE, ADDRESS
  </sql>
 
  <select id="selectUserById" parameterType="int" resultMap="BaseResultMap">
    select 
    <include refid="Base_Column_List" />
    from WEB_USER
    where ID = #{id,jdbcType=DECIMAL}
  </select>
  
</mapper>

<mapper namespace="com.zhougl.web.dao.mapper.WebArticleMapper">
  <resultMap id="BaseResultMap" type="com.zhougl.web.beans.WebArticle">
    <id column="ID" jdbcType="DECIMAL" property="id" />
    <result column="NAME" jdbcType="VARCHAR" property="name" />
    <result column="PRICE" jdbcType="DECIMAL" property="price" />
    <result column="REMARK" jdbcType="VARCHAR" property="remark" />
  </resultMap>
  
  <sql id="Base_Column_List">
    ID, NAME, PRICE, REMARK
  </sql>
  
  <select id="selectArticleByOrderId" parameterType="int" resultMap="BaseResultMap">
    select 
    <include refid="Base_Column_List" />
    from WEB_ARTICLE where id in(
        select article_id from WEB_ITEM where order_id =#{orderId,jdbcType=DECIMAL}
    )
  </select>
  
</mapper>

7.4.3 mapper接口

public interface WebOrderMapper {
    List<WebOrder> selectOrderByUserId(int userId);
    WebOrder selectOrderById(int id); 
}
public interface WebUserMapper {
    WebUser selectUserById(int id);
}

7.4.4 测试类

public class ManyToManyTest {

    public static void main(String[] args) {
        SqlSession session = SqlSessionFactoryUtil.getSession();
        ManyToManyTest test = new ManyToManyTest();
        //test.testOneToMany(session);
        test.testManyToMany(session);
        session.commit();
        session.close();
    }
    public void testOneToMany(SqlSession session) {
        WebUserMapper userMapper = session.getMapper(WebUserMapper.class);
        WebUser user = userMapper.selectUserById(1);
        System.out.println(user.getUsername()+" "+user.getLoginname()+" "+user.getPhone()+" "+user.getAddress());
        List<WebOrder> orders = user.getOrders();
        for (WebOrder webOrder : orders) {
            System.out.println(webOrder.toString());
        }
    }
    public void testManyToMany(SqlSession session) {
        WebOrderMapper orderMapper = session.getMapper(WebOrderMapper.class);
        WebOrder order = orderMapper.selectOrderById(1);
        System.out.println(order.getCode()+" "+order.getTotal());
        WebUser user = order.getUser();
        System.out.println(user.toString());
        List<WebArticle> articles = order.getArticles();
        for (WebArticle webArticle : articles) {
            System.out.println(webArticle.toString());
        }
    }
}