Ultimate JPA Queries and Tips List – Part 2

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>)

JPA: NativeQuery, NamedNativeQuery

The JPA uses the JPQL that does not have any database specific feature. How could you do a query invoking a specific database function when the JPQL just provides the commons functionalities between the databases?

“select p from Person p where p.name ~* :name” This query syntax is a valid query for Postgres database; if you try to run this query using a NamedQuery (with JPQL) you will see the exception bellow:

<strong><em>ERROR SessionFactoryImpl:422 - Error in named query: Person.FindByName</em></strong>
<strong><em>org.hibernate.QueryException: unexpected char: '~' [select p from com.model.Person p where p.name ~* :name]</em></strong>
<strong><em>at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:229)</em></strong>
<strong><em>at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:136)</em></strong>
<strong><em>at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:101)</em></strong>
<strong><em>at org.hibernate.engine.query.HQLQueryPlan.<init>(HQLQueryPlan.java:80)</em></strong>
<strong><em>at org.hibernate.engine.query.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:124)</em></strong>
<strong><em>at org.hibernate.impl.SessionFactoryImpl.checkNamedQueries(SessionFactoryImpl.java:547)</em></strong>
<strong><em>at org.hibernate.impl.SessionFactoryImpl.<init>(SessionFactoryImpl.java:411)</em></strong>
<strong><em>at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1842)</em></strong>
<strong><em>at org.hibernate.ejb.Ejb3Configuration.buildEntityManagerFactory(Ejb3Configuration.java:902)</em></strong>
<strong><em>at org.hibernate.ejb.HibernatePersistence.createEntityManagerFactory(HibernatePersistence.java:57)</em></strong>
<strong><em>at javax.persistence.Persistence.createEntityManagerFactory(Persistence.java:48)</em></strong>
<strong><em>at javax.persistence.Persistence.createEntityManagerFactory(Persistence.java:32)</em></strong>
<strong><em>at com.main.CodeGenerator.startConnection(CodeGenerator.java:27)</em></strong>
<strong><em>at com.main.Page05.main(Page05.java:12)</em></strong>
<strong><em>Exception in thread “main” javax.persistence.PersistenceException: [PersistenceUnit: JpaQuery] Unable to build EntityManagerFactory</em></strong>
<strong><em>at org.hibernate.ejb.Ejb3Configuration.buildEntityManagerFactory(Ejb3Configuration.java:911)</em></strong>
<strong><em>at org.hibernate.ejb.HibernatePersistence.createEntityManagerFactory(HibernatePersistence.java:57)</em></strong>
<strong><em>at javax.persistence.Persistence.createEntityManagerFactory(Persistence.java:48)</em></strong>
<strong><em>at javax.persistence.Persistence.createEntityManagerFactory(Persistence.java:32)</em></strong>
<strong><em>at com.main.CodeGenerator.startConnection(CodeGenerator.java:27)</em></strong>
<strong><em>at com.main.Page05.main(Page05.java:12)</em></strong>
<strong><em>Caused by: org.hibernate.HibernateException: Errors in named queries: Person.FindByName</em></strong>
<strong><em>at org.hibernate.impl.SessionFactoryImpl.<init>(SessionFactoryImpl.java:424)</em></strong>
<strong><em>at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1842)</em></strong><strong><em> </em></strong>
<strong><em>at org.hibernate.ejb.Ejb3Configuration.buildEntityManagerFactory(Ejb3Configuration.java:902)</em></strong>
<strong><em> ... 5 more</em></strong>

The solution to this kind of situation is to use a query that does not use JPQL as its syntax. With NativeQuery you will be able to do queries using the database syntax.

You can see in the code bellow how to use NativeQueries:

package com.main;

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

import com.model.Dog;

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

  CodeGenerator.generateData();

  EntityManager em = CodeGenerator.getEntityManager();

  String nameOfFirstPerson = getFirstPersonName(em);
  System.out.println(nameOfFirstPerson);

  Dog dog = getTopDogDescending(em);
  System.out.println(dog.getName());

  CodeGenerator.closeConnection();
 }

 /**
  * Returns the name of the first person using a native sql
  */
 private static String getFirstPersonName(EntityManager em) {
  Query query = em.createNativeQuery('select top 1 name from person');
  return (String) query.getSingleResult();
 }

 /**
  * Return an object using a native sql
  */
 private static Dog getTopDogDescending(EntityManager em) {
  Query query = em.createNativeQuery('select top 1 id, name, weight from dog order by id desc', Dog.class);
  return (Dog) query.getSingleResult();
 }
}

