javax.persistence.NoResultException: No entity found for query

Question

Before I posted this question, I already looked this, but I couldn't get what I was looking for.

I know that for the query I wrote there may exist only one row or none at all. So, there is not reason for me to use getResultList().

Here is my code:

String hql="from DrawUnusedBalance where unusedBalanceDate= :today";
Query query=em.createQuery(hql);
query.setParameter("today",new LocalDate());

DrawUnusedBalance drawUnusedBalance= 
    (DrawUnusedBalance)query.getSingleResult();// we can have only a
                                               // single datum per day
//`System.out.println(drawUnusedBalance.toString());`

The problem is, if there is no row, it throws an exception, and if not it works fine. I know the problem but I am also looking for the best solution.

What I wanted is, if there is no row in the DB I wanted to get a null object (instead of getting an exception) so I will insert a new data, if it is not null, I just want to update it.

There is one way to handle this, which I believe is not the right way to do it. It is: I will have a try-catch block and if it throws an exception I can write to insert new data in to the DB on the catch block. But I believe there will be a better way.

Solution 1

Yes. You need to use the try/catch block, but no need to catch the Exception. As per the API it will throw NoResultException if there is no result, and its up to you how you want to handle it.

DrawUnusedBalance drawUnusedBalance = null;
try{
drawUnusedBalance = (DrawUnusedBalance)query.getSingleResult()
catch (NoResultException nre){
//Ignore this because as per your logic this is ok!
}

if(drawUnusedBalance == null){
 //Do your logic..
}

Solution 2

When using java 8, you may take advantage of stream API and simplify code to

return (YourEntityClass) entityManager.createQuery()
....
.getResultList()
.stream().findFirst();

That will give you java.util.Optional

If you prefer null instead, all you need is

 ...
.getResultList()
.stream().findFirst().orElse(null);

Solution 3

You mentioned getting the result list from the Query, since you don't know that there is a UniqueResult (hence the exception) you could use list and check the size?

if (query.list().size() == 1) 

Since you're not doing a get() to get your unique object a query will be executed whether you call uniqueResult or list.

Solution 4

Another option is to use uniqueResultOptional() method, which gives you Optional in result:

String hql="from DrawUnusedBalance where unusedBalanceDate= :today";
Query query=em.createQuery(hql);
query.setParameter("today",new LocalDate());

Optional<DrawUnusedBalance> drawUnusedBalance=query.uniqueResultOptional();

Solution 5

When you don't know whether there are any results, use getResultList().

List<User> foundUsers = (List<User>) query.getResultList();
        if (foundUsers == null || foundUsers.isEmpty()) {
            return false;
        }
User foundUser = foundUsers.get(0);

View more solutions

Comments

[WowBow]

Thanks ManuPK ...But I still have to write some code which is creating an object and persist do a DB ..in the Catch block .... and I thought that will not be a good way to persist data from a catch block ... Though tnx for your response.

[ManuPK]

I feel there is nothing wrong in catching a checked exception and taking an alternative path. That why the JPA is giving you option on how you want to handle it. Actually the method you are looking for is available in hibernate which is not available with JPA.

[WowBow]

For now, I am using the query.list().isEmpty() to see if there is any data returned ... I thought it would be easy for the programmer if we were getting a null object like the get() method. Thanks

[Alex Barnes]

No problem. IsEmpty was obviously what I meant when I wrote my answer ;)

[ManuPK]

@user1017111 I got your problem in writing code inside the catch. I have updated the answer slightly to make the code readable.

[ManuPK]

@Alex: The only problem with this approach is that you need to use an additional query to get the count. It might be a performance overhead.

[WowBow]

I should have mentioned to you I almost did it in that way after I got some clue from U. Again, thanks for helping. And I better rename my user name!!

[Alex Barnes]

Why would you need an additional query to get the count? You've already got a list containing all of the entities matching the query.

[ManuPK]

@Alex : Ok. Then query.list() should be stored separably and checked. I got your point and you are right.I got confused by the if condition.

[Alex Barnes]

Yes. If you want to access an element from the collection you would need to store it to prevent a second query from occurring.

[Alex Barnes]

Interestingly this solutions is discussed here: stackoverflow.com/questions/2002993/jpa-getsingleresult-or-n‌​ull

[Alex Barnes]

I am of the opinion that if your database isn't enforcing that there WILL be exactly one row returned from SingleResult then you should be using list().

[WowBow]

the DB doesnt enforce that but the business logic does. You know what? In our framework, I found a method which will give me either the object or null ... thats what I was looking for .. Thank u guys again.

[WowBow]

The link was helpful. How couldnt I find it from the very beginning?

[Alex Barnes]

I'm not sure. I found it when I was looking to prove that my approach is probably a better one.

[Gunjan Shah]

@ManuPK : javax.persistence.NoResultException is not checked Exception. Its unChecked Exception.

[ACV]

OK, it's a suggestion rather than an answer. But might be useful to others.

[ACV]

When you don't know whether there are any results, use getResultList(). List<User> foundUsers = (List<User>) query.getResultList(); if (foundUsers == null || foundUsers.isEmpty()) { return false; } User foundUser = foundUsers.get(0);

[ACV]

This looks worse no?

[tjholmes66]

This is nonsense. I have tons of existing code that does calls against the database, and if a single row is not returned then the object is simply null. I am using Hibernate 5.1.0.Final and I've never seen this error until now. There definitely is a better way that using list and checking for size or catching for an exception. No thanks!

[Sergei Dubinin]

The question is related to getSingleResult(), not to .getResultList(). It's even stated in the description.

[Felipe Mosso]

He's still taking a single element from getResultList() though.