assignment order jpa

Spring Data JPA Tutorial: Sorting

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:

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:

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:

• Spring Data JPA Tutorial: Creating Database Queries From Method Names
• How to sort by multiple properties in Spring Data (JPA) derived queries? @StackOverflow

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:

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 .

• Spring Data JPA Tutorial: Creating Database Queries With the @Query Annotation
• Spring Data JPA Tutorial: Creating Database Queries With Named Queries
• JPQL ORDER BY clause
• SQL ORDER BY clause

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:

The following examples demonstrates how we can implement the private orderBy() method:

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:

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:

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:

• The Javadoc of the Sort class

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:

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:

• Spring Data JPA Reference Documentation: 3.7.1 Web Support
• The Javadoc of the @EnableSpringDataWebSupport annotation
• The Javadoc of the SortHandlerMethodArgumentResolver class
• The Javadoc of the @SortDefault annotation

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:

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:

• The Javadoc of the PagingAndSortingRepository

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.

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.

• Spring Data JPA Tutorial: CRUD

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:

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:

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:

• Spring Data JPA Tutorial: Creating Database Queries With the JPA Criteria API
• The Javadoc of the JpaSpecificationExecutor interface

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.
• 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.
• Spring Data JPA Tutorial: Creating Database Queries With Querydsl
• The Javadoc of the OrderSpecifier class
• The Javadoc of the QueryDslPredicateExecutor interface

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.

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.

thanks for your comment. It was great to hear that I could help you out.

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.

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:

@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:

• The reference manual of Spring Data JPA has a brief description of the required steps.
• Customizing Spring Data JPA Repository is a blog post written by Boris Lam and it explains how you can add custom methods to all repositories.

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!

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:

• Add the query method to your repository interface. This method returns a list of Event objects and takes a Pageable object as a parameter.
• Annotate the query method with the @Query annotation and set the executed query as its value.

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:

• Add a timestamp field to the Event class. This field is updated every time when a new participant registers to the event.
• Modify the sorting logic of your query to take this new field into account.

The source code of your repository interface looks as follows:

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:

• Introduce a new model called Registration . This model contains a reference to the event, participant and a registration timestamp.
• Change the Event class to contain a list of Registration objects.
• When a participant cancels his registration, you have to remove the registration from the associated event.
• Find the most recent registration and update the timestamp of the associated event.

I think that this would do the trick.

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

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:

• Add the validation method to your domain model object. This method should throw an exception if the state of your domain object is invalid.
• Call the validation method before a new domain model object is persisted or existing one is updated. The optimal way to do this is to call the validation method inside your domain object when a new domain object is created or the information of an existing one is changed. This way the other classes which deal with these objects won't have to do it (you can also change the visibility of the validation method to private so that it is impossible to call it outside your domain model class).

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!

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:

• If the business rule changes, you don't have to change them in multiple locations.
• It makes your code cleaner (especially the service layer) because the services don't have to worry about enforcing the business rules of your application.
• When you need to check how the business rules are implemented, you know where to find the implementation.

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:

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

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:

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:

Sort option for queries. You have to provide at least a list of properties to sort for that must not include null or empty strings.

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.

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.

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 and BeanPropertyRowMapper for this purpose. Of course, if I need to create dynamic queries, I will use Querydsl (if it is possible).

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 the emp entity is saved even though the dept.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?

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 .

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.

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 the Sort 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.

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.

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 use JdbcTemplate .

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.

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 of Worker 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,

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!

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 :

To customize this behavior extend either SpringDataWebConfiguration or the HATEOAS-enabled equivalent and override the pageableResolver() or sortResolver() methods and import your customized configuration file instead of using the @Enable -annotation.

In your case, you have to override the pageableResolver() method and implement it by following these steps:

• Create a new PageableHandlerMethodArgumentResolver object.
• Change the parameter names by using the setPageParameterName() and setSizeParameterName() methods.
• Return the created object.

Accurate as always. Thank you very much!

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

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.

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 a Sort 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);

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 multiple Sort objects by using the static by() method of the Sort class .

Thank you so much! Great tutorial, a real blessing

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

Leave a Reply Cancel reply

