Has anybody faced an wierd error (column alias not found error) using Java Spring/JPA Framework that-CodePudding

Error Message:

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)

Entity class:

@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 class:

@Repository

public interface TestObjectRepository extends PagingAndSortingRepository<TestObject, Long>

{}

Controller class/method:

@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)

CodePudding user response：

I was facing the same problem when trying to adapt this example: Spring JPA Snowflake

No matter what i tried i was getting the same problem. Then i saw that example was using:

<snowflake.jdbc.version>3.6.8</snowflake.jdbc.version>

By updating to latest available at the moment problem was solved:

<snowflake.jdbc.version>3.13.23</snowflake.jdbc.version>

CodePudding user response：

This is a driver bug and the latest version is affected. The root of it is in SnowflakeBaseResultSet class:

@Override
  public int findColumn(String columnLabel) throws SQLException {
    ...
    int columnIndex = resultSetMetaData.getColumnIndex(columnLabel);
    ...
  }

so, it looks for column by name, BUT the names in result set are upper case, while the name for lookup is lower-case. Solution - tune hibernate to lookup in upper-case (but seems no easy way)