eclipse Hibernate + PostgreSQL：关系不存在 - SQL 错误：0，SQLState：42P01

Hibernate + PostgreSQL : relation does not exist - SQL Error: 0, SQLState: 42P01

提问by user328229

I am having some problems trying to work with PostgreSQL and Hibernate, more specifically, the issue mentioned in the title. I've been searching the net for a few hours now but none of the found solutions worked for me.

我在尝试使用 PostgreSQL 和 Hibernate 时遇到了一些问题，更具体地说，是标题中提到的问题。我已经在网上搜索了几个小时，但没有一个找到的解决方案对我有用。

I am using Eclipse Java EE IDE for Web Developers. Build id: 20090920-1017 with HibernateTools, Hibernate 3, PostgreSQL 8.4.3 on Ubuntu 9.10.

我正在为 Web 开发人员使用 Eclipse Java EE IDE。构建 ID：20090920-1017，在 Ubuntu 9.10 上使用 HibernateTools、Hibernate 3、PostgreSQL 8.4.3。

Here are the relevant files:

以下是相关文件：

Message.class

消息类

package hello;

        public class Message {
         private Long id;
         private String text;

         public Message() {
         }

         public Long getId() {
          return id;
         }

         public void setId(Long id) {
          this.id = id;
         }

         public String getText() {
          return text;
         }

         public void setText(String text) {
          this.text = text;
         }
        }

Message.hbm.xml

消息.hbm.xml

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
 "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
 "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping package="hello">
  <class 
   name="Message"
   table="public.messages">
   <id  name="id" column="id">
    <generator class="assigned"/>
   </id>
   <property name="text" column="messagetext"/>
   </class>
</hibernate-mapping>

hibernate.cfg.xml

休眠文件.cfg.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
  "-//Hibernate/Hibernate Configuration DTD 3.0//EN"
  "http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
    <session-factory>
        <property name="hibernate.connection.driver_class">org.postgresql.Driver</property>
        <property name="hibernate.connection.password">bar</property>
        <property name="hibernate.connection.url">jdbc:postgresql:postgres/tommy</property>
        <property name="hibernate.connection.username">foo</property>
        <property name="hibernate.dialect">org.hibernate.dialect.PostgreSQLDialect</property>
        <property name="show_sql">true</property>
        <property name="log4j.logger.org.hibernate.type">DEBUG</property>
        <mapping resource="hello/Message.hbm.xml"/> 
    </session-factory>
</hibernate-configuration>

Main

主要的

package hello;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.cfg.Configuration;

public class App {

 public static void main(String[] args) {
  SessionFactory sessionFactory = new Configuration().configure()
    .buildSessionFactory();

  Message message = new Message();
  message.setText("Hello Cruel World");
  message.setId(2L);

  Session session = null;
  Transaction transaction = null;
  try {
   session = sessionFactory.openSession();
   transaction = session.beginTransaction();
   session.save(message);
  } catch (Exception e) {
   System.out.println("Exception attemtping to Add message: "
     + e.getMessage());

  } finally {
   if (session != null && session.isOpen()) {
    if (transaction != null)
     transaction.commit();
    session.flush();
    session.close();
   }

  }
 }
}

Table structure:

表结构：

foo=# \d messages
 Table "public.messages"
   Column    |  Type   | Modifiers 
-------------+---------+-----------
 id          | integer | 
 messagetext | text    | 

Eclipse console output when I run it

运行它时的 Eclipse 控制台输出

