Mybatis -- 复杂查询环境搭建，多对一的处理，一对多的处理、按结果嵌套查询（推荐）、按照查询嵌套处理一. 复杂查询环境搭建二. 多对一的处理 – （学生对老师）三. 一对多的处理 – （老师对学生）四. 小结

54 阅读 0 评论 36 点赞

我是靠谱客的博主忐忑母鸡，最近开发中收集的这篇文章主要介绍Mybatis -- 复杂查询环境搭建，多对一的处理，一对多的处理、按结果嵌套查询（推荐）、按照查询嵌套处理一. 复杂查询环境搭建二. 多对一的处理 – （学生对老师）三. 一对多的处理 – （老师对学生）四. 小结，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

文章目录

一. 复杂查询环境搭建
- 1）搭建数据表 Teacher、Student
- 2）导入lombok
- 3）新建实体类 Teacher，Student
- 4）建立Mapper接口
- 5）建立Mapper.XML文件
- 6）在核心配置文件中绑定注册我们的Mapper接口或者文件
- 7）测试查询是否能够成功
二. 多对一的处理 -- （学生对老师）
- 1) Teacher、Student 实体类
- 2）TeacherMapper、StudentMapper 接口
- 3) TeacherMapper.xml、StudentMapper.xml 配置文件（按结果嵌套查询（推荐）、按照查询嵌套处理）
- 4) 测试
三. 一对多的处理 -- （老师对学生）
- 1) Teacher、Student 实体类
- 2）TeacherMapper、StudentMapper 接口
- 3) TeacherMapper.xml、StudentMapper.xml 配置文件（按结果嵌套查询（推荐）、按照查询嵌套处理）
- 4) 测试
四. 小结

一. 复杂查询环境搭建

具体情况：
多个学生，对应一个老师

对于学生这边而言，关联，多个学生，关联一个老师【多对一】
对于老师而言，集合，一个老师，有很多学生【一对多】

工程目录如下：

1）搭建数据表 Teacher、Student

Teacher 数据表包含两个字段 id、name
Student 数据表包含三个字段 id、name、tid

2）导入lombok

在 Maven 工程 pom.xml 导入 LomBok.jar包

        <!--LOG4J-->
        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>

3）新建实体类 Teacher，Student

使用注解形式创建实体类 Teacher，Student

Teacher 实体类

@Data
public class Teacher {
    private int id;
    private String name;
}

Student 实体类

@Data
public class Student {

    private int id;
    private String name;

    // 多个学生关联一个老师
    private Teacher teacher;
}

4）建立Mapper接口

TeacherMapper

public interface TeacherMapper {

    // 根据id号查询
    @Select("select * from teacher where id = #{tid}")
    Teacher getTeacher(@Param("tid") int id);
}

StudentMapper

public interface StudentMapper {
}

5）建立Mapper.XML文件

创建 TeacherMapper.xml 和 StudentMapper.xml 配置文件

TeacherMapper.xml

<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.dao.TeacherMapper">
</mapper>

StudentMapper.xml

<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.dao.StudentMapper">
</mapper>

6）在核心配置文件中绑定注册我们的Mapper接口或者文件

在 mybatis-config 配置文件中：

    <mappers>
        <mapper class="com.dao.TeacherMapper"></mapper>
        <mapper class="com.dao.StudentMapper"></mapper>
    </mappers>

7）测试查询是否能够成功

    @Test
    public void getTeacher(){
        //第一步：获得SqlSession对象
        SqlSession sqlSession = MybatisUtils.getSqlSession();

        //方式一：getMapper
        TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);
        Teacher teacher = teacherMapper.getTeacher(1);
        System.out.println(teacher);

        //关闭SqlSession
        sqlSession.close();
    }

二. 多对一的处理 – （学生对老师）

1) Teacher、Student 实体类

Teacher 实体类

@Data
public class Teacher {
    private int id;
    private String name;
}

Student 实体类

@Data
public class Student {

    private int id;
    private String name;

    // 多个学生关联一个老师
    private Teacher teacher;
}

2）TeacherMapper、StudentMapper 接口

TeacherMapper

public interface TeacherMapper {

    // 根据id号查询
    @Select("select * from teacher where id = #{tid}")
    Teacher getTeacher(@Param("tid") int id);
}

StudentMapper

public interface StudentMapper {
    // 多对一
    // 方式一
    public List<Student> getStudent1();
    // 方式二
    public List<Student> getStudent2();
}

3) TeacherMapper.xml、StudentMapper.xml 配置文件（按结果嵌套查询（推荐）、按照查询嵌套处理）

TeacherMapper.xml

<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.dao.TeacherMapper">

</mapper>

StudentMapper.xml

<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.dao.StudentMapper">
    <!-- 多对一 -->
    <!-- 方式一（子查询）按照查询嵌套处理 -->
    <select id="getStudent1" resultMap="StudentTeacher1">
        select * from student
    </select>

    <resultMap id="StudentTeacher1" type="Student"><!-- resultmap -->
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        <!--复杂的属性，我们需要单独处理 对象： association 集合： collection -->
        <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
    </resultMap>

    <select id="getTeacher" resultType="Teacher">
        select * from teacher where id = #{id}
    </select>

    <!-- 方式二（连表查询）按照结果嵌套处理 -->
    <select id="getStudent2" resultMap="StudentTeacher2">
        select s.id sid, s.name sname, t.id tid, t.name tname
        from student s,teacher t
        where s.tid = t.id;
    </select>

    <resultMap id="StudentTeacher2" type="Student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
        <association property="teacher" javaType="Teacher">
            <result property="id" column="tid"/>
            <result property="name" column="tname"/>
        </association>
    </resultMap>
