前言

一直在使用mybatis但一直没有好好使用过resultMap,最近写了一个考试系统,处理用户历史答题记录的时候,发现使用resultMap更方便一点

代码(可以跳过看结论)

pojo类

import cn.ims.exam.entity.Title;
import cn.ims.exam.entity.TitleAnswer;

import java.util.List;

public class TitlePo extends Title {

    private Integer userId;
    private List<Integer> answerIds;
    private String num;
    private String typeName;
    private List<TitleAnswer> answers;

    public String getNum() {
        return num;
    }

    public void setNum(String num) {
        this.num = num;
    }

    public Integer getUserId() {
        return userId;
    }

    public void setUserId(Integer userId) {
        this.userId = userId;
    }

    public List<Integer> getAnswerIds() {
        return answerIds;
    }

    public void setAnswerIds(List<Integer> answerIds) {
        this.answerIds = answerIds;
    }

    public String getTypeName() {
        return typeName;
    }

    public void setTypeName(String typeName) {
        this.typeName = typeName;
    }

    public List<TitleAnswer> getAnswers() {
        return answers;
    }

    public void setAnswers(List<TitleAnswer> answers) {
        this.answers = answers;
    }
}

resultMap设置(其中num为自定义的字段,它不和数据库中的字段关联,只用于接受传入的参数后传递给查询selectAllAnswerChecked)

 <resultMap id="titleMapForUser" type="cn.ims.exam.po.TitlePo">
        <id column="id" property="id" jdbcType="INTEGER"/>
        <result column="type_id" property="typeId" jdbcType="INTEGER"/>
        <result column="type" property="type" jdbcType="VARCHAR"/>
        <result column="userId" property="userId" jdbcType="VARCHAR"/>
        <result column="num" property="num" jdbcType="VARCHAR"/>
        <result column="title" property="title" jdbcType="VARCHAR"/>
        <result column="grade" property="grade" jdbcType="DOUBLE"/>
        <result column="create_by" property="createBy" jdbcType="INTEGER"/>
        <result column="create_date" property="createDate" jdbcType="TIMESTAMP"/>
        <result column="valid" property="valid" jdbcType="CHAR"/>
        <collection property="answers" ofType="cn.ims.exam.entity.TitleAnswer" select="selectAllAnswerForUser"
                    column="id=id"/>
        <collection property="answerIds" ofType="java.lang.Integer" select="selectAllAnswerChecked"
                    column="{id=id,userId=userId,num=num}"/>
    </resultMap>

Mapper:
List<TitlePo> selectItems(@Param("po")UserItemPo po);

Mapper.xml:
<select id="selectItems" resultMap="titleMapForUser">
        SELECT
        t.id,
        t.title,
        t.type,
        #{po.userId} as userId,
        #{po.num} as num
        FROM title t
        <if test="po.itemIds == null">
            LEFT JOIN user_type ut ON ut.id = t.type_id
            JOIN (
            SELECT ROUND(
            RAND() * ((SELECT MAX(id)
            FROM title where valid = '0' and type_id = #{po.userTypeId}) - (SELECT MIN(id)
            FROM title where valid = '0' and type_id = #{po.userTypeId})) - 40
            ) AS id
            ) AS t2
            WHERE  t.id > t2.id and t.valid = '0' and type_id = #{po.userTypeId}
            LIMIT 40
        </if>
        <if test="po.itemIds != null">
            WHERE t.id IN(
            <foreach collection="po.itemIds" item="id" separator="," index="index">
                #{id}
            </foreach>
            )
        </if>
    </select>

 <select id="selectAllAnswerForUser" resultType="cn.ims.exam.entity.TitleAnswer">
        SELECT
            ta.id,
            ta.name
        FROM title_answer ta
        WHERE title_id = #{id}
    </select>

    <select id="selectAllAnswerChecked" resultType="java.lang.Integer" parameterType="map">
        SELECT a.answer_id
        FROM user_answer a
        WHERE a.title_id = #{id} AND a.user_id = #{userId} and a.num = #{num}
    </select>

结论

以上代码可以返回考试题目列表(包括每个题目的答案列表,以及每个题目考生的答案列表)
其中:

• 可以设置自定义字段用于参数传递(num)
• 可以设置自定义列表进行相关查询(即一对多的对应关系处理)
• 注意collection中的参数传递为 column(参数名=对应result的column)