JPA Query does not work

Question

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?

even if you use: "FROM User u WHERE u.nick =:nick" as the query ? — Laabidi Raissi
– Laabidi Raissi, Commented Jan 1, 2014 at 21:39
Where is the problem? That is not a valid JPQL query. There is no "*" in JPQL. Any basic JPA reference would tell you this — Neil Stockton
– Neil Stockton, Commented Jan 12, 2017 at 19:22

Glenn Lane · Accepted Answer · 2014-01-01 22:13:30Z

1

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.

answered Jan 1, 2014 at 22:13

Glenn Lane

4,06022 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Mato Over a year ago

Thanks, if I use your second output, I get this error: javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.0.v20130507-3faac2b): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: java.sql.SQLSyntaxErrorException: Syntax error: Encountered "User" at line 1, column 15. Error Code: -1 Call: SELECT 1 FROM User WHERE (Nick = 'aaa') Query: ReportQuery(referenceClass=User sql="SELECT ? FROM User WHERE (Nick = ?)")

Glenn Lane Over a year ago

I suspect your underlying database has User as a reserved word. Try changing the table name (use @Table annotation on the entity class).

Mato Over a year ago

Thanks, User is really reserved word. I have created the new project with name Userr. I have used this query: "SELECT UserID, EnterKey, Nick FROM Userr WHERE (Nick = :nick)" and I get this error: java.lang.IllegalArgumentException: An exception occurred while creating a query in EntityManager: Exception Description: Syntax error parsing [SELECT UserID, EnterKey, Nick FROM Userr WHERE (Nick = :nick)]. [41, 41] An identification variable must be provided for a range variable declaration. Can you help me please?

Glenn Lane Over a year ago

That is not a JPA query. Instead use: SELECT u.UserID, u.EnterKey, u.Nick FROM Userr u WHERE u.Nick = :nick

RamPrakash · Accepted Answer · 2019-12-12 00:15:03Z

0

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.

edited Dec 12, 2019 at 0:15

RamPrakash

1,8403 gold badges21 silver badges28 bronze badges

answered Jan 1, 2014 at 21:22

Diversity

1,90013 silver badges22 bronze badges

12 Comments

Mato Over a year ago

Thanks, but now I get this error: javax.persistence.PersistenceException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.0.v20130507-3faac2b): org.eclipse.persistence.exceptions.DatabaseException Internal Exception: java.sql.SQLSyntaxErrorException: Syntax error: Encountered "FROM" at line 1, column 9. Error Code: -1 Call: SELECT FROM User WHERE (Nick = ?) bind => [1 parameter bound] Query: ReportQuery(referenceClass=User sql="SELECT FROM User WHERE (Nick = ?)")

Mato Over a year ago

And if I use "u" I get this error: java.lang.IllegalArgumentException: An exception occurred while creating a query in EntityManager: Exception Description: Problem compiling [SELECT User u From User u Where u.nick = :nick]. [24, 25] The identification variable 'u' cannot be declared more than once. [12, 13] The identification variable 'u' cannot be declared more than once.

Benny Bottema Over a year ago

Try simply: from User u where u.nick = :nick, without the select statement.

Diversity Over a year ago

Put the u in bracket or just use the from clause without select statement as Laabidi Raaisi suggested

Laabidi Raissi Over a year ago

please try the exact code you mentioned in your question, just without "select *" and give us the result errors if any. Here you are using "?" in your query and we don't know how did you set the param value

|

Collectives™ on Stack Overflow

JPA Query does not work

2 Answers 2

4 Comments

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related