ottijp blog

Spring BootでマルチテナントDBを使う時に注意すること

  • 2025-03-23

Spring BootでマルチテナントDB（スキーマベース）を使う時に，以下の様な記事を参考に実装したところ，うまくいかない部分があったので記録しておきます．

TL;DR

  • spring.jpa.open-in-view=falseとすること
  • トランザクション管理が必要な場合は@Transactionalを付けたメソッドを呼ぶ前にスキーマを切り替えること

ソースコードの全体は以下に置いています．

cf. ottijp/spring-boot-multi-tenant

環境

  • PostgreSQL: 17.4
  • Java: 21.0
  • 依存ライブラリ
    • lombok: 1.18
    • spring-boot-starter-web: 3.4
    • spring-boot-starter-data-jpa: 3.4
    • postgresql: 42.7

やりたいこと

管理用のスキーマとテナントごとのスキーマから構成されるマルチテナントDB（スキーマベース）にSpring Bootアプリケーションからアクセスします． Spring Bootアプリケーションは，HTTPリクエストに含まれるテナント名からテナントのスキーマを特定し，テナントのスキーマからデータを読み出します．

database structure

この際，HTTPリクエストに含まれるのはテナント名（≠スキーマ名）であり，管理用のスキーマでテナントのスキーマ名を名前解決する必要があります．

実装概要

こちらのSpring Initialzerをベースしました．

冒頭のブログ記事を参考に，Hibernateの MultiTenantConnectionProviderと CurrentTenantIdentifierResolver を使って，サービスクラスでスキーマを切り替えてデータアクセスできるようにしました．

コード例は以下です．

コネクションプロバイダ
@Component
@Slf4j
class ConnectionProvider implements MultiTenantConnectionProvider<String>, HibernatePropertiesCustomizer {

  @Autowired
  DataSource dataSource;

  @Override
  public Connection getAnyConnection() throws SQLException {
    return getConnection("PUBLIC");
  }

  @Override
  public void releaseAnyConnection(Connection connection) throws SQLException {
    connection.close();
  }

  @Override
  public void customize(Map<String, Object> hibernateProperties) {
    hibernateProperties.put(AvailableSettings.MULTI_TENANT_CONNECTION_PROVIDER, this);
  }

  @Override
  public boolean isUnwrappableAs(Class aClass) {
    return false;
  }

  @Override
  public <T> T unwrap(Class<T> aClass) {
    return null;
  }

  @Override
  public Connection getConnection(String tenantIdentifier) throws SQLException {
    Connection connection = dataSource.getConnection();
    log.info("getConnection: tenantId={}, connection={}", tenantIdentifier, connection.toString());
    connection.setSchema(tenantIdentifier);
    return connection;
  }

  @Override
  public void releaseConnection(String tenantIdentifier, Connection connection) throws SQLException {
    log.info("releaseConnection: tenantId={}, connection={}", tenantIdentifier, connection.toString());
    connection.setSchema("PUBLIC");
    connection.close();
  }

  @Override
  public boolean supportsAggressiveRelease() {
    return false;
  }
}
同一スレッド内でテナントIDを保持するコンテキスト
@Slf4j
public class TenantContext {
  private static ThreadLocal<String> currentTenant = new InheritableThreadLocal<>();

  public static String getCurrentTenant() {
    return currentTenant.get();
  }

  public static void setCurrentTenant(String tenantId) {
    log.info("setCurrentTenant: tenantId={}", tenantId);
    currentTenant.set(tenantId);
  }

  public static void clear() {
    currentTenant.set(null);
  }
}
テナントIDリゾルバ
@Component
@Slf4j
class TenantIdentifierResolver implements CurrentTenantIdentifierResolver<String>, HibernatePropertiesCustomizer {

  private String defaultTenant = "PUBLIC";

  @Override
  public String resolveCurrentTenantIdentifier() {
    String t =  TenantContext.getCurrentTenant();
    log.info("resolveCurrentTenantIdentifier: current={}", t);
    if(t != null){
      return t;
    } else {
      return defaultTenant;
    }
  }

