Mybatis结果集映射+分页

结果集映射

多对一，比如多个学生被一个老师教授课程

实体类，老师

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

学生

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Student {
    private int id;
    private String name;

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

StudentMapper

public interface StudentMapper {
    public List<Student> getStudent();

    public List<Student> getStudent2();
}

TeacherMapper

public interface TeacherMapper {
    @Select("select * from teacher where id = #{tid}")
    Teacher getTeacher(@Param("tid") int id);
}

UserMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--configuration核心配置文件-->
<mapper namespace="com.xiaofan.dao.StudentMapper">

<!--    按照结果嵌套处理-->
    <select id="getStudent2" resultMap="StudentTeacher2">
        select s.id sid,s.name sname,t.name tname from student s,teacher t where s.tid=t.id
    </select>
    <!--        colum对应数据库中的字段，property对应实体类的属性-->
    <resultMap id="StudentTeacher2" type="Student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
        <association property="teacher" javaType="Teacher">
            <result property="name" column="tname"/>
        </association>
    </resultMap>


  <!--  =======================================================================  -->
<!--    按照查询嵌套处理-->
<!--    子查询-->

    <select id="getStudent" resultMap="StudentTeacher">
        select * from student
    </select>
    <resultMap id="StudentTeacher" type="Student">
        <result column="id" property="id" />
        <result column="name" property="name"/>
        <!--        复杂的属性需要单独处理,对象：association,集合：collection  -->
        <association column="tid" property="teacher" javaType="Teacher" select="getTeacher"/>
    </resultMap>
    <select id="getTeacher" resultType="Teacher">
        select * from teacher where id=#{tid}
    </select>
</mapper>

    @Test
    public void test1(){
        SqlSession sqlSession =MybatisUtils.getSqlSession();
        StudentMapper mapper =sqlSession.getMapper(StudentMapper.class);
        List<Student> studentList=mapper.getStudent();
        for (Student student : studentList) {
            System.out.println(student);
        }
        sqlSession.close();
    }

    @Test
    public void test2(){
        SqlSession sqlSession =MybatisUtils.getSqlSession();
        StudentMapper mapper =sqlSession.getMapper(StudentMapper.class);
        List<Student> studentList=mapper.getStudent2();
        for (Student student : studentList) {
            System.out.println(student);
        }
        sqlSession.close();
    }

test1()

test2()

一对多，一个老师可以教多名同学

实体类，学生

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Student {
    private int id;
    private String name;
    private int tid;
}

教师

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

    //一个老师要有多个学生
    private List<Student> students;
}

TeacherMapper

public interface TeacherMapper {
    //获取老师
    List<Teacher> getTeacher();

    //获取指定老师下的所有学生及老师的信息
    Teacher getTeacher(@Param("tid") int id);

    Teacher getTeacher2(@Param("tid") int id);
}

TeacherMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--configuration核心配置文件-->
<mapper namespace="com.xiaofan.dao.TeacherMapper">

<!--    按结果秦嵌套查询-->
    <select id="getTeacher" resultMap="TeacherStudent">
        select s.id sid,s.name sname,t.name tname,t.id tid
        from teacher t,student s 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"/>
        <!-- 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 teacher where id =#{tid}
    </select>
    <resultMap id="TeacherStudent2" type="Teacher">
        <collection property="students" column="id" javaType="ArrayList" ofType="Student" select="getStudent"/>
    </resultMap>
    <select id="getStudent" resultType="Student">
        select * from student where tid = #{id}
    </select>
</mapper>

测试类

public class MyTest {
    @Test
    public void test1(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        Teacher teacher1 = mapper.getTeacher(1);
        System.out.println(teacher1);
        sqlSession.close();
    }

    @Test
    public void test2(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        Teacher teacher1 = mapper.getTeacher2(1);
        System.out.println(teacher1);
        sqlSession.close();
    }

test1()

test2()

分页

   List<User> getUserByLimit(Map<String,Integer> map);

    <resultMap id="UserMap" type="User">
<!--        colum对应数据库中的字段，property对应实体类的属性-->
        <result column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="pwd" property="password"/>
    </resultMap>

    <select id="getUserByLimit" parameterType="map" resultMap="UserMap">
        select * from mybatis.user limit #{startIndex},#{pageSize}
    </select>

    @Test
    public void getUserByLimit(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        HashMap<String, Integer> hashMap = new HashMap<>();
        hashMap.put("startIndex",0);
        hashMap.put("pageSize",2);
        List<User> userByLimit = mapper.getUserByLimit(hashMap);

        for (User user : userByLimit) {
            System.out.println(user);
        }

        sqlSession.close();
    }