MyBatis 详解(八)

发布于 2021-07-27  83 次阅读


resultMap(重点)

resultMap 元素表示结果映射集,是 MyBatis 中最重要也是最强大的元素,主要用来定义映射规则、级联的更新以及定义类型转化器等。

解决属性名和字段名不一致问题

之前使用 resultType 属性时,必须保证属性名和字段名一样,否则会取不出值,显示为 null。而这个问题就可以通过使用 resultMap 元素解决。(其实还可以通过在 sql 语句中起与类属性相同的别名解决这个问题,不过实际开发中并不会这样用)

package ml.guest997.pojo;

public class User02 {

    private int uid;
    private String name;
    private String pwd;
    //省略 get、set 方法
    @Override
    public String toString() {
        return "User02{" +
                "uid=" + uid +
                ", name='" + name + '\'' +
                ", pwd='" + pwd + '\'' +
                '}';
    }
}
//通过 id 查询用户
User02 getUserById(int i);
<resultMap id="getUserById" type="ml.guest997.pojo.User02">    <!--指定返回类型-->
    <!-- property 是类属性,column 是查询结果的列名-->
    <id property="uid" column="id"/>    <!--用于表示哪个列是主键-->
    <result property="name" column="name"/>
</resultMap>
<!--指定 resultMap 的 id-->
<select id="getUserById" parameterType="int" resultMap="getUserById">
    select * from mybatis.user where id = #{id}
</select>
@Test
public void getUserById(){
    SqlSession sqlSession = MyBatisUtil.getSqlSession();
    UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
    User02 user = userMapper.getUserById(1);
    System.out.println(user);
    sqlSession.close();
}
//结果为 User02{uid=1, name='Guest997', pwd='123456'}

环境搭建

在 mybatis 数据库下创建名为 student 和 teacher 的数据库表并插入数据