© 2010-Present Petri Kainulainen (all code samples are licensed under Apache License 2.0 ) Cookie Policy | Privacy Policy

Understand Spring Data JPA with Simple Example

1. Configure Dependencies in Maven

2. configure database connection properties in persistence.xml, 3. configure entitymanagerfactory and transactionmanager, 4. code model class, 5. code repository interface.

6. Code Service Class

7. code test program for spring data jpa.

References:

• Spring Data JPA Project
• Spring Data JPA Reference Documentation
• CrudRepository Javadoc
• JpaRepository Javadoc

Related Spring and Database Tutorials:

• Spring Data JPA EntityManager Examples (CRUD Operations)
• JPA EntityManager: Understand Differences between Persist and Merge
• Spring Data JPA Custom Repository Example
• Spring Data JPA Native Query Examples
• Spring MVC with JdbcTemplate Example
• How to configure Spring MVC JdbcTemplate with JNDI Data Source in Tomcat
• Spring and Hibernate Integration Tutorial (XML Configuration)
• Spring MVC + Spring Data JPA + Hibernate - CRUD Example

Other Spring Tutorials:

• Understand the core of Spring framework
• Understand Spring MVC
• Understand Spring AOP
• Spring MVC beginner tutorial with Spring Tool Suite IDE
• Spring MVC Form Handling Tutorial
• Spring MVC Form Validation Tutorial
• 14 Tips for Writing Spring MVC Controller
• Spring Web MVC Security Basic Example (XML Configuration)

About the Author:

Add comment

Notify me of follow-up comments

Comments  

• Meet Our Team
• Judgment Enforcement & Collection
• Collections, Pre-litigation, Litigation, & Remedies
• Spousal Support Enforcement
• Judgment Domestications and Sister State Judgments
• Los Angeles Collection Wins
• Santa Monica/Beverly Hills
• Orange County Wins
• San Fernando Valley Wins
• Ventura County Judgement Collection Wins
• Riverside Judgment Collection Wins
• Fresno County Judgment Enforcement
• Testimonials
• Online Case Submission
• POWER OF ASSIGNMENT ORDERS IN JUDGMENT ENFORCEMENT

A staple in a judgment enforcement lawyers toolbox is the assignment order. Essentially, it is a Court-ordered assignment of rights of the debtor to receive payments,  which diverts those payments away from the debtor, and cause them to be made to Creditor instead. Similar to a wage garnishment, but covering just about any sort of payments imaginable, it is one of the broadest remedies in the Enforcement of Judgments Law (EJL).

AI art by Richard Evanns

An incomplete list of payments covered by assignment orders appears in  Code of Civil Procedure 708.510 :

*Rents and lease payments

*Royalties and residuals

*Commissions, Stipends, Draws, other non-wage compensation

*Money paid by insurance policies

However, because the code is clear that the list “includes but is not limited to” the things listed, assignment orders cover just about every payment type you can think of. It can also include a restraining order preventing transfer of the payments, under  Code of Civil Procedure Section 708.520 .

Another great thing about assignment orders as a judgment collection mechanism is that, what the Court considers “payments to the debtor” can be construed in multiple ways, and assignment orders can grab money that would otherwise require going after third parties, to get. Thus, if a debtor has diverted payments away from his/herself to somewhere else, you can “re-divert” them back, and take them.*

Examples of this are:

Los Angeles Judgment Enforcement Case #1 : Debtor was working for his own company, but wasn’t getting paid. His ex wife was mysteriously getting $20000.00 per month from the company, even though she had never set foot in, or worked for, the business. We asked the court to divert those payments which the ex-wife was getting, to us, based on the fact that this was really money the debtor was earning, he was just paying it to someone else. The Court agreed, and granted the order.

Los Angeles Judgment Enforcement Case #2 : Debtor owned a 40% of a large warehouse property. The rent was being paid to a relative of his, and he stated that he transferred the property to this relative (but the documents provided were inconclusive on this point) We asked for an assignment of rents, because even though the money was going to another person, the debtor owned the property, and therefore, those were the debtors rents, in reality. Court agreed, and we received $7000 per month for years on this case.

