[Mybatis] Summary of subqueries (one-to-many Collection), when querying parent objects, also querying child objects;

1. Requirements

For example, we have two objects, one question and answer, one comment, which is similar to what Baidu knows; when we query a question and answer, you can see the comments below;
I'm looking for J_When questionandanswer this table, you need to query j_comment this table and put j_comment The data in this table is placed in the entity questionAndAnswer; the steps are as follows:

2. Solutions

Dead work:
1. j_Creation of comment table;
2. Entity class creation for comment
3. In j_Queionandanswer (alias qa) A left join is added to this table:

left join j_comment jc on jc.titleid = qa.id

4. In select, add what you want in j_The field queried in the comment table; jc is j_Alias for comment;
5. In the QuestionAndAnswer entity, add the List collection of Comment mappings:

6. Note: IDS in jc tables conflict with IDS in qa tables.jc.id The alias is cid;

<!--Paging Query-->
<select id="selectByProperties" parameterType="com.jxdx.questionAndAnswer.model.QuestionAndAnswer" resultMap="BaseResultMap">
select <include refid="Base_Column_List_Alias"/>,jc.id AS cid, jc.titlename, jc.titleid, jc.username, jc.userid, jc.comment, jc.create_date from jxdx_questionandanswer qa
<if test="studentid != null">
left join sys_t_user u on u.id = qa.studentid
<if test="teacherid != null">
left join sys_t_user u on u.id = qa.teacherid
left join jxdx_comment jc on jc.titleid = qa.id
where qa.deleted=0
<if test="name!=null">
AND qa.name like '%${name}%'
<if test="studentid!=null">
AND qa.studentid=#{studentid,jdbcType=BIGINT}
<if test="studentname!=null">
AND qa.studentname=#{studentname,jdbcType=VARCHAR}
<if test="createDate != null">
AND qa.create_date <![CDATA[>=]]> #{createDate}
<if test="updateDate != null">
AND qa.update_date <![CDATA[>=]]> #{updateDate}
<!-- <if test="updateDate != null">
AND qa.update_date <![CDATA[<=]]> date_add(#{updateDate}, INTERVAL 1 DAY)
<if test="teacherid != null">
AND qa.teacherid = #{teacherid,jdbcType=BIGINT}
<if test="teachername!=null">
AND qa.teachername=#{teachername,jdbcType=VARCHAR}
<if test="studentclassid!=null">
AND qa.studentclassid=#{studentclassid,jdbcType=BIGINT}
<if test="studentclassname!=null">
AND qa.studentclassname=#{studentclassname,jdbcType=VARCHAR}
<if test="question!=null">
AND qa.question=#{question,jdbcType=VARCHAR}
<if test="answer!=null">
AND qa.answer=#{answer,jdbcType=VARCHAR}
<if test="status!=null">
AND qa.status=#{status,jdbcType=INTEGER}
<if test="remark!=null">
AND qa.remark=#{remark,jdbcType=VARCHAR}
<if test="deleted!=null">
AND qa.deleted=#{deleted,jdbcType=INTEGER}
<if test="createBy != null">
AND qa.createBy = #{createBy,jdbcType=BIGINT}
<if test="updateBy != null">
AND qa.updateBy = #{updateBy,jdbcType=BIGINT}
order by qa.create_date desc


  • Here <include refid="Base_Column_List_Alias "/>
    Is a collection of fields that are queried and can be replaced with specific field names;

  • ` AND qa.create_date

<collection property="commentList" ofType="com.jxdx.questionAndAnswer.model.Comment" notNullColumn="id">
<id column="cid" property="id" jdbcType="BIGINT"/>
<result column="titlename" property="titleName" jdbcType="VARCHAR" />
<result column="titleid" property="titleId" jdbcType="VARCHAR"/>
<result column="username" property="userName" jdbcType="BIGINT" />
<result column="userid" property="userId" jdbcType="VARCHAR" />
<result column="comment" property="comment" jdbcType="VARCHAR"/>
<result column="remark" property="remark" jdbcType="VARCHAR"/>
<result column="create_date" property="createDate" jdbcType="VARCHAR"/>


Posted on Fri, 22 May 2020 12:31:58 -0400 by nemesis.2002