2
4

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

複数のDBへ1つのEntityから参照する(SpringBoot+JPA)

Last updated at Posted at 2019-05-04

テーブル名、構成が全く同じ複数DB(シャーディングなどしているような場合)
に対してSpringBootから参照する。

参考

以下を参考にさせていただきました。
[Spring Boot] 動的データソース変更
https://qiita.com/syukai/items/0d4bf27f82fef9965cdd

やること

・複数のDBを参照する(登録、更新はなし)
・テーブル構成が同じなので1つのEntityを使い回す
・リクエストのクエリ文字列によって参照先DBを切り替える

作成/修正クラス

・Config
・DynamicRoutingDataSourceResolver
・ContextHolder
・DBの切り替え

修正

Congfig

DataSourceConfig.java
package src.main.java.com.example.demo;

import java.util.Map;
import java.util.HashMap;
import javax.sql.DataSource;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Primary;
import org.springframework.context.annotation.Configuration;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import com.zaxxer.hikari.HikariDataSource;

@Configuration
@EnableJpaRepositories(
  basePackages = "jp.co.example.repositories",
  entityManagerFactoryRef = "entityManagerFactory"
)
public class DataSourceConfig {
  public HikariDataSource Node1() {
    HikariDataSource ds = new HikariDataSource();
    ds.setDriverClassName("com.mysql.cj.jdbc.Driver");
    ds.setJdbcUrl("jdbc:mysql://XXX.XXX.XXX.XXX/node1");
    ds.setUsername("");
    ds.setPassword("");
    return ds;
  }

  public HikariDataSource Node2() {
    HikariDataSource ds = new HikariDataSource();
    ds.setDriverClassName("com.mysql.cj.jdbc.Driver");
    ds.setJdbcUrl("jdbc:mysql://XXX.XXX.XXX.XXX/node2");
    ds.setUsername("");
    ds.setPassword("");
    return ds;
  }

  @Bean(name = "datasource")
  @Primary
  public DynamicRoutingDataSourceResolver datasource() {
    DynamicRoutingDataSourceResolver resolver = new DynamicRoutingDataSourceResolver();
    Map<Object, Object> dataSources = new HashMap<>();
    dataSources.put("node1", Node1());
    dataSources.put("node2", Node2());
    resolver.setTargetDataSources(dataSources);
    resolver.setDefaultTargetDataSource(Node1());
    return resolver;
  }

  @Bean(name = "datasource")
  public LocalContainerEntityManagerFactoryBean entityManagerFactory(EntityManagerFactoryBuilder bulder, @Qualifier("datasource") DataSource dataSource) {
    return bulder.dataSource(dataSource).packages("jp.co.example.entity").build();
  }
}

DynamicRoutingDataSourceResolver

DynamicRoutingDataSourceResolver.java
package src.main.java.com.example.demo;

import java.util.Objects;
import org.springframework.jdbc.datasource.lookup.AbstractRoutingDataSource;

public class DynamicRoutingDataSourceResolver extends AbstractRoutingDataSource {
  @Override
  protected Object determineCurrentLookupKey() {
    if (Objects.equals("0", SchemaContextHolder.getSchemaType())) {
      return "node1";
    } else {
      return "node2";
    }
  }
}

SchemaContextHolder

SchemaContextHolder.java
package src.main.java.com.example.demo;

public class SchemaContextHolder {
  private static ThreadLocal<String> contextHolder = new ThreadLocal<>();
  public static void setSchemaType(String partitionkey) { contextHolder.set(partitionkey); }
  public static String getSchemaType() { return contextHolder.get(); }
}

DBの切り替え

package src.main.java.com.example.demo;

import java.util.List;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import src.main.java.com.example.demo.User;

public class DbDao implements Db {
  @PersistenceContext
  private EntityManager entityManager;

  public List<User> getUsers(String partitionkey) {
    SchemaContextHolder.setSchemaType(partitionkey);
    return entityManager.createNativeQuery("SELECT * FROM USERS;", User.class).getResultList();
  }
}
2
4
2

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
2
4

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?