JPA Uppercase table names

Question

I have a table in Postgresql:

CREATE TABLE "UTILISATEUR"(
 
 "IdUtilisateur" serial NOT NULL,
 "Nom" character varying(50),
 "Prenom" character varying(50),
 "Profil" character varying(50),
 "Pseudo" character varying(20),
 "IdSite" integer DEFAULT 0,
 "Password" character varying(1024),
  id_role integer,
  )

and I am trying Map on this Table So I used @Table JPA annotation (see below). This is my application.propreties:

spring.datasource.url = jdbc:postgresql://localhost/baseecu
spring.datasource.username = postgres
spring.datasource.password =root
spring.datasource.driverClassName=org.postgresql.Driver
spring.jpa.database = MYSQL 
spring.jpa.show-sql = true
spring.jpa.hibernate.ddl-auto=update 
spring.jpa.hibernate.naming-strategy = org.hibernate.cfg.ImprovedNamingStrategy
spring.jpa.properties.hibernate.dialect = org.hibernate.dialect.MySQL5InnoDBDialect

and finaly this is my entity class:

@Entity
@Table(name="UTILISATEUR")
public class Utilisateur   {
@Id
@GeneratedValue(strategy=GenerationType.AUTO) 
@Column(name="IdUtilisateur")
public Long id ;
public String Nom ; 
public String Prenom ; 
public String Profil ; 
public String Pseudo ; 


public String Password ;
@ManyToOne
@JoinColumn(name="id_role")
public Role role ;
public Long getId() {
    return id;

If I have it like this @Table(name="UTILISATEUR") I get msg from PostgreSQL

ERREUR: la relation « utilisateur » n'existe pas

When I try to escape quote @Table(name="\"UTILISATEUR\"")

ERROR: syntax error at or near "`"

I tried the responses from this question but it doesn't work

Spring boot JPA insert in TABLE with uppercase name with Hibernate

update

I've been searching about ImprovedNamingstrategy it's seems like an issue in spring boot instead I've used EJB3NamingStrategy and also DefaultNamingStrategy it's not working what funny it's when Hibernate execute the SQL the result is pretty confusing :

Hibernate: select utilisateu0_.IdUtilisateur as IdUtilis1_2_, utilisateu0_.Nom as Nom2_2_, utilisateu0_.Password as Password3_2_, utilisateu0_.Prenom as Prenom4_2_, utilisateu0_.Profil as Profil5_2_, utilisateu0_.Pseudo as Pseudo6_2_, utilisateu0_.id_role as id_role7_2_ from UTILISATEUR utilisateu0_

It's like it knows the table name is in UPPERCASE but does not want to map or I don't know what wrong with it this is the result of using

spring.jpa.hibernate.naming-strategy = org.hibernate.cfg.DefaultNamingStrategy
or 
spring.jpa.hibernate.naming_strategy = org.hibernate.cfg.DefaultNamingStrategy

and also the EJB3NamingStrategy

This is also the result when I use the annotation like that

@Table(name="UTILISATEUR")

and I also tried like that

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

and I get the issue

ERROR: syntax error at or near "`"

Hibernate: select utilisateu0_.IdUtilisateur as IdUtilis1_2_, utilisateu0_.Nom as Nom2_2_, utilisateu0_.Password as Password3_2_, utilisateu0_.Prenom as Prenom4_2_, utilisateu0_.Profil as Profil5_2_, utilisateu0_.Pseudo as Pseudo6_2_, utilisateu0_.id_role as id_role7_2_ from `UTILISATEUR` utilisateu0_

finally this is my pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>

<groupId>com.xx.MDValidation</groupId>
<artifactId>xx.MDValidation</artifactId>
<version>0.0.1-SNAPSHOT</version>
<packaging>jar</packaging>

<name>xx.MDValidation</name>
<description>Projet Validation xx</description>

<parent>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-parent</artifactId>
<version>1.3.2.RELEASE</version>
</parent>
<properties>
    <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    <java.version>1.8</java.version>
<hibernate.version>4.2.21.Final</hibernate.version>
<commons-dbcp.version>1.2.2</commons-dbcp.version>
</properties>

    <dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-data-jpa</artifactId>
        
    </dependency>

    
        <dependency>
<groupId>postgresql</groupId>
<artifactId>postgresql</artifactId>
<version>9.1-901.jdbc4</version>
</dependency>
<dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
    </dependency>

    
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-thymeleaf</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-security</artifactId>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <scope>runtime</scope>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-test</artifactId>
        <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jooq</artifactId>
    </dependency>
    <dependency>
        <groupId>bsf</groupId>
        <artifactId>bsf</artifactId>
        <version>2.4.0</version>
    </dependency>
    </dependencies>


<build>
    <plugins>
        <plugin>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-maven-plugin</artifactId>
        </plugin>
    </plugins>
</build>

The best approach is to never use quoted identifiers - they are much more trouble then they are worth it. — , Apr 01 '16 at 10:08
I know that But I can't touch database structure :/ it's those project where they have the database and you are going to use it — Kamel Mili, Apr 01 '16 at 10:10
In the JPA implementation I use (DataNucleus) I can simply set a persistence property to "lowercase" or "UPPERCASE" and it does the necessary changes to what is specified, and all works. Maybe your chosen implementation has something like that? — Neil Stockton, Apr 01 '16 at 10:17
In my opinion what you can do is implement a [custom naming strategy](http://docs.jboss.org/hibernate/core/4.3/javadocs/org/hibernate/cfg/NamingStrategy.html) to convert entity names to their corresponding table names. — Bunti, Apr 01 '16 at 10:42
I have just checked the usage of quoted table identifier with JPA (hibernate 5.0.3) and Derby, and with these it's OK. I can use @Entity(name="\"FFiirrmm\"") and everything is as expected. Can you show us the whole "syntax error" message? — riskop, Apr 01 '16 at 12:39
`unexpected char: '"' [select generatedAlias0 from "UTILISATEUR" as generatedAlias0]` i got this error when i use it like you did — Kamel Mili, Apr 01 '16 at 12:54
okey so am using JPA 2.1 and they said hibernate 5 is supported so am going to add hibernate 5 in the maven dependencies — Kamel Mili, Apr 01 '16 at 13:03
@KamelMili You will not be able to use `org.hibernate.cfg.ImprovedNamingStrategy` — v.ladynev, Apr 01 '16 at 13:04
then i will not be able to name my table in uppercase or lower case so :/ — Kamel Mili, Apr 01 '16 at 13:06
@KamelMili You can http://stackoverflow.com/questions/34949839/implementing-a-namingstrategy-in-hibernate-5-make-autogenerated-column-names-up/34956988#34956988 — v.ladynev, Apr 01 '16 at 13:07
If you are using postgres, then why are you working with a Mysql Dialect?! — M. Deinum, Apr 01 '16 at 13:35
According to the documentation All identifiers (including column names) that are not double-quoted are folded to lower case in PostgreSQL. Column names that were created with double-quotes and thereby retained upper-case letters (and/or other syntax violations) have to be double-quoted for the rest of their life. Did you try to lower case the table name? — uniknow, Apr 08 '16 at 21:49
I did escape it was a dialect problem i could not change table name because my client is using the database in another app — Kamel Mili, Apr 09 '16 at 00:00
I tried to simulate your problem, but I can't found any problem with your case: https://github.com/xsalefter/stackoverflowmvn Could you try this and see what happened on your machine? FYI: I'm using ubuntu 15.10 and PostgreSQL 9.4 — xsalefter, Apr 10 '16 at 17:45

score 12 · Accepted Answer · edited May 23 '17 at 12:02

12

maybe because you are using MYSQL5DIALECT there's a Postgres Dialect just used post it like this and for the improved naming strategy use EJB3 like Spring boot JPA insert in TABLE with uppercase name with Hibernate

spring.jpa.database-platform=org.hibernate.dialect.PostgreSQLDialect

hope it works for you

edited May 23 '17 at 12:02

Community

1
1

answered Apr 04 '16 at 10:10

amani92

903
8
22

2

for people use newer version: `PostgreSQLDialect` is Deprecated: use `PostgreSQL82Dialect` instead doc: https://docs.jboss.org/hibernate/orm/5.0/javadocs/org/hibernate/dialect/PostgreSQLDialect.html – GuoJunjun Mar 28 '18 at 17:14

score 9 · Answer 2 · answered Apr 04 '16 at 08:55

9

I reproduced your UTILISATEUR table (role removed) in postgres 8.4 and hibernate 5.0.3.

It works as expected with explicit table and column names annotation:

@Entity(name="\"UTILISATEUR\"")
public class Utilisateur {

    @Id
    @GeneratedValue(strategy=GenerationType.AUTO) 
    @Column(name="\"IdUtilisateur\"")
    private Long id ;

    @Column(name="\"Nom\"")
    private String Nom ; 
    @Column(name="\"Prenom\"")
    private String Prenom ; 
    @Column(name="\"Profil\"")
    private String Profil ; 
    @Column(name="\"Pseudo\"")
    private String Pseudo ; 

    @Column(name="\"Password\"")
    private String Password ;

    ... getter / setters

}

answered Apr 04 '16 at 08:55

riskop

1,693
1
16
34

org.hibernate.QueryException: unexpected char: '"' [select generatedAlias0 from "UTILISATEUR" as generatedAlias0] – Kamel Mili Apr 04 '16 at 09:09
Can you try running this against your db? http://peter.risko.hu/java_incubator/JPA_Postgres_quoted_identifiers_test.zip – riskop Apr 04 '16 at 09:30
this is another structure i can't use this – Kamel Mili Apr 04 '16 at 09:45
Well, if I were you, I'd try to narrow down the problem. I would create a simplified Utilisateur table and concentrate on the case sensitive table name / column names. My first step would be to assure, that it's possible to handle the simplified case without error. If that is working, then I would start modifying the working -- albeit simplified -- solution to make it more similar to the actual system (more columns, table connections, queries etc.). The code I attached can be your first step in this direction. – riskop Apr 04 '16 at 09:56
Thank you for everything riskop you've done a lot so long – Kamel Mili Apr 04 '16 at 10:04

score 2 · Answer 3 · edited Jul 03 '19 at 16:45

2

You can configure your application with the next line depend the database:

MySql

spring.jpa.hibernate.naming_strategy = org.hibernate.cfg.DefaultNamingStrategy

Postgres

spring.jpa.database-platform=org.hibernate.dialect.PostgreSQLDialect

Oracle

spring.jpa.database-platform=org.hibernate.dialect.Oracle10gDialect

edited Jul 03 '19 at 16:45

AdrianHHH

13,492
16
50
87

answered Jul 03 '19 at 16:20

German Andres Ariza Castañeda

31
2

JPA Uppercase table names

update

3 Answers3

Linked