H2数据库集成测试中JPA原生查询检索CLOB存储JSON字段触发SQL语法异常求助

• 第一版查询WHERE v.v_collected_data.assigneeId = ?是Oracle针对JSON类型列的隐式属性访问语法。你的字段实际是CLOB存储的JSON字符串，这种写法在非Oracle数据库的解析逻辑里会被识别为“取v表的v_collected_data字段下的assigneeId子列”，直接触发列不存在的语法错误。
• 第二版使用的JSON_VALUE是Oracle 12c+的标准JSON函数，默认状态下H2没有开启对应函数的兼容映射，才会抛出函数不存在的异常。

方案1：开启H2的Oracle兼容模式 #

• 修改集成测试的H2数据库连接串，增加Oracle模式配置，开启内置JSON函数支持：
  jdbc:h2:mem:visit_test_db;MODE=Oracle;DB_CLOSE_DELAY=-1;CASE_INSENSITIVE_IDENTIFIERS=TRUE
• 保持Hibernate方言配置与生产环境一致，不要在测试环境单独切换为H2方言：

  spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.Oracle12cDialect
  

• H2 2.0及以上版本已原生支持JSON_VALUE语法，开启兼容模式后可直接复用生产环境的原生查询逻辑，无需修改Repository层代码。

方案2：按数据库类型动态匹配查询语句 #

• 利用Spring Data JPA的条件化查询机制，针对不同数据库环境编写对应语法的查询，业务逻辑层无需感知底层数据库差异：

  public interface VisitRepository extends JpaRepository<Visit, Long> {
  
      // Oracle生产环境查询
      @Query(nativeQuery = true, value = "SELECT * FROM TDED_VISITS v WHERE JSON_VALUE(V_COLLECTED_DATA, '$.assigneeId') = ?1")
      List<Visit> findByAssigneeIdOracle(String assigneeId);
  
      // H2测试环境查询，使用H2原生支持的JSON提取语法
      @Query(nativeQuery = true, value = "SELECT * FROM TDED_VISITS v WHERE JSON_EXTRACT(V_COLLECTED_DATA, '$.assigneeId') = ?1")
      List<Visit> findByAssigneeIdH2(String assigneeId);
  }
  

• 配合Spring Profile的Bean注入机制，在不同环境加载对应查询实现的Repository实例，无需硬编码适配分支。

方案3：测试层语法解耦 #

• 如果集成测试不需要覆盖JSON字段解析的查询逻辑，可在测试库的TDED_VISITS表增加冗余的ASSIGNEE_ID普通VARCHAR字段，测试时直接查询该冗余字段，规避跨库JSON语法的兼容成本。

• 禁止使用表别名.字段.属性的点号式JSON查询语法，该语法不属于SQL标准，仅在Oracle原生JSON列场景可用，切换数据库必然触发语法错误。
• 集成测试使用的H2版本不低于2.1.210，低版本H2对CLOB类型的JSON解析存在已知bug，会出现字段值匹配失败的问题。
• 确保CLOB字段存储的JSON格式完全合法，无多余转义字符，避免不同数据库解析逻辑差异导致的测试结果偏差。

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