MyBatis总结(2)--级联查询

一、一对一
1、数据库设计
建立两张表，class表和teacher表，一个老师只能带一个班（建表语句略）
2、domain对象

package com.person.test.mybatis.domain;import java.util.List;/** * Copyright: Copyright (c) 2017/10/24 * Description: 描述班级的domain对象 * CLASS_NAME: Classes * PACKAGE_NAME: com.person.test.mybatis.domain * version: v1.0.0 * author: Jin Biao * date: 2017/10/24 8:56 */public class Classes {    private int id;    private String name;    private Teacher teacher;    public Classes() {    }    public Classes(int id, String name, Teacher teacher) {        this.id = id;        this.name = name;        this.teacher = teacher;    }    public int getId() {        return id;    }    public void setId(int id) {        this.id = id;    }    public String getName() {        return name;    }    public void setName(String name) {        this.name = name;    }    public Teacher getTeacher() {        return teacher;    }    public void setTeacher(Teacher teacher) {        this.teacher = teacher;    }    @Override    public String toString() {        return "Classes{" +                "id=" + id +                ", name='" + name + '\'' +                ", teacher=" + teacher +                        '}';    }}

package com.person.test.mybatis.domain;/** * Copyright: Copyright (c) 2017/10/24 * <p> * Description: * CLASS_NAME: Teacher * PACKAGE_NAME: com.person.test.mybatis.domain * version: v1.0.0 * author: Jin Biao * date: 2017/10/24 8:57 */public class Teacher {    private int id;    private String tName;    public Teacher() {    }    public Teacher(int id, String tName) {        this.id = id;        this.tName = tName;    }    public int getId() {        return id;    }    public void setId(int id) {        this.id = id;    }    public String gettName() {        return tName;    }    public void settName(String tName) {        this.tName = tName;    }    public String toString(){        return "Teacher [ id = " + id + " t_name = " + tName + "]";    }}

3、mapper.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="mybatis.mapping.classMapper"><!--使用resultMap属性实现一对一的对应关系-->    <select id="getById" parameterType="int" resultMap="ClassResultMap1">        SELECT c.*,t.id t_id,t.name t_name FROM class c,teacher t  WHERE  c.t_id = t.id AND c.id = #{id}    </select>    <!--column对应的是表中的字段名-->     <resultMap id="ClassResultMap1" type="Classes">        <id property="id" column="id"/>        <result property="name" column="name"/>        <!--这里使用<association>标签实现两张表的查询关联 column 指的是classes的外键，这里有两种实现方法：1.一种是关联查询：       SELECT c.*,t.id t_id,t.name t_name FROM class c,teacher t  WHERE  c.t_id = t.id AND c.id = #{id}  2.另一种是嵌套查询，先得到t_id,在通过id去teacher表查询teacher的信息-->        <association property="teacher" javaType="Teacher" column="t_id" >        <!--这里需要特别关注的是如果两个表的字段名一样，要在查询结果将其中一个的字段名重命名，如果没有，不会报错，但是查询得到的teacher的结果会是classes的数据-->            <id property="id" column="t_id"/>            <result property="tName" column="t_name"/>        </association><!--嵌套查询的实现  select 指的是获取哪个查询语句这里使用的是teacherMpper.xml文件中的getById语句-->        <association property="teacher" column="t_id" select="mybatis.mapping.teacherMapper.getById"/>    </resultMap></mapper>

4.junit测试

• MybatisUtil类

