Cannot get the correct value using SELECT query using MAX () in JPA

Question

I am new to JPA and I have a problem when I try to query a database using the MAX () function. The code for my function is as follows. Can someone help me? Thanks.

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 use JPA, TopLink and Apache Derby. My method should return the maximum user id of the table.

Edit: I call this function from the 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."); } 

The value of user.setId() always 0.

Edit (2): Journal

  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 User entity 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; 

Answer 1

You can directly use the simpler 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 .

Answer 2

It’s hard to say from your comments, and you haven’t published any protocols.

How about this:

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

Answer 3

Im using this code:

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

The reason is, if there are no elements in table "t", a NullpointerException is thrown.

Answer 4

This is a good solution, but you need to use different names in two parts of sql. Like this: "SELECT u FROM users u WHERE u.id = (SELECT MAX (u2.id) FROM users u2)"