Spring Data JPA Tutorial: Adding Custom Methods to a Single Repository

Although Spring Data JPA provides an excellent support for implementing CRUD operations and creating database queries, sometimes we need to do things that are not supported by it. For example, Spring Data JPA doesn't provide a built-in support for querying DTOs by using SQL.

Luckily for us, we can "extend" our Spring Data JPA repositories by adding custom methods into them.

This blog post describes how we can add custom methods into a single Spring Data JPA repository. During this blog post we will implement a search function that fulfills the following requirements:

  • It must ignore case and return the id and title of todo entries whose title or description contains the given search term.
  • It must sort the query results in ascending order by using the value of the title column.
  • The search function must use an SQL query that is invoked by using JDBC.
  • The repository method must return a list of DTO objects.

Let's get started.

Additional Reading:

If you are not familiar with Spring Data JPA, you should read the following blog posts before you continue reading this blog post:

Creating a Custom Repository Interface

When we want to add custom methods into a Spring Data JPA repository, the first thing that we have to do is to create an interface which declares the custom methods.

However, because we want to create a method that returns a list of custom DTO objects, we have to create the returned DTO class before we can create the custom repository interface. Because the search result page of our example application uses only the values of the id and title fields, we have to create a DTO class that has two fields: id and title.

The source code of the TodoSearchResultDTO class looks as follows:

public final class TodoSearchResultDTO {

    private Long id;

    private String title;

    public TodoSearchResultDTO() {}

    public Long getId() {
        return id;
    }

    public String getTitle() {
        return title;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public void setTitle(String title) {
        this.title = title;
    }
}

We can now create the custom repository interface by following these steps:

  1. Create a CustomTodoRepository interface.
  2. add findbySearchTerm() method to the created interface. This method takes the search term as a method parameter and returns a list of TodoSearchResultDTO objects.

The source code of the CustomTodoRepository interface looks as follows:

import java.util.List;

interface CustomTodoRepository {

    List<TodoSearchResultDTO> findBySearchTerm(String searchTerm);
}

After we have created our custom repository interface, we have to naturally implement it. Let's find out how we can create a repository class that fetches the search results by using JDBC.

Implementing the Custom Repository Interface

Because we want to create an SQL query that uses named parameters and we want to invoke that query by using JDBC, we have to configure the NamedParameterJdbcTemplate bean before we can implement our custom repository interface. We can configure this bean by making the following changes to the application context configuration class which configures the persistence layer of our example application:

  1. Add a jdbcTemplate() method to the configuration class and annotate it with the @Bean annotation. This method takes a DataSource object as a method parameter and returns a NamedParameterJdbcTemplate object.
  2. Implement the method by creating a new NamedParameterJdbcTemplate object and returning the created object.

The relevant part of the PersistenceContext class looks as follows:

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.config.EnableJpaAuditing;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@Configuration
@EnableJpaAuditing(dateTimeProviderRef = "dateTimeProvider")
@EnableJpaRepositories(basePackages = {
        "net.petrikainulainen.springdata.jpa.todo"
})
@EnableTransactionManagement
class PersistenceContext {

    @Bean
    NamedParameterJdbcTemplate jdbcTemplate(DataSource dataSource) {
        return new NamedParameterJdbcTemplate(dataSource);
    }
	
	//Other beans are omitted for the sake of clarity
}

We can now implement our custom repository interface by following these steps:

  1. Create a custom repository class that implements the CustomTodoRepository interface. By default, the name of a custom repository class must follow this syntax: [The name of the repository interface]Impl. Because the name of our repository interface is TodoRepository, the name of our custom repository class must be TodoRepositoryImpl.
  2. Annotate the created class with the @Repository annotation.
  3. Create an SQL query that returns the id and title of todo entries, whose title or description contains the given search term, and sorts the query results in ascending order by using the value of the title column. Set this SQL query as the value of a static final field.
  4. Add a final NamedParameterJdbcTemplate field to repository class and inject the value of this field by using constructor injection.
  5. Implement the findBySearchTerm() method by following these steps:
    1. Annotate the method with the @Transactional annotation and mark the transaction as read-only. This ensures that our SQL query is always invoked inside a read-only transaction.
    2. Create a Map object, which contains the query parameters of our SQL query, and put the search term given as a method parameter into the created map.
    3. Invoke the SQL query and transform the query results into a list of TodoSearchResultDTO objects by using the BeanPropertyRowMapper class. We can use this method as long as the aliases of the selected columns matches with the property names of the "target class".
    4. Return the query results.

The source code of the TodoRepositoryImpl class looks follows:

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository
final class TodoRepositoryImpl implements CustomTodoRepository {

