JPA Queries and Tips

JPA: NamedQuery, querying with dates, warnings about the getSingleResult method

To avoid the repetition of queries codes, upgrade the performance and make easier to maintain the queries we can use the NamedQueries. A NamedQuery uses JPQL as syntax and it is declared in the entity class. It is easier to edit the query after an update in the class code.

If you want to do a query using a date as parameter you can send only the date object or you can pass an enum that will describe the date type (recommended).

Bellow you will see how to create and use a @NamedQuery and how to query with date:

package com.model;

import java.util.Date;

import javax.persistence.*;

@Entity
@NamedQuery(name="Dog.FindByDateOfBirth", query="select d from Dog d where d.dateOfBirth = :dateOfBirth")
public class Dog {

	public static final String FIND_BY_DATE_OF_BIRTH = "Dog.FindByDateOfBirth";

	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private int id;

	// get and set
}
package com.model;

import java.util.*;

import javax.persistence.*;

@Entity
@NamedQueries({
		@NamedQuery(name="Person.findByName", query="select p from Person p where p.name = :name"),
		@NamedQuery(name="Person.findByAge", query="select p from Person p where p.age = :age")})
})
public class Person {

	public static final String FIND_BY_NAME = "Person.findByName";
	public static final String FIND_BY_AGE = "Person.findByAge";

	@Id
	@GeneratedValue(strategy = GenerationType.AUTO)
	private int id;

        // get and set

}
package com.main;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.Query;
import javax.persistence.TemporalType;

import com.model.Dog;
import com.model.Person;

public class Page07 {
	public static void main(String[] args) {
		CodeGenerator.startConnection();

		CodeGenerator.generateData();

		EntityManager em = CodeGenerator.getEntityManager();

		int age = 70;
		List<Person> personByAge = getPersonByAge(em, 70);
		System.out.println("Found " + personByAge.size() + " person(s) with the age of: " + age);

		SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");
		Date dateOfBirth = null;
		try {
			dateOfBirth = formatter.parse("10/1/1995");
		} catch (ParseException e) {
			e.printStackTrace();
		}

		List<Dog> dogsByDayOfBirth = getDogsByDayOfBirth(em, dateOfBirth);
		System.out.println("Found " + dogsByDayOfBirth.size() + " dog with birth date of " + formatter.format(dateOfBirth));

		/*
		 * This queries will raise Runtime Exceptions
		 *
		 * em.createQuery("select p from Person p").getSingleResult(); // NonUniqueResultException
		 *
		 * em.createQuery("select p from Person p where p.name = 'JJJJ'").getSingleResult(); //NoResultException
		 */

		CodeGenerator.closeConnection();
	}

	@SuppressWarnings("unchecked")
	private static List<Dog> getDogsByDayOfBirth(EntityManager em, Date dateOfBirth) {
		Query query = em.createNamedQuery(Dog.FIND_BY_DATE_OF_BIRTH);
		query.setParameter("dateOfBirth", dateOfBirth, TemporalType.DATE);

		return query.getResultList();
	}

	@SuppressWarnings("unchecked")
	private static List<Person> getPersonByAge(EntityManager em, int age) {
		Query query = em.createNamedQuery(Person.FIND_BY_AGE);
		query.setParameter("age", age);

		return query.getResultList();
	}
}

About the code above:

  • If you have only one query you can use the @NamedQuery annotation; if you have more than one query you can use the @NamedQueries annotations.
  • When you do a query using a date object you can also use the TemporalType enum to detail the type of the date. For date queries you can use “java.util.Date” or “java.util.GregorianCalendar”.

getSingleResult()

Be careful when you use this method. It has a special way to handle two behaviors that it is easy to happen and both behaviors will raise an exception:

  • Find more than one object from the query result: NonUniqueResultException
  • Find no result: NoResultException

You always need to use a try/catch to avoid these exceptions to be thrown in the production environment.

If you want to see this exceptions in real time, in the code above you can find two commented queries; it will raise the exceptions bellow:

Exception in thread "main" <span style="text-decoration: underline;">javax.persistence.NonUniqueResultException</span>: result returns more than one elements
at org.hibernate.ejb.QueryImpl.getSingleResult(<span style="text-decoration: underline;">QueryImpl.java:287</span>)
Exception in thread "main" <span style="text-decoration: underline;">javax.persistence.NoResultException</span>: No entity found for query
at org.hibernate.ejb.QueryImpl.getSingleResult(<span style="text-decoration: underline;">QueryImpl.java:280</span>)

