MyBatis的collection(返回值包含集合)

我们在这里用实例做解释(我们需要做一个简单需求)

我们需求:用户评论列表(这个比较简单)

CREATE TABLE `cc_user` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '主键',
  `sn` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '编号',
  `avatar` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '头像',
  `real_name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '真实姓名',
  `nickname` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '用户昵称',
  `account` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '用户账号',
  `password` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '用户密码',
  `mobile` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '用户电话',
  `sex` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '用户性别: [1=男, 2=女]',
  `channel` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '注册渠道: [1-微信小程序 2-微信公众号 3-手机H5 4-电脑PC 5-苹果APP 6-安卓APP]',
  `is_disable` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '是否禁用: [0=否, 1=是]',
  `login_ip` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '最后登录IP',
  `login_time` int unsigned NOT NULL DEFAULT '0' COMMENT '最后登录时间',
  `is_new_user` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否是新注册用户: [1-是, 0-否]',
  `user_money` decimal(10,2) unsigned DEFAULT '0.00' COMMENT '用户余额',
  `total_recharge_amount` decimal(10,2) unsigned DEFAULT '0.00' COMMENT '累计充值',
  `create_time` datetime DEFAULT '0000-00-00 00:00:00' COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `open_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '小程序用户的唯一标识\r\n',
  `scenic_id` int DEFAULT NULL COMMENT '景区id',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `sn` (`sn`) USING BTREE COMMENT '编号唯一',
  UNIQUE KEY `account` (`account`) USING BTREE COMMENT '账号唯一',
  UNIQUE KEY `open_id` (`open_id`) USING BTREE COMMENT '小程序用户标识唯一'
) ENGINE=In
CREATE TABLE `cc_review` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT 'id',
  `scenic_id` int NOT NULL COMMENT '景区id',
  `user_id` int NOT NULL COMMENT '用户id',
  `order_id` int DEFAULT NULL COMMENT '订单ID',
  `order_seed_id` int DEFAULT NULL COMMENT '子订单ID',
  `products_id` int DEFAULT NULL COMMENT '票型ID',
  `sort` int DEFAULT NULL COMMENT '排序(数字越小越靠前)',
  `star` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '星级(五)',
  `channel` int NOT NULL DEFAULT '1' COMMENT '渠道[1-微信小程序 2-微信公众号 3-手机H5 4-电脑PC 5-苹果APP 6-安卓APP]',
  `article` text COLLATE utf8mb4_general_ci COMMENT '评论文章',
  `is_show` int DEFAULT '0' COMMENT '是否显示:1-是.0-否',
  `create_by` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建人',
  `update_by` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '更新人',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=80 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='景区评价表';
CREATE TABLE `cc_picture_url` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT 'id',
  `review_id` int NOT NULL COMMENT '评论id',
  `picture_url` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '图片',
  `create_by` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建人',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '更新人',
  `update_time` datetime DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=150 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='评论图片';

表之间的关系,我就不说了(大家应该都能看懂)。

现在开始操作

我们需要创建一个无关实体类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class ReviewList {
    @Excel(name = "id")
    private Long id;
    @Excel(name = "头像")
    private String avatar;
    @Excel(name = "昵称")
    private String nickname;
    @Excel(name = "星级")
    private String star;
    @Excel(name = "评论")
    private String article;
    @Excel(name = "图片")
    private List<pictureUrls> imageList;
    @Excel(name = "创建时间")
    private Date createTime;
}

@Data
@AllArgsConstructor
@NoArgsConstructor
public class pictureUrls {
    //图片
    private String pictureUrl;
}

​

MyBatis

