JPA with Hibernate 3.6.8.Final, PostgreSQL 9.1, SQLGrammarException - configuration issue? Weird SQL statement

Question

Edit: SOLVED Right. I found the thing that confused me. I use pgadmin to create tables and others database internals, checked right now: if at least one letter in the name (table name, column name, pk name, etc) is in the upper case, then pgadmin uses it in the SQL creation script as it is, using double quotes, so PostgreSQL interprets the name as it was written. If run the following script:

CREATE TABLE SAMPLE
(
  ID integer NOT NULL,
  TITLE character varying(100) NOT NULL,
  CONSTRAINT SAMPLE_ID_PK PRIMARY KEY (ID)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE SAMPLE
  OWNER TO postgres;_

it creates everything in the lower case, and the original Sample.java version works fine.

---

What is wrong here? Is this issue specific to PostgreSQL 9.1 or PostgreSQL in general, or some hibernate configuration is missing?

persistence.xml:

<persistence-unit name="com.sample.persistence.jpa" transaction-type="RESOURCE_LOCAL">
    <class>com.sample.persistence.Sample</class>
    <properties>
        <property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect"/>
        <property name="hibernate.connection.url" value="jdbc:postgresql:sample"/>
        <property name="javax.persistence.jdbc.driver" value="org.postgresql.Driver"/>
        <property name="hibernate.connection.username" value="postgres"/>
        <property name="hibernate.connection.password" value="postgres"/>
        <property name="hibernate.show_sql" value="true"/>
        <property name="hibernate.format_sql" value="true"/>
        <property name="hbm2ddl.auto" value="update"/>
    </properties>
</persistence-unit>

Sample.java:

@Entity
@Table(name = "SAMPLE")
public class Sample {

    @Id
    @Column(name = "ID")
    private long id;

    @Column(name = "TITLE")
    private String title;

    public String getTitle() {
        return title;
    }
}

PersistenceMain.java:

public class PersistenceMain {


    public static void main(String[] args) {
        EntityManagerFactory emf = Persistence.createEntityManagerFactory("com.sample.persistence.jpa");
        EntityManager em = emf.createEntityManager();

        Sample sample = em.find(Sample.class, 1l);

        System.out.println("Sample Title: " + sample.getTitle());

        em.close();

        emf.close();
    }
}

Exception:

...
Hibernate: 
    select
        sample0_.ID as ID0_0_,
        sample0_.TITLE as TITLE0_0_ 
    from
        SAMPLE sample0_ 
    where
        sample0_.ID=?
Exception in thread "main" javax.persistence.PersistenceException:     org.hibernate.exception.SQLGrammarException: could not load an entity:   [com.sample.persistence.Sample#1]
 ...
Caused by: org.postgresql.util.PSQLException: ERROR: relation "sample" does not exist
 ...

Obviously, this SQL statement above:

select
    sample0_.ID as ID0_0_,
    sample0_.TITLE as TITLE0_0_ 
from
    SAMPLE sample0_ 
where
    sample0_.ID=?

is not executed successfully from the PostgreSQL itself (from pgadmin).

But, if I change Sample.java to:

@Entity
@Table(name = "\"SAMPLE\"")
public class Sample {

    @Id
    @Column(name = "\"ID\"")
    private long id;

    @Column(name = "\"TITLE\"")
    private String title;

    public String getTitle() {
        return title;
    }
}

which is weird, it works.

Hibernate: 
    select
        sample0_."ID" as ID1_0_0_,
        sample0_."TITLE" as TITLE2_0_0_ 
    from
        "SAMPLE" sample0_ 
    where
        sample0_."ID"=?
Sample Title: Sample

Is hibernate.dialect useless here, or it doesn't work properly with PostgreSQL 9.1? Also, I would like not to type columns names if they are the same as the field, but in upper case, is it also possible?

Thank you.

Answer 1

The @Table("\"...\"") construct is to force the JPA provider to use the exact value you provide (i.e. use the exact case of the table name).

Moreover, it's similar in the PostgreSQL world. If you invoke CREATE TABLE and specify the table name in quotes than it will create the table with the exact name you specify (not only the case but also the semantics - in this way you can even create a table named TABLE):

CREATE TABLE "TeSt" ( id int PRIMARY KEY NOT NULL )

will result in table TeSt8.

CREATE TABLE TeSt2 ( id int PRIMARY KEY NOT NULL )

will result in table test2.

Hence, to query for "TeSt" table you will need to execute SELECT * FROM "TeSt" (not SELECT * FROM TeSt).

So, if you create a table with CREATE TABLE "SAMPLE" you need to specify @Table(name="\"SAMPLE\"") in order to get it worked.