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

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 be 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 = ...
Lis<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.data.jpa.domain.Specification;
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.
211 comments… add one
  • Stone Feb 15, 2012 @ 20:43

    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.

    • Petri Feb 15, 2012 @ 21:54

      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.

      • Stone Feb 28, 2012 @ 23:21

        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.

        • Petri Feb 29, 2012 @ 10:17

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

          • Stone Feb 29, 2012 @ 14:51

            I didn't use H2, I used MySQL.

        • Petri Feb 29, 2012 @ 19:11

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

  • David Feb 19, 2012 @ 5:22

    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

  • amol Apr 12, 2012 @ 17:53

    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 ?

    • Petri Apr 12, 2012 @ 18:25

      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?
      • amol Apr 17, 2012 @ 12:12

        Well, I managed to fix that. It was with the created criteria as you rightly said.
        Thanks again.

        • Petri Apr 17, 2012 @ 12:19

          Hi Amol,

          great to hear that you managed to solve your problem.

    • Vale G Nov 19, 2022 @ 1:22

      Saludos desde Uruguay!

      • Petri Nov 29, 2022 @ 19:31

        Terveisiä Suomesta!

  • albert Apr 17, 2012 @ 10:48

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

    • Petri Apr 17, 2012 @ 10:55

      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.

      • amol Apr 17, 2012 @ 14:44

        Hi Petri,

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

        Many Thanks,

    • Petri Apr 17, 2012 @ 17:11

      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?

      • albert Apr 17, 2012 @ 18:15

        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.

  • amol Apr 18, 2012 @ 13:45

    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

    • Petri Apr 18, 2012 @ 19:35

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

      • amol Apr 18, 2012 @ 20:09

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

        • Petri Apr 18, 2012 @ 20:37

          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?

          • amol Apr 18, 2012 @ 21:03

            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

          • Petri Apr 18, 2012 @ 21:21

            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;
            }

      • raja Jun 20, 2018 @ 16:56

        how to write jUnit test cases for toPredicated method? can you please explain.

        • Petri Jun 20, 2018 @ 18:14

          Hi,

          I wouldn't write unit tests for the predicate builder methods because these tests are very hard to read and write. Also, these tests don't test if the created query returns the correct results. Instead, I would write integration tests for my Spring Data JPA repository. I admit that these tests are slower than unit tests, but in this case this is acceptable because integration tests help me to ensure that my query returns the correct results.

  • amol Apr 18, 2012 @ 23:19

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

    • Petri Apr 19, 2012 @ 9:44

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

  • amol Apr 19, 2012 @ 16:11

    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.

    • Petri Apr 19, 2012 @ 17:50

      Amol,

      Great!

  • albert Apr 23, 2012 @ 14:05

    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

    • Petri Apr 23, 2012 @ 19:32

      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?

      • albert Apr 24, 2012 @ 12:43

        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.

        • Petri Apr 25, 2012 @ 18:08

          Hi Albert,

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

  • Raghu Jul 31, 2012 @ 4:47

    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.

    • Petri Jul 31, 2012 @ 10:05

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

      • Kam Apr 21, 2016 @ 11:10

        Hi Petri,

        The bunch of spring jpa tutorial is really worthful, i have tried @Query and Specification and QueryDSL also to implement my need, how ever not 100% success in it.

        I need to fetch first 200 rows from EMP table where emp_loc is distinct
        Table Structure :

        EMP ID - PK
        EMP NAME
        EMP LOCA

        public Predicate toPredicate(Root EmplRoot, CriteriaQuery query, CriteriaBuilder cb) {
        query.distinct(true);
        //What should be placed here as Predicate need to be returned
        //cb.???
        }
        };
        Thanks in advance

        • Petri Apr 21, 2016 @ 23:19

          Hi Kam,

          It was nice to hear that my blog posts have been useful to you. If you want to use criteria queries, the easiest way to achieve your goal is to use the pagination support of Spring Data JPA. Just set the page size to 200 and get the first page.

  • Dhana kumar Aug 10, 2012 @ 16:57

    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();
    }
    };
    }

    • Petri Aug 10, 2012 @ 22:52

      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.

      • Dhana kumar Aug 11, 2012 @ 18:32

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

        • Petri Aug 11, 2012 @ 18:45

          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.

  • Dhana kumar Aug 11, 2012 @ 20:34

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

    • Petri Aug 11, 2012 @ 20:53

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

  • Dhana kumar Aug 12, 2012 @ 13:15

    Thanks, it works now, appreciate your help.

  • Gauthier Peel Sep 5, 2012 @ 13:48

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

    • Martin Dec 21, 2012 @ 10:13

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

      TIA

      • Petri Dec 21, 2012 @ 10:44

        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.

  • Alex Sep 25, 2012 @ 22:01

    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.

    • Petri Sep 25, 2012 @ 22:24

      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.

  • Lev Dec 18, 2012 @ 20:47

    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.

    • Petri Dec 18, 2012 @ 23:42

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

      • Lev Dec 19, 2012 @ 10:06

        works perfectly! thanks a lot

        • Petri Kainulainen Dec 19, 2012 @ 10:27

          You are welcome!

  • MiB Jan 21, 2013 @ 14:20

    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?

    • Petri Jan 21, 2013 @ 14:42

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

  • Lev Feb 4, 2013 @ 10:27

    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

    • Petri Feb 4, 2013 @ 21:51

      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?

      • Lev Feb 5, 2013 @ 12:30

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

        thanks :)

        • Petri Feb 5, 2013 @ 14:19

          You are welcome!

  • i42 Mar 6, 2013 @ 19:35

    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

    • Petri Mar 6, 2013 @ 20:30

      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.

  • i42 Mar 7, 2013 @ 13:55

    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

  • i42 Mar 15, 2013 @ 18:15

    No further comment Petri ?

    • Petri Mar 15, 2013 @ 20:33

      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?

  • i42 Mar 18, 2013 @ 11:36

    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

    • Petri Mar 18, 2013 @ 22:47

      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.

    • Petri Mar 24, 2013 @ 22:41

      I have started working on this.

    • Petri Apr 7, 2013 @ 17:59

      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.

      • i42 Apr 8, 2013 @ 12:08

        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

        • Petri Apr 8, 2013 @ 19:54

          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.

  • J Romero Mar 30, 2013 @ 2:17

    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.

    • Petri Apr 1, 2013 @ 11:32

      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?

  • Dhana kumar Anem Jul 14, 2013 @ 16:30

    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.

    • Petri Jul 14, 2013 @ 17:45

      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.

  • Dhana kumar Anem Jul 15, 2013 @ 18:49

    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

  • Dhana kumar Anem Jul 15, 2013 @ 19:19

    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
    ;
    
    
    • Petri Jul 15, 2013 @ 20:17

      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?

  • Dhana kumar Anem Jul 17, 2013 @ 21:44

    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
    
    
    • Petri Jul 22, 2013 @ 15:13

      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?

  • Chinmay Sep 30, 2013 @ 9:06

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

    • Petri Sep 30, 2013 @ 20:50

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

  • Mukunda Oct 16, 2013 @ 10:52

    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
    { ...

    • Petri Oct 20, 2013 @ 22:14

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

  • Pedro Oct 28, 2013 @ 17:05

    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.

    • Petri Oct 28, 2013 @ 20:13

      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.

  • Va Nov 4, 2013 @ 23:20

    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

    • Petri Nov 4, 2013 @ 23:41

      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.

  • Dex Dec 30, 2013 @ 23:13

    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) );
    
    • Petri Dec 30, 2013 @ 23:27

      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 (the example is changed and doesn't have this class anymore). 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.

  • Dex Dec 30, 2013 @ 23:40

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

    • Petri Dec 30, 2013 @ 23:45

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

  • Ray Jun 11, 2014 @ 16:58

    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

  • artiko Sep 1, 2014 @ 15:54

    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.

  • Yvau Sep 19, 2014 @ 8:35

    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,

    • Petri Sep 22, 2014 @ 22:43

      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.

      • Anonymous Sep 23, 2014 @ 5:14

        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 !

        • Petri Sep 24, 2014 @ 23:17

          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.

  • Sanjay Jain Dec 17, 2014 @ 6:16

    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

    • Petri Dec 17, 2014 @ 20:44

      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.

  • Paul Jun 24, 2015 @ 22:54

    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

    • Petri Jun 24, 2015 @ 23:20

      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.

  • Anthony Jul 1, 2015 @ 21:56

    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!

    • Petri Jul 1, 2015 @ 22:13

      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.

  • ANI Jul 21, 2015 @ 12:09

    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.

    • Petri Jul 21, 2015 @ 21:58

      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.

      • Ani Jul 21, 2015 @ 23:43
        
        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)

        • ANI Jul 21, 2015 @ 23:44

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

        • Petri Jul 22, 2015 @ 0:26

          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.

  • ANI Jul 22, 2015 @ 21:44

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

    • Petri Jul 22, 2015 @ 22:14

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

  • Clement Aug 1, 2015 @ 11:35

    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

    • Petri Aug 1, 2015 @ 12:37

      Hi Clement,

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

      • Clement Aug 1, 2015 @ 13:44

        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

        • Petri Aug 2, 2015 @ 11:32

          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.

          • Clement Aug 2, 2015 @ 15:09

            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

          • Petri Aug 2, 2015 @ 16:48

            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?

          • Clement Aug 3, 2015 @ 8:23

            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

          • Petri Aug 3, 2015 @ 9:07

            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.

          • clement Aug 3, 2015 @ 9:36

            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

          • Petri Aug 3, 2015 @ 19:36

            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)
            
            
          • Clement Aug 4, 2015 @ 9:06

            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

          • Petri Aug 4, 2015 @ 11:45

            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?

          • Clement Aug 4, 2015 @ 12:37

            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.

          • Petri Aug 4, 2015 @ 13:51

            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.

          • Clement Aug 5, 2015 @ 9:29

            Hi Petri

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

          • Petri Aug 5, 2015 @ 14:59

            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?

          • Clement Aug 6, 2015 @ 13:25

            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

          • Petri Aug 6, 2015 @ 19:58

            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. :(

          • Clement Aug 7, 2015 @ 9:11

            Hi petri

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

            Thanks!!!!

          • Petri Aug 7, 2015 @ 19:52

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

          • clement Aug 8, 2015 @ 14:05

            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

          • Petri Aug 8, 2015 @ 14:37

            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.

  • Clement Aug 19, 2015 @ 8:59

    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

  • Mayank Sep 10, 2015 @ 9:07

    Hi Petri,

    Thanks for an awesome article. Successfully followed it to introduce Specifications for my Project.

    I am having an issue though for fetching data from Multiple table.

    Suppose I have 3 tables.. Request_Details, Customer_Org_Details, Address_Details

    Request_Details is primary table and it stores primary keys of Customer_Org_Details and Address_Details table as foreign key in two different columns to maintain relations.

    I have Delivery Address city, Customer Org name and request ID to fetch data. So I need to get data from three tables. I am unable to make this join using Specification.

    Could you pls help.

    Thanks,
    Mayank.

    • Petri Sep 10, 2015 @ 20:50

      Could you add your entity classes into a new comment? The reason why ask this is that it's impossible to answer to your question without seeing those classes. Also, I only need to see what kind of relationship the RequestDetails entity has with the CustomerOrgDetails and AddressDetails entities.

      • Mayank Sep 11, 2015 @ 16:39

        Hi Petri,

        Thanks for quick response.. Please find below drive url for seeing images of table structure:

        https://drive.google.com/folderview?id=0By53-A1xA5XfWTlKT3cweDBmbjA&usp=sharing

        As of now I am fetching value from one table and filtering the records at client side in backend for filter values from other table (Like filter on the basis of city name for requests).

        Code for Specification:

        
        public final class ReleaseByProdTypeReqSpecification {
        
            private ReleaseByProdTypeReqSpecification() {}
        
            public static Specification hasOrderType(String orderType) {
                return (root, query, cb) -> {
                    return cb.equal(
        				root.get(JpaReleaseByProductTypeReq_.orderType), 
        				orderType
        			);
                };
            }
        
            public static Specification hasDeliveryIdentifier(String deliveryIdentifier) {
                return (root, query, cb) -> {
                    return cb.equal(
        				root.get(JpaReleaseByProductTypeReq_.additionalDeliveryIdentifier), 
        				deliveryIdentifier
        			);
                };
            }
        
            public static Specification hasSendersReference(String sendersRef) {
                return (root, query, cb) -> {
                    return cb.equal(
        				root.get(JpaReleaseByProductTypeReq_.sendersReference), 
        				sendersRef
        			);
                };
            }
        
            public static Specification hasDeliveryDate(Date desiredDeliveryDate) {
                return (root, query, cb) -> {
                    return cb.equal(
        				root.get(JpaReleaseByProductTypeReq_.desiredDeliveryDate).as(Date.class), 
        				desiredDeliveryDate
        			);
                };
            }
        }
        
        

        Requirement is to have specifications for joins as well for these three related tables.

        Insights appreciated.

        Regards,
        Mayank Porwal.

        • Petri Sep 12, 2015 @ 1:28

          You can combine multiple specifications by using the Specifications<T> class.

          If you have two specifications (specA and specB), and you want to get entities that fulfil both of them, you can use the following code:

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

          I hope that this answered to your question. Also, if you have any other questions, do not hesitate to ask them!

          • Anonymous Sep 12, 2015 @ 10:26

            Hi Petri,

            I am already ady using "and" "or" for combining specs, but on one table columns only.. Is it allowed for specs from multiple tables as well.?

            I do this when suppose I need filter on request desired date and sender.. But if I hv delivery city which is another table how to get requests for that..

          • Petri Sep 12, 2015 @ 11:30

            Is it allowed for specs from multiple tables as well.?

            Yes (and no). You can combine multiple Specification<A> objects, but you cannot combine Specification<A> and Specification<B> objects.

            But if I hv delivery city which is another table how to get requests for that..

            I will demonstrate this by using an example. Let's assume that

            • The "main" entity is called RequestDetails and its has a one-to-one relationship with the DeliveryAddress entity.
            • You want to create a specification that fetches all request details objects whose senderReference is 'XXX' and city is 'Atlanta'.

            The specification builder class that builds these individual specifications looks as follows:

            
            public final class RequestDetailsSpecifications {
            
            	public static Specification<RequestDetails> hasSendersReference(String ref) {
            		return (root, query, cb) -> {
                        return cb.equal(
            				root.<String>get("sendersReference"), 
            				ref
            			);
                    };
                }
            	
            	public static Specification<RequestDetails> isDeliveredIntoCity(String city) {
            		return (root, query, cb) -> {
                    	return cb.equal(
            				root.<DeliveryAddress>get("deliveryAddress").<String>get("city"), 
            				city
            			);
                	};
            	}
            }
            
            

            (I replaced the static meta model with strings because it makes this example a bit easier to read).

            You can now combine these specifications by using the following code:

            
            Specification<RequestDetails> senderReferenceSpec = hasSendersReference("XXX");
            Specification<RequestDetails> deliveryCitySpec = isDeliveredIntoCity("Atlanta");
            List results =  repository.findAll(
                Specifications.where(senderReferenceSpec).and(deliveryCitySpec)
            );
            
            

            In other words, if you can navigate from the Root<RequestDetails> object to the preferred entity, you can create a Specification<RequestDetails> object and combine it with other Specification<RequestDetails> objects.

            Again, if you have some questions, feel free to ask them.

  • John Sep 27, 2015 @ 16:50

    Hi,
    I have an issue in fetching the associated records using Spring Data JPA
    Suppose my repository is as below

    
    public interface PersonRepository extends JpaRepository {    
    
    	public final static String FIND_BY_ADDRESS_QUERY = "SELECT p " + 
    			"FROM Person p LEFT JOIN p.addresses a " +
    			"WHERE a.address = :address";
     
     	@Query(FIND_BY_ADDRESS_QUERY)
     	public List findByAddress(@Param("address") String address);
    }
    
    

    Entity as below

    
    @Entity
    @Table(name = "Person")
    public class Person {
    
        @Id
        @GeneratedValue
        private Long Id;
    
        @Column(name = "name", length = 255, nullable = false)
        private String name;
    
        @OneToMany(cascade=CascadeType.ALL)
    	@JoinColumn(name = "Id", referencedColumnName = "Id")
    	private Set address;
    }
    
    

    I will get the list of Person and and how will i get the addresses ?

    Question is on how to fetch the associated table details

    • Petri Sep 28, 2015 @ 22:27

      Do you mean that "extra" SQL queries are invoked when you try to access the address of a person? If so, you might want to use a fetch join (LEFT JOIN FETCH). However, before you do that, you should read this blog post: Fetch Joins and whether we should use them.

      • Sam May 26, 2016 @ 20:55

        Hi,

        The blog is simple and straight with syntax both in jpql and criteria api.

        SQL SERVER QUERY
        ---------------------
        select
        *
        from
        employee jj
        inner join
        (
        select
        max(join_date) as jdate,
        empltype as emptype
        from
        employee
        where
        empltype='clerk'
        group by empltype
        ) mm
        on jj.join_date=mm.jdate and jj.empltype=mm.emptype;

        JPQL :
        -------

        em.createQuery("Select e from EMPLOYEE e join e.empltype=:c.empltype,MAX(c.joindate) from EMPLOYEE c where c.emplytpe like :empltype GROUP BY c.empltype e1 ON e.empltype=e1.empltype AND e.joindate=e1.joindate")

        However i am stuck up in achieving the following functionality using both jpql and criteria.

        Throwing an exception unexpepected token:=near line1,column 75[ ]

        Any inputs are really appreciated..

        • Petri Jun 1, 2016 @ 21:33

          Hi,

          If I remember correctly, JPQL does not have the ON keyword. In other words, you can join two entities only if they have a relationship. I think that your best bet is to use a native SQL query.

  • sandy Nov 12, 2015 @ 16:56

    I am Passing java.sql.date in my method and in my entity column contain Time stamp , now i want to find by only by date in my specification how can i do this .
    below code snippet is not working

    public static Specification hasDesiredDeliveryDate(java.sql.Date desiredDeliveryDate) {
    return (root, query, cb) -> {
    return cb.equal(
    root.get("productIndividual").get("deliveryPoint").get("desiredDeliveryDate").as(java.sql.Timestamp.class), desiredDeliveryDate);
    };
    }

  • Vipul Jain Dec 17, 2015 @ 15:00

    How to use order by in specifications for example I am writing my specification like this
    Specifications.where(getPositionSpecification(filterDTO.getPositions()))
    .and(getUserSpecification(filterDTO.getUsers())).and(getDateBetween(filterDTO));

    then how can I apply orderBy here ??

  • Vinod Jan 4, 2016 @ 7:08

    Hello Petri,

    Hope you are doing well. Thanks for the blog it's really helpful for me. I am stuck with one issue can you please help me with it.

    I want to generate dynamic conditonal filter for spring data jpa with feild "notificationId" but my dto has list for normal dtos without list i am able to create it by following your blog i.e is done for "email" and all. can you please help me with it. I am getting null.userGcmData

    do i need to use builder.like or some other method if so can you give me syntax for it if possible.

    Thanks in advance

    
    public class UserSpecification implements Specification {
    
    	private SearchCriteria searchCriteria;
    
    	public UserSpecification(SearchCriteria searchCriteria) {
    		super();
    		this.searchCriteria = searchCriteria;
    	}
    
    	@Override
    	public Predicate toPredicate(Root root, CriteriaQuery query, 	CriteriaBuilder builder) {
    		//Not able to create the query its giving me null.userGcmData
    		if ("notificationId".equalsIgnoreCase(searchCriteria.getKey())) {
    			return builder.like(
    				root.get("userGcmData").get("notificationId"), 
    				"%" + searchCriteria.getValue() + "%"
    			);
    		}
    	}
    }
    
    //My Pojo classes
    @Entity
    public class User {
    
        @JsonProperty("gcm")
        @OneToMany(mappedBy = "user", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
        private List userGcmData;
    }
    
    
    @Entity
    @Table(name = "user_gcm_data")
    public class UserGcmData {
    
        @JsonProperty("notification_id")
        @Column(name = "notification_id")
        private String notificationId;
    }
    
    

    Update: I removed the irrelevant parts of code listing since it was quite long - Petri

    • Petri Jan 4, 2016 @ 19:40

      Hi,

      The problem is that you cannot access a collection property by using the get() method. You need to use join for this purpose. You can fix this problem by replacing your return statement with this one:

      
      return builder.like(
      	root.join("userGcmData").get("notificationId"), 
      	"%" + searchCriteria.getValue() + "%"
      );
      
      

      This blog post provides more information about joins: JPA Criteria API by samples – Part-II.

  • Mayank Jan 7, 2016 @ 13:07

    Hi Petri..

    Thanks a lot for this series.. Its really helping.. Need one help..

    Suppose I have 3 tables from which I need some.. Containing something like below structures:

    
    @Entity
    class Series {
    	
    	@Id
    	Long id;
    
    	@OneToMany(mappedBy="series")
    	List dossiers;
    }
    
    @Entity
    class Dossier {
      
    	@Id
    	Long id;
    
    	@ManyToOne
    	Series series;
    
    	@OneToMany(mappedBy="dossier"
    	List items;
    }
    
    @Entity
    class Item {
    	
    	@Id
    	Long id;
    
    	@ManyToOne
    	Dossier dossier;
    }
    
    

    I tried writing specification as given below:

    
    public static Specification hasNoErrors(Long id) {
    	return (root, query, cb) -> {
    		return cb.equal(
    			root.get("dossiers").get("items").get("id"), 
    			id
    		);
    	};
    }
    
    

    However, I get below exception:
    org.springframework.dao.InvalidDataAccessApiUsageException: Illegal attempt to dereference path source [null.Series] of basic type; nested exception is java.lang.IllegalStateException: Illegal attempt to dereference path source

    Kindly help... I am stuck here big time.

    Regards,
    Mayank

    • Mayank Jan 7, 2016 @ 14:12

      Was able to do it by reading earlier comments.. Using join.. :)

      Thanks anyways..

      Regards,
      Mayank

      • Petri Jan 7, 2016 @ 22:13

        Hi,

        It's good to hear that you were able to solve your problem!

      • Mayank Jan 8, 2016 @ 13:06

        Hi petri,

        Ran into another issue with the above query...

        If I use join, I get Duplicate records due to OneToMany relationship, so i used query.distinct(true), as suggested by you in some of the solutions earlier.

        However, that gives me error as I have one column as CLOB type in my parent class and seems CLOB datatypes are not supported with Distinct comparisons.

        Can u suggest something.

        Regards,
        Mayank

        • Mayank Jan 8, 2016 @ 14:39

          Hi petri...

          Was able to create oracle query w/o Distinct keyword.. However having issue now in converting to Specification..

          Can u help. Below is the query..

          Select * from STG_PRODEF_REQUEST where ID in
          (Select PRODEF_PROD_TYPE_REQ.PRODEF_REQUEST_ID from PRODEF_PROD_TYPE_REQ
          INNER JOIN
          STG_PRODEF_RESPONSE on PRODEF_PROD_TYPE_REQ.ID = STG_PRODEF_RESPONSE.PRODEF_INDIVIDUAL_ID and STG_PRODEF_RESPONSE.ERROR_CODE is not null)

          • Petri Jan 8, 2016 @ 21:37

            Hi Mayank,

            Check out this StackOverflow question. It should help you to solve your problem. If not, let me know.

          • Mayank Jan 11, 2016 @ 11:24

            Hi Petri,

            First of all, Thanks a lot for all your help.
            Tried replicating the resolution suggested by you earlier. However not able to... Some insights on what I am doing wrong will be really helpful:

            My table structure:

            STG_PRODEF_REQUEST Table:
            __________________________________________________________________
            public class JpaProDefRequest {

            @NotNull
            @Id
            @Column(name="ID")
            private Long id;

            @NotNull
            @Column(name="REQUEST_XML")
            @Lob
            private String proDefVersionRequestXML;

            @OneToMany(mappedBy = "orderId")
            private List proDefProductTypeReqs;
            ____________________________________________________________________

            PRODEF_PROD_TYPE_REQ Table:
            ____________________________________________________________________
            @Id
            @Column(name="ID")
            private Long id;

            @NotNull
            @ManyToOne(fetch = FetchType.LAZY, cascade=CascadeType.ALL)
            @JoinColumn(name="PRODEF_REQUEST_ID")
            private JpaProDefRequest orderId;

            @OneToOne(mappedBy = "individualId")
            private JpaProDefServiceResponse serviceResponse;
            ______________________________________________________________________

            STG_PRODEF_RESPONSE Table:
            ______________________________________________________________________
            @NotNull
            @Id
            private Long id;

            @Nullable
            @OneToOne(fetch = FetchType.LAZY, cascade=CascadeType.ALL)
            @JoinColumn(name="PRODEF_INDIVIDUAL_ID")
            private JpaProDefProductTypeReq individualId;

            @Nullable
            @Column(name = "ERROR_CODE")
            private String errorCode;

            @Nullable
            @Column(name = "ERROR_DESC")
            private String errorDescription;
            ______________________________________________________________________

            Need to fetch responses for below query:
            ____________________________________________________________________________
            Select * from STG_PRODEF_REQUEST where ID in
            (Select PRODEF_PROD_TYPE_REQ.PRODEF_REQUEST_ID from PRODEF_PROD_TYPE_REQ
            INNER JOIN
            STG_PRODEF_RESPONSE on PRODEF_PROD_TYPE_REQ.ID = STG_PRODEF_RESPONSE.PRODEF_INDIVIDUAL_ID and STG_PRODEF_RESPONSE.ERROR_CODE is not null)
            ____________________________________________________________________________

            I tried:

            public static Specification hasA() {
            return new Specification() {
            @Override
            public javax.persistence.criteria.Predicate toPredicate(Root jpaProDefRequestRoot, CriteriaQuery criteriaQuery, CriteriaBuilder cb) {
            CriteriaQuery query = cb.createQuery(JpaProDefRequest.class);
            Root proDefRequestRoot = query.from(JpaProDefRequest.class);
            Subquery sq = query.subquery(Long.class);
            Root request = sq.from(JpaProDefProductTypeReq.class);
            Join proDefProductTypeReqs = request.join("proDefProductTypeReqs");

            sq.select(proDefProductTypeReqs.get(JpaProDefRequest_.id)).where(
            cb.isNotNull(request.get(JpaProDefProductTypeReq_.serviceResponse).get("errorCode")));

            return query.select(proDefRequestRoot).where(
            cb.in(proDefRequestRoot.get(JpaProDefRequest_.id)).value(sq));
            }
            };
            }

            However doesn't work.

            Regards,
            Mayank.

          • Petri Jan 11, 2016 @ 21:46

            Hi Mayank,

            Do you get an error message or does the query return wrong results? The reason why I ask this is that it is kind of hard to figure this out when I cannot run the actual code. If you get an error message, that could help me to find the problem.

          • Mayank Jan 12, 2016 @ 16:33

            Hi Petri,

            Somehow was able to get it work. I was not using sub query properly. Realized how naive I am in Specifications.

            Thanks for the support.

            Regards,
            Mayank.

          • Petri Jan 12, 2016 @ 20:21

            Hi Mayank,

            You are welcome! I am happy to hear that were able to solve your problem.

  • Subhash Koganti Feb 17, 2016 @ 23:00

    Hi There ,

    Can you please do a tutorial by integrating the Spring Data JPA with Spring Boot and Mysql ??

    I seem to have issues when i use Mysql instead of H2 Db.

    • Petri Feb 18, 2016 @ 18:29

      Hi,

      What kind of issues are you having?

  • Santiago Feb 20, 2016 @ 22:35

    Hello Petri,

    Very good job with this site, I always end up here when I'm looking up for info about Spring and JPA. :)

    I want to ask you something. I see in your examples you make use of predicates in the service layer, plus you expose in the repositories the functions from JpaSpecificationExecutor.

    Is it really OK in a real world application to expose these signatures in your repository interface and making use of predicates in the service layer when you expect to have multiple implementations of both layers in a Spring app?

    Thanks :)

    • Petri Feb 21, 2016 @ 21:29

      Hi Santiago,

      Thank you for your kind words. I really appreciate them.

      Is it really OK in a real world application to expose these signatures in your repository interface and making use of predicates in the service layer when you expect to have multiple implementations of both layers in a Spring app?

      If you have multiple repository implementations, you cannot naturally expose implementation details to the service layer.

      However, if I have only one repository implementation (right now), I always create the predicates on the service layer. The reason for this is that it's a lot simpler and I can always hide it behind an additional interface if I need to create a second repository implementation (this is very rare).

  • Sam May 16, 2016 @ 16:21

    Hi Petri,

    Your previous posts on QueryDSL solved many of my requirements, now am stuck up with sub query using Specification to satisft one requirement,tried couple of ways to achieve, but no luck.

    I need to fetch the one record of employee role from empl_tbl whose JOINING DATE is the latest one.

    SQL-QUERY
    --------------------
    select * from EMPL_TABLE WHERE EMPL_ROLE='MGR' and JOIN_DATE=
    (select max(JOIN_DATE) as datecol from EMPL_TABLE WHERE EMPL_ID='MGR')

    Entity -- EmpTableEntity

    How can i transform the above query using specifications.

    Appreciate your response.

    Thanks
    Sam

  • Lucas Araújo Jun 21, 2016 @ 20:46

    I'm working in a project that i need create a Specification for this query:
    "select l.* from lead as l
    where l.id in (select t.lead_id from telefone as t where t.lead_id = l.id);"

    The entity Phone has ManyToOne relationship for Lead.

    But i'm having trouble for create a subquery for select column "lead_id" with join.
    Someone can help me please?

    • Petri Jun 23, 2016 @ 23:52

      Hi,

      Your query looks pretty similar as the query which I found from this StackOverflow answer. The key is to create the subquery (check the link) and use it when you specify the IN condition by invoking the in() method of the CriteriaBuilder interface.

      If you have any additional questions, don't hesitate to ask them.

  • sathishkumar Jul 1, 2016 @ 22:04

    Hi,

    Help required, I got stuck with the below exception. Am getting this exception while executing the below piece of code from my junit test case

    contractRepository.findAll(ContractSpecification.searchByContractId("XXXX"));

    i have my specification smehting like below

    
    public static Specification searchByContractId(String contractNumber) {
    	return (Root root, CriteriaQuery query, CriteriaBuilder cb) -> {
    		return cb.equal(root.get("contractNumber"), contractNumber);
    	};
    }
    
    

    java.lang.ClassCastException: javax.persistence.criteria.$Impl_Predicate cannot be cast to org.hibernate.jpa.criteria.Renderable
    at org.hibernate.jpa.criteria.QueryStructure.render(QueryStructure.java:262)
    at org.hibernate.jpa.criteria.CriteriaQueryImpl.interpret(CriteriaQueryImpl.java:312)
    at org.hibernate.jpa.criteria.compile.CriteriaCompiler.compile(CriteriaCompiler.java:147)
    at org.hibernate.jpa.spi.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:736)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.lang.reflect.Method.invoke(Method.java:498)

    Update: I removed the irrelevant part of this stack trace - Petri

    • Petri Jul 7, 2016 @ 11:42

      Hi,

      That looks really weird (I have never seen this problem myself). What Hibernate version are you using?

  • wenhao Oct 24, 2016 @ 10:57

    I prefer to https://github.com/wenhao/jpa-spec

    public Page findAll(SearchRequest request) {
    Specification specification = new Specifications()
    .eq(StringUtils.isNotBlank(request.getName()), "name", request.getName())
    .gt(Objects.nonNull(request.getAge()), "age", 18)
    .between("birthday", new Range(new Date(), new Date()))
    .like("nickName", "%og%", "%me")
    .build();

    return personRepository.findAll(specification, new PageRequest(0, 15));
    }

    • Petri Oct 24, 2016 @ 22:20

      Hi,

      That looks pretty slick! Do you have any plans to add support for JPA static metamodel classes?

  • Akansha Apr 13, 2017 @ 15:39

    org.springframework.data.jpa.repository.JpaSpecificationExecutor.findOne(Specification) is not working as excepted. It is returning more than single record.

    • Petri Apr 13, 2017 @ 18:38

      The default implementation of that method (check the SimpleJpaRepository class) invokes the getSingleResult() method of the TypedQuery interface. This method throws a NonUniqueResultException if your query returns more than one result. If you get this exception, the implementation is working as "expected" (although its documentation could be improved). That being said, it is impossible to say what is wrong since you didn't share the problematic code.

      • Akansha Apr 17, 2017 @ 13:21

        Yes, I am getting same error as you have mentioned, but if we try to judge the working of 'findOne()' method by its name, then it should have returned Single record by enforcing 'Limit 1' in the query(in case of having multiple records having same filter criteria).

        Suppose if we want to fetch single record based on 2 key details(having composite key of 3 fields). I am passing 'nul' values for the 3rd key:
        eg:

        
        Record record =  Repo.findOne(SpecificationClass.usingFewKeys(key1, key2, null));
        
        

        Above statement throws a NonUniqueResultException.

        What I have to do in order to fetch the single record, is to create a custom method for this purpose and fetching the first record using findAll(spec):

        
        public Record findOne(Specification spec) {
        	List records= Repository.findAll(spec);
        
        	if (records.size() == 0) {
        		return null;
        	}
        
        	return records.get(0);
        }
        
        

        This has increased some look-head in my code.

        • Petri Apr 18, 2017 @ 9:58

          Hi,

          You can also use the method that takes a Pageable object as a method parameter and set the size of the returned page to one. The downside of this technique is that you have to remember to create the Pageable object => You probably still need a utility method.

          If you decide to use this technique, check out my blog post that explains how you can paginate your query results with Spring Data JPA.

          By the way, if you think that the method is not working correctly, you should create a new Jira issue (I cannot do anything about it since I am not a member of the Spring Data team).

          • Akansha Apr 18, 2017 @ 12:38

            Thanks for the help Petri!

            But, I have to stick to the solution that i have mentioned above due to application requirement.

            Regards,
            Akansha

          • Petri Apr 19, 2017 @ 9:59

            You are welcome. :)

  • Akansha May 19, 2017 @ 9:48

    Hi Patri!

    I have one more query, I was using Specification with DB2 and found one issue with 'ORDER BY' clause:

    If we use:
    org.springframework.data.jpa.repository.JpaSpecificationExecutor.findAll(Specification spec, Pageable pageable)

    With Specification as:

    public static Specification xxSpecification(String xxx) {
    return (root, cq, cb) -> {
    cq.orderBy(Arrays.asList(cb.asc(root.get("xxx"))));

    List predicates = new ArrayList();

    if (StringUtils.isNotEmpty(xxx)) {
    predicates.add(cb.like(root.get("xxx"), "%" + xxx+ "%"));
    }

    return cb.and(predicates.toArray(new Predicate[0]));
    };
    }

    Then the query when executed against DB2 fails because of the presence of "order by" clause. Though note that this would work fine when executed against MySQL.
    The query results in below error :
    [SQL0122] Column XXX or expression in SELECT list not valid.

  • Mayank Jun 9, 2017 @ 12:07

    Hi Petri,

    I need to execute something like below through Specification and Criteria Builder:

    Select * from P
    INNER JOIN
    F
    ON
    P.FID = F.ID
    where
    (F.XXX = 'ABC')
    OR
    (
    F.XXX = 'DEF'
    AND F.XXX = 'JKL'
    );

    However I am not getting successful since last OR condition is dynamic (i.e. I can have more than 1 AND Conditions as also shown above) .

    Can you suggest something. ?

    • Petri Jun 10, 2017 @ 10:56

      Hi,

      You can use the and() and or() methods of the CriteriaBuilder interface. Here is a simple pseudo code that demonstrates how this works:

      
      Predicate pred = cb.like("title", "%foo%");
      
      if (parentId != null) {
      	pred = cb.and(cb.eq("parentId", parentId));	
      }
      
      return pred;
      
      

      If you have any additional questions, don't hesitate to ask them.

  • Akansha Jun 13, 2017 @ 16:12

    Hi Patri!

    I have one more query, I was using Specification with DB2 and found one issue with ‘ORDER BY’ clause:

    If we use:
    org.springframework.data.jpa.repository.JpaSpecificationExecutor.findAll(Specification spec, Pageable pageable)

    With Specification as:

    public static Specification xxSpecification(String xxx) {
    return (root, cq, cb) -> {
    cq.orderBy(Arrays.asList(cb.asc(root.get(“xxx”))));

    List predicates = new ArrayList();

    if (StringUtils.isNotEmpty(xxx)) {
    predicates.add(cb.like(root.get(“xxx”), “%” + xxx+ “%”));
    }

    return cb.and(predicates.toArray(new Predicate[0]));
    };
    }

    Then the query when executed against DB2 fails because of the presence of “order by” clause. Though note that this would work fine when executed against MySQL.
    The query results in below error :
    [SQL0122] Column XXX or expression in SELECT list not valid.

    • Petri Jun 13, 2017 @ 18:35

      Hi,

      You could try to specify the ORDER BY condition by using the Sort class. If you don't know how to do it, take a look at this blog post.

      • Akansha Jun 14, 2017 @ 7:39

        Thanks!

        • Petri Jun 14, 2017 @ 10:23

          You are welcome.

  • Durga Shanker Dec 3, 2017 @ 12:41

    Hi Petri,

    Thanks alot for your tutorial on spring data. I have one query in service implementation class:
    This is your service implementation

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

    My question is :
    in method : public List findBySearchTerm(String searchTerm) , instead of passing searchTerm of String type, if I pass "TodoDTO dto" like shown below :

    
    public List findBySearchTerm(TodoDTO dto){  }
    
    

    and there are many number of fields available in TodoDTO class, suppose 20 number of fields are available.
    whenever user sending the POST request then he is sending value for 8 fields only i.e in Request Body , he is providing the value of only 8 fields in JSON format.
    and he wants to retrieve all the records from database where these 8 fields conditions are satisfying.

    Then how will i write serviceImplmentation for this method because the value of other 12 fields are null as user has been provided the value for only 8 fields.??? and 8 number is not fixed , it could be anything like 5,6,7,9,10.

    public List findBySearchTerm(TodoDTO dto){
    ? ? ?
    }
    and how will i write implementation of Specification class. ???Please help me in this :)

    • Petri Dec 6, 2017 @ 16:50

      Hi,

      You can indeed use a DTO instead of a search term. In fact, this is a quite common practice when you need to use multiple search conditions. So, if you need multiple search conditions, your specification factory class could look as follows:

      
      final class Specifications {
       
          private Specifications() {}
       
          static Specification<Foo> matchesConditions(SearchDTO searchConditions) {
              return new Specification<Foo>() {
                  @Override
                  public Predicate toPredicate(Root<Foo> root,                                 
                                      CriteriaQuery<?> query, 
                                      CriteriaBuilder cb) {
                      //Create the query here.
                  }
              }
          }
      }
      
      

      The key of implementing the toPredicate() method is to leverage the and() and/or or() methods of the CriteriaBuilder class. Basically, you have to go through all fields of your DTO and apply this pseudo code to every field:

      • If the field has a non-empty value
        • Create a new Predicate by using the CriteriaBuilder class.
        • Link the created Predicate with the previous Predicate (Use either the and() or or() method of the CriteriaBuilder class).
      • If the field has no value, ignore it.

      Can you understand what I am trying to say?

  • pratik Dec 13, 2017 @ 15:30

    Hi, Can you please tell me step by step process to generate Metamodel Classes in eclipse.

    • Petri Dec 13, 2017 @ 22:00

      Hi,

      Unfortunately I don't use Eclipse => I have no idea how you can generate metamodel classes with it. That being said, I found this blog post that explains how you can do it.

  • Anonymous Dec 29, 2017 @ 18:37

    Thanks for this great series on Spring Data JPA. Just a couple things... Is there any way to remove irrelevant comments from this page? Some are referring to Person objects so I guess the tutorial has changed over time.
    Also, the last code example is missing the import for the Specification class.
    Finally, as a personal preference, if I may, I would suggest not using static imports, especially in this example. The method is only invoked in one place but besides that, I think it is more readable to invoke the method by referring to the class where it is implemented (TodoSpecifications.) - this makes it immediately obvious that the method is invoked statically on some other class - not locally or in some super class.

    • Petri Dec 29, 2017 @ 22:06

      Hi,

      First, thank you for your comment.

      Is there any way to remove irrelevant comments from this page? Some are referring to Person objects so I guess the tutorial has changed over time.

      It's possible to remove old (or new comments). The reason (or maybe an excuse) why I haven't done this is time. This blog post has now 5200 comments and it's "impossible" to read them all because I maintain this blog on my free time.

      On the other hand, now that you mentioned it, maybe I could clean at least the comments of my most popular blog posts because it's quite frustrating to read irrelevant comments.

      Also, the last code example is missing the import for the Specification class.

      This is now fixed. Thank you for reporting this.

      Finally, as a personal preference, if I may, I would suggest not using static imports, especially in this example. The method is only invoked in one place but besides that, I think it is more readable to invoke the method by referring to the class where it is implemented (TodoSpecifications.) – this makes it immediately obvious that the method is invoked statically on some other class – not locally or in some super class.

      Yes, I have noticed that some hate static imports and some people love them. I don't actually use them very often, but in this particular case, I think that the name of the specification class doesn't add relevant information to my code. On the other hand, if I use a static import, I can emphasize the the search criteria that is used by the created specification.

      But like you said, this is a matter of preference and it's fine to decide that you don't want to use static imports.

  • Sephi Jul 30, 2018 @ 16:12

    I have the following entity structure:

    | ParentObject|
    +-------------+
    ^
    |
    +-------+---------+----------------+
    | | |
    +-----------+ +-----------+ +-----------+
    | Son1 | | Son2 | | Son3 |
    +-----------+ +-----------+ +-----------+
    What I want is to get all the Son2 and Son3 that have an attribute that doesn't exist on Son1, the inheritance is strategy is SINGLE_TABLE with a DiscriminatorColumn

    What I did so far:

    class ParentObjectPredicat

    public static Specification inNaturesSon2(List natures) {
    return (root, query, cb) -> {
    final Root son2Root = cb.treat(root, Son2.class);
    return son2Root.get(Constants.NATURE).in(natures);
    }
    }

    public static Specification inNaturesSon3(List natures) {
    return (root, query, cb) -> {
    final Root son3Root = cb.treat(root, Son3.class);
    return son3Root.get(Constants.NATURE).in(natures);
    }
    }

    SpecSon2 son2Spec = ParentObjectPredicat.inNaturesSon2(natures);
    SpecSon3 son3Spec = ParentObjectPredicat.inNaturesSon3(natures);

    Specification specifications = Specification.where(son2Spec).and(son3Spec);
    Iterable listOfSons = this.parentObjectRepository.findAll(specifications);
    What I got as result:

    org.springframework.dao.InvalidDataAccessApiUsageException: Illegal attempt to dereference path source [treat(null as mypackage.Son2).nature] of basic type; nested exception is java.lang.IllegalStateException: Illegal attempt to dereference path source [treat(null as mypackage.Son2).nature] of basic type

    From my understanding treat() is used to resolve subclass. Any suggestions on how to do this?

    • Petri Jul 31, 2018 @ 13:46

      Hi,

      Unfortunately you cannot write your query by using JPA because JPA doesn't allow you to use attributes which aren't found from all child classes if you want to "return" super class objects. As far as I know, you have two options:

      • You can invoke two queries which fetch Son2 and Son3 objects and combine the query results in Java code.
      • You can use SQL.

      If you have any additional questions, don't hesitate to ask them.

      • Hi Petry Jul 31, 2018 @ 21:40

        Thnx a lot for your answer, this is what i taught but i wasn't sure, thnx again

        Sephi

        • Petri Aug 2, 2018 @ 10:16

          Hi,

          you are welcome.

  • Mini Jan 19, 2019 @ 20:13

    //I want to find who are all entered first post comment with in an hour after their account created

    Path accountCreatedTime = root. get("AccountCreatedTime");
    Path FirstPostCreatedTime = root. get("FirstPostCreatedTime");

    final Predicate timeInHourPredicate = criteriaBuilder
    .greaterThanOrEqualTo(accountCreatedTime, FirstPostCreatedTime);

    Example,

    1. Account Created at: 2018-SEP-10 at 10am and First Post entered 2018-SEP-10 at 10.15 am this recond should be fetched. (FALLS IN AN HOUR)
    2. Account Created at: 2018-SEP-10 at 10am and First Post entered 2018-SEP-10 at 3.50 pm this SHOULD NOT be fetched.

    Is there any way to add or separate hours from Path accountCreatedTime? or can we get difference between Path accountCreatedTime and Path FirstPostCreatedTime in hours and in criteriaBuilder

    • Petri Jan 24, 2019 @ 19:27

      Hi,

      To be honest, I haven't used the JPA criteria API for several years, and I don't remember if it's possible to do the things you need. However, I did quick Google search and found out that some implementations allow you to extract date and time parts.

      I recommend that you use SQL for this query. If you have to use the JPA Criteria API, I recommend that you contact Thorben Janssen. I am sure that he can answer to your questions.

  • sobhan Nov 28, 2021 @ 14:28

    Hi,
    I had some requirements so I searched a bit and find out your tutorial about JpaSpecification
    I could implement it with your blog post, but yet I have some requirements like joining tables or retrieving unknown resultSets that are return from complex queries, it will be great if you mention them here because nowhere has taught even these basics as well as you.
    thanks!

  • farzad May 11, 2022 @ 6:59

    Thanks a lot for you master

    • Petri May 11, 2022 @ 19:30

      You are welcome!

Leave a Reply