H2数据库兼容@Where注解条件配置问题求助

开发环境使用MySQL，集成测试使用H2内存数据库，相关配置及代码如下：

测试环境配置（src\test\resources\application.yaml） #

spring:
  datasource:
    primary:
      url: jdbc:h2:mem:app;MODE=MySQL;
      driver-class-name: org.h2.Driver
      username: __username
      password: __password
  jpa:
    defer-datasource-initialization: true
    hibernate:
      ddl-auto: create-drop
      naming:
        implicit-strategy: org.hibernate.boot.model.naming.ImplicitNamingStrategyLegacyJpaImpl
        physical-strategy: org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
    show-sql: true
    properties:
      hibernate:
        format_sql: true
        globally_quoted_identifiers: true
        globally_quoted_identifiers_skip_column_definitions: true

业务代码（AggregationService.kt） #

fun getAggregations(widgetFieldContentId: Int?): ResponseEntity<ResponseGetAggregations> {
    val aggregation = widgetFieldContentId?.let {
        widgetFieldContentRepository findOutById it
    }?.aggregation
    val data = Aggregation
        .values()
        .filter { aggregation == null || (it.value and aggregation) != ZERO }
        .map(mapper::toDTO)
    return responseOK(data)
}

集成测试代码（AggregationServiceIntegrationTest.kt） #

@Test
@DisplayName("AggregationService_getAggregationsWithWidgetFieldContentId_shouldRequestToDatabase_returnsAvailableAggregationsForWidgetFieldContent")
fun getAggregationsWitWidgetFieldContentId() {
    // Arrange
    val widgetFieldContentId = 1

    // Act & Assert
    val response = assertDoesNotThrow { service.getAggregations(widgetFieldContentId) }

    // Assert
    assertAll(
        { assertEquals(HttpStatus.OK, response.statusCode) },
        { assertNotNull(response.body?.data) },
        { response.body?.success?.let(::assertTrue) },
        { response.body?.data?.isNotEmpty()?.let(::assertTrue) }
    )
}

报错信息 #

表创建无异常，但调用getAggregationsWitWidgetFieldContentId方法时，@Where注解的enabled=true条件触发SQL查询，抛出异常：

Caused by: org.h2.jdbc.JdbcSQLSyntaxErrorException: Column "WIDGETFIEL0_.ENABLED" not found; SQL statement:
select widgetfiel0_."id" as id1_8_0_, widgetfiel0_."auditor_id" as auditor11_8_0_, widgetfiel0_."enabled" as enabled2_8_0_, widgetfiel0_."aggregation" as aggregat3_8_0_, widgetfiel0_."category_id" as categor12_8_0_, widgetfiel0_."filter_id" as filter_13_8_0_, widgetfiel0_."filterCondition" as filterco4_8_0_, widgetfiel0_."filterType" as filterty5_8_0_, widgetfiel0_."filterValue" as filterva6_8_0_, widgetfiel0_."name" as name7_8_0_, widgetfiel0_."outValue" as outvalue8_8_0_, widgetfiel0_."selectValue" as selectva9_8_0_, widgetfiel0_."sortValue" as sortval10_8_0_, widgetfiel1_."id" as id1_9_1_, widgetfiel1_."auditor_id" as auditor_8_9_1_, widgetfiel1_."enabled" as enabled2_9_1_, widgetfiel1_."max" as max3_9_1_, widgetfiel1_."measurement" as measurem4_9_1_, widgetfiel1_."min" as min5_9_1_, widgetfiel1_."multipleOption" as multiple6_9_1_, widgetfiel1_."optionUrl" as optionur7_9_1_ from "widget_field_content" widgetfiel0_ left outer join "widget_field_content_filter" widgetfiel1_ on widgetfiel0_."filter_id"=widgetfiel1_."id" where widgetfiel0_."id"=? and ( widgetfiel0_.enabled=true) [42122-214]
...（省略后续堆栈信息）

实体类配置 #

@Entity
@Table(name = "widget_field_content", uniqueConstraints = [UniqueConstraint(columnNames = ["name", "category_id"])])
@Where(clause = "enabled=true")
@SQLDelete(sql = "UPDATE widget_field_content SET enabled=FALSE WHERE id=? AND enabled")
class WidgetFieldContentEntity(

    @Column(
        nullable = false,
        columnDefinition = "JSON"
    )
    val name: String,
...

尝试参照表创建的命名策略配置条件，但问题未解决，需排查原因。

问题根源在于开启了hibernate.globally_quoted_identifiers=true，Hibernate会给生成SQL中的所有表名、列名添加引号，但@Where和@SQLDelete注解里的是原生SQL片段，Hibernate不会自动给这些片段里的列名加引号，导致H2数据库无法识别未加引号的enabled列（H2在区分大小写模式下，未加引号的标识符会转为大写，而实际生成的列名是带引号的原大小写）。

有两种解决方式：

1. 修改注解中的SQL片段，给列名添加引号
   调整@Where和@SQLDelete的内容，让列名和Hibernate生成的格式一致：

   @Where(clause = "\"enabled\"=true")
   @SQLDelete(sql = "UPDATE widget_field_content SET \"enabled\"=FALSE WHERE id=? AND \"enabled\"")
   

2. 针对测试环境关闭全局引号配置
   在测试的application.yaml中修改Hibernate配置：

   spring:
     jpa:
       properties:
         hibernate:
           globally_quoted_identifiers: false
   

   这种方式适合不需要严格区分标识符大小写的测试场景，无需修改实体类注解。

优先推荐第一种方法，能保证SQL片段与Hibernate生成逻辑的一致性，避免H2兼容模式下的其他潜在问题。

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