第6章 Spring Boot数据库层集成

6.1 Spring Boot集成mybatis

在SpringBoot集成MyBatis时，我们将去掉和Mybatis配置相关的xml文件配置方式，用注解和属性配置来代替这些繁杂的配置。

本节我们使用SpringBoot集成Mybatis开发一个简易的H5性能测试的Web后台。我们采用Java，Groovy混合编程的方式。

新建gradle工程

组织工程目录结构如下

.
├── build.gradle
├── settings.gradle
└── src
    ├── main
    │   ├── groovy
    │   ├── java
    │   └── resources
    │       └── application.yml
    └── test
        ├── groovy
        ├── java
        └── resources

9 directories, 3 files

配置build.gradle

添加mybatis-spring-boot-starter依赖

compile('org.mybatis.spring.boot:mybatis-spring-boot-starter:1.1.1')

该starter将会自动配置好Spring Boot集成mybatis需要的mybatis,mybatis-spring等所有依赖。这个starter的依赖树如下图所示：

其中，mybatis-spring-boot-autoconfigure会完成我们之前使用xml配置mybatis使用的sqlmap-config.xml类似如下配置：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <settings>
        <setting name="lazyLoadingEnabled" value="false"></setting>
    </settings>
</configuration>

和data-source.xml文件配置数据源dataSource和TransactionManager，

<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
       xmlns:p="http://www.springframework.org/schema/p" xmlns:aop="http://www.springframework.org/schema/aop"
       xmlns:tx="http://www.springframework.org/schema/tx"
       xsi:schemaLocation="http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-3.2.xsd http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop.xsd http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx.xsd">

        <!-- 数据源定义 -->
       <bean id="dataSource"
          class="org.springframework.jdbc.datasource.DriverManagerDataSource">
        <property name="driverClassName">
            <value>com.mysql.jdbc.Driver</value>
        </property>
        <property name="url">
            <value>jdbc:mysql://127.0.0.1:3306/h5perf?useUnicode=true&autoReconnect=true&characterEncoding=UTF8&zeroDateTimeBehavior=convertToNull</value>
        </property>
        <property name="username">
            <value>root</value>
        </property>
        <property name="password">
            <value>root</value>
        </property>
    </bean>


    <!-- TransactionManager定义。 -->
    <bean id="transactionManager"
          class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource"/>
    </bean>

    <bean id="transactionTemplate"
          class="org.springframework.transaction.support.TransactionTemplate">
        <property name="transactionManager" ref="transactionManager"/>
    </bean>


    <!-- 通过aop定义事务增强切面 -->
    <aop:config>
        <aop:pointcut id="serviceMethod" expression="execution(* com.easy.springboot.h5perf.service.*.*(..))"/>
        <aop:advisor pointcut-ref="serviceMethod" advice-ref="txAdvice"/>
    </aop:config>
    <tx:advice id="txAdvice" transaction-manager="transactionManager">
        <tx:attributes>
            <tx:method name="find*" read-only="false"/>
            <tx:method name="save*" rollback-for="Exception"/>
            <tx:method name="new*" rollback-for="Exception"/>
            <tx:method name="update*" rollback-for="Exception"/>
            <tx:method name="*" rollback-for="Exception"/>
        </tx:attributes>
    </tx:advice>

    <!-- MyBatis配置sqlSessionFactory, SQL Map -->
    <bean id="sqlSessionFactory" class="org.mybatis.spring.SqlSessionFactoryBean">
        <property name="dataSource" ref="dataSource"/>
        <!-- 自动扫描entity目录 -->
        <property name="typeAliasesPackage" value="com.easy.springboot.h5perf.model"/>
        <!-- 显式指定Mapper文件位置 -->
        <property name="mapperLocations"
                  value="classpath:/sqlmap/*Mapper.xml"/>
        <property name="configLocation" value="classpath:sqlmap-config.xml"></property>
    </bean>
    <!-- 扫描basePackage下所有以@MyBatisRepository标识的 接口 -->
    <bean class="org.mybatis.spring.mapper.MapperScannerConfigurer"
          p:basePackage="com.alibaba.swork.info.common.mapper"/>

