JPA Queries and Tips

Hello, how are you?

There are several JPAs “how to” that we can find on the internet, here in this blog, that teaches how to do several tasks with JPA.

Usually I see some people asking questions about Queries with JPA; usually to answer this kind of questions several links are provided trying to find a solution to the question.

Until today I could not find a blog post that puts together a good subject about queries with JPA, tips about performance/utilization, source code to download…
Challenge Accepted

Today we will see:

  • Page 2: Model classes and a class  that will generate database data
  • Page 3: Find method; Use the getReference method to get a better performance, displaying query parameters in the console with the log4j
  • Page 4: JPQL: Queries with simple parameters or objects, Joins, Order By, Navigating through relationships
  • Page 5: JPQL: Functions: AVG, COUNT, MAX, MIN, TRIM, SUM, UPPER, LOWER, MOD, LENGHT, SQRT; Using HAVING, GROUP BY
  • Page 6: JPQL: Filtering Conditions: LIKE, IN, DISTINCT, EMPTY, BETWEEN, NULL, MEMBER OF, EXISTS (Subqueries), ANY, ALL, SOME, CONCAT, CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP, LOCATE, SIZE, SUBSTRING
  • Page 7: JPA: NamedQuery, querying with dates, warnings about the getSingleResult method
  • Page 8: JPA: NativeQuery, NamedNativeQuery
  • Page 9: JPA: Complex Native Queries
  • Page 10: JPA: Optimizing queries with EJB
  • Page 11: JPA: Pagination
  • Page 12: JPA: Database Hints
  • Page 13: JPA: Creating a object from a query
  • Page 14: JPQL: Bulk Update and Delete
  • Page 15: JPA: Criteria

You will see that in every main class we will invoke the method “CodeGenerator.generateData()”. This class method only creates data in the database; with this data our queries will find the correct results.

In the last page of this post you will find the link to download the source code of this post.

In this post we will use JPA 2.0 with Hibernate as the provider. The database will be the HSQLDB and will be attached to this project. You can download the source code and run the project without the need of any extra configuration. We will not talk about how to set up the HSQLDB because the focus of this post is how to query data of the database.

This post will not use best practices of development in some points. The focus of this post will be to display how the JPA queries works.

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