一、需求
比如,我们有两个对象,一个问答,一个评论,类似百度知道一样的东西;我们查询一个问答的时候,可以查看其下面的评论;
我在查询J_questionandanswer这张表的时候,需要查询j_comment这张表,并把j_comment这张表里的数据放到questionAndAnswer这个实体中;操作步骤如下:
二、解决办法
准备工作:
1、j_comment表的创建;
2、comment的实体类创建
3、在j_questionandanswer(别名qa)这个表里加入左连接:
left join j_comment jc on jc.titleid = qa.id
4、在select中,加入想要在j_comment表中查询到的字段;jc是j_comment的别名;
5、QuestionAndAnswer实体中,加入Comment映射的List集合:
6、注意:jc表中的id会和qa表中的id冲突,jc.id起别名叫cid;
<!--分页查询-->
<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>
<if test="teacherid != null">
left join sys_t_user u on u.id = qa.teacherid
</if>
left join jxdx_comment jc on jc.titleid = qa.id
where qa.deleted=0
<if test="name!=null">
AND qa.name like '%${name}%'
</if>
<if test="studentid!=null">
AND qa.studentid=#{studentid,jdbcType=BIGINT}
</if>
<if test="studentname!=null">
AND qa.studentname=#{studentname,jdbcType=VARCHAR}
</if>
<if test="createDate != null">
AND qa.create_date <![CDATA[>=]]> #{createDate}
</if>
<if test="updateDate != null">
AND qa.update_date <![CDATA[>=]]> #{updateDate}
</if>
<!-- <if test="updateDate != null">
AND qa.update_date <![CDATA[<=]]> date_add(#{updateDate}, INTERVAL 1 DAY)
</if>-->
<if test="teacherid != null">
AND qa.teacherid = #{teacherid,jdbcType=BIGINT}
</if>
<if test="teachername!=null">
AND qa.teachername=#{teachername,jdbcType=VARCHAR}
</if>
<if test="studentclassid!=null">
AND qa.studentclassid=#{studentclassid,jdbcType=BIGINT}
</if>
<if test="studentclassname!=null">
AND qa.studentclassname=#{studentclassname,jdbcType=VARCHAR}
</if>
<if test="question!=null">
AND qa.question=#{question,jdbcType=VARCHAR}
</if>
<if test="answer!=null">
AND qa.answer=#{answer,jdbcType=VARCHAR}
</if>
<if test="status!=null">
AND qa.status=#{status,jdbcType=INTEGER}
</if>
<if test="remark!=null">
AND qa.remark=#{remark,jdbcType=VARCHAR}
</if>
<if test="deleted!=null">
AND qa.deleted=#{deleted,jdbcType=INTEGER}
</if>
<if test="createBy != null">
AND qa.createBy = #{createBy,jdbcType=BIGINT}
</if>
<if test="updateBy != null">
AND qa.updateBy = #{updateBy,jdbcType=BIGINT}
</if>
order by qa.create_date desc
</select>
注:
这里的
<include refid="Base_Column_List_Alias"/>
是查询的字段的集合,可以替换成具体的字段名;` 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"/>
</collection>
完成
欢迎关注我的公众号:
【幕桥社区】