功能描述:
需求说明:查看某篇文章下面的评论
首先表结果如下:(文章是一的一方,评论是多的一方)
表语句如下:
/*
Navicat MySQL Data Transfer
Source Server : 120.78.225.98_3306
Source Server Version : 50717
Source Host : 120.78.225.98:3306
Source Database : lofter
Target Server Type : MYSQL
Target Server Version : 50717
File Encoding : 65001
Date: 2018-10-20 16:08:55
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for content
-- ----------------------------
DROP TABLE IF EXISTS `content`;
CREATE TABLE `content` (
`cid` varchar(255) DEFAULT NULL,
`contents` varchar(255) DEFAULT NULL,
`publisher` varchar(255) DEFAULT NULL,
`title` varchar(255) DEFAULT NULL,
`addTime` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`userId` varchar(255) DEFAULT NULL,
KEY `FK_dyy1e2asd9khdcxduewg02y9` (`userId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Table structure for comment
-- ----------------------------
DROP TABLE IF EXISTS `comment`;
CREATE TABLE `comment` (
`commenter` varchar(255) DEFAULT NULL,
`comtents` varchar(255) DEFAULT NULL,
`contentId` varchar(255) DEFAULT NULL,
`addTime` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
KEY `FK_ne3ebmfwk4qaciiqotnbdy9um` (`contentId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
mybatis实体文件如下:
文章实体:
package com.ly.pojo;
import java.util.List;
import java.util.Set;
public class Content {
private String cid;
private String title;
private String publisher;
private String addTime;
private String contents;
private List<Comment> comments;
public String getCid() {
return cid;
}
public void setCid(String cid) {
this.cid = cid;
}
public String getTitle() {
return title;
}
public void setTitle(String title) {
this.title = title;
}
public String getPublisher() {
return publisher;
}
public void setPublisher(String publisher) {
this.publisher = publisher;
}
public String getAddTime() {
return addTime;
}
public void setAddTime(String addTime) {
this.addTime = addTime;
}
public String getContents() {
return contents;
}
public void setContents(String contents) {
this.contents = contents;
}
public List<Comment> getComments() {
return comments;
}
public void setComments(List<Comment> comments) {
this.comments = comments;
}
}
评论实体:
package com.ly.pojo;
public class Comment {
private String cid;
private String commenter;
private String comtents;
private String addTime;
public String getCid() {
return cid;
}
public void setCid(String cid) {
this.cid = cid;
}
public String getCommenter() {
return commenter;
}
public void setCommenter(String commenter) {
this.commenter = commenter;
}
public String getComtents() {
return comtents;
}
public void setComtents(String comtents) {
this.comtents = comtents;
}
public String getAddTime() {
return addTime;
}
public void setAddTime(String addTime) {
this.addTime = addTime;
}
}
Dao实体如下:
package com.ly.dao;
import java.util.List;
import java.util.Map;
import com.ly.pojo.Content;
public interface ContentDao {
public int save(Map map);
public Content find(Map map);
public List<Content> findList(Map map);
}
package com.ly.dao;
import java.util.List;
import java.util.Map;
import com.ly.pojo.Comment;
import com.ly.pojo.Content;
public interface CommentDao {
public int save(Map map);
public Comment find(Map map);
public List<Comment> findList(Map map);
}
mybatis mapper文件:
<?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,namespace的值习惯上设置成包名+sql映射文件名,这样就能够保证namespace的值是唯一的
例如namespace="me.gacl.mapping.userMapper"就是me.gacl.mapping(包名)+userMapper(userMapper.xml文件去除后缀) -->
<mapper namespace="com.ly.dao.CommentDao">
<select id="findList" resultType="comment">
select * from comment t
<where>
<if test="cid != null"> t.cid= #{cid}</if>
</where>
</select>
<insert id="save">
<selectKey keyProperty="count" resultType="int" order="BEFORE">
select count(1) from content where `cid` = #{cid}
</selectKey>
<if test="count > 0">
update comment
<trim prefix="set" suffixOverrides=",">
<if test="contents != null">`commenter` = #{commenter, jdbcType=VARCHAR},</if>
<if test="title != null">`comtents` = #{comtents,jdbcType=VARCHAR},</if>
<if test="userId != null">contentId = #{contentId,jdbcType=VARCHAR},</if>
</trim>
where `cid` = #{cid}
</if>
<if test="count == 0">
insert into comment
(
`cid`
`addTime`
`commenter`
`comtents`
`contentId`
)
values
(
#{cid, jdbcType=VARCHAR},
#{addTime, jdbcType=VARCHAR},
#{commenter,jdbcType=VARCHAR},
#{comtents,jdbcType=VARCHAR},
#{contentId,jdbcType=VARCHAR}
)
</if>
</insert>
</mapper>
<?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,namespace的值习惯上设置成包名+sql映射文件名,这样就能够保证namespace的值是唯一的
例如namespace="me.gacl.mapping.userMapper"就是me.gacl.mapping(包名)+userMapper(userMapper.xml文件去除后缀) -->
<mapper namespace="com.ly.dao.ContentDao">
<!-- 查询所有的文章下面的所有评论 -->
<select id="findList" parameterType="Content" resultMap="contentMaps"><!--resultMap contentMaps 先查询文章,之后再关联-->
SELECT
*
FROM
content t
</select>
<resultMap type="Content" id="contentMaps">
<id column="cid" property="cid"/>
<result column="contents" property="contents"/>
<result column="publisher" property="publisher"/>
<result column="title" property="title"/>
<collection property="comments" ofType="Comment" select="getComments" column="cid"><!--ofType 指定关联实体,select 去找当前文章下面的评论句。 注意:column指定关联的查询条件的值,它是作为下方的查询条件,它是Content实体对应的关联查询,比如找文章ID为1的评论。对应是select comment where contentId = '1'
</collection>
</resultMap>
<select id="getComments" parameterType="int" resultType="Comment">
select
*
from comment s
where contentId = #{mid} order by addtime desc
</select>
<insert id="save">
<selectKey keyProperty="count" resultType="int" order="BEFORE">
select count(1) from content where `cid` = #{cid}
</selectKey>
<if test="count > 0">
update Content
<trim prefix="set" suffixOverrides=",">
<if test="contents != null">`contents` = #{contents, jdbcType=VARCHAR},</if>
<if test="title != null">`title` = #{title,jdbcType=VARCHAR},</if>
<if test="userId != null">userId = #{userId,jdbcType=VARCHAR},</if>
</trim>
where `cid` = #{cid}
</if>
<if test="count == 0">
insert into Content
(
`contents`,
`name`,
title,userId
)
values
(
#{contents, jdbcType=VARCHAR},
#{name,jdbcType=VARCHAR},
#{title,jdbcType=VARCHAR}
#{userId,jdbcType=VARCHAR}
)
</if>
</insert>
</mapper>
到处一切完工。
最后说明为什么这种查询方式快:
常见关联查询可以用如下三种方式写SQL:
方式1:
with temp as
(select * from comment )
select * from content t2 ,temp t where t2.cid = t.contentid;
方式2:
select * from content t1, comment t2 where t1.cid = t2.contentid;
方式3:
select * from content t1 left join comment t2 on t1.cid = t2.contentid
三种方式查询出来都可以。但是执行效率比较慢。