Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

org.postgresql.util.PSQLException: ERROR: column user0_.id does not exist - Hibernate

I have a model class that is mapped to a postgres database using hibernate. My model class is:

@Entity
@Table(name="USER")
public class User {

    @Id 
    @GeneratedValue
    @Column(name="id")
    private long id;

    @Column(name="username", unique=true)
    private String username;

    @Column(name="email")
    private String email;

    @Column(name="created")
    private Timestamp created;

    public User(long id, String username, String email) {
        this.id = id;
        this.username = username;
        this.email = email;
    }
}

I try to retrieve the user with username "adam" using the below query:

tx = session.beginTransaction();
TypedQuery<User> query = session.createQuery("FROM User u WHERE u.username = :username", User.class).setParameter("username", "adam");
user = query.getSingleResult();

I get an exception that says:

org.postgresql.util.PSQLException: ERROR: column user0_.id does not exist

My database from bash shell looks like:

database

How does hibernate map class attributes to table columns? Does it match based on the @Column(name="username") only or does it also try to match based on datatypes and constraints such as unique/auto-increment?

like image 312
kovac Avatar asked Aug 20 '17 12:08

kovac


3 Answers

Solution

In PostgreSQL you have to specify the name of schema like so :

@Table(name="table_name", schema = "myapp")
                          ^^^^^^^^^^^^^^^^

Long Story

you got this error :

org.postgresql.util.PSQLException: ERROR: column user0_.id does not exist

because when you create a database in PostgreSQL, it create a default schema named public, so when you don't specify the name in the Entity then Hibernate will check automatically in the public schema.


Good practices

  1. Don't use Upper letters in the name of database, schema, tables or columns in PostgreSQL. Else you should to escape this names with quotes, and this can cause Syntax errors, so instead you can use :

@Table(name="table_name", schema = "schema_name")
             ^^^^^^^^^^             ^^^^^^^^^^^
  1. the keyword USER is reserved keyword in PostgreSQL take a look at

+----------+-----------+----------+-----------+---------+
| Key Word |PostgreSQL |SQL:2003  | SQL:1999  | SQL-92  |
+----------+-----------+----------+-----------+---------+
|  ....        ....       ....       ....       ....    |
+----------+-----------+----------+-----------+---------+
| USER     |  reserved |reserved  | reserved  | reserved|
+----------+-----------+----------+-----------+---------+
  1. to difference between Dto and Entity its good practice to use Entity in the end of the name of your Entity for example UserEntity
like image 54
YCF_L Avatar answered Nov 09 '22 18:11

YCF_L


For people getting this exception ,In postgres Whenever you write an Entity Class try to associate it with the correct schema (where your table is present), like this:

@Entity
@Table(name = "user", schema = "users_details")
public class User implements Serializable{

    @Column(name = "id")
    Long id;    //long is not recommended

   // Other data
}

As @YCF_L has said Don't use Upper_case letters in a table name or column name otherwise you will get this exception.

This convention becomes more important when their is a scenario where you have to auto generate the tables from entity classes or vice-versa.

like image 5
Vikash Kumar Avatar answered Nov 09 '22 20:11

Vikash Kumar


Should add schema name on the Entity class. For this example, when the schema name is public

@Table(name = "user", schema = "public")

See the PostgreSQL Admin view below

enter image description here

See here for more about SpringBoot Java and Postgre SQL connectivity: https://cmsoftwaretech.wordpress.com/2020/04/25/springboot-thymleaf-using-postgresql/

like image 4
Cebu CM Solutions Avatar answered Nov 09 '22 19:11

Cebu CM Solutions