Databases
StackOverflow2013
Postsdbo
POJPA with Hibernate 3.6.8.Final, PostgresSQL 9.1, SQLGrammarException - configuration issue? Weird SQL statement
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POJPA with Hibernate 3.6.8.Final, PostgresSQL 9.1, SQLGrammarException - configuration issue? Weird SQL statement
    primarykey
    data
    text
    <p><strong>Edit</strong>: <strong>SOLVED</strong> 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:</p> <pre><code>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;_ </code></pre> <p>it creates everything in the lower case, and the original Sample.java version works fine. <hr /> What is wrong here? Is this issue specific to PostgreSQL 9.1 or PostgreSQL in general, or some hibernate configuration is missing?</p> <p><strong>persistence.xml:</strong></p> <p> </p> <pre><code>&lt;persistence-unit name="com.sample.persistence.jpa" transaction-type="RESOURCE_LOCAL"&gt; &lt;class&gt;com.sample.persistence.Sample&lt;/class&gt; &lt;properties&gt; &lt;property name="hibernate.dialect" value="org.hibernate.dialect.PostgreSQLDialect"/&gt; &lt;property name="hibernate.connection.url" value="jdbc:postgresql:sample"/&gt; &lt;property name="javax.persistence.jdbc.driver" value="org.postgresql.Driver"/&gt; &lt;property name="hibernate.connection.username" value="postgres"/&gt; &lt;property name="hibernate.connection.password" value="postgres"/&gt; &lt;property name="hibernate.show_sql" value="true"/&gt; &lt;property name="hibernate.format_sql" value="true"/&gt; &lt;property name="hbm2ddl.auto" value="update"/&gt; &lt;/properties&gt; &lt;/persistence-unit&gt; </code></pre> <p></p> <p><strong>Sample.java</strong>:</p> <pre><code>@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; } } </code></pre> <p><strong>PersistenceMain.java:</strong></p> <pre><code>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(); } } </code></pre> <p><strong>Exception:</strong></p> <pre><code>... 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 ... </code></pre> <p>Obviously, this SQL statement above:</p> <pre><code>select sample0_.ID as ID0_0_, sample0_.TITLE as TITLE0_0_ from SAMPLE sample0_ where sample0_.ID=? </code></pre> <p>is not executed successfully from the PostgreSQL itself (from pgadmin).</p> <p>But, if I change Sample.java to:</p> <pre><code>@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; } } </code></pre> <p>which is weird, it works.</p> <pre><code>Hibernate: select sample0_."ID" as ID1_0_0_, sample0_."TITLE" as TITLE2_0_0_ from "SAMPLE" sample0_ where sample0_."ID"=? Sample Title: Sample </code></pre> <p>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?</p> <p>Thank you.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USakazlou
    1. USakazlou
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POJPA with Hibernate 3.6.8.Final, PostgresSQL 9.1, SQLGrammarException - configuration issue? Weird SQL statement
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POJPA with Hibernate 3.6.8.Final, PostgresSQL 9.1, SQLGrammarException - configuration issue? Weird SQL statement
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POJPA with Hibernate 3.6.8.Final, PostgresSQL 9.1, SQLGrammarException - configuration issue? Weird SQL statement
      1. USRick-Rainer Ludwig
      1. VTFavorite
    1. COIf you remove the hibernate dialect-related property - will it work? AFAIR the `@Entity(\""..."\")` construct is to force the JPA provider to use the exact value provided (i.e. use the exact case of the table name). Don't know why in your case it do some magic.
      singulars
      1. POJPA with Hibernate 3.6.8.Final, PostgresSQL 9.1, SQLGrammarException - configuration issue? Weird SQL statement
      1. USPiotr Nowicki
    2. COI tried without dialect, the original Sample.java doesn't work, modified works, also tried to use MySQL dialect instead of PostgreSQL, just to expect to see warnings or errors, the result is the same, it seems that a) dialect is ignored b) dialect is not used while constructing SQL queries, but probably only for vendor specific behavior, like pagination, for example.
      singulars
      1. POJPA with Hibernate 3.6.8.Final, PostgresSQL 9.1, SQLGrammarException - configuration issue? Weird SQL statement
      1. USakazlou
    3. COand what if you would try using the JPA properties for JDBC connection (http://www.vogella.de/articles/JavaPersistenceAPI/article.html#example_persistenceunit) and remove the dialect and hibernate-related properties? What's the exact table name in your DB?
      singulars
      1. POJPA with Hibernate 3.6.8.Final, PostgresSQL 9.1, SQLGrammarException - configuration issue? Weird SQL statement
      1. USPiotr Nowicki
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload