JPA query exception

Question

Hello !

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