一般情况下，应用都会和数据发生交互，所以这张主要介绍一下如何和数据库发生交互。

常用的集中方式包括：JdbcTemplate、ORM框架如Hibernate/Mybatis、JPA等等。

我也是看着别人的例子照猫画虎，将Mybatis集成了进来。具体的细节，自己也在慢慢的理解中。

一、jar包的引入，用到的比较多：

<!-- 据说很牛的一个数据源 -->
<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
</dependency>
<!-- 阿里开源的一个数据源 -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>${druid.version}</version>
</dependency>
<!-- 集成 mybatis -->
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>${mybatis-spring-boot-starter.version}</version>
</dependency>
<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper-spring-boot-starter</artifactId>
    <version>${pagehelper-spring-boot-starter.version}</version>
</dependency>
<dependency>
    <groupId>org.hibernate</groupId>
    <artifactId>hibernate-validator</artifactId>
</dependency>
<dependency>
    <groupId>io.jsonwebtoken</groupId>
    <artifactId>jjwt</artifactId>
    <version>${jjwt.version}</version>
</dependency>

用的版本：
<druid.version>1.0.31</druid.version>
<mybatis-spring-boot-starter.version>1.3.0</mybatis-spring-boot-starter.version>
<pagehelper-spring-boot-starter.version>1.1.3</pagehelper-spring-boot-starter.version>
<jjwt.version>0.7.0</jjwt.version>

二、在yml文件中配置数据库的相关信息：

app: 
  jdbc_url: jdbc:mysql://127.0.0.1:3306/udpdb?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull
  jdbc_username: root
  jdbc_password: xxxx
  jdbc_driverClass: com.mysql.jdbc.Driver
  maxPoolPreparedStatementPerConnectionSize: 50
  # 申请连接时执行validationQuery检测连接是否有效，做了这个配置会降低性能
  testOnBorrow: false
  # 归还连接时执行validationQuery检测连接是否有效
  testOnReturn: false
  # 申请连接的时候检测，如果空闲时间大于timeBetweenEvictionRunsMillis，执行validationQuery检测连接是否有效
  testWhileIdle: true
  validationQuery: SELECT 1
  initialSize: 30
  maxActive:  100

三、增加数据源配置类
实际上这里要还有一些事情没有搞的特别清楚，这个配置类不是必须的，可以用Spring Boot自带的数据源配置，但是我们使用的是第三方的数据源，并且使用Druid的时候，可以使用到它自带的监控统计功能，所以需要写这个配置类。

我放在了这个包路径下面：com.spring.lw.Towns.config

3.1 读取配置文件中的配置

package com.spring.lw.Towns.config;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import lombok.Data;

/**
 * 数据库 配置属性
 *
 * @author duyq06
 */
@Component
@Data
public class DbConfigProperties {

  @Value("${app.jdbc_url}")
  private String jdbc_url;

  @Value("${app.jdbc_username}")
  private String jdbc_username;

  @Value("${app.jdbc_password}")
  private String jdbc_password;

  @Value("${app.jdbc_driverClass}")
  private String jdbc_driverClass;

  @Value("${app.maxPoolPreparedStatementPerConnectionSize}")
  private int maxPoolPreparedStatementPerConnectionSize;

  @Value("${app.testOnBorrow}")
  private boolean testOnBorrow;

  @Value("${app.testOnReturn}")
  private boolean testOnReturn;

  @Value("${app.testWhileIdle}")
  private boolean testWhileIdle;

  @Value("${app.validationQuery}")
  private String validationQuery;

  @Value("${app.initialSize}")
  private int initialSize;

  @Value("${app.maxActive}")
  private int maxActive;
}

3.2 具体配置数据源和监控，非生产环境使用的是Druid数据源，生产环境使用效率更高的HikariCP

package com.spring.lw.Towns.config;

import java.util.HashMap;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.web.servlet.FilterRegistrationBean;
import org.springframework.boot.web.servlet.ServletRegistrationBean;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.context.annotation.Profile;
import org.springframework.jdbc.core.JdbcTemplate;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

/** 配置 DataSourceConfig */
@Configuration
public class DataSourceConfig {

  @Autowired private DbConfigProperties dbConfigProperties;
  /**
   * 注册DruidServlet 启用数据源的Web监控统计功能
   *
   * @return
   */
  @Profile({"development", "dev", "int", "uat", "test"})
  @Bean
  public ServletRegistrationBean druidServletRegistrationBean() {
    ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean();
    servletRegistrationBean.setServlet(new StatViewServlet());
    servletRegistrationBean.addUrlMappings("/druid/*");
    return servletRegistrationBean;
  }

  /**
   * 注册DruidFilter拦截
   *
   * @return
   */
  @Profile({"development", "dev", "int", "uat", "test"})
  @Bean
  public FilterRegistrationBean duridFilterRegistrationBean() {
    FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean();
    filterRegistrationBean.setFilter(new WebStatFilter());
    Map<String, String> initParams = new HashMap<String, String>();
    // 设置忽略请求
    initParams.put("exclusions", "*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*");
    filterRegistrationBean.setInitParameters(initParams);
    filterRegistrationBean.addUrlPatterns("/*");
    return filterRegistrationBean;
  }

