MyBatis（四）resultMap结果集映射

Mybatis的resultMap结果集映射

一、解决问题

解决数据库中字段名和属性名不一致的情况

比如说：

public class User {
    private Integer id;
    private String name;
    private String password;
}
           

<select id="selectUserById" resultMap="User">
  select id , name , pwd from user where id = #{id}
</select>
           

如果这样去查询操作的话，User的password就会对应不上，会为null

解决方案1

在sql语句中指定列名的别名

<select id="selectUserById" resultMap="User">
  select id , name , pwd  as password from user where id = #{id}
</select>
           

解决方案2

• 使用ResultMap映射

<resultMap id="UserMap" type="User">
   <id column="id" property="id"/>
   <result column="name" property="name"/>
   <result column="pwd" property="password"/>
</resultMap>
<select id="selectUserById" resultMap="UserMap">
  select id , name , pwd from user where id = #{id}
</select>
           

和中最基本的映射内容

他们可以将查询的结果中一个对应字段的值映射到返回结果对象的属性。

返回结果对象就是 标签中的type

二、ResultMap的嵌套查询使用

给定Teacher和Student类

public class Teacher {
    private Integer id;
    private String name;
    private List<Student> students;
}
public class Student {
    private Integer id;
    private String name;
    private Teacher teacher;
    private int tid;
}
           

编写对应的Mapper接口

public interface StudentMapper {
    //查询所有的学生信息以及对应的老师信息
    public List<Student> getStudent();
    
    public List<Student> getStudent2();
}

public interface TeacherMapper {

    Teacher getTeacher(@Param("tid") int id);


    Teacher getTandS(int id);
}
           

最主要的是mapper.xml中sql语句查询的结果如何对应到编写的poji类

以Teacher类为例

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.gx.dao.TeacherMapper">
    <select id="getTeacher" resultType="teacher">
        select * from teacher where id=#{tid}
    </select>
<!--ofType="student"-->
    <resultMap id="resultTeacher1" type="teacher">
        <result property="id" column="id"/>
        <collection property="students" column="id"
                    select="getStu">
        </collection>
    </resultMap>
    <select id="getStu" resultType="student">
        select * from student where tid=#{tid}
    </select>
    <select id="getTandS" resultMap="resultTeacher1">
        select * from teacher where id=#{id}
    </select>
</mapper>
           

在

getTandS

的sql语句查询中，使用了结果集映射resultMap，对应的就是

<resultMap id="resultTeacher1" type="teacher">
        <result property="id" column="id"/>
        <collection property="students" column="id"
                    select="getStu">
        </collection>
    </resultMap>
           

分析这个resultMap

• collection

  元素用于处理查询结果中关联其他对象集合的情况

将查询结果的id字段对应到

Teacher

类的id属性上面，这里使用collection标签的话意思就是将查询的id字段作为

参数传递到

select

对应的sql查询语句

getStu

中。然后执行的sql语句返回的数据绑定的是

students

属性。

<select id="getStu" resultType="student">
        select * from student where tid=#{tid}
    </select>
           

这个sql执行将传递的id作为参数进行查询操作，返回的字段对应的对象是student。

student类

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


    <!--按照查询嵌套处理-->
    <resultMap id="resultMap1" type="student">
        <!--teacher对象-->
        <association property="teacher" column="tid"
                javaType="teacher"
                select="getTeacher"/>
    </resultMap>
    <select id="getStudent" resultMap="resultMap1">
        select * from student
    </select>
    <select id="getTeacher" resultType="teacher">
        select * from teacher
    </select>

    <resultMap id="resultMap2" 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="getStudent2" resultMap="resultMap2">
        select
            s.id as sid,s.name as sname,t.name as tname
        from
            student s,teacher t
        where
            s.tid=t.id
    </select>

</mapper>
           

查询用户的方法

getStudent

执行如下的sql语句

<select id="getStudent" resultMap="resultMap1">
        select * from student
    </select>
           

resultMap1对应的结果集映射

• association元素用于处理查询结果中关联其他对象的情况

<resultMap id="resultMap1" type="student">
        <!--teacher对象-->
        <association property="teacher" column="tid"
                javaType="teacher"
                select="getTeacher"/>
    </resultMap>
           

同样，执行sql语句之后，将结果返回到结果集映射resultMap中。association元素将

select

的查询结果关联到

teacher

属性中，参数是以tid字段传递到

getTeacher

的select语句

<select id="getTeacher" resultType="teacher">
        select * from teacher
    </select>
           

这个sql执行将传递的id作为参数进行查询操作，返回的字段对应的对象是teacher。