Apr 28, 2010 11:13:53 PM org.hibernate.cfg.Environment <clinit>
INFO: Hibernate 3.5.1-Final
Apr 28, 2010 11:13:53 PM org.hibernate.cfg.Environment <clinit>
INFO: hibernate.properties not found
Apr 28, 2010 11:13:53 PM org.hibernate.cfg.Environment buildBytecodeProvider
INFO: Bytecode provider name : javassist
Apr 28, 2010 11:13:53 PM org.hibernate.cfg.Environment <clinit>
INFO: using JDK 1.4 java.sql.Timestamp handling
Apr 28, 2010 11:13:53 PM org.hibernate.cfg.Configuration configure
INFO: configuring from resource: /hibernate.cfg.xml
Apr 28, 2010 11:13:53 PM org.hibernate.cfg.Configuration getConfigurationInputStream
INFO: Configuration resource: /hibernate.cfg.xml
Apr 28, 2010 11:13:53 PM org.hibernate.cfg.Configuration addResource
INFO: Reading mappings from resource : hello/Message.hbm.xml
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.HbmBinder bindRootPersistentClassCommonValues
INFO: Mapping class: hello.Message -> public.messages
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.Configuration doConfigure
INFO: Configured SessionFactory: null
Apr 28, 2010 11:13:54 PM org.hibernate.connection.DriverManagerConnectionProvider configure
INFO: Using Hibernate built-in connection pool (not for production use!)
Apr 28, 2010 11:13:54 PM org.hibernate.connection.DriverManagerConnectionProvider configure
INFO: Hibernate connection pool size: 20
Apr 28, 2010 11:13:54 PM org.hibernate.connection.DriverManagerConnectionProvider configure
INFO: autocommit mode: false
Apr 28, 2010 11:13:54 PM org.hibernate.connection.DriverManagerConnectionProvider configure
INFO: using driver: org.postgresql.Driver at URL: jdbc:postgresql:postgres/tommy
Apr 28, 2010 11:13:54 PM org.hibernate.connection.DriverManagerConnectionProvider configure
INFO: connection properties: {user=foo, password=****}
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: RDBMS: PostgreSQL, version: 8.4.3
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: JDBC driver: PostgreSQL Native Driver, version: PostgreSQL 8.4 JDBC4 (build 701)
Apr 28, 2010 11:13:54 PM org.hibernate.dialect.Dialect <init>
INFO: Using dialect: org.hibernate.dialect.PostgreSQLDialect
Apr 28, 2010 11:13:54 PM org.hibernate.engine.jdbc.JdbcSupportLoader useContextualLobCreation
INFO: Disabling contextual LOB creation as createClob() method threw error : java.lang.reflect.InvocationTargetException
Apr 28, 2010 11:13:54 PM org.hibernate.transaction.TransactionFactoryFactory buildTransactionFactory
INFO: Using default transaction strategy (direct JDBC transactions)
Apr 28, 2010 11:13:54 PM org.hibernate.transaction.TransactionManagerLookupFactory getTransactionManagerLookup
INFO: No TransactionManagerLookup configured (in JTA environment, use of read-write or transactional second-level cache is not recommended)
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Automatic flush during beforeCompletion(): disabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Automatic session close at end of transaction: disabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: JDBC batch size: 15
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: JDBC batch updates for versioned data: disabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Scrollable result sets: enabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: JDBC3 getGeneratedKeys(): enabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Connection release mode: auto
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Default batch fetch size: 1
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Generate SQL with comments: disabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Order SQL updates by primary key: disabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Order SQL inserts for batching: disabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory createQueryTranslatorFactory
INFO: Query translator: org.hibernate.hql.ast.ASTQueryTranslatorFactory
Apr 28, 2010 11:13:54 PM org.hibernate.hql.ast.ASTQueryTranslatorFactory <init>
INFO: Using ASTQueryTranslatorFactory
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Query language substitutions: {}
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: JPA-QL strict compliance: disabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Second-level cache: enabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Query cache: disabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory createRegionFactory
INFO: Cache region factory : org.hibernate.cache.impl.NoCachingRegionFactory
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Optimize cache for minimal puts: disabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Structured second-level cache entries: disabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Echoing all SQL to stdout
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Statistics: disabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Deleted entity synthetic identifier rollback: disabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Default entity-mode: pojo
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Named query checking : enabled
Apr 28, 2010 11:13:54 PM org.hibernate.cfg.SettingsFactory buildSettings
INFO: Check Nullability in Core (should be disabled when Bean Validation is on): enabled
Apr 28, 2010 11:13:54 PM org.hibernate.impl.SessionFactoryImpl <init>
INFO: building session factory
Apr 28, 2010 11:13:55 PM org.hibernate.impl.SessionFactoryObjectFactory addInstance
INFO: Not binding factory to JNDI, no JNDI name configured
Hibernate: insert into public.messages (messagetext, id) values (?, ?)
Apr 28, 2010 11:13:55 PM org.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: 0, SQLState: 42P01
Apr 28, 2010 11:13:55 PM org.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: Batch entry 0 insert into public.messages (messagetext, id) values ('Hello Cruel World', '2') was aborted.  Call getNextException to see the cause.
Apr 28, 2010 11:13:55 PM org.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: 0, SQLState: 42P01
Apr 28, 2010 11:13:55 PM org.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: ERROR: relation "public.messages" does not exist
  Position: 13
Apr 28, 2010 11:13:55 PM org.hibernate.event.def.AbstractFlushingEventListener performExecutions
SEVERE: Could not synchronize database state with session
org.hibernate.exception.SQLGrammarException: Could not execute JDBC batch update
 at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92)
 at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
 at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:275)
 at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:263)
 at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:179)
 at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321)
 at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:51)
 at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1206)
 at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:375)
 at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:137)
 at hello.App.main(App.java:31)
Caused by: java.sql.BatchUpdateException: Batch entry 0 insert into public.messages (messagetext, id) values ('Hello Cruel World', '2') was aborted.  Call getNextException to see the cause.
 at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2569)
 at org.postgresql.core.v3.QueryExecutorImpl.handleError(QueryExecutorImpl.java:459)
 at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1796)
 at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:407)
 at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2708)
 at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:70)
 at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:268)
 ... 8 more
