PostgreSQL Hibernate and Intellij IDEA results in org.hibernate.QueryException: unexpected char: '"'

Question

I use PostgreSQL, Intellij IDEA, Spring MVC, Hibernate.

When I'm trying retrieve list of users from table "Users" I recieve exception like below:

SEVERE: Servlet.service() for servlet [idea] in context with path [] threw exception [Request processing failed; nested exception is org.hibernate.QueryException: unexpected char: '"' [select * from "Users"]] with root cause org.hibernate.QueryException: unexpected char: '"' [select * from "Users"].

When I trying execute SQL query (select * from "Users") in DB console it working fine.

Here's me code when I recieve exception:

public List listUser() {
  return sessionFactory.getCurrentSession()
      .createQuery("select * from \"Users\"").list();
} 

Can somebody advice me with this issue?

Answer 1

So as you are using HQL, your query should be:

select from Users

assuming your Entity class is Users

Or just from Users

Answer 2

You need something like this :

...    
    return sessionFactory.getCurrentSession().createQuery("SELECT * FROM Users").list();
}

You can do that too with HQL Query :

         ...
         String HQL = "FROM Users";
         return sessionFactory.getCurrentSession().createQuery(HQL);
    }

Answer 3

Remove the " and the select * from the query, they're not needed:

.createQuery("from Users")

But is your entity really called Users or is this just the table name? Because Hibernate expects you to use the entity name in the query.

If you want to query the Users table directly, use

.createSQLQuery("select * from Users").list();

instead, but it'll just return a List<Object[]>, with your columns being the elements of the array(s). You can make hibernate do the translation into an entity (I assume it's called User) like so:

.createSQLQuery("select * from Users").addEntity(User.class).list();

Though I really recommend using the HQL version for that.