    private static final String SEARCH_TODO_ENTRIES = "SELECT id, title FROM todos t WHERE " +
            "LOWER(t.title) LIKE LOWER(CONCAT('%',:searchTerm, '%')) OR " +
            "LOWER(t.description) LIKE LOWER(CONCAT('%',:searchTerm, '%')) " +
            "ORDER BY t.title ASC";

    private final NamedParameterJdbcTemplate jdbcTemplate;

    @Autowired
    TodoRepositoryImpl(NamedParameterJdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Transactional(readOnly = true)
    @Override
    public List<TodoSearchResultDTO> findBySearchTerm(String searchTerm) {
        Map<String, String> queryParams = new HashMap<>();
        queryParams.put("searchTerm", searchTerm);

        List<TodoSearchResultDTO> searchResults = jdbcTemplate.query(SEARCH_TODO_ENTRIES,
                queryParams,
                new BeanPropertyRowMapper<>(TodoSearchResultDTO.class)
        );

        return searchResults;
    }
}
If you want to change the postfix that is used when Spring Data JPA looks for custom repository implementations, you have to use one of these two methods:

Additional Reading:

Let's move on and add our custom method into our repository interface (TodoRepository).

Adding Custom Methods Into a Repository Interface

We can add custom methods into a repository interface by extending the custom repository interface that declares the custom methods. In other words, we have to modify the TodoRepository interface to extend the CustomTodoRepository interface.

After we have modified the TodoRepository interface, its source code looks as follows:

import org.springframework.data.repository.Repository;

import java.util.List;
import java.util.Optional;

interface TodoRepository extends Repository<Todo, Long>, CustomTodoRepository {

    void delete(Todo deleted);

    List<Todo> findAll();

    Optional<Todo> findOne(Long id);

    void flush();

    Todo save(Todo persisted);
}

Let's move on and summarize what we learned from this blog post.

Summary

This blog post has taught us three things:

  • If we want to query DTOs by using SQL, we have to add custom method into our Spring Data JPA repository interface.
  • We can map our query results into objects by using the BeanPropertyRowMapper class.
  • We can change the postfix that is used to identify our custom repository implementations.

The next part of my Spring Data JPA tutorial describes how we can add custom methods into all Spring Data JPA repositories.

P.S. You can get the example application of this blog post from Github.

If you want to learn how to use Spring Data JPA, you should read my Spring Data JPA tutorial.
29 comments… add one
  • Seth G Brown Jul 25, 2012 @ 18:35

    Hi,

    Excellent tutorials - You have a gift for explaining things. Thank you for taking the time!
    I am new to Spring 3.x and JPA and have been struggling to understand it as tutorials for the right versions seem hard to come by. This series of 5 tutorials are easily the best ones I have read so far.

    That said, while the first 3 tutorials made perfect sense to me, I had difficulty with the last three as I did not quite understand how I would use them despite you examples. Perhaps a re-read will help :)

    I could not see any JOIN queries (INNERJOIN, OUTERJOIN) in your tutorials. I would appreciate another tutorial with examples with JOIN, perhaps an address table and/or an employment table for the Person will be helpful to explain how to join two or more tables.

    Also would appreciate a tutorial in which the java code calls a stored proc to do the query and get the results.