</beans>

配置application.yml

配置数据源

spring:
    datasource:
        name: h5perf
        url: jdbc:mysql://localhost:3306/h5perf?autoReconnect=true&useSSL=false
        username: root
        password: root
        # 使用druid数据源
        type: com.alibaba.druid.pool.DruidDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver

配置mybatis

mybatis:
    type-aliases-package: com.easy.springboot.h5perf.model
    configuration:
        # 开启mybatis开启数据库字段自动映射驼峰命名规则java属性
        map-underscore-to-camel-case: true
        default-fetch-size: 100
        default-statement-timeout: 30

数据库表结构

CREATE DATABASE IF NOT EXISTS `h5perf`
  DEFAULT CHARACTER SET utf8;
USE `h5perf`;

DROP TABLE IF EXISTS `test_case`;

CREATE TABLE `test_case` (
  `id`             INT(11) NOT NULL AUTO_INCREMENT,
  `container_type` VARCHAR(45)      DEFAULT NULL
  COMMENT '‘ORIGIN_WEBVIEW’,’UC_WEBVIEW’',
  `test_url`       VARCHAR(45)      DEFAULT NULL,
  `net_type`       VARCHAR(45)      DEFAULT NULL
  COMMENT '‘WIFI’,‘3G’,‘4G’',
  `gmt_created`    DATETIME         DEFAULT NULL,
  `gmt_modified`   DATETIME         DEFAULT NULL,
  PRIMARY KEY (`id`)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8;


DROP TABLE IF EXISTS `test_resource`;

CREATE TABLE `test_resource` (
  `id`            INT(11) NOT NULL AUTO_INCREMENT,
  `test_case_id`  INT(11)          DEFAULT NULL,
  `start_time`    BIGINT(11)       DEFAULT NULL,
  `resource_url`  VARCHAR(500)     DEFAULT NULL,
  `resource_type` VARCHAR(45)      DEFAULT NULL
  COMMENT 'IMG,JS,CSS,OTHER',
  `resource_time` BIGINT(11)       DEFAULT NULL
  COMMENT 'onLoadResource资源时间戳',
  `resource_size` BIGINT(11)       DEFAULT NULL
  COMMENT '资源大小',
  `gmt_created`   DATETIME         DEFAULT NULL,
  PRIMARY KEY (`id`)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8;


DROP TABLE IF EXISTS `test_time_data`;

CREATE TABLE `test_time_data` (
  `id`               INT(11) NOT NULL AUTO_INCREMENT,
  `test_case_id`     INT(11)          DEFAULT NULL,
  `on_recieve_title` VARCHAR(45)      DEFAULT NULL
  COMMENT '近似白屏时间',
  `on_page_finished` VARCHAR(45)      DEFAULT NULL
  COMMENT '页面加载完成时间',
  `dom_content_load` VARCHAR(45)      DEFAULT NULL
  COMMENT 'dom内容加载完成时间',
  `load`             VARCHAR(45)      DEFAULT NULL
  COMMENT '资源加载完成时间',
  `gmt_created`      DATETIME         DEFAULT NULL,
  PRIMARY KEY (`id`)
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

Model层代码

我们在application.yml中开启了mybatis开启数据库字段自动映射驼峰命名规则java属性：

map-underscore-to-camel-case: true

所以，我们model类的属性命名规则按照驼峰命名规则。

TestCase.groovy代码：

package com.easy.springboot.h5perf.model

/**
 * Created by jack on 2017/4/22.
 */
class TestCase {
    Integer id
    String containerType
    String testUrl
    String netType
    Date gmtCreated
    Date gmtModified

    @Override
    String toString() {
        return "TestCase{" +
                "id=" + id +
                ", containerType='" + containerType + '\'' +
                ", testUrl='" + testUrl + '\'' +
                ", netType='" + netType + '\'' +
                ", gmtCreated=" + gmtCreated +
                ", gmtModified=" + gmtModified +
                '}';
    }
}

Mapper接口层代码

我们以往的通过xml配置显式指定Mapper文件位置:

<!-- 显式指定Mapper文件位置 -->
        <property name="mapperLocations"
                  value="classpath:/sqlmap/*Mapper.xml"/>

我们现在用注解方式，直接在接口上添加@Mapper注解即可。代码示例TestCaseMapper.groovy

package com.easy.springboot.h5perf.mapper

import com.easy.springboot.h5perf.model.TestCase
import org.apache.ibatis.annotations.Insert
import org.apache.ibatis.annotations.Mapper
import org.apache.ibatis.annotations.Options
import org.apache.ibatis.annotations.Param
import org.apache.ibatis.annotations.Select

@Mapper
interface TestCaseMapper {

    @Insert([
            "insert into test_case",
            "set container_type = #{t.containerType},",
            "test_url = #{t.testUrl},",
            "net_type = #{t.netType},",
            "gmt_created = #{t.gmtCreated},",
            "gmt_modified = #{t.gmtModified}"
    ])
    @Options(useGeneratedKeys = true, keyProperty = "t.id")
    int insert(@Param("t") TestCase testCase)

    @Select("select * from test_case")
    List<TestCase> findAll()

    @Select("select * from test_case where net_type=#{netType}")
    List<TestCase> findByNetType(@Param(value="netType")String netType)

    @Select("select * from test_case where id=#{id} limit 1")
    TestCase findOne(@Param(value="id")Integer id)

    int insertSelective(TestCase record)
}

Controller层代码

package com.easy.springboot.h5perf.controller

import com.easy.springboot.h5perf.model.TestCase
import com.easy.springboot.h5perf.result.Result
import com.easy.springboot.h5perf.service.TestCaseService
import com.github.pagehelper.PageInfo
import groovy.json.JsonOutput
import io.swagger.annotations.Api
import io.swagger.annotations.ApiImplicitParam
import io.swagger.annotations.ApiOperation
import org.springframework.beans.factory.annotation.Autowired
import org.springframework.stereotype.Controller
import org.springframework.ui.Model
import org.springframework.web.bind.annotation.GetMapping
import org.springframework.web.bind.annotation.PostMapping
import org.springframework.web.bind.annotation.RequestParam
import org.springframework.web.bind.annotation.ResponseBody

/**
 * Created by jack on 2017/4/22.
 * http://springfox.github.io/springfox/docs/current/#springfox-samples
 */

//在Controller上使用@Api会生成这个Controller的整体描述
@Api(value = "测试任务管理", tags = ["测试任务管理API"], description = "描述信息")
@Controller
class TestCaseController {
    @Autowired
    TestCaseService testCaseService

    @PostMapping("/saveTestCase")
    @ResponseBody
    def saveTestCase(TestCase testCase) {
        testCase.gmtCreated = new Date()
        println("testCase===" + testCase)
        int insert = testCaseService.save(testCase)
        println("testCase===" + testCase)
        Result result = new Result()
        if (insert == 1) {
            result.success = true
            result.result = testCase
        } else {
            result.success = false
            result.result = testCase
        }
        def jsonOutput = new JsonOutput()
        println("saveTestCase result===" + jsonOutput.toJson(result))
        result
    }

    @GetMapping("/listTestCase")
    def listTestCase(Model model,
                     @RequestParam(value = "pageNo", required = false) Integer pageNo,
                     @RequestParam(value = "pageSize", required = false) Integer pageSize) {
        def pageInfo = testCaseService.findAll(pageNo, pageSize)
        model.addAttribute("testCaseList", pageInfo.list)
        model.addAttribute("pageNo", pageInfo.pageNum)
        model.addAttribute("totalPage", pageInfo.pages)
        model.addAttribute("prePage", pageInfo.prePage)
        model.addAttribute("nextPage", pageInfo.nextPage)
        model.addAttribute("hasPreviousPage", pageInfo.hasPreviousPage)
        model.addAttribute("hasNextPage", pageInfo.hasNextPage)

        "/test_case/list"
    }

//    在具体方法上使用@ApiOperation可以生成接口的描述
    @ApiOperation(value = "list all TestCase Json", notes = "listTestCaseJson", produces = "application/json")
    @GetMapping("/listTestCaseJson")
    @ResponseBody
    def listTestCaseJson(@RequestParam(value = "pageNo", required = false) Integer pageNo,
                         @RequestParam(value = "pageSize", required = false) Integer pageSize) {
        testCaseService.findAll(pageNo, pageSize)
    }

    @ApiOperation(value = "findOne TestCase Json", notes = "findOne TestCase", produces = "application/json")
//  在方法上使用@ApiImplicitParam可以增加对参数等的描述
    @ApiImplicitParam(name = "id",
            value = "测试任务ID",
            dataType = "Integer",//This can be the class name or a primitive
            required = true,
            paramType = "query")
//Valid values are {@code path}, {@code query}, {@code body}, {@code header} or {@code form}
    @GetMapping("/findOne")
    @ResponseBody
    def findOne(@RequestParam(value = "id", required = true) Integer id) {
        testCaseService.findOne(id)
    }

    @ApiOperation(value = "findByNetType TestCase Json", notes = "findByNetType TestCase", produces = "application/json")
//  在方法上使用@ApiImplicitParam可以增加对参数等的描述
    @ApiImplicitParam(name = "netType",
            value = "findByNetType",
            dataType = "String",//This can be the class name or a primitive
            required = true,
            paramType = "query")
    @GetMapping("/findByNetType")
    @ResponseBody
    def findByNetType(@RequestParam(value = "netType", required = false) String netType,
                      @RequestParam(value = "pageNo", required = false) Integer pageNo,
                      @RequestParam(value = "pageSize", required = false) Integer pageSize
    ) {
        testCaseService.queryByPage(netType, pageNo, pageSize)
    }


}

使用PageHelper实现后端分页

（1）添加pagehelper-spring-boot-starter

//   pagehelper
    compile('com.github.pagehelper:pagehelper-spring-boot-starter:1.1.0')

（2）Service层代码实现

接口类 TestCaseService.groovy

package com.easy.springboot.h5perf.service

import com.easy.springboot.h5perf.model.TestCase
import com.github.pagehelper.PageInfo

/**
 * Created by jack on 2017/4/23.
 */
interface TestCaseService {
    def save(TestCase testCase)

    PageInfo<TestCase> findAll(Integer pageNo, Integer pageSize)

    TestCase findOne(Integer id)

    PageInfo<TestCase> queryByPage(String netType, Integer pageNo, Integer pageSize)

}

实现类TestCaseServiceImpl.groovy

package com.easy.springboot.h5perf.service

import com.easy.springboot.h5perf.mapper.TestCaseMapper
import com.easy.springboot.h5perf.model.TestCase
import com.github.pagehelper.PageHelper
import com.github.pagehelper.PageInfo
import org.springframework.beans.factory.annotation.Autowired
import org.springframework.stereotype.Service

/**
 * Created by jack on 2017/4/23.
 */
@Service
class TestCaseServiceImpl implements TestCaseService {
    @Autowired
    TestCaseMapper testCaseMapper;

    @Override
    def save(TestCase testCase) {
        testCaseMapper.insert(testCase)
    }

    @Override
    PageInfo<TestCase> findAll(Integer pageNo, Integer pageSize) {
        pageNo = pageNo == null ? 1 : pageNo
        pageSize = pageSize == null ? 10 : pageSize
        PageHelper.startPage(pageNo, pageSize)
        List<TestCase> testCaseList = testCaseMapper.findAll()
        //用PageInfo对结果进行包装
        PageInfo<TestCase> testCasePageInfo = new PageInfo<>(testCaseList)
        testCasePageInfo

    }

    @Override
    TestCase findOne(Integer id) {
        testCaseMapper.findOne(id)
    }

    @Override
    PageInfo<TestCase> queryByPage(String netType, Integer pageNo, Integer pageSize) {
        pageNo = pageNo == null ? 1 : pageNo
        pageSize = pageSize == null ? 10 : pageSize
        PageHelper.startPage(pageNo, pageSize)

        List<TestCase> testCaseList = testCaseMapper.findByNetType(netType)
        //用PageInfo对结果进行包装
        PageInfo<TestCase> testCasePageInfo = new PageInfo<>(testCaseList)
        testCasePageInfo
    }
}

（3）Controller层与前端代码集成

传到前端的Model层数据：

    @GetMapping("/listTestCase")
    def listTestCase(Model model,
                     @RequestParam(value = "pageNo", required = false) Integer pageNo,
                     @RequestParam(value = "pageSize", required = false) Integer pageSize) {
        def pageInfo = testCaseService.findAll(pageNo, pageSize)
        model.addAttribute("testCaseList", pageInfo.list)
        model.addAttribute("pageNo", pageInfo.pageNum)
        model.addAttribute("totalPage", pageInfo.pages)
        model.addAttribute("prePage", pageInfo.prePage)
        model.addAttribute("nextPage", pageInfo.nextPage)
        model.addAttribute("hasPreviousPage", pageInfo.hasPreviousPage)
        model.addAttribute("hasNextPage", pageInfo.hasNextPage)

        "/test_case/list"
    }

前端使用Bootstrap pagination样式类：

    <table class="table table-hover">
        <thead>
        <th>Id</th>
        <th>testUrl</th>
        <th>containerType</th>
        <th>netType</th>
        <th>gmtCreated</th>
        </thead>
        <tbody>
        <#list testCaseList as tc >
        <tr>
            <td>${tc.id}</td>
            <td><a href="${tc.testUrl}">${tc.testUrl}</a></td>
            <td>${tc.containerType}</td>
            <td>${tc.netType}</td>
            <td>${tc.gmtCreated?string('yyyy/MM/dd HH:mm:ss')}</td>
        </tr>
        </#list>
        </tbody>
    </table>


    <div class="center">
        <ul class="pagination">
            <li><a href="/listTestCase?pageNo=${prePage}&pageSize=10">&laquo;</a></li>
        <#list 1..totalPage as index>
            <#if index=pageNo>
                <li class="active"><a href="/listTestCase?pageNo=${index}&pageSize=10">${index}</a></li>
            <#else>
                <li><a href="/listTestCase?pageNo=${index}&pageSize=10">${index}</a></li>
            </#if>
        </#list>
            <li><a href="/listTestCase?pageNo=${nextPage}&pageSize=10">&raquo;</a></li>
        </ul>
    </div>

（4）运行测试

第1页

第2页

集成Druid对数据库进行监控

本节完整的工程源代码：https://github.com/EasySpringBoot/h5perf

6.2 Spring Boot集成jpa

6.3 Spring Boot集成多数据源

参考资料：

1.http://www.mybatis.org/spring-boot-starter/mybatis-spring-boot-autoconfigure/
2.https://github.com/Jason-Chen-2017/MyBatis-Spring-Boot
3.https://github.com/Jason-Chen-2017/pagehelper-spring-boot

《Spring Boot极简教程》第6章 Springboot数据库集成

第6章 Spring Boot数据库层集成

6.1 Spring Boot集成mybatis

新建gradle工程

配置build.gradle

配置application.yml

数据库表结构

Model层代码

Mapper接口层代码

Controller层代码

使用PageHelper实现后端分页

集成Druid对数据库进行监控

6.2 Spring Boot集成jpa

6.3 Spring Boot集成多数据源

热门文章

最新文章

相关课程

相关电子书

相关实验场景

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

《Spring Boot极简教程》 第6章 Springboot数据库集成

第6章 Spring Boot数据库层集成

6.1 Spring Boot集成mybatis

新建gradle工程

配置build.gradle

配置application.yml

数据库表结构

Model层代码

Mapper接口层代码

Controller层代码

使用PageHelper实现后端分页

集成Druid对数据库进行监控

6.2 Spring Boot集成jpa

6.3 Spring Boot集成多数据源

热门文章

最新文章

相关课程

相关电子书

相关实验场景

《Spring Boot极简教程》第6章 Springboot数据库集成