[FIXED] snowflake returning uppercase fields resultset that causes issues jpql mapping jpa/hibernate

Issue

I have a project to do integration with snowflake database using ORM like JPA/Hibernate but for the resultset from snowflakes always returns fields in UPPERCASE which conflicts with JPQL default behevior.

Example below is a select query using JPQL as you can see all fields are in lowercase

select podioitem0_.ID as id1_0_0_, podioitem0_.JSON as json2_0_0_ from INLIFE_MARK.PUBLIC.podio_item podioitem0_ where podioitem0_.ID=?

The Resultset from snowflake returns Uppercase columns

Given the samples above I get this error

o.h.engine.jdbc.spi.SqlExceptionHelper   : Column not found: json2_0_0_

Its because when trying to map json2_0_0_ column from resultset the mapper cant find it because the columns from resultset are on uppercase.

So Question, is there a way to tell JPQL or jpa/hibernate to generate the JPQL query in Uppercase? at least for the column names so I hope it would look like this?

select podioitem0_.ID as ID1_0_0_, podioitem0_.JSON as JSON2_0_0_ from INLIFE_MARK.PUBLIC.podio_item podioitem0_ where podioitem0_.ID=?

Additional details below

properties

spring.jpa.show-sql=true
spring.jpa.database=SQL_SERVER
spring.jpa.hibernate.ddl-auto=none
spring.jpa.hibernate.naming.implicit-                
strategy=org.hibernate.boot.model.naming.ImplicitNamingStrategyJpaCompliantImpl
spring.jpa.hibernate.naming.physical-    
strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.SQLServerDialect

Sample Entity Class

@ToString
@Entity
@Table(schema = "PUBLIC", catalog = "INLIFE_MARK", name = "podio_item")
public class PodioItem {
    @Id
    @Column(name = "id")
    public Long id;

    @Column(name = "json", columnDefinition="varbinary")
    public byte[] json;
}

I am using CrudRepository to save and find objects

@Repository
public interface PodioItemRepository extends        
           CrudRepository<PodioItem, Long> {
}

I was hoping maybe somekind of property as a solution but any suggestion is welcome.

Solution

Please see: How to add quotes to aliases in generated sql via Hibernate?

Just add &CLIENT_RESULT_COLUMN_CASE_INSENSITIVE=true to your snowflake connection string.

Answered By - Katrin Pfisterer
Answer Checked By - Dawn Plyler (JavaFixing Volunteer)