基于MySQL与Oracle，如何用Hibernate @ColumnTransformer实现分库加解密？

方案一：扩展Hibernate方言，统一加密解密函数名 #

核心思路是给不同数据库的方言注册同名自定义函数，映射到各自原生的加密解密逻辑，让实体类的@ColumnTransformer可以用统一调用，无需关心底层数据库类型。

1. 自定义MySQL方言 #

public class CustomMySQLDialect extends MySQL8Dialect {
    public CustomMySQLDialect() {
        super();
        // 注册加密函数，映射到MySQL原生AES_ENCRYPT
        registerFunction("encrypt", new StandardSQLFunction("AES_ENCRYPT", StandardBasicTypes.BINARY));
        // 注册解密函数，映射到MySQL原生AES_DECRYPT
        registerFunction("decrypt", new StandardSQLFunction("AES_DECRYPT", StandardBasicTypes.STRING));
    }
}

2. 自定义Oracle方言 #

Oracle需依赖DBMS_CRYPTO包，提前给数据库用户授权：GRANT EXECUTE ON SYS.DBMS_CRYPTO TO your_user;

public class CustomOracleDialect extends OracleDialect {
    public CustomOracleDialect() {
        super();
        // 注册加密函数，封装Oracle的AES256加密逻辑
        registerFunction("encrypt", new StandardSQLFunction("DBMS_CRYPTO.ENCRYPT", StandardBasicTypes.RAW) {
            @Override
            public String render(Type firstArgType, List<?> arguments, SessionFactoryImplementor factory) {
                // 常量2对应DBMS_CRYPTO.AES256_ENCRYPT，统一用UTF-8字符集转换
                return "DBMS_CRYPTO.ENCRYPT(UTL_I18N.STRING_TO_RAW(" + arguments.get(0) + ", 'AL32UTF8'), 2, UTL_I18N.STRING_TO_RAW(" + arguments.get(1) + ", 'AL32UTF8'))";
            }
        });

        // 注册解密函数，封装Oracle的AES256解密逻辑
        registerFunction("decrypt", new StandardSQLFunction("DBMS_CRYPTO.DECRYPT", StandardBasicTypes.STRING) {
            @Override
            public String render(Type firstArgType, List<?> arguments, SessionFactoryImplementor factory) {
                return "UTL_I18N.RAW_TO_CHAR(DBMS_CRYPTO.DECRYPT(" + arguments.get(0) + ", 2, UTL_I18N.STRING_TO_RAW(" + arguments.get(1) + ", 'AL32UTF8')), 'AL32UTF8')";
            }
        });
    }
}

3. 实体类中使用统一函数 #

@Entity
@Table(name = "user")
public class User {
    @Id
    private Long id;

    // 写入调用encrypt，读取调用decrypt，密钥通过会话参数传递
    @ColumnTransformer(
        write = "encrypt(?, :encryptKey)",
        read = "decrypt(encrypted_data, :encryptKey)"
    )
    // MySQL用VARBINARY，Oracle用RAW(256)，根据加密长度调整
    @Column(name = "encrypted_data", columnDefinition = "VARBINARY(256)")
    private String sensitiveData;

    // getter/setter省略
}

4. 配置动态切换方言 #

通过配置文件根据数据库类型指定对应方言：

spring:
  jpa:
    properties:
      hibernate:
        dialect: ${db.dialect}
        encrypt.key: ${ENCRYPT_KEY:your_default_key}

# 环境变量切换：MySQL填com.example.CustomMySQLDialect，Oracle填com.example.CustomOracleDialect
db:
  dialect: com.example.CustomMySQLDialect

5. 绑定加密密钥到会话 #

用Hibernate拦截器把密钥注入会话参数，供@ColumnTransformer使用：

public class EncryptionKeyInterceptor extends EmptyInterceptor {
    private final String encryptKey;

    public EncryptionKeyInterceptor(String encryptKey) {
        this.encryptKey = encryptKey;
    }

