Spring Data JPA Tutorial Part Three: Custom Queries with Query Methods

by PETRI KAINULAINEN on FEBRUARY 5, 2012

The second part of my Spring Data JPA tutorialdescribed how you can create a simple CRUD application with Spring Data JPA. This blog entry will describe how you can use query methods for creating custom queries with Spring Data JPA. In order to have a reasonable example, I have created three new requirements for my example application:

• It must be possible to search persons by using their last name as a search criteria.
• The search function must return only such persons which last name is an exact match with the given search criteria.
• The search must be case insensitive.

It is time to get to work and start extending the example application.

Required Steps

The steps required to fulfill the given requirements are following:

• Creating a query method.
• Using the created query method.

Spring Data JPA provides three different approaches for creating custom queries with query methods. Each of these approaches is described in following.

Query Creation from Method Name

Spring Data JPA has a built in query creation mechanism which can be used for parsing queries straight from the method name of a query method. This mechanism first removes common prefixes from the method name and parses the constraints of the query from the rest of the method name. The query builder mechanism is described with more details in Defining Query Methods Subsection of Spring Data JPA reference documentation.

Using this approach is quite simple. All you have to do is to ensure that the method names of your repository interface are created by combining the property names of an entity object and the supported keywords. The Query Creation Subsection of the Spring Data JPA reference documentation has nice examples concerning the usage of supported keywords.

The source code of the repository method which is using this approach is given in following:

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

/**
 * 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> {

    /**
     * Finds persons by using the last name as a search criteria.
     * @param lastName  
     * @return  A list of persons which last name is an exact match with the given last name.
     *          If no persons is found, this method returns an empty list.
     */
    public List<Person> findByLastName(String lastName);
}

The strength of this approach is that it is rather fast to implement simple queries. On the other hand, if your query has many parameters, your method name will be rather long and ugly. Also, if the keyword you need is not supported by Spring Data JPA, you are out of luck.

A good example of this is the fact that at the moment you cannot use the lower keyword in your method names. This means that this approach cannot be used to fulfill the requirements which I specified in the beginning.

JPA Named Queries

Spring Data JPA provides also support for the JPA Named Queries. You have got following alternatives for declaring the named queries:

• You can use either named-query XML element or @NamedQuery annotation to create named queries with the JPA query language.
• You can use either named-native-query XML element or @NamedNative query annotation to create queries with SQL if you are ready to tie your application with a specific database platform.

The only thing you have to do to use the created named queries is to name the query method of your repository interface to match with the name of your named query. I have chosen to specify the named query by using @NamedQuery annotation in my entity class.

The source code of the Person class is given in following:

import org.apache.commons.lang.builder.ToStringBuilder;

import javax.persistence.*;

/**
 * An entity class which contains the information of a single person.
 * @author Petri Kainulainen
 */
@Entity
@NamedQuery(name = "Person.findByName", query = "SELECT p FROM Person p WHERE LOWER(p.lastName) = LOWER(?1)")
@Table(name = "persons")
public class Person {
    
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
    
    @Column(name = "creation_time", nullable = false)
    private Date creationTime;
    
    @Column(name = "first_name", nullable = false)
    private String firstName;

    @Column(name = "last_name", nullable = false)
    private String lastName;
    
    @Column(name = "modification_time", nullable = false)
    private Date modificationTime;
    
    @Version
    private long version = 0;

    public Long getId() {
        return id;
    }

    /**
     * Gets a builder which is used to create Person objects.
     * @param firstName The first name of the created user.
     * @param lastName  The last name of the created user.
     * @return  A new Builder instance.
     */
    public static Builder getBuilder(String firstName, String lastName) {
        return new Builder(firstName, lastName);
    }
    
    public Date getCreationTime() {
        return creationTime;
    }

    public String getFirstName() {
        return firstName;
    }

    public String getLastName() {
        return lastName;
    }

    /**
     * Gets the full name of the person.
     * @return  The full name of the person.
     */
    @Transient
    public String getName() {
        StringBuilder name = new StringBuilder();
        
        name.append(firstName);
        name.append(" ");
        name.append(lastName);
        
        return name.toString();
    }

    public Date getModificationTime() {
        return modificationTime;
    }

    public long getVersion() {
        return version;
    }

    public void update(String firstName, String lastName) {
        this.firstName = firstName;
        this.lastName = lastName;
    }
    
