Unable to have correct value with SELECT query using MAX() in JPA
47,711
Solution 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
.
Solution 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)");
Solution 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.
Comments
-
Spacemonkey about 4 years
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;