CODEWITHSUNDEEP
springpostgresqlhibernatejpa
wukkie
wukkie

Reputation: 127

Hibernate and postgresql after migration from Oracle

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?

Upvotes: 0

Views: 1579

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246053

I can think of two possibilities:

  1. 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.

  2. 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".

Upvotes: 1

Related Questions