Enterprise Java

Ultimate JPA Queries and Tips List – Part 3

Before you read the third part , remember the first and second part of the series

JPA: Creating a object from a query

The JPA allow us to create objects inside a query, just with the values that we need:
 
 
 
 
 
 
 

package com.model;

public class PersonDogAmountReport {
 private int dogAmount;
 private Person person;

 public PersonDogAmountReport(Person person, int dogAmount) {
  this.person = person;
  this.dogAmount = dogAmount;
 }

 public int getDogAmount() {
  return dogAmount;
 }

 public void setDogAmount(int dogAmount) {
  this.dogAmount = dogAmount;
 }

 public Person getPerson() {
  return person;
 }

 public void setPerson(Person person) {
  this.person = person;
 }
}
package com.main;

import java.util.List;

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

import com.model.PersonDogAmountReport;

public class Page13 {

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

  CodeGenerator.generateData();

  EntityManager em = CodeGenerator.getEntityManager();

  Query query = em.createQuery('select new com.model.PersonDogAmountReport(p, size(p.dogs)) from Person p group by p.id');

  List<PersonDogAmountReport> persons = query.getResultList();
  for (PersonDogAmountReport personReport : persons) {
   System.out.println(personReport.getPerson().getName() + ' has: ' + personReport.getDogAmount() + ' dogs.');
  }

  CodeGenerator.closeConnection();
 }
}

Notice that inside our query we create a new object. The good news is that you can create any object, it does not need to be an entity. You just need to pass the full path of the class and the JPA will handle new class instantiation.

This is a very useful functionality to use with reports that you need of specifics fields but those fields do not exists in the entity.

JPQL: Bulk Update and Delete

Sometimes we need do execute an operation to update several rows in a table database. E.g. update all persons with the age higher than 70 and define than as elderly.

You can run a Bulk Update/Delete like this:

package com.main;

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

import com.model.Person;

public class Page14 {

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

  CodeGenerator.generateData();

  EntityManager em = CodeGenerator.getEntityManager();

  em.clear();
  Query query = em.createQuery('update Person p set p.name = 'Fluffy, the destroyer of worlds!'');
  query.executeUpdate();

  query = em.createQuery('select p from Person p where p.id = 4');

  Person person = (Person) query.getSingleResult();

  System.out.println('My new name is: ' + person.getName());

  query = em.createQuery('delete from Person p where p.dogs is empty');
  query.executeUpdate();

  query = em.createQuery('select p from Person p');

  System.out.println('We had 6, but was found ' + query.getResultList().size() + ' persons in the database');

  CodeGenerator.closeConnection();
 }
}

The cascade option will not be triggered in this scenario; you will not be able to delete an object and hope that the JPA will delete the cascaded objects in the relationship. The database data integrity belongs to the Developer once we talk about bulk operations. If you want to remove an object from the database and its relationships you need to update the object setting the relationship to null before executing the delete.

We can define this kind of operation as a very dangerous operation. If we comment the line 17 (“em.clear(); “), we will see that the name of the person stills the same after the update.

The “problem” is that the Persistence Context keeps all the data in memory “attached”, but these kinds of bulk operations do not update the Persistence Context. We will have an operation done in our database but was not reflected at our Persistence Context yet. This kind of situation could give us synchronization problems.

Picture the following scenario:

  • A transaction is started.
  • Person A is persisted in the database through the method em.persist().
  • Person B has its name updated to “Louanne” through the method em.merge().
  • Person A will be removed by a bulk delete.
  • Person B has its name updated to “Fernanda” by a bulk update.
  • Transaction finishes.

What would happen in this scenario? The Person A was removed by the bulk operation but the Persistence Context will try to persist it the database. The Person B had its name updated to Fernanda but the Persistence Context will try to update to Louanne.

There is no default behavior for kind of situation, but there are solutions that we can use to avoid these problems:

  • Start a new transaction before the bulk operation: With a new transaction started just to the bulk operation, when this operations finishes the updates/deletes will be executed in the database. You will not have an entity manager trying to use data that has not been written to the database yet.
  • Call the “entityManager.clear()” method before the bulk operation: if you invoke this method you will force the Persistence Context to release all cached data. After the bulk operation if you use the find method, the Persistence Context will get the data from the database because you cleaned the cached data before the bulk operation.

Invoke the clear() method is not a silver bullet, if you use it too many times it can give you performance problems. If your Persistence Context has a lot of cached objects and you invoke the clear() method, your Persistence Context will have to do a lot of “trips” to get the needed data again. The Persistence Context has a wonderful data cache control and should take advantage of it.

The bulk operations are an option that will help us in several situations, but you must use it carefully.

JPA: Criteria

JPA is a good framework do run your queries, but Criteria it is not a good way of doing queries with JPA.

JPA Criteria is too much verbose, complicated and it need too much code to do some basic queries. Unfortunately it is not easy as Hibernate Criteria.

The code bellow will show an easy Criteria code, but we will not see more of this subject. I already read 3 books about EJB/JPA and not a single one book talked about it.

The code bellow has a criteria code:

package com.main;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.criteria.CriteriaQuery;

import com.model.Person;

public class Page15 {

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

  CodeGenerator.generateData();

  EntityManager em = CodeGenerator.getEntityManager();

  CriteriaQuery criteriaQuery = em.getCriteriaBuilder().createQuery();
  criteriaQuery.select(criteriaQuery.from(Person.class));

  List<Person> result = em.createQuery(criteriaQuery).getResultList();

  System.out.println('Found ' + result.size() + ' persons.');

  CodeGenerator.closeConnection();
 }
}

I am sorry to say what I think about Criteria like this here, but at this point I do not see simplicity to use Criteria in your code unless for A ListALL.

The code above code easily applied to a Generic DAO, it would be easier to list all objects by it.

The following link shows a Generic DAO in an application: Full WebApplication JSF EJB JPA JAAS.

The end!

I hope this post might help you.

Click here do download the source code.

You will not need to edit any configuration to run the code of this post, just import it to the Eclipse.

If you got any doubt/comment just post it bellow.

See you soon.

Useful Links:

  • http://www.mkyong.com/hibernate/how-to-display-hibernate-sql-parameter-values-log4j/
  • http://stackoverflow.com/questions/1659030/how-to-get-the-database-time-with-jpql
  • Pro EJB 3: Java Persistence API, Mike Keith, Merrick Schincariol
  • Enterprise JavaBeans 3.0 – Richard Monson-Haefel, Bill Burke

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

Hebert Coelho

Senior Java Development, with 4 certifications and a published book about JSF (portuguese only). Founder of the blog uaiHebert.com visited from more than 170 different countries.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

3 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Milan Aleksic
Milan Aleksic
11 years ago

The reason why I like to use CriteriaQuery is when you need to generically create SQL based on multiple filtering rules. Criteria beats hand-written JPA queries hands down in those cases

UaiHebert Jake
11 years ago
Reply to  Milan Aleksic

Hello Milan, do you know EasyCriteria? http://easycriteria.uaihebert.com/

Is a framework (Open Source) that helps with JPA Criteria.

It makes easier to handle the JPA Criteria and keep you code cleaner and less verbose.

=D

Alonso
Alonso
10 years ago

Thanks for sharing your knowledge. I have read the full series and i had learned new ways to create queries, but i dont know how you are avoiding SQL injection with the code that you provide. I think i need to learn more!

thanks anyway

Back to top button