【Java】DB Connection Pool ～ HikariCP / 複数DB編 ～

Java / DB関連

■ はじめに

https://blogs.yahoo.co.jp/dk521123/37397210.html
の続き。

今回は、複数DB「sampledb01」「sampledb02」「sampledb03」に
アクセスできるようなサンプルを作成する。

補足

 * Hibernate であれば、AbstractMultiTenantConnectionProviderが用意されている模様
http://docs.jboss.org/hibernate/orm/4.1/devguide/en-US/html/ch16.html
http://useof.org/java-open-source/org.hibernate.engine.jdbc.connections.spi.AbstractMultiTenantConnectionProvider

■ ポイント

 * java.sql.Connection.setCatalog(【DB名】)で切り替える
[[https://docs.oracle.com/javase/jp/8/docs/api/java/sql/Connection.html#setCatalog-java.lang.String-]]

■ 実行環境

 * OS : Windows10
 * Java : JDK1.8
 * DB : MySQL5.7

 * ライブラリは、Gradle を使用してダウンロード。

build.gradle

// Apply the java-library plugin to add support for Java Library
apply plugin: 'java-library'

// In this section you declare where to find the dependencies of your project
repositories {
    // Use jcenter for resolving your dependencies.
    // You can declare any Maven/Ivy/file repository here.
    jcenter()
}

dependencies {
    // !! HikariCP for Java8/9 !!
    compile group: 'com.zaxxer', name: 'HikariCP', version: '2.7.8'
    testCompile group: 'org.slf4j', name: 'slf4j-simple', version: '1.7.25'
    
    // MySQL
    compile group: 'mysql', name: 'mysql-connector-java', version: '5.1.46'
}

■ サンプルDBデータ

-- DB
CREATE DATABASE sampledb01;
CREATE DATABASE sampledb02;
CREATE DATABASE sampledb03;

-- Table
CREATE TABLE sampledb01.person(id INT NOT NULL AUTO_INCREMENT, name VARCHAR(20), birthdate DATE, PRIMARY KEY (`id`));
CREATE TABLE sampledb02.person(id INT NOT NULL AUTO_INCREMENT, name VARCHAR(20), birthdate DATE, PRIMARY KEY (`id`));
CREATE TABLE sampledb03.person(id INT NOT NULL AUTO_INCREMENT, name VARCHAR(20), birthdate DATE, PRIMARY KEY (`id`));

-- Sample Data
INSERT INTO sampledb01.person (`name`, `birthdate`) VALUES ('Mike', '2018-01-01');
INSERT INTO sampledb01.person (`name`, `birthdate`) VALUES ('Tom', '2018-01-02');
INSERT INTO sampledb01.person (`name`, `birthdate`) VALUES ('Kevin', '2018-01-03');

INSERT INTO sampledb02.person (`name`, `birthdate`) VALUES ('Kim', '2018-02-01');
INSERT INTO sampledb02.person (`name`, `birthdate`) VALUES ('Ken', '2018-02-02');
INSERT INTO sampledb02.person (`name`, `birthdate`) VALUES ('Chan', '2018-02-03');

INSERT INTO sampledb03.person (`name`, `birthdate`) VALUES ('Ichiro', '2018-03-01');
INSERT INTO sampledb03.person (`name`, `birthdate`) VALUES ('Giro', '2018-03-02');
INSERT INTO sampledb03.person (`name`, `birthdate`) VALUES ('Taro', '2018-03-03');
INSERT INTO sampledb03.person (`name`, `birthdate`) VALUES ('Saburo', '2018-03-04');

■ サンプル

DbDataSourceManager.java

import java.io.Closeable;
import java.sql.Connection;
import java.sql.SQLException;
import java.text.MessageFormat;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

public class DbDataSourceManager implements Closeable {
  private static final String DB_URL_FORMAT = "jdbc:mysql://{0}:{1}/{2}?useSSL=false&characterEncoding=UTF-8";

  private HikariDataSource dataSource;

  public DbDataSourceManager() {
  }

  public void initialize(String dbHost, String dbPort, String dbName) {
    HikariConfig dbConfig = DbDataSourceManager.createDbConfig(dbHost, dbPort, dbName);
    this.dataSource = new HikariDataSource(dbConfig);
  }

  public Connection getConnection() throws SQLException {
    if (this.dataSource == null) {
      return null;
    }

    return this.dataSource.getConnection();
  }

  @Override
  public void close() {
    if (this.dataSource != null) {
      this.dataSource.close();
    }
  }

  private static HikariConfig createDbConfig(String dbHost, String dbPort, String dbName) {
    HikariConfig config = new HikariConfig();

    // MySQL用ドライバを設定
    config.setDriverClassName("com.mysql.jdbc.Driver");

    // URL指定
    String dbUrl = MessageFormat.format(DB_URL_FORMAT, dbHost, dbPort, dbName);
    System.out.println("DB URL : " + dbUrl);
    config.setJdbcUrl(dbUrl);

    // ユーザ名、パスワード指定
    config.addDataSourceProperty("user", "root");
    config.addDataSourceProperty("password", "password");

    // キャッシュ系の設定(任意)
    config.addDataSourceProperty("cachePrepStmts", "true");
    config.addDataSourceProperty("prepStmtCacheSize", "250");
    config.addDataSourceProperty("prepStmtCacheSqlLimit", "2048");
    // サーバサイドプリペアードステートメントを使用する(任意)
    config.addDataSourceProperty("useServerPrepStmts", "true");

    // 接続をテストするためのクエリ
    config.setConnectionInitSql("SELECT 1");

    return config;
  }
}

MaltiDbDemo.java

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

public class MaltiDbDemo {

  public static void main(String[] args) {
    String[] dbNames = new String[] { "sampledb01", "sampledb02", "sampledb03" };

    try (DbDataSourceManager dataSourceManager = new DbDataSourceManager();) {
      dataSourceManager.initialize("localhost", "3306", "sampledb01");

      for (String dbName : dbNames) {
        System.out.println("DB Name : " + dbName);
        try (Connection connection = dataSourceManager.getConnection();) {
          // ★ここで切り替え★
          connection.setCatalog(dbName);
          
          try (Statement statement = connection.createStatement();
              ResultSet resultSet = statement.executeQuery("select * from person");) {
            while (resultSet.next()) {
              System.out.println("Id:" + resultSet.getString("id") + " Name:" + resultSet.getString("name")
                  + resultSet.getString("birthdate"));
            }
            System.out.println("***************");
          } catch (SQLException ex) {
            ex.printStackTrace();
          }
        } catch (SQLException ex) {
          ex.printStackTrace();
        }
      }
    }
  }
}
出力結果 
DB URL : jdbc:mysql://localhost:3306/sampledb01?useSSL=false&characterEncoding=UTF-8
[main] INFO com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Starting...
[main] INFO com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Start completed.
DB Name : sampledb01
Id:1 Name:Mike2018-01-01
Id:2 Name:Tom2018-01-02
Id:3 Name:Kevin2018-01-03
***************
DB Name : sampledb02
Id:1 Name:Kim2018-02-01
Id:2 Name:Ken2018-02-02
Id:3 Name:Chan2018-02-03
***************
DB Name : sampledb03
Id:1 Name:Ichiro2018-03-01
Id:2 Name:Giro2018-03-02
Id:3 Name:Taro2018-03-03
Id:4 Name:Saburo2018-03-04
***************
[main] INFO com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Shutdown initiated...
[main] INFO com.zaxxer.hikari.HikariDataSource - HikariPool-1 - Shutdown completed.

関連記事

【Java】DB Connection Pool ～ HikariCP / 導入編 ～

https://blogs.yahoo.co.jp/dk521123/37397210.html