搭建大型分布式服务（七）SpringBoot整合数据库连接池druid

一、本文要点

接上文，我们已经把SpringBoot整合mybatis了，本文将介绍SpringBoot如何整合阿里的数据库连接池druid，管理我们的数据库连接池。阿里的druid会提供了基础监控，不过如果配置不当，会有安全问题。系列文章完整目录

• springboot整合druid

• junit 5 单元测试

• 自动回滚单元测试操作DB数据

• springboot + mybatis + druid

• yml 和 properties 在线互转

二、开发环境

• jdk 1.8
• maven 3.6.2
• mybatis 1.3.0
• springboot 2.4.3
• mysql 5.6.46
• junit 5
• druid
• idea 2020

三、修改pom.xml 增加依赖

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

三、修改配置文件

修改application-dev.properties文件。

logging.file.path=./logs

spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
spring.datasource.druid.url=jdbc:mysql://9.135.xxx.xxx:3306/mmc?useUnicode=true&characterEncoding=utf8&autoReconnect=true&useSSL=false&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&serverTimezone=GMT%2B8
spring.datasource.druid.username=root
spring.datasource.druid.password=123456

spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.druid.async-close-connection-enable=true
spring.datasource.druid.connectionProperties=druid.stat.mergeSql=true;druid.stat.slowSqlMillis=2000
spring.datasource.druid.filters=stat,wall
spring.datasource.druid.initial-size=2
spring.datasource.druid.max-active=8
spring.datasource.druid.max-pool-prepared-statement-per-connection-size=5
spring.datasource.druid.max-wait=2000
spring.datasource.druid.min-evictable-idle-time-millis=30001
spring.datasource.druid.min-idle=2
spring.datasource.druid.pool-prepared-statements=true
spring.datasource.druid.test-on-borrow=false
spring.datasource.druid.test-on-return=false
spring.datasource.druid.test-while-idle=true
spring.datasource.druid.time-between-eviction-runs-millis=10000
spring.datasource.druid.validation-query=select 1 from dual
spring.datasource.druid.validation-query-timeout=1
spring.datasource.druid.stat-view-servlet.enabled=true
spring.datasource.druid.stat-view-servlet.login-username=admin
spring.datasource.druid.stat-view-servlet.login-password=dc8d31b188f343b19b9c6cf888c933ba

五、增加配置类

1、注释掉启动类MemberApplication.java增加@MapperScan。

// @MapperScan(basePackages = "com.mmc.lesson.member.mapper")
@SpringBootApplication
public class MemberApplication {

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

}

2、编写MemberService.java，这样写的后续增加多数据源的时候会有好处。

@Configuration
@MapperScan(basePackages = {
        "com.mmc.lesson.member.mapper"}, sqlSessionTemplateRef = "druidSqlSessionTemplate")
public class MemberDataSourceConfiguration {


    @Bean(name = "druidDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.druid")
    public DataSource dataSource() {
        return DataSourceBuilder.create().type(DruidDataSource.class).build();
    }

    /**
     * 会话工厂.
     */
    @Bean(name = "druidSqlSessionFactory")
    public SqlSessionFactory setSqlSessionFactory(@Qualifier("druidDataSource") DataSource dataSource)
            throws Exception {
        SqlSessionFactoryBean bean = new SqlSessionFactoryBean();
        bean.setDataSource(dataSource);
        bean.setMapperLocations(
                new PathMatchingResourcePatternResolver().getResources("classpath:mysqlMappers/*.xml"));
        bean.setVfs(SpringBootVFS.class);
        return bean.getObject();
    }

    /**
     * 目前只有单元测试用到事务.
     */
    @Bean(name = "druidTransactionManager")
    public PlatformTransactionManager platformTransactionManager(@Qualifier("druidDataSource") DataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

    /**
     * mybatis用到会话.
     */
    @Bean(name = "druidSqlSessionTemplate")
    @Primary
    public SqlSessionTemplate sqlSessionTemplate(
            @Qualifier("druidSqlSessionFactory") SqlSessionFactory sqlSessionFactory) {
        return new SqlSessionTemplate(sqlSessionFactory);
    }
}

六、运行一下

1、编写单元测试。

@Slf4j
@ActiveProfiles("dev")
@ExtendWith(SpringExtension.class)
@SpringBootTest
@Transactional // 自动回滚单元测试插入DB的数据
public class MemberServiceTest {

    @Resource
    private MemberService memberService;

    /**
     * add.
     */
    @Test
    public void testAdd() {

        TblMemberInfo member = new TblMemberInfo();
        member.setUname("zhangsan");
        member.setUsex(1);
        member.setUbirth(new Date());
        member.setUtel("888");
        member.setUaddr("凌霄殿");
        member.setState(0);
        member.setDelFlag(0);
        member.setUphoto(null);

        TblMemberInfo ret = memberService.save(member);

        Assertions.assertThat(ret).isNotNull();
        log.info("--------------------------------------------------");
        log.info(ret.getUname());


    }

}

2、效果。

[2021-02-25 12:03:41.974] [main] [DEBUG] [c.m.l.member.mapper.TblMemberInfoMapper.insert:?] - ==> Parameters: null, zhangsan(String), 1(Integer), 2021-02-25 12:03:41.729(Timestamp), 888(String), 凌霄殿(String), null, null, 0(Integer), 0(Integer), null
[2021-02-25 12:03:41.990] [main] [DEBUG] [c.m.l.member.mapper.TblMemberInfoMapper.insert:?] - <==    Updates: 1
[2021-02-25 12:03:41.991] [main] [DEBUG] [org.mybatis.spring.SqlSessionUtils:?] - Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2d64c100]
[2021-02-25 12:03:42.032] [main] [INFO] [com.mmc.lesson.member.service.MemberServiceTest:?] - --------------------------------------------------
[2021-02-25 12:03:42.032] [main] [INFO] [com.mmc.lesson.member.service.MemberServiceTest:?] - zhangsan

3、启动应用，浏览器访问 http://localhost:8080/druid/index.html。

七、小结

虽然druid挺好用的，不过公司不提倡用，所以后文都是以Hikari为主，完整代码地址：戳这里。下一篇《搭建大型分布式服务（八）Docker搭建开发环境安装ES》

加我一起交流学习！