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.
If you are not familiar with Spring Data JPA, you should read the following blog posts before you continue reading this blog post:
- Spring Data JPA Tutorial: Introduction provides a quick introduction to Spring Data JPA and gives an overview of the Spring Data repository interfaces.
- Spring Data JPA Tutorial: Getting the Required Dependencies describes how you can get the required dependencies.
- Spring Data JPA Tutorial: Configuration describes how you can configure the persistence layer of a Spring application that uses Spring Data JPA.
- Spring Data JPA Tutorial: CRUD describes how you can create a Spring Data JPA repository that provides CRUD operations for an entity.
- Spring Data JPA Tutorial: Auditing, Part One describes how you can add the creation and modification time fields into your entities by using the auditing infrastructure of Spring Data JPA.
- Spring Data JPA Tutorial: Auditing, Part Two describes how you can add the information of the authenticated user, who created and/or updated an entity, into your entities by using the auditing infrastructure of Spring Data JPA.
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:
- Create a CustomTodoRepository interface.
- 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:
- 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.
- 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:
- 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.
- Annotate the created class with the @Repository annotation.
- 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.
- Add a final NamedParameterJdbcTemplate field to repository class and inject the value of this field by using constructor injection.
- Implement the findBySearchTerm() method by following these steps:
- 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.
- 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.
- 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".
- 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 are using Java configuration, you can configure the postfix by setting the value of the @EnableJpaRepository annotation's repositoryImplementationPostfix attribute.
- If you are using XML configuration, you can configure the postfix by setting value of the repository element's repository-impl-postfix attribute.
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.
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 :)
Hi Seth,
Thank you for the feedback and it is great to hear that I could help you out in some way. You mentioned that you wanted to learn more about using join queries. I talked a bit to my friend Google and here are some tutorials that might be relevant to you:
I hope that you find these links helpful. Remember that the underlying technology is still the same. Spring Data JPA simply offers a cleaner way to use it so that you don't have to do everything by yourself.
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
toPersonRepositoryImpl
.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!
Thanks for your comment. It is good to hear that I could help you out!
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.
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
Is there any way to get it done with Specification?
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:
I assume that the type of your entity class is EntityClass. I hope that this answered to your question.
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
Have you tried following the advice given in this StackOverflow question?
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
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.Thank you so much! Great tutorial!
You are welcome! I am happy to hear that this blog post was useful to you.
Hi Petri,
These are all examples with every explanation is excellence.
And every examples are very helpful for us.
Thanks
Hi,
you are welcome. I am happy to hear that this blog post was useful to you.
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
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.Hi Petri,
Thank you so much, your are a very good teacher. Your posts are excelent!!
Thank you for your kind words. I really appreciate them.
Hi Petri,
Thank you so much Your posts are excelent!! Can you pl explain how can write join query using spring data jpa
Thanks
Hi,
This article describes how you can create joins with JPQL.
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
Please ignore that question. that was resolved
Hi,
It is good to hear that you were able to solve your problem.
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 ?
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" ?
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 HibernateSessionFactory
, JPAEntityManager
, or jOOQ.