My Spring Data JPA tutorial has taught us how we can create both static and dynamic database queries with Spring Data JPA.
We have also implemented a search function that ignores case and returns todo entries whose title or description contains the given search term.
However, we haven't paid any attention to one very important thing:
We have no idea how we can sort the query results of our database queries.
This blog post fixes this problem. We will learn to sort the query results of our database queries and add sorting support into the search function of our example application.
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: Introduction to Query Methods describes how you can pass method parameters to your query methods and identifies the "legal" return values of Spring Data JPA query methods.
- Spring Data JPA Tutorial: Creating Database Queries From Method Names describes how you can create database queries from the method names of your query methods.
- Spring Data JPA Tutorial: Creating Database Queries With the @Query Annotation describes how you can create database queries by annotating your query methods with the @Query annotation.
- Spring Data JPA Tutorial: Creating Database Queries With Named Queries describes how you can create database queries by using named queries.
- Spring Data JPA Tutorial: Creating Database Queries With the JPA Criteria API describes how you can create dynamic queries by using the JPA Criteria API.
- Spring Data JPA Tutorial: Creating Database Queries With Querydsl describes how you can create dynamic database queries by using Querydsl.
Sorting Query Results With the Method Names of Our Query Methods
If we create our database queries by using the query generation from the method name strategy, we can sort the query results of our database queries by using the OrderBy keyword. We can use the OrderBy keyword by following these steps:
- Append the OrderBy keyword to the method name of our query method.
- Append the name of the property to the method name of our query method and transform its first letter into uppercase. If we want to order our query results by using the title of a todo entry, we have to append the string: Title to the method name of our query method.
- Describe the sort direction. If we want to sort the query results in ascending order, we have to append the keyword Asc to the method name of our query method. On the other hand, if we want to sort the query results in descending order, we have to append the keyword Desc to the method name of our query method.
- If we need to sort our query results by using multiple properties, we have to go back to the step 2.
Example 1:
We have created a query method that returns todo entries whose title is given as a method parameter. If we want to sort the query results of that query method in ascending order by using the value of the title field, we have to use the following code:
import org.springframework.data.repository.Repository; import java.util.List; interface TodoRepository extends Repository<Todo, Long> { List<Todo> findByTitleOrderByTitleAsc(String title); }
Example 2:
We have created a query method that returns todo entries whose title is given as a method parameter. If we want to sort the query results of that query method in ascending order by using the value of the title field and in descending order by using the value of the description field, we have to use the following code:
import org.springframework.data.repository.Repository; import java.util.List; interface TodoRepository extends Repository<Todo, Long> { List<Todo> findByTitleOrderByTitleAscDescriptionDesc(String title); }
Example 3:
The search function of our example application returns todo entries whose title or description contains the given search term. If we want to sort the search results in ascending order by using the value of the title field, we have to add the following query method into our repository interface:
import org.springframework.data.repository.Repository; import java.util.List; interface TodoRepository extends Repository<Todo, Long> { List<Todo> findByDescriptionContainsOrTitleContainsAllIgnoreCaseOrderByTitleAsc(String descriptionPart, String titlePart); }
Let's move on and find out how we can sort query results with query strings.
Sorting Query Results With Query Strings
If we create our database queries by using named queries or the @Query annotation, we can specify the sorting logic in our query strings.
The search function of our example application is case-insensitive. It returns todo entries whose title or description contains the given search term. The following examples demonstrate how we can sort our query results by modifying the existing JPQL and SQL queries:
Example 1:
If we want to modify an existing JPQL query to sort the query results in ascending order by using the value of the title field, we have to use the JPQL ORDER BY clause.
The modified JPQL query looks as follows:
SELECT t FROM Todo t WHERE LOWER(t.title) LIKE LOWER(CONCAT('%',:searchTerm, '%')) OR LOWER(t.description) LIKE LOWER(CONCAT('%',:searchTerm, '%')) ORDER BY t.title ASC
Example 2:
If we want to modify an existing SQL query to sort the query results in ascending order by using the value of the title field, we have to use the SQL ORDER BY clause.
SELECT * FROM todos t WHERE LOWER(t.title) LIKE LOWER(CONCAT('%',:searchTerm, '%')) OR LOWER(t.description) LIKE LOWER(CONCAT('%',:searchTerm, '%')) ORDER BY t.title ASC
Let's find out how we can sort query results by using the Sort class.
Sorting Query Results With the Sort Class
If our database queries are not named queries or native queries that use the @Query annotation, we can sort their query results by using the Sort class. It is essentially a specification class that describes the sorting options of our database queries.
We can sort our query results by following these steps:
- Obtain the Sort object that describes the sorting options of the invoked database query.
- Pass the Sort object forward to the correct repository method as a method parameter.
Let's find out how we can obtain the Sort object.
Obtaining the Sort Object
We can obtain the Sort object by using two different methods: we can specify the sort options manually or we can use Spring Data Web Support.
Let's start by specifying the sort options manually.
Specifying the Sort Options Manually
If we want to specify the sort options manually, the service class (or another component) that wants to sort the query results returned by a Spring Data JPA repository, must create the Sort object and pass it forward to the invoked repository method.
The source code of the RepositoryTodoSearchService class, which uses this method, looks as follows:
import org.springframework.beans.factory.annotation.Autowired; import org.springframework.data.domain.Sort; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Transactional; import java.util.List; @Service final class RepositoryTodoSearchService implements TodoSearchService { private final TodoRepository repository; @Autowired public RepositoryTodoSearchService(TodoRepository repository) { this.repository = repository; } @Transactional(readOnly = true) @Override public List<TodoDTO> findBySearchTerm(String searchTerm) { Sort sortSpec = orderBy(); //Obtain search results by invoking the preferred repository method. List<Todo> searchResults = ... return TodoMapper.mapEntitiesIntoDTOs(searchResults); } private Sort orderBy() { //Create a new Sort object here. } }
The following examples demonstrates how we can implement the private orderBy() method:
Example 1:
If we must sort the query results in ascending order by using the value of the title field, we have to create the Sort object by using the following code:
private Sort orderBy() { return new Sort(Sort.Direction.ASC, "title"); }
Example 2:
If we must sort the query results by in descending order by using the values of the title and description fields, we have to create the Sort object by using the following code:
private Sort orderBy() { return new Sort(Sort.Direction.DESC, "title", "description"); }
Example 3:
If we want to sort the query results in descending order by using the value of the description field and in ascending order by using the value of the title field, we have to create the Sort object by using the following code:
private Sort orderBy() { return new Sort(Sort.Direction.DESC, "description") .and(new Sort(Sort.Direction.ASC, "title")); }
Let's find out how we can obtain Sort objects by using Spring Data web support.
Using Spring Data Web Support
We can enable Spring Data web support by annotating our application context configuration class with the @EnableSpringDataWebSupport annotation. The relevant part of the PersistenceContext class, which configures the persistence layer of our example application, looks as follows:
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.data.web.config.EnableSpringDataWebSupport; import org.springframework.transaction.annotation.EnableTransactionManagement; @Configuration @EnableJpaAuditing(dateTimeProviderRef = "dateTimeProvider") @EnableJpaRepositories(basePackages = { "net.petrikainulainen.springdata.jpa.todo" }) @EnableTransactionManagement @EnableSpringDataWebSupport class PersistenceContext { }
This registers a new SortHandlerMethodArgumentResolver instance that can create Sort objects from request parameters or @SortDefault annotations. This means that we can specify the sorting logic by setting the value of the sort request parameter. The reference documentation of Spring Data JPA describes the content of the sort request parameter as follows:
Properties that should be sorted by in the format property,property(,ASC|DESC). Default sort direction is ascending. Use multiple sort parameters if you want to switch directions, e.g. ?sort=firstname&sort=lastname,asc.
After we have enabled Spring Data web support, we can inject Sort objects into controller handler methods. The source code of the TodoSearchController class, which utilizes Spring Data web support, looks as follows:
import org.springframework.beans.factory.annotation.Autowired; import org.springframework.data.domain.Sort; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RequestMethod; import org.springframework.web.bind.annotation.RequestParam; import org.springframework.web.bind.annotation.RestController; import java.util.List; @RestController final class TodoSearchController { private final TodoSearchService searchService; @Autowired public TodoSearchController(TodoSearchService searchService) { this.searchService = searchService; } @RequestMapping(value = "/api/todo/search", method = RequestMethod.GET) public List<TodoDTO> findBySearchTerm(@RequestParam("searchTerm") String searchTerm, Sort sort) { return searchService.findBySearchTerm(searchTerm, sort); } }
The TodoSearchController gets the information of the returned todo entries from the TodoSearchService object. The RepositoryTodoSearchService class implements the TodoSearchService interface, and its findBySearchTerm() method simply passes the search term and the Sort object forward to the invoked repository method.
The source code of the RepositoryTodoSearchService class looks as follows:
import org.springframework.beans.factory.annotation.Autowired; import org.springframework.data.domain.Sort; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Transactional; import java.util.List; @Service final class RepositoryTodoSearchService implements TodoSearchService { private final TodoRepository repository; @Autowired public RepositoryTodoSearchService(TodoRepository repository) { this.repository = repository; } @Transactional(readOnly = true) @Override public List<TodoDTO> findBySearchTerm(String searchTerm, Sort sort) { //Obtain search results by invoking the preferred repository method. List<Todo> searchResults = ... return TodoMapper.mapEntitiesIntoDTOs(searchResults); } }
Let's move on and find out how we can use the Sort object.
Using the Sort Object
After we have created the Sort object manually or obtained it by using Spring Data web support, we have to create the database query that sorts its query results by using the Sort object.
Let's start by finding out how we can sort all entities found from the database.
Sorting All Entities
If we want to sort all entities found from the database, we can use one of the following methods:
First, if we created our repository interface by extending the CrudRepository interface, we can modify it to extend only the PagingAndSortingRepository interface.
The relevant part of our repository interface looks as follows:
import org.springframework.data.repository.PagingAndSortingRepository; interface TodoRepository extends PagingAndSortingRepository<Todo, Long> { }
The PagingAndSortingRepository interface declares one method that we can use when we want to get all entities found from the database and sort them:
- The Iterable<T> findAll(Sort sort) method returns all entities found from the database and sorts them by using the sort options specified by the Sort object.
In other words, if we want to get a sorted list of all entities found from the database, we have to use the Iterable<T> findAll(Sort sort) method instead of the Iterable<T> findAll() method.
Second, if we created our repository interface by extending the Repository interface, we can declare the findAll(Sort sort) method in our repository interface.
The relevant part of our repository interface looks as follows:
import org.springframework.data.domain.Sort; import org.springframework.data.repository.Repository; import java.util.List; import java.util.Optional; interface TodoRepository extends Repository<Todo, Long> { void delete(Todo deleted); List<Todo> findAll(Sort sort); Optional<Todo> findOne(Long id); void flush(); Todo save(Todo persisted); }
We can now get a sorted of list of all entities found from the database by invoking the findAll() method and passing the Sort object as a method parameter.
Let's find out how we can sort the query results of database queries that use the query generation from the method name strategy.
Sorting the Query Results of Queries That Use the Query Generation From the Method Name Strategy
If we create our database queries from the method name of our query method, we can sort the query results by adding a new method parameter (Sort object) to the query method.
The search function of our example application is case-insensitive. It returns todo entries whose title or description contains the given search term. If our query method uses the query generation from the method name strategy, its source code looks as follows:
import org.springframework.data.domain.Sort; import org.springframework.data.repository.Repository; import java.util.List; interface TodoRepository extends Repository<Todo, Long> { List<Todo> findByDescriptionContainsOrTitleContainsAllIgnoreCase(String descriptionPart, String titlePart, Sort sort); }
Let's move on and find out how we can sort the query results of JPQL queries that are created by using the @Query annotation.
Sorting the Query Results of JPQL Queries That Use the @Query Annotation
If we create our database queries by using JPQL and the @Query annotation, we can sort the query results by adding a new method parameter (Sort object) to the query method.
The search function of our example application is case-insensitive. It returns todo entries whose title or description contains the given search term. If our query method uses the @Query annotation, its source code looks as follows:
import org.springframework.data.domain.Sort; import org.springframework.data.repository.Repository; import org.springframework.data.repository.query.Param; import java.util.List; interface TodoRepository extends Repository<Todo, Long> { @Query("SELECT t FROM Todo t WHERE " + "LOWER(t.title) LIKE LOWER(CONCAT('%',:searchTerm, '%')) OR " + "LOWER(t.description) LIKE LOWER(CONCAT('%',:searchTerm, '%'))") List<Todo> findBySearchTerm(@Param("searchTerm") String searchTerm, Sort sort); }
Let's move on and find out how we can sort the query results of JPA criteria queries.
Sorting the Query Results of JPA Criteria Queries
If we create our database queries by using the JPA Criteria API, our repository interface must extend the JpaSpecificationExecutor<T> interface. This interface declares one method that we can use when we want to sort the query results of JPA criteria queries:
- The List<T> findAll(Specification<T> spec, Sort sort) method returns all entities that fulfil the conditions specified by the Specification object. It sorts the returned entities by using the Sort object given as a method parameter.
In other words, we can sort the query results of JPA criteria queries by using the List<T> findAll(Specification<T> spec, Sort sort) method instead of the List<T> findAll(Specification<T> spec) method.
The source code of the RepositoryTodoSearchService class, which sorts our query results by using the Sort object, looks as follows:
import org.springframework.beans.factory.annotation.Autowired; import org.springframework.data.domain.Sort; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Transactional; import java.util.List; import static net.petrikainulainen.springdata.jpa.todo.TodoSpecifications.titleOrDescriptionContainsIgnoreCase; @Service final class RepositoryTodoSearchService implements TodoSearchService { private final TodoRepository repository; @Autowired public RepositoryTodoSearchService(TodoRepository repository) { this.repository = repository; } @Transactional(readOnly = true) @Override public List<TodoDTO> findBySearchTerm(String searchTerm, Sort sort) { Specification<Todo> searchSpec = titleOrDescriptionContainsIgnoreCase(searchTerm); List<Todo> searchResults = repository.findAll(searchSpec, sort); return TodoMapper.mapEntitiesIntoDTOs(searchResults); } }
Let's find out how we can sort the query results of database queries that are created by using Querydsl.
Sorting Query Results of Querydsl Queries
If we create our database queries by using Querydsl, our repository interface must extend the QueryDslPredicateExecutor<T> interface. This interface declares one method that we can use when we want to sort the query results of the invoked query:
- The Iterable<T> findAll(Predicate predicate, OrderSpecifier<?>... orders) method returns all entities that fulfil the search conditions specified by the Predicate object and sorts the query results by using the sort options specified by the OrderSpecifier objects.
This means that we can sort the query results of an existing Querydsl query by following these steps:
- Specify the sorting options by creating new OrderSpecifier objects.
- Invoke the findAll() method, and pass the Predicate and OrderSpecier objects as method parameters.
For example, if we want to modify the findBySearchTerm() method of the RepositoryTodoSearchService class to sort the query results in ascending order by using the value of the title field, we have to make following changes to the RepositoryTodoSearchService class:
- Add a private orderByTitleAsc() method to the class and implement by returning an OrderSpecifier object which specifies that the search results are sorted in ascending order by using the value of the title field.
- Make the following changes to the findBySearchTerm() method:
- Get the OrderSpecifier object by invoking the orderByTitleAsc() method.
- Invoke the Iterable<T> findAll(Predicate predicate, OrderSpecifier<?>... orders) method of the QueryDslPredicateExecutor interface instead of the Iterable<T> findAll(Predicate predicate) method. Pass the Predicate and OrderSpecifier objects as method parameters.
The source code of the RepositoryTodoSearchService class looks as follows:
import com.mysema.query.types.OrderSpecifier; import com.mysema.query.types.Predicate predicate; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import org.springframework.transaction.annotation.Transactional; import java.util.List; import static net.petrikainulainen.springdata.jpa.todo.TodoPredicates.titleOrDescriptionContainsIgnoreCase; @Service final class RepositoryTodoSearchService implements TodoSearchService { private final TodoRepository repository; @Autowired public RepositoryTodoSearchService(TodoRepository repository) { this.repository = repository; } @Transactional(readOnly = true) @Override public List<TodoDTO> findBySearchTerm(String searchTerm) { Predicate searchPred = titleOrDescriptionContainsIgnoreCase(searchTerm); OrderSpecifier sortSpec = orderByTitleAsc(); Iterable<Todo> searchResults = repository.findAll(searchPred, sortSpec); return TodoMapper.mapEntitiesIntoDTOs(searchResults); } private OrderSpecifier<String> orderByTitleAsc() { return QTodo.todo.title.asc(); } }
Let's move on and find out which sorting method we should use.
Which Sorting Method Should We Use?
Sometimes the technique that we use to create our database queries forces us to use a specific sorting method. For example,
- If our database queries are named queries or native queries that use the @Query annotation, we must add the sorting logic into our query strings.
- If we create our database queries by using the JPA Criteria API, we have to sort the query results by using the Sort class.
- If we create our database queries by using Querydsl, we have to sort the query results by using the OrderSpecifier class.
However, if we have a choice, we should put our query generation logic and sorting logic to the same place because it makes our code easier to read. This means that:
- If we create our database queries by using SQL or JPQL, we should add the sorting logic into our query strings.
- If we create our database queries by using the query generation from the method name strategy, we should use the same method for sorting our query results (append the OrderBy keyword to the method name). If we don't want to use this method because the method name of our query method becomes too long, we should rewrite our query by using the @Query annotation.
Nevertheless, there are a couple of situations when we must separate our query generation logic and sorting logic:
- If we have to paginate the query results of our database queries, we must sort them by using the Sort class. We will talk more about this in the next part of this tutorial.
- If we must support dynamic sorting (i.e. the sorting direction and the used fields can be changed), we must sort our query results by using the Sort class because it is the only way that allows us to fulfil this requirement.
Let's move on and summarize what we learned from this blog post.
Summary
This blog post has taught us five things:
- We can create Sort objects manually or obtain them by using Spring Data web support.
- We can configure Spring Data web support by annotating our application context configuration class with @EnableSpringDataWebSupport annotation.
- We can create OrderSpecifier objects by using the Querydsl query types.
- If the technique that we use for creating our database queries doesn't force us to use a specific sorting method, we should put our query generation logic and sorting logic to the same place.
- If we have to paginate the query results of our database queries, or we must support dynamic sorting, we must sort the query results by using the Sort class.
The next part of this tutorial describes how we can paginate the query results of our database queries.
P.S. You can get the example applications of this blog post from Github: query methods, JPA Criteria API, and Querydsl. If you decide to clone this repository, remember that the examples are found from the sorting branch.
Great work, this blog was a big help for me. I am just getting into using Spring Data JPA and there is not much info out there on it. I now understand it. Thanks.
Devin,
thanks for your comment. It was great to hear that I could help you out.
Hi Petry,
I have one question, how do you using one "Generic" repository, for example:
public interface GenericRepository
extends JpaRepository {
//method custom
void sharedCustomMethod(ID id);
}
You must have a GenericRepositoryImpl ?
For I am following the documentation JPA is indicating the use of this form, most here is giving the error: Invalid derived query! In shared property found for type java.lang.Object in the declaration of method.
can you help me?
Are you trying to add methods to a single repository or to all repositories?
If you are trying to add methods to a single repository, you can follow the instructions given in this blog post: Adding Functionality to a Repository.
If you want to add methods to all repositories, you should follow the instructions given in the reference manual of Spring Data JPA.
I hope that this answered to your question.
Hi,
I need to add methods to all repositories. And I put it as the specification:
public interface MyRepository
extends JpaRepository {
ClasseX sharedCustomMethod(ID id);
}
But, Is not working, an error occurs saying:
Invalid derived query! No property found for type sharedCustomMethod com.example.domain.ClasseX.
So, it if I put anotation @ Query upon the method, the error disappears. For example:
public interface MyRepository
extends JpaRepository {
@Query
ClasseX sharedCustomMethod(ID id);
}
understand?
Thank you, for help me!
The exception is caused by the fact that the Spring Data repository infrastructure thinks that you want to generate the executed query from the method name of your query method and cannot find the sharedCustomMethod property from ClasseX class.
If you annotate the method with the @Query annotation, the problem disappears because Spring Data JPA executes the query given as the value of the @Query annotation when your query method is called.
If you want to add custom methods to all repositories, you must follow the instructions given in these resources:
I hope that this answered to your question.
Thank you sir!
You are welcome!
Hi, is there a way to sort for a sublist. I have an event which has participants and now I want to find the event with the most participants. Thought something like the following would work.
repository.findAll(QEvent.event.participants);
or
repository.findAll(QEvent.event.participants.size());
I think that is a little problem for you but I don't get it to work.
Thanks for your help!
Hi,
I think that the easiest way to do this is to create a query method and limit the query results by applying pagination. You can do this by following these steps:
The source code of the event repository looks as follows:
You can now get the Event which has the most participants by using the following code:
I did not test this but it should do the trick.
Thank you very much!
That did it!
I thought that there has to be a solution with predicates but with the @Query-annotation works great.
I have another Problem that I solved in the services but perhaps there is a solution without handling it manually. What is when there are two events with the same number of participants? I want to have that event on first position that has the number of participants at first time.
Is there a best practice doing it with a query or would you do that also in the code?
You are welcome! I am happy that I could help you out.
About your other problem:
If your current solution works and it is not a resource hog, I recommend that you continue using it.
On the other hand, if you want to handle this on database, you can solve this by following these steps:
Event
class. This field is updated every time when a new participant registers to the event.The source code of your repository interface looks as follows:
I hope that this answered to your question.
The solution with with timestamp was an idea I also had. The problem with this is, that I always have to watch what happens when a participant is canceling its participation. Maybe this is something where it comes to errors in database when it is not well handled. So I think I will try the solution I have at the moment and when I see that it leads to lags I have to think about the solution with the timestamp.
Thank you again for your great help!
I agree that dealing with a cancellations can be tricky and you might have to change your domain model in order to solve this problem. I though about this and I think that can you solve this problem by following these steps:
Registration
. This model contains a reference to the event, participant and a registration timestamp.Event
class to contain a list ofRegistration
objects.I think that this would do the trick.
Hi Petri,
First I would like to apologize for not responding in time, but I was in an uproar here and I got no time to answer.
Thank you for your assistance because it was quite valid.
But now I got a question somewhat puzzling, and his experience must have been through it. It is this: I noticed that in the Spring MVC framework, the business rules of the application hum in the service layer, but if I need specific rules, also more common for the entire application when the use of that object in the service layer. Like, at some point my controller will call the desired service, but prior to persist itself, I'll run some rules (validations), as it would be? as you do it in your applications in case.
Thanks again, and look forward ...
PS: If you do not understand can ask what I try to explain better ...
Hi Thiago,
Don't worry about not having time to answer to me. I think it is only natural that sometimes we have more important things to take care of.
About your problem:
Although it is quite common that Spring applications have business logic in the service layer, I think that the business logic should be added to the domain model. If I understood you correctly, you want to validate that the state of your domain object is correct before you persist it to the database. If this is the case, you probably want to do this check when a new domain object is added to the database or the information of an existing domain object is updated.
There is a quite clean way to implement this. You do that by following these steps:
private
so that it is impossible to call it outside your domain model class).I hope that this answered to your question.
Hi Petry,
Thanks for answer my question, in true i don't want validate my object, because i'm using validation via hibernate annotation and BindingResult , then the i want say is validate business rules properly said, in nivel of rules really, for example:
If one user before be inserted, need to validate if it is active, have dependents and have age above 20 years, where would this validation? understood.
Most I've managed to solve here, I created an interface that defines my methods that must be performed before this insertion, and each service implements this interface, implementing their respective rules.
Thank again you for your help ... it's people like you that our community needs, willing to help ...
Thank you again!
Hi Thiago,
Maybe I did not choose my words wisely in my first answer (the word validation was maybe not a good choice). I will try to clarify what I meant.
When you are creating a new object or updating the information information of an existing object, you have to implement a two-step validation process. The steps of this process are described in the following:
First, you need to validate that the entered data is "correct". This validation typically ensures that all the required fields are given and that the given data is "correct". This is the validation which is done in the controller classes.
Second, you need enforce the business rules of your application. The best place for this logic is to add it to your domain model class. There are basically three reasons for this:
You mentioned that you are enforcing the business rules on the service layer. Although this typical for Spring applications, I think that it has one big problem:
The service layer should not know when the state of an object is not legal. Only the object in question should know this. I think that the best way to demonstrate this is to think about the following situation:
Let's assume that I am a service class and you are an object. I order you to do something. Who should check that you are ready to do it? I think that it would a bit weird if it would be me.
Here is the source code of a simple domain model class which enforces the business rules when new objects are created or the information of an existing object is updated:
I hope that this answered to your question.
You're amazing my friend, Congratulations for your great tutorials.
Thank you a lot, has been very useful to me.
Thank you for your kind words. I really appreciate them. It is is good to hear that these tutorials have been useful to you.
Great!
Thanks!
Great examples, I especially like that you have also examples of unit tests :) Spring Data JPA documentation sucks, for example using Sort there's zero lines of howto use it.
Olli,
thank you for your kind words.
You might want to check out my blog post titled: Spring Data Solr Tutorial: Sorting. It has a section titled: 'Specifying the Sort Options of a Query' that explains how you can create new
Sort
objects.Also, I plan to update my Spring Data JPA tutorial since my current tutorial is a bit obsolete. Thanks for pointing out that I can search inspiration from the reference manual of Spring Data JPA.
Kiitos suuri blogi (I hope that means, thanks for your great blog)
It had the best explanation for the sorting in the repository methods. However, I could still find no way to sort beyond the basic field name, like this:
I want to sort on an expression on a field name, like this:
Is that possible?
Thanks
Mark.
I might have solved my own problem like this in a named Query (taken from another post on this page)
Still would have been nice if something like this would have worked:
Hi,
I am sorry that it took me some time to answer to your question. I have been trying to find a solution to the same problem for a while now, but I haven't been able to find a way to do this by using the
Sort
class.The Javadoc of the
Sort
class states that:I assume that you can use the
Sort
class only when you want to sort your query results by using the values of your entity's properties. That is why you cannot use it if you want to sort your query results by using the size of the associated collection (it would require a some kind of expression support that seems to be missing).In other words, as far as I know, you have to use JPQL to solve this problem. However, I would love to be wrong.
Thanks for this really in-depth analysis of sorting with our loved Spring!
In my limited experience, I found myself to use less and less these annotations/hierarchies. Most of the time I have to optimize my queries, joining with other entities or returning a subset of entity's properties.
So I end with writing a whole querydsl query (starting from Entity Manager) and missing all these goodies.
Thank you for your kind words! I really appreciate them.
I have noticed the same thing. Although I prefer using the
@Query
annotation, the syntax that is required to query DTOs is pretty awful. That is why I often end up adding custom methods into my repository interfaces.I use
NamedParameterJdbcTemplate
andBeanPropertyRowMapper
for this purpose. Of course, if I need to create dynamic queries, I will use Querydsl (if it is possible).@service
I thought emp.save would not save since depEnity has wrong length (max is 4, i have passed 10).
what happens is, emp entity saved successfully and dept.save throws Exception for max length.
is there anything to be done?
Is the
commitTwoRep()
method invoked inside a transaction? If theemp
entity is saved even though thedept.save()
method throws an exception, I assume that you are not using transactions. If you don't know how to use the declarative transaction management, read the section 12.5 Declarative transaction management of the Spring Framework reference documentation.Yes, you are right. commitTwoRep() has declared as Transnational.
@Transactional //this was missing in the previous comment
commitTwoRep() {
Is the
commitTwoRep()
method public? If it is not public, you should make it public because the@Transactional
annotation works only when the annotated method is public.If the method is public, it is impossible to say what is wrong because I am not able to debug your code (or see your app context configuration). Can you add the whole class and your application context configuration to Pastebin.com?
If you cannot share your code, you should check out a blog post titled: Tips for Debugging Spring's
@Transactional
Annotation.Can we use In operator in native query to delete records? seems its not working.
Yes. Because I had never used the
IN
operator in a native query, I did a small test and noticed that we can use it in native queries as well.First, if you want to find something from the database, you can use this query method:
Second, if you want to delete something from the database, you can use this query method:
If you have any additional questions, don't hesitate to ask them.
Hi, I have been using JpaRepository in my application, now i have to save around 2000 entities.
is it advisable to use save (Interable EntityList>, what is the maximum entities can be saved in one transaction? or what is the best limit?
Hi Sankar,
It's hard to give specific advice because this really depends from your use case and the saved entity. The only way to know this is to profile your application and see how it behaves when you try to save 10, 50, 100, 500, 1000, and 2000 entities. Basically the idea is to save X entities inside one transaction and see which X fulfills your performance requirements.
Is there a reason why you want to save so many entities inside one transaction?
Its like recreating groups for a company. Delete old groups emp and recreate new groups for all the emp.
Is this a batch job? If so, you should consider using Spring Batch.
Hi Petri,
Your blog is amazing! It's really helpful to me.
I'm a beginner to Spring-data-jpa, so it's not surprised that I have met many questions, thanks for your detailed examples, I have solved most of them. But recently I was stuck by one question:
I want to add collation variable in query, so I can execute query like below dynamically:
I mean the b.name, direction and "en_US" should be variables.
Do you have a proper solution? Thanks in advance.
Best Wishes!
Brent.
Hi Brent,
If you want to specify the name of the sort property (e.g.
b.name
) and the sort direction (e.g.desc
) dynamically, you need sort your query results by using theSort
class (this is explained in this blog post).However, JPQL doesn't support the
COLLATE
keyword. This means that you have to use SQL query for this purpose. The problem is that AFAIK you cannot set the sort field and the sort order dynamically if you use native queries with Spring Data JPA.In other words, it seems that you cannot fulfill your requirements with Spring Data JPA.
Hi Petri,
Thanks for your guidance. Do you mean that even I choose native SQL, it's still impossible to add COLLATE keyword in the query? So I have to turn to Spring jdbcTemplate or something related to dialect?
Thanks.
Brent.
Hi Brent,
I meant that AFAIK you cannot support dynamic sorting (specify sort property and direction) and use the
COLLATE
keyword at the same time (if you use Spring Data JPA). It seems that your "best" choice is to useJdbcTemplate
.you can use like this, like a Switch case clause, you use 'any string' just to control the direction of your order by, direction of what column will be ordered, for 1 column you have to use 1 case, for instance if you have two column you have to use 2 'CASE WHEN' like the example below:
+" ORDER BY CASE WHEN :sort = 'any string' THEN yourSqlColumn END ASC "
+" , CASE WHEN :sort = 'any string' THEN yourSqlColumn END ASC "
this is the most dynamic way to have a order by using Spring data jpa you will find.
This looks awesome. Thank you for sharing :)
Hi, thank you for this post!
This was useful to implement an example with spring data, but now I want to do the unit test but I don't know to to mock the Pageable object. I have the following code:
could you help me please?, I don't know how to create a mockito object to test this method, I have been searching some example but I don't found anything that can be useful.
Hi Juanca,
First, if you want to verify that your service method returns the correct objects, you need to create a
WorkerRepository
mock in your setup method. After you have done this, you can configure it to return a list ofWorker
objects by using the following code:Second, if you want to verify that your service method creates a
Pageable
object with correct page number and page size, you have to use argument captors. You can do this by using the following code:P.S. If you use Java 8, you might want to read this blog post.
Thank you so much Petri, it was very useful your help.
Kind regards,
You are welcome!
Hi Petri,
Your website is awesome!
Can you help me? How do I change the name of the query parameters used in Pageable ? By default they are size = and page= . If I needed to change pageSize = and currentPage= . What would be the best approach?
Thanks!
Hi Anaice,
You need to customize the default configuration provided by Spring Data JPA. The Reference Documentation of Spring Data JPA describes how you can do this:
In your case, you have to override the
pageableResolver()
method and implement it by following these steps:PageableHandlerMethodArgumentResolver
object.setPageParameterName()
andsetSizeParameterName()
methods.If you have any additional questions, don't hesitate to ask them.
Hi Petri,
Accurate as always.
Thank you very much!
Hi Anaice,
You are welcome. I am happy to hear that you were able to solve your problem. Also, thank you for sharing your solution. I bet that it is useful to other people who have the same problem.
Is it possible to have a complex sort query to be written in specification?
eg, a simple table with 3 columns
(ID date_of_death date_of_birth)
Now i want to order based on 2 date columns, with a simple logic written in sql may look like
Select ID,date_of_birth,date_of_death from SOMETABLE ORDER BY CASE
WHEN date_of_birth IS NULL THEN date_of_death
WHEN date_of_death IS NULL THEN date_of_birth
WHEN date_of_birth < date_of_death THEN date_of_birth
END;
Could you please enlighten me if it's possible
Hi,
Very nice tutorial. A lot example queries you mentioned. I was wondering is there any possible query for finding only one random id? Thanks for your efforts. really appreciated.
Hi,
It is possible, but you need to use SQL. This blog post explains how you can select a random database row when you are using MySQL, PostgreSQL, Microsoft SQL Server, DB2, or Oracle. That being said, this query might be quite slow (more details).
What if i pass sorting as null in findAll Method does it givies any error
I have never tried that, but I assume that Spring Data JPA will throw an exception if the
Sort
object is null. I would probably specify a default sort direction or invoke a method that doesn't take aSort
object as a method parameter.Hello sir,
within orderBy() method of "Sorting" return new Sort(Sort.Direction.ASC, "title");
May I know this "title" is an exact field of POJO class or representing directly a column name in DB?
how i can sort by usint multipel table below is my repository
public interface DownPaymentRepository extends JpaRepository {
@Query(value = "select ddc.sku as sku, slc.description as description from DefaultDownpaymentConfig ddc,SkuLtcConfig slc where ddc.sku = slc.sku order by slc.description desc")
public Page getAlldefaultDownpaymentConfig_SkuAndskuLtcConfig_Description(Pageable pageable);
}
Hi,
I have never done this myself, but based on this StackOverflow answer, you should be able to solve your problem as long as you specify the used alias when you create a new
Sort
object. Also, keep mind that you can combine multipleSort
objects by using thestatic
by()
method of theSort
class.Thank you so much! Great tutorial, a real blessing
Thank you for your kind words. I really appreciate them!