Spring Data JPA Tutorial Part Four: JPA Criteria Queries

by Petri Kainulainen on February 12, 2012

Stay in course

The third part of my Spring Data JPA tutorial described how you can create custom queries by using query methods. This blog entry will describe how you can implement more advanced queries by using the JPA criteria API.

If you have read the previous part of this tutorial, you might remember that the search function which I used as an example returned all persons whose last name matched with the given search criteria. This requirement is now replaced with a new one:

  • The search function must return only such persons whose last name begins with the given search term.

I am going to walk you through the implementation of this requirement next.

Required Steps

The steps required to implement the new search function are following:

  • Creating the JPA criteria query.
  • Extending the repository to support JPA criteria queries.
  • Using the created criteria query and repository.

Each of these steps is described with more details in following.

Building the JPA Criteria Query

Spring Data JPA uses the specification pattern for providing an API which is used to create queries with the JPA criteria API. The heart of this API is the Specification interface which contains a single method called toPredicate(). In order to build the required criteria query, you must create a new implementation of the Specification interface and build the predicate in the toPredicate() method.

Before going in to the details, I will introduce the source code of my static meta model class which is used to create type safe queries with the JPA criteria API. The source code of the Person_ class is given in following:

import javax.persistence.metamodel.SingularAttribute;
import javax.persistence.metamodel.StaticMetamodel;

/**
 * A  meta model class used to create type safe queries from person
 * information.
 * @author Petri Kainulainen
 */
@StaticMetamodel(Person.class)
public class Person_ {
    public static volatile SingularAttribute<Person, String> lastName;
}

A clean way to create specifications is to implement a specification builder class and use static methods to build the actual specification instances. My specification builder class is called PersonSpecifications and its source code given in following:

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;

/**
 * A class which is used to create Specification objects which are used
 * to create JPA criteria queries for person information.
 * @author Petri Kainulainen
 */
public class PersonSpecifications {

    /**
     * Creates a specification used to find persons whose last name begins with
     * the given search term. This search is case insensitive.
     * @param searchTerm
     * @return
     */
    public static Specification<Person> lastNameIsLike(final String searchTerm) {
       
        return new Specification<Person>() {
            @Override
            public Predicate toPredicate(Root<Person> personRoot, CriteriaQuery<?> query, CriteriaBuilder cb) {
                String likePattern = getLikePattern(searchTerm);                
                return cb.like(cb.lower(personRoot.<String>get(Person_.lastName)), likePattern);
            }
           
            private String getLikePattern(final String searchTerm) {
                StringBuilder pattern = new StringBuilder();
                pattern.append(searchTerm.toLowerCase());
                pattern.append("%");
                return pattern.toString();
            }
        };
    }
}

Testing my specification builder implementation is pretty straightforward. I used Mockito mocking framework to mock the JPA criteria API. The source code of my test class is given following:

import org.junit.Before;
import org.junit.Test;
import org.springframework.data.jpa.domain.Specification;

import javax.persistence.criteria.*;

import static junit.framework.Assert.assertEquals;
import static org.mockito.Mockito.*;

public class PersonSpecificationsTest {
   
    private static final String SEARCH_TERM = "Foo";
    private static final String SEARCH_TERM_LIKE_PATTERN = "foo%";
   
    private CriteriaBuilder criteriaBuilderMock;
   
    private CriteriaQuery criteriaQueryMock;
   
    private Root<Person> personRootMock;

    @Before
    public void setUp() {
        criteriaBuilderMock = mock(CriteriaBuilder.class);
        criteriaQueryMock = mock(CriteriaQuery.class);
        personRootMock = mock(Root.class);
    }

