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.

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:

 1public final class TodoSearchResultDTO {
 2
 3    private Long id;
 4
 5    private String title;
 6
 7    public TodoSearchResultDTO() {}
 8
 9    public Long getId() {
10        return id;
11    }
12
13    public String getTitle() {
14        return title;
15    }
16
17    public void setId(Long id) {
18        this.id = id;
19    }
20
21    public void setTitle(String title) {
22        this.title = title;
23    }
24}

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:

1import java.util.List;
2
3interface CustomTodoRepository {
4
5    List<TodoSearchResultDTO> findBySearchTerm(String searchTerm);
6}

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:

 1import org.springframework.context.annotation.Bean;
 2import org.springframework.context.annotation.Configuration;
 3import org.springframework.data.jpa.repository.config.EnableJpaAuditing;
 4import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
 5import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
 6import org.springframework.transaction.annotation.EnableTransactionManagement;
 7
 8@Configuration
 9@EnableJpaAuditing(dateTimeProviderRef = "dateTimeProvider")
10@EnableJpaRepositories(basePackages = {
11        "net.petrikainulainen.springdata.jpa.todo"
12})
13@EnableTransactionManagement
14class PersistenceContext {
15
16    @Bean
17    NamedParameterJdbcTemplate jdbcTemplate(DataSource dataSource) {
18        return new NamedParameterJdbcTemplate(dataSource);
19    }
20
21	//Other beans are omitted for the sake of clarity
22}

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:
6. 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.
7. 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.
8. 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”.
9. Return the query results.

The source code of the TodoRepositoryImpl class looks follows:

 1import org.springframework.beans.factory.annotation.Autowired;
 2import org.springframework.jdbc.core.BeanPropertyRowMapper;
 3import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
 4import org.springframework.stereotype.Repository;
 5import org.springframework.transaction.annotation.Transactional;
 6
 7import java.util.HashMap;
 8import java.util.List;
 9import java.util.Map;
10
11@Repository
12final class TodoRepositoryImpl implements CustomTodoRepository {
13
14    private static final String SEARCH_TODO_ENTRIES = "SELECT id, title FROM todos t WHERE " +
15            "LOWER(t.title) LIKE LOWER(CONCAT('%',:searchTerm, '%')) OR " +
16            "LOWER(t.description) LIKE LOWER(CONCAT('%',:searchTerm, '%')) " +
17            "ORDER BY t.title ASC";
18
19    private final NamedParameterJdbcTemplate jdbcTemplate;
20
21    @Autowired
22    TodoRepositoryImpl(NamedParameterJdbcTemplate jdbcTemplate) {
23        this.jdbcTemplate = jdbcTemplate;
24    }
25
26    @Transactional(readOnly = true)
27    @Override
28    public List<TodoSearchResultDTO> findBySearchTerm(String searchTerm) {
29        Map<String, String> queryParams = new HashMap<>();
30        queryParams.put("searchTerm", searchTerm);
31
32        List<TodoSearchResultDTO> searchResults = jdbcTemplate.query(SEARCH_TODO_ENTRIES,
33                queryParams,
34                new BeanPropertyRowMapper<>(TodoSearchResultDTO.class)
35        );
36
37        return searchResults;
38    }
39}

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:

 1import org.springframework.data.repository.Repository;
 2
 3import java.util.List;
 4import java.util.Optional;
 5
 6interface TodoRepository extends Repository<Todo, Long>, CustomTodoRepository {
 7
 8    void delete(Todo deleted);
 9
10    List<Todo> findAll();
11
12    Optional<Todo> findOne(Long id);
13
14    void flush();
15
16    Todo save(Todo persisted);
17}

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.