CODEWITHSUNDEEP
javapostgresqlhibernate
Varun Jain
Varun Jain

Reputation: 507

Hibernate + Postgres table and column name sensitive issue

I am using Spring Boot 2.1 + Hibernate 5.x + Postgres 11.x My Database connection string :-

url: jdbc:postgresql://localhost:5432/mydatabase?currentSchema=dbo

Postgres database have mixed case table name and column name. i have hibernate class as below (which have all the table and column name in lower cases):- 

@Entity
@Table(name = "products")
@Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
public class Products implements Serializable {
...
@Size(max = 80)
@Column(name = "barcode", length = 80)
private String barcode;

@NotNull
@Size(max = 80)
@Column(name = "name", length = 80, nullable = false)
private String name;

@Column(name = "hidden")
private Boolean hidden = false;
.....
}

And database table is having column like below (mixed case):-

enter image description here

When i select all the data using JPA repository method, hibernate is generating below query.

select products0_.id as id1_140_, products0_.barcode as barcode2_140_, 
products0_.creation_time as creation3_140_, products0_.hidden as hidden4_140_, 
from dbo.products products0_ limit 10

which cause error like below :- 

Caused by: org.postgresql.util.PSQLException: ERROR: relation "products" does not exist
  Position: 449
        at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2440)

Even if i use table name like below :-

@Table(name = "Products") // same name as i have in database

I am getting same error, because postgres need quote for mixed case name, and adding this change in each and every class is big pain.

if i use name like below it will work :- 

@Table(name = "\"Products\"")

but this is very painful solution and it will make my code depended on postgres database only.

is there any setting in hibernate or in postgres to make table and column name insensitive? any suggestion to solve this issue?

Upvotes: 6

Views: 7505

Answers (1)

Mani
Mani

Reputation: 111

Try changes in application.properties as below :

spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.PostgreSQL95Dialect
spring.jpa.hibernate.naming.implicit-strategy=org.hibernate.boot.model.naming.ImplicitNamingStrategyLegacyJpaImpl
spring.jpa.hibernate.naming.physical-strategy= org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
spring.jpa.properties.hibernate.globally_quoted_identifiers=true

BTW, in @Table add schema also. ie., @Table(name="table", schema="schemaname") Also, In @Column (name ="xx") not required.

This worked for me with Postgresql 10, Hibernate 5.x and Spring Boot 2.1.x

good luck

Upvotes: 10

Related Questions