本文以druid數(shù)據(jù)庫連接池及mybatis框架，搭建基本的應(yīng)用開發(fā)框架。

1拢驾、druid數(shù)據(jù)庫連接池

1.1、Druid是什么改基？有什么作用繁疤？

Druid首先是一個數(shù)據(jù)庫連接池，但它不僅僅是一個數(shù)據(jù)庫連接池，它還包含一個ProxyDriver稠腊，一系列內(nèi)置的JDBC組件庫躁染，一個SQL Parser。

整個項目由數(shù)據(jù)庫連接池架忌、插件框架和SQL解析器組成吞彤。該項目主要是為了擴(kuò)展JDBC的一些限制，可以讓程序員實(shí)現(xiàn)一些特殊的需求叹放，比如向密鑰服務(wù)請求憑證饰恕、統(tǒng)計SQL信息、SQL性能收集井仰、SQL注入檢查埋嵌、SQL翻譯等，程序員可以通過定制來實(shí)現(xiàn)自己需要的功能糕档。
github:https://github.com/alibaba/druid

1.2莉恼、Druid支持哪些數(shù)據(jù)庫？

Druid支持所有JDBC兼容的數(shù)據(jù)庫速那，包括Oracle俐银、MySql、Derby端仰、Postgresql捶惜、SQL Server、H2等等荔烧。
Druid針對Oracle和MySql做了特別優(yōu)化吱七，比如Oracle的PS Cache內(nèi)存占用優(yōu)化，MySql的ping檢測優(yōu)化鹤竭。

1.3踊餐、Druid是如何擴(kuò)展JDBC的?

Druid在DruidDataSourc和ProxyDriver上提供了Filter-Chain模式的擴(kuò)展API，類似Serlvet的Filter臀稚，配置Filter攔截JDBC的方法調(diào)用吝岭。

1.4、Druid有那些監(jiān)控特性吧寺？

• 監(jiān)控SQL的執(zhí)行時間窜管、ResultSet持有時間、返回行數(shù)稚机、更新行數(shù)幕帆、錯誤次數(shù)、錯誤堆棧信息赖条。
• SQL執(zhí)行的耗時區(qū)間分布失乾。什么是耗時區(qū)間分布呢常熙？比如說，某個SQL執(zhí)行了1000次仗扬，其中0_{1毫秒?yún)^(qū)間50次症概，1}10毫秒800次蕾额，10_{100毫秒100次早芭，100}1000毫秒30次，1~10秒15次诅蝶，10秒以上5次退个。通過耗時區(qū)間分布，能夠非常清楚知道SQL的執(zhí)行耗時情況调炬。
• 監(jiān)控連接池的物理連接創(chuàng)建和銷毀次數(shù)语盈、邏輯連接的申請和關(guān)閉次數(shù)、非空等待次數(shù)缰泡、PSCache命中率等刀荒。

2、配置druid數(shù)據(jù)庫連接池

目前Spring Boot中默認(rèn)支持的連接池有dbcp,dbcp2, tomcat, hikari三種連接池棘钞，若要支持druid缠借，需要我們自己配置；

2.1宜猜、基于java代碼配置

2.1.1泼返、pom依賴

<dependency>

  <groupId>mysql</groupId>

  <artifactId>mysql-connector-java</artifactId>

  <scope>runtime</scope>

</dependency>

<dependency>

  <groupId>com.alibaba</groupId>

  <artifactId>druid</artifactId>

  <version>1.0.25</version>

</dependency>

2.1.2、application.properties配置信息

# 驅(qū)動配置信息
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.driverClassName = com.mysql.jdbc.Driver
spring.datasource.url = jdbc:mysql://192.168.101.xx:3306/jdronetest?characterEncoding=UTF-8
spring.datasource.username = xxxxx
spring.datasource.password = xxxxxxx

#連接池的配置信息
spring.datasource.initialSize=2
spring.datasource.minIdle=2
spring.datasource.maxActive=2
spring.datasource.maxWait=60000
spring.datasource.timeBetweenEvictionRunsMillis=60000
spring.datasource.minEvictableIdleTimeMillis=300000
spring.datasource.validationQuery=SELECT 1 FROM USER
spring.datasource.testWhileIdle=true
spring.datasource.testOnBorrow=false
spring.datasource.testOnReturn=false
spring.datasource.poolPreparedStatements=true
spring.datasource.maxPoolPreparedStatementPerConnectionSize=20
spring.datasource.filters=stat,wall,log4j
spring.datasource.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000

