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;

Koestler

I suggest using generated ID values. – Logicize

You should not swallow the exception, try use ex.printStackTrace() to print full error stack for more details. – Dialectical

@JMelnik Yes, I agree but I wanted to understand how JPA/JPQL it works trying to use persistent opreations and queries. – Koestler

@PauKiatWee Thanks for the advice, I just added the prinstStackTrace() to my code. – Koestler

@Julián look for books, documentation and finally implementation's source code. – Logicize

@JMelnik I did it but I did not find any MAX() example. – Koestler

@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". – Logicize

@JMelnik Yes, It is what I always do. I am reading this book [link]amazon.com/Pro-JPA-Mastering-Persistence-Technology/dp/… I just wanted to know if any of you can recongnize an error in my code. Thank you very much. – Koestler

Since when has "select * ..." been JPQL? A simple glance at the JPA spec or any JPA docs would reveal the valid syntax – Maculate

Answer 1

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.

Dialectical

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. – Koestler

Hello Pau, means that call to getMaxId() function always raises catch() in the Service layer (see update) – Koestler

Could you log the exception caught in the catch statement? – Kaslik

@PauKiatWee Where should I use users? In JPQL statement? It is not working either. I just changed. I got the same error with users. – Koestler

@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. – Koestler

@Julián In JPQL should use the entity name, while SQL is use table name. – Dialectical

@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. – Logicize

Answer 2

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

Zebulun

Answer 3

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.

Corcyra

Answer 4

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)"

Kemme