I love getting comments from my readers. Here are the best comments of July 2015.

Spring Data JPA Tutorial: Creating Database Queries With the JPA Criteria API

Stay in course

The previous part of this tutorial described how we can create database queries with named queries.

This tutorial has already taught us how we can create static database queries with Spring Data JPA. However, when we are writing real-life applications, we have to able to create dynamic database queries as well.

This blog post describes how we can create dynamic database queries by using the JPA Criteria API. We will also implement a search function that has two requirements:

  • It must return todo entries whose title or description contains the given search term.
  • The search must be case-insensitive.

Let’s start by ensuring that Maven creates the JPA static metamodel classes when we compile our project.

Additional Reading:

If you are not familiar with Spring Data JPA, you should read the following blog posts before you continue reading this blog post:

Creating the JPA Static Metamodel Classes

A static metamodel consists of classes that describe the entity and embeddable classes found from our domain model. These metamodel classes provide static access to the metadata that describes the attributes of our domain model classes.

We want to use these classes because they give us the possibility to create type-safe criteria queries, but we don’t want to create them manually.

Luckily, we can create these classes automatically by using the Maven Processor Plugin and the JPA Static Metamodel Generator. We can configure these tools by following these steps:

  1. Add the Maven Processor Plugin (version 2.2.4) declaration to the plugins section of the pom.xml file.
  2. Configure the dependencies of this plugin and add the JPA static metamodel generator dependency (version 4.3.8) to the plugin’s dependencies section.
  3. Create an execution that invokes the plugin’s process goal in the generate-sources phase of the Maven default lifecycle.
  4. Ensure that the plugin runs only the org.hibernate.jpamodelgen.JPAMetaModelEntityProcessor. This annotation processor scans our entities and embeddable classes, and creates the static metamodel classes.

The configuration of the Maven Processor Plugin looks as follows:

<plugin>
	<groupId>org.bsc.maven</groupId>
	<artifactId>maven-processor-plugin</artifactId>
	<version>2.2.4</version>
	<executions>
		<execution>
			<id>process</id>
			<goals>
				<goal>process</goal>
			</goals>
			<phase>generate-sources</phase>
			<configuration>
				<processors>
					<processor>org.hibernate.jpamodelgen.JPAMetaModelEntityProcessor</processor>
				</processors>
			</configuration>
		</execution>
	</executions>
	<dependencies>
		<dependency>
			<groupId>org.hibernate</groupId>
			<artifactId>hibernate-jpamodelgen</artifactId>
			<version>4.3.8.Final</version>
		</dependency>
	</dependencies>
</plugin>

When we compile our project, the invoked annotation processor creates the JPA static metamodel classes to the target/generated-sources/apt directory. Because our domain model has only one entity, the annotation processor creates only one class called Todo_. The source code of the Todo_ class looks as follows:

package net.petrikainulainen.springdata.jpa.todo;

import java.time.ZonedDateTime;
import javax.annotation.Generated;
import javax.persistence.metamodel.SingularAttribute;
import javax.persistence.metamodel.StaticMetamodel;

@Generated(value = "org.hibernate.jpamodelgen.JPAMetaModelEntityProcessor")
@StaticMetamodel(Todo.class)
public abstract class Todo_ {

	public static volatile SingularAttribute<Todo, ZonedDateTime> creationTime;
	public static volatile SingularAttribute<Todo, String> createdByUser;
	public static volatile SingularAttribute<Todo, ZonedDateTime> modificationTime;
	public static volatile SingularAttribute<Todo, String> modifiedByUser;
	public static volatile SingularAttribute<Todo, String> description;
	public static volatile SingularAttribute<Todo, Long> id;
	public static volatile SingularAttribute<Todo, String> title;
	public static volatile SingularAttribute<Todo, Long> version;

}

Let’s move and find out how we can create database queries with the JPA criteria API.

Creating Database Queries With the JPA Criteria API

We can create database queries with the JPA Criteria API by following these steps:

  1. Modify the repository interface to support queries that use the JPA Criteria API.
  2. Specify the conditions of the invoked database query.
  3. Invoke the database query.

Let’s get started.

Modifying the Repository Interface

The JpaSpecificationExecutor<T> interface declares the methods that can be used to invoke database queries that use the JPA Criteria API. This interface has one type parameter T that describes the type of the queried entity.

In other words, if we need to modify our repository interface to support database queries that use the JPA Criteria API, we have to follow these steps:

  1. Extend the JpaSpecificationExecutor<T> interface.
  2. Set the type of the managed entity.

Example:
The only Spring Data JPA repository of our example application (TodoRepository) manages Todo objects. After we have modified this repository to support criteria queries, its source code looks as follows:

import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.repository.Repository;

interface TodoRepository extends Repository<Todo, Long>, JpaSpecificationExecutor<Todo> {
}

After we have extended the JpaSpeciticationExecutor interface, the classes that use our repository interface get access to the following methods:

  • The long count(Specification<T> spec) method returns the number of objects that fulfil the conditions specified by the Specification<T> object given as a method parameter.
  • The List<T> findAll(Specification<T> spec) method returns objects that fulfil the conditions specified by the Specification<T> object given as a method parameter.
  • The T findOne(Specification<T> spec) method returns an object that fulfils the conditions specified by the Specification<T> object given as a method parameter.
The JpaSpecificationExecutor<T> interface declares also two other methods that are used to sort and paginate objects that fulfil the conditions specified by the Specification<T> object. We will talk more about these methods when we learn to sort and paginate our query results.

Additional Reading:

Let’s find out how we can specify the conditions of the invoked database query.

Specifying the Conditions of the Invoked Database Query

We can specify the conditions of the invoked database query by following these steps:

  1. Create a new Specification<T> object.
  2. Set the type of the queried entity as the value of the type parameter (T).
  3. Specify the conditions by implementing the toPredicate() method of the Specification<T> interface.

Example 1:

If we have to create a criteria query that returns Todo objects, we have to create the following specification:

new Specification<Todo>() {
	@Override
	public Predicate toPredicate(Root<Todo> root, 
					CriteriaQuery<?> query, 
					CriteriaBuilder cb) {
		//Create the query by using the JPA Criteria API
	}
}

The obvious next question is:

Where should we create these Specification<T> objects?

I argue that we should create our Specification<T> objects by using specification builder classes because:

  • We can put our query generation logic into one place. In other words, we don’t litter the source code of our service classes (or other components) with the query generation logic.
  • We can create reusable specifications and combine them in the classes that invoke our database queries.

Example 2:

If we need to create a specification builder class that constructs Specification<Todo> objects, we have to follow these steps:

  1. Create a final TodoSpecifications class. The name of this class isn’t important, but I like to use the naming convention: [The name of the queried entity class]Specifications.
  2. Add a private constructor the created class. This ensures that no one can instantiate our specification builder class.
  3. Add static specification builder methods to this class. In our case, we will add only one specification builder method (hasTitle(String title)) to this class and implement it by returning a new Specification<Todo> object.

The source code of the TodoSpecifications class looks as follows:

import org.springframework.data.jpa.domain.Specification;

import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;

final class TodoSpecifications {

    private TodoSpecifications() {}

    static Specification<Todo> hasTitle(String title) {
        return new Specification<Todo>() {
            @Override
            public Predicate toPredicate(Root<Todo> root, 								
								CriteriaQuery<?> query, 
								CriteriaBuilder cb) {
				//Create the query here.
            }
        }
    }
}

If we use Java 8, we can clean up the implementation of the hasTitle(String title) method by using lambda expressions. The source code of our new specification builder class looks as follows:

import org.springframework.data.jpa.domain.Specification;

import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;

import org.springframework.data.jpa.domain.Specification;

final class TodoSpecifications {

    private TodoSpecifications() {}

    static Specification<Todo> hasTitle(String title) {
        return (root, query, cb) -> {
        	//Create query here
        };
    }
}

Let’s find out how we can invoke the created database query.

Invoking the Created Database Query

After we have specified the conditions of the invoked database query by creating a new Specification<T> object, we can invoke the database query by using the methods that are provided by the JpaSpecificationExecutor<T> interface.

The following examples demonstrates how we can invoke different database queries:

Example 1:

If we want to get the number of Todo objects that have the title ‘foo’, we have to create and invoke our database query by using this code:

Specification<Todo> spec = TodoSpecifications.hasTitle("foo");
long count =  repository.count(spec);

Example 2:

If we want to the get a list of Todo objects that have the title ‘foo’, we have to create and invoke our database query by using this code:

Specification<Todo> spec = TodoSpecifications.hasTitle("foo");
List<Todo> todoEntries =  repository.findAll(spec);

Example 3:

If we want to get the Todo object whose title is ‘foo’, we have to create and invoke our database query by using this code:

Specification<Todo> spec = TodoSpecifications.hasTitle("foo");
List<Todo> todoEntries =  repository.findOne(spec);

