CODEWITHSUNDEEP
javajpa
Mato
Mato

Reputation: 225

JPA Query does not work

Can you help me please with this problem? I want use this method for find the specific nick in my database (It made with Apache Derby). I have used the EntityManager and mapping Persistence - Entity classes from database in the NetBeans.

public static boolean findByNick(String nick) {

    List<eng.db.User> ret;
    EntityManager em = getEntityManager();

    Query q = em.createQuery("SELECT * FROM User u WHERE u.nick =:nick");
    q.setParameter("nick", nick);
    ret = q.getResultList();
    em.close();

    boolean hodnota = false;

    if (ret.isEmpty()) {
        hodnota = true;
    }

    return hodnota;
}

I get this error:

java.lang.IllegalArgumentException: An exception occurred while creating a query in EntityManager:
Exception Description: Syntax error parsing [SELECT * FROM User u WHERE u.nick =:nick)].
[21, 21] A select statement must have a FROM clause.
[7, 7] The left expression is missing from the arithmetic expression.
[9, 20] The right expression is not an arithmetic expression.
[41, 42] The query contains a malformed ending.

Where is the problem please?

Upvotes: 0

Views: 12689

Answers (2)

Diversity
Diversity

Reputation: 1900

It should be

UPDATE

according to your comment now the query should be:

SELECT u.userID, u.enterKey, u.nick 
FROM User u
WHERE u.nick = ?

or with named param

SELECT u.userId, u.enterKey, u.nick 
FROM User u
WHERE u.nick = :nick

where usereID, enterKey and nick are fields(properties) of your entity type User.

Your User class should look pretty much like this

@Entity
public class User {

      @Id
      private long userId;

     @column(name="EnterKey"
      private String enterKey;

      @column(name="nick")
      private String nick;

      // setter getter
}

Regard that this hql and you use the class and property names of the object model which you defined as arguments for the query.

in your query Hibernate or any other JPA implementation creates a list of objects of type User using the mapping you defined. The expression * can not be associated with an object name of this type. The equivalent to * in sql is in object related query languages just the of the alias of the from clause in your case "u" since the from clause looks like this:

From User u

If you want just to select separate fields of your Entity you have to declare 

 Select alias.property
 from Entity alias

or in your special case 

 Select u.nick
 From User u

In this case instances of type User are created and the field nick is set.

Upvotes: 0

Glenn Lane
Glenn Lane

Reputation: 4010

If nick is the primary key of your entity (@Id), then use:

return em.find(eng.db.User, nick) != null;

Or if not:

Query q = em.createQuery("SELECT TRUE FROM User u WHERE u.nick =:nick");
q.setParameter("nick", nick);
return !q.getResultList().isEmpty();

By returning a simple boolean, you minimize the DB response.

Upvotes: 1

Related Questions