About the code above:

• Notice that we use a native query instead JPQL. We can have an entity as result of a native query. The method “getTopDogDescending” returns a Dog object after a native query invocation.

You also can create your native query as @NamedNativeQuery. The difference between NamedNativeQuery and NativeQuery is that the NamedNativeQuery is defined on its entity class, and you can have only one with that name.

The advantages of using a @NamedNativeQuery are:

• Easy to maintain the code: every query is on the class, if a class update an attribute it will be easier to update the query.
• Helps on a better performance: Once your query is already declared the JPA will map it and keep its syntax on the memory. The JPA will not need to “parse” you query every time that your project use it.
• Raises your code reutilization: Once you declare a @NamedNativeQuery you must provide the “name” parameter with a value and this name must be unique to the Persistence Unit scope (you set this value in the “persistence.xml” and it is used by the EntityManager).

You will see bellow how to declare a @NamedNativeQuery:

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')
})

@NamedNativeQuery(name='Person.findByNameNative', query='select id, name, age from person where name = :name')
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
}

You will be able to use the @NamedNativeQuery just like the @NativeQuery: “em.createNamedQuery(“Person.findByNameNative”);”.

Here it comes some bad news. Unfortunately the Hibernate does not have the @NamedNativeQuery implemented yet. If you try to run the code with this annotation you will see the exception bellow:

<em>Caused by: <span style='text-decoration: underline;'>org.hibernate.cfg.NotYetImplementedException</span>: Pure native scalar queries are not yet supported</em>
<em>at org.hibernate.cfg.annotations.QueryBinder.bindNativeQuery(<span style='text-decoration: underline;'>QueryBinder.java:140</span>)</em>
<em>at org.hibernate.cfg.AnnotationBinder.bindQueries(<span style='text-decoration: underline;'>AnnotationBinder.java:339</span>)</em>
<em>at org.hibernate.cfg.AnnotationBinder.bindClass(<span style='text-decoration: underline;'>AnnotationBinder.java:548</span>)</em>
<em>at org.hibernate.cfg.Configuration$MetadataSourceQueue.processAnnotatedClassesQueue(<span style='text-decoration: underline;'>Configuration.java:3977</span>)</em>
<em>at org.hibernate.cfg.Configuration$MetadataSourceQueue.processMetadata(<span style='text-decoration: underline;'>Configuration.java:3931</span>)</em>
<em>at org.hibernate.cfg.Configuration.secondPassCompile(<span style='text-decoration: underline;'>Configuration.java:1368</span>)</em>
<em>at org.hibernate.cfg.Configuration.buildMappings(<span style='text-decoration: underline;'>Configuration.java:1345</span>)</em>
<em>at org.hibernate.ejb.Ejb3Configuration.buildMappings(<span style='text-decoration: underline;'>Ejb3Configuration.java:1477</span>)</em>
<em>at org.hibernate.ejb.EventListenerConfigurator.configure(<span style='text-decoration: underline;'>EventListenerConfigurator.java:193</span>)</em>
<em>at org.hibernate.ejb.Ejb3Configuration.configure(<span style='text-decoration: underline;'>Ejb3Configuration.java:1096</span>)</em>
<em>at org.hibernate.ejb.Ejb3Configuration.configure(<span style='text-decoration: underline;'>Ejb3Configuration.java:278</span>)</em>
<em>at org.hibernate.ejb.Ejb3Configuration.configure(<span style='text-decoration: underline;'>Ejb3Configuration.java:362</span>)</em>

JPA: Complex Native Queries

You will be able to create complexes mapping to a NativeQuery; this mappings will return more than one class or value.

You can see bellow how our class map this complex results and how to execute a query with it:

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')})
})
@SqlResultSetMappings({
 @SqlResultSetMapping(name='personAndAdress',
   entities={
    @EntityResult(entityClass=Person.class),
    @EntityResult(entityClass=Address.class,
     fields={
      @FieldResult(name='id', column='ADDRESS_ID')
     }
    )
  }),
 @SqlResultSetMapping(name='personWithDogAmount',
  entities={@EntityResult(entityClass=Person.class)},
  columns={@ColumnResult(name='dogAmount')}
 )
})
public class Person {

 public static final String FIND_BY_NAME = 'Person.findByName';
 public static final String FIND_BY_AGE = 'Person.findByAge';
 public static final String MAPPING_PERSON_AND_ADDRESS = 'personAndAdress';
 public static final String MAPPING_DOG_AMOUNT = 'personWithDogAmount';

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