Exception in thread "main" org.hibernate.exception.SQLGrammarException: Could not execute JDBC batch update
 at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92)
 at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
 at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:275)
 at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:263)
 at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:179)
 at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:321)
 at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:51)
 at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1206)
 at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:375)
 at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:137)
 at hello.App.main(App.java:31)
Caused by: java.sql.BatchUpdateException: Batch entry 0 insert into public.messages (messagetext, id) values ('Hello Cruel World', '2') was aborted.  Call getNextException to see the cause.
 at org.postgresql.jdbc2.AbstractJdbc2Statement$BatchResultHandler.handleError(AbstractJdbc2Statement.java:2569)
 at org.postgresql.core.v3.QueryExecutorImpl.handleError(QueryExecutorImpl.java:459)
 at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1796)
 at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:407)
 at org.postgresql.jdbc2.AbstractJdbc2Statement.executeBatch(AbstractJdbc2Statement.java:2708)
 at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:70)
 at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:268)
 ... 8 more

PostgreSQL log file

PostgreSQL 日志文件

2010-04-28 23:13:55 EEST LOG:  execute S_1: BEGIN
2010-04-28 23:13:55 EEST ERROR:  relation "public.messages" does not exist at character 13
2010-04-28 23:13:55 EEST STATEMENT:  insert into public.messages (messagetext, id) values (, )
2010-04-28 23:13:55 EEST LOG:  unexpected EOF on client connection

If I copy/paste the query into the postgre command line and put the values in and ; after it, it works.

如果我将查询复制/粘贴到 postgre 命令行并将值放入 and 中；之后，它的工作原理。

Everything is lowercase, so I don't think that it's that issue.

一切都是小写，所以我不认为这是那个问题。

If I switch to MySQL, the same code same project (I only change driver,URL, authentication), it works.

如果我切换到 MySQL，相同的代码相同的项目（我只更改驱动程序、URL、身份验证），它就可以工作。

In Eclipse Datasource Explorer, I can ping the DB and it succeeds. Weird thing is that I can't see the tables from there either. It expands the public schema but it doesn't expand the tables. Could it be some permission issue?

在 Eclipse 数据源资源管理器中，我可以 ping 数据库并成功。奇怪的是我也看不到那里的桌子。它扩展了公共架构，但不扩展表。会不会是一些权限问题？

Thanks!

谢谢！

采纳答案by araqnid

Your JDBC URL is "jdbc:postgresql:postgres/tommy" which is unusual. The documentationsuggests "jdbc://hostname/databasename". Modern installations come with a "postgres" database that almost definitely isn't what you want to connect to; I don't know how strict the JDBC driver's URL parsing is.

您的 JDBC URL 是“jdbc:postgresql:postgres/tommy”，这是不寻常的。该文件建议“的jdbc：//主机名/数据库名称”。现代安装带有一个“postgres”数据库，它几乎肯定不是您想要连接的；我不知道 JDBC 驱动程序的 URL 解析有多严格。

What are you expecting your database name and hostname to be? e.g. what are your parameters to psql to connect to the database that way?

您希望您的数据库名称和主机名是什么？例如，你的 psql 参数是什么以连接到数据库？

Tip: in postgresql.conf, some settings you may consider:

提示：在 postgresql.conf 中，您可能会考虑一些设置：

log_connections = on
log_disconnections = on
log_line_prefix = '%t %c %q%u@%h:%d '

If the error is what I think (you're connecting to the wrong database), this would log things like the database name along with the error in your postgresql.log file.

如果错误是我认为的（您连接到错误的数据库），这将记录诸如数据库名称之类的内容以及 postgresql.log 文件中的错误。

回答by HankCa

I've just solved the same / similar problem by specifying the schema.

我刚刚通过指定架构解决了相同/类似的问题。

@Entity
@Table(name = "mytable", schema="myschema")
public class MyTable implements Serializable {
    ...

回答by Ross

Another answer may be to check schema permissions - which may be useful for others who happen to come across the subject heading of this question. I had the same exact symptoms regarding the error message which was;

另一个答案可能是检查架构权限 - 这对于碰巧遇到此问题主题标题的其他人可能很有用。关于错误消息，我有相同的确切症状；

<SQL Error: 0, SQLState: 42P01>
<ERROR: relation "tablename" does not exist>

The solution was to enable permissions on that schema for the user I was using to log in with.

解决方案是为我用来登录的用户启用对该架构的权限。

Also: Noting that a good diagnostic tool here is to log into the postgres command line console with the same user your program is using and attempt to access the table and schema in the same way.

另外：请注意，这里的一个很好的诊断工具是使用您的程序使用的同一用户登录 postgres 命令行控制台，并尝试以相同的方式访问表和模式。