  @Override
  public void customize(Map<String, Object> hibernateProperties) {
    hibernateProperties.put(AvailableSettings.MULTI_TENANT_IDENTIFIER_RESOLVER, this);
  }

  @Override
  public boolean validateExistingCurrentSessions() {
    return true;
  }
}
コントローラ
@SpringBootApplication
@RestController
@Slf4j
public class DemoApplication {

  @Autowired
  private ProductService productService;

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

  @GetMapping("/products")
  public List<Product> products(@RequestParam("tenantId") String tenantId) {
    log.info("/products: tenantId={}", tenantId);
    var products = productService.loadProducts(tenantId);
    return products;
  }

}
サービス
@Service
@Slf4j
class ProductService {

  @Autowired
  private TenantRepository tenantRepository;
  @Autowired
  private ProductRepository productRepository;

  public List<Product> loadProducts(String tenantId) {
    log.info("loadProducts: tenantId={}", tenantId);

    // get tenant information
    log.info("start to get tenant information");
    TenantContext.setCurrentTenant("management");
    var tenants = tenantRepository.findById(tenantId);
    log.info("tenants: {}", tenants);

    // get product from tenant schema
    log.info("start to get product from tenant schema");
    TenantContext.setCurrentTenant(tenants.get().getSchemaName());
    var product = productRepository.findAll();
    log.info("product: {}", product);

    return product;
  }

}

問題

HTTPクライアントからhttp://localhost:8080/products?tenantId=TENANT%2001にリクエストを送ると，以下のようなエラーが発生しました．

[exec-1] INFO  rBase.[Tomcat].[localhost].[/]: Initializing Spring DispatcherServlet 'dispatcherServlet'
[exec-1] INFO  .web.servlet.DispatcherServlet: Initializing Servlet 'dispatcherServlet'
[exec-1] INFO  .web.servlet.DispatcherServlet: Completed initialization in 1 ms
[exec-1] INFO  .demo.TenantIdentifierResolver: resolveCurrentTenantIdentifier: current=null
[exec-1] TRACE hibernate.internal.SessionImpl: Opened Session [e03eab8d-e033-4332-8cc9-f9f2be8d33da] at timestamp: 1742725994117
[exec-1] INFO  m.example.demo.DemoApplication: /products: tenantId=TENANT 01
[exec-1] INFO  om.example.demo.ProductService: loadProducts: tenantId=TENANT 01
[exec-1] INFO  om.example.demo.ProductService: start to get tenant information
[exec-1] INFO  com.example.demo.TenantContext: setCurrentTenant: tenantId=management
[exec-1] INFO  xample.demo.ConnectionProvider: getConnection: tenantId=PUBLIC, connection=HikariProxyConnection@27085631 wrapping org.postgresql.jdbc.PgConnection@3a4e524
[exec-1] DEBUG ction.internal.TransactionImpl: On TransactionImpl creation, JpaCompliance#isJpaTransactionComplianceEnabled == false
[exec-1] DEBUG ction.internal.TransactionImpl: begin
[exec-1] DEBUG              org.hibernate.SQL: select t1_0.tenant_name,t1_0.schema_name from tenants t1_0 where t1_0.tenant_name=?
[exec-1] WARN  ne.jdbc.spi.SqlExceptionHelper: SQL Error: 0, SQLState: 42P01
[exec-1] ERROR ne.jdbc.spi.SqlExceptionHelper: ERROR: relation "tenants" does not exist
  Position: 47
[exec-1] INFO  ernal.DefaultLoadEventListener: HHH000327: Error performing load command
org.hibernate.exception.SQLGrammarException: JDBC exception executing SQL [select t1_0.tenant_name,t1_0.schema_name from tenants t1_0 where t1_0.tenant_name=?] [ERROR: relation "tenants" does not exist
  Position: 47] [n/a]
        at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:91)
        at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:58)

（中略）

