Spring Boot+EclipseLink按租户分Schema多租户：切换与自动建表问题

一、根据请求动态切换租户Schema #

1. 实体类多租户配置 #

确保实体类（或统一基类）正确配置EclipseLink多租户注解，指定按Schema隔离：

import org.eclipse.persistence.annotations.Multitenant;
import org.eclipse.persistence.annotations.MultitenantType;
import org.eclipse.persistence.annotations.TenantDiscriminatorContextProperty;

@Entity
@Multitenant(type = MultitenantType.SCHEMA)
// 指定传递租户Schema名称的上下文属性
@TenantDiscriminatorContextProperty("eclipselink.tenant-id")
public class User {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String username;
    // 其他字段、getter/setter
}

2. 请求拦截器绑定租户上下文 #

实现Spring MVC拦截器，从请求中提取租户标识（示例用请求头X-Tenant-Id），绑定到EclipseLink会话：

import org.eclipse.persistence.jpa.JpaHelper;
import org.eclipse.persistence.sessions.Session;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.servlet.HandlerInterceptor;
import javax.persistence.EntityManagerFactory;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

@Component
public class TenantSchemaInterceptor implements HandlerInterceptor {

    @Autowired
    private EntityManagerFactory entityManagerFactory;

    @Override
    public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler) throws Exception {
        String tenantId = request.getHeader("X-Tenant-Id");
        if (tenantId != null && !tenantId.isBlank()) {
            // 获取EclipseLink会话并设置租户Schema属性
            Session session = JpaHelper.getEntityManagerFactory(entityManagerFactory).getActiveSession();
            session.setProperty("eclipselink.tenant-id", tenantId);
        }
        return true;
    }
}

3. 注册拦截器到Spring MVC #

将拦截器配置到Spring MVC，确保所有请求经过租户处理：

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Configuration;
import org.springframework.web.servlet.config.annotation.InterceptorRegistry;
import org.springframework.web.servlet.config.annotation.WebMvcConfigurer;

@Configuration
public class WebMvcConfig implements WebMvcConfigurer {

    @Autowired
    private TenantSchemaInterceptor tenantSchemaInterceptor;

    @Override
    public void addInterceptors(InterceptorRegistry registry) {
        registry.addInterceptor(tenantSchemaInterceptor)
                .addPathPatterns("/**");
    }
}

二、自动创建新租户的Schema及数据表 #

1. 租户Schema创建服务 #

编写服务类，检测租户Schema是否存在，不存在则创建并生成数据表：

import org.eclipse.persistence.jpa.JpaHelper;
import org.eclipse.persistence.sessions.Session;
import org.eclipse.persistence.sessions.DatabaseLogin;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import javax.persistence.EntityManagerFactory;

@Service
public class TenantManagementService {

    @Autowired
    private EntityManagerFactory entityManagerFactory;

    public void createTenantSchema(String tenantId) {
        Session rootSession = JpaHelper.getEntityManagerFactory(entityManagerFactory).getSession();
        DatabaseLogin login = (DatabaseLogin) rootSession.getDatasourceLogin();

        // 切换到系统默认Schema（如PostgreSQL的public、MySQL的默认库）执行创建操作
        login.setProperty("eclipselink.tenant-id", "public");
        Session systemSession = rootSession.getEntityManagerFactory().createSession(login);

        try {
            // 检查目标Schema是否已存在
            boolean schemaExists = systemSession.getPlatform().supportsSchemas()
                    && systemSession.getAccessor().doesSchemaExist(tenantId);

            if (!schemaExists) {
                // 创建新Schema
                systemSession.getAccessor().createSchema(tenantId);

                // 切换到新Schema，生成数据表结构
                login.setProperty("eclipselink.tenant-id", tenantId);
                Session tenantSession = rootSession.getEntityManagerFactory().createSession(login);
                try {
                    // 根据实体类生成DDL并执行
                    tenantSession.getSchemaManager().createDefaultTables(false);
                } finally {
                    tenantSession.close();
                }
            }
        } finally {
            systemSession.close();
        }
    }
}

2. 触发自动创建逻辑 #

修改拦截器，在设置租户上下文前先检查并创建租户Schema：

// 更新TenantSchemaInterceptor的preHandle方法
@Override
public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler) throws Exception {
    String tenantId = request.getHeader("X-Tenant-Id");
    if (tenantId != null && !tenantId.isBlank()) {
        // 通过Spring上下文获取租户服务
        TenantManagementService tenantService = 
            WebApplicationContextUtils.getWebApplicationContext(request.getServletContext())
                .getBean(TenantManagementService.class);
        // 首次访问时自动创建Schema
        tenantService.createTenantSchema(tenantId);

        // 设置租户上下文
        Session session = JpaHelper.getEntityManagerFactory(entityManagerFactory).getActiveSession();
        session.setProperty("eclipselink.tenant-id", tenantId);
    }
    return true;
}

3. 基础配置要求 #

• 数据库权限：确保应用连接数据库的用户拥有CREATE SCHEMA（MySQL为CREATE DATABASE）权限。
• EclipseLink DDL配置：在application.properties中开启自动DDL生成：

spring.jpa.eclipselink.ddl-generation=create-or-extend-tables
spring.jpa.eclipselink.ddl-generation.output-mode=database

内容的提问来源于stack exchange，提问作者Suhail Ahmed