</mapper>

4) 测试

public class My_Test {

    @Test
    public void getTeacher(){
        //第一步：获得SqlSession对象
        SqlSession sqlSession = MybatisUtils.getSqlSession();

        //方式一：getMapper
        TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);
        Teacher teacher = teacherMapper.getTeacher(1);
        System.out.println(teacher);

        //关闭SqlSession
        sqlSession.close();
    }

    @Test
    public void getStudent1(){
        //第一步：获得SqlSession对象
        SqlSession sqlSession = MybatisUtils.getSqlSession();

        //方式一：getMapper
        StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> list = studentMapper.getStudent1();
        for(Student student:list){
            System.out.println(student);
        }

        //关闭SqlSession
        sqlSession.close();
    }

    @Test
    public void getStudent2(){
        //第一步：获得SqlSession对象
        SqlSession sqlSession = MybatisUtils.getSqlSession();

        //方式一：getMapper
        StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> list = studentMapper.getStudent2();
        for(Student student:list){
            System.out.println(student);
        }

        //关闭SqlSession
        sqlSession.close();
    }
}

三. 一对多的处理 – （老师对学生）

1) Teacher、Student 实体类

Teacher 实体类

@Data
public class Teacher {
    private int id;
    private String name;

    private List<Student> students;
}

Student 实体类

@Data
public class Student {

    private int id;
    private String name;
    private String tid;
}

2）TeacherMapper、StudentMapper 接口

TeacherMapper 接口

public interface TeacherMapper {
    List<Teacher> getTeacher();

    Teacher getTeacher1(int id);

    Teacher getTeacher2(int id);
}

StudentMapper 接口

public interface StudentMapper {


}

3) TeacherMapper.xml、StudentMapper.xml 配置文件（按结果嵌套查询（推荐）、按照查询嵌套处理）

TeacherMapper.xml

<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.dao.TeacherMapper">

    <!-- 测试代码1 -->
    <select id="getTeacher" resultType="Teacher">
        select * from teacher
    </select>

    <!--按结果嵌套查询-->
    <select id="getTeacher1" resultMap="TeacherStudent">
        select s.id sid, s.name sname, t.name tname,t.id tid
        from student s,teacher t
        where s.tid = t.id and t.id = #{tid}
    </select>

    <resultMap id="TeacherStudent" type="Teacher">
        <result property="id" column="tid"/>
        <result property="name" column="tname"/>
        <!--复杂的属性，我们需要单独处理 对象： association 集合： collection
        javaType="" 指定属性的类型！
        集合中的泛型信息，我们使用ofType获取
        -->
        <collection property="students" ofType="Student">
            <result property="id" column="sid"/>
            <result property="name" column="sname"/>
            <result property="tid" column="tid"/>
        </collection>
    </resultMap>

    <!-- 按照查询嵌套处理 -->
    <select id="getTeacher2" resultMap="TeacherStudent2">
        select * from mybatis.teacher where id = #{tid}
    </select>

    <resultMap id="TeacherStudent2" type="Teacher">
        <collection property="students" javaType="ArrayList" ofType="Student" select="getStudentByTeacherId" column="id"/>
    </resultMap>

    <select id="getStudentByTeacherId" resultType="Student">
        select * from mybatis.student where tid = #{tid}
    </select>

</mapper>

StudentMapper.xml

<?xml version="1.0" encoding="UTF8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.dao.StudentMapper">

</mapper>

4) 测试

public class My_Test 
{
    @Test
    public void getTeacher(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();

        //方式一：getMapper
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        List<Teacher> teachers = mapper.getTeacher();
        for(Teacher teacher: teachers){
            System.out.println(teacher);
        }

        //关闭SqlSession
        sqlSession.close();
    }


    @Test
    public void getTeacher1(){
        //第一步：获得SqlSession对象
        SqlSession sqlSession = MybatisUtils.getSqlSession();

        //方式一：getMapper
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        Teacher teacher = mapper.getTeacher1(1);
        System.out.println(teacher);

        //关闭SqlSession
        sqlSession.close();
    }

    @Test
    public void getTeacher2(){
        //第一步：获得SqlSession对象
        SqlSession sqlSession = MybatisUtils.getSqlSession();

        //方式一：getMapper
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        Teacher teacher = mapper.getTeacher2(1);
        System.out.println(teacher);

        //关闭SqlSession
        sqlSession.close();
    }
}

四. 小结

- 关联 - association 【多对一】 - 集合 - collection 【一对多】 - javaType & ofType 1） JavaType 用来指定实体类中属性的类型 2） ofType 用来指定映射到List或者集合中的 pojo类型，泛型中的约束类型！ - 注意点： 1）保证SQL的可读性，尽量保证通俗易懂 2）注意一对多和多对一中，属性名和字段的问题！ 3）如果问题不好排查错误，可以使用日志，建议使用 Log4j