The list goes on, but the point is illustrated: Money that is going to third parties, but which can be shown to  “actually” belong to the debtor, can be garnished.* The idea of what “actually belongs” to whom, is a topic for another time, but one which we promise we will write about.

Lastly, Assignment orders  be very broad in not only in terms of the types of payments, but in terms of the additional things that can be in iters. Examples of this are:

*Court can order debtor to submit monthly accountings of all moneys subject to the order

*Court can order Debtor to collect all the money, account for it, and pay it over the creditor himself, in situations where the creditor cannot serve the assignment order on all the payment sources (Example, Debtor was a salesman and did not know who his future customers would be that would be paying him; Court ordered debtor to keep records of all customers he solicited and who actually paid him and how much, and to pay Creditor at end of month )*

Assignment orders , like many remedies in judgment enforcement, can range from extremely simple and straightforward, to extremely creative, and everything in between.

If you ever want to talk about it, Evanns Collection Law is a Los Angeles Judgment Enforcement Law Firm. Our judgment enforcement attorneys have been enforcing judgments in Los Angeles and throughout Southern California since 2011. We write articles to inform and help others regarding enforcing their judgments.  We love what we do and are happy to talk to clients and other practitioners regarding their judgment collection needs. Phone calls are always free. Call us!

*: Note , this is NOT in the code, this is strictly something that needs to be argued in front of the judge, and the judge needs to be convinced of it. No warranties or representations are made that this will always work or ever work for you; Each case depends on facts and evidence. Always consult a judgment collection attorney with such matters.

Share This Story, Choose Your Platform!

About the author: richard.

Related Posts

• Judgement enforcement and collections against third parties

How to Collect Divorce Judgment in California

Collecting A Judgment From an Employer

Collecting from an Out of Business Company or Employer

How to Prevent Getting Ripped Off in Litigation – Part 2

Recent posts.

• The Power of Intangible Levies: Making Third Parties Pay (part 2)
• Sometimes Speed is the Key – Emergency Application to Enforce Sister State Judgment
• How to Collect on a Judgment in a California Civil Matter

Recent Comments

• November 2023
• January 2021
• October 2020
• October 2019
• January 2015
• November 2014
• October 2014
• September 2014
• August 2013
• December 2012
• October 2012
• August 2012
• Domesticating Judgment
• Judgment Collection
• Uncategorized
• Entries RSS
• Comments RSS
• WordPress.org

Creating a CRUD REST API/Service with Spring Boot, JPA, and Hibernate

This guide will help you create a CRUD REST API/Service with Spring Boot, JPA, and Hibernate. We will create a JPA Repository for a Student Entity and expose it using a Student Resource.

You will learn

• What is a RESTful Service?
• Basics of designing a REST API.
• How to create a RESTful service offering all CRUD operations?
• How to use Spring Boot, Spring MVC, JPA, and Hibernate to create a RESTful API?
• How to execute different kinds of REST API with Postman?
• What are the differences between GET, POST, PUT, and DELETE request methods?

Resources Overview

In this guide, we will create a student resource exposing three services using proper URIs and HTTP methods:

• Retrieve all students: @GetMapping("/students")
• Get details of a specific student: @GetMapping("/students/{id}")
• Delete a student: @DeleteMapping("/students/{id}")
• Create a new student: @PostMapping("/students")
• Update student details with @PutMapping("/students/{id}")

Project Code Structure

The following screenshot shows the structure of the project we will create.

A few details

• SpringBoot2RestServiceApplication.java - The Spring Boot Application class that Spring Initializer generates.This class acts as the launching point for applications.
• pom.xml -Contains all the dependencies needed to build this project. We will use Spring Boot Starter AOP.
• Student.java - Student JPA Entity
• StudentRepository.java -Student JPA Repository This was created using Spring Data JpaRepository.
• StudentResource.java -Spring Rest Controller exposing all services on the student resource.
• data.sql : initial data for the student table. Spring Boot would execute this script after the tables are created from the entities.

You will require the following tools:

• Maven 3.0+ is your build tool
• Your favorite IDE. We use Eclipse.

A Complete Maven Project With Code Examples