    @Test
    public void lastNameIsLike() {
        Path lastNamePathMock = mock(Path.class);        
        when(personRootMock.get(Person_.lastName)).thenReturn(lastNamePathMock);
       
        Expression lastNameToLowerExpressionMock = mock(Expression.class);
        when(criteriaBuilderMock.lower(lastNamePathMock)).thenReturn(lastNameToLowerExpressionMock);
       
        Predicate lastNameIsLikePredicateMock = mock(Predicate.class);
        when(criteriaBuilderMock.like(lastNameToLowerExpressionMock, SEARCH_TERM_LIKE_PATTERN)).thenReturn(lastNameIsLikePredicateMock);

        Specification<Person> actual = PersonSpecifications.lastNameIsLike(SEARCH_TERM);
        Predicate actualPredicate = actual.toPredicate(personRootMock, criteriaQueryMock, criteriaBuilderMock);
       
        verify(personRootMock, times(1)).get(Person_.lastName);
        verifyNoMoreInteractions(personRootMock);
       
        verify(criteriaBuilderMock, times(1)).lower(lastNamePathMock);
        verify(criteriaBuilderMock, times(1)).like(lastNameToLowerExpressionMock, SEARCH_TERM_LIKE_PATTERN);
        verifyNoMoreInteractions(criteriaBuilderMock);

        verifyZeroInteractions(criteriaQueryMock, lastNamePathMock, lastNameIsLikePredicateMock);

        assertEquals(lastNameIsLikePredicateMock, actualPredicate);
    }
}

However, if you have to build more complex queries by using this approach, testing your queries will become more troublesome because the JPA criteria API is not the easiest one to mock. In this case it is a good idea to divide the search conditions into multiple specifications and use the Specifications class to combine your specification instances. This way your unit tests don’t become so complex but you can still harness the power of the JPA criteria API in your application.

Extending the Repository

Extending your Spring Data JPA repository to support JPA criteria queries is quite easy. All you have to do is to extend the JpaSpecificationExecutor interface. This gives you access to the findAll(Specification spec) method which returns all entities fulfilling the search conditions specified by the specification. The source code of my PersonRepository is given in following:

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

/**
 * Specifies methods used to obtain and modify person related information
 * which is stored in the database.
 * @author Petri Kainulainen
 */
public interface PersonRepository extends JpaRepository<Person, Long>, JpaSpecificationExecutor<Person> {

}

Using the Specification Builder and the Repository

The last step is to implement the service class which uses the created specification builder and the repository. The search() method of the PersonService interface takes the used search term as a parameter. The relevant part of the PersonService interface is given in following:

/**
 * Declares methods used to obtain and modify person information.
 * @author Petri Kainulainen
 */
public interface PersonService {

    /**
     * Searches persons by using the given search term as a parameter.
     * @param searchTerm
     * @return  A list of persons whose last name begins with the given search term. If no persons is found, this method
     *          returns an empty list. This search is case insensitive.
     */
    public List<Person> search(String searchTerm);
}

The implementation of the search() method is very simple. It simply passes the search term to the lastNameIsLike() method of the PersonSpecifications class and gives the created specification object to the PersonRepository. The source code of the implementation of the search() method is given in following:

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;

/**
 * This implementation of the PersonService interface communicates with
 * the database by using a Spring Data JPA repository.
 * @author Petri Kainulainen
 */
@Service
public class RepositoryPersonService implements PersonService {
   
    private static final Logger LOGGER = LoggerFactory.getLogger(RepositoryPersonService.class);
   
    @Resource
    private PersonRepository personRepository;

    @Transactional(readOnly = true)
    @Override
    public List<Person> search(String searchTerm) {
        LOGGER.debug("Searching persons with search term: " + searchTerm);

        //Passes the specification created by PersonSpecifications class to the repository.
        return personRepository.findAll(lastNameIsLike(searchTerm));
    }
}

This solution is not perfect from the architectural point of view because it introduces a dependency between the service layer and the Spring Data JPA. A general guideline is that an upper layer should not have any knowledge about the implementation details of the layers located below it.

One solution to this problem would be to create a custom search method and integrate it with the generic repository abstraction as described in the reference manual of Spring Data JPA. However, this would mean that you would have to write a lot of boilerplate code which does not really add any value to your application. Also, since the goal of the Spring Data JPA is to reduce the amount of boilerplate code, I think that my solution is an acceptable compromise between over engineering and getting things done in a clean way.

Oh, this reminds me of something. We should not forget the unit test of the search() method. The source code of this unit test is given in following:

import org.junit.Before;
import org.junit.Test;
import org.springframework.data.jpa.domain.Specification;

import static junit.framework.Assert.assertEquals;
import static org.mockito.Mockito.*;

