Mybatsi Lofter后台功能实现（一对多），高效查询方式

功能描述：

需求说明：查看某篇文章下面的评论

首先表结果如下：（文章是一的一方，评论是多的一方）

 

表语句如下：

 

/*
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

三种方式查询出来都可以。但是执行效率比较慢。