带有Hibernate 3.6.8.Final，PostgreSQL 9.1，SQLGrammarException的JPA-配置问题?奇怪的SQL语句 [英] JPA with Hibernate 3.6.8.Final, PostgreSQL 9.1, SQLGrammarException - configuration issue? Weird SQL statement
问题描述
修改: 已解决 正确的.我发现了令我困惑的事情. 我使用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的，还是缺少某些休眠配置?
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();
}
}
例外:
...
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中无法正常工作? 另外，如果列名与字段相同，我也不想键入列名，但是大写也可以吗?
谢谢.
@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"(不是 SELECT * FROM TeSt).
因此，如果使用CREATE TABLE "SAMPLE"创建表，则需要指定@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.
这篇关于带有Hibernate 3.6.8.Final，PostgreSQL 9.1，SQLGrammarException的JPA-配置问题?奇怪的SQL语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