 // get and set
}

package com.main;

import java.math.BigInteger;

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

import com.model.Address;
import com.model.Person;

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

  CodeGenerator.generateData();

  EntityManager em = CodeGenerator.getEntityManager();

  Query query = em.createNativeQuery('select id, name, age, a.id as ADDRESS_ID, houseNumber, streetName ' +
             'from person p join address a on a.id = p.address_id where p.id = 1',
             Person.MAPPING_PERSON_AND_ADDRESS);

  Object[] result = (Object[]) query.getSingleResult();

  Person personWithAdress = (Person) result[0];
  Address address = (Address) result[1];

  System.out.println(personWithAdress.getName() + ' lives at ' + address.getStreetName());

  query = em.createNativeQuery('select p.id, p.name, count(0) as dogAmount ' +
                'from person p join dog d on p.id = d.person_id where name = 'Mark' ' +
                'group by p.id, p.name',
             Person.MAPPING_DOG_AMOUNT);

  result = (Object[]) query.getSingleResult();

  Person person = (Person) result[0];
  BigInteger total = (BigInteger) result[1];

  System.out.println(person.getName() + ' has ' + total + ' dogs');

  CodeGenerator.closeConnection();
 }
}

About the code above:

• With the “@SqlResultSetMapping” you will notify the JPA which entities we want as result. Notice that in the mapping “personAndAdress” we wrote the classes that will be returned. We also used an attribute named “@FieldResult”. This attribute will map query fields with the same name, in our query we got the person id and the address id. That is why we used the “@FieldResult” to notify the JPA to map the column ADDRESS_ID to the ID attribute of the Address class.
• In the “dogAmount” mapping we set the “@ColumnResult” attribute that notifies to the JPA that we will have an “extra column” in the query result and this “extra column” does not belong to any class.

JPA: Optimizing queries with EJB

Every time you do a query inside a transaction scope, the Persistence Context will keep the result “attached”; the Persistence Context will “watch” this objects just in case if any of this “attached” objects receive any update. All “attached” objects updates will be persisted in the database.

@PersistenceContext(unitName = 'myPU')
private EntityManager em;

@TransactionAttribute(TransactionAttributeType.REQUIRED)
public void editPersonName(Integer personId, String personNewName){
 Person person = em.find(Person.class, personId);
 person.setName(personNewName);
}

You can see in the code above that we did not need to invoke the “em.merge()” to update the person name in the database.

When we bring a collection from the database, usually to be displayed on a datatable or reports, all these objects will be attached to the Persistence Context. The process to make this objects attached will fire several processes, data validations and synchronizations. When higher is the number of objects, higher will be the allocated memory to the query result and higher will be the work of the Persistence Context to keep all this objects “attached”.

What is the point of having all these objects “attached” when their final destination will be to be sent to the view? When the objects leave the EJB project and go to the view project they will be considered “detached”. With this scenario we have an unnecessary work grabbing all the data from the database, make than “attached” and send them to the view to make them “detached”.

There is an easy way to do this objects to come from the database already detached. The advantage of this approach is that the Persistence Context will never waste time and container processor trying to synchronize the query result.

You can see in the code bellow how this solution works.

package com.main;

import java.util.List;

import javax.ejb.Stateless;
import javax.ejb.TransactionAttribute;
import javax.ejb.TransactionAttributeType;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import com.model.Person;

@Stateless
@TransactionAttribute(TransactionAttributeType.NOT_SUPPORTED)
public class PersonDAO {
 @PersistenceContext(unitName = 'myPU')
 private EntityManager em;

 @TransactionAttribute(TransactionAttributeType.REQUIRED)
 public void editPersonName(Integer personId, String personNewName){
  Person person = em.find(Person.class, personId);
  person.setName(personNewName);
 }

 @SuppressWarnings('unchecked')
 public List<Person> listAll(){
  Query query = em.createQuery('select p from Person p');
  return query.getResultList();
 }

 @SuppressWarnings('unchecked')
 public List<Person> listAllWithoutDogs(){
  Query query = em.createQuery('select p from Person p where p.dogs is empty');
  return query.getResultList();
 }
}

In the code above we got a DAO class that is an EJB. Our EJB has by default the lack of transaction (“@TransactionAttribute(TransactionAttributeType.NOT_SUPPORTED)”), with this kind of transaction the Persistence Context will not “attach” the result of the queries. The objects returned by the queries will be considered “detached”.

