Are you interested in automated testing? If so, become a member of the Java Testing Society!

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

Custom bikes

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.

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 more about Spring Data JPA, you should read all parts of my Spring Data JPA tutorial.

About the Author

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

About Petri Kainulainen →

42 comments… add one

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

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

      Reply
  • 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

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

      Reply
  • 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?

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

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

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

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

          • Great to hear that.

  • how to use this script “X” whith method when? How to location config?

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

      Reply
  • Thanks, i resolved.

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

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

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

    Reply
  • 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?

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

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

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

      Reply
  • 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

    Reply
    • In this scenario, you can either:

      I hope that this answered to your question.

      Reply
      • 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

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

          Reply
          • 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

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

  • 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

    Reply
  • 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

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

      Reply
  • 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

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

      Reply
  • 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 :)

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

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

    Reply
    • 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?

      Reply
  • 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

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

      Reply
      • 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

        Reply
        • 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

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

Leave a Comment