    Once again, thank you for your excellent tutorials. Finally things are beginning to make sense :)

  • Petri Aug 15, 2012 @ 10:30

    A fast comment concerning the naming strategy that should be used to select a name for the custom repository implementation:

    This behavior has changed in the newer versions of Spring Data JPA (newer than 1.0.2.RELEASE that is used in the example application), and you must create the name of the custom repository implementation by adding the configured post fix to the simple name of the actual repository interface. In this case, the name of the custom implementation should be changed from PaginatingPersonRepositoryImpl to PersonRepositoryImpl.

  • kisst Nov 23, 2012 @ 21:29

    Hi,
    Nice article, actually nice series of articles about spring-data-jpa, however I didn't have the time so far to run throught all of them.
    But your last comment about new naming strategy saved my day.Thank you!

    • Petri Nov 24, 2012 @ 11:53

      Thanks for your comment. It is good to hear that I could help you out!

  • Petri Nov 25, 2012 @ 21:55

    One of my readers (Thanks Eric!) told me about an another way to fulfill the requirements described in this blog entry. Check it out: Spring Data JPA with QueryDSL: Repositories made easy.

  • Mohit Dec 12, 2012 @ 22:54

    Very Nice article
    We have dynamic search requirement on specific entity from UI.
    How can we add Specification dynamically?

    Presently we are directly using Criteria API like

    
    @SuppressWarnings({ "rawtypes", "unchecked" })
    public static TypedQuery createCriteriaQueryForEntity(EntityManager em, 
    Class entityClazz, List criteriaFilters)
    {
    	CriteriaBuilder cb = em.getCriteriaBuilder();
    	CriteriaQuery createQuery = cb.createQuery(entityClazz);
    	Root root = createQuery.from(entityClazz);
    	CriteriaQuery select = createQuery.select(root);
    	Predicate[] predicates = buildPredicates(criteriaFilters, root, cb);
    	TypedQuery typedQuery = em.createQuery(select.where(cb.and(predicates)));
    	return typedQuery;
    }
    
    

    Is there any way to get it done with Specification?

    • Petri Dec 12, 2012 @ 23:10

      Hi Mohit,

      I have written another blog entry that describes the usage of specifications with Spring Data JPA. However, here is skeleton of a specification builder class which you can use to build your query by using specifications:

      
      public class Specifications {
      
      	public static Specification search(final List filters) {
      		return new Specification() {
      			@Override
      			public Predicate toPredicate(Root root, 
      						CriteriaQuery query, 
      						CriteriaBuilder cb) {
      				Predicate[] p = buildPredicates(filters, root, cb);
      				return cb.and(p);
      			}
                 
      			private Predicate[] buildPredicates(List filters, 
      							Root root, 
      							CriteriaBuilder cb) {
      				//Build your predicates here.
      			}
      		};
      	}
      }
      
      

      I assume that the type of your entity class is EntityClass. I hope that this answered to your question.

  • Pablo Oct 3, 2013 @ 4:44

    Hi Petri, I have a problem to connect my application to multiple datasources, I saw It's possible to use @@PersistenceContext(unitName=, but I don't know how to implement it. Currently I have configuration using .java and not .xml files, other thing is that I use DTO's and jparepository.

    Thanks a lot for helping me.
    Pablo

  • Vij Jan 29, 2014 @ 6:16

    Hello Petri ,
    As usual, reading your blog is always a pleasure and informative.
    I am trying to solve a problem and hoping your get your insights.
    I am trying to write a generic repository that loads entities that are subclasses of a base type.
    Now, I have a base repository implementation working and am able to manipulate specific entities For E-g Car. I am attempting to write a generic repo that is able to load all reference data like "CarType"
    So I am adding custom methods to that repo and extending from the base repo.
    http://stackoverflow.com/questions/19765684/spring-jpa-adding-custom-functionality-to-all-repositories-and-at-the-same-time?rq=1
    I am running into an error :
    Caused by: java.lang.IllegalArgumentException: Not an managed type: class xxx.BaseEntity
    at org.hibernate.ejb.metamodel.MetamodelImpl.managedType(MetamodelImpl.java:200) ~[hibernate-entitymanager-4.2.7.Final.jar:4.2.7.Final]
    at org.springframework.data.jpa.repository.support.JpaMetamodelEntityInformation.(JpaMetamodelEntityInformation.java:68) ~[spring-data-jpa-1.4.3.RELEASE.jar:na]
    at org.springframework.data.jpa.repository.support.JpaEntityInformationSupport.getMetadata(JpaEntityInformationSupport.java:65) ~[spring-data-jpa-1.4.3.RELEASE.jar:na]
    at org.springframework.data.jpa.repository.support.JpaRepositoryFactory.getEntityInformation(JpaRepositoryFactory.java:146) ~[spring-data-jpa-1.4.3.RELEASE.jar:na]

    I saw the below post but could not figure it out :

    http://stackoverflow.com/questions/14286841/can-i-use-a-generic-repository-for-all-children-of-a-mappedsuperclass-with-sprin

    I have created a SO question with complete implementation details(which might be a bit long to post here).

    http://stackoverflow.com/questions/21415551/generic-spring-data-jpa-repository-implementation-to-load-data-by-class-type

    If possible, kindly have a look and advice. It will be much appreciated.

    Thanks

    • Petri Feb 1, 2014 @ 21:45

      Hi,

      I am sorry that it took me a few days to answer to this question.

      The root cause of this problem is that the BaseEntity class isn't mapped as an entity like suggested in the answer of your StackOverflow question.

  • Lukas Mar 17, 2014 @ 1:51

    Thank you so much! Great tutorial!

    • Petri Mar 17, 2014 @ 15:38

      You are welcome! I am happy to hear that this blog post was useful to you.

  • Satish Dhiman May 25, 2014 @ 12:33

    Hi Petri,

    These are all examples with every explanation is excellence.
    And every examples are very helpful for us.
    Thanks

    • Petri May 25, 2014 @ 19:56

      Hi,

      you are welcome. I am happy to hear that this blog post was useful to you.

  • Shyamala Jun 5, 2014 @ 17:59

    Hi Petri,

    All ur examples are very easy to understand. Can you pl explain how to call stored procedures in spring data jpa?

    Thanks in advance

    Shyamala

    • Petri Jun 6, 2014 @ 20:28

      I think that you have two options: you can either create a native SQL query by using the @Query annotation, or add a custom method to your repository which calls the stored procedure by using JDBC. You can get more information about the second option by reading this StackOverflow question.

  • Weena May 25, 2016 @ 11:12

    Hi Petri,

    Thank you so much, your are a very good teacher. Your posts are excelent!!

    • Petri May 26, 2016 @ 17:24

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

  • teja Jun 3, 2016 @ 18:15

    Hi Petri,

    Thank you so much Your posts are excelent!! Can you pl explain how can write join query using spring data jpa

    Thanks

    • Petri Jun 4, 2016 @ 11:48

      Hi,

      This article describes how you can create joins with JPQL.

      • Teja Jun 6, 2016 @ 17:22

        Thank you so much petri

        That site was very helpful. I tried it.but I am getting the below error
        Caused by: org.hibernate.MappingException: broken column mapping for

        do you have any idea when this error will occur

        • Teja Jun 6, 2016 @ 17:42

          Please ignore that question. that was resolved

          • Petri Jun 7, 2016 @ 19:49

            Hi,

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

  • john Sep 16, 2016 @ 13:25

    Hi Petri,
    I am creating a project using spring boot. I am creating an aspect class. I want to run a query on @before aspect . would you please help me how to do that?
    I want to run query like "select * from Student where name = abc". How can i do that ?

  • erdem Apr 30, 2018 @ 19:23

    Hi,
    Firstly, thank you so much for the great articles.
    But i didnt understand why we need to create a NamedParameterJdbcTemplate beans.
    Can't we use "@Autowired private SessionFactory sf;" then "sf.getCurrentSession().createQuery" ?

    • Petri Apr 30, 2018 @ 19:39

      Hi,

      I use the NamedParameterJdbcTemplate here mainly because I like its API. If you don't want to use it, you can create your SQL queries by using the Hibernate SessionFactory, JPA EntityManager, or jOOQ.

Leave a Reply