package com.person.test.mybatis.utils;import org.apache.ibatis.session.SqlSession;import org.apache.ibatis.session.SqlSessionFactory;import org.apache.ibatis.session.SqlSessionFactoryBuilder;import java.io.FileInputStream;import java.io.InputStream;/** * Copyright: Copyright (c) 2017/10/23 * <p> * Description: * CLASS_NAME: MybatisUtil * PACKAGE_NAME: com.person.test.mybatis.utils * version: v1.0.0 * author: Jin Biao * date: 2017/10/23 8:57 */public class MybatisUtil {    /** method_name:getSessionFactory    * param:  void    * description: 获取sessionFactory    * create_user: Jin Biao     * create_date: 2017/10/23     * create_time: 9:07     **/    public static SqlSessionFactory getSessionFactory() throws Exception{        String resource = "src/com/person/test/mybatis/conf.xml";        //读取配置文件        InputStream inputStream = new FileInputStream(resource);        //建立factory        SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(inputStream);        return factory;    }    public static SqlSession getSqlSession() throws Exception {        return getSessionFactory().openSession();    }    /** method_name: getSqlSession    * param:  boolean isAutoCommit    * description: 是否自动提交事务  true：自动提交  false：需要手动提交    * create_user: Jin Biao     * create_date: 2017/10/23     * create_time: 9:13     **/    public static SqlSession getSqlSession(boolean isAutoCommit) throws Exception {        return getSessionFactory().openSession(isAutoCommit);    }}

• 测试内容

   @Test    public void testGetById() throws Exception {//这里使用MybatisUtil将mybatis实现的功能进行封装        SqlSession sqlSession = MybatisUtil.getSqlSession(true);        String statement = "mybatis.mapping.classMapper.getById";        Classes classes = sqlSession.selectOne(statement,2);        sqlSession.close();        System.out.println(classes);    }

二、一对多查询
1、数据库设计
再新增一张表student表，一个班级内有多个学生，一个学生只能在一个班级，建表语句也不多说。
2.domain对象
Student对象

package com.person.test.mybatis.domain;/** * Copyright: Copyright (c) 2017/10/25 * Description: * CLASS_NAME: Student * PACKAGE_NAME: com.person.test.mybatis.domain * version: v1.0.0 * author: Jin Biao * date: 2017/10/25 17:35 */public class Student {    private int id;    private String name;    public Student(){    }    public int getId() {        return id;    }    public void setId(int id) {        this.id = id;    }    public String getName() {        return name;    }    public void setName(String name) {        this.name = name;    }    @Override    public String toString() {        return "Student{" +                "id=" + id +                ", name='" + name + '\'' +                '}';    }}

Class对象，这里要多一个属性List<Student>package com.person.test.mybatis.domain;import java.util.List;/** * Copyright: Copyright (c) 2017/10/24 * Description: 描述班级的domain对象 * CLASS_NAME: Classes * PACKAGE_NAME: com.person.test.mybatis.domain * version: v1.0.0 * author: Jin Biao * date: 2017/10/24 8:56 */public class Classes {    private int id;    private String name;    private List<Student> students;    public Classes() {    }    public int getId() {        return id;    }    public void setId(int id) {        this.id = id;    }    public String getName() {        return name;    }    public void setName(String name) {        this.name = name;    }    public List<Student> getStudents() {        return students;    }    public void setStudents(List<Student> students) {        this.students = students;    }    @Override    public String toString() {        return "Classes{" +                "id=" + id +                ", name='" + name + '\'' +                               ", students=" + students +                               '}';    }}

3、classes.xml文件
一对多的实现和一对一相差不大，将association标签换成collection

<?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="mybatis.mapping.classMapper">  <select id="getClasses" parameterType="int" resultMap="ClassResultMap2">    SELECT * FROM class c , student s where c.id = s.c_id AND c.id = #{id}</select> <!--一对多联合查询--> <resultMap id="ClassResultMap2" type="Classes">  <id property="id" column="id"/>  <result property="name" column="name"/>  <collection property="students" ofType="Student">      <id property="id" column="s_id"/>      <result property="name" column="s_name"/>  </collection></resultMap></mapper>

4、junit测试

   @Test    public void getClasses() throws Exception {        SqlSession sqlSession = MybatisUtil.getSqlSession(true);        String statement = "mybatis.mapping.classMapper.getClasses";        Classes classes = sqlSession.selectOne(statement,1);        sqlSession.close();        System.out.println(classes);    }