JPA query exception

Question

I have a query builded with EntityManager:

Query q = em
 .createQuery("SELECT * FROM :table WHERE username = :username AND password = MD5(:password)")
 .setParameter("table", User.class.getName())
 .setParameter("username", txtLogin.getText())
 .setParameter("password", passPassword.getPassword())
;
User user = (User) q.getSingleResult();

but I get an exception:

Exception in thread "AWT-EventQueue-0" java.lang.IllegalArgumentException: An exception occurred while creating a query in EntityManager:
Exception Description: Syntax error parsing the query [SELECT * FROM :table WHERE username = :username AND password = MD5(:password)], line 1, column 7: unexpected token [*].

How to fix it ?

Is it impossible to use * in queries ?

Answer 1

JPQL syntax is different from SQL, you do

Select T from Thingy T

instead of

Select * from Thingy

But that's only part of your problem. SELECT t FROM :table t won't work either, as parameters are not allowed in the from clause, but only in the where clause. So you must do it something like this:

Query q = em
 .createQuery("SELECT u FROM " + User.class.getName()
 + "u WHERE username = :username AND password = MD5(:password)")
 .setParameter("username", txtLogin.getText())
 .setParameter("password", passPassword.getPassword())
 ;

Also, there is no MD5() function in JPQL, so to use MD5 you either have to do that in java code or use a native SQL query.

Answer 2

Yes you cannot use * like that.

This is how to do it. Note even the SELECT is optional

 Query q = em
 .createQuery("FROM " + User.class.getName() + " WHERE username = :username AND password = MD5(:password)")
 .setParameter("username", txtLogin.getText())
 .setParameter("password", passPassword.getPassword())
 ;
User user = (User) q.getSingleResult();

With SELECT you can do like this:

Query q = em
 .createQuery("SELECT us FROM " + User.class.getName() + "us WHERE username = :username AND password = MD5(:password)")
 .setParameter("username", txtLogin.getText())
 .setParameter("password", passPassword.getPassword())
 ;

• DataNucleus: Which part of the JPA spec says that "SELECT" is optional ? In particular, in section 4.2.1 of the JPA2 spec we have "select_statement :: = select_clause from_clause [where_clause] [groupby_clause] [having_clause] [orderby_clause]" The "select_clause" is not optional at all
• Shervin Asgari: I don't know about the spec. However, if you want a select * from then you don't need to type SELECT. You can just write FROM. Perhaps it is hibernate that supports this, and not jpa specifically.