mybatis 查询结果中包括不同的字段

当 返回的对象中 有可能 包括另一个表中的字段时，可以新建一个  StudentHomeworkBo 对象，包括 该字段。

在xml 中 ，当查询结果不包括该字段时， 使用  <sql id="resultColumn">   ， 包括该字段时， 使用   <sql id="resultBoColumn"> ， 都映射到  BaseBoResult ，即对象 StudentHomeworkBo 

中，查询的结果没有该字段时，对象 的该字段是null。

public interface TtStudentHomeworkMapper extends SimpleMapper<TtStudentHomework> {

    List<StudentHomeworkBo> selectStudentHomework(@Param("homewordNo") String homewordNo, @Param("memberCode") String memberCode, @Param("status") StudentHomeworkStatus status,
        @Param("subject") String subject, @Param("homeworkType") String homeworkType,@Param("keyword") String keyword);
}

 

 

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
<mapper namespace="com.xl.poss.basic.mapper.TtStudentHomeworkMapper">
    <resultMap id="BaseResult" type="com.xl.poss.basic.dto.TtStudentHomework">
        <id property="id" column="id"/>
        <result property="homeworkNo" column="homework_no"/>
        <result property="homeworkType" column="homework_type"/>
        <result property="markType" column="mark_type"/>
        <result property="memberCode" column="member_code"/>
        <result property="teacherCode" column="teacher_code"/>
        <result property="classCode" column="class_code"/>
        <result property="targetCode" column="target_code"/>
        <result property="title" column="title"/>
        <result property="description" column="description"/>
        <result property="items" column="items"/>
        <result property="subject" column="subject"/>
        <result property="submitPages" column="submit_pages"/>
        <result property="markResult" column="mark_result"/>
        <result property="status" column="status"/>
        <result property="submitIndex" column="submit_index"/>
        <result property="submitTime" column="submit_time"/>
        <result property="endTime" column="end_time"/>
        <result property="createTime" column="create_time"/>
        <result property="updateTime" column="update_time"/>
        <result property="ext1" column="ext1"/>
        <result property="ext2" column="ext2"/>
    </resultMap>
    <sql id="resultColumn">
        id, homework_no, homework_type, mark_type, member_code, teacher_code, class_code, target_code, title, description, items, subject, submit_pages, mark_result, status, submit_index, submit_time, end_time, create_time, update_time, ext1, ext2
    </sql>

    <resultMap id="BaseBoResult" type="com.lesmart.xl.poss.basic.bo.StudentHomeworkBo">
        <id property="id" column="id"/>
        <result property="homeworkNo" column="homework_no"/>
        <result property="homeworkType" column="homework_type"/>
        <result property="markType" column="mark_type"/>
        <result property="memberCode" column="member_code"/>
        <result property="teacherCode" column="teacher_code"/>
        <result property="classCode" column="class_code"/>
        <result property="targetCode" column="target_code"/>
        <result property="title" column="title"/>
        <result property="description" column="description"/>
        <result property="items" column="items"/>
        <result property="subject" column="subject"/>
        <result property="submitPages" column="submit_pages"/>
        <result property="markResult" column="mark_result"/>
        <result property="status" column="status"/>
        <result property="submitIndex" column="submit_index"/>
        <result property="submitTime" column="submit_time"/>
        <result property="endTime" column="end_time"/>
        <result property="createTime" column="create_time"/>
        <result property="updateTime" column="update_time"/>
        <result property="groupName" column="group_name"/>
    </resultMap>
    <sql id="resultBoColumn">
        h.id, h.homework_no, h.homework_type, h.mark_type, h.member_code, h.teacher_code, h.class_code, h.target_code, h.title, h.description, h.items, h.subject, h.submit_pages, h.mark_result, h.status, h.submit_index, h.submit_time, h.end_time, h.create_time, h.update_time, g.group_name
    </sql>


    <!--
    <select id="selectStudentHomework" resultMap="BaseBoResult">
        SELECT <include refid="resultBoColumn"/>
        FROM lesmart_flas.tt_student_homework h
        LEFT JOIN lesmart_ma.tm_group g ON h.target_code = g.group_code
        WHERE  1=1
            <if test="homewordNo !=null and homewordNo != ''">AND h.homework_no = #{homewordNo}</if>
            <if test="memberCode !=null and memberCode != ''">AND h.member_code = #{memberCode}</if>
            <if test="status !=null ">AND h.status = #{status}</if>
            <if test="subject !=null and subject !=''">AND h.subject = #{subject}</if>
            <if test="homeworkType != null and homeworkType != ''">AND h.homework_type = #{homeworkType}</if>
            <if test="keyword != null and keyword != ''">AND h.title LIKE CONCAT('%', #{keyword}, '%')</if>
    </select>
    -->

    <select id="selectStudentHomework" resultMap="BaseBoResult">
        <choose>
            <when test="homewordNo != null  and homewordNo != ''">
                SELECT <include refid="resultBoColumn"/>
                FROM lesmart_flas.tt_student_homework h
                LEFT JOIN lesmart_ma.tm_group g ON h.target_code = g.group_code
                WHERE  1=1
                <if test="homewordNo !=null and homewordNo != ''">AND h.homework_no = #{homewordNo}</if>
                <if test="memberCode !=null and memberCode != ''">AND h.member_code = #{memberCode}</if>
                <if test="status !=null ">AND h.status = #{status}</if>
            </when>
            <otherwise>
                SELECT <include refid="resultColumn"/>
                FROM lesmart_flas.tt_student_homework
                WHERE  1=1
                <if test="homewordNo !=null and homewordNo != ''">AND homework_no = #{homewordNo}</if>
                <if test="memberCode !=null and memberCode != ''">AND member_code = #{memberCode}</if>
                <if test="status !=null ">AND status = #{status}</if>
                <if test="subject !=null and subject !=''">AND subject = #{subject}</if>
                <if test="homeworkType != null and homeworkType != ''">AND homework_type = #{homeworkType}</if>
                <if test="keyword != null and keyword != ''">AND title LIKE CONCAT('%', #{keyword}, '%')</if>
            </otherwise>
        </choose>
    </select>

</mapper>

 根据查询条件中 homewordNo 参数是否为空，来执行不同的sql 。

 

@Getter
@Setter
@ToString(callSuper = true)
public class StudentHomeworkBo extends AbstractStringBootEntity {
    
    /**
     * 家庭作业编号
     */
    private String homeworkNo;
    
    /**
     * 作业类型
     */
    private String homeworkType;
    
    /**
     * 批阅类型
     */
    private String markType;
    
    /**
     * 学生用户编号
     */
    private String memberCode;
    
    /**
     * 老师用户编号
     */
    private String teacherCode;
    
    /**
     * 
     */
    private String classCode;
    
    /**
     * 所属组编号
     */
    private String targetCode;
    
    /**
     * 作业标题
     */
    private String title;
    
    /**
     * 
     */
    private String description;
    
    /**
     * 
     */
    private JSONArray items;
    
    /**
     * 科目
     */
    private String subject;
    
    /**
     * 提交的页面
     */
    private JSONArray submitPages;
    
    /**
     * 批阅结果
     */
    private String markResult;
    
    /**
     * 作业状态
     */
    private String status;
    
    /**
     * 提交顺序
     */
    private Integer submitIndex;
    
    /**
     * 作业提交时间
     */
    private LocalDateTime submitTime;
    
    /**
     * 截止时间
     */
    private LocalDateTime endTime;
    
    /**
     * 分组名称
     */
    private String groupName;


}

（实体表TtStudentHomework 中不包括  groupName 字段）