2.1.3姨拥、定義DataSource

package com.springboot.demo.config;

import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.support.http.StatViewServlet;
import com.alibaba.druid.support.http.WebStatFilter;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
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 javax.sql.DataSource;
import java.sql.SQLException;

@SuppressWarnings("all")
@Configuration
public class DruidConfig {
    private static final Logger log = LoggerFactory.getLogger(DruidConfig.class);

    @Value("${spring.datasource.url}")
    private String dbUrl;

    @Value("${spring.datasource.username}")
    private String username;

    @Value("${spring.datasource.password}")
    private String password;

    @Value("${spring.datasource.driverClassName}")
    private String driverClassName;

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

    @Value("${spring.datasource.minIdle}")
    private int minIdle;

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

    @Value("${spring.datasource.maxWait}")
    private int maxWait;

    @Value("${spring.datasource.timeBetweenEvictionRunsMillis}")
    private int timeBetweenEvictionRunsMillis;

    @Value("${spring.datasource.minEvictableIdleTimeMillis}")
    private int minEvictableIdleTimeMillis;

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

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

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

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

    @Value("${spring.datasource.poolPreparedStatements}")
    private boolean poolPreparedStatements;

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

    @Value("${spring.datasource.filters}")
    private String filters;

    @Value("{spring.datasource.connectionProperties}")
    private String connectionProperties;

    @Bean(initMethod = "init", destroyMethod = "close")
    @Primary
    public DataSource dataSource() {
        DruidDataSource datasource = new DruidDataSource();

        datasource.setUrl(this.dbUrl);
        datasource.setUsername(username);
        datasource.setPassword(password);
        datasource.setDriverClassName(driverClassName);

        /** configuration */
        datasource.setInitialSize(initialSize);
        datasource.setMinIdle(minIdle);
        datasource.setMaxActive(maxActive);
        datasource.setMaxWait(maxWait);
        datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
        datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
        datasource.setValidationQuery(validationQuery);
        datasource.setTestWhileIdle(testWhileIdle);
        datasource.setTestOnBorrow(testOnBorrow);
        datasource.setTestOnReturn(testOnReturn);
        datasource.setPoolPreparedStatements(poolPreparedStatements);
        datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
        try {
            datasource.setFilters(filters);
        } catch (SQLException e) {
            log.error("druid configuration initialization filter", e);
        }
        datasource.setConnectionProperties(connectionProperties);
        return datasource;
    }

    /**
     * http://127.0.0.1:8090/monitor/druid/login.html
     *
     * @throws
     * @Title: druidServlet
     * @Description: 注冊一個StatViewServlet 相當(dāng)于在web.xml中聲明了一個servlet
     * @param: void
     * @return: ServletRegistrationBean
     */
    @Bean
    public ServletRegistrationBean druidServlet() {
        ServletRegistrationBean reg = new ServletRegistrationBean();
        reg.setServlet(new StatViewServlet());
        reg.addUrlMappings("/druid/*");
        /** 白名單 */
        // IP白名單
        reg.addInitParameter("allow", "*");
        // IP黑名單(共同存在時绅喉，deny優(yōu)先于allow)
        reg.addInitParameter("deny", "192.168.1.100");

        /** /druid/login.html登錄時賬號密碼 */
        reg.addInitParameter("loginUsername", "zhaozhou");
        reg.addInitParameter("loginPassword", "zhaozhou");
        /** 是否能夠重置數(shù)據(jù) 禁用HTML頁面上的“Reset All”功能 */
        reg.addInitParameter("resetEnable", "false");
        return reg;
    }