Our Github repository has all the code examples - https://github.com/in28minutes/spring-boot-examples/tree/master/spring-boot-2-rest-service-basic

Types of Web Services

Not exactly types, but rather a broad classification

These are not really mutually exclusive. Some SOAP services can actually be RESTful.

First of all, REST does not define a standard message exchange format. You can build REST services with both XML and JSON. However, JSON is a more popular format than REST.

So, if it does not define a standard message exchange format, what is REST then?

REST is a style of software architecture for distributed hypermedia systems.

REST stands for REpresentational State Transfer. The definitions of REST can be vague. So let’s understand the important concepts.

A key abstraction in REST is a resource. There is no restriction on what can be a resource. A todo is a resource. A person on Facebook is a resource.

A resource has a URI (Uniform Resource Identifier):

• /user/Ranga/todos/1
• /person/Ranga

A resource will have representation.

A resource will have a state. The representation of a resource should capture its current state.

When a resource is requested, we provide a representation of the resource.

REST and HTTP

REST is built on top of HTTP (Hypertext Transfer Protocol). HTTP is the language of the web.

HTTP has a few important verbs.

• POST: Create a new resource
• GET: Read an article
• PUT: Update an existing resource
• DELETE: Delete a resource

HTTP also defines standard response codes.

• 200 - SUCCESS
• 404 - RESOURCE NOT FOUND
• 400 - BAD REQUEST
• 201 - CREATED
• 401 - UNAUTHORIZED
• 415 - UNSUPPORTED TYPE - Representation not supported for the resource
• 500 - SERVER ERROR

Restful Service Constraints

• Client-Server: There should be a service producer and a service consumer.
• The interface (URL) is uniform and exposes resources. The interface uses nouns (not actions).
• The service is stateless. Even if the service is called 10 times, the result must be the same.
• The service response should be cacheable. HTTP cache, for example.
• Services should assume a layered architecture. The client should not assume direct connection to the server-it might be getting info from a middle layer-cache.

Creating RESTful APIs

Following are the important things to consider when designing RESTful API’s:

• While designing any API, the most important thing is to think about the api consumer, i.e., the client who is going to use the service. What are his needs? Does the service make sense to him? Does the request and response format make sense to him?
• For the rest, we think nouns (resources) and not verbs (not actions). So, URIs should represent resources. URIs should be hierarchical and as self-descriptive as possible. Prefer plurals.
• GET : You should not update anything. should be idempotent (results the same in multiple calls). 200 (OK) + 404 (NOT FOUND) +400 (BAD REQUEST) are possible return codes.
• POST : Should create new resource. Ideally, return JSON with a link to the newly created resource. same return codes as possible. In addition, return code 201 (CREATED) is possible.
• PUT : Update an existing resource. update client details. Possible Return Codes : 200(OK)
• DELETE : Used to delete a resource.

When do you use JPA?

• SQL Database
• Static Domain Model
• Mostly CRUD
• Mostly simple queries/mappings

Bootstrapping with Spring Initializr

Creating a REST service with Spring Initializr is a cake walk. We will use Spring Web MVC as our web framework.

Spring Initializr http://start.spring.io/ is great tool to bootstrap your Spring Boot projects.

As shown in the image above, following steps have to be done

• Choose com.in28minutes.springboot.rest.example as Group
• Choose spring-boot-2-rest-service-basic as Artifact
• Click Generate.
• Import the project into Eclipse. File > Import > Existing Maven Project

Do not forget to add JPA and H2 as dependencies.

Make Your Very First JPA Entity 

The first step is to create a JPA Entity. Let’s create a simple student entity with a primary key id.

Important things to note:

• @Entity : Specifies that the class is an entity. This annotation is applied to the entity class.
• @Id : Specifies the primary key of an entity.
• @GeneratedValue : It provides for the specification of generation strategies for the values of primary keys.
• public Student() : JPA-friendly default function Object() { [native code] } 

When the application reloads, you can launch the H2 console at http://localhost:8080/h2-console.

You will see that a new table called ‘student’ is created in H2 Console.

How did the Student table get created?

Spring Boot Auto Configuration detects that we are using an in-memory database H2. It autoconfigures the property to create the database tables based on the entities.