    @PreUpdate
    public void preUpdate() {
        modificationTime = new Date();
    }
    
    @PrePersist
    public void prePersist() {
        Date now = new Date();
        creationTime = now;
        modificationTime = now;
    }

    @Override
    public String toString() {
        return ToStringBuilder.reflectionToString(this);
    }

    /**
     * A Builder class used to create new Person objects.
     */
    public static class Builder {
        Person built;

        /**
         * Creates a new Builder instance.
         * @param firstName The first name of the created Person object.
         * @param lastName  The last name of the created Person object.
         */
        Builder(String firstName, String lastName) {
            built = new Person();
            built.firstName = firstName;
            built.lastName = lastName;
        }

        /**
         * Builds the new Person object.
         * @return  The created Person object.
         */
        public Person build() {
            return built;
        }
    }

    /**
     * This setter method should only be used by unit tests.
     * @param id
     */
    protected void setId(Long id) {
        this.id = id;
    }
}

The relevant part of my PersonRepository interface looks following:

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

/**
 * 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> {

    /**
     * Finds person by using the last name as a search criteria.
     * @param lastName
     * @return  A list of persons whose last name is an exact match with the given last name.
     *          If no persons is found, this method returns null.
     */
    public List<Person> findByName(String lastName);
}

Using named queries is valid option if your application is small or if you have to use native queries. If your application has a lot of custom queries, this approach will litter the code of your entity class with query declarations (You can of course use the XML configuration to avoid this but in my opinion this approach is even more horrible).

@Query Annotation

The @Query annotation can be used to create queries by using the JPA query language and to bind these queries directly to the methods of your repository interface. When the query method is called, Spring Data JPA will execute the query specified by the @Query annotation (If there is a collision between the @Query annotation and the named queries, the query specified by using@Query annotation will be executed).

The source code of the repository method which is implemented by using this approach is given in following:

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

/**
 * 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> {

    /**
     * Finds a person by using the last name as a search criteria.
     * @param lastName
     * @return  A list of persons whose last name is an exact match with the given last name.
     *          If no persons is found, this method returns an empty list.
     */
    @Query("SELECT p FROM Person p WHERE LOWER(p.lastName) = LOWER(:lastName)")
    public List<Person> find(@Param("lastName") String lastName);
}

This approach gives you access to the JPA query language and keeps your queries in the repository layer where they belong. On the other hand, you cannot use the @Query annotation (I will describe more advanced strategies in the next parts of this tutorial) if the JPA query language cannot be used to create the query you need.

Using Created Query Methods

I have now described you three ways to create query methods with Spring Data JPA. The next step is to take a look of the service class which uses the created query methods.

The SearchType enumeration identifies the used query method. Its source code is given in following:

/**
 * Describes the search type of the search. Legal values are:
 *

 *    
• METHOD_NAME which means that the query is obtained from the method name of the query method.

 *    
• NAMED_QUERY which means that a named query is used.

 *    
• QUERY_ANNOTATION which means that the query method annotated with @Query annotation is used.

 *

 * @author Petri Kainulainen
 */
public enum SearchType {
    METHOD_NAME,
    NAMED_QUERY,
    QUERY_ANNOTATION;
}

The SearchDTO is a simple DTO object which contains the search term given by the user and identifies the used query method. Its source code is given in following:

import org.apache.commons.lang.builder.ToStringBuilder;

/**
 * A DTO class which is used as a form object in the search form.
 * @author Petri Kainulainen
 */
public class SearchDTO {

    private String searchTerm;

    private SearchType searchType;

    public SearchDTO() {

    }

    public String getSearchTerm() {
        return searchTerm;
    }

    public void setSearchTerm(String searchTerm) {
        this.searchTerm = searchTerm;
    }

    public SearchType getSearchType() {
        return searchType;
    }

    public void setSearchType(SearchType searchType) {
        this.searchType = searchType;
    }

    @Override
    public String toString() {
        return ToStringBuilder.reflectionToString(this);
    }
}

The PersonService interface has got one new method . The relevant part of the PersonServiceinterface is described in following:

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

    /**
     * Searches persons by using the search criteria given as a parameter.
     * @param searchCriteria
     * @return  A list of persons matching with the search criteria. If no persons is found, this method
     *          returns an empty list.
     * @throws IllegalArgumentException if search type is not given.
     */
    public List<Person> search(SearchDTO searchCriteria);
}

