SpringBoot整合Druid和MyBatis

1. 加入依赖

<!-- druid -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.16</version>
</dependency>
<!-- mybatis -->
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.5.1</version>
</dependency>
<!-- mybatis对接Spring -->
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis-spring</artifactId>
    <version>2.0.1</version>
</dependency>
<!-- mysql链接驱动 -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.15</version>
</dependency>
<!-- jdbc -->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<!-- log4j -->
<dependency>
    <groupId>log4j</groupId>
    <artifactId>log4j</artifactId>
    <version>1.2.17</version>
</dependency>

2. application.yml中添加配置

spring:
    druid:
    datasource:
      type: com.alibaba.druid.pool.DruidDataSource
      driver-class-name: com.mysql.cj.jdbc.Driver
      initialSize: 5
      minIdle: 5
      maxActive: 20
      maxWait: 60000
      timeBetweenEvictionRunsMillis: 60000
      minEvictableIdleTimeMillis: 300000
      validationQuery: SELECT 1 FROM DUAL
      testWhileIdle: true
      testOnBorrow: true
      testOnReturn: true
      poolPreparedStatements: true
      maxPoolPreparedStatementPerConnectionSize: 20
      filters: stat,wall,log4j
      connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=5000
      useGlobalDataSourceStat: true
      loginUsername: pibigstar
      loginPassword: pibigstar
      #数据库连接地址
      url: jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&allowMultiQueries=true&characterEncoding=utf-8&serverTimezone=Asia/Shanghai&useSSL=false
      #用户名
      username: root
      #密码
      password: 123456

3. 配置映射类

@Configuration
@ConfigurationProperties(prefix = "spring.druid.datasource")
public class DruidProperties {
    private String type;
    private String driverClassName;
    private String url;
    private String username;
    private String password;

    private Integer initialSize;
    private Integer minIdle;
    private Integer maxActive;
    private Long maxWait;
    private Long timeBetweenEvictionRunsMillis;
    private Long minEvictableIdleTimeMillis;
    private String validationQuery;
    private boolean testWhileIdle;
    private boolean testOnBorrow;
    private boolean testOnReturn;
    private boolean poolPreparedStatements;
    private Integer maxPoolPreparedStatementPerConnectionSize;
    private String filters;
    private String connectionProperties;
    private boolean useGlobalDataSourceStat;
    private String loginUsername;
    private String loginPassword;
    //setter,getter方法
}

4. 初始化Druid

@Configuration
public class DruidAutoConfiguration {

    @Autowired
    private DruidProperties properties;

    @Bean
    @Primary
    public DruidDataSource dataSource() throws Exception {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName(properties.getDriverClassName());
        dataSource.setUrl(properties.getUrl());
        dataSource.setUsername(properties.getUsername());
        dataSource.setPassword(properties.getPassword());
        dataSource.setInitialSize(properties.getInitialSize());
        dataSource.setMinIdle(properties.getMinIdle());
        dataSource.setMaxActive(properties.getMaxActive());
        dataSource.setMaxWait(properties.getMaxWait());
        dataSource.setTimeBetweenEvictionRunsMillis(properties.getTimeBetweenEvictionRunsMillis());
        dataSource.setMinEvictableIdleTimeMillis(properties.getMinEvictableIdleTimeMillis());
        String validationQuery = properties.getValidationQuery();
        if (validationQuery != null && !"".equals(validationQuery)) {
            dataSource.setValidationQuery(validationQuery);
        }
        dataSource.setTestWhileIdle(properties.isTestWhileIdle());
        dataSource.setTestOnBorrow(properties.isTestOnBorrow());
        dataSource.setTestOnReturn(properties.isTestOnReturn());
        if (properties.isPoolPreparedStatements()) {
            dataSource.setMaxPoolPreparedStatementPerConnectionSize(properties.getMaxPoolPreparedStatementPerConnectionSize());
        }
        String connectionPropertiesStr = properties.getConnectionProperties();
        if (connectionPropertiesStr != null && !"".equals(connectionPropertiesStr)) {
            Properties connectProperties = new Properties();
            String[] propertiesList = connectionPropertiesStr.split(";");
            for (String propertiesTmp : propertiesList) {
                String[] obj = propertiesTmp.split("=");
                String key = obj[0];
                String value = obj[1];
                connectProperties.put(key, value);
            }
            dataSource.setConnectProperties(connectProperties);
        }
        dataSource.setUseGlobalDataSourceStat(properties.isUseGlobalDataSourceStat());
        WallConfig wallConfig = new WallConfig();
        wallConfig.setMultiStatementAllow(true);
        WallFilter wallFilter = new WallFilter();
        wallFilter.setConfig(wallConfig);
        List<Filter> filters = new ArrayList<>();
        filters.add(wallFilter);
        filters.add(new StatFilter());
        filters.add(new Log4jFilter());
        dataSource.setProxyFilters(filters);
        return dataSource;
    }

