AI专业者开发社区 1 号成员
  •  0 回帖  •  3 浏览  •  4 个月前
   

Java 数据库连接池介绍(3)--DBPool 介绍

DBPool 是一个高效易配置的数据库连接池，支持 JDBC 4.2，但目前已经不维护了；本文简单介绍下 DBPool 的使用，文中使用到的软件版本：Java 1.8.0_191、DBPool 7.0.1、Spring Boot 2.3.12.RELEASE。

1、配置参数

参数 描述
name 连接池名称
description 描述
driverClassName 驱动名称
url 连接 url
user 用户名
password 密码
passwordDecoderClassName 密码解密类名，需实现 snaq.db.PasswordDecoder 接口，需要有无参构造方法
minPool 连接池最小连接数
maxPool 连接池最大连接数
maxSize 可以创建的最大连接数
idleTimeout 空闲连接数最大存活时间 (秒)，0 表示无限制
loginTimeout 创建连接的超时时间 (秒)
validatorClassName 校验类名，需实现 snaq.db.ConnectionValidator 接口，需要有无参构造方法
validatorQuery 校验查询语句

详细说明可参考官网文档：https://www.snaq.net/software/dbpool/

2、使用

2.1、直接使用

2.1.1、引入依赖

<dependency>
    <groupId>net.snaq</groupId>
    <artifactId>dbpool</artifactId>
    <version>7.0.1</version>
</dependency>

2.1.2、使用例子

package com.abc.demo.general.dbpool;

import snaq.db.DBPoolDataSource;


import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;


public class DBPoolCase {
public static void main(String[] args) {
DBPoolDataSource dbPoolDataSource = new DBPoolDataSource();
dbPoolDataSource.setName("DBPool 连接池");
dbPoolDataSource.setDescription("DBPool 连接池测试");
dbPoolDataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
dbPoolDataSource.setUrl("jdbc:mysql://10.49.196.11:3306/mydb?useUnicode=true&amp;characterEncoding=UTF-8");
dbPoolDataSource.setUser("root");
dbPoolDataSource.setPassword("123456");
dbPoolDataSource.setMinPool(5);
dbPoolDataSource.setMaxPool(10);
dbPoolDataSource.setMaxSize(30);
dbPoolDataSource.setIdleTimeout(3600);
dbPoolDataSource.setLoginTimeout(60);
dbPoolDataSource.setValidationQuery("select 1");


    Connection connection </span>= <span style="color: rgba(0, 0, 255, 1)">null</span><span style="color: rgba(0, 0, 0, 1)">;
    </span><span style="color: rgba(0, 0, 255, 1)">try</span><span style="color: rgba(0, 0, 0, 1)"> {
        connection </span>=<span style="color: rgba(0, 0, 0, 1)"> dbPoolDataSource.getConnection();
        Statement st </span>=<span style="color: rgba(0, 0, 0, 1)"> connection.createStatement();
        ResultSet rs </span>= st.executeQuery("select version()"<span style="color: rgba(0, 0, 0, 1)">);
        </span><span style="color: rgba(0, 0, 255, 1)">if</span><span style="color: rgba(0, 0, 0, 1)"> (rs.next()) {
            System.out.println(rs.getString(</span>1<span style="color: rgba(0, 0, 0, 1)">));
        }
    } </span><span style="color: rgba(0, 0, 255, 1)">catch</span><span style="color: rgba(0, 0, 0, 1)"> (SQLException e) {
        e.printStackTrace();
    } </span><span style="color: rgba(0, 0, 255, 1)">finally</span><span style="color: rgba(0, 0, 0, 1)"> {
        close(connection);
    }

    </span><span style="color: rgba(0, 128, 0, 1)">//</span><span style="color: rgba(0, 128, 0, 1)">实际使用中一般是在应用启动时初始化数据源，应用从数据源中获取连接；并不会关闭数据源。</span>



        dbPoolDataSource.release();
}


</span><span style="color: rgba(0, 0, 255, 1)">private</span> <span style="color: rgba(0, 0, 255, 1)">static</span> <span style="color: rgba(0, 0, 255, 1)">void</span><span style="color: rgba(0, 0, 0, 1)"> close(Connection connection) {
    </span><span style="color: rgba(0, 0, 255, 1)">if</span> (connection != <span style="color: rgba(0, 0, 255, 1)">null</span><span style="color: rgba(0, 0, 0, 1)">) {
        </span><span style="color: rgba(0, 0, 255, 1)">try</span><span style="color: rgba(0, 0, 0, 1)"> {
            connection.close();
        } </span><span style="color: rgba(0, 0, 255, 1)">catch</span><span style="color: rgba(0, 0, 0, 1)"> (SQLException e) {
            e.printStackTrace();
        }
    }
}



}

