springboot---->集成mybatis开发（二）

　　这里面我们介绍一下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语句日志如下：

22:25:19.281 [http-nio-9998-exec-4] DEBUG user.queryAllUserInfo_COUNT - ==>  Preparing: SELECT count(0) FROM puser 
22:25:19.282 [http-nio-9998-exec-4] DEBUG user.queryAllUserInfo_COUNT - ==> Parameters: 
22:25:19.285 [http-nio-9998-exec-4] DEBUG user.queryAllUserInfo_COUNT - <==      Total: 1
22:25:19.286 [http-nio-9998-exec-4] DEBUG user.queryAllUserInfo - ==>  Preparing: SELECT userId, username, password, address, phoneNumber, birthday, sex FROM puser ORDER BY userId DESC LIMIT ?, ? 
22:25:19.286 [http-nio-9998-exec-4] DEBUG user.queryAllUserInfo - ==> Parameters: 3(Integer), 3(Integer)
22:25:19.290 [http-nio-9998-exec-4] DEBUG user.queryAllUserInfo - <==      Total: 3

　　可以看到查询出三条数据，但是封闭到一个实体bean里面。这个对于之前总是用map接收sql查询返回的数据来说，确实有一些想法。之前用map的话，没有用Bean这样很好的表现数据模型。其次类似于这种的查询，返回的就是一个list列表。最后感觉用Bean的话，可以使用＠Valid去对数据进行校验，这个挺不错的。这种方式返回的数据：

{
    "returnCode": 0,
    "returnMessage": "成功",
    "response": {
        "person": {
            "personId": 1,
            "personName": "huhx"
        },
        "comments": [
            {
                "commentId": 1,
                "content": "content huhx",
                "commentTime": 1513606831000
            },
            {
                "commentId": 2,
                "content": "content java",
                "commentTime": 1513606862000
            },
            {
                "commentId": 3,
                "content": "content golang",
                "commentTime": 1513606863000
            }
        ]
    }
}

 我们修改为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": 0,
    "returnMessage": "成功",
    "response": [
        {
            "comment_time": 1513606831000,
            "person_name": "huhx",
            "comment_id": 1,
            "content": "content huhx",
            "person_id": 1
        },
        {
            "comment_time": 1513606862000,
            "person_name": "huhx",
            "comment_id": 2,
            "content": "content java",
            "person_id": 1
        },
        {
            "comment_time": 1513606863000,
            "person_name": "huhx",
            "comment_id": 3,
            "content": "content golang",
            "person_id": 1
        }
    ]
}

友情链接