[exec-1] DEBUG ction.internal.TransactionImpl: rolling back
[exec-1] TRACE hibernate.internal.SessionImpl: SessionImpl#afterTransactionCompletion(successful=false, delayed=false)
[exec-1] TRACE hibernate.internal.SessionImpl: Closing session [e03eab8d-e033-4332-8cc9-f9f2be8d33da]
[exec-1] INFO  xample.demo.ConnectionProvider: releaseConnection: tenantId=PUBLIC, connection=HikariProxyConnection@27085631 wrapping org.postgresql.jdbc.PgConnection@3a4e524
[exec-1] ERROR lhost].[/].[dispatcherServlet]: Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: org.springframework.dao.InvalidDataAccessResourceUsageException: JDBC exception executing SQL [select t1_0.tenant_name,t1_0.schema_name from tenants t1_0 where t1_0.tenant_name=?] [ERROR: relation "tenants" does not exist
  Position: 47] [n/a]; SQL [n/a]] with root cause
org.postgresql.util.PSQLException: ERROR: relation "tenants" does not exist
  Position: 47
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2733)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2420)

（中略）

[exec-1] INFO  .demo.TenantIdentifierResolver: resolveCurrentTenantIdentifier: current=management
[exec-1] TRACE hibernate.internal.SessionImpl: Opened Session [18855ddf-dcd7-4c0c-a165-8e154db58d50] at timestamp: 1742725994154
[exec-1] TRACE hibernate.internal.SessionImpl: Closing session [18855ddf-dcd7-4c0c-a165-8e154db58d50]

ログを見ると，TenantIdentifierResolverによるテナント名の名前解決がコントローラメソッドの実行前に呼ばれており， サービスクラスでTenantContext.setCurrentTenant("management");としたにも関わらず，その後はテナント名の名前解決は行われていませんでした． その状態でコネクションプロバイダのgetConnection(String)が呼ばれているので，期待したスキーマに対するSQLクエリが発行されていないようです．

原因と解決方法

application.propertiesにspring.jpa.open-in-view=falseと設定を入れていないのが原因でした． 設定を入れることで，以下のように期待したSQLクエリが発行されるようになりました．

[exec-1] INFO  rBase.[Tomcat].[localhost].[/]: Initializing Spring DispatcherServlet 'dispatcherServlet'
[exec-1] INFO  .web.servlet.DispatcherServlet: Initializing Servlet 'dispatcherServlet'
[exec-1] INFO  .web.servlet.DispatcherServlet: Completed initialization in 1 ms
[exec-1] INFO  m.example.demo.DemoApplication: /products: tenantId=TENANT 01
[exec-1] INFO  om.example.demo.ProductService: loadProducts: tenantId=TENANT 01
[exec-1] INFO  om.example.demo.ProductService: start to get tenant information
[exec-1] INFO  com.example.demo.TenantContext: setCurrentTenant: tenantId=management
[exec-1] INFO  .demo.TenantIdentifierResolver: resolveCurrentTenantIdentifier: current=management
[exec-1] TRACE hibernate.internal.SessionImpl: Opened Session [dacf4cec-8efb-4dab-aac4-afa57755ac72] at timestamp: 1742726140602
[exec-1] INFO  xample.demo.ConnectionProvider: getConnection: tenantId=management, connection=HikariProxyConnection@319323152 wrapping org.postgresql.jdbc.PgConnection@36551e97
[exec-1] DEBUG ction.internal.TransactionImpl: On TransactionImpl creation, JpaCompliance#isJpaTransactionComplianceEnabled == false
[exec-1] DEBUG ction.internal.TransactionImpl: begin
[exec-1] DEBUG              org.hibernate.SQL: select t1_0.tenant_name,t1_0.schema_name from tenants t1_0 where t1_0.tenant_name=?
[exec-1] DEBUG ction.internal.TransactionImpl: committing
[exec-1] TRACE hibernate.internal.SessionImpl: SessionImpl#beforeTransactionCompletion()
[exec-1] TRACE hibernate.internal.SessionImpl: SessionImpl#afterTransactionCompletion(successful=true, delayed=false)
[exec-1] TRACE hibernate.internal.SessionImpl: Closing session [dacf4cec-8efb-4dab-aac4-afa57755ac72]
[exec-1] INFO  xample.demo.ConnectionProvider: releaseConnection: tenantId=management, connection=HikariProxyConnection@319323152 wrapping org.postgresql.jdbc.PgConnection@36551e97
[exec-1] INFO  om.example.demo.ProductService: tenants: Optional[Tenant(tenantName=TENANT 01, schemaName=tenant01)]
[exec-1] INFO  om.example.demo.ProductService: start to get product from tenant schema
[exec-1] INFO  com.example.demo.TenantContext: setCurrentTenant: tenantId=tenant01
[exec-1] INFO  .demo.TenantIdentifierResolver: resolveCurrentTenantIdentifier: current=tenant01
[exec-1] TRACE hibernate.internal.SessionImpl: Opened Session [4469aad4-6f26-4e50-8b9b-768f8ecfe7e8] at timestamp: 1742726140631
[exec-1] INFO  xample.demo.ConnectionProvider: getConnection: tenantId=tenant01, connection=HikariProxyConnection@1520898455 wrapping org.postgresql.jdbc.PgConnection@36551e97
[exec-1] DEBUG ction.internal.TransactionImpl: On TransactionImpl creation, JpaCompliance#isJpaTransactionComplianceEnabled == false
[exec-1] DEBUG ction.internal.TransactionImpl: begin
[exec-1] DEBUG              org.hibernate.SQL: select p1_0.id,p1_0.name from products p1_0
[exec-1] DEBUG ction.internal.TransactionImpl: committing
[exec-1] TRACE hibernate.internal.SessionImpl: SessionImpl#beforeTransactionCompletion()
[exec-1] TRACE hibernate.internal.SessionImpl: SessionImpl#afterTransactionCompletion(successful=true, delayed=false)
[exec-1] TRACE hibernate.internal.SessionImpl: Closing session [4469aad4-6f26-4e50-8b9b-768f8ecfe7e8]
[exec-1] INFO  xample.demo.ConnectionProvider: releaseConnection: tenantId=tenant01, connection=HikariProxyConnection@1520898455 wrapping org.postgresql.jdbc.PgConnection@36551e97
[exec-1] INFO  om.example.demo.ProductService: products: [Product(id=1, name=tenant01 product01)]