Let’s now populate some data into the student table.

/src/main/resources/data.sql

When the application reloads, you will see the following statements in the log, indicating that the sql files are picked up.

After app reload, when you login to H2 Console (http://localhost:8080/h2-console), you can see that the student table is created and the data is populated.

Make a JPA Repository class that reads students from a database.

/src/main/java/com/in28minutes/springboot/jpa/hibernate/h2/example/student/StudentRepository.java

We created a simple interface for StudentRepository, extending JpaRepository.

• We will talk about all the methods in the JpaRepository a little later.
• public interface StudentRepository extends JpaRepository<Student, Long> - We are extending JpaRepository using two generics—Student and Long. Student is the entity that is being managed and the primary key of Student is Long.

JpaRepository

JpaRepository (defined in Spring Data JPA) is the JPA-specific repository interface.

JpaRepository extends PagingAndSortingRepository , which in turn extends the CrudRepository interface. So, JpaRepository inherits all the methods from the two interfaces shown below.

PagingAndSortingRepository

CrudRepository

Exposing resources using StudentResource

Let’s start with setting up the StudentResource class and then move into creating methods to handle different kinds of request methods to the Student Resource.

Setting up Student Resource

• @RestController : Combination of @Controller and @ResponseBody. Beans returned are converted to and from JSON or XML.
• @Autowired private StudentRepository studentRepository : Autowire the StudentRepository so that we can retrieve and save data to the database.

Getting Access to Resources for Students

Let’s create the method to expose the details of all students.

The below picture shows how we can execute a Get Request Method on a resource using Postman, my favourite tool to run rest services.

• URL - http://localhost:8080/students
• Request Method - GET

Let’s create a method to expose the details of a specific student.

• URL - http://localhost:8080/students/10002

Showcase the DELETE Method on Student Resources

The delete method is simple. All you have to do is to call studentRepository.deleteById(id) .

The below picture shows how we can execute a DELETE Request method on a resource from Postman, my favourite tool to run rest services.

• Request Method - DELETE

Request - Empty Body Response with status 200 - Successful

Expose the POST method to create a new student

The POST method is simple. All you have to do is to call studentRepository.save(student) . Note that we are using @RequestBody to map the student details from request to bean. We are also returning a ResponseEntity with a header containing the URL of the created resource.

The below picture shows how we can execute a POST Request method on a resource from Postman, my favourite tool to run rest services.

• Request Method - POST
• Status 201 - CREATED
• Header Location →http://localhost:8080/students/2

To update existing students, use the PUT Method.

Before updating the student, we check if the student exists. If the student does not exist, we return a not found statur. Otherwise, we save the student details using studentRepository.save method.

The below picture shows how we can execute a PUT Request method on a resource from Postman, my favourite tool to run rest services.

• URL → http://localhost:8080/students/10002
• Method → PUT

Response with status 200 - Successful

Remember that you can check the updates in the database using H2 Console http://localhost:8080/h2-console after each of the requests.

Example of Complete Code

Github repository has all the code examples - https://github.com/in28minutes/spring-boot-examples/tree/master/spring-boot-2-rest-service-basic

Just Released

Related Articles

Spring Profile - Quick Tutorial for Beginners

Microservices Architectures - What is Service Discovery?

Index - 500+ Videos

Spring Boot Tutorials for Beginners

Introduction to spring boot framework - a quick tutorial for beginners.

Introduction To Spring Data Rest - Quick Tutorial for Beginners

Spring batch tutorial for beginners.

What Are Spring Projects?

Getting started with spring boot - 7 things a beginner should know.

Versioning RESTful Services - Spring Boot REST API

Creating a soap web service with spring boot starter web services, 20+ spring boot projects with code examples, spring boot rest api projects with code examples, spring boot exception handling for restful services errors, implementing validation for restful services with spring boot, related courses.

• Send Message
• Core Java Tutorials
• Java EE Tutorials
• Java Swing Tutorials
• Spring Framework Tutorials
• Unit Testing
• Build Tools
• Misc Tutorials
• Address.java
• Employee.java
• ExampleMain.java
• TableMappingMain.java
• persistence.xml

Maintaining List Order in Jpa

If you want to maintain the order of a list of objects retrieve from the database using hibernate/jpa, use the @OrderColumn annotation. An additional column will be created in the database (if ddl is set to update) that will keep track of the order/position of an item in a list.

CAVEAT: If you change the position of an item in a list OR delete an item besides the last one, it will cause a multi update across the list to update all other positions. Be aware of this intensive operation for large lists.

• Java Tutorial
• Java Spring
• Spring Interview Questions
• Java SpringBoot
• Spring Boot Interview Questions
• Spring MVC Interview Questions
• Java Hibernate
• Hibernate Interview Questions
• Advance Java Projects
• Java Interview Questions
• JPA - ORDER BY Clause
• MySQL ORDER BY Clause
• SQLite ORDER BY Clause
• Python SQLite - ORDER BY Clause
• JPA - Criteria WHERE Clause
• JPA - Cascade Remove
• JPA - Cascade Persist
• JPA - Criteria GROUP BY Clause
• SQL Server - OVER Clause
• JPA - Criteria SELECT Clause
• How to Custom Sort in SQL ORDER BY Clause?
• PHP | MySQL ORDER BY Clause
• Node.js MySQL Order By Clause
• Python MySQL - Order By Clause
• PostgreSQL - ORDER BY clause
• Group by clause in MS SQL Server
• Python MariaDB - Order By Clause using PyMySQL
• ORDER BY in SQL
• PostgreSQL - LIMIT clause
• Difference between order by and group by clause in SQL

JPA – ORDER BY Clause

JPA in Java can be defined as the Java Persistence API and consists of Java instructions for accessing and managing data between Java objects and related databases. JPA provides ways for us to access object-oriented and complex databases through SQL queries and database connections. There are many clauses in JPA and the ORDER BY clause is one of them. The ORDER BY clause can be used to sort the query results in ascending or descending order based on the fields.

ORDER BY Clause in JPA

The ORDER BY clause in JPA is used to sort query results generated in a JPQL (Java Persistence Query Language) query. This allows us to format the query results in the desired format and simplifies data interpretation and manipulation.

Steps to Implement ORDER BY Clause:

• Define the JPQL query with the SELECT statement of the JPA application.
• We can append the ORDER BY clause to the query and it can specify the fields by which results should be sorted.
• Execute the query and retrieve the sorted results of the program.

1. Sorting in Ascending Order

In the JPA Application, we can sort the query results in ascending order and it can specify the fields in the ORDER BY clause without the additional keywords.

2. Sorting in Descending Order

We can sort the query results in descending order add the DESC keyword after the field in the ORDER BY clause.

3. Sorting by Multiple Fields

We can sort the query results by the multiple fields by the listing in the ORDER BY clause separated by the commas.

4. Using Entity Relationships for Sorting

Entity relationships such as the one-to-many or many-to-one associations and it can be leveraged for the sorting. It can be performed based on the attributes of the related entities.

Sorting the employees by the name of their department or by date of joining where the department information or the joining date is stored in the related entities.

Step-by-Step Implementation of JPA ORDER BY Clause

We can develop the simple JPA application that can demonstrate the ORDER BY clause of the application.

Step 1 : Create the new JPA project using the Intellj Idea named jpa-order-by-clause-demo. After creating the project, the file structure looks like the below image.

Step 2 : Open the open.xml and add the below dependencies into the project.

Step 3 : Open the persistence.xml and put the below code into the project and it can configure the database of the project.

Step 4 : Create the table in MySQL database using the below SQL query:

Step 5 : Create the new Entity Java class named as the Employee .

Go to src > main > java > Employee and put the below code.

Step 6 : Create the new Java main class named as the Main .

Go to src > main > java > Main and put the below code.

Step 7 : Once the project is completed, run the application. It will then show the Employee name and salary by the descending order as output. Refer the below output image for the better understanding of the concept.

By the following the above steps of the article, developers can gain the soild understanding of the how to effectively use the ORDER BY clause in their JPA applications.

Please Login to comment...

Similar reads.

• Advance Java

Improve your Coding Skills with Practice

What kind of Experience do you want to share?