使用Java Spring/JPA连接Snowflake时遇列别名未找到错误求助

错误详情 #

org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; 

SQL [select testobject0_.ID as id1_0_, testobject0_.NAME as name2_0_ from MY_DBO.PUBLIC.test_object testobject0_]; 

nested exception is org.hibernate.exception.SQLGrammarException: could not execute query
...
Caused by: java.sql.SQLException: Column not found: id1_0_

    at net.snowflake.client.jdbc.SnowflakeBaseResultSet.findColumn(SnowflakeBaseResultSet.java:295)

    at net.snowflake.client.jdbc.SnowflakeBaseResultSet.getLong(SnowflakeBaseResultSet.java:177)

    at org.hibernate.type.descriptor.sql.BigIntTypeDescriptor$2.doExtract(BigIntTypeDescriptor.java:63)

相关代码 #

实体类TestObject #

@Entity
@Table(schema = "PUBLIC", catalog = "MY_DBO", name = "test_object")
public class TestObject {
 
  @Id
  @GeneratedValue(generator = "MILLIS")
  @GenericGenerator(
      name = "MILLIS",
      strategy = "com.snowflakes.demo.CustomIdentifierGenerator"
  )
  @Column(name = "ID")
  public Long id;
   
  @Column(name = "NAME")
  public String name;
}

JPA Repository接口 #

@Repository
public interface TestObjectRepository extends PagingAndSortingRepository<TestObject, Long>
{}

控制器方法 #

@GetMapping(value = "", produces = MediaType.APPLICATION_JSON_VALUE)
public ResponseEntity<List<TestObject>> list(Pageable Pageable) {
    List<TestObject> lst = (List<TestObject>) testObjectRepository.findAll();
    return ResponseEntity.ok().body(lst);
}

解决方案 #

该问题核心是Snowflake JDBC驱动与Hibernate生成的小写别名存在大小写匹配冲突，以下是几种可行解决方式：

• 强制Hibernate使用带引号的标识符：在配置文件中添加参数，让Hibernate对所有标识符（含别名）添加引号，避免大小写差异问题：

  # application.properties
  hibernate.globally_quoted_identifiers=true
  

  或YAML格式：

  # application.yml
  hibernate:
    globally_quoted_identifiers: true
  

• 修改Snowflake JDBC连接参数：在JDBC URL中添加CLIENT_RESULT_COLUMN_CASE_INSENSITIVE=true，让客户端忽略列名大小写：

  jdbc:snowflake://<account>.snowflakecomputing.com/?db=<database>&schema=<schema>&warehouse=<warehouse>&CLIENT_RESULT_COLUMN_CASE_INSENSITIVE=true
  

• 检查自定义ID生成器：确认CustomIdentifierGenerator是否正确处理ID字段的大小写逻辑，确保插入数据时对应到大写的ID列。

• 升级Snowflake JDBC驱动：部分旧版本驱动存在大小写处理bug，升级至最新稳定版可解决此类兼容问题。

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