    /**
     * Druid的Servlet
     * @return
     */
    @Bean
    public ServletRegistrationBean druidStatViewServlet() {
        ServletRegistrationBean servletRegistrationBean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");

        /** 添加初始化参数：initParams */
        /** 白名单，如果不配置或value为空，则允许所有 */
        servletRegistrationBean.addInitParameter("allow", "127.0.0.1,192.0.0.1");
        /** 黑名单，与白名单存在相同IP时，优先于白名单 */
        servletRegistrationBean.addInitParameter("deny", "192.0.0.1");
        /** 用户名 */
        servletRegistrationBean.addInitParameter("loginUsername", properties.getLoginUsername());
        /** 密码 */
        servletRegistrationBean.addInitParameter("loginPassword", properties.getLoginPassword());
        /** 禁用页面上的“Reset All”功能 */
        servletRegistrationBean.addInitParameter("resetEnable", "false");
        return servletRegistrationBean;
    }

    /**
     * Druid拦截器，用于查看Druid监控
     * @return
     */
    @Bean
    public FilterRegistrationBean druidStatFilter() {
        FilterRegistrationBean filterRegistrationBean = new FilterRegistrationBean(new WebStatFilter());
        /** 过滤规则 */
        filterRegistrationBean.addUrlPatterns("/*");
        /** 忽略资源 */
        filterRegistrationBean.addInitParameter("exclusions", "*.js,*.gif,*.jpg,*.png,*.css,*.ico,/druid/*");
        return filterRegistrationBean;
    }
}

5. Druid与Mybatis对接

@Configuration
@EnableTransactionManagement
@AutoConfigureAfter(DruidAutoConfiguration.class)
@MapperScan(value = {"com.pibigstar.**.mapper.**"})
public class MybatisAutoConfiguration{

    /**
     * mybatis 配置路径
     */
    private static String MYBATIS_CONFIG = "classpath:mybatis/mybatis-config.xml";

    /**
     * mybatis mapper xml
     */
    private static String[] MAPPER_LOCATIONS_CONFIG = new String[]{
            "classpath*:com/pibigstar/**/mapper/mysql/*.xml",
            "classpath*:com/pibigstar/**/mapper/mysql/*/*.xml",
    };

    @Autowired
    private DruidDataSource dataSource;

    /**
     * 创建sqlSession
     * @return
     * @throws Exception
     */
    @Bean(name = "sqlSessionFactory")
    public SqlSessionFactoryBean createSqlSessionFactoryBean() throws Exception {

        PathMatchingResourcePatternResolver resolver = new PathMatchingResourcePatternResolver();
        Resource resource = resolver.getResource(MYBATIS_CONFIG);

        SqlSessionFactoryBean sqlSessionFactoryBean = new SqlSessionFactoryBean();

        /** 设置mybatis configuration 扫描路径 */
        sqlSessionFactoryBean.setConfigLocation(resource);

        /** 设置datasource */
        sqlSessionFactoryBean.setDataSource(dataSource);

        /** 设置mapperLocations */
        List<Resource> all = new ArrayList<>();
        for (String mapperLocation : MAPPER_LOCATIONS_CONFIG) {
            all.addAll(Arrays.asList(resolver.getResources(mapperLocation)));
        }
        sqlSessionFactoryBean.setMapperLocations(all.toArray(new Resource[all.size()]));
        return sqlSessionFactoryBean;
    }

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

    /**
     * 配置事务管理器
     */
    @Bean(name = "transactionManager")
    @Primary
    public DataSourceTransactionManager transactionManager(@Qualifier("dataSource") DataSource dataSource) throws Exception {
        return new DataSourceTransactionManager(dataSource);
    }
}

6. 新建mybatis-config.xml文件

在resources下新建mybatis文件夹，在此文件夹下新建mybatis-config.xml文件
可以在此文件中新增mysql插件之类的。

<?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="callSettersOnNulls" value="true"/>
        <setting name="localCacheScope" value="STATEMENT"/>
        <setting name="logPrefix" value="dao."/>
    </settings>
</configuration>

7. 新建log4j.properties

在resources下新建log4j.properties文件，如果没有此文件，控制台不会输出SQL语句

#1. DEBUG
#2. INFO
#3. WARN
#4. ERROR
#5. FATAL
log4j.rootLogger=DEBUG,stdout
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d [%p] %c:%L - %m%n

8. 测试

启动项目，浏览器访问：http://localhost:8080/druid/index.html
会自动跳转到登录页面，输入你在application.yml文件中配置的loginUsername和loginPassword，可进入首页，首页如下：

控制台打印SQL信息