24 thoughts on “JPA Queries and Tips

  1. Great article but unfortunate page layout makes code samples impossible to read.
    You have to scroll left-right in every code window.
    This is torture! like making us look at code samples through keyhole.

  2. Hello Hebert,
    Firstly congrats for this post, really good tips.
    I just didn’t get why you’re flushing after every operation!? It could compromise your application, like, what if you want to rollback this updated object on setAddressToOtherPerson() method due to an error/exception on deletePerson()?

    • Hello matcouto,

      If a developer uses the flush method to many times that will decrease the application performance for sure.

      For this post it is used just to process the data correctly. [=

      A developer can use the Open Session in View pattern to avoid this or handle the transaction in the View. [=

      Thanks for passing by, and for the support.

  3. You’re very generous with the @SuppressWarnings(‘unchecked’) annotation. In 9/10 cases you don’t need it.
    E.g. on page 4, this:

    @SuppressWarnings(‘unchecked’)
    private static List listAllDogs(EntityManager em) {
    Query query = em.createQuery(‘select d from Dog d’, Dog.class);
    return query.getResultList();
    }

    Should be:

    private static List listAllDogs(EntityManager em) {
    TypedQuery query = em.createQuery(‘select d from Dog d’, Dog.class);
    return query.getResultList();
    }

    • Hello Wouter,

      Indeed I could change the @SuppressWarnings(‘unchecked’) with the TypedQuery interface.

      When I wrote this tutorial I did not had any knowledge about JPA 2.0.

      Two weeks after I wrote this I was reading the Pro JPA 2 book and found out about the TypedQuery.

      Thanks for the observation.

      It will help others that pass by here.

      [=

  4. Another couple of things:

    You shouldn’t introduce dublicated literal Strings:
    @Entity
    @NamedQueries({
    @NamedQuery(name=”Person.findByName”, query=”select p from Person p where p.name = :name”),
    @NamedQuery(name=”Person.findByAge”, query=”select p from Person p where p.age = :age”)})
    })
    public class Person {

    public static final String FIND_BY_NAME = “Person.findByName”;
    public static final String FIND_BY_AGE = “Person.findByAge”;
    ….

    Should be:
    @Entity
    @NamedQueries({
    @NamedQuery(name=Person.FIND_BY_NAME, query=”select p from Person p where p.name = :name”),
    @NamedQuery(name=Person.FIND_BY_AGE, query=”select p from Person p where p.age = :age”)})
    })
    public class Person {

    public static final String FIND_BY_NAME = “Person.findByName”;
    public static final String FIND_BY_AGE = “Person.findByAge”;
    ….

    You should make use of TypedQueries when using normal, named or even native queries:
    private static List getDogsByDayOfBirth(EntityManager em, Date dateOfBirth) {
    TypedQuery query = em.createNamedQuery(Dog.FIND_BY_DATE_OF_BIRTH, Dog.class);
    query.setParameter(“dateOfBirth”, dateOfBirth, TemporalType.DATE);
    return query.getResultList();
    }

    Instead of:
    @SuppressWarnings(“unchecked”)
    private static List getDogsByDayOfBirth(EntityManager em, Date dateOfBirth) {
    Query query = em.createNamedQuery(Dog.FIND_BY_DATE_OF_BIRTH);
    query.setParameter(“dateOfBirth”, dateOfBirth, TemporalType.DATE);
    return query.getResultList();
    }

    You don’t need to use EJB’s for transactions. It can easily be done in Java SE. You
    (basically) just need to start and end the transaction.

    The CriteriaBuilder API might be verbose but it allows you to create (compile-time) type-safe
    queries and build generic queries (for DAO’s for example). I personally can recommend JPA 2 by
    Mike Keith and Merrick Schincariol if you want a good book about JPA which includes the
    CriteriaBuilder API.

    • Hello again Wouter!

      I am really thankful for your observation. Really! :D

      About the TypedQuery I answered you in the topic above.

      About the NamedQuery name I did not though about that. It is a really better practice.

      Thanks for sharing your knowledge.

      I will consider to edit this post later! =D

  5. Hi! Nice article but few mistakes have crept into the queries:

    select p, count(p) from Person p join p.dogs d where d.weight > :weight group by p

    select min(weight), max(weight) from Dog

    select p, count(p) from Person p join p.dogs group by p.id having count(p) > :dogAmount

    the correct versions IMO are:
    select p, count(d) from Person p join p.dogs d where d.weight > :weight group by p

    select min(d.weight), max(d.weight) from Dog d

    select p, count(p.dogs) from Person p join p.dogs group by p.id having count(p) > :dogAmount

    • Hello, good afternoon.

      Some people around the world are reporting some problems about the source code.

      The code is hosted on google, maybe this is the problem.

      In the last page of this post I provided other link that you can use.

      Thanks for the feedback.

  6. Hi,

    I think the following code is not correct.

    private static Person findPersonByNameWithAllDogs(EntityManager em, String name) {
    Query query = em.createQuery(“select p from Person p join fetch p.dogs where p.name = :name”, Person.class);
    query.setParameter(“name”, name);
    return (Person) query.getSingleResult();
    }

    If the requirement is to find out the person who have dogs then query should be
    select p from Person p where p.name = :name and p.dogs is not empty

    Please let me know if my finding is correct.

    —————————————-
    The problem with query is that, if person has more than one dogs say 3 dogs, it will return 3 records, so the result cannot be retrived using query.getSingleResult() call. The query needs to be changed or query.getResultList() needs to be used to retrive list.
    Regards,
    Nirav Pradhan

Leave a Comment