Unable to have correct value with SELECT query using MAX() in JPA

Question

I am new in JPA and I have a problem when I try to query to the database using MAX() function. Code of my function is following. Can anyone help me? Thank you.

public int getMaxId(){

    entityManager = this.entityManagerFactory.createEntityManager();

    Query query = entityManager.createQuery("SELECT * FROM user WHERE id = (SELECT MAX(u.id) FROM user u)");
    User user = (User) query.getSingleResult();

    int id = user.getId();
    return id;
}

I am using JPA, TopLink and Apache Derby. My method should return the maximum id of table users.

Edit: I call that function from a service:

try {
        int id = userDAO.getMaxId();
        logger.info("Max id: " + id);
        user.setId(id+1);

    }
    catch (Exception ex){
        logger.error("Unable to get the max id.");
    }

Value of user.setId() is always '0'.

Edit(2): Log

    Caused by: Exception [EclipseLink-8034] (Eclipse Persistence Services - 2.3.0.v20110604-r9504): org.eclipse.persistence.exceptions.JPQLException
Exception Description: Error compiling the query [SELECT u FROM user u WHERE u.id = (SELECT MAX(uu.id) FROM user uu)]. Unknown entity type [user].
    at org.eclipse.persistence.exceptions.JPQLException.entityTypeNotFound(JPQLException.java:483)
    at org.eclipse.persistence.internal.jpa.parsing.ParseTreeContext.classForSchemaName(ParseTreeContext.java:138)
    at org.eclipse.persistence.internal.jpa.parsing.SelectNode.getClassOfFirstVariable(SelectNode.java:327)
    at org.eclipse.persistence.internal.jpa.parsing.SelectNode.getReferenceClass(SelectNode.java:316)
    at org.eclipse.persistence.internal.jpa.parsing.ParseTree.getReferenceClass(ParseTree.java:436)
    at org.eclipse.persistence.internal.jpa.parsing.ParseTree.adjustReferenceClassForQuery(ParseTree.java:75)
    at org.eclipse.persistence.internal.jpa.parsing.JPQLParseTree.populateReadQueryInternal(JPQLParseTree.java:103)
    at org.eclipse.persistence.internal.jpa.parsing.JPQLParseTree.populateQuery(JPQLParseTree.java:84)
    at org.eclipse.persistence.internal.jpa.EJBQueryImpl.buildEJBQLDatabaseQuery(EJBQueryImpl.java:219)
    at org.eclipse.persistence.internal.jpa.EJBQueryImpl.buildEJBQLDatabaseQuery(EJBQueryImpl.java:190)
    at org.eclipse.persistence.internal.jpa.EJBQueryImpl.<init>(EJBQueryImpl.java:142)
    at org.eclipse.persistence.internal.jpa.EJBQueryImpl.<init>(EJBQueryImpl.java:126)
    at org.eclipse.persistence.internal.jpa.EntityManagerImpl.createQuery(EntityManagerImpl.java:1475)
    ... 35 more

My entity User is declared as follows:

@Entity
@Table(name = "user")
public class User {

@Id
private int id;
private String name;
private String lastName;
private String city;
private String password;

You should not swallow the exception, try use `ex.printStackTrace()` to print full error stack for more details. — Pau Kiat Wee, Jun 12 '12 at 08:49
@JMelnik Yes, I agree but I wanted to understand how JPA/JPQL it works trying to use persistent opreations and queries. — Spacemonkey, Jun 12 '12 at 09:00
@PauKiatWee Thanks for the advice, I just added the `prinstStackTrace()` to my code. — Spacemonkey, Jun 12 '12 at 09:01
@Julián look for books, documentation and finally implementation's source code. — d1e, Jun 12 '12 at 09:05
@Julián, do not look for MAX(), look for what you are really out there for - "understand how JPA/JPQL it works trying to use persistent opreations and queries". — d1e, Jun 12 '12 at 09:09
@JMelnik Yes, It is what I always do. I am reading this book [link]http://www.amazon.com/Pro-JPA-Mastering-Persistence-Technology/dp/1430219564[link] I just wanted to know if any of you can recongnize an error in my code. Thank you very much. — Spacemonkey, Jun 12 '12 at 09:12
Since when has "select * ..." been JPQL? A simple glance at the JPA spec or *any* JPA docs would reveal the valid syntax — DataNucleus, Jun 12 '12 at 10:29

score 22 · Accepted Answer · edited Nov 18 '13 at 17:09

22

You can directly use more simple JPQL

return (Integer)entityManager.createQuery("select max(u.id) from User u").getSingleResult();

Or use TypedQuery

return entityManager.createQuery("select max(u.id) from User u", Integer.class).getSingleResult();

EDIT:

Unknown entity type [user]

You should use User instead of user.

edited Nov 18 '13 at 17:09

aioobe

413,195
112
811
826

answered Jun 12 '12 at 08:13

Pau Kiat Wee

9,485
42
40

Thanks for your answer. I have tryed to modify the code but it is still not returning a value. When I try to persist any object I do like this and without problems. entityManager.getTransaction().begin(); entityManager.persist(user); entityManager.getTransaction().commit(); But querys are not working ok, I don't know why. – Spacemonkey Jun 12 '12 at 08:37
Hello Pau, means that call to getMaxId() function always raises catch() in the Service layer (see update) – Spacemonkey Jun 12 '12 at 08:47
Could you log the exception caught in the catch statement? – bbaja42 Jun 12 '12 at 08:49
@PauKiatWee Where should I use `users`? In JPQL statement? It is not working either. I just changed. I got the same error with `users`. – Spacemonkey Jun 12 '12 at 09:05
@PauKiatWee Pau, thank you very much. It is working now. Why it happens that? Should I refer to the Entity and not the table in JPQL statemen? Thank you very much again. – Spacemonkey Jun 12 '12 at 09:17
1

@Julián In JPQL should use the entity name, while SQL is use table name. – Pau Kiat Wee Jun 12 '12 at 09:18
@Julián exactly, in JPQL you do not work with tables and columns, but with entities and properties. Try using Criteria API with Hibernate Metamodel Generator instead of JPQL, it is compiler-safe and would not let you do such a mistakes. – d1e Jun 12 '12 at 09:20

score 4 · Answer 2 · answered Jun 12 '12 at 08:14

4

Well it is hard to say from your comments and you haven't posted any logging.

How about this:

Query query = entityManager.createQuery("SELECT u FROM users u WHERE u.id = (SELECT MAX(u.id) FROM users u)");

answered Jun 12 '12 at 08:14

maba

47,113
10
108
118

EdU · Answer 3 · 2013-08-22T22:30:22.220

1

Im Using that code:

    Query qry = em.createQuery("SELECT MAX(t.column) FROM Table t");
    Object obj = qry.getSingleResult();
    if(obj==null) return 0;
    return (Integer)obj;

Cause if there is no elements on Table "t", NullpointerException is throwing.

edited Aug 22 '13 at 22:30

answered Aug 22 '13 at 18:07

EdU

71
4

score -3 · Answer 4 · answered Feb 20 '14 at 14:21

-3

It's a good solution, but you have to use different names in tho two part of sql. Like this: "SELECT u FROM users u WHERE u.id = (SELECT MAX(u2.id) FROM users u2)"

answered Feb 20 '14 at 14:21

drawen

1

Unable to have correct value with SELECT query using MAX() in JPA

4 Answers4