    /**
     * 注冊一個：filterRegistrationBean 相當(dāng)于在web.xml中聲明了一個Filter
     */
    @Bean
    public FilterRegistrationBean druidStatFilter() {
        FilterRegistrationBean druidStatFilter = new FilterRegistrationBean();
        druidStatFilter.setFilter(new WebStatFilter());
        /** 添加過濾規(guī)則. */
        druidStatFilter.addUrlPatterns("/*");
        /** 監(jiān)控選項濾器 */
        druidStatFilter.addInitParameter("DruidWebStatFilter", "/*");
        /** 添加不需要忽略的格式信息. */
        druidStatFilter.addInitParameter("exclusions",
                "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
        /** 配置profileEnable能夠監(jiān)控單個url調(diào)用的sql列表 */
        druidStatFilter.addInitParameter("profileEnable", "true");
        /** 當(dāng)前的cookie的用戶 */
        druidStatFilter.addInitParameter("principalCookieName", "USER_COOKIE");
        /** 當(dāng)前的session的用戶 */
        druidStatFilter
                .addInitParameter("principalSessionName", "USER_SESSION");
        return druidStatFilter;
    }
}

2.2、基于starter配置

此種配置只需要配置pom及application.properties信息即可叫乌；

2.2.1柴罐、pom配置

<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <scope>runtime</scope>
</dependency>

<dependency>
  <groupId>com.alibaba</groupId>
  <artifactId>druid-spring-boot-starter</artifactId>
  <version>1.1.8</version>
</dependency>

2.3、mybatis配置

2.3.1憨奸、pom配置

<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>1.3.2</version>
</dependency>

2.3.2革屠、application.properties配置

mybatis.mapper-locations=classpath:mapper/*.xml
mybatis.type-aliases-package=com.springboot.demo.entity

2.3.3、@MapperScan配置

@SpringBootApplication
@MapperScan("com.springboot.demo.dao")
public class TestApplication {

  public static void main(String[] args) {
    SpringApplication.run(TestApplication.class, args);
  }
}

2.3.3膀藐、mabatis擴(kuò)展配置

mybatis.config-location：指定mybatis-config.xml的位置屠阻；
mybatis.check-config-location：檢查mybatis-config.xml是否存在；
mybatis.mapper-locations：指定mapper的xml的位置额各；
mybatis.type-aliases-package：指定別名的包国觉，可以多個，逗號分隔虾啦；
mybatis.type-handlers-package：指定handler的掃描碼路徑麻诀；
mybatis.executor-type：執(zhí)行器類型: SIMPLE, REUSE, BATCH痕寓；
mybatis.configuration-properties：MyBatis配置的外部化屬性。指定的屬性可以用作MyBatis配置文件和映射文件的占位符蝇闭；
mybatis.configuration：嵌套配置呻率，具體子配置如下；
defaultStatementTimeout：設(shè)置超時時間呻引，它決定驅(qū)動等待數(shù)據(jù)庫響應(yīng)的秒數(shù)礼仗；
defaultFetchSize：為驅(qū)動的結(jié)果集獲取數(shù)量（fetchSize）設(shè)置一個提示值。此參數(shù)只可以在查詢設(shè)置中被覆蓋逻悠；
mapUnderscoreToCamelCase：是否開啟自動駝峰命名規(guī)則（camel case）映射元践，即從經(jīng)典數(shù)據(jù)庫列名 A_COLUMN 到經(jīng)典 Java 屬性名 aColumn 的類似映射；
autoMappingUnknownColumnBehavior：指定發(fā)現(xiàn)自動映射目標(biāo)未知列（或者未知屬性類型）的行為童谒；NONE：不做任何反應(yīng)单旁；WARNING：輸出提醒日志 ('org.apache.ibatis.session.AutoMappingUnknownColumnBehavior' 的日志等級必須設(shè)置為 WARN)；FAILING：映射失敗 (拋出 SqlSessionException)饥伊；

具體參考：http://www.mybatis.org/spring-boot-starter/mybatis-spring-boot-autoconfigure/

2.3象浑、日志配置

2.3.1、pom配置

<dependency>
  <groupId>log4j</groupId>
  <artifactId>log4j</artifactId>
  <version>1.2.17</version>
</dependency>

2.3.2琅豆、application.properties配置

logging.level.root = debug

本處只做基本的root配置愉豺，可以看到sql的日志即可，不做過多配置趋距；

3粒氧、項目最終結(jié)果

3.1、項目目錄結(jié)構(gòu)

目錄結(jié)構(gòu).png

3.2节腐、dao層

package com.springboot.demo.dao;

import com.springboot.demo.entity.User;

public interface UserDao {
    User getUserById(Long id);

}

dao層只提供一個接口外盯，通過用戶id查找用戶信息；

3.3翼雀、entity數(shù)據(jù)庫實(shí)體

/**
 * @author 
 */
public class User implements Serializable {
    /**
     * 主鍵id
     */
    private Long id;

