mybatis一对多查询

最新推荐文章于 2023-06-09 11:24:04 发布

孟夏草木长

最新推荐文章于 2023-06-09 11:24:04 发布

阅读量725

点赞数

分类专栏： mybatis 文章标签： mybatis 一对多查询

本文链接：https://blog.csdn.net/muyeju/article/details/49362089

版权

mybatis 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

本文介绍了在项目中使用MyBatis进行一对多查询的场景，以评论表和附件表为例，阐述了如何通过SQL和DAO代码实现从评论表获取多个附件信息的过程。

摘要由CSDN通过智能技术生成

在做项目时，有这样两张表，一个评论表，一个附件表，一个评论表可以上传多个附件（一对多）

1.sql部分代码如下：

<span style="font-family:Microsoft YaHei;font-size:14px;"><span style="font-family:Microsoft YaHei;"><!-- 根据用户id查询用户发表的点评，同时获得点评里的图片 collection里面的property属性为实体表里面的关联属性，collection里面的column为关联的字段-->
    <resultMap id="selectComments" type="cn.com.sch.comment.pojo.Comment">
    	<id column="id" property="id" jdbcType="VARCHAR" />
	    <result column="startLevel" property="startlevel" jdbcType="INTEGER" />
	    <result column="commentContent" property="commentcontent" jdbcType="VARCHAR" />
	    ......
	    <result column="createDate" property="createdate" jdbcType="VARCHAR" />
		<collection property="attachments" javaType="ArrayList" column="id"
		ofType="cn.com.sch.comment.pojo.CommentAttachment" select="selectAttachments"/>
    </resultMap>
	<!--查询附表，附件表-->
	<select id="selectAttachments" parameterType="String" resultType="cn.com.sch.comment.pojo.CommentAttachment">
		select * from comment_attachment where commentId = #{commentid}
	</select>
	<!--查询主表评论表-->
	<select id="selectComment" parameterType="String" resultMap="selectComments">
		select * from comment where commentuserId = #{commentuserid}
	</select></span></span>

另一种写法：

<span style="font-family:Microsoft YaHei;"><!-- 主表属性（查询Comment表，同时将关联的CommentRecommendUser和CommentAttachment表查询出来放在comment的集合中 -->	
	<resultMap id="selectComments" type="cn.com.sch.comment.pojo.Comment">
    	<id column="c_id" property="id" jdbcType="VARCHAR" />
	    <result column="startLevel" property="startlevel" jdbcType="INTEGER" />
	    <result column="commentContent" property="commentcontent" jdbcType="VARCHAR" />
	    <result column="commentUserId" property="commentuserid" jdbcType="VARCHAR" />
	    <result column="consumption" property="consumption" jdbcType="DOUBLE" />
	    <result column="palate" property="palate" jdbcType="INTEGER" />
	    <result column="sound" property="sound" jdbcType="INTEGER" />
	    <result column="environment" property="environment" jdbcType="INTEGER" />
	    <result column="service" property="service" jdbcType="INTEGER" />
	    <result column="c_shopid" property="shopid" jdbcType="VARCHAR" />
	    <result column="c_createdate" property="createdate" jdbcType="VARCHAR" />
	    <!-- 副表，在主表中有一个List<CommentRecommendUser>集合，变量名就是commentRecommendUsers，collection里面是该表的属性 -->
		<collection property="commentRecommendUsers" ofType="cn.com.sch.comment.pojo.CommentRecommendUser">
		    <id column="r_id" property="id" jdbcType="VARCHAR" />
		    <result column="r_shopid" property="shopid" jdbcType="VARCHAR" />
		    <result column="recommendName" property="recommendname" jdbcType="VARCHAR" />
		    <result column="r_createdate" property="createdate" jdbcType="VARCHAR" />
		</collection> 
		<!-- 副表，在主表中有一个List<CommentAttachment>集合，变量名就是attachments，collection里面是该表的属性 -->
		<collection property="attachments" ofType="cn.com.sch.comment.pojo.CommentAttachment">
		    <id column="ca_id" property="id" jdbcType="VARCHAR" />
		    <result column="imageName" property="imagename" jdbcType="VARCHAR" />
		    <result column="imageUpdateName" property="imageupdatename" jdbcType="VARCHAR" />
		    <result column="url" property="url" jdbcType="VARCHAR" />
		    <result column="commentId" property="commentid" jdbcType="VARCHAR" />
		    <result column="ca_createdate" property="createdate" jdbcType="VARCHAR" />
		</collection>
    </resultMap>
    <!-- 查询语句，因为关联了两个附表，所有用了链接查询，为有冲突的属性取了别名 -->
    <select id="selectComment" parameterType="string" resultMap="selectComments">
		select 
		c.id as c_id, startLevel, commentContent, commentUserId, consumption, palate, sound, environment, service, c.shopId as c_shopid, c.createDate as c_createdate,
		r.id as r_id, r.shopId as r_shopid, recommendName, r.createDate as r_createdate,
		ca.id as ca_id, imageName, imageUpdateName, url, commentId, ca.createDate as ca_createdate
		from comment c
		left outer join comment_recommend_user r on c.shopId = r.shopId
		left outer join comment_attachment ca on c.id = ca.commentId
		 where c.commentuserId = #{commentuserid}
	</select></span>

2.dao代码：

<span style="font-family:Microsoft YaHei;font-size:14px;"><span style="font-family:Microsoft YaHei;">/**
     * 根据用户id查询用户发表的点评
     * @param commentuserid
     * @return
     */
    List<Comment> selectComment(String commentuserid);</span></span>

3.Service代码：

<span style="font-family:Microsoft YaHei;font-size:14px;"><span style="font-family:Microsoft YaHei;">/**
	 * 查询某用户的所有点评(根据点评用户id查询某用户的所有点评)
	 * @param useId
	 * @return
	 */
	public List<Comment> selectCommentByCommentuserid(String commentuserid);</span></span>

4.Service里面的impl实现类：

<span style="font-family:Microsoft YaHei;font-size:14px;"><span style="font-family:Microsoft YaHei;">/**
	 * 查询某用户的所有点评(根据点评用户id查询某用户的所有点评)
	 */
	public List<Comment> selectCommentByCommentuserid(String commentuserid) {
		List<Comment> comment = commentMapper.selectComment(commentuserid);
		return comment;
	}</span></span>

5.controller代码：

<span style="font-family:Microsoft YaHei;font-size:14px;"><span style="font-family:Microsoft YaHei;">@RequestMapping(value="/selectCommentByUserId",method=RequestMethod.POST)
	public ResultData selectCommentByUserId(@RequestParam("commentuserid") String commentuserid){
		try {
			List<Comment> comment = commentService.selectCommentByCommentuserid(commentuserid);
			return new ResultData(comment);
		} catch (Exception e) {
			return SchExceptionUtil.ExceptionConvertState(e);
		}
	}</span></span>

6.junit测试类代码：

<span style="font-family:Microsoft YaHei;font-size:14px;"><span style="font-family:Microsoft YaHei;">@Test
	public void selectCommentByCommentid(){
		String result = "" ;
		try {
			ResultActions ra = this.mockMvc.perform(MockMvcRequestBuilders.post("/commentController/selectCommentByUserId")
					.param("commentuserid", "e0ff0c3ff3c74418974f82d18f64a9cc")
					);
			MvcResult mr = ra.andReturn();
			result = mr.getResponse().getContentAsString();
			System.out.println("+++++++++++"+result);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}</span></span>