Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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 {
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, 2012 at 8: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, 2012 at 9:00
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.
                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.  <code>entityManager.getTransaction().begin();             entityManager.persist(user);             entityManager.getTransaction().commit();</code> But querys are not working ok, I don't know why.
– Spacemonkey
                Jun 12, 2012 at 8:37
                Hello Pau, means that call to getMaxId() function always raises catch() in the Service layer (see update)
– Spacemonkey
                Jun 12, 2012 at 8:47
                @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, 2012 at 9:05
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)");
    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.
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)"
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.