JPA与Hibernate 3.6.8.Final，PostgresSQL 9.1，SQLGrammarException - 配置问题？奇怪的SQL语句 [英] JPA with Hibernate 3.6.8.Final, PostgresSQL 9.1, SQLGrammarException - configuration issue? Weird SQL statement

问题描述

编辑：
已解决
正确。我发现让我感到困惑的东西。
我使用pgadmin创建表和其他数据库内部，现在进行检查：如果名称中至少有一个字母（表名，列名，pk名称等）是大写字母，则pgadmin将其用于SQL创建脚本照原样使用双引号，因此PostgreSQL会在写入时解释名称。如果运行以下脚本：

  CREATE TABLE SAMPLE 
（
 ID integer NOT NULL，
标题字符变化（100）NOT NULL，
 CONSTRAINT SAMPLE_ID_PK PRIMARY KEY（ID）
）
 WITH（
 OIDS = FALSE 
）; 
 ALTER TABLE SAMPLE 
所有者到postgres; _ 
  

小写，并且原始的Sample.java版本正常工作。

---

这里有什么问题？这个问题一般是针对PostgreSQL 9.1或PostgreSQL的，还是缺少一些hibernate配置？

persistence.xml：

 < persistence-unit name =com.sample。 persistence.jpatransaction-type =RESOURCE_LOCAL> 
< class> com.sample.persistence.Sample< / class> 
<属性> 
< property name =hibernate.dialectvalue =org.hibernate.dialect.PostgreSQLDialect/> 
< property name =hibernate.connection.urlvalue =jdbc：postgresql：sample/> 
< property name =javax.persistence.jdbc.drivervalue =org.postgresql.Driver/> 
< property name =hibernate.connection.usernamevalue =postgres/> 
< property name =hibernate.connection.passwordvalue =postgres/> 
< property name =hibernate.show_sqlvalue =true/> 
< property name =hibernate.format_sqlvalue =true/> 
< property name =hbm2ddl.autovalue =update/> 
< / properties> 
< / persistence-unit> 
  

Sample.java

$ $ p $ @Entity
@Table（name =SAMPLE）
public class示例{

@Id
@Column（name =ID）
私人长ID;

@Column（name =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（）; 
 
 
  

例外：

  ... 
 Hibernate：
选择
 sample0_.ID作为ID0_0_，
 sample0_.TITLE as TITLE0_0_ 
 from 
 SAMPLE sample0_ 
 where 
 sample0_.ID =？ 
线程main中的异常javax.persistence.PersistenceException：org.hibernate.exception.SQLGrammarException：无法加载实体：[com.sample.persistence.Sample＃1] 
 ... 
导致：org.postgresql.util.PSQLException：错误：关系sample不存在
 ... 
  

显然，这个SQL语句如上：

 选择
 sample0_.ID作为ID0_0_， 
 sample0_.TITLE as TITLE0_0_ 
 from 
 SAMPLE sample0_ 
其中
 sample0_.ID =？ 
  

不会从PostgreSQL本身（从pgadmin）成功执行。

但是，如果我将Sample.java更改为：

  @Entity 
 @Table （$ name =\SAMPLE \ b $ b私人长ID; 
 
 @Column（name =\TITLE \）
私有字符串标题; 
 
 public String getTitle（）{
 return title; 
} 
} 
  

这很奇怪，它很有用。

  Hibernate：
选择
 sample0 _。ID为ID1_0_0_，
 sample0 _。TITLEas TITLE2_0_0_ 
 from 
SAMPLEsample0_ 
其中
 sample0 _。ID=？ 
示例标题：示例
  

hibernate.dialect在这里是无用的，或者它不起作用正确使用PostgreSQL 9.1？
另外，如果列名与字段相同，我不想输入列名，但是大写字母也可以吗？

谢谢。 code>构造是强制JPA提供程序使用您提供的确切值（即使用表名的确切大小写）。

此外，它类似在PostgreSQL世界。如果您调用 CREATE TABLE ，并用引号指定表名，那么它将使用您指定的确切名称（不仅是案例，而且还包括语义）来创建表格你甚至可以创建一个名为TABLE的表）：

pre $ code CREATE TABLETeSt（id int PRIMARY KEY NOT NULL）

会导致表格 TeSt8 。

  CREATE TABLE TeSt2（id int PRIMARY KEY NOT NULL）
  

将导致表 test2 。

因此，要查询TeSt表，您需要执行 SELECT * FROMTeSt（ not SELECT * FROM TeSt ）。

因此，如果您使用 CREATE TABLESAMPLE创建表，则需要指定 @Table（name =\\ \\SAMPLE \）为了得到它的工作。

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.

解决方案

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.

这篇关于JPA与Hibernate 3.6.8.Final，PostgresSQL 9.1，SQLGrammarException - 配置问题？奇怪的SQL语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！