    /**
     * 用戶名
     */
    private String name;

    /**
     * 電話
     */
    private String tel;
    /**
     * 郵件
     */
    private String email;

    /**
     * 航線編號
     */
    private Date createTime;

    //getter饱苟、setter方法
}

3.4、mapper文件

<?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">
<mapper namespace="com.springboot.demo.dao.UserDao">
  <resultMap id="BaseResultMap" type="com.springboot.demo.entity.User">
    <id column="id" jdbcType="BIGINT" property="id" />
    <result column="name" jdbcType="VARCHAR" property="name" />
    <result column="tel" jdbcType="VARCHAR" property="tel"/>
    <result column="email" jdbcType="VARCHAR" property="email" />
    <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
  </resultMap>

  <sql id="Base_Column_List">
    id, name, tel, email, create_time
  </sql>


<!--  通過ID獲取用戶信息-->
  <select id="getUserById" parameterType="Long" resultMap="BaseResultMap">
    select
    <include refid="Base_Column_List" />
    from t_user
    <where>
      id = #{id, jdbcType=BIGINT}
    </where>
  </select>
</mapper>

3.5狼渊、test測試用例

@RunWith(SpringRunner.class)
@SpringBootTest
public class TestApplicationTests {

    @Resource
    private UserService userService;

    @Test
    public void getUserTest() {
        User user = userService.getUserById(1L);
        System.out.println("用戶名：" + user.getName());
    }

}

3.6箱熬、運(yùn)行test結(jié)果

2018-10-30 14:57:14.552 DEBUG 9816 --- [           main] org.mybatis.spring.SqlSessionUtils       : Creating a new SqlSession
2018-10-30 14:57:14.556 DEBUG 9816 --- [           main] org.mybatis.spring.SqlSessionUtils       : SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4f1fb828] was not registered for synchronization because synchronization is not active
2018-10-30 14:57:14.595 DEBUG 9816 --- [           main] o.s.jdbc.datasource.DataSourceUtils      : Fetching JDBC Connection from DataSource
2018-10-30 14:57:14.599 DEBUG 9816 --- [           main] o.m.s.t.SpringManagedTransaction         : JDBC Connection [com.alibaba.druid.proxy.jdbc.ConnectionProxyImpl@6a55594b] will not be managed by Spring
2018-10-30 14:57:14.603 DEBUG 9816 --- [           main] c.s.demo.dao.UserDao.getUserById         : ==>  Preparing: select id, name, tel, email, create_time from t_user WHERE id = ? 
2018-10-30 14:57:14.769 DEBUG 9816 --- [           main] c.s.demo.dao.UserDao.getUserById         : ==> Parameters: 1(Long)
2018-10-30 14:57:14.790 DEBUG 9816 --- [           main] c.s.demo.dao.UserDao.getUserById         : <==      Total: 1
2018-10-30 14:57:14.791 DEBUG 9816 --- [           main] c.a.druid.pool.PreparedStatementPool     : {conn-10002, pstmt-20000} enter cache
2018-10-30 14:57:14.791 DEBUG 9816 --- [           main] org.mybatis.spring.SqlSessionUtils       : Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@4f1fb828]
2018-10-30 14:57:14.791 DEBUG 9816 --- [           main] o.s.jdbc.datasource.DataSourceUtils      : Returning JDBC Connection to DataSource
用戶名：zhaozhou

以上為運(yùn)行查詢?nèi)罩荆梢钥吹奖芬兀瑪?shù)據(jù)庫事務(wù)及查詢語句都運(yùn)行正常城须。

示例源碼：https://github.com/zhaozhou11/spring-boot-demo.git

相關(guān)閱讀：
spring-boot基礎(chǔ)環(huán)境搭建 【http://www.reibang.com/p/ee36bb9faa10】
spring-boot配置詳解【http://www.reibang.com/p/1d037ab638ef】
spring-boot+logback+log4j2+MDC【http://www.reibang.com/p/51da61a425ba】

參考博客：
https://blog.csdn.net/moshowgame/article/details/80304198
https://blog.csdn.net/zjh_746140129/article/details/81254987
https://blog.csdn.net/m0_38044453/article/details/81132523