この設定は，ビューでのlazy loadingを実現するためのもののようで，デフォルトがtrueです． trueの場合，HibernateのSessionがリクエスト処理中に共有されるため，最初の例のようにコントローラのメソッドが開始する前にテナントIDの名前解決が行われるようです．

トランザクション管理をする場合の注意

サービスクラスのloadProducts(String)でトランザクション管理するために@Transactional loadProducts(String)とした場合，以下のようなエラーが発生しました．

[exec-1] INFO  rBase.[Tomcat].[localhost].[/]: Initializing Spring DispatcherServlet 'dispatcherServlet'
[exec-1] INFO  .web.servlet.DispatcherServlet: Initializing Servlet 'dispatcherServlet'
[exec-1] INFO  .web.servlet.DispatcherServlet: Completed initialization in 1 ms
[exec-1] INFO  m.example.demo.DemoApplication: /products: tenantId=TENANT 01
[exec-1] INFO  .demo.TenantIdentifierResolver: resolveCurrentTenantIdentifier: current=null
[exec-1] TRACE hibernate.internal.SessionImpl: Opened Session [83aaed2d-5743-45b1-a3ff-3939fb2fd479] at timestamp: 1742726303864
[exec-1] DEBUG ction.internal.TransactionImpl: On TransactionImpl creation, JpaCompliance#isJpaTransactionComplianceEnabled == false
[exec-1] DEBUG ction.internal.TransactionImpl: begin
[exec-1] INFO  xample.demo.ConnectionProvider: getConnection: tenantId=PUBLIC, connection=HikariProxyConnection@599589696 wrapping org.postgresql.jdbc.PgConnection@3dc39459
[exec-1] INFO  om.example.demo.ProductService: loadProducts: tenantId=TENANT 01
[exec-1] INFO  om.example.demo.ProductService: start to get tenant information
[exec-1] INFO  com.example.demo.TenantContext: setCurrentTenant: tenantId=management
[exec-1] DEBUG              org.hibernate.SQL: select t1_0.tenant_name,t1_0.schema_name from tenants t1_0 where t1_0.tenant_name=?
[exec-1] WARN  ne.jdbc.spi.SqlExceptionHelper: SQL Error: 0, SQLState: 42P01
[exec-1] ERROR ne.jdbc.spi.SqlExceptionHelper: ERROR: relation "tenants" does not exist
  Position: 47