CREATE TABLE `teacher` (
  `id` INT(10) NOT NULL,
  `name` VARCHAR(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO `teacher` (`id`, `name`) VALUES (1, '秦老师'); 

CREATE TABLE `student` (
  `id` INT(10) NOT NULL,`user`
  `name` VARCHAR(30) DEFAULT NULL,
  `tid` INT(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fktid` (`tid`),
  CONSTRAINT `fktid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO `student` (`id`, `name`, `tid`) VALUES (1, '小明', 1); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES (2, '小红', 1); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES (3, '小张', 1); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES (4, '小李', 1); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES (5, '小王', 1);

编写 pojo 层实体类

package ml.guest997.pojo;

import lombok.Data;

@Data
public class Student {
    private int id;
    private String name;
}
package ml.guest997.pojo;

import lombok.Data;

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

编写 Mapper 层接口和实现类

package ml.guest997.mapper;

public interface StudentMapper {
}
package ml.guest997.mapper;

public interface TeacherMapper {
}

为了分离接口和 mapper 配置文件,在 resources 下创建和接口所在相同的包。

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

<mapper namespace="ml.guest997.mapper.StudentMapper">

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

<mapper namespace="ml.guest997.mapper.TeacherMapper">

</mapper>

注册配置文件

<typeAliases>
    <!--包扫描使用别名-->
    <package name="ml.guest997.pojo"/>
</typeAliases>
<package name="ml.guest997.mapper"/>

多对一(查询出指定学生以及对应老师的信息)

Stedent 实体类中添加 Teacher 对象属性。

private Teacher teacher;    //学生需要关联一个老师

按照查询嵌套处理(子查询)

Student getStudent(@Param("id") int id);
<!--
1.根据 id 查询出指定学生信息
2.再根据学生的 tid 字段查询出相应的老师信息
-->
<select id="getStudent" resultMap="StudentTeacher">
    select * from student where id = #{id}
</select>
<resultMap id="StudentTeacher" type="student">
    <!--属性是一个对象使用 association 属性处理-->
    <association property="teacher" column="tid" select="getTeacher"/>
</resultMap>
<select id="getTeacher" resultType="teacher">
    select * from teacher where id = #{id}
</select>
package ml.guest997.mapper;

import ml.guest997.pojo.Student;
import ml.guest997.util.MyBatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

public class StudentMapperTest {
    @Test
    public void getStudent(){
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
        Student student = studentMapper.getStudent(1);
        System.out.println(student);
        sqlSession.close();
    }
}
//结果为 Student(id=1, name=小明, teacher=Teacher(id=1, name=秦老师))

按照结果查询嵌套处理(连接查询)

Student getStudent02(@Param("id") int id);
<!--使用完整的 sql 语句,在 sql 语句中给字段起别名,这样就能通过嵌套结果映射来去除重复的数据-->
<select id="getStudent02" resultMap="StudentTeacher02">
    select s.id sid,s.name sname,t.id tid,t.name tname from student s,teacher t where s.tid=t.id and s.id = #{id}
</select>
<resultMap id="StudentTeacher02" type="student">
    <id property="id" column="sid"/>
    <result property="name" column="sname"/>
    <association property="teacher">
        <id property="id" column="tid"/>
        <result property="name" column="tname"/>
    </association>
</resultMap>
@Test
public void getStudent02(){
    SqlSession sqlSession = MyBatisUtil.getSqlSession();
    StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
    Student student = studentMapper.getStudent02(1);
    System.out.println(student);
    sqlSession.close();
}
//结果为 Student(id=1, name=小明, teacher=Teacher(id=1, name=秦老师))

一对多(查询出指定老师以及对应学生的信息)

注意:需要新建一个子项目或者把上面多对一的文件删除,要不然会报错。注释掉 Student 实体类中的 Teacher 对象属性,添加 tid 属性;然后在 Teacher 实体类中添加以 List 封装的多个 Student 对象属性。

package ml.guest997.pojo;

import lombok.Data;

@Data
public class Student {
    private int id;
    private String name;
//    private Teacher teacher;    //学生需要关联一个老师
    private int tid;
}
private List<Student> students;     //老师下有多个学生

按照查询嵌套处理(子查询)

Teacher getTeacher(@Param("id") int id);
<!--
1.根据 id 查询出指定老师信息
2.再根据老师的 id 字段查询出与 tid 字段相等的多个学生
-->
<select id="getTeacher" resultMap="TeacherStudents">
    select * from teacher where id = #{id}
</select>
<resultMap id="TeacherStudents" type="teacher">
    <id property="id" column="id"/>
    <!--属性是一个集合使用 collection 属性处理-->
    <collection property="students" select="getStudent" column="id"/>
</resultMap>
<select id="getStudent" resultType="student">
    select * from student where tid = #{id}
</select>
package ml.guest997.mapper;

import ml.guest997.pojo.Teacher;
import ml.guest997.util.MyBatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

public class TeacherMapperTest {
    @Test
    public void getTeacher(){
        SqlSession sqlSession = MyBatisUtil.getSqlSession();
        TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);
        Teacher teacher = teacherMapper.getTeacher(1);
        System.out.println(teacher);
        sqlSession.close();
    }
}
/*结果为
Teacher(id=1, name=秦老师, students=[
Student(id=1, name=小明, tid=1), 
Student(id=2, name=小红, tid=1), 
Student(id=3, name=小张, tid=1), 
Student(id=4, name=小李, tid=1), 
Student(id=5, name=小王, tid=1)
])
*/

按照结果查询嵌套处理(连接查询)

Teacher getTeacher02(@Param("id") int id);
<select id="getTeacher02" resultMap="TeacherStudents02">
    select t.id tid,t.name tname,s.id sid,s.name sname from student s,teacher t where t.id=s.tid and t.id=#{id}
</select>
<resultMap id="TeacherStudents02" type="teacher">
    <id property="id" column="tid"/>
    <result property="name" column="tname"/>
    <collection property="students" ofType="student">
        <id property="id" column="sid"/>
        <result property="name" column="sname"/>
        <result property="tid" column="tid"/>
    </collection>
</resultMap>
@Test
public void getTeacher02(){
    SqlSession sqlSession = MyBatisUtil.getSqlSession();
    TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);
    Teacher teacher = teacherMapper.getTeacher02(1);
    System.out.println(teacher);
    sqlSession.close();
}
/*结果为
Teacher(id=1, name=秦老师, students=[
Student(id=1, name=小明, tid=1), 
Student(id=2, name=小红, tid=1), 
Student(id=3, name=小张, tid=1), 
Student(id=4, name=小李, tid=1), 
Student(id=5, name=小王, tid=1)
])
*/