Hibernate and postgresql after migration from Oracle

Question

I have problem with right set configuration of spring-boot, jpa and postgresql database.

All my queries hibernate launch without quotes and in lowercase. Postgresql dont accept this, and return error.

My application.yml file:

spring:
    datasource:
        url: jdbc:postgresql://127.0.0.1:5432/xxx
        username: xxx
        password: xxx
        driver-class-name: org.postgresql.Driver
        dialect: org.hibernate.dialect.PostgreSQL94Dialect
    jpa:
        show-sql: true
        naming-strategy: org.hibernate.cfg.EJB3NamingStrategy
        hibernate:
            ddl-auto: none
            default-schema: madmax
            dialect: org.hibernate.dialect.PostgreSQL94Dialect
        database-platform: org.hibernate.dialect.PostgreSQL94Dialect
    hadoop:
      config:
        fs.defaultFS: hdfs://192.168.56.104:54310/

Example of enitity:

@Entity
@Table(name="RecommendationItem")
public class RecommendationItem {

And preview of hibernate query log:

Hibernate: select recommenda0_.id as id1_2_ [...] from recommendation_item recommenda0_ where recommenda0_.user_id=?

And error:

SEVERE: Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet] with root cause
org.postgresql.util.PSQLException: ERROR: relation "recommendation_item" does not exist

It is possible to config this architecture without renaming all annotation in entities?

dunno what you are asking, log says relation "recommendation_item" does not exist, you shouldn't have an entity RecommendationItem, it is only a relation, which can be described with annotations.. if you don't want to use annotation, what do you have in mind? — Jaiwo99
– Jaiwo99, Commented May 9, 2017 at 13:56
Postgresql should not care about case on tables as default. Are you sure you are connection to the right database, and that you have a table called recommendation_item in the database? — MTilsted
– MTilsted, Commented May 9, 2017 at 13:58
When I take this hibernate query and run in postgresql i get this same error. All is correct when I add quotes to query and uppercase table name: (select recommenda0_."ID" from "RECOMMENDATION_ITEM" recommenda0_ ) — wukkie
– wukkie, Commented May 9, 2017 at 13:59

Laurenz Albe · Accepted Answer · 2017-05-09 13:59:37Z

1

I can think of two possibilities:

The configuration parameter search_path does not include the schema that contains recommendation_item, so it cannot be found.

Solution: add the schema to search_path.
You kept the table name in upper case when you migrated the table from Oracle, so it is called RECOMMENDATION_ITEM.

The problem here is that while Oracle folds unquoted names to upper case, as the SQL standard directs, while PostgreSQL folds them to lower case.

There are two solutions:
- Rename the tables to lower case names.
  I think that this is the better solution.
- Always use quoted parameters, like "RECOMMENDATION_ITEM".

answered May 9, 2017 at 13:59

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

wukkie Over a year ago

Thanks! It is a second option. I use scripts from stackoverflow.com/a/10087286/2847298 and everything is ok now :-)

Collectives™ on Stack Overflow

Hibernate and postgresql after migration from Oracle

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related