[exec-1] INFO  ernal.DefaultLoadEventListener: HHH000327: Error performing load command
org.hibernate.exception.SQLGrammarException: JDBC exception executing SQL [select t1_0.tenant_name,t1_0.schema_name from tenants t1_0 where t1_0.tenant_name=?] [ERROR: relation "tenants" does not exist
  Position: 47] [n/a]
        at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:91)
        at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:58)

（中略）

Caused by: org.postgresql.util.PSQLException: ERROR: relation "tenants" does not exist
  Position: 47
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2733)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2420)

（中略）

[exec-1] DEBUG ction.internal.TransactionImpl: rolling back
[exec-1] TRACE hibernate.internal.SessionImpl: SessionImpl#afterTransactionCompletion(successful=false, delayed=false)
[exec-1] TRACE hibernate.internal.SessionImpl: Closing session [83aaed2d-5743-45b1-a3ff-3939fb2fd479]
[exec-1] INFO  xample.demo.ConnectionProvider: releaseConnection: tenantId=PUBLIC, connection=HikariProxyConnection@599589696 wrapping org.postgresql.jdbc.PgConnection@3dc39459
[exec-1] ERROR lhost].[/].[dispatcherServlet]: Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: org.springframework.dao.InvalidDataAccessResourceUsageException: JDBC exception executing SQL [select t1_0.tenant_name,t1_0.schema_name from tenants t1_0 where t1_0.tenant_name=?] [ERROR: relation "tenants" does not exist
  Position: 47] [n/a]; SQL [n/a]] with root cause
org.postgresql.util.PSQLException: ERROR: relation "tenants" does not exist
  Position: 47
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2733)
        at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2420)

（中略）

この場合，loadProducts(String)メソッドに入る前にテナントIDの名前解決が行われたため，最初の例と同様に，期待したスキーマに対するSQLクエリが発行されませんでした． トランザクション管理のためにSessionが先行して作られるため，このような動作になるようです．

トランザクション管理が必要な場合は，@Transactionalメソッドの外でスキーマの切り替えが必要です． 例えば，以下のようにすることでトランザクション管理が実現可能です．

コントローラ
@SpringBootApplication
@RestController
@Slf4j
public class DemoApplication {

  @Autowired
  private ProductService productService;

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

  @GetMapping("/products_transactional")
  public List<Product> productsTransactional(@RequestParam("tenantId") String tenantId) {
    log.info("/products_transactional: tenantId={}", tenantId);
    var schemaName = productService.loadSchemaName(tenantId);
    TenantContext.setCurrentTenant(schemaName);
    var products = productService.loadTenantProducts();
    return products;
  }

}
サービス
@Service
@Slf4j
class ProductService {

  @Autowired
  private TenantRepository tenantRepository;
  @Autowired
  private ProductRepository productRepository;

  public String loadSchemaName(String tenantId) {
    log.info("loadSchemaName: tenantId={}", tenantId);

    // get tenant information
    log.info("start to get tenant information");
    TenantContext.setCurrentTenant("management");
    var tenants = tenantRepository.findById(tenantId);
    log.info("tenants: {}", tenants);

    return tenants.get().getSchemaName();
  }

  @Transactional
  public List<Product> loadTenantProducts() {
    log.info("loadTenantProducts");
    var products = productRepository.findAll();
    log.info("product: {}", products);
    return products;
  }

}

@Transactionalは同一クラスからの内部呼び出しでは機能しないので，ここではコントローラ側でスキーマを切り替えていることに注意してください．

まとめ

Spring Boot (Hibernate) でマルチテナントDBを利用する場合，Sessionがどの単位（スコープ）で作られるかを意識し，スキーマの切り替え（CurrentTenantIdentifierResolver.resolveCurrentTenantIdentifier()の呼び出し）がSession作成前に行われるように注意する必要があります．

そのために，以下が必要でした．

  • spring.jpa.open-in-view=falseの設定を明示的に行う
  • @Transactionalを付けたメソッドを呼ぶ前にスキーマを切り替える
© 2025, ottijp