springJpa下Mysql多租户（分库）

不废话，直接上 参考spring-data-examples-main和百度

数据库DB

实体类

@Entity
public class Person {

   @Id
   @GeneratedValue
   private Long id;

   private String name;

   public Long getId() {
      return id;
   }

   public void setId(Long id) {
      this.id = id;
   }

   public String getName() {
      return name;
   }

   public void setName(String name) {
      this.name = name;
   }

   @Override
   public String toString() {
      return "Person{" + "id=" + id + ", name='" + name + ''' + '}';
   }
}

Dao

public interface PersonRepository extends JpaRepository<Person, Long> {
   
}

简易的租户标识返回器，一般是用拦截器设置到ThreadLocal中**

public class TenantIdentifierResolver {

  private static final ThreadLocal<String> currentTenant = ThreadLocal.withInitial(() -> "");

  public static void setCurrentTenant(String tenant) {
     currentTenant.set(tenant);
  }

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

配置文件

@Component
public class TenantRoutingDatasource extends AbstractRoutingDataSource {



   TenantRoutingDatasource() {
      setDefaultTargetDataSource(createEmbeddedDatabase("1"));
      HashMap<Object, Object> targetDataSources = new HashMap<>();
      targetDataSources.put("0", createEmbeddedDatabase("0"));
      targetDataSources.put("1", createEmbeddedDatabase("1"));
      setTargetDataSources(targetDataSources);
   }

   private DataSource createEmbeddedDatabase(String name) {

      return  DataSourceBuilder.create()
            .driverClassName("com.mysql.cj.jdbc.Driver")
            .url(name.equals("1")?"jdbc:mysql://ip:3306/test0?serverTimezone=UTC" :
                  "jdbc:mysql://ip:3306/test1?serverTimezone=UTC")
            .username("root")
            .password("ysf123456")
            .build();
   }


   @Override
   protected String determineCurrentLookupKey() {
      return TenantIdentifierResolver.getCurrentTenant();
   }


}

@Component
public class NoOpConnectionProvider implements MultiTenantConnectionProvider, HibernatePropertiesCustomizer {

   @Autowired DataSource dataSource;

   @Override
   public Connection getAnyConnection() throws SQLException {
      return dataSource.getConnection();
   }

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

   @Override
   public Connection getConnection(String schema) throws SQLException {
      return dataSource.getConnection();
   }

   @Override
   public void releaseConnection(String s, Connection connection) throws SQLException {
      connection.close();
   }

   @Override
   public boolean supportsAggressiveRelease() {
      return false;
   }

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


   @Override
   public <T> T unwrap(Class<T> aClass) {
      throw new UnsupportedOperationException("Can't unwrap this.");
   }

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

   }

配置文件

spring:
  main:
    allow-bean-definition-overriding: true
  datasource:
    url: jdbc:mysql://ip:3306/123456?serverTimezone=UTC
    driver-class-name: com.mysql.cj.jdbc.Driver
    password: 123456
    username: 123456
  jpa:
    hibernate:
      ddl-auto: update
    show-sql: true
    properties:
      hibernate:
        type:
          json_format_mapper:  org.hibernate.type.jackson.JacksonJsonFormatMapper
        format_sql: true

测试

@Test
void contextLoads() {
    add("mpp_default");
    TenantIdentifierResolver.setCurrentTenant("1");
    add("mpp_1");
    TenantIdentifierResolver.setCurrentTenant("0");
    add("mpp_0");

}
private void add(String name){
    final Person person = new Person();
    person.setName(name);
    personRepository.save(person);
}

结果