这里面我们介绍一下springboot集成mybatis完成一对多数据和一对一数据的功能。任何一个人离开你 都并非突然做的决定 人心是慢慢变冷 树叶是渐渐变黄 故事是缓缓写到结局 而爱是因为失望太多 才变成不爱。
springboot集成mybatis
我们创建两张表分别为：person表和comment表。我们的建表语句和插入脚本如下：对应关系是一个用户可以有多个评论
create TABLE person(
person_id int(11) PRIMARY KEY ,
person_name VARCHAR(45) not NULL
);
INSERT INTO person(person_id, person_name) VALUES (1, "huhx"); create table comment(
comment_id int(11) PRIMARY key,
person_id int(11) not null,
content VARCHAR(45) not null,
comment_time DATETIME not NULL
);
INSERT INTO comment(comment_id, person_id, content, comment_time) VALUES (1, 1, "content huhx", now());
INSERT INTO comment(comment_id, person_id, content, comment_time) VALUES (2, 1, "content java", now());
INSERT INTO comment(comment_id, person_id, content, comment_time) VALUES (3, 1, "content golang", now());
现在我们开始我们的代码测试，首先定义我们的实体bean类。
一、对应数据库表的实体类
表的设计可能不是特别的合理，这里面主要是学习mybatis一对多关系的知识。
- Comment：评论表
public class Comment {
private int commentId;
private String content;
private Date commentTime;
}
- Person：用户表
public class Person {
private int personId;
private String personName;
}
- PersonComment：用户的评论表
public class PersonComment {
private Person person;
List<Comment> comments;
}
二、我们的控制器测试类
@RequestMapping("/mybatis")
@RestController
public class MyBatisBeanAction {
private static Logger logger = LoggerFactory.getLogger(MyBatisBeanAction.class);
@Resource
private SqlSession sqlSession;
@GetMapping("getComment")
public ResponseBean<PersonComment> getPersonComments(@RequestParam("personId") int personId) {
PersonComment personComment = this.sqlSession.selectOne("user.queryPersonComments", personId);
return ResultUtil.success(personComment);
}
}
user.queryPersonComments的mybatis语句如下：
<resultMap id="personCommentMap" type="PersonComment">
<id column="person_id"/><!--这条语句不能省略，否则查询出三条。会electOne在返回处报错..-->
<association property="person" column="person_id" javaType="Person">
<id property="personId" column="person_id"/>
<result property="personName" column="person_name"/>
</association>
<collection property="comments" ofType="Comment">
<id property="commentId" column="comment_id"/>
<result property="content" column="content"/>
<result property="commentTime" column="comment_time"/>
</collection>
</resultMap>
<!-- 一对多的bean映射的写法 -->
<select id="queryPersonComments" parameterType="int" resultMap="personCommentMap">
SELECT
a.person_id,
a.person_name,
b.comment_id,
b.content,
b.comment_time
FROM
person a, comment b
WHERE
a.person_id = b.person_id AND
a.person_id = #{personId}
ORDER BY
a.person_id DESC
</select>
我们在浏览器发送get请求：http://localhost:9998/mybatis/getComment?personId=1。控制台发送的sql语句日志如下：
::19.281 [http-nio--exec-] DEBUG user.queryAllUserInfo_COUNT - ==> Preparing: SELECT count() FROM puser
::19.282 [http-nio--exec-] DEBUG user.queryAllUserInfo_COUNT - ==> Parameters:
::19.285 [http-nio--exec-] DEBUG user.queryAllUserInfo_COUNT - <== Total:
::19.286 [http-nio--exec-] DEBUG user.queryAllUserInfo - ==> Preparing: SELECT userId, username, password, address, phoneNumber, birthday, sex FROM puser ORDER BY userId DESC LIMIT ?, ?
::19.286 [http-nio--exec-] DEBUG user.queryAllUserInfo - ==> Parameters: (Integer), (Integer)
::19.290 [http-nio--exec-] DEBUG user.queryAllUserInfo - <== Total:
可以看到查询出三条数据，但是封闭到一个实体bean里面。这个对于之前总是用map接收sql查询返回的数据来说，确实有一些想法。之前用map的话，没有用Bean这样很好的表现数据模型。其次类似于这种的查询，返回的就是一个list列表。最后感觉用Bean的话，可以使用＠Valid去对数据进行校验，这个挺不错的。这种方式返回的数据：
{
"returnCode": ,
"returnMessage": "成功",
"response": {
"person": {
"personId": ,
"personName": "huhx"
},
"comments": [
{
"commentId": ,
"content": "content huhx",
"commentTime":
},
{
"commentId": ,
"content": "content java",
"commentTime":
},
{
"commentId": ,
"content": "content golang",
"commentTime":
}
]
}
}
我们修改为List的返回格式，具体的修改内容如下：
@GetMapping("getCommentMap")
public ResponseBean<List<Map<String, Object>>> getPersonCommentsMap(@RequestParam("personId") int personId) {
List<Map<String, Object>> lists = this.sqlSession.selectList("user.queryPersonCommentsMap", personId);
return ResultUtil.success(lists);
}
<select id="queryPersonCommentsMap" parameterType="int" resultType="map">
SELECT
a.person_id,
a.person_name,
b.comment_id,
b.content,
b.comment_time
FROM
person a, comment b
WHERE
a.person_id = b.person_id AND
a.person_id = #{personId}
ORDER BY
a.person_id DESC
</select>
修改之后返回的结果如下：可以看到返回的数据中comment_time和person_name重复了多次，而且数据list越多的时候，重复的次数就越大。最后对比这两种的返回，可以得到用map的确不能很好的反应数据模型的关系。以后还是用Bean去映射返回的数据吧，之前一直认为用map特别的方便和灵活。
{
"returnCode": ,
"returnMessage": "成功",
"response": [
{
"comment_time": ,
"person_name": "huhx",
"comment_id": ,
"content": "content huhx",
"person_id":
},
{
"comment_time": ,
"person_name": "huhx",
"comment_id": ,
"content": "content java",
"person_id":
},
{
"comment_time": ,
"person_name": "huhx",
"comment_id": ,
"content": "content golang",
"person_id":
}
]
}