SpringBoot数据库连接池入门

1.HikariCP单数据源

1.1 应用配置文件

在application.yml文件中配置属性，分别为：IP地址、用户名、密码、Driver
HikariCP连接池及其在springboot中的配置

#IP地址、用户名、密码、驱动注册
spring:
  datasource:
    url: jdbc:mysql://localhost:3306/springbootdemo?useUnicode=true&characterEncoding=utf-8&useSSL=false
    username: root
    password: 123456
    driver-class-name: com.mysql.cj.jdbc.Driver
#HikariCP配置
    hikari:
      minimum-idle: 10 #池中维护的最小连接数，默认是10个
      maximum-pool-size: 10 #池中最大连接数，包括空闲和使用的，默认是10个

1.2 测试是否配置成功

@SpringBootApplication
public class Demo03Application implements CommandLineRunner {

    private final static Logger logger = LoggerFactory.getLogger(Demo03Application.class);

    @Autowired
    DataSource dataSource;

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

    @Override
    public void run(String... args) throws Exception {
        try(Connection conn = dataSource.getConnection()) {
            logger.info("[run][获得连接：{}]",conn);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

运行结果如下图所示：

2.HikariCP多数据源

2.1 应用配置文件

spring:
  #datasource配置内容
  datasource:
    ##订单数据源配置内容
    orders:
      url: jdbc:mysql://localhost:3306/springbootdemo?useSSL=false&useUnicode=true&characterEncoding=utf-8
      username: root
      password: 123456
      driver-class-name: com.mysql.cj.jdbc.Driver
      hikari:
        minimum-idle: 10 #最小空闲连接数，默认是10
        maximum-pool-size: 10 #维护的最大连接数，默认是10

    ##用户数据源配置内容
    users:
      url: jdbc:mysql://localhost:3306/springbootdemo?useSSL=false&useUnicode=true&characterEncoding=true
      username: root
      password: 123456
      driver-class-name: com.mysql.cj.jdbc.Driver
      hikari:
        minimum-idle: 15 #最小空闲连接数
        maximum-pool-size: 15 #维护的最大连接数

2.2 创建数据源配置类

在config包下创建DataSourceConfig类

下面这段代码主要是创建了DataSourceProperties,再通过其去创建DataSource。
DataSourceProperties.initializeDataSourceBuilder()创建一个DataSourceBuilder
DataSource.type(HikariDataSource.class)为DataSource指定类型
DataSource.build()创建type中的指定数据源对象

@Configuration
public class DataSourceConfig {
    //创建数据源配置对象，并将一级属性读入到配置对象中
    @Primary//作为主DataSourceProperties Bean
    @Bean(name = "ordersDataSourceProperties")
    @ConfigurationProperties(prefix = "spring.datasource.orders")
    public DataSourceProperties ordersDataSourceProperties() {
        return new DataSourceProperties();
    }
    //创建数据源对象，并将二级属性读入到数据源对象中
    @Bean(name = "ordersDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.orders.hikari")
    public DataSource ordersDataSource() {
        DataSourceProperties properties = this.ordersDataSourceProperties();
        return createHikariDataSource(properties);
    }
    
    @Bean(name = "usersDataSourceProperties")
    @ConfigurationProperties(prefix = "spring.datasource.users")
    public DataSourceProperties usersDataSourceProperties() {
        return new DataSourceProperties();
    }
    
    @Bean(name = "usersDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.users.hikari")
    public DataSource usersDataSource(DataSourceProperties properties) {
        return createHikariDataSource(properties);
    }
    //创建HikariDataSource对象的静态方法
    private static HikariDataSource createHikariDataSource(DataSourceProperties properties) {
        HikariDataSource dataSource = properties.initializeDataSourceBuilder().type(HikariDataSource.class).build();
        if(StringUtils.hasText(properties.getName())) {
            dataSource.setPoolName(properties.getName());
        }
        return dataSource;
    }
}

2.3 测试是否配置成功

@Resource是代替setter方法，自动注入对象中，根据byName
@Autowires是代替setter方法，自动注入对象中，根据byType

@SpringBootApplication
public class Demo032Application implements CommandLineRunner {

    private final static Logger logger = LoggerFactory.getLogger(Demo032Application.class);

    @Resource(name = "ordersDataSource")
    private DataSource ordersDataSource;

    @Resource(name = "usersDataSource")
    private DataSource usersDataSource;

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

    @Override
    public void run(String... args) throws Exception {
        try (Connection conn = ordersDataSource.getConnection()) {
            logger.info("获得ordersDataSource连接：{}",conn);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

        try (Connection conn = usersDataSource.getConnection()) {
            logger.info("获得usersDataSource连接：{}",conn);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

运行结果如下图所示：

3. Druid单数据源

3.1 应用配置文件

在application.yml文件中配置属性，分别为：IP地址、用户名、密码、Driver、监控器

• 添加依赖

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

spring:
  datasource:
    url: jdbc:mysql://localhost:3306/springbootdemo?useSSL=false&useUnicode=true&characterEncoding=utf-8
    driver-class-name: com.mysql.cj.jdbc.Driver
    username: root
    password: 123456
    type: com.alibaba.druid.pool.DruidDataSource #自定义数据库连接池
  druid:
    min-idle: 0 #池中维护的最小空闲连接数，默认为0
    max-active: 20 #池中最大连接数
    filter:
      stat: #配置StatFilter,统计监控信息
        log-slow-sql: true #开启慢查询记录
        slow-sql-millis: 5000 #超过5000ms，就是慢查询
    stat-view-servlet: #配置StatViewServlet,展示监控信息
      enabled: true
      login-username: guowenchao
      login-password: 123456

3.2 测试是否配置成功

@Slf4j
@SpringBootApplication
public class Demo033Application implements CommandLineRunner {

    @Autowired
    private DataSource dataSource;

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

    @Override
    public void run(String... args) throws Exception {
        log.info("获取连接：{}",dataSource.getClass());
    }
}

运行结果如下图所示：

4. Druid多数据源

4.1 应用配置文件

• 添加依赖

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

spring:
  # datasource 数据源配置内容
  datasource:
    # 订单数据源配置
    orders:
      url: jdbc:mysql://127.0.0.1:3306/springbootdemo?useSSL=false&useUnicode=true&characterEncoding=UTF-8
      driver-class-name: com.mysql.jdbc.Driver
      username: root
      password: 123456
      type: com.alibaba.druid.pool.DruidDataSource
      # Druid 自定义配置，对应 DruidDataSource 中的 setting 方法的属性
      min-idle: 0
      max-active: 20
    # 用户数据源配置
    users:
      url: jdbc:mysql://127.0.0.1:3306/tspringbootdemo?useSSL=false&useUnicode=true&characterEncoding=UTF-8
      driver-class-name: com.mysql.jdbc.Driver
      username: root
      password:
      type: com.alibaba.druid.pool.DruidDataSource
      # Druid 自定义配置，对应 DruidDataSource 中的 setting 方法的属性
      min-idle: 0
      max-active: 20
    druid:
      # 过滤器配置
      filter:
        stat: # 配置 StatFilter
          log-slow-sql: true
          slow-sql-millis: 5000
      # StatViewServlet 配置
      stat-view-servlet:
        enabled: true
        login-username: guowenchao
        login-password: 123456

4.2 创建数据源配置类

在config包下创建DataSourceConfig类

因为druid的默认配置放在orders和users下面，所以可以按照下面方式创建：

@Configuration
public class DataSourceConfig {

    @Primary
    @Bean(name = "ordersDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.orders")
    public DataSource ordersDataSource() {
        return DruidDataSourceBuilder.create().build();
    }

    @Bean(name = "usersDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.users")
    public DataSource usersDataSource() {
        return DruidDataSourceBuilder.create().build();
    }
}

4.3 测试是否配置成功

@Slf4j
@SpringBootApplication
public class Demo034Application implements CommandLineRunner {

    @Resource(name = "ordersDataSource")
    private DataSource ordersDataSource;

    @Resource(name = "usersDataSource")
    private DataSource usersDataSource;

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

    @Override
    public void run(String... args) throws Exception {
        log.info("获得连接1：{}",ordersDataSource.getClass());
        log.info("获得连接2：{}",usersDataSource.getClass());
    }
}

运行结果如下图所示：

声明：上述文章参考自：芋道SpringBoot数据库连接池入门

环环无敌大可爱😗