Notice that the annotation of the method “editPersonName” stills the same: “@TransactionAttribute(TransactionAttributeType.REQUIRED)”. This kind of transaction indicates to the EJB that a new transaction should be started if none has been started yet. You can set a transaction attribute for the class, but a method can override this attribute just like we did in the “editPersonName”. The method transaction definition will have priority over the class transaction definition.

JPA: Pagination

If you want to do the JPA pagination just do like the method bellow:

package com.main;

import java.util.List;

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

import com.model.Dog;

public class Page11 {

 @SuppressWarnings('unchecked')
 public static void main(String[] args) {
  CodeGenerator.startConnection();

  CodeGenerator.generateData();

  EntityManager em = CodeGenerator.getEntityManager();

  Query query = em.createQuery('select d from Dog d');

  List<Dog> dogs = query.getResultList();

  System.out.println('Total of dogs found: ' + dogs.size());

  query.setMaxResults(5);
  query.setFirstResult(0);

  List<Dog> fiveFirstDogs = query.getResultList();

  System.out.print('Total of dogs found: ' + fiveFirstDogs.size() + ' ');
  for (Dog dog : fiveFirstDogs) {
   System.out.print(dog.getName() + ' ');
  }System.out.println();

  query.setMaxResults(5);
  query.setFirstResult(5);

  List<Dog> fiveSecondDogs = query.getResultList();

  System.out.print('Total of dogs found: ' + fiveSecondDogs.size() + ' ');
  for (Dog dog : fiveSecondDogs) {
   System.out.print(dog.getName() + ' ');
  }

  CodeGenerator.closeConnection();
 }
}

About the code above:

• The method “setMaxResults” will set the amount of result that the query will return.
• The method “setFirstResult” will set the first line that will be brought.

In the first query we searched for all data in the database.

In the second query we got five results starting from the position 0.

In the last query we got the five results again but starting of the position 5.

Remember that the first position is always zero and not one.

JPA: Database Hints

The database vendors provided to us specifics functions named Hints. These Hints are very useful because they optimize queries and help us with other tasks. Each database got its own Hints and these values are not portable.

Bellow you can see some hints:

• SQLServer: OPTION ( OPTIMIZE FOR (@name = ‘Mark’, @age UNKNOWN) );
• Oracle: select /*+ first_rows (100) */ name
• MySQL: select * from person ignore index (col3_index)

Each database vendor will set the rules to its hints, rules like syntax and executions commands.

There are two ways to define hints:

package com.main;

import java.util.List;

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

import com.model.Dog;

public class Page12 {

 @SuppressWarnings('unchecked')
 public static void main(String[] args) {
  CodeGenerator.startConnection();

  CodeGenerator.generateData();

  EntityManager em = CodeGenerator.getEntityManager();

  Query query = em.createQuery('select d from Dog d');
  query.setHint('org.hibernate.timeout', 1000);

  List<Dog> dogs = query.getResultList();
  System.out.println('Found ' + dogs.size() + ' dogs');

  CodeGenerator.closeConnection();
 }
}

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', hints={@QueryHint(name='org.hibernate.timeout', value='1000')})
})
@SqlResultSetMappings({
 @SqlResultSetMapping(name='personAndAdress',
   entities={
    @EntityResult(entityClass=Person.class),
    @EntityResult(entityClass=Address.class,
     fields={
      @FieldResult(name='id', column='ADDRESS_ID')
     }
    )
  }),
 @SqlResultSetMapping(name='personWithDogAmount',
  entities={@EntityResult(entityClass=Person.class)},
  columns={@ColumnResult(name='dogAmount')}
 )
})
public class Person {

 public static final String FIND_BY_NAME = 'Person.findByName';
 public static final String FIND_BY_AGE = 'Person.findByAge';
 public static final String MAPPING_PERSON_AND_ADDRESS = 'personAndAdress';
 public static final String MAPPING_DOG_AMOUNT = 'personWithDogAmount';

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

 // get and set
}

You can set a hint in the @NamedQuery and directly in the Query.

You must always remember the disadvantage of the Hints. Once you set the Hint on your NamedQuery your code will not be portable to others database vendors. One solution would be using only Query instead NamedQuery. Before you add the hint to the Query you could check if the current database supports that hint; you could use a properties file to define the current database of the application.

Continue to the third part of the series

Reference: JPA Queries and Tips from our JCG partner Hebert Coelho at the uaiHebert blog.