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

Question

I have a model class that maps to a postgres database using sleep mode. My model class:

@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 am trying to get a user with username adam using the following 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 the bash shell looks like this:

How does hibernate map class class attributes to table columns? Is it only compatible with the @Column(name="username") parameter @Column(name="username") or is it also trying to match based on data types and restrictions, such as a unique / automatic increment?

Answer 1

Decision

In PostgreSQL, you must specify the schema name as follows:

 @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 creates a default schema called public , so if you do not specify a name in Entity , Hibernate will check automatically in the public schema.

---

Good practices

1. Do not use capital letters in the name database , schema , tables or columns in PostgreSQL . Otherwise, you must escape these names in quotation marks, and this can cause syntax errors, so instead you can use:

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

2. The USER keyword is a reserved keyword in PostgreSQL , look at

 +----------+-----------+----------+-----------+---------+ | Key Word |PostgreSQL |SQL:2003 | SQL:1999 | SQL-92 | +----------+-----------+----------+-----------+---------+ | .... .... .... .... .... | +----------+-----------+----------+-----------+---------+ | USER | reserved |reserved | reserved | reserved| +----------+-----------+----------+-----------+---------+ 

3. to distinguish between Dto and Entity it is recommended to use Entity at the end of your Entity name, for example UserEntity

Answer 2

For people getting this exception in postgres When you write the Entity Class, try to associate it with the correct schema (where your table is present), for example:

 @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 said, Do not capitalize the table or column name, otherwise you will get this exception.

This convention becomes more important when it is a scenario in which you need to automatically generate tables from entity classes or vice versa.