2.2、在 SpringBoot 中使用

2.1.1、引入依赖

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.3.12.RELEASE</version>
    <relativePath />
</parent>

<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.springframework</groupId>
<artifactId>spring-jdbc</artifactId>
</dependency>
<dependency>
<groupId>net.snaq</groupId>
<artifactId>dbpool</artifactId>
<version>7.0.1</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>
</dependencies>

2.1.2、单数据源

application.yml 配置：

spring:
  datasource:
    dbpool:
      name: DBPool 连接池
      description: DBPool 连接池测试
      driver-class-name: com.mysql.cj.jdbc.Driver
      url: jdbc:mysql://10.49.196.11:3306/myDb?useUnicode=true&characterEncoding=UTF-8
      user: root
      password: 123456
      min-pool: 5
      max-pool: 10
      max-size: 30
      idle-timeout: 3600
      login-timeout: 60
      validator-query: select 1

数据源配置类：

package com.abc.demo.config;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import snaq.db.DBPoolDataSource;

import javax.sql.DataSource;


@Configuration
public class DataSourceConfig {
@Bean("dataSource")
@ConfigurationProperties(prefix = "spring.datasource.dbpool")
public DataSource dataSource() {
return DataSourceBuilder.create().type(DBPoolDataSource.class).build();
}
}

使用：

@Autowired
private DataSource dataSource;

2.1.3、多数据源

application.yml 配置：

spring:
  datasource:
    dbpool:
      db1:
        name: DBPool 连接池 1
        description: DBPool 连接池测试 1
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://10.49.196.11:3306/mydb?useUnicode=true&characterEncoding=UTF-8
        user: root
        password: 123456
        min-pool: 5
        max-pool: 10
        max-size: 30
        idle-timeout: 3600
        login-timeout: 60
        validator-query: select 1
      db2:
        name: DBPool 连接池 2
        description: DBPool 连接池测试 2
        driver-class-name: com.mysql.cj.jdbc.Driver
        url: jdbc:mysql://10.49.196.12:3306/mydb?useUnicode=true&characterEncoding=UTF-8
        user: root
        password: 123456
        min-pool: 5
        max-pool: 10
        max-size: 30
        idle-timeout: 3600
        login-timeout: 60
        validator-query: select 1

数据源配置类：

package com.abc.demo.config;

import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import snaq.db.DBPoolDataSource;


import javax.sql.DataSource;


@Configuration
public class DataSourceConfig {
@Bean("dataSource1")
@ConfigurationProperties(prefix = "spring.datasource.dbpool.db1")
public DataSource dataSource1() {
return DataSourceBuilder.create().type(DBPoolDataSource.class).build();
}


@Bean(</span>"dataSource2"<span style="color: rgba(0, 0, 0, 1)">)
@ConfigurationProperties(prefix </span>= "spring.datasource.dbpool.db2"<span style="color: rgba(0, 0, 0, 1)">)
</span><span style="color: rgba(0, 0, 255, 1)">public</span><span style="color: rgba(0, 0, 0, 1)"> DataSource dataSource2() {
    </span><span style="color: rgba(0, 0, 255, 1)">return</span> DataSourceBuilder.create().type(DBPoolDataSource.<span style="color: rgba(0, 0, 255, 1)">class</span><span style="color: rgba(0, 0, 0, 1)">).build();
}



}

使用：

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

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