public class RepositoryPersonServiceTest {

    private static final String SEARCH_TERM = "foo";
   
    private RepositoryPersonService personService;

    private PersonRepository personRepositoryMock;

    @Before
    public void setUp() {
        personService = new RepositoryPersonService();

        personRepositoryMock = mock(PersonRepository.class);
        personService.setPersonRepository(personRepositoryMock);
    }

    @Test
    public void search() {
        List<Person> expected = new ArrayList<Person>();
        when(personRepositoryMock.findAll(any(Specification.class))).thenReturn(expected);
       
        List<Person> actual = personService.search(SEARCH_TERM);
       
        verify(personRepositoryMock, times(1)).findAll(any(Specification.class));
        verifyNoMoreInteractions(personRepositoryMock);
       
        assertEquals(expected, actual);
    }
}

What is Next?

I have now demonstrated to you how you can use the JPA criteria API with Spring Data JPA. As always, the example application of this blog entry is available at Github. The next part of my Spring Data JPA tutorial describes how you can use Querydsl for building queries with Spring Data JPA.

About the Author

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

About Petri Kainulainen →

Tagged as: spring data, spring data jpa, Spring Framework

Related Posts

{ 70 comments… add one }

  • Stone February 15, 2012 at 8:43 pm

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

    Reply

    • Petri February 15, 2012 at 9:54 pm

      Stone,

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

      Reply

      • Stone February 28, 2012 at 11:21 pm

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

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

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

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

        Person returned = personService.update(updated);

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

        assertPerson(updated, returned);

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

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

        Reply

        • Petri February 29, 2012 at 10:17 am

          Stone,

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

          Reply

        • Petri February 29, 2012 at 7:11 pm

          Stone,

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

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

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

          Common causes for the problem you were having are:

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

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

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

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

          Reply

  • David February 19, 2012 at 5:22 am

    Hi Petri,

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

    List repository.findAll(Specification s);

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

    T repository.find(Specification s);

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

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

    Thanks,
    David

    Reply

  • amol April 12, 2012 at 5:53 pm

    Hi Petri,

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

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

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

    Reply

    • Petri April 12, 2012 at 6:25 pm

      Hi Amol,

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

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

      • What kind of entities do you have?
      • What kind of condition are you trying to implement with JPA criteria API?
      Reply

  • albert April 17, 2012 at 10:48 am

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

    Thanks in advance!!!

    Reply

    • Petri April 17, 2012 at 10:55 am

      Hi Albert,

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

      Reply

    • Petri April 17, 2012 at 5:11 pm

      Hi Albert,

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


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

      public Person() {

      }

      public Address getAddress() {
      return address;
      }
      }

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


      @Embeddable
      public class Address {

      private String street;

      private int number;

      public Address() {

      }

      public String getStreet() {
      return street;
      }

      public int getNumber() {
      return number;
      }
      }

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


      public class PersonSpecifications {

      public static Specification livesInGivenStreet(final String street) {

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

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

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

      Reply

      • albert April 17, 2012 at 6:15 pm

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

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

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

        Reply

  • amol April 18, 2012 at 1:45 pm

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

    Check – main search table has
    userid
    and other fields

    User table has
    userid
    and other fields

    UserDetail table has
    userid
    firstname
    lastname

    the domain model is

    Check class
    has User

    User class
    has userDetail

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

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

    Thanks in advance

    Reply

    • Petri April 18, 2012 at 7:35 pm

      Hi Amol,

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


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

      Reply

      • amol April 18, 2012 at 8:09 pm

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

        Reply

        • Petri April 18, 2012 at 8:37 pm

          Hi Amol,

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

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

          Reply

          • amol April 18, 2012 at 9:03 pm

            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 April 18, 2012 at 9:21 pm

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

  • amol April 18, 2012 at 11:19 pm

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

    Reply

    • Petri April 19, 2012 at 9:44 am

      In this case the following specification builder should work:


      public class CheckRecordSpecifications {

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

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

      Reply

  • amol April 19, 2012 at 4:11 pm

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

    Reply

  • albert April 23, 2012 at 2:05 pm

    Hi Petri!

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

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

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

    Tahnks in advance

    Reply

    • Petri April 23, 2012 at 7:32 pm

      Hi Albert,

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

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

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

      Reply

      • albert April 24, 2012 at 12:43 pm

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

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

        Thanks for your replying.

        Reply

  • Raghu July 31, 2012 at 4:47 am

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

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

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

    Thank you in advance.

    Reply

    • Petri July 31, 2012 at 10:05 am

      Hi Raghu,

      You have two options for implementing this:

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

      JPQL Example:

      SELECT DISTINCT p FROM Person p WHERE...

      Criteria API with Specification Builder:

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

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

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

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

      Reply

  • Dhana kumar August 10, 2012 at 4:57 pm

    Hi Petri,

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

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

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

    Reply

    • Petri August 10, 2012 at 10:52 pm

      Hi Dhana,

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

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

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

      Also, check out Collections in JPQL and Criteria Queries.

      Reply

      • Dhana kumar August 11, 2012 at 6:32 pm

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

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

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

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

        Reply

        • Petri August 11, 2012 at 6:45 pm

          Hi Dhana,

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

          return builder.isMember(roleName, roleExpression);

          To:

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

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

          Reply

  • Dhana kumar August 11, 2012 at 8:34 pm

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

    Reply

    • Petri August 11, 2012 at 8:53 pm

      Hi Dhana,

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

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

      Reply

  • Dhana kumar August 12, 2012 at 1:15 pm

    Thanks, it works now, appreciate your help.

    Reply

  • Gauthier Peel September 5, 2012 at 1:48 pm

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

    Reply

    • Martin December 21, 2012 at 10:13 am

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

      TIA

      Reply

      • Petri December 21, 2012 at 10:44 am

        Follow these steps:

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

        Let me know if this solved your problem.

        Reply

  • Alex September 25, 2012 at 10:01 pm

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

    Reply

  • Lev December 18, 2012 at 8:47 pm

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

    Reply

    • Petri December 18, 2012 at 11:42 pm

      Hi Lev,

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

      public class PersonSpecifications {
	public static Specification<Person> idIsIn(final List<Long> ids) {

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

      Reply

  • MiB January 21, 2013 at 2:20 pm

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

    Reply

  • Lev February 4, 2013 at 10:27 am

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

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

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

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

    Thanks a lot,
    Lev

    Reply

    • Petri February 4, 2013 at 9:51 pm

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

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

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

      public class PersonSpecifications {
  public static Specification<Person> trialPeriodHasPassed() {

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


      Did this solve your problem?

      Reply

  • i42 March 6, 2013 at 7:35 pm

    Hi Petri,

    thanx for your valuable article…

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

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

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

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

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

    ++

    i42

    Reply

    • Petri March 6, 2013 at 8:30 pm

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

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

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

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

      Reply

  • i42 March 7, 2013 at 1:55 pm

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

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

    yes

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

    yes

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

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

    your both previous assumptions were right Petri…

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

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

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

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

    Reply

  • i42 March 15, 2013 at 6:15 pm

    No further comment Petri ?

    Reply

    • Petri March 15, 2013 at 8:33 pm

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

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

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

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

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

      Reply

  • i42 March 18, 2013 at 11:36 am

    Hey,

    no worries mate…

    to re-sum up the scene:

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

    -> issue: method is returning duplicates entries with JPA

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

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

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

    my temporary empirik conclusion are:

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

    so Petri, do you like challenges ?

    :)

    i42

    Reply

    • Petri March 18, 2013 at 10:47 pm

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

      Reply

    • Petri March 24, 2013 at 10:41 pm

      I have started working on this.

      Reply

    • Petri April 7, 2013 at 5:59 pm

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

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

      Reply

      • i42 April 8, 2013 at 12:08 pm

        Thx a lot Petri for investing my problem…

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

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

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

        Dimitri42

        Reply

        • Petri April 8, 2013 at 7:54 pm

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

          Reply

  • J Romero March 30, 2013 at 2:17 am

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

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

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

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

    Reply

    • Petri April 1, 2013 at 11:32 am

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

      Reply

Leave a Comment

Next post:

Previous post:

Follow Me

Spring Data Book

Implement JPA repositories with less code and harness the performance of Redis in your applications.

Read more →

Tutorials