ResultMap结果集映射笔记
ResultMap的作用理解
有时候,当数据库中的某些属性名与Java Bean中的属性名不对应时,从数据库中查出的这些就为null。所有就需要通过ResultMap来映射这些字段来正常查询这些信息。
1.ResultMap的用法
<select id="getUser" resultMap="userResultMap">
SELECT * from user;
</select>
<resultMap id="userResultMap" type="User">
<result property="id" column="id"/>
<result property="name" column="name"/>
<result property="password" column="pwd"/>
</resultMap>
- 查询全部用户使用select标签时,设置resultMap属性,其值为你定义resultMap标签的id。
- resultMap的type属性为方法的返回类型。
- 通过设置result来映射数据库和pojo的属性,其中property为Java Bean中自己定义的属性名,column为数据库中定义的属性名。
2.多对一的处理
需要通过多个学生的tid对应一个老师的id来查询到老师的信息
方试一
<select id="getStudent" resultMap="StudentTeacher">
SELECT * FROM student
</select>
<resultMap id="StudentTeacher" type="Student">
<result property="name" column="name" />
<result property="id" column="id" />
<association property="teacher" column="tid" javaType="Teacher" select="Teacher"/>
</resultMap>
<select id="Teacher" resultType="Teacher">
SELECT * FROM teacher where id = #{id}
</select>
- 设置resultMap中的association属性,association的property属性为Java Bean中定义的属性名,column属性为数据库的属性,设置javaType为Teacher类名,select另一个sql语句。
//学生要关联一个老师
private Teacher teacher;
方式二
<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>
<resultMap id="StudentTeacher2" type="Student">
<result property="name" column="sname"/>
<result property="id" column="sid"/>
<association property="teacher" javaType="Teacher">
<result property="name" column="tname"/>
</association>
</resultMap>
3.一对多处理
方式一
<select id="getAllmessage" 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"/>
<collection property="students" ofType="Student">
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<result property="tid" column="tid"/>
</collection>
</resultMap>
方式二
<select id="getAllmessage2" resultMap="TeacherStudent2">
SELECT * from teacher WHERE id=#{id}
</select>
<resultMap id="TeacherStudent2" type="Teacher">
<collection property="students" column="id" javaType="ArrayList" ofType="Student" select="studentGet"/>
</resultMap>
<select id="studentGet" resultType="Student">
SELECT * from student where tid=#{id}
</select>