The actual implementation of the search() method is responsible of selecting the correct query method and passing the given search term to it. The source code of my search() method implementation 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(SearchDTO searchCriteria) {
        LOGGER.debug("Searching persons with search criteria: " + searchCriteria);
        
        String searchTerm = searchCriteria.getSearchTerm();
        SearchType searchType = searchCriteria.getSearchType();
        
        if (searchType == null) {
            throw new IllegalArgumentException();
        }
         
        return findPersonsBySearchType(searchTerm, searchType);
    }
    
    private List<Person> findPersonsBySearchType(String searchTerm, SearchType searchType) {
        List<Person> persons;

        if (searchType == SearchType.METHOD_NAME) {
            LOGGER.debug("Searching persons by using method name query creation.");
            persons = personRepository.findByLastName(searchTerm);
        }
        else if (searchType == SearchType.NAMED_QUERY) {
            LOGGER.debug("Searching persons by using named query");
            persons = personRepository.findByName(searchTerm);
        }
        else {
            LOGGER.debug("Searching persons by using query annotation");
            persons = personRepository.find(searchTerm);
        }

        return persons;
    }
}

Naturally the created search() must be tested as well. The source code of the relevant unit tests is given in following:

import org.junit.Before;
import org.junit.Test;

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

public class RepositoryPersonServiceTest {

    private static final String LAST_NAME = "Bar";
    
    private RepositoryPersonService personService;

    private PersonRepository personRepositoryMock;

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

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

    @Test
    public void searchWhenSearchTypeIsMethodName() {
        SearchDTO searchCriteria = createSearchDTO(LAST_NAME, SearchType.METHOD_NAME);
        List<Person> expected = new ArrayList<Person>();
        when(personRepositoryMock.findByLastName(searchCriteria.getSearchTerm())).thenReturn(expected);
        
        List<Person> actual = personService.search(searchCriteria);
        
        verify(personRepositoryMock, times(1)).findByLastName(searchCriteria.getSearchTerm());
        verifyNoMoreInteractions(personRepositoryMock);
        
        assertEquals(expected, actual);
    }

    @Test
    public void searchWhenSearchTypeIsNamedQuery() {
        SearchDTO searchCriteria = createSearchDTO(LAST_NAME, SearchType.NAMED_QUERY);
        List<Person> expected = new ArrayList<Person>();
        when(personRepositoryMock.findByName(searchCriteria.getSearchTerm())).thenReturn(expected);

        List<Person> actual = personService.search(searchCriteria);

        verify(personRepositoryMock, times(1)).findByName(searchCriteria.getSearchTerm());
        verifyNoMoreInteractions(personRepositoryMock);

        assertEquals(expected, actual);
    }

    @Test
    public void searchWhenSearchTypeIsQueryAnnotation() {
        SearchDTO searchCriteria = createSearchDTO(LAST_NAME, SearchType.QUERY_ANNOTATION);
        List<Person> expected = new ArrayList<Person>();
        when(personRepositoryMock.find(searchCriteria.getSearchTerm())).thenReturn(expected);

        List<Person> actual = personService.search(searchCriteria);

        verify(personRepositoryMock, times(1)).find(searchCriteria.getSearchTerm());
        verifyNoMoreInteractions(personRepositoryMock);

        assertEquals(expected, actual);
    }

    @Test(expected = IllegalArgumentException.class)
    public void searchWhenSearchTypeIsNull() {
        SearchDTO searchCriteria = createSearchDTO(LAST_NAME, null);

        personService.search(searchCriteria);

        verifyZeroInteractions(personRepositoryMock);
    }
    
    private SearchDTO createSearchDTO(String searchTerm, SearchType searchType) {
        SearchDTO searchCriteria = new SearchDTO();
        searchCriteria.setSearchTerm(searchTerm);
        searchCriteria.setSearchType(searchType);
        return searchCriteria;
    }
}

What is Next?

I have now described to you how you can use query methods for creating custom queries with Spring Data JPA. If you are interested of seeing my example application in action, you can get it from Github. The next part of my Spring Data JPA tutorial will describe how you can create JPA criteria queries with Spring Data JPA.

About the Author

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

About Petri Kainulainen →