Java PSQLException：错误：关系“TABLE_NAME”不存在

PSQLException: ERROR: relation "TABLE_NAME" does not exist

提问by Lucas T

I am trying to run hibernate on a PostgreSQL 8.4.2 DB. Whenever I try to run a simple java code like:

我正在尝试在 PostgreSQL 8.4.2 DB 上运行休眠。每当我尝试运行一个简单的 Java 代码时，例如：

List<User> users = service.findAllUsers();

I get the following error:

我收到以下错误：

PSQLException: ERROR: relation "TABLE_NAME" does not exist

Since I have option hibernate.show_sql option set to true, I can see that hibernate is trying to run the following SQL command:

由于我将选项 hibernate.show_sql 选项设置为 true，我可以看到 hibernate 正在尝试运行以下 SQL 命令：

    select this_.USERNAME as USERNAME0_0_, this_.PASSWORD as PASSWORD0_0_ 
from "TABLE_NAME" this_

When in reality, it should at least run something like:

在现实中，它至少应该运行如下：

    select this_."USERNAME" as USERNAME0_0_, this_."PASSWORD" as PASSWORD0_0_ 
from "SCHEMA_NAME"."TABLE_NAME" as this_

Does anyone know what changes I need to make for Hibernate to produce the right SQL for PostgreSQL?

有谁知道我需要对 Hibernate 进行哪些更改才能为 PostgreSQL 生成正确的 SQL？

I have set up the necessary postgreSQL datasource in applicationContext.xml file:

我已经在 applicationContext.xml 文件中设置了必要的 postgreSQL 数据源：

<!-- Use Spring annotations -->
 <context:annotation-config /> 
 <!-- postgreSQL datasource -->
 <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource"
  destroy-method="close">
  <property name="driverClassName" value="org.postgresql.Driver" />
  <property name="url"
   value="jdbc:postgresql://localhost/DB_NAME:5432/SCHEMA_NAME" />
  <property name="username" value="postgres" />
  <property name="password" value="password" />
  <property name="defaultAutoCommit" value="false" />
 </bean>

On the same file I have set up the session factory with PostgreSQL dialect:

在同一个文件中，我使用 PostgreSQL 方言设置了会话工厂：

<!-- Hibernate session factory -->
 <bean id="sessionFactory"   class="org.springframework.orm.hibernate3.annotation.AnnotationSessionFactoryBean">
  <property name="dataSource" ref="dataSource" />
  <property name="annotatedClasses">
   <list>
    <value>com.myPackage.dbEntities.domain.User</value>
   </list>
  </property>
  <property name="hibernateProperties">
   <props>
    <prop key="hibernate.dialect">org.hibernate.dialect.PostgreSQLDialect</prop>
    <prop key="hibernate.show_sql">true</prop>
   </props>
  </property>
 </bean>
 <!-- setup transaction manager -->
 <bean id="transactionManager"
  class="org.springframework.orm.hibernate3.HibernateTransactionManager">
  <property name="sessionFactory">
   <ref bean="sessionFactory" />
  </property>
 </bean>

Finally, the way I am mapping the domain class to the table is:

最后，我将域类映射到表的方式是：

    @Entity
@Table(name = "`TABLE_NAME`")
public class User {
@Id
@Column(name = "USERNAME")
private String username;

Has anyone encountered a similar error?. Any help in solving this issue will be much appreciated. Please note that question is different to post Cannot simply use PostgreSQL table name (”relation does not exist”)

有没有人遇到过类似的错误？任何解决此问题的帮助将不胜感激。请注意，问题与postCannot simple use PostgreSQL table name ("relation does not exist") 不同

Apologies for the lengthy post.

为冗长的帖子道歉。

采纳答案by BalusC

You need to specify the schema name in the Spring's Hibernate properties, not in the JDBC connection URL:

您需要在 Spring 的 Hibernate 属性中指定架构名称，而不是在 JDBC 连接 URL 中：

<prop key="hibernate.default_schema">SCHEMA_NAME</prop>

That said, your JDBC connection URL is in fact syntactically invalid. According to the PostgreSQL JDBC documentationyou have to use one of the following syntaxes:

也就是说，您的 JDBC 连接 URL 实际上在语法上是无效的。根据PostgreSQL JDBC 文档，您必须使用以下语法之一：

• jdbc:postgresql:database
• jdbc:postgresql://host/database
• jdbc:postgresql://host:port/database

• jdbc:postgresql:database
• jdbc:postgresql://host/database
• jdbc:postgresql://host:port/database

The databaseis here the database name. If the host is left away, it will default to localhost. If the port number is left away, it will just default to 5432. Thus, one of the following is valid in your case:

在database这里是数据库名。如果主机离开，它将默认为localhost。如果端口号被忽略，它只会默认为5432。因此，以下情况之一对您的情况有效：

• jdbc:postgresql:DB_NAME
• jdbc:postgresql://localhost/DB_NAME
• jdbc:postgresql://localhost:5432/DB_NAME

• jdbc:postgresql:DB_NAME
• jdbc:postgresql://localhost/DB_NAME
• jdbc:postgresql://localhost:5432/DB_NAME

回答by Magnus Hagander

Looking at the PostgreSQL JDBC driver documentation, it doesn't seem to support you adding the schema at the end of the connection url. Are you sure that's supposed to work?

查看 PostgreSQL JDBC 驱动程序文档，它似乎不支持您在连接 url 的末尾添加架构。你确定这应该有效吗？

A workaround would be to set the search_path in the database to include your schema, but that would obviously fail if you have the same table in multiple schemas.

一种解决方法是在数据库中设置 search_path 以包含您的架构，但如果您在多个架构中拥有相同的表，这显然会失败。

I don't know hibernate enough to comment on if it's possible to teach it about a schema.

我对 hibernate 的了解不够，无法评论是否可以教它有关模式的知识。

回答by Shahid Hussain Abbasi

Due to using column definations I was getting this error

由于使用列定义，我收到此错误

issue @Column(name = "user_id", nullable = false, unique = true, columnDefinition ="user unique ID" )

问题@Column(name = "user_id", nullable = false, unique = true, columnDefinition ="user unique ID" )

solved @Column(name = "user_id", nullable = false, unique = true)

解决了@Column(name = "user_id", nullable = false, unique = true)

Thanks

谢谢

回答by Shripad Bhat

If you are using spring-boot set default schema in the configuration:

如果您在配置中使用 spring-boot 设置默认架构：

spring.jpa.properties.hibernate.default_schema: my_schema

Make sure to include the schema name in the query:

确保在查询中包含架构名称：

@Query(value = "SELECT user_name FROM my_schema.users", nativeQuery = true)
List<String> findAllNames();

回答by YouAreAwesome

Not sure about others but my case if worked only if I wrote my query like this:

不确定其他人，但我的情况只有在我这样写查询时才有效：

@Query(value = "SELECT tbl.* FROM my_schema.my_table tbl", nativeQuery = true)

Always have some reference "tbl" for table and use it in the query.

总是有一些表的参考“tbl”并在查询中使用它。