If we need to create a new specification that combines our existing specifications, we don’t have to add a new method to our specification builder class. We can simply combine our existing specifications by using the Specifications<T> class. The following examples demonstrates how we can use that class:

Example 4:

If we have specifications A and B, and we want to create a database query that returns Todo objects which fulfil the specification A and the specification B, we can combine these specifications by using the following code:

Specification<Todo> specA = ...
Specification<Todo> specB = ...
List<Todo> todoEntries =  repository.findAll(
	Specifications.where(specA).and(specB)
);

Example 5:

If we have specifications A and B, and we want to create a database query that returns Todo objects which fulfil the specification A or the specification B, we can combine these specifications by using the following code:

Specification<Todo> specA = ...
Specification<Todo> specB = ...
List<Todo> todoEntries =  repository.findAll(
	Specifications.where(specA).or(specB)
);

Example 6:

If we have specifications A and B, and we want to create a database query that returns Todo objects which fulfil the specification A but not the specification B, we can combine these specifications by using the following code:

Specification<Todo> specA = ...
Specification<Todo> specB = ...
List<Todo> searchResults = repository.findAll(
	Specifications.where(specA).and(
		Specifications.not(specB)
	)
);

Let’s move on and find out how we can implement the search function.

Implementing the Search Function

We can implement our search function by following these steps:

  1. Modify our repository interface to support criteria queries.
  2. Create the specification builder class that creates Specification<Todo> objects.
  3. Implement the service method that uses our specification builder class and invokes the created database queries by using our repository interface.

Let’s start by modifying our repository interface.

Modifying Our Repository Interface

We can make the necessary modifications to our repository interface by following these steps:

  1. Extend the JpaSpecificationExecutor<T> interface.
  2. The type of the queried entity to Todo.

The source code of our repository interface looks as follows:

import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.repository.Repository;

import java.util.List;
import java.util.Optional;

interface TodoRepository extends Repository<Todo, Long>, JpaSpecificationExecutor<Todo> {

    void delete(Todo deleted);

    List<Todo> findAll();

    Optional<Todo> findOne(Long id);

    void flush();

    Todo save(Todo persisted);
}

Let’s move on and create the specification builder class.

Creating the Specification Builder Class

We can create a specification builder class that fulfils the requirements of our search function by following these steps:

  1. Create the specification builder class and ensure that it cannot be instantiated.
  2. Create a private static getContainsLikePattern(String searchTerm) method and implement it by following these rules:
    • If the searchTerm is null or empty, return the String “%”. This ensures that if the search term is not given, our specification builder class will create a specification that returns all todo entries.
    • If the search isn’t null or empty, transform the search term into lowercase and return the like pattern that fulfils the requirements of our search function.
  3. Add a static titleOrDescriptionContainsIgnoreCase(String searchTerm) method to the specification builder class and set its return type to Specification<Todo>.
  4. Implement this method by following these steps:
    1. Create a Specification<Todo> object that selects todo entries whose title or description contains the given search term.
    2. Return the created Specification<Todo> object.

The source code or our specification builder class looks as follows:

import org.springframework.data.jpa.domain.Specification;

final class TodoSpecifications {

    private TodoSpecifications() {}

    static Specification<Todo> titleOrDescriptionContainsIgnoreCase(String searchTerm) {
        return (root, query, cb) -> {
            String containsLikePattern = getContainsLikePattern(searchTerm);
            return cb.or(
                    cb.like(cb.lower(root.<String>get(Todo_.title)), containsLikePattern),
                    cb.like(cb.lower(root.<String>get(Todo_.description)), containsLikePattern)
            );
        };
    }

    private static String getContainsLikePattern(String searchTerm) {
        if (searchTerm == null || searchTerm.isEmpty()) {
            return "%";
        }
        else {
            return "%" + searchTerm.toLowerCase() + "%";
        }
    }
}

Let’s find out how we can implement the service method that creates and invokes our database query.

Implementing the Service Method

The first thing that we have to do is to create an interface called TodoSearchService. This interface declares one method called findBySearchTerm(). This method takes the search term as a method parameter and returns a list of TodoDTO objects. The source code of the TodoSearchService interface looks as follows:

import java.util.List;

public interface TodoSearchService {

    List<TodoDTO> findBySearchTerm(String searchTerm);
}

We can implement this interface by following these steps:

  1. Create a RepositoryTodoSearchService class, implement the TodoSearchService interface, and annotate the class with the @Service annotation.
  2. Add a private final TodoRepository field to the created class.
  3. Create a constructor that injects a TodoRepository object to the created field by using constructor injection.
  4. Override the findBySearchTerm() method. Annotate the method with the @Transactional annotation and ensure that the transaction is read-only.
  5. Implement the findBySearchTerm() method by following these steps:
    1. Get the Specification<Todo> object by invoking the static titleOrDescriptionContainsIgnoreCase() method of the TodoSpecifications class.
    2. Get the todo entries whose title or description contains the given search term by invoking the findAll() method of the JpaSpecificationExecutor interface. Pass the created Specification<Todo> object as a method parameter.
    3. Transform the list of Todo objects into a list of TodoDTO objects and return the created list.

The source of our service class looks as follows:

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

import static net.petrikainulainen.springdata.jpa.todo.TodoSpecifications.titleOrDescriptionContainsIgnoreCase;

@Service
final class RepositoryTodoSearchService implements TodoSearchService {

    private final TodoRepository repository;

    @Autowired
    public RepositoryTodoSearchService(TodoRepository repository) {
        this.repository = repository;
    }

    @Transactional(readOnly = true)
    @Override
    public List<TodoDTO> findBySearchTerm(String searchTerm) {
		Specification<Todo> searchSpec = titleOrDescriptionContainsIgnoreCase(searchTerm);
        List<Todo> searchResults = repository.findAll(searchSpec);
        return TodoMapper.mapEntitiesIntoDTOs(searchResults);
    }
}

Let’s move on and find out when we should create our database queries by using the JPA Criteria API.

Why Should We Use the JPA Criteria API?

This tutorial has already taught us how we can create database queries by using the method names of our query methods, the @Query annotation, and named queries. The problem of these query generation methods is that we cannot use them if we have to create dynamic queries (i.e queries that don’t have a constant number of conditions).

If we need to create dynamic queries, we have to create these queries programmatically, and using the JPA Criteria API is one way to do it. The pros of using the JPA Criteria API are:

  • It supports dynamic queries.
  • If we have an existing application that uses the JPA Criteria API, it is easy to refactor it to use Spring Data JPA (if we want to).
  • It is the standard way to create dynamic queries with the Java Persistence API (this doesn’t necessarily matter, but sometimes it does matter).

That sounds impressive. Unfortunately, the JPA Criteria API has one big problem:

It is very hard to implement complex queries and even harder to read them.

That is why I think that we should use criteria queries only when it is absolutely necessary (and we cannot use Querydsl).

Let’s move on and summarize what we have learned from this blog post.

Summary

This blog post has taught us six things:

  • We can create the JPA static metamodel classes by using the Maven Processor Plugin.
  • If we want to invoke queries that use the JPA Criteria API, our repository interface must extend the JpaSpecificationExecutor<T> interface.
  • We can specify the conditions of our database queries by creating new Specification<T> objects.
  • We should create our Specification<T> objects by using specification builder classes.
  • We can combine Specification<T> objects by using the methods provided by the Speficications<T> class.
  • We should use criteria queries only when we don’t have a choice.

The next part of this tutorial describes how we can create database queries with Querydsl.

P.S. You can get the example application of this blog post from Github.

If you want to learn how to use Spring Data JPA, you should read my Spring Data JPA tutorial.

About the Author

Petri Kainulainen is passionate about software development and continuous improvement. He is specialized in software development with the Spring Framework and is the author of Spring Data book.

About Petri Kainulainen →

