Spring Boot升级后SQL Server加密字段空值查询报错求助

问题背景 #

Spring Boot从2.1.7.RELEASE升级到2.7.10后，使用SQL Server Always Encrypted加密字段时出现异常：当查询中加密字段A（带@Nationalized注解、可空）为null时，执行existsByAAndBAndC查询失败，报错提示nvarchar加密字段与varchar参数类型不兼容；而A非null时查询正常，此时参数会被转为nvarchar。

错误日志：

The data types nvarchar(255) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'xxx', column_encryption_key_database_name = 'yyy') and varchar(1) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'xxx', column_encryption_key_database_name = 'yy') collation_name = 'zzz' are incompatible in the equal to operator.

解决建议 #

1. 强制JDBC发送Unicode字符串参数 #

在数据源URL中添加sendStringParametersAsUnicode=true参数，让SQL Server JDBC驱动默认将所有字符串参数（包括null值）以NVARCHAR类型发送，从根源避免类型不匹配：

spring.datasource.url=jdbc:sqlserver://your-db-host;databaseName=your-db;sendStringParametersAsUnicode=true

2. 自定义Repository查询，显式指定参数类型 #

放弃Spring Data JPA自动生成的查询，改用原生SQL或HQL显式处理null参数的类型：

• 原生SQL方式：

@Repository
public interface YourRepository extends JpaRepository<YourEntity, Long> {
    @Query(value = "SELECT EXISTS(SELECT 1 FROM your_table WHERE A = CAST(?1 AS NVARCHAR(255)) AND B = ?2 AND C = ?3)", nativeQuery = true)
    boolean existsByAAndBAndC(String a, String b, String c);
}

• HQL方式，配合Hibernate类型注解：

@Repository
public interface YourRepository extends JpaRepository<YourEntity, Long> {
    @Query("SELECT CASE WHEN COUNT(t) > 0 THEN true ELSE false END FROM YourEntity t WHERE t.a = :a AND t.b = :b AND t.c = :c")
    boolean existsByAAndBAndC(
        @Param("a") @Type(type = "org.hibernate.type.NVarcharType") String a,
        @Param("b") String b,
        @Param("c") String c
    );
}

3. 实体字段显式声明列定义 #

在加密字段A上，用@Column的columnDefinition属性明确指定数据库列类型，让Hibernate生成查询时严格遵循该类型：

@Entity
@Table(name = "your_table")
public class YourEntity {
    // 其他字段...
    @Nationalized
    @Column(name = "A", columnDefinition = "NVARCHAR(255) NULL ENCRYPTED WITH (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'xxx', column_encryption_key_database_name = 'yyy')")
    private String a;
    // getters/setters...
}

4. 自定义Hibernate类型处理空值 #

编写自定义Hibernate类型，确保null值也以NVARCHAR类型绑定到PreparedStatement：

public class CustomNVarcharType extends NVarcharType {
    @Override
    public void nullSafeSet(PreparedStatement st, Object value, int index, SharedSessionContractImplementor session) throws HibernateException, SQLException {
        if (value == null) {
            st.setNull(index, Types.NVARCHAR);
        } else {
            super.nullSafeSet(st, value, index, session);
        }
    }
}

然后在实体字段上引用该类型：

@Nationalized
@Type(type = "com.yourpackage.CustomNVarcharType")
private String a;

5. 调整Hibernate全局配置 #

在application.properties中添加全局配置，强制Hibernate优先使用国际化字符串类型：

hibernate.type.preferred_nationalized_java_type=java.lang.String
hibernate.connection.useUnicode=true
hibernate.connection.characterEncoding=utf8

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