PSQLException：错误：关系“TABLE_NAME”不存在 [英] PSQLException: ERROR: relation "TABLE_NAME" does not exist

问题描述

我试图在PostgreSQL 8.4.2 DB上运行hibernate。每当我尝试运行一个简单的java代码时：

  List< User> users = service.findAllUsers（）; 
  

我得到以下错误：

 PSQLException：错误：关系TABLE_NAME不存在
  

从我有选项hibernate.show_sql选项设置为true，我可以看到，休眠正在尝试运行以下SQL命令：

  select this_.USERNAME as USERNAME0_0_，this_.PASSWORD asPASSWORD0_0_ 
 fromTABLE_NAMEthis_ 
  

当实际上，它至少应该运行如下内容：

 选择这个_。USERNAME作为USERNAME0_0_，这个_PASSWORD为PASSWORD0_0_ 
 fromSCHEMA_NAME。TABLE_NAMEas this_ 
  

有人知道我有什么变化需要让Hibernate为PostgreSQL产生正确的SQL？

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

 <！ - 使用Spring注释 - > 
< context：annotation-config /> 
<！ -  postgreSQL数据源 - > 
< bean id =dataSourceclass =org.apache.commons.dbcp.BasicDataSource
 destroy-method =close> 
< property name =driverClassNamevalue =org.postgresql.Driver/> 
< property name =url
 value =jdbc：postgresql：// localhost / DB_NAME：5432 / SCHEMA_NAME/> 
< property name =usernamevalue =postgres/> 
< property name =passwordvalue =password/> 
< property name =defaultAutoCommitvalue =false/> 
< / bean> 
  

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

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

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

  @Entity 
 @Table（name =`TABLE_NAME）
 public class User {
 @Id 
 @Column（name =USERNAME）
私人字符串用户名; 
  

有没有人遇到类似的错误？任何帮助解决这个问题将不胜感激。
请注意，问题不同于发布不能简单地使用PostgreSQL表名（关系不存在）

这篇冗长的文章道歉。

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

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

$
$ b

• jdbc：postgresql：database


• jdbc：postgresql：// host / database


• jdbc：postgresql：// host：port / database
  
  $ 数据库在这里是数据库名称。如果主机离开，它将默认为 localhost 。如果端口号保持不变，它将默认为 5432 。因此，您的情况如下：
  
  
  
  
  • jdbc：postgresql：DB_NAME c> jdbc：postgresql：// localhost：5432 / DB_NAME
  
  
  
  

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

  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:

      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?

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

  <!-- 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:

  <!-- 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")

  Apologies for the lengthy post.

  解决方案

  You need to specify the schema name in the Spring's Hibernate properties, not in the JDBC connection 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 documentation you have to use one of the following syntaxes:

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

  The database is 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:

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

  这篇关于PSQLException：错误：关系“TABLE_NAME”不存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！