140 comments… add one

  • Your project structure is amazing. I was blown away by how well you laid it out. I am still trying to soak it all in and I’m going to keep poring over it to really understand it all. I expect that it will serve as a foundation for future Spring 3.1 JPA based projects. I can’t thank you enough for putting together such a wonderful tutorial where you took the time to set up an elegant foundation.

    Reply
    • Stone,

      thanks for your comment. It is always nice to hear that I could actually help someone to learn something new. Also, it would be nice to hear which things are hard to understand so that I could try to provide a bit better explanation.

      Reply
      • It took some time for me to grasp how the Specification stuff worked, I still don’t think I’m very clear on that. It also took a little bit of time to understand how the environment was getting initialized in the ApplicationContext (I’m still a bit of a novice when it comes to Spring configurations, and from what I’ve gathered, it seems that Spring parsed the data from the @ImportResource and @PropertySource specifications to initialize the environment). One other issue that I had was figuring out how to access all of the pages and when it was deployed locally (I had to prefix all of the form:action and href values to include the project name prefix). Lastly, the verify statements in the test cases were also new to me, so I learned about Mockito from this project as well.

        I’d like to give back to you — I found a few issues in the code that you may want to include. I kept getting a NPE in AbstractPathImpl.get() method. To get around it, I had to move the Person_ class into the same package as Person (~.model). I also changed the return statement on the PersonRepositoryService.update() method to “return personRepository.save(person);
        ” instead of “return person;” — the value was never getting updated in the database. This necessitated changing the PersonRepositoryServiceTest.update() method to:

        PersonDTO updated = PersonTestUtil.createDTO(PERSON_ID, FIRST_NAME_UPDATED, LAST_NAME_UPDATED);
        Person person = PersonTestUtil.createModelObject(PERSON_ID, FIRST_NAME, LAST_NAME);

        when(personRepositoryMock.findOne(updated.getId())).thenReturn(person);
        when(personRepositoryMock.save(person)).thenReturn(person);

        Person returned = personService.update(updated);

        verify(personRepositoryMock, times(1)).findOne(updated.getId());
        verify(personRepositoryMock, times(1)).save(person);
        verifyNoMoreInteractions(personRepositoryMock);

        assertPerson(updated, returned);

        Finally, there was a simple type in the PersonRepositoryServiceTest.assertPerson() — the last assert statement should read “assertEquals(expected.getLastName(), actual.getLastName());”.

        Again, thank you so much for such a thoughtful and well designed tutorial — I learned a lot.

        Reply
        • Stone,

          thanks your comment. I am planning to add more links to resources which contains tutorials and other material about the used libraries and frameworks. I will also check out the issues you mentioned later today. By the way, did you use the H2 in memory database when you noticed these problems? In any case, thanks for your contribution. :)

          Reply
          • I didn’t use H2, I used MySQL.

        • Stone,

          I tried to reproduce the problem you were having with the update() method of Person RepositoryPersonService class by using MySQL 5.5.19. Unfortunately I was not able to reproduce it. In my environment the updates made to the Person instance were updated to the database.

          The thing is that you should not have to call the save() method of PersonRepository when you are updating the information of a person. The reason for this is that Hibernate will automatically detect the changes made to persistent objects during the transaction and synchronize the changes with the database after the transaction is committed.

          Check the Working with Objects Section of the Hibernate reference manual for more details: http://docs.jboss.org/hibernate/core/4.0/manual/en-US/html/objectstate.html#objectstate-overview

          Common causes for the problem you were having are:

          You do not have got transaction at all (the @Transactional annotation is not used either at method or class level)

          The transaction is read only (The readOnly property of the @Transactional annotation is set to true)

          The state of the updated entity is not persistent (Check the link to the reference manual for more details).

          I am wondering if this advice helped you? (I am bit of a perfectionist so it would be a personal victory for me to help you to remove that unnecessary call to the save() method of PersonRepository).

          Reply
  • Hi Petri,

    Thank you for the very nice explanation. For Spring Data JPA + criteria queries, is this the only signature available ?

    List repository.findAll(Specification s);

    If I know that my query will return only a single result, can I use something like

    T repository.find(Specification s);

    I tried find(), but I exceptions, e.g. “No property find found for type class domain.Customer”.

    So, is findAll() the only available query method with the Specification parameter?

    Thanks,
    David

    Reply
  • Hi Petri,

    Nice tutorial with good and clear examples which gives good insight on Spring Data JPA. Thanks for that.

    I tried to implement jpa criteria and got NPE on org.hibernate.ejb.criteria.path.AbstractPathImpl.get(AbstractPathImpl.java:141)
    Apparently, I got exact same exception, when i tried to run your project – tutorial 4.

    Then I moved my staticMetamodel to the package where my entity is and this exception went away. But the simple criteria is also not returning anything. I did check the table and can retrive data before I apply criteria to filter. So I am stumped. any clues ?

    Reply
    • Hi Amol,

      Thanks for your comment. I finally ended up moving the static meta model class to the same package where the Person entity is located. Hopefully this will finally fix the issue with the NPE you (and Stone) mentioned. Thanks for the bug report. I should have done this ages ago but somehow I managed to forget this issue.

      In my experience, if a query is not returning the correct results, the problem is almost always in the created criteria. It would be helpful if you could give a bit more detailed description about your problem. The answers to following questions would help me to get a better idea of the situation:

      • What kind of entities do you have?
      • What kind of condition are you trying to implement with JPA criteria API?
      Reply
      • Well, I managed to fix that. It was with the created criteria as you rightly said.
        Thanks again.

        Reply
        • Hi Amol,

          great to hear that you managed to solve your problem.

          Reply
  • Hei Petri of all the tutorials about jpa I found yours has been the most helpful! But I have still a doubt, we will see if you can find a solution: If I want to create an specification of one object that it is a parameter in another object how can I do it? for example: imagine that your object person has another attribute that is adress, and Adress has as attributes street and number, how can I create an specification that obtain all the people that live in one street?

    Thanks in advance!!!

    Reply
    • Hi Albert,

      Thanks for your comment. It was nice to see that that you enjoyed my tutorials. The answer to your question is: it can be done. I am currently at work but I will describe the solution after I get back to home.

      Reply
      • Hi Petri,

        I am also trying to implement similar criteria. Hoping to see some input from you.

        Many Thanks,

        Reply
    • Hi Albert,

      Lets assume that you have got a Person object which has a reference to a Address object. So, the source code of the Person class could be something like this:


      @Entity
      @Table("persons")
      public class Person {
      private Address address;

      public Person() {

      }

      public Address getAddress() {
      return address;
      }
      }

      Now, the source code of the Address class is following:


      @Embeddable
      public class Address {

      private String street;

      private int number;

      public Address() {

      }

      public String getStreet() {
      return street;
      }

      public int getNumber() {
      return number;
      }
      }

      As you said, you want search all persons which are living in the same street. This criteria is built like this (Note that I am not using the static meta model in this example):


      public class PersonSpecifications {

      public static Specification livesInGivenStreet(final String street) {

      return new Specification () {
      @Override
      public Predicate toPredicate(Root
      personRoot, CriteriaQuery< ?> query, CriteriaBuilder cb) {
      return cb.equal(root.

      get("address").get("street"), street);
      }
      };
      }
      }

      In this solution I have assumed the the database column containing the street in which the person lives is found from persons database table. Is the case for you or are you using a separate entity object in your domain model instead of component?

      Reply
      • this is exactly what I was looking for. I was having problems in this line:
        “root.get(“address”).get(“street”), street);”

        I didn’t know how to reach the street from address, I thought I had to make an “innerjoin” but I have seen that if I execute your code the innerjoin is created alone when the query is created.

        Thanks a lot for your help!!!! I’ll try now to make it a little more complicate using the metamodel and using classes than extend from other classes, we will see if it works fine…thanks again.

        Reply
  • Hi Petri,
    I have 3 tables as Check, User and UserDetail

    Check – main search table has
    userid
    and other fields

    User table has
    userid
    and other fields

    UserDetail table has
    userid
    firstname
    lastname

    the domain model is

    Check class
    has User

    User class
    has userDetail

    I am trying to build predicate to perform search on firstname and that is giving me trouble.
    my predicate is as below
    predicate = cb.equal(root.get(“user”).get(“userid”).get(“userDetail”).get(“firstname”), searchName)
    this throws exception as Illegal attempt to dereference path source [null,user]
    Any clues on how to build the search with these 3 tables ? Do i have to use some Join while building predicate ?

    If I create a link between Check and UserDetail table by adding userdetail in Check then following works fine
    predicate = cb.equal(root.get(“userDetail”).get(“firstname”), searchName)

    Thanks in advance

    Reply
    • Hi Amol,

      If I understood your domain model correctly, you can obtain the correct predicate with this code:


      cb.equal(root.<User>get(“user”).<UserDetail>get(“userDetail”).<String>get(“firstname”), searchName);

      Reply
      • Thanks for the reply Petri but that throws exception as “Unable to resolve attribute [userDetail] against path”. ?

        Reply
        • Hi Amol,

          it seems that I would have to see the source code in order to help you. It seems that the attribute called userDetail cannot be resolved. This means that the property called userDetail is not found.

          This seems a bit puzzling because I assumed that the Check class contains a property called user, the User class contains a property called userDetail and the UserDetail class contains property firstName. Are you trying to navigate from Check to UserDetail when building the Predicate?

          Reply
          • Hi Petri,
            Here is the code snippet. I have removed unwanted comments, fields and getter/setter methods.
            You are right CheckRecord has User has userDetail has firstName.

            @PersistenceUnit(name = "core-dal")
            public class CheckRecord {
            private Long id;
            private String status;
            private Date expiry;
            private User user;

            @ManyToOne(optional = true, fetch = FetchType.LAZY, targetEntity = User.class)
            @JoinColumn(name = "userId")
            public User getUser() {
            return user;
            }
            }

            @Entity
            @Table(name = "UserTable")
            @PersistenceUnit(name = "core-dal")
            public class User {
            private Long id;
            private String username;
            private Account account;
            private UserDetail userDetail;

            @OneToOne(mappedBy = "user", cascade = CascadeType.ALL)
            public UserDetail getUserDetail() {
            return userDetail;
            }
            }

            @Entity
            @Table(name = "UserDetail")
            @PersistenceUnit(name = "core-dal")
            public class UserDetail {
            private Long id;
            private String firstName;
            private String lastName;
            private User user;
            public String getFirstName() {
            return firstName;
            }
            }

            Note: Added code tags and removed some unnecessary setters – Petri

          • Hi Amol,

            I noticed that the getUser() method of UserDetail class is missing. Does it look like this:


            @OneToOne
            @JoinColumn("userId")
            public User getUser() {
            return user;
            }

  • Hi Petri,
    yes it is like that. I removed that and others so my post is not too big.

    Reply
    • In this case the following specification builder should work:


      public class CheckRecordSpecifications {

      public static Specification<CheckRecord> firstNameIs(final String searchTerm) {

      return new Specification<CheckRecord>() {
      @Override
      public Predicate toPredicate(Root<CheckRecord> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
      return cb.equal(root.<User>get(“user”).<UserDetail>get(“userDetail”).<String>get(“firstName”), searchTerm);
      }
      };
      }
      }

      Reply
  • spot on.. that did work.. I think last night eclipse was culprit as it was not picking up the latest class file.
    Many Thanks for your help.

    Reply
    • Amol,

      Great!

      Reply
  • Hi Petri!

    I have been working in this issue last week, but when I thought it was working well suddenly this problem has appeared: “org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.dao.InvalidDataAccessApiUsageException: Illegal attempt to dereference path source [null];”

    What I´m doing is this:
    Path path = root.get(CustomsOrder_.messageType);
    predicates.add(cb.equal(path.get(MessageType_.group), messageGroup));

    My CustomsOrder has a MessageType, this type belongs to a group and I would like to find a CustomsOrder by the group.
    Do u have an idea what can happen here?

    Tahnks in advance

    Reply
    • Hi Albert,

      the exception you mentioned is thrown when the path used to get the compared property is not correct. You should definitely try to create the predicate without using the static meta model. Does the following work or is the same exception thrown?

      predicates.add(cb.equal(root.get("messageType").get("group"), messageGroup));

      Also, are you saying that CustomsOrder class has a property called messageType, and MessageType has a property called group?

      Reply
      • Hi Petri!
        I found the problem, after some hours checking the solution I have discovered that MessageType is an enumerator that is grouped by another enumerator that it is MessageGroup, as I didn’t do this code I assumed both were regular classes. So when I was getting the MessageType I could not reach the MessageGroup.My finall solution is to obtain from MessageType all the messages that belong to a group and search by list of messages instead of group. If you think that another solution more elegant exists please make me know it.
        Code I have used:

        List list =getMessagesTypeByGroup(group); //obtain msg by group selected
        predicates.add(root.get(CustomsOrder_.messageType).in(list));

        Thanks for your replying.

        Reply
        • Hi Albert,

          Good to see that you were able to solve your problem.

          Reply
  • Hi Petri,
    Thank you very much for your detailed article.
    I am trying to implement the below scenario in my project, but i dont see any distinct key word in spring JPA.

    I mean in below way.
    E.g- select c from customer
    customer table– has firstname, lastname as columns.
    Now I need to pull data as below.
    select distinct c.lastname from customer c ;

    Is there anyway we can achieve it ? I mean using NamedQuery or Specifications.

    Thank you in advance.

    Reply
    • Hi Raghu,

      You have two options for implementing this:

      • Use the DISTINCT keyword of JPQL when you are creating query by using the @NamedQuery or @Query annotation.
      • Call the disctinct() method of the CriteriaQuery class in your specification builder method (The toPredicate() method of the Specification interface gets a reference of the CriteriaQuery object as a parameter).

      JPQL Example:

      SELECT DISTINCT p FROM Person p WHERE...

      Criteria API with Specification Builder:

      public class PersonSpecifications {
      public static Specification lastNameIsLike(final String searchTerm) {

      return new Specification () {
      @Override
      public Predicate toPredicate(Root
      personRoot, CriteriaQuery< ?> query, CriteriaBuilder cb) {
      query.distinct(true);
      //Build Predicate
      }
      };
      }
      }

      In your case, I would add the following method to the CustomerRepository interface (or whatever your repository interface is):

      @Query("SELECT DISTINCT c.lastName FROM Customer c")
      public List<String> findLastNames();

      Reply
  • Hi Petri,

    Really loved your way of explanation and thanks for sharing, I read the entire comments and was really useful.

    I have a below scenerio, how can we achieve conditions on one-to-many relation ship, below is predicate method for me.

    public static Specification hasRole(final String roleName) {
    return new Specification() {
    public Predicate toPredicate(Root root, CriteriaQuery query, CriteriaBuilder builder) {
    if (null != roleName) {
    Expression<List> roleExpression = root.get(User_.roles);
    // TODO: Need to do how can I join one-to-many relationship ?
    }
    return builder.conjunction();
    }
    };
    }

    Reply
    • Hi Dhana,

      thanks for your comment. It is nice to hear that you found this tutorial useful.

      If you are trying to fetch all users who have a certain role, you can do this by using the isMember() method of the CriteriaBuilder class:

      Predicate hasRole = builder.isMember(roleName, roleExpression);

      Also, check out Collections in JPQL and Criteria Queries.

      Reply
      • Hi Petri,
        thanks, I have issue here, roleName is a String, but roleExpression is Expression<List<Role>>.

        The generic bounded is expecting to pass Role object instead of roleName. Didn’t find solution, here is the my code.

        public static Specification<User> hasRole(final String roleName) {
        return new Specification<User>() {
        public Predicate toPredicate(Root<User> root,
        CriteriaQuery< ?> query, CriteriaBuilder builder) {
        if (null != roleName) {
        Expression<List<Role>> roleExpression = root
        .get(User_.roles);
        return builder.isMember(roleName, roleExpression);
        }
        return builder.conjunction();
        }
        };
        }

        List users = userRepository.findAll(where(isActiveUser()).and(hasAddress(address)).and(hasRole(roleName)));

        Reply
        • Hi Dhana,

          Is the Role an enum? If it is, you have to change this line:

          return builder.isMember(roleName, roleExpression);

          To:

          return builder.isMember(Role.valueOf(roleName), roleExpression);

          Let me know if this works. Also, if some exception is thrown, it would be useful to know what it is.

          Reply
  • No Petri, Role is an entity which is mapped as one to many for the User.

    Reply
    • Hi Dhana,

      My bad. I totally missed the one to many relationship. You can use join for this purpose. Try the following code:

      //Roles is a list?
      ListJoin<User, Role> roleJoin = root.joinList(User_.roles);
      //Role name matches with the role name given as a parameter
      return builder.equal(roleJoin.<String>get(Role_.name), roleName);

      Reply
  • Thanks, it works now, appreciate your help.

    Reply
  • I noticed than you cannot have the following 2 extensions simultaniously : (1) a custom extension of the JpaRepository (to introduce a new generic method for all repo) and (2) implements JpaSpecificationExecutor.
    If you try it you get exception when Spring builds your repo:
    Error creating bean with name ‘pilotRepository’: FactoryBean threw exception on object creation; nested exception …
    Caused by: org.springframework.data.mapping.PropertyReferenceException: No property delete found for type pilot.business.model.Pilot
    In the preceeding comment David may have encountered the same problem …

    Reply
    • Hi,
      I stumbled in the same type of problem. Do you have a solution for the problem in the meantime?

      TIA

      Reply
      • Follow these steps:

        1. Create an interface for the custom methods that are introduced to all repositories and extend both JpaRepository and JpaSpecificationExecutor interfaces in it.
        2. Create an implementation for the base repository that extends the SimpleJpaRepository class.
        3. Create repository factory bean for your new base repository class.
        4. Create the actual repository interface and extend the base repository interface.

        Let me know if this solved your problem.

        Reply
  • Thanks a lot for the tutorial. I’m just getting started with Spring Data JPA and I was having a hard time getting around how to extend it to do more complicated queries. This was a huge help.

    Reply
    • Alex,

      It is great to hear that you liked this tutorial. Also, you might be interested to read my blog entry about Spring Data JPA and Querydsl. To be honest, the JPA Criteria API can be a bit hard to use if you have to create a really complicated query with it. Querydsl offers an solution to this problem.

      Reply
  • Hi Petri,
    Awesome post! Most appreciated.
    What is i want to match an id (for example) against a list of ids (List)? i can’t seem to use isMember cause the list is no good.

    Reply
    • Hi Lev,

      It is nice to hear that you like this blog entry. About your problem, you can implement the in condition by using the in() method of the Expression interface. Here is a little example about this:

      public class PersonSpecifications {
      	public static Specification<Person> idIsIn(final List<Long> ids) {
      
      		return new Specification<Person> () {
      			@Override
      			public Predicate toPredicate(Root root<Person>, 
                                                    CriteriaQuery< ?> query, 
                                                    CriteriaBuilder cb) {
      				//This does not work if list is empty.
                                      //Remember to check it here.
      				return root.get("id").in(ids);
      			}
      		};
      	}
      }
      

      Reply
      • works perfectly! thanks a lot

        Reply
        • You are welcome!

          Reply
  • I believe that “Before going in to the details, I will introduce the source code of my static *metal* model class” is really supposed to be “Before going in to the details, I will introduce the source code of my static *meta* model class”, no?

    Reply
    • You are right. Fixed it. Thanks for pointing this out.

      Reply
  • Hi Petri,
    I have two tables, one is a Users table and the other one is a Settings table. Per every user i have a settings row (they user the same id as PK).

    The user has a created_date field and in the settings he has a number_of_days field which specifies the total days he had for his trial period (may vary from user to user). i wanna find all the users that passed their trial period but i need to get the created_date java Date object and number_of_days as an Integer object and do something like createdDate.addDays(numDays) and compare that with now’s date.

    How can i get a Date object from root.get(“createdDate”)?

    and how can i reference another table from one specification and get number_of_days as an int from withing the same specification?

    Thanks a lot,
    Lev

    Reply
    • I have not done any calculations with dates by using the JPA criteria API. Actually, it seems that JPA 2.0 does not support arithmetic operations for date operands. I suggest that you change your domain model by following these steps:

      1. Add a new Date property called lastDayOfTrialPeriod to the Settings entity. The value of this property can be calculated when a new user is added to the system. Also, you can use the numberOfDays property to calculate the value of this property to existing users.
      2. Remove the numberOfDays property from the Settings entity.

      This way you could get the information you need by using a very simple query. If you want to use the JPA Criteria API, you can use the following specification builder class (I assume that the Person entity has a property called settings and that the relationship between the Person and Settings entities is one-to-one):

      public class PersonSpecifications {
        public static Specification<Person> trialPeriodHasPassed() {
      
          return new Specification<Person> () {
            @Override
            public Predicate toPredicate(Root root<Person>, 
                                         CriteriaQuery< ?> query, 
                                         CriteriaBuilder cb) {
              return cb.lessThan(
      	  root.<Settings>get("settings").<Date>get("lastDayOfTrialPeriod"), 
      	  cb.currentDate()
      	);
            }
          };
        }
      }
      


      Did this solve your problem?

      Reply
      • Haha, this is actually what we ended up doing.
        Yeah this did to do the trick!

        thanks :)

        Reply
        • You are welcome!

          Reply
  • Hi Petri,

    thanx for your valuable article…

    I’m currently working on a DAO.findAll with “specific branch fetching” (on an entity tree, with more than 3 level of depth, any relation declared as lazy, and you only want to fetch some custom one),

    have you ever worked on this topic (not covered by your article… and not really mentionned in Spring Data… except if we include Root.JoinType.LEFT logic in Specification… which can be discussed… :), if yes any recommandation ?

    the only valuable post i’ve found is this one:

    http://jdevelopment.nl/fetching-arbitrary-object-graphs-jpa-2/comment-page-1/#comment-4208

    which works perfectly for a findOne, but doesn’t work for a findAll (duplicated entities fetched… :(

    ++

    i42

    Reply
    • Unfortunately I have not worked on this kind of structure before. Also, it is hard to give “good” answers without seeing the domain model and understanding what kind of query you want to create. I assume that you want to fetch a list of entities and use join to “overwrite” the lazy fetching. Am I correct?

      You added a link to a blog entry and told that it works perfectly when you fetch only one entity. I assume that when you try to get a list of entities, the list contains duplicate entities. Is this correct?

      If my assumption is correct, you might want to call the distinct(boolean distinct) method of the CriteriaQuery class and set the value of the distinct method parameter to true.

      P.S. This is just a shot in the dark.

      Reply
  • Hi Petri, (and thx for your quick reply…)

    “you want to fetch a list of entities and use join to “overwrite” the lazy fetching. Am I correct?”

    yes

    “when you try to get a list of entities, the list contains duplicate entities. Is this correct?”

    yes

    “call the distinct(boolean distinct) method of the CriteriaQuery”

    already tried, this method is rejected by the DB telling us in return:
    -> com.sybase.jdbc3.jdbc.SybSQLException: TEXT, IMAGE, UNITEXT and off-row Java datatypes may not be selected as DISTINCT.

    your both previous assumptions were right Petri…

    to simplify, the model would be similar to something like: Class O[List<A> lA, List lZ] A[B b, C c] Z[X x, Y y], everything (one/many) constraintly declared as lazy.

    We want the DAO.findAllBranch method to be able to fetch branch with calls like findAllBranch(O.class, “lA.b”) or findAllBranch(O.class, “lZ.y”), fetching then all O objets + only associated list + sub property.

    currently using join operator produces duplicates O instances for each A or Z instances… :(

    P.S: “A shot in the dark”… you mean http://www.youtube.com/watch?v=uzzQp6slqqQ (yeah, me to I love techno musik) :D

    Reply
  • No further comment Petri ?

    Reply
    • I have to confess that I forgot this comment. I am sorry about that.

      The information you gave changes things a bit because I thought that you were trying to get a list of objects from the “middle” of the entity tree.

      It seems that you want to select all O objects, a list of A objects and all properties of A (or the same stuff for Z).

      Am I correct? What is the search criteria which is used to select the O objects or do you simply want to get all O objects found from the database?

      Unfortunately I cannot give you an answer straight away. However, I could try to replicate this scenario and see if I can figure out an answer. In order to do this, I need to see the real domain model because otherwise I might create a solution which works for me but is totally useless to you (especially since it seems that you storing binary data to the database). Is this possible?

      Reply
  • Hey,

    no worries mate…

    to re-sum up the scene:

    – it’s a findAll,
    – for a massive model (not postable, hundreds of entities… you can switch it to any one you want indeed, not really mattering…),
    – ALL relations declared as Lazy,
    – we have 100% configurable & generic Criteria building with Root.JoinType.LEFT (let’s say, to simplify the case, no other use of Criteria than entities’ joining)

    -> issue: method is returning duplicates entries with JPA

    – setDistinct(true) is refused by JDBC driver->DB command

    and (new info…), if I use 100% Hibernate processing, (through
    – (Session) getEntityManager().getDelegate();
    – and then org.hibernate.Criteria.setResultTransformer( Criteria.DISTINCT_ROOT_ENTITY);)

    the findAll behaves correctly, no duplicates entries are return, result is fully consistent !

    my temporary empirik conclusion are:

    JPA facade for building findAll is functionnaly under-efficient/functionnal than its delegated/Wrapped full Hibernate processing…

    so Petri, do you like challenges ?

    :)

    i42

    Reply
    • It seems that I have to try to solve this puzzle. :) However, I am not sure if I can use any time to this before next weekend. I will keep you posted in any case.

      Reply
    • I have started working on this.

      Reply
    • I managed to reproduce this problem with H2 database as well. However, when I used the distinct(true) method, the criteria query returned correct results.

      This issue is discussed with more details in this Stack Overflow question. It seems that you should either continue using your current approach or use sub queries as suggested in the answer of the linked question.

      Reply
      • Thx a lot Petri for investing my problem…

        unfortunately the “distinct” call is refused by Sybase 15 (not allowed on Text/CLOB value), so I guess I’ll keep my generic Hibernate delegated session implementation (using setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY)) which is 100% efficiently working…

        BTW, the link you’ve mentionned is the one which decided me previously to pass through JPA and use 100% Hibernate querying…

        Once again, thanx Petri for your concern and your help, all the best…

        Dimitri42

        Reply
        • You are welcome! I agree that you should keep your current implementation because it is working for you. There is no point in trying to find another solution which might not exist.

          Reply
  • Hi Petri, this is an amazing help from your part, I’ve been following your blog and I’ve read most of your notes and well, definitely there is always something else to learn, and this is my case.

    This is similar to Raghu’s case [July 31, 2012 at 4:47 am EDIT] about to distinct, except I had not chance to use it in my repository interface because precisely I had to work with Specification idea (filtering conditions in a dynamic query using OR operator — but was resolved with your help) and I see that distinct is for all the table’s fields and I just need to distinct some of them because business logic. I’ve read a lot notes from Sn Google without luck, so, that is the challenge, to use DISTINCT for some fields through Specification class. Is there any solution?

    And also is there a way to get the query built from Specification just to confirm what Specification object will send to the Repository for its execution.

    Any help I’d really appreciate.
    The best.
    /JRomero.

    Reply
    • I have to confess that I am not sure if this can be done with the JPA Criteria API. Do you need to get the full information of the entities or do you want to get only the information stored to the distinct fields?

      Reply
  • Hi Petri,

    is there a simple way of finding greatest size of one-to-many relationship using max function. for example, need to find one customer who has more orders done so far. i.e, customer.orders is one-to-many relationship.

    Reply
    • Hi Dhana,

      The first solution which came to my mind had these steps:

      1. Sort the customers in descending order by using the number of orders.
      2. Get the first result.

      Then I noticed the greatest() method of the CriteriaBuilder class.

      Then I came up with a solution which uses the JPA Criteria API and Spring Data JPA (I have not tried it out so I have no idea if it works).

      The source code of my specification builder class looks as follows:

      
      public class CustomerSpecifications {
        public static Specification<Customer> customerWithMostOrders() {
      
          return new Specification<Customer> () {
            @Override
            public Predicate toPredicate(Root root<Customer>, 
                                         CriteriaQuery< ?> query, 
                                         CriteriaBuilder cb) {
      		
      		//Note that this can return multiple customers 
      		//if they have the same number of orders.
                      return cb.equal(
      			cb.size(root.get("orders")), 
      			cb.greatest(cb.size(root.get("orders")))
      		);
            }
          };
        }
      }
      
      

      Let me know if this did the trick.

      Reply
  • Liked your response Petri, thanks a lot making your blog being read very interesting. :)
    It didn’t work somehow, I have got the following error and sql generated is

    
    SELECT
        customer0_.id AS id1_0_ ,
        customer0_.email AS email2_0_ ,
        customer0_.firstName AS firstNam3_0_ ,
        customer0_.lastName AS lastName4_0_ ,
        customer0_.notes AS notes5_0_ ,
        customer0_.phone AS phone6_0_
    FROM
        CUSTOMER customer0_
    WHERE
        (
            SELECT
                COUNT(orders1_.customer_id)
            FROM
                ITEM_ORDER orders1_
            WHERE
                customer0_.id = orders1_.customer_id
        ) = MAX(
            (
                SELECT
                    COUNT(orders2_.customer_id)
                FROM
                    ITEM_ORDER orders2_
                WHERE
                    customer0_.id = orders2_.customer_id
            )
        )
    
    


    SQL Error [42574]: expression not in aggregate or GROUP BY columns: CUSTOMER0_.ID
    expression not in aggregate or GROUP BY columns: CUSTOMER0_.ID

    Reply
  • I found the right sql query and need to design the speicification.

    Ref from: http://www.techonthenet.com/sql/max.php

    
    SELECT
        query1. customer_id
    FROM
        (
            SELECT
                Customer_id ,
                COUNT(*) AS order_count
            FROM
                item_order
            GROUP BY
                item_order.Customer_id
        ) query1 ,
        (
            SELECT
                MAX(query2.order_count) AS highest_count
            FROM
                (
                    SELECT
                        Customer_id ,
                        COUNT(*) AS order_count
                    FROM
                        item_order
                    GROUP BY
                        item_order.Customer_id
                ) query2
        ) query3
    WHERE
        query1.order_count = query3.highest_count
    ;
    
    
    Reply
    • Like you already figured out, this problem can be solved by using the GROUP BY clause (check this StackOverflow question for more details about this).

      The CriteriaQuery class has a groupBy() method which can be used for this purpose.

      The problem is that I am not sure how this can be used in the original specification builder method. One option would be to to add this line to that method:


      query.groupBy(root.get("id"));

      The problem is that the “main” query does not use aggregate functions. I have to confess that I have no idea how this could work.

      I also tried to find some examples about the correct usage of the greatest() method but I had no luck. Do you have any other ideas?

      Reply
  • Resolved with a different solution, pls sugest if you have better way,
    wrote a specification on Order

    
    public static Specification highestOrdersGroupedByCustomer() {
    
    	return new Specification() {
    		@SuppressWarnings("unchecked")
    		@Override
    		public Predicate toPredicate(Root root,
    				CriteriaQuery< ?> query, CriteriaBuilder builder) {
    
    			CriteriaQuery query1 = (CriteriaQuery) query;
    			query1.select(root.get(Order_.customer).get(Customer_.id));
    
    			query1.groupBy(root.get(Order_.customer).get(Customer_.id));
    			query1.orderBy(
    				builder.desc(builder.count(root.get(Order_.id)))
    			);
    			return null;
    		}
    	};
    }
    
    

    And my repository supports to execute the above specification which returns the following.

    
    List customerId = repository.findAll(
    	Long.class, 
    	OrderSpecifications.highestOrdersGroupedByCustomer(), 
    	1
    );// 1 represents max rows
    Assert.assertFalse(customerId.isEmpty());
    System.out.println(customerId.get(0));
    
    

    Later do a find by id of this customer Id.

    sql generated is like this,

    
    SELECT
        order0_.customer_id AS col_0_0_
    FROM
        ITEM_ORDER order0_
    GROUP BY
        order0_.customer_id
    ORDER BY
        COUNT(order0_.ORDER_ID) DESC FETCH FIRST 1 ROWS ONLY
    
    
    Reply
    • Thank you for posting your solution.

      Unfortunately I have not found a solution for this yet. It is extremely hard to find examples about the correct usage of the greatest() method which is kind of weird because this is not an uncommon requirement. Maybe I should write a blog post about this.

      What do you think?

      Reply
  • How do i get to know when I fail to enter a duplicate database entry?

    I’m using Spring Data JPA with Hibernate.

    I have a class with a composite key mapped to a database table.

    When I perform a save operation using the JPARepository extended interface object, I see the following log in the console:

    Hibernate: select rolefuncti0_.functionalityId as function1_4_0_, rolefuncti0_.roleId as roleId2_4_0_ from RoleFunctionality_Mapping rolefuncti0_ where rolefuncti0_.functionalityId=? and rolefuncti0_.roleId=?
    Hibernate: insert into RoleFunctionality_Mapping (functionalityId, roleId) values (?, ?)

    This is what I see when i repeat the operation with the same data:

    Hibernate: select rolefuncti0_.functionalityId as function1_4_0_, rolefuncti0_.roleId as roleId2_4_0_ from RoleFunctionality_Mapping rolefuncti0_ where rolefuncti0_.functionalityId=? and rolefuncti0_.roleId=?

    It appears Spring Data first checks whether the Key exists in the database, and then proceeds to perform insertion.

    There should be a way to catch the information which hibernate has found (that the database entry/key exists in the database)? How can we check that? There should be some kind of information which would be possible to get from Spring that it would return/give in any other way to the application- if it is not going to go ahead with insertion in the event of a duplicate entry. (Spring makes a decision (based on some information) not to insert after finding an existing primary key.)

    Reply
    • The SimpleJpaRepository class provides an implementation for the save() method. The source code of this method looks as follows:

      
      @Transactional
      public <S extends T> S save(S entity) {
      
      	//This returns true if the id of the entity is null.
      	if (entityInformation.isNew(entity)) {
      		em.persist(entity);
      		return entity;
      	} 
      	else {
      		return em.merge(entity);
      	}
      }
      
      

      As we can see, the SimpleJpaRepository class calls the persist() method of the EntityManager class if the id of the entity is null. If the id is not null, the merge() method of the EntityManager class is called.

      In other words, if the id of the saved entity is not null, the entity is not persisted. It is merged into the current persistence context. Here is a nice blog post which describes the difference.

      In your case, this is what happens:

      1. When you call the save() method for the first time, Hibernate checks if an entity exists. Because it does not exists, it is inserted to the database.
      2. When you call the save() method for the second time, Spring Data JPA notices that the id is not null. Thus, it tries to merge the entity into the current persistence context. Nothing “happens” because the information of the detached entity is equal than the information of the persisted entity.

      Of course, you can always handle this in the service layer. This is the approach which I use when I want to verify that unique constraints aren’t broken (I don’t use this for primary keys though. I am happy with the way Spring Data JPA takes care of this).

      Reply
  • NPE while accessing the getter method for a field annotated with @ManyToMany annotation. Pls. find the outline of the sample code below.
    Repository & Service layer were the default implementation provided by Spring using Spring Roo commands.. I am a newbie and could you pls. help me what I am doing wrong ?

    class A {

    @ManyToMany( CASCADE.ALL, FetchType.LAZY)
    List<B> b;
    }

    class B {

    }

    interface ARepository {
    List<A> findAllAs();
    }

    class AService {
    List<A> findAllAs() { return aRepository.findAllAs()}
    }

    Application code:

    List<A> aList = aService.findAllAs();
    for (A a : aList)
    {
    for (B b : a.getB()) { <—- Results in NPE
    { …

    Reply
    • Take a look at this blog post. It describes how you can create a many-to-many relationship with JPA.

      Reply
  • Hi Petri,

    After looking at your blog and reading the documentation, I think it might be a good idea to show how to make joins and concat specifications. Here is my approach:

    Specification filterSpec = null; // query by lastname in the post. I parse filters from jqGrid, out of scope.
    Specification joinSpec = new Specification() {
    @Override
    public Predicate toPredicate(Root root, CriteriaQuery query, CriteriaBuilder cb) {
    Join join = root.join(Transaction_.parentCard, JoinType.INNER);
    return cb.equal(join.get(Card_.id), idFromRequest);
    }
    };
    myRepo.findAll(Specifications.where(joinSpec).and(filterSpec), pageable);

    Hope you find it useful.

    Regards.

    Reply
    • Hi Pedro,

      thanks for sharing this. I think that it will be useful to my readers. I am actually a bit surprised that there are so few good tutorials about the JPA Criteria API.

      Reply
  • hello thanks, thats a lot of good information.
    I am using the hibernateJpaProvider,
    I am got the basic app working,
    now I am trying to read the SEQUENCE, in a query, how to do it

    @Query(“select party_id_seq.nextval from dual”)
    double findNextSeq4PartyId();

    but I am getting
    nested exception is java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: dual is not mapped

    Reply
    • If you want to create a native query by using a query method annotated with the @Query annotation, you have to set the value of its nativeQuery attribute to true. In other words, you have to add the following method to your repository interface:

      
      @Query(value="select party_id_seq.nextval from dual", nativeQuery="true")
      public long findNextSeq4PartyId(); 
      
      

      If you are trying the get next available id for the private key of an entity, you should use the @GeneratedValue annotation because it makes your code a lot cleaner.

      Reply
  • Great set of articles!

    I’ve been living in the world of NamedQueries in my Entities, and more complex Native queries defined as Autowired Strings (becoming difficult to manage). This tutorial is just what I need to move beyond queries and dive into the Criteria API.

    My question might be a bit naive, but what I don’t understand or see is how your service implementation resolves your custom Specification methods ( lastNameIsLike() ). I put together a rather simple scenario in my project and I am unable to resolve these methods.

    Example Code:

    
    public interface AgencyDao extends JpaRepository, JpaSpecificationExecutor {}
    
    public class AgencySpecifications {
        public static Specification isInJurisdiction( final JurisdictionType jurisdictionType ) {
            return new Specification() {
                @Override
                public Predicate toPredicate( Root root, CriteriaQuery query, CriteriaBuilder cb ){
                    return cb.equal( root.get( Agency_.jurisdictionType ), jurisdictionType );
                }
            };
        }
    }
    
    

    This service method does not resolved my Spec method isInJurisdiction:

    List agencies = agencyDao.findAll( isInJurisdiction(JurisdictionType.AZ) );
    
    Reply
    • Hi Dex,

      I assume that you get a compilation error because the static isInJurisdiction() method of the AgencySpecifications class is not found?

      I noticed that I had forgot to add one crucial static import to the source code of the RepositoryPersonService class which is found from this page (It is found from the example project). I added that import to the source code found from this page as well.

      Let’s assume that the AgencySpecifications class is found from package foo. If this is the case, you have to add the following static import to your service class:

      import static foo.AgencySpecifications.isInJurisdiction;
      

      This should solve your problem. Again, I am sorry for the trouble I caused to you.

      Reply
  • Yes, I completely missed that. Again great job, and keep up the great work.

    Reply
    • Thanks! If you have any other questions in the future, I am happy to help you out.

      Reply
  • Hi petri , you are doing a great job , the post is very useful thank you . I ‘m using spring Data and i have a problem with duplicate entries :

    this is a code sample :

     
    public static Specification finByAmount(final double amount) {
    	return new Specification() {
    		
    		@Override
    		public Predicate toPredicate(Root root,
    								CriteriaQuery arg1, 
    								CriteriaBuilder cb) {
    			return cb.lt(
    				root.join(Tarif_.timeUnits).get(TarifTimeUnit_.amount),
    				amount
    			);
    		}
    	};
    }
    
    

    the join is returning duplicate result is there a way to apply DISTINCT to this Criteria. thank you

    Reply
  • Hi petri, your tutorial help me a lot, thanks. Actually I have a question, how to select some columns, using specification? It is like DTO in your tutorial part 3. Because I don’t want to select all columns from table. Thanks in advance.

    Reply
  • Hello petri,
    first thanks for your blog, im new in jpa data but have successfully followed the step of the part four, it works when but i’m struggling to add two specifcations
    like example: statusIsLike, skillLike

    projectRepostory.findAll(where(statusIsLike("done")).and(skillLike("manager");

    Thanks in advance,

    Reply
    • Hi Yvau,

      If you take a look at the Javadocs of the Specification<T> and JpaSpecificationExecutor<T> interfaces, you can see that you cannot combine Specification<T> objects in that manner.

      I would probably add a new method to the specification builder class and return a Specification<T> object that contains all the required conditions.

      Reply
      • Hello petri,
        To solve it, I was passing the data as object from my form like final SearchDTO searchTerm instead of final String searchTerm and customizing everything with if else statement ,in one specification. But now i got it, i’ll try your approach.

        Thank you for your response !

        Reply
        • You are welcome!

          Actually, I would also wrap the search conditions into a DTO and then pass this object to my specification builder method. This makes sense if you have to support more than a few search conditions (especially if these conditions have the same type).

          Let me know if you have any other questions.

          Reply
  • Hello Petri,

    Nice article.I have used above information to use specification in my project. Currently facing one issue with it.

    Is it possible to tranform Specification of type T to specification of Type T1.

    Here T and T1 having same type of attributes and I am using it for having seperate DTO for different layer (Business layer / Persistence layer). So at business layer I am getting specification. Now to tranfer it to persistence layer I need to have Specification. So is it possible to transform it from one type to another type (having same attribute applied in specification) ?

    Thanks in advance

    Reply
    • I think that it is not possible to transform a Specification<A> object into a Specification<B> object (or at least I don’t know how to do this).

      Maybe I don’t understand your use case, but you shouldn’t have to do this. Remember that the type parameter, which is given when you create a new Specification<T> object, describes the type of the entity. Thus, you should create only one specification that specifies the invoked query by using the JPA Criteria API.

      Reply
  • Hi Petri,

    Question about the StaticMetaModel, in this case you’re just using the lastName attribute of the Person class.

    But say you wanted to build a query based on all attributes of the Person class (say age, firstname, lastname, location etc.)

    Would using this StaticMetaModel combined with a JpaSpecifcationExecutor/JpaRepository be a good way to acheive this level of filtering?

    Thanks,
    Paul

    Reply
    • Hi Paul,

      It depends.

      Although you can use property names as well, the problem is that you notice your mistakes (typos, missing properties, and so on) at runtime. If you use static model classes, you will notice these mistakes at compile time. This is of course a lot faster than running your application just to realize that it doesn’t work.

      I think that if you are going to write only a few criteria queries AND you want to make your build as fast as possible, you could consider using property names. If you need to write many criteria queries, you should generate static meta model classes when you project is compiled and use these classes when you create your queries.

      By the way, have you considered using Querydsl? I am not a big fan of the JPA Criteria API because complex queries tend to be extremely hard to write and read. Querydsl provides a bit better user experience.

      Reply
  • First, many thanks for this highly informative series! I have a question regarding filtering/searching. If one needs to obtain a list of an entity type filtered by multiple (optional) elements (say in the case of a Person, lastNameStartsWith, yearOfBirth, gender, etc…), what’s the best approach? I shall also need to perform sorting and pagination on this list…
    Many Thanks!

    Reply
    • Hi Anthony,

      thank you for your kind words! I really appreciate them.

      Does optional mean that the condition might or might be present? For example, do you need to find persons whose:

      • last name starts with X AND year of birth is Y
      • last name starts with X AND gender is Z
      • last name starts with X AND year of birth is Y AND gender is Z

      If so, I recommend that you use either JPA Criteria API or Querydsl. I would use Querydsl just because I don’t a big fan of the JPA Criteria API, but you can definitely use it if you don’t want to add another dependency to your project.

      Reply
  • Hi Petri,
    I want to say that is amazing tutorial but i have some problems with jpa criteria api and I want your help. I have installed eclipse mars and also mysql. I made the connection between them and generate entities from the tables that I had created in mysql. I also installed JPA but the problems stay when I start to create criteria api. I have put some criteria api code and it doesn’t execute due to the libraries or something else.But when I put the right libraries and the code don’t have any problems it is unable to execute and a Failure trace message. Could you help me what to do to resolve the problem and execute criteria api.

    Reply
    • Hi Ani,

      Unfortunately I don’t know what is wrong without seeing the stack trace and the code that throws an exception. If you can add this information here, I can try to figure out what is wrong.

      Reply
      • 
        package ani;
        
        import javax.persistence.EntityManager;
        import javax.persistence.PersistenceContext;
        
        import javax.persistence.criteria.CriteriaBuilder;
        import javax.persistence.criteria.CriteriaQuery;
        import javax.persistence.criteria.Root;
        
        import org.junit.Test;
        
        public class CriteriaApi {
            
        	@PersistenceContext
            EntityManager em;
        	
        	@Test
        	public void test() {
        		CriteriaBuilder cb = em.getCriteriaBuilder();
        		CriteriaQuery crit = cb.createQuery(Person.class);
        		Root candidateRoot = crit.from(Person.class);
        		candidateRoot.alias("p");		
        		}
        	}
        
        

        Stack Trace
        give me a failure trace
        java.lang.NullPointerException
        at ani.CriteriaApi.test(CriteriaApi.java:25)

        Reply
        • I will appreciate if you give me a solution for this problem as soon as possible.

          Reply
        • A NullPointerException is thrown because the value of the em is null. You need to create an EntityManager object and inject it to the em field before you can write tests that use it.

          If you are using Spring Data JPA, check out my blog post titled: Spring Data JPA Tutorial: Integration Testing. It explains how you can write integration tests for your Spring powered repositories.

          If you are using Java EE, you should take a look at Arquillian. I have never used it myself, but you can write tests for your persistence layer by using Arquillian.

          Reply
  • Hell Petri,
    I have another issue if you could help me. I don’t know how to add data with criteria api.

    Reply
    • Hi Ani,

      You cannot add or update data with criteria API. You need to use either Spring Data repositories or the methods provided by the entity manager (if you use Java EE).

      Reply
  • Hi Petri

    I want to fetch some records from the table. But am getting the record only if all the selected column is not null otherwise, that row is not returning in the final result.

    @Query(Select le.id,le.name,le.address.no from Ent le)

    If i execute the above query, it will return the result only if all the three columns are not null.
    If le.address is null in table , it is getting skip and not fetching while am executing.

    Can you please tell me, how can i fetch selected column from table, even if it is null
    Thanks

    Reply
    • Hi Clement,

      does your query method return an entity object or an object array?

      Reply
      • Hi Petri

        Returning the result with entity object only.
        But am not selecting the whole entity object, trying to fetch selected columns only. If the column has null value, that record is not getting fetch

        Please provide any solution if you have any

        Reply
        • I tried to reproduce your problem, but I ran into a different problem. My query method looks as follows:

          
          @Query("SELECT t.title, t.description FROM Todo t WHERE t.id=:id")
          Todo findPartial(@Param("id") Long id);
          
          

          When I invoke it, the ConversionFailedException is thrown (even if the title and description fields are not null):

          org.springframework.core.convert.ConversionFailedException: Failed to convert from type java.lang.Object[] to type net.petrikainulainen.springdata.jpa.todo.Todo for value ‘{title, description}’; nested exception is org.springframework.core.convert.ConverterNotFoundException: No converter found capable of converting from type java.lang.String to type net.petrikainulainen.springdata.jpa.todo.Todo

          There two solutions to this problem:

          I hope that this answered to your question.

          Reply
          • Hi petri

            Thanks for your reply.
            I do have an option by bindind the result in generic object. But that way i should not follow. i have to bind the result to corresponding pojo

          • But a DTO is a POJO, right?

            If you want to return an entity, you can add a similar constructor to your entity class as well, but I think that it is a bad idea. If remember correctly, the returned object is not registered to the entity manager => you cannot save or update it, lazy loading doesn’t work, and so on.

            If I would be you, I would return a DTO. Is there some reason why you cannot do it?

          • Hi Petri

            I can return a DTO . Am not facing any problem in getting result.
            The problem is, am not getting the exact result set count. Because JPA skips the record when i am trying to select columns from the entity

            For Eg : Select le.id,le.name,le.address from personal le.

            In the above query, le.id and le.name is a string in that personal entity
            But le.address is in manyToOne relation in personal entity.
            So , if am trying to execute the query, JPA returns the records only if le.address is not null,
            If it is null, JPA skips the record in the final result set.

            Finally, I want all the records including null and not null of le.address

            Thanks Petri

          • In the above query, le.id and le.name is a string in that personal entity
            But le.address is in manyToOne relation in personal entity.

            Ah, that explains it. I couldn’t figure out what is wrong because I thought that you are trying to select the fields of the Personal entity. However, if the address field contains another entity, you should use LEFT JOIN. These links describe how you can use left joins in your JPQL queries:

            I hope that this helps you to solve your problem.

          • Hi Petri

            Thanks for your immediate answer.
            I am using left join only . Please find the exact query that am using below.

            @Query(“SELECT NEW com.test.las.domain.reports.testPojo(le.id,le.a, le.k, le.b, ”
            + “le.c.id, le.d.id) from pojo1 le LEFT JOIN le.pojo2 ge order by le.id”)

            In the above query , if le.c is null, am not getting that corresponding row.. but am getting all other rows as well

            Thanks

          • It’s a bit hard to provide an exact answer because I don’t know which of the selected values are fields of the original entity and which are other entities. However, there are two rules that you should follow:

            1. If you select fields whose values are other entities, you have to specify that all of them are fetched by using left join.
            2. When you select fields of other entities, you have use the alias which you specified when you created the left join.

            Example:

            I need to select the following values:

            • The title of a todo entry. The title of the todo entry is stored to the title field of the Todo class.
            • The name of the person who created the selected todo entry. The name of the person is stored to the name field of the Person class. The person who created the todo entry is stored to the creator field of the Todo class.

            If want to create a query method that returns the title of the todo entry even if the creator is null, I have to use the following query method:

            
            @Query(
            	"SELECT new foo.bar.TodoDTO(t.title, p.name) " + 
            	"FROM Todo t " + 
            	"LEFT JOIN t.creator p " +
            	"WHERE t.id=:id"
            )
            public TodoDTO findPartial(@Param("id") Long id)
            
            
          • Hi Petri

            SELECT le.a,le.b from table le
            Please note that, query will return all the result even if le.a or le.b has null values because both are defined as string in their own entity.

            But if Select le.a,le.b,le.address.name from table le
            In this scenario, le.address is an object and if it is null, It will skip the records that having address as null. Only will get the address as not null.
            I want all the result to be fetched

            Thanks

          • Hi Clement,

            If the Address object is an entity, you can solve your problem by following the instructions given in this comment.

            Is the Address an entity or an @Embeddable value object?

          • Hi Petri

            Thanks for the reply..
            Address is an entity only in my scenario.

            And in the example that you mentioned, if p.name is an object (ManytoOne) in ToDo, Row wil be skipped during execution of query.

          • Actually LEFT JOIN includes todo entries that don’t have a creator:

            By default all joins in JPQL are INNER joins. This means that results that do not have the relationship will be filtered from the query results. To avoid this, a join can be defined as an OUTER join using the LEFT options.

          • Hi Petri

            I have tried in all the way but still i couldn’t get all the rows as expected :-(

          • I modified your original query to use LEFT JOIN. The modified query looks as follows:

            
            @Query(
            	"SELECT new foo.bar.EntDTO(le.id, le.name, a.no) " +
            	"FROM Ent le " +
            	"LEFT JOIN le.address a"
            )
            
            

            This should return the wanted result (and not ignore rows that don’t have address information).

            Are you by any chance sorting the query results of your query? Also, which JPA provider are you using?

          • Hi Petri

            Thanks for the information. I tried exactly like this but still am not getting the exact result.
            And am using order by le.id

          • The reason why I asked about sorting is that if you would have sorted your query results by using a property of an entity that can be null, you would have faced this problem. However, since you don’t do it, I have to admit that I don’t know what is wrong. :(

          • Hi petri

            Thanks a lot for your help . I will also try . If i found anything i will let you know.

            Thanks!!!!

          • You are welcome. I hope that you find a solution to your problem!

          • Hi Petri

            I found the solution for my scenario.

            Previously i was using like select:

            
            SELECT NEW com.foo.las.domain.table1(le.id,le.name,le.address,ge.time) 
            FROM table1 le 
            LEFT JOIN le.table2 re
            
            

            In the above query , if address is null , i couldnt get the complete result.

            So i changed the query like select:

            
            SELECT NEW com.foo.las.domain.table1(le,ge.time) 
            from table1 le 
            LEFT JOIN le.table2 re
            
            

            In POJO:

            
            table1(table1 le,time time){
            	this.id = le.id;
            	this.name = le.name;
            	this.address = le.address;
            	this.time = time;
            }
            
            

            So, now its fetching all the results as expected.

            Thanks for your try petri for me!! :-)

            Update: I modified the package name because it identified the end customer. – Petri

          • Great work! Also, thank you for posting the solution on my blog.

            I think that I will write some blog posts that talk about these “special” scenarios and describe how you can solve them.

  • Hi Petri

    Can you please tell me .. Is there any other way to handle this scenario in better way

    I am trying to execute findAll() and am getting the result as List<B> totalList;
    i want to set it to List<C> without iterating in for loop because i want to give parameter of List<C> to .save method

    Reply

Leave a Comment