Spring Boot集成SQL Server列级加密遇数值类型冲突问题求助

在Spring Boot应用集成SQL Server 2019列级加密时，仅decimal/numeric类型出现操作数类型冲突错误，varchar或整数类型正常。具体情况如下：

实体类定义 #

@Entity
@Table(name = "TRANSACTIONS")
public class Transaction {

    @Id
    @Column(name = "TRANSACTION_ID")
    private String id;

    @Column(name = "AMOUNT", nullable = false)
    private BigDecimal amount;

    @Column(name = "DESCRIPTION", nullable = false)
    private String description;

    @Column(name = "INSERT_TIMESTAMP")
    private LocalDate insertTimestamp;
    // getter、setter省略
}

JPA仓库 #

@Repository
public interface TransactionRepository extends JpaRepository<Transaction, String> { }

SQL Server表结构 #

CREATE TABLE [dbo].[TRANSACTIONS](
    [transaction_id] [nvarchar](64) NOT NULL,
    [amount] [numeric](16,2) ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL,
    [insert_timestamp] [date] NULL,
    [description] [varchar](1024) COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = [CEK1], ENCRYPTION_TYPE = Deterministic, ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256') NOT NULL,
 CONSTRAINT [TRANSACTIONS_PK] PRIMARY KEY CLUSTERED 
(
    [transaction_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

接口与请求体 #

接口定义：

@PostMapping
public Transaction saveTransaction(@RequestBody Transaction request){
    // 保存逻辑省略
}

Postman请求体：

{
    "amount":120.0,
    "description":"TV"
}

错误信息 #

首次错误：

com.microsoft.sqlserver.jdbc.SQLServerException: Operand type clash: decimal(18,1) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK1', column_encryption_key_database_name = 'BANK') is incompatible with numeric(16,2) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK1', column_encryption_key_database_name = 'BANK')

修改数据库amount类型为decimal(18,1)后，错误变为：

com.microsoft.sqlserver.jdbc.SQLServerException: Operand type clash: numeric(4,1) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK1', column_encryption_key_database_name = 'BANK') is incompatible with decimal(18,1) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'CEK1', column_encryption_key_database_name = 'BANK')

使用Double+float组合可正常工作，但需保留numeric(16,2)类型。

1. 明确实体字段的精度与小数位映射 #

在实体类的amount字段上，通过@Column注解指定与数据库一致的精度（precision）和小数位（scale），确保JPA生成的SQL参数类型与加密列完全匹配：

@Column(name = "AMOUNT", nullable = false, precision = 16, scale = 2)
private BigDecimal amount;

2. 控制Jackson序列化的BigDecimal精度 #

请求体中的120.0会被Jackson默认序列化为精度18、小数位1的BigDecimal，与数据库的numeric(16,2)不匹配。可通过两种方式处理：

方式一：全局配置 #

在Spring Boot配置类中添加Jackson全局配置，强制保留指定小数位：

@Configuration
public class JacksonConfig {
    @Bean
    public ObjectMapper objectMapper() {
        ObjectMapper mapper = new ObjectMapper();
        // 强制将浮点型转为BigDecimal，避免精度丢失
        mapper.configure(DeserializationFeature.USE_BIG_DECIMAL_FOR_FLOATS, true);
        mapper.setNodeFactory(JsonNodeFactory.withExactBigDecimals(true));
        
        // 序列化时保留2位小数
        SimpleModule module = new SimpleModule();
        module.addSerializer(BigDecimal.class, new JsonSerializer<BigDecimal>() {
            @Override
            public void serialize(BigDecimal value, JsonGenerator gen, SerializerProvider serializers) throws IOException {
                gen.writeNumber(value.setScale(2, RoundingMode.HALF_UP));
            }
        });
        mapper.registerModule(module);
        return mapper;
    }
}

方式二：字段级配置+字符串请求体 #

在实体类字段上添加Jackson注解，同时请求体中将数值以字符串形式传入：

@JsonSerialize(using = ToStringSerializer.class)
@JsonDeserialize(using = BigDecimalDeserializer.class)
@Column(name = "AMOUNT", nullable = false, precision = 16, scale = 2)
private BigDecimal amount;

修改后的请求体：

{
    "amount":"120.00",
    "description":"TV"
}

3. 确保JDBC连接支持列级加密 #

在application.properties/application.yml中，确保SQL Server JDBC URL包含列级加密启用参数：

spring.datasource.url=jdbc:sqlserver://<your-server>:1433;databaseName=BANK;columnEncryptionSetting=Enabled;encrypt=true;trustServerCertificate=true
spring.datasource.driver-class-name=com.microsoft.sqlserver.jdbc.SQLServerDriver

其中columnEncryptionSetting=Enabled是启用列级加密的关键参数，必须添加。

4. 手动处理请求参数精度 #

在接口层手动将请求中的数值转换为指定精度的BigDecimal后再存入数据库：

@PostMapping
public Transaction saveTransaction(@RequestBody Transaction request){
    // 将amount转换为16位精度、2位小数的BigDecimal
    BigDecimal formattedAmount = request.getAmount().setScale(2, RoundingMode.HALF_UP);
    request.setAmount(formattedAmount);
    return transactionRepository.save(request);
}

验证步骤 #

1. 恢复数据库表的amount字段为numeric(16,2)
2. 应用上述配置后，重新发送请求测试
3. 检查数据库中是否成功插入加密的数值

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