首页 文章

JPA与Hibernate 3.6.8.Final,PostgreSQL 9.1,SQLGrammarException - 配置问题?奇怪的SQL语句

提问于
浏览
5

EditSOLVED 对 . 我找到了困扰我的事情 . 我使用pgadmin创建表和其他数据库内部,立即检查:如果名称中至少有一个字母(表名,列名,pk名称等)是大写的,那么pgadmin在SQL创建脚本中使用它实际上,使用双引号,因此PostgreSQL会在编写时解释名称 . 如果运行以下脚本:

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;_

它以小写形式创建所有内容,原始的Sample.java版本工作正常 .


这里有什么问题?这个问题一般特定于PostgreSQL 9.1或PostgreSQL,还是缺少一些hibernate配置?

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

显然,这个SQL语句如上:

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

从PostgreSQL本身(来自pgadmin)未成功执行 .

但是,如果我将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;
    }
}

这很奇怪,很有效 .

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

hibernate.dialect在这里没用,或者它与PostgreSQL 9.1无法正常工作?此外,如果列名称与字段相同,我不想键入列名称,但在大写字母中,是否也可以?

谢谢 .

1 回答

  • 4

    @Table("\"...\"") 构造是强制JPA提供程序使用您提供的确切值(即使用表名的确切大小写) .

    而且,它在PostgreSQL世界中也很相似 . 如果你调用 CREATE TABLE 并用引号指定表名,那么它将创建具有你指定的确切名称的表(不仅是大小写而且还有语义 - 这样你甚至可以创建一个名为TABLE的表):

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

    将导致表 TeSt8 .

    CREATE TABLE TeSt2 ( id int PRIMARY KEY NOT NULL )
    

    将导致表 test2 .

    因此,要查询"TeSt"表,您需要执行 SELECT * FROM "TeSt"not SELECT * FROM TeSt ) .

    因此,如果您使用 CREATE TABLE "SAMPLE" 创建表,则需要指定 @Table(name="\"SAMPLE\"") 才能使其正常工作 .

相关问题