PSQLException: ERROR: relation "TABLE_NAME" does not exist

Question

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.

Answer 1

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

Answer 2

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

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();