我们在这里用实例做解释(我们需要做一个简单需求)
我们需求:用户评论列表(这个比较简单)
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>