<!-- mybatis和分页插件 -->
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.2.11</version>
</dependency>
application.properties
#mybatis配置Mapper.xml文件的位置resources/mapper/目录下
mybatis.mapper-locations=classpath:mapper/*.xml
#打印sql
#mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
resources/mapper/ 目录下存放所有 Mapper.xml文件
使用一条sql查询出所有数据,列名重复的,需要使用别名修改。
SELECT
a.id,
a.content,
a.like_count,
a.user_id,
a.user_nick_name,
a.quest_id,
a.createtime,
a.accept_status,
c.id comment_id,
c.user_id comment_user_id,
c.user_nick_name comment_user_nick_name,
c.answer_id comment_answer_id,
c.content comment_content,
c.createtime comment_createtime
FROM answer a
LEFT JOIN comment c ON a.id=c.answer_id
WHERE quest_id=149
ORDER BY a.id
编写Mapper的Xml文件
xml文件内容如下
<?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">
<!--
namespace
指定当前xml文件对应的Mapper接口
必须指定一个存在的Mapper接口,当前xml文件才能正常运作
-->
<mapper namespace="cn.tedu.knows.portal.mapper.AnswerMapper">
<!-- 通用查询映射结果 -->
<resultMap id="answerCommentMap" type="cn.tedu.knows.portal.model.Answer">
<id column="id" property="id" />
<result column="content" property="content" />
<result column="like_count" property="likeCount" />
<result column="user_id" property="userId" />
<result column="user_nick_name" property="userNickName" />
<result column="quest_id" property="questId" />
<result column="createtime" property="createtime" />
<result column="accept_status" property="acceptStatus" />
<!--
配置collection标签,
将查询结果中Comment对象保存在当前回答Answer对象中
property指定要保存Comment对象的属性
ofType指定集合泛型的类型,这里是List<Comment>所以是Comment的全类名
-->
<collection property="comments"
ofType="cn.tedu.knows.portal.model.Comment">
<id column="comment_id" property="id" />
<result column="comment_user_id" property="userId" />
<result column="comment_user_nick_name" property="userNickName" />
<result column="comment_answer_id" property="answerId" />
<result column="comment_content" property="content" />
<result column="comment_createtime" property="createtime" />
</collection>
</resultMap>
<!--
select标签,表示一个查询方法,id和AnswerMapper中的一个方法名一致
注意不建议在标签内打注释,如果一定要打,写数据库sql语句注释,写xml注释会报错
resultMap是指定当前查询的返回值映射关系的名称
这个名称要和上面<resultMap>的id对应
-->
<select id="findAnswersByQuestionId" resultMap="answerCommentMap">
SELECT
a.id,
a.content,
a.like_count,
a.user_id,
a.user_nick_name,
a.quest_id,
a.createtime,
a.accept_status,
c.id comment_id,
c.user_id comment_user_id,
c.user_nick_name comment_user_nick_name,
c.answer_id comment_answer_id,
c.content comment_content,
c.createtime comment_createtime
FROM answer a
LEFT JOIN comment c ON a.id=c.answer_id
WHERE quest_id=#{id}
ORDER BY a.id
</select>
</mapper>
AnswerMapper接口中的代码要随上面xml文件编写对应的方法
@Repository
public interface AnswerMapper {
// Mybatis关联查询,实现查询指定问题的所有回答包含所有回答的评论
// 它对应resources中mapper里AnswerMapper.xml中的同名的xml配置
List<Answer> findAnswersByQuestionId(Integer questionId);
}
实体类Answer
package cn.tedu.knows.portal.model;
import com.baomidou.mybatisplus.annotation.TableName;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import java.time.LocalDateTime;
import com.baomidou.mybatisplus.annotation.TableField;
import java.io.Serializable;
import java.util.ArrayList;
import java.util.List;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;
@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@TableName("answer")
public class Answer implements Serializable {
private static final long serialVersionUID = 1L;
@TableId(value = "id", type = IdType.AUTO)
private Integer id;
/**
* 回答内容
*/
@TableField("content")
private String content;
/**
* 点赞数量
*/
@TableField("like_count")
private Integer likeCount;
/**
* 回答问题的用户id
*/
@TableField("user_id")
private Integer userId;
/**
* 回答者用户名
*/
@TableField("user_nick_name")
private String userNickName;
/**
* 对应的问题id
*/
@TableField("quest_id")
private Integer questId;
/**
* 回答时间
*/
@TableField("createtime")
private LocalDateTime createtime;
/**
* 是否采纳
*/
@TableField("accept_status")
private Integer acceptStatus;
/**
* 当前回答的所有评论集合
*/
@TableField(exist = false)
private List<Comment> comments=new ArrayList<>();
}
实体类 Comment
package cn.tedu.knows.portal.model;
import com.baomidou.mybatisplus.annotation.TableName;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import java.time.LocalDateTime;
import com.baomidou.mybatisplus.annotation.TableField;
import java.io.Serializable;
import lombok.Data;
import lombok.EqualsAndHashCode;
import lombok.experimental.Accessors;
@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@TableName("comment")
public class Comment implements Serializable {
private static final long serialVersionUID = 1L;
/**
* 主键
*/
@TableId(value = "id", type = IdType.AUTO)
private Integer id;
/**
* 用户id
*/
@TableField("user_id")
private Integer userId;
/**
* 用户昵称
*/
@TableField("user_nick_name")
private String userNickName;
/**
* 回答id
*/
@TableField("answer_id")
private Integer answerId;
/**
* 评论内容
*/
@TableField("content")
private String content;
/**
* 创建时间
*/
@TableField("createtime")
private LocalDateTime createtime;
}
@EqualsAndHashCode(callSuper = false)
见名知意,equals和hashcode方法是否需要考虑父类的属性,如果没有父类,那这个注解可以当不存在。
测试
package cn.tedu.knows.portal;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import java.util.List;
// 不加这个注解测试不了!!!!
@SpringBootTest
public class TeaTest {
@Autowired
AnswerMapper answerMapper;
@Test
public void testAnswer(){
List<Answer> answers=
answerMapper.findAnswersByQuestionId(149);
for(Answer a:answers){
System.out.println(a);
}
}
}