Introduction to Java Database Connection Pool DBPool

  • 2021-12-13 08:16:11
  • OfStack

Directory 1, configuration parameter 2, use 2.1, use 2.1. 1 directly, introduce dependency 2.1. 2, use example 2.2, use 2.1. 1 in SpringBoot, introduce dependency 2.1. 2, single data source 2.1. 3, multiple data sources

Foreword:

DBPool It is an efficient and easy-to-configure database connection pool, which supports JDBC 4.2, but it is no longer maintained at present; This paper briefly introduces the use of DBPool. The software versions used in this paper are Java 1.8.0_191, DBPool 7.0. 1, Spring Boot 2.3. 12. RELEASE.

1. Configuration parameters

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

For detailed explanation, please refer to the official website document: https://www.snaq.net/software/dbpool/

2. Use

2.1. Direct use

2.1. 1, Introducing dependencies

<dependency>

    <groupId>net.snaq</groupId>

    <artifactId>dbpool</artifactId>

    <version>7.0.1</version>

</dependency>

2.1. 2. Use examples

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 Connection pool ");

        dbPoolDataSource.setDescription("DBPool Connection pool test ");

        dbPoolDataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");

        dbPoolDataSource.setUrl("jdbc:mysql://10.40.9.11:3306/mydb?useUnicode=true&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 = null;

        try {

            connection = dbPoolDataSource.getConnection();

            Statement st = connection.createStatement();

            ResultSet rs = st.executeQuery("select version()");

            if (rs.next()) {

                System.out.println(rs.getString(1));

            }

        } catch (SQLException e) {

            e.printStackTrace();

        } finally {

            close(connection);

        }

        // In actual use 1 Generally, the data source is initialized when the application starts, and the application obtains the connection from the data source; The data source is not closed. 

        dbPoolDataSource.release();

    }

    private static void close(Connection connection) {

        if (connection != null) {

            try {

                connection.close();

            } catch (SQLException e) {

                e.printStackTrace();

            }

        }

    }

}

2.2. Used in SpringBoot

2.1. 1, Introducing dependencies

<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, single data source

application. yml configuration:


spring:

  datasource:

    dbpool:

      name: DBPool Connection pool 

      description: DBPool Connection pool test 

      driver-class-name: com.mysql.cj.jdbc.Driver

      url: jdbc:mysql://10.40.9.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

Data source configuration class:


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();

    }

}

Use:


@Autowired

private DataSource dataSource;

2.1. 3, Multiple Data Sources

application. yml configuration:


spring:

  datasource:

    dbpool:

      db1:

        name: DBPool Connection pool 1

        description: DBPool Connection pool test 1

        driver-class-name: com.mysql.cj.jdbc.Driver

        url: jdbc:mysql://10.40.9.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 Connection pool 2

        description: DBPool Connection pool test 2

        driver-class-name: com.mysql.cj.jdbc.Driver

        url: jdbc:mysql://10.40.9.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

Data source configuration class:


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("dataSource2")

    @ConfigurationProperties(prefix = "spring.datasource.dbpool.db2")

    public DataSource dataSource2() {

        return DataSourceBuilder.create().type(DBPoolDataSource.class).build();

    }

}

Use:


@Autowired

@Qualifier("dataSource1")

private DataSource dataSource1;

@Autowired

@Qualifier("dataSource2")

private DataSource dataSource2;

 

Related articles: