How to resolve javax.persistence.NoResultException OR javax.persistence.NonUniqueResultException in JPA
In case you are getting the following exception in JPA -
Then it is most likely that your database does not have even a single record matching your query OR you have too many results being returned.
Normally this happens when the method javax.persistence.Query.getSingleResult() is used. The method javax.persistence.Query.getSingleResult() is very strict in its usage. Its restrictive as in it allows for one and only one record to be fetched as part of its call. In case there is no result obtained on running the query and a getSingleResult() method is executed then a javax.persistence.NoResultException is thrown.
On the other hand, if more than one result is obtained on running this query then a javax.persistence.NonUniqueResultException is thrown.
So, what one needs to ensure is that when a Query.getSingleResult() method is used then the application is expecting a single result only. For example, the query can be a fetch with a where clause on a single value of a primary or unique key for a record which the application is sure of that it exists in the database.
One scenario where getSingleResult() applies perfectly is say there is a field the screen where multiple 'userIds' are displayed in a dropdown. The values filling up this dropdown are populated from the 'USER' table when the page gets loaded. This ensures that all userIds are existing in the database. On selection of any dropdown value the application needs to fetch the corresponding user record and display its values. UserId is a unique type of field on USER table i.e. a where clause written for matching a userId will return only a single user's record. The code for fetching from USER table will be as below (with checks for NoResultException and NonUniqueResultException)...
Salient points for the above program -
1. javax.persistence.Query type of Query object is created which queries for a USER record based on userId passed to it
2. The userId is passed as a query parameter and the value for demonstration is set as 1
3. query.getSingleResult() is invoked to execute the query created in point 1 and the object returned is assigned to an object of type User which is the JPA entity for USER table
4. Then the two exceptions are checked -
(i) NoResultException for the case in which the query does not return any result
(ii) NonUniqueResultException for the case in which more than one results are returned
javax.persistence.NoResultException: No entity found for query
OR
javax.persistence.NonUniqueResultException: result returns more than one elements
OR
javax.persistence.NonUniqueResultException: result returns more than one elements
Then it is most likely that your database does not have even a single record matching your query OR you have too many results being returned.
Normally this happens when the method javax.persistence.Query.getSingleResult() is used. The method javax.persistence.Query.getSingleResult() is very strict in its usage. Its restrictive as in it allows for one and only one record to be fetched as part of its call. In case there is no result obtained on running the query and a getSingleResult() method is executed then a javax.persistence.NoResultException is thrown.
On the other hand, if more than one result is obtained on running this query then a javax.persistence.NonUniqueResultException is thrown.
So, what one needs to ensure is that when a Query.getSingleResult() method is used then the application is expecting a single result only. For example, the query can be a fetch with a where clause on a single value of a primary or unique key for a record which the application is sure of that it exists in the database.
Query query = entityManager.createQuery(" FROM User where userId=:userId");
query.setParameter("userId", 1);
try{
User userRecord = (User) query.getSingleResult();
} catch (NoResultException nre) {
// Code for handling NoResultException
} catch (NonUniqueResultException nure) {
// Code for handling NonUniqueResultException
}
Salient points for the above program -
1. javax.persistence.Query type of Query object is created which queries for a USER record based on userId passed to it
2. The userId is passed as a query parameter and the value for demonstration is set as 1
3. query.getSingleResult() is invoked to execute the query created in point 1 and the object returned is assigned to an object of type User which is the JPA entity for USER table
4. Then the two exceptions are checked -
(i) NoResultException for the case in which the query does not return any result
(ii) NonUniqueResultException for the case in which more than one results are returned