    @Override
    public void beforeTransactionCompletion(Transaction tx) {
        tx.getSession().setParameter("encryptKey", encryptKey);
    }
}

Spring配置中注册拦截器：

@Configuration
public class HibernateConfig {
    @Value("${spring.jpa.properties.hibernate.encrypt.key}")
    private String encryptKey;

    @Bean
    public HibernatePropertiesCustomizer hibernatePropertiesCustomizer() {
        return props -> props.put("hibernate.session_factory.interceptor", new EncryptionKeyInterceptor(encryptKey));
    }
}

方案二：自定义Hibernate类型封装加密解密逻辑 #

如果觉得@ColumnTransformer不够灵活，可自定义Hibernate类型，把加密解密逻辑完全封装，实体类只需标注@Type即可。

1. 自定义加密字符串类型 #

public class EncryptedStringType extends AbstractSingleColumnStandardBasicType<String> {
    private static final String ENCRYPT_KEY_PROPERTY = "hibernate.encrypt.key";

    public EncryptedStringType() {
        super(VarcharTypeDescriptor.INSTANCE, StringTypeDescriptor.INSTANCE);
    }

    @Override
    public String getName() {
        return "encrypted_string";
    }

    @Override
    public String[] getRegistrationKeys() {
        return new String[]{getName(), String.class.getName()};
    }

    // 读取结果集时解密
    @Override
    public String nullSafeGet(ResultSet rs, String[] names, SharedSessionContractImplementor session, Object owner) throws SQLException {
        Dialect dialect = session.getJdbcServices().getDialect();
        byte[] encrypted = rs.getBytes(names[0]);
        if (encrypted == null) return null;

        String key = getEncryptKey(session.getFactory());
        if (dialect instanceof MySQLDialect) {
            return CipherUtils.decryptAES(encrypted, key);
        } else if (dialect instanceof OracleDialect) {
            return CipherUtils.decryptAESOracle(encrypted, key);
        }
        return new String(encrypted);
    }

    // 写入数据库时加密
    @Override
    public void nullSafeSet(PreparedStatement st, String value, int index, SharedSessionContractImplementor session) throws SQLException {
        if (value == null) {
            st.setNull(index, getSqlType());
            return;
        }

        Dialect dialect = session.getJdbcServices().getDialect();
        String key = getEncryptKey(session.getFactory());
        byte[] encrypted;

        if (dialect instanceof MySQLDialect) {
            encrypted = CipherUtils.encryptAES(value, key);
        } else if (dialect instanceof OracleDialect) {
            encrypted = CipherUtils.encryptAESOracle(value, key);
        } else {
            encrypted = value.getBytes(StandardCharsets.UTF_8);
        }
        st.setBytes(index, encrypted);
    }

    // 从Hibernate配置中获取密钥
    private String getEncryptKey(SessionFactoryImplementor factory) {
        return (String) factory.getProperties().get(ENCRYPT_KEY_PROPERTY);
    }
}

2. 注册自定义类型 #

spring:
  jpa:
    properties:
      hibernate:
        encrypt.key: ${ENCRYPT_KEY:your_default_key}
        type:
          registration:
            key: encrypted_string
            class: com.example.EncryptedStringType

3. 实体类中使用 #

@Entity
@Table(name = "user")
public class User {
    @Id
    private Long id;

    @Type(type = "encrypted_string")
    @Column(name = "encrypted_data", columnDefinition = "VARBINARY(256)")
    private String sensitiveData;

    // getter/setter省略
}

注意事项 #

• 数据库列类型：MySQL用VARBINARY/BLOB，Oracle用RAW/BLOB，根据加密后的数据长度调整。
• 密钥管理：禁止硬编码密钥，建议用环境变量、配置中心或专业密钥管理服务存储。
• Oracle权限：必须给数据库用户授予DBMS_CRYPTO执行权限，否则会抛出权限不足异常。
• 字符集一致性：加密解密统一使用UTF-8字符集，避免乱码问题。

内容的提问来源于stack exchange，提问作者Adithya R