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

The second part of my Spring Data JPA tutorial described 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.

This blog post is outdated. You can read the new blog posts by clicking these links:

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:
 * <ul>
 *     <li>METHOD_NAME which means that the query is obtained from the method name of the query method.</li>
 *     <li>NAMED_QUERY which means that a named query is used.</li>
 *     <li>QUERY_ANNOTATION which means that the query method annotated with @Query annotation is used.</li>
 * </ul>
 * @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 PersonService interface 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.

If you want to learn how to use Spring Data JPA, you should read my Spring Data JPA tutorial.
I have closed the comments of this blog post because this blog post is outdated. You can read the new blog posts by clicking these links:

Comments on this entry are closed.

  • Bruce Apr 9, 2012 @ 16:38

    Excellent tutorials. I especially like how you use Java classes to configure your Spring managed beans and JPA configuration.

    You might consider adding to each projects README how to run your examples using the Jetty Maven plugin you've included in your pom.xml. I used mvn -e clean jetty:run and then in a web browser went to http://localhost:8080.

    The Jetty Maven plugin is very handy for running a web application locally, but some Maven users may not be familiar with the Jetty Maven goals.

    Thanks for posting this series of tutorials on Spring Data JPA.

    • Petri Apr 9, 2012 @ 17:02

      Hi Bruce,

      Thanks for you comment. I am happy to hear that you enjoyed my tutorials. Also, I agree that I should have added the usage instructions of the Jetty Maven plugin to the README files of my example applications. I will do this ASAP.

  • venu Jun 13, 2012 @ 10:14

    Hi Petri Kainulainen,

    I have a requirement that i want to insert the defualt user in database ,while running the application , after that i have to login with that user credentials,and then i create as many users, for now i am entering the default user from backend script in to database, i am using spring mvc+Jpa,please suggest me your idea or any working example how to do this.

    thanks in advance
    venu

    • Petri Sep 9, 2012 @ 12:23

      Hi Venu,

      I would continue using the script since it is the simplest way to do this. Just make sure that the script is run when the application starts and do nothing if the default user exists.

  • charlesChan Sep 9, 2012 @ 12:05

    hi Petri,
    Your tutorials is excellent. Thanks! But I have a problem, if I want to select some column and don't want to select other column, what can I do?like the Person entity, if I want to select the last_name(just this column), but others column I don't want to select ,what can I do for spring data jpa?

    • Petri Sep 9, 2012 @ 12:16

      Hi charlesChan,

      you can use the @Query annotation together with JPQL. Add the following query method to your repository interface (Naturally this one creates duplicates. If you want to remove them, use the DISTINCT keyword):

      @Query("SELECT p.lastName FROM Person p")
      public List<String> getLastNames();

      • charlesChan Sep 9, 2012 @ 13:14

        Thanks. But if I want to select two or three or more, but no full select. Can I write like the below:

        @Query("SELECT p.lastName,p.creationTime FROM Person p where p.lastName=?1")
        public List<String> findByLastName(String lastName);

        • Petri Sep 9, 2012 @ 14:38

          Hi,

          In this case you would have to first create a DTO class that contains the wanted properties. In your case, the content of this would be following:

          public class PersonDTO {
          private String lastName;
          private Date creationTime;

          public PersonDTO() {
          }

          //Getters and setters
          }

          When this done, you should add the following query method to your repository:

          @Query("SELECT p.lastName,p.creationTime FROM Person p where p.lastName=:lastName")
          public List<PersonDTO> findByLastName(@Param("lastName") String lastName);

          I hope that this solves your problem.

          • charlesChan Sep 9, 2012 @ 15:10

            Yes.I know how to do now! Thank you very much!!!

          • Petri Sep 9, 2012 @ 21:58

            Great to hear that.

      • Sandeep Allampalli Jan 31, 2015 @ 18:56

        This does not work, It returns the whole Person object instead of List of String.
        @Query("SELECT DISTICT p.lastName FROM Person p")
        public List getLastNames();

        • Petri Jan 31, 2015 @ 20:48

          I am not sure what your problem is because when I added that query method to the PersonRepository interface of the example application, it returned a list of unique last names that are found from the database.

          My query method looks as follows:

          
          @Query("SELECT DISTINCT p.lastName FROM Person p")
          public List<String> getLastNames();
          
          
          • Sandeep Allampalli Feb 1, 2015 @ 7:11

            Mine looks like :
            @Query("SELECT DISTINCT t.createdBy FROM MyEntity t")
            public List getCreatedByNames();

            And some how it returns the entire entity objects in a JSON array.
            Any idea what I could be doing wrong ?

          • Sandeep Feb 1, 2015 @ 19:23

            Nevermind, I got it working. I had @Query(name="myquery") instead of @Query("myquery"). Thanks !

          • Petri Feb 3, 2015 @ 18:17

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

  • thiago henrique Mar 15, 2013 @ 16:09

    how to use this script "X" whith method when? How to location config?

    • Petri Mar 15, 2013 @ 16:32

      Which script are you referring to?

      The configuration of the example application is described in the first part of my Spring Data JPA tutorial.

      You can locate the used configuration classes and files by following these instructions:

      • The application context configuration class (ApplicationContext) and the web application initializer class (DataJPAExampleInitializer) are found from the net.petrikainulainen.spring.datajpa.config package.
      • The application context XML configuration file (applicationContext.xml) and the used properties file (application.properties) are found from the src/main/resources directory.

      You can get the example application from Github.

  • thiago henrique Mar 15, 2013 @ 16:58

    Thanks, i resolved.

    Just import package Mock correctly, in case import static org.mockito.Mockito.*; and not import static org.mockito.Mockito.mock;

    • Petri Mar 15, 2013 @ 20:37

      Great. I am happy to hear that you solved your problem.

  • Thiago Henrique Mar 16, 2013 @ 4:11

    Thanks for your suggestion, nice good! Your example is a hand in wheels. Congratulations for your initiative!

  • A.M. Jun 28, 2013 @ 13:05

    Nice post!

    Is it possible to define the type of table join using the method-name query approach? My case is the following:

    Let's say Person has a 1 to Many unidirectional relation with Car and we search all persons with a red car.

    
    public class Person {
    	@Id
    	@Column(name = "ID_PERSON")
    	private Long id;
    	
    	@OneToMany(cascade = CascadeType.ALL, orphanRemoval=true)
    	@JoinColumn(name = "ID_PERSON")
    	@ForeignKey(name = "FK_CAR")
    	private Collection cars;
    }
    public class Car{
    	@Id
    	private long id;
    	private String colour;
    }
    
    public interface PersonRepository extends JpaRepository {
            //1.
    	List findByCar_ColourStartingWith(String colour);
    
           //2.
           @Query("select op from Person op JOIN op.cars t where t.colour like ?1%")
    	List findByColourLike(String colour);
    }
    
    

    When I add a Person with two cars the 1rst method results in

    
    select … from Person p_
    left outer join Car car1_ on p_.id_person=car1_.id_person
    left outer join Car car2_ on p_.id_person=car2_.id_person
    where (car2_.colour like ?)
    
    

    while the correct query would be the one produced from the 2nd method:

    
    select … from Person p_
    inner join Car car1_ on p_.id_person=car1_.id_person
    where (car1_.colour like ?)
    
    

    I have no idea why there are two outer joins, any hints?

    • Petri Jun 28, 2013 @ 13:24

      I think that it is not possible to specify the join type when you are creating your query by using the query generation from method name strategy. At least the list of supported keywords does not contain anything related to joins.

      I assume that the query generation from method name strategy is meant to be used only for creating "simple" queries, or at least it kind of makes sense. This would mean that the more complex queries have to be created by using the @Query annotation (like you did).

  • P.K Aug 3, 2013 @ 13:38

    How to limit search results with @Query annotation? I would like to add LIMIT 1 for improved performance.

    • Petri Aug 3, 2013 @ 13:57

      The LIMIT keyword is not supported by JPQL.

      If your JPQL query returns more than one entity and you want want to get the first entity, you can paginate your query results in a such way that only the wanted entity is returned (set the page size to 1 and get the first page).

      I hope that this answered to your question.

  • varun Sep 5, 2013 @ 8:25

    How to get the required columns from a table instead of all columns.
    like below,

    @Query("select emp_id,emp_name from EMPLOYEE where emp_sal = :sal")
    public ? find(@Param("sal") int sal);

    I just want to query only the selected columns instead of all the data.Because if I take all the data ,it takes me some more time to fetch the whole data and the getting the required column data.
    can u help me to query like the above

    thanks in advance,
    varun

    • Petri Sep 5, 2013 @ 23:41

      In this scenario, you can either:

      I hope that this answered to your question.

      • varun Sep 24, 2013 @ 17:23

        I tried to apply the sample code snippet mentioned in the above link for one of the requirement.
        When i try to run the code am getting the response data as List instead of List.

        and when we try to iterate the list of objects returned from the method we getting the follwoing ClassCastException shown below.

        java.lang.ClassCastException: [Ljava.lang.Object; cannot be cast to net.petrikainulainen.spring.datajpa.dao.CompanyVO
        at net.petrikainulainen.spring.datajpa.todo.repository.
        ITTodoRepositoryTest.companyInfoTest3(ITTodoRepositoryTest.java:117)

        Please let us know how we could get the List ?

        Thanks And Regards.
        Varun

        Note: I removed unnecessary information from the stacktrace because it broke the layout - Petri

        • Petri Sep 24, 2013 @ 17:36

          Which sample code are you talking about?

          Also, it would be helpful to see the source code of the test and the source code of the repository. It is impossible to figure out the exact cause of the ClassCastException without seeing the code.

          • varun Sep 25, 2013 @ 17:08

            Code snippet

            We tried to modify and execute the given example from the below link
            http://www.petrikainulainen.net/programming/spring-framework/spring-data-jpa-tutorial-integration-testing/

            We have added two entities defined as following
            1. CompanyInfo
            2. CompanyTypeMaster

            @Entity
            public class CompanyInfo {
            @Id
            @GeneratedValue
            private long companyId;
            private String companyName;
            @OneToOne
            @JoinColumn(name = "companyType")
            private CompanyTypeMaster companyTypeMaster;

            public long getCompanyId() {
            return companyId;
            }
            public void setCompanyId(long companyId) {
            this.companyId = companyId;
            }
            public String getCompanyName() {
            return companyName;
            }
            public void setCompanyName(String companyName) {
            this.companyName = companyName;
            }
            public CompanyTypeMaster getCompanyTypeMaster() {
            return companyTypeMaster;
            }
            public void setCompanyTypeMaster(CompanyTypeMaster companyTypeMaster) {
            this.companyTypeMaster = companyTypeMaster;
            }
            }
            ******************************************
            @Entity
            public class CompanyTypeMaster {
            @Id
            @GeneratedValue
            private long typeId;
            private String typeName;

            public long getTypeId() {
            return typeId;
            }
            public void setTypeId(long typeId) {
            this.typeId = typeId;
            }
            public String getTypeName() {
            return typeName;
            }
            public void setTypeName(String typeName) {
            this.typeName = typeName;
            }
            }
            ******************************************

            Corresponding repositories to the above entities

            public interface CompanyInfoRepository extends JpaRepository{
            @Query("SELECT c.companyId,c.companyName,c.companyTypeMaster.typeId FROM CompanyInfo c where c.companyName=:companyName")
            List<CompanyVO> findByCompanyName(@Param("companyName") String companyName);
            }

            public interface CompanyTypeMasterRepository extends JpaRepository {

            }

            ******************************************

            Correspong DTO for the CompanyInfoRepository

            public class CompanyVO {
            private long companyId;
            private String companyName;
            private long typeId;

            public long getCompanyId() {
            return companyId;
            }
            public void setCompanyId(long companyId) {
            this.companyId = companyId;
            }
            public String getCompanyName() {
            return companyName;
            }
            public void setCompanyName(String companyName) {
            this.companyName = companyName;
            }
            public long getTypeId() {
            return typeId;
            }
            public void setTypeId(long typeId) {
            this.typeId = typeId;
            }
            }

            ******************************************

            The below code is added to the ITTodoRepositoryTest class in the above link code
            @test
            public void companyInfoTest3() {
            List<CompanyVO> companyVOs = companyInfoRepository.findByCompanyName("companyName");
            for (CompanyVO companyVO : companyVOs) {
            System.out.println(companyVO.getCompanyId());
            System.out.println(companyVO.getCompanyName());
            System.out.println(companyVO.getTypeId());
            }
            }
            .
            When we try to retrive the List from the CompanyInfoRepository we are getting response values as List<CompanyInfo>
            instead of List<CompanyVO>

            Note: I added the generics in place because Wordpress "eats" text inside brackets. If they are not correct, please let me know. - Petri

          • Petri Sep 25, 2013 @ 20:13

            You could try this approach:

            First, you have to add a constructor to the CompanyVO class. The source code of the constructor looks as follows:

            
            public CompanyVO(long companyId, String companyName, long typeId) {
            	this.companyId = companyId;
            	this.companyName = companyName;
            	this.typeId = typeId;
            }
            
            

            Second, modify your query to look like this (let's assume that the CompanyVO class is found from package foo.bar):

            @Query(“SELECT new foo.bar.CompanyVO(c.companyId, c.companyName, c.companyTypeMaster.typeId) FROM CompanyInfo c where c.companyName=:companyName”)

            I have not tried this out but it should do the trick. See this thread for more details (not much though) about this.

  • varun Sep 10, 2013 @ 12:27

    Thank you Petri , It's working

    Can we do the Aggregate functions using JPA ?

    I have one scenario in which I am maintaing the viewed profiles for a user, I will add the data every time any user visits a profile. Now, I need to display the- count of that hits - for a profile across the system.(going to take top 10 records out of this)

    select top 10 count(profileId), profileId from userviewd groupby profileId order by count(profileId) desc

  • Patrick Nov 9, 2013 @ 13:07

    Hi Petri,

    is there another possibility to use custom return types (non @Entity) objects with the JPQL method than using the "SELECT new package.CustomType(args) ..." syntax?

    Your proposed method to use a standard Java bean with matching properties as return type (comment #8) doesn't work for me without the "SELECT new" syntax.

    Regards,
    Patrick

    • Petri Nov 9, 2013 @ 13:57

      Hi Patrick,

      You are right that the method I proposed at comment #8 doesn't work anymore. I assume that the implementation of Spring Data JPA was changed in some way which broke that. Anyway, as far as I know, the only way to return non entity objects (DTOs) with Spring Data JPA is to use the new foo.bar.DTO syntax.

      However, if you want to do the mapping manually, you could try returning a List of Object arrays from your query method. I am not 100% sure that this is possible but I remember seeing an example about this somewhere (of course I cannot find that example right now).

      If you are willing to add a custom method to your repository (and you use Hibernate), you can use the AliasToBeanResultTransformer class to transform your query results into non entity objects.

  • Mahan Shatry Nov 19, 2013 @ 11:08

    Hi Petri
    I have a very complex query for which I have written the native query. This has multiple joins.
    Part of the query is: MY_QUERY
    SELECT
    `a`.`school_id` AS `school_id`,
    `a`.`school_name` AS `school_name`,
    `a`.`school_type_code` AS `school_type_code`,
    `a`.`assignee` AS `assignee`,
    `a`.`parent_id` AS `parent_id`,
    ...................(very big query... for 15 values)

    My Query code:
    Query query = getEntityManager().createNativeQuery(School.MY_QUERY);
    List qryList = query.getResultList();
    for(int i=0; i< qryList.size(); i++){
    Object[] obj = qryList.get(i);
    .....
    }
    My question:
    " Object[] obj " Does not have all the values. If any column data in the above query is NULL then object[] does not contain this values.
    How do I get all 15 values even if it contains NULL ? If lot of values are null I cannot map to my object correctly.

    Thanks in advance,

    Regards
    Shastry

    • Petri Nov 19, 2013 @ 21:14

      This was a tricky question. I tried both native and JPQL queries and if the value of a column was null, it was not included in the Object array. I tried to search answers from Google but didn't find anything interesting. Then I realized that you can do this by using Hibernate.

      Let's first create a DTO class for our query results. Its source code looks as follows:

      
      public class TodoDTO {
      
          private String title;
      
          private String description;
      
          public TodoDTO() {
      
          }
      
          public String getTitle() {
              return title;
          }
      
          public String getDescription() {
              return description;
          }
      
          public void setTitle(String title) {
              this.title = title;
          }
      
          public void setDescription(String description) {
              this.description = description;
          }
      }
      
      

      If you want to use native query, you can get a list of TodoDTO objects by using the following the code:

      
      EntityManager em = ... //Get entity manager
      Session session = em.unwrap(Session.class);
      SQLQuery query = session
      	.createSQLQuery("SELECT title AS title, description AS description from todos");
      List results = query
      	.addScalar("title")
      	.addScalar("description")
      	.setResultTransformer(Transformers.aliasToBean(TodoDTO.class))
      	.list();
      session.close();
      
      

      I had to use the addScalar() method because HSQLDB returns column names (and aliases) as uppercase. You might not need to call it.

      I hope that this answered to your question.

  • Dominik Werner Nov 25, 2013 @ 11:56

    Hi Petri,

    first of all, I really liked your tutorial series as a starting point for jpa and spring data jpa.
    So perhaps you want to work something in here we here just found out (and found extremely useful): Directly import jpa-results into a DTO instead of the entity.

    You can see an example in the accepted answer here: http://stackoverflow.com/questions/20120619/optimize-spring-data-jpa-queries - at least for our usecase it had a good impact at the performance of the query execution, because in our OR-Model, there was something like an Order, which had several Article-Objects, and some more "full objects" which jpa needed to resolve. Now we just "pull" our desired columns and add them directly to the dto. Even paging works as expected. Only "not-so-desirable" thing about this is, you have to use the fully-qualified object name (e.g. com.mypacket.dtos.myDto instead of myDto only).

    I think it's worth to mention here :)

    • Petri Nov 27, 2013 @ 19:16

      Hi Dominic,

      Thanks for sharing this!

      You make a good point. Querying DTOs is a lot faster than querying entities. Also, if this information is read-only, querying DTOs is a no-brainer.

      However, I don't like the way this is handled in Spring Data JPA because I would have to create constructors which might have a lot of constructor arguments. That is why I have been considering to bypass the JPA provider and use something like JOOQ.

      Of course, if you want to use Spring Data JPA, this is the best way to query DTOs.

  • Djordje Mar 26, 2014 @ 15:58

    Hi Petri,
    great example, helped me a lot. I have a one question. If I have a complex object as a field in my entity, is it posible to just define interface method like: findByManager(User manager); or I need to make some custom impl. I tried this but all I got is NullPointerException.
    My main entity is QDO and have User manager; as a field; My QDORepository extends JpaRepository. Thanks in advance!

    • Petri Mar 27, 2014 @ 18:18

      Yes, it should be possible to do this as long as the method name is constructed by using the correct naming convention (your method name follows it).

      How do you get a reference to the User object? Do you get it from the database inside the transaction where you call the findByManager(User manager) method?

  • Javid Aug 2, 2014 @ 16:48

    Java - 1.7
    spring.framework.version - 4.0.0.RELEASE
    spring-data- 1.7.0.M1
    spring-data-commons 1.9.0.BUILD-SNAPSHOT

    When I add a public List findByLastName(String lastName); to the PersonRepository which is extended from JpaRepository ,a run time error is thrown as below

    org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'PersonRepository': Invocation of init method failed; nested exception is java.lang.IllegalStateException: You have defined query method in the repository but you don't have any query lookup strategy defined. The infrastructure apparently does not support query methods!

    I was following steps in the tutorial-three-custom-queries-with-query-methods ,please help me to solve this

    • Petri Aug 3, 2014 @ 10:19

      That is an interesting error because the reference manual of Spring Data JPA states that you have to configure the used query lookup strategy only if you want to override the default configuration (CREATE_IF_NOT_FOUND).

      Have you tried to downgrading Spring Data JPA to version 1.6.2? If you don't want to do this, you could try to configure the query lookup strategy by following the instructions given in the Spring Data JPA reference manual.

      • Javid Aug 3, 2014 @ 10:36

        I had tried by configuring the query lookup strategy as well

        I will try by degrading the JPA version and let you know.
        Thanks for the reply

        • Javid Aug 3, 2014 @ 11:35

          Yes!!

          It works fine with the latest stable release of spring JPA (1.6.2.RELEASE) without any other change

          org.springframework.data
          spring-data-jpa
          <!-- 1.7.0.M1 Before-->
          1.6.2.RELEASE

          • Petri Aug 3, 2014 @ 17:05

            Great! I am happy to hear that you were able to solve your problem.

  • ew Aug 28, 2014 @ 20:45

    hi,

    Can I put a JPA query on a repository that selects some normal fields and some fields that are aggregates e.g. sum() , and if so what should it return ? An object[] ? or can I map it to some custom class some way?

    I know I can do this with a native query (using sqlresultstmapping) but if I go native I cant use Pageable, which is why I want to use a JPA @Query

  • Silur Feb 7, 2015 @ 20:49

    When i add a custom query to my repository like this:
    @Query("select u from User u where u.Email = ?1")
    User findByEmail(String emailAddress);
    I get java.lang.ClassNotFoundException: org.springframework.validation.beanvalidation.OptionalValidatorFactoryBean

    When I delete the @Query annotation it's good again

    • Petri Feb 7, 2015 @ 21:44

      Which Spring version are you using? If you are not using Spring Framework 4.0.X (or newer), you need to update your Spring version.

  • Varun Feb 11, 2015 @ 16:11

    Hi Petri,
    Can we apply the same DTO approach for the Spring Data MongoDB
    For SQL ::
    @Query("SELECT p.lastName,p.creationTime FROM Person p where p.lastName=:lastName")
    public List findByLastName(@Param("lastName") String lastName);
    For MongoDB ::
    @Document
    public class Country extends CommonEntity {
    private String name;
    private String description;
    private boolean active;
    }
    @Query(fields = "{ 'id' : 1, 'name' : 1}")
    List getCountryIdNames();
    here the query is different.. So can we have approach to implement DTO for MongoDB query also.
    We can limit and get the data in Country object only.. but I want the required fields in different object.
    Is there any possibility...?
    Thanks in advance

    • Petri Feb 11, 2015 @ 19:35

      Hi Varun,

      Unfortunately I don't know if you can query DTOs by using Spring Data MongoDB. I think that your best chance of getting an answer to this question is to create a new Stack Overflow question.

  • Clement Jun 19, 2015 @ 19:49

    Hi Petri,

    First of all thanks for answering all the questions.

    I have some other scenario like , i want to use subquery in the @query
    and i am getting error like "Illegal argument exception"
    Please can you provide sample code for sub query

    
    select 
    'AI' as carrierCode,
    (select count(*) from a le,b cc,c c
    where
    le.classoftravel_id=cc.id
    and le.bpoperatingcarrier_id=c.id
    and le.bpoperatingcarrier_id=1
    and le.helpax_id=4
    and cc.cabin_class_code='FCL') as paxFCL
    
    
    • Clement Jun 19, 2015 @ 19:54

      The above mentioned query is my sample query that i am trying to execute!!
      Please do reply , am struck to do the same

      • Petri Jun 20, 2015 @ 13:51

        Could you add your query method definition and the stack trace here? Also, do you want to return an entity and the result of count query? If so, you have to either split that query into two separate database queries or return a DTO.

        • Clement Jun 21, 2015 @ 12:04

          Hi Petri

          Thanks for the quick reply

          I will provide the stacktrace in few mins.
          I have another doubt. Can you please give some solution for that.

          I want to use native query and please find the sample query below

          
          @Query(value="select a,b FROM abc",nativeQuery=true)
          List findABC();
          
          

          In the above statement, I want to bind the result set to ABC pojo , i couldnt able to do the same for native query.

          Can you please give me some solution

          • Petri Jun 21, 2015 @ 13:18

            As far as I know, native queries can return either entities, basic types, and collections that contain either entities or basic types (you might be able to return object array as well). If your pojo is not an entity, you cannot use native queries (also, if it is an entity, you should use select *).

            However, you can specify the invoked database query by using JPQL and return a DTO aka pojo (this StackOverflow answer provides more details about this). The only "downside" of this solution is that you need add a constructor that takes the field values of the created object as constructor arguments.

          • Clement Jun 21, 2015 @ 14:16

            Hi Petri

            Thanks a lot for yoyur information

            
            @Query(value="select * FROM abc",nativeQuery=true)
            List abc();
            
            

            I tried to retrieve this way. And i am getting the result as a generic object.
            After i got the generic object. I have to iterate the list and pasre each field and set it to variable in pojo like the below snippet.

            tempObj.stream().forEach((record) -> {
            			finalObj.setA((String) record[1]);
            			finalObj.setB((String) record[7]);
            		})
            


            My doubt is , is this fine or do i have to follow any better idea??
            Please help me

          • Petri Jun 21, 2015 @ 17:55

            If you return an object array, you should select only the columns you need (in your case a and b).

            I only mentioned SELECT * because that is required if you want to return entities. Since you don't want to do that, selecting only the required columns is a better choice because it is faster than selecting all columns.

          • clement Jun 21, 2015 @ 18:20

            Hi Petri

            Selecting only the specific columns is fine for me. But the thing is i couldn't bind the result to the pojo.. Thats why am using in this way of generic object to bind.

            
            @Query(value="select a,b,cFROM abc",nativeQuery=true)
            List abc();
            
            

            In the above snippet, if am trying to get a,b and c. Can you please tell me how my return type should be and do i need to add anything in pojo to bind the result set to pojo correctly other than contructor way.

            can you please give me some sample snippet.

            Thanks Petri

          • Petri Jun 21, 2015 @ 19:47

            This StackOverflow answer explains how you can return DTOs (aka pojos). Unfortunately the only way to bind query results into DTO objects is to pass the query results as constructor arguments. If you still want to return DTOs, you should follow these steps:

            First, you need to create the returned DTO. Let's use a simple DTO that has an id and a title:

            
            public class TodoDTO {
            
            	private final Long id;
            	private final String title;
            	
            	public TodoDTO(Long id, String title) {
            		this.id = id;
            		this.title = title;
            	}
            }
            
            

            Second, you have to create the query method that returns a list of TodoDTO objects. You can do this by adding the following query method to your repository interface:

            
            @Query("select new foo.bar.TodoDTO(t.id, t.title) from Todo t")
            List<TodoDTO> findAllTodos();
            
            

            Remember that you have to use the canonical class name when you select a new TodoDTO object. If you don't do this, Spring Data JPA will throw an exception because it cannot locate the DTO class.

            Third, use your new query method.

          • Petri Jun 21, 2015 @ 20:05

            If you don't want to use the previous method because the constructor can become a bit too messy, you could try returning an object array:

            
            @Query(value="select a,b FROM abc",nativeQuery=true)
            List<Object[]> findABC();
            
            

            This way you wouldn't have query extra information from the database, but you would have to transform the object arrays into "meaningful" objects.

          • Clement Jun 27, 2015 @ 8:53

            Hi Petri

            Thanks a lot for your information.
            Its really a great tip for me!!!

          • Petri Jun 27, 2015 @ 11:11

            You are welcome!

  • Clement Jun 27, 2015 @ 8:56

    Hi petri

    Another one question is there. Please provide some sample if you have any

    i want to execute the sub query in @query like

    
    @query(select * from ((select count(*) from a)  as aCount,(select count(*) from b) as bCount ))
    
    

    Am getting the error like, expecting from but not found..
    Please can you tell me!! is there any other way to execute the sub-query

    Thank you

    • Clement Jun 28, 2015 @ 9:27

      Hi Petri

      Can you please give any solution for the above thread if you have any

      Thanks!!

      • Petri Jun 28, 2015 @ 10:13

        Hi,

        Are you trying to create a query that returns two integers (count a and count b), or are you trying to create a query that returns an entity and two extra integers? Also, it would be helpful to see:

        • The full query method declaration (including the full db query).
        • The stack trace that contains the error message.

        If you can add this information here, I can try to figure out what is wrong.