​
<?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.ypy.mini.mapper.CcReviewMapper">
    
    <resultMap type="CcReview" id="CcReviewResult">
        <result property="id"    column="id"    />
        <result property="scenicId"    column="scenic_id"    />
        <result property="userId"    column="user_id"    />
        <result property="orderId"    column="order_id"    />
        <result property="orderSeedId"    column="order_seed_id"    />
        <result property="productsId"    column="products_id"    />
        <result property="sort"    column="sort"    />
        <result property="star"    column="star"    />
        <result property="channel"    column="channel"    />
        <result property="article"    column="article"    />
        <result property="isShow"    column="is_show"    />
        <result property="createBy"    column="create_by"    />
        <result property="updateBy"    column="update_by"    />
        <result property="createTime"    column="create_time"    />
        <result property="updateTime"    column="update_time"    />
    </resultMap>

    <!--评论列表-->
    <resultMap id="CcReviewList" type="com.ypy.mini.dto.init.ReviewList">
        <result property="id" column="id"/>
        <result property="avatar" column="avatar"/>
        <result property="article" column="article"/>
        <result property="star" column="star"/>
        <result property="article" column="article"/>
        <result property="createTime" column="createTime"/>
        <collection  property="imageList"  column="id" select="selectPictureById"/>
    </resultMap>
    <!--图片集合-->
    <resultMap id="CcReviewPicture" type="com.ypy.mini.dto.init.pictureUrls">
        <result property="pictureUrl"    column="pictureUrl"    />
    </resultMap>

    <sql id="selectCcReviewVo">
        select id, scenic_id, user_id , order_id , order_seed_id , products_id , sort, star, channel, article, is_show, create_by, update_by, create_time, update_time from cc_review
    </sql>

    <!--小程序评论列表 id,头像 ,昵称,星,评论,创建时间-->
    <select id="selectCcReviewByScenicId" resultMap="CcReviewList" parameterType="Long">
        SELECT
            t.id as id,
            t1.avatar as avatar,
            t1.nickname as nickname,
            t.star as star,
            t.article as article,
            t.create_time as createTime
        FROM
            cc_review t
                LEFT JOIN cc_user t1 on t.user_id = t1.id

        WHERE
            t.is_show = 1
          AND t.scenic_id = #{scenicId}
        ORDER BY
            t.create_time DESC
    </select>

    <select id="selectPictureById" resultMap="CcReviewPicture" parameterType="Long">
        SELECT picture_url as pictureUrl   from cc_picture_url WHERE review_id = #{id}
    </select>

    <select id="selectCcReviewOwn" resultMap="CcReviewList" parameterType="Long">
        SELECT
            t.id as id,
            t1.avatar as avatar,
            t1.nickname as nickname,
            t.star as star,
            t.article as article,
            t.create_time as createTime
        FROM
            cc_review t
                LEFT JOIN cc_user t1 on
                t.user_id = t1.id
        WHERE
             t.user_id = #{userId}
        ORDER BY
            t.create_time DESC
    </select>


</mapper>

​
//    <!--评论列表-->
    <resultMap id="CcReviewList" type="com.ypy.mini.dto.init.ReviewList">
        <result property="id" column="id"/>
        <result property="avatar" column="avatar"/>
        <result property="article" column="article"/>
        <result property="star" column="star"/>
        <result property="article" column="article"/>
        <result property="createTime" column="createTime"/>
        <collection  property="imageList"  column="id" select="selectPictureById"/>
    </resultMap>

// <!--图片集合-->
    <resultMap id="CcReviewPicture" type="com.ypy.mini.dto.init.pictureUrls">
        <result property="pictureUrl"    column="pictureUrl"    />
    </resultMap>   


//查询同一个评论下的所有图片,
    <select id="selectPictureById" resultMap="CcReviewPicture" parameterType="Long">
        SELECT picture_url as pictureUrl   from cc_picture_url WHERE review_id = #{id}
    </select>
    <!--小程序评论列表 id,头像 ,昵称,星,评论,创建时间-->
    <select id="selectCcReviewByScenicId" resultMap="CcReviewList" parameterType="Long">
        SELECT
            t.id as id,
            t1.avatar as avatar,
            t1.nickname as nickname,
            t.star as star,
            t.article as article,
            t.create_time as createTime
        FROM
            cc_review t
                LEFT JOIN cc_user t1 on t.user_id = t1.id

        WHERE
            t.is_show = 1
          AND t.scenic_id = #{scenicId}
        ORDER BY
            t.create_time DESC
    </select>

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 1
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值