  /**
   * 测试环境可以用 DruidDataSource 以可视化观察应用sql 的执行状况
   *
   * @return
   */
  @Profile({"development", "dev", "int", "uat", "test"})
  @Bean(name = "appDataSource")
  @Primary
  public DataSource appDataSource() {
    DruidDataSource dataSource = new DruidDataSource();
    dataSource.setUrl(this.dbConfigProperties.getJdbc_url());
    dataSource.setUsername(this.dbConfigProperties.getJdbc_username());
    dataSource.setPassword(this.dbConfigProperties.getJdbc_password());
    dataSource.setDriverClassName(this.dbConfigProperties.getJdbc_driverClass());
    dataSource.setTestOnReturn(this.dbConfigProperties.isTestOnReturn());
    dataSource.setTestWhileIdle(this.dbConfigProperties.isTestWhileIdle());
    dataSource.setTestOnBorrow(this.dbConfigProperties.isTestOnBorrow());
    dataSource.setInitialSize(this.dbConfigProperties.getInitialSize());
    dataSource.setMaxPoolPreparedStatementPerConnectionSize(
        this.dbConfigProperties.getMaxPoolPreparedStatementPerConnectionSize());
    dataSource.setMaxActive(this.dbConfigProperties.getMaxActive());
    dataSource.setValidationQuery(this.dbConfigProperties.getValidationQuery());
    return dataSource;
  }

  /**
   * 生产环境选择性能更高的HikariCP https://github.com/brettwooldridge/HikariCP
   *
   * @return
   */
  @Profile({"prd"})
  @Bean(name = "appDataSource")
  @Primary
  public DataSource hikariDataSource() {
    HikariConfig config = new HikariConfig();
    config.setJdbcUrl(this.dbConfigProperties.getJdbc_url());
    config.setUsername(this.dbConfigProperties.getJdbc_username());
    config.setPassword(this.dbConfigProperties.getJdbc_password());
    config.setDriverClassName(this.dbConfigProperties.getJdbc_driverClass());
    config.setConnectionTestQuery(this.dbConfigProperties.getValidationQuery());
    config.setMaximumPoolSize(
        this.dbConfigProperties.getMaxPoolPreparedStatementPerConnectionSize());
    config.addDataSourceProperty("cachePrepStmts", "true");
    config.addDataSourceProperty("prepStmtCacheSize", "250");
    config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
    HikariDataSource dataSource = new HikariDataSource(config);
    return dataSource;
  }

  @Bean(name = "appJdbcTemplate")
  public JdbcTemplate primaryJdbcTemplate(@Qualifier("appDataSource") DataSource dataSource) {
    return new JdbcTemplate(dataSource);
  }
}

四、MyBatis的相关配置

4.1

package com.spring.lw.Towns.mybatis.config;

import javax.sql.DataSource;

import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.AutoConfigureAfter;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.core.io.support.ResourcePatternResolver;
import org.springframework.jdbc.datasource.DataSourceTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.TransactionManagementConfigurer;

import com.spring.lw.Towns.config.DataSourceConfig;


@Configuration
@AutoConfigureAfter(DataSourceConfig.class)
public class MyBatisConfig implements TransactionManagementConfigurer {

  @Autowired
  @Qualifier("appDataSource")
  private DataSource appDataSource;

  @Bean(name = "sqlSessionFactory")
  public SqlSessionFactory sqlSessionFactoryBean() {
    SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
    bean.setDataSource(this.appDataSource);
    bean.setTypeAliasesPackage("com.spring.lw.Towns.mybatis.domain");

    //添加XML目录
    ResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
    try {
      bean.setMapperLocations(
          resolver.getResources("classpath:/com/spring/lw/Towns/mybatis/mapper/*Mapper.xml"));
      return bean.getObject();
    } catch (Exception e) {
      e.printStackTrace();
      throw new RuntimeException(e);
    }
  }

  @Bean
  public SqlSessionTemplate sqlSessionTemplate(SqlSessionFactory sqlSessionFactory) {
    return new SqlSessionTemplate(sqlSessionFactory);
  }

  @Bean
  public PlatformTransactionManager annotationDrivenTransactionManager() {
    return new DataSourceTransactionManager(this.appDataSource);
  }
}

4.2

package com.spring.lw.Towns.mybatis.config;

import org.apache.ibatis.annotations.Mapper;
import org.mybatis.spring.mapper.MapperScannerConfigurer;
import org.springframework.boot.autoconfigure.AutoConfigureAfter;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
@AutoConfigureAfter(MyBatisConfig.class)
public class MyBatisMapperScannerConfig {
  @Bean
  public MapperScannerConfigurer mapperScannerConfigurer() {
    MapperScannerConfigurer mapperScannerConfigurer = new MapperScannerConfigurer();
    mapperScannerConfigurer.setSqlSessionFactoryBeanName("sqlSessionFactory");
    mapperScannerConfigurer.setBasePackage("com.spring.lw.Towns.mybatis.domain");
    mapperScannerConfigurer.setAnnotationClass(Mapper.class);
    return mapperScannerConfigurer;
  }
}

五、Mapper

以往我们都比较习惯将sql文件写在mapper.xml文件中，还有一个方法，可以直接将sql和方法进行绑定。

package com.spring.lw.Towns.mybatis.domain;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import com.spring.lw.Towns.vo.User;

@Mapper
public interface UserMapper {
    
    @Select("select * from USER where id = #{id}")
    public User getUserByID(@Param("id")int id);

}

一切OK，但是还有很多不明白的，感觉垮过去一步，先研究一下Spring Boot docs上的例子，补上。