Hello There!
Recently, i did a POC that involving 4 databases at the same time in one application. My application just do a simple CRUD in 4 databases which are MySQL, Oracle, Microsoft SQL Server and PostgreSQL. You can image that, one application can be use 4 databases as well.
1. THE PROBLEM
At this note that i’m writing, i created an application using Springboot and postgreSQL with a naming strategy. According to hibernate, part of the mapping of an object model to the relational database is mapping names from the object model to the corresponding database names. The configuration that i had was like :
spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
I did use above configuration when my application was connected using MySQL all went fine, smooth and no such error at all.
But not in postgreSQL. This is interesting part, when i ran the application that connected to postgreSQL, it appears like bellow :
[14:55:08] [ERROR] [org.apache.catalina.core.ContainerBase.[Tomcat].[localhost].[/].[dispatcherServlet]]: Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed: org.springframework.dao.InvalidDataAccessResourceUsageException: JDBC exception executing SQL [select d1_0.id,d1_0.Address,d1_0."Bank Account",d1_0."Born Date",d1_0.Country,d1_0.email,d1_0.firstname,d1_0.lastname,d1_0."Mother Name",d1_0.Phone,d1_0.Sex from dummy d1_0 order by d1_0.id desc offset ? rows fetch first ? rows only] [ERROR: column d1_0.address does not exist
Hint: Perhaps you meant to reference the column "d1_0.Address".
Position: 16] [n/a]; SQL [n/a]] with root cause
org.postgresql.util.PSQLException: ERROR: column d1_0.address does not existI have checked my entity class is something like below :
@Data
@Entity
@Table(name = "dummy")
public class Dummy {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id",nullable = false, unique = true, length = 30)
private Long id;
@Column(name = "firstname", nullable = true)
private String firstName;
@Column(name = "lastname", nullable = true)
private String lastName;
@Column(name = "email", nullable = true)
private String email;
@Column(name = "Born Date", nullable = true)
private String bornDate;
@Column(name = "Sex", nullable = true)
private String sex;
@Column(name = "Mother Name", nullable = true)
private String motherName;
@Column(name = "Address", nullable = true)
private String address;
@Column(name = "Country", nullable = true)
private String country;
@Column(name = "Phone", nullable = true)
private String phone;
@Column(name = "Bank Account", nullable = true)
private String bankAccount;
@JsonProperty(value="isInsert")
@Transient
private boolean isInsert;
}
Apparently there is nothing wrong in my entity class, all database’s field name and json’s field name has been completely same. Either the letter for uppercase and lowercase is identical comparing between class’s entity and json’s field.
An entity class is a class that represents a store of information in a system, or a table in a database.
2. SOLUTION
So, i have updated the configuration of my application.properties which before is something like:
spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImplInto something like:
spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
spring.jpa.properties.hibernate.globally_quoted_identifiers=trueI have added “spring.jpa.properties.hibernate.globally_quoted_identifiers” into “true” value. Which based on the hibernate documentation says “When enabled, all database identifiers are quoted.”.
After added above configuration, the application completely run! Now, all the error gone. We are now happy! 😘
However, if you’d like to view my other note related to other database like Oracle, you can visit this.
That’s all folks! Happy coding! 🥰😍
Note : hibernate 6.4.4
Leave a Reply