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)