Java Spring Boot - JPA - Hibernate - H2 - Paging with Filtering and Sorting

what's up cosforge here today we'll learn how to implement paging sorting and filtering in java springboot application and to achieve this we will use criteria api on the screen you can see the project setup we are using spring initializer and we will use following dependencies h2 database as our in-memory database engine where we'll store our data spring web for starting our application and exposing rest endpoint and of course spring data jpa so we'll use the hibernate to implement our database access layer later on we'll also add swagger ui so we'll be able to test our rest endpoints from the web browser okay so we can generate the project and open it in the id it is already imported so in the first place we want to go to the main directory sources main resources and we want to open application properties over here i will paste some configuration and this is basically the setup of the jpa and the database if you want to know what's going on over here in details you can check out my video which is dedicated to the h2 database setup to implement paging sorting and filtering we'll need entity to work with so we'll navigate to the java our main package and inside we want to create a new package and we'll call it model inside we'll create our new class i want to select new java class and we'll call it employee first of all we want to mark it with the entity annotation and also we will specify the table name so we want to use the table annotation and we will use name attribute so now we can pass the name of the table and it will be the same as the class name each of our employee will have the primary key so we want to use the id annotation together with the generated value annotation in the generated value annotation we'll specify the strategy and we'll set it to auto we of course want to put those on the private long id property so it will store the primary key our entity will have two more properties and the first one will be private string first name and the second one will be private string last name we will also need default constructor for the hibernate so let's generate one and here we'll select none and one more constructor for our properties and we'll need it for the first name and last name we don't have to do it for the id since it's auto generated value we'll also generate the getters and setters so it will be possible to serialize and deserialize our objects so we want to go over here and we want to select getters and setters and we want to do it for each of our properties but we'll remove the set id because it will be auto generated and we don't have to do it manually so our entity is ready now we can move on and create the repository for it so we want to right click on our main package select package and we'll call it repository inside we want to create a new interface so we select new java class we change it to the interface and we'll call it employee repository here in the first place we want to mark our interface with the repository notation and also we want to make it extend the crude repository so we say extends root repository and it is generic type so we have to provide the entity for which we want to create the repository so in our case it is employee and also we have to provide the type of the primary key for this entity and it will be long we will use this repository for adding employees to the database and for paging filtering and sorting will create another repository so we want to create a new class over here and it will be called employee criteria repository we'll start with marking it as the repository bin so i'm going to go over here and say repository in the second place we want to inject entity manager but will not use the outlier approach we'll use the constructor approach so we want to say private final entitymanager with the same name also across our methods in the criteria repository we'll use something that is called criteria builder so we'll create a field for it it will be private final criteria builder with the same name and now we want to initialize our fields in the constructor so you go over here we want to generate the constructor and we'll generate it only for the entity manager like this and this is because entity manager will be injected and we'll use it to initialize the criteria builder so over here we want to say this criteria builder and now we want to assign to it entity manager get criteria builder like this now we will create a method which will return the page of employees so we want to go under the constructor and we want to say public and as i said we will return the page of the employee so this is the type and we have to import the page like this and the name will be find all with filters because we also want to filter our results this method will accept two object arguments the first one will be employee page which will store information about the page like the page size or the page number and also it will accept the employee search criteria argument which will store the filters let's create them first one will be employee page so we want to go to model right click it select new java class and we'll call it employee page our employee page will consist of four fields and the first one will be private into page number and by default it will be set to zero so it will return first page the second one will be the private int page size and by default it will be set to 10. next field on our list is private and it is the type of sort dot direction and the name will be sort direction and by default it will be set to ascending order so we want to say sort dot direction dot ascending and the last one will be the default sort field so we want to go over here we want to say private string sort by it's the name of the field and by default we want to sort using the last name property so we'll use the values from the last name column or field to sort our results we will also need getters and setters for those fields so we'll go over here and we'll select getters and setters and we want it for each of our fields employee page is ready so now we can move on and create another class and this one will be called employee search criteria over here we will have two fields first one will be private string first name and the second one will be private string last name so user will be able to sort results using the first names or the last names we will also need getters and setters so let's generate them we go over here and we want it for the first name and for the last name this one is also ready so we can go back to the employee criteria repository now we are ready to define the parameters for our method so it was employee page which is over here and we'll use the same name as the type and also employee search criteria over here and we also use the same name as the type and we'll move it to the another line like this now the interesting part begins and we want to start with creating criteria query we go to the body of our method and you want to say criteria query and this generic type because we have to specify for which entity we want to create the query and we want to do it for the employee and we'll call this criteria query and we want to assign to it criteria builder dot create query and now we have to specify the class for which we want to create this query and since we are doing it for the employee entity it will be employee.class so we have defined that the result of our criteria query will be the employee type now we have to specify from which entity we want to retrieve this result in our case it will be the same type so we want to go over here and we want to say root and it is also the generic type and we'll say employee here and we have to import it like this and we'll call it employee root and we'll assign here criteria query dot and again it will be employee.class so we have the query and we have the query root so we know what will be the type returned by the query and from which entity we want to retrieve this result now we want to define our predicates and in our case predicates will filter our results we want to go over here and we'll create our predicate variable with the same name and we want to assign to this variable the result of the method which we will call get predicate and this method will be called with the following arguments it will be employee search criteria and also employee root and now let's generate this method so we'll go over here and we'll hit enter and let's move this to the another line now we'll implement our get predicate method and we'll start with creating the list of predicates so we'll say list predicate and we'll call it predicate but we have to import the list in the first place now it will be predicates and we want to assign memory i will say new array list our filter values are optional so user can pass them or not and because of that we have to first check if the values are not null so we have to go here and we'll say if and we'll use the static method from the object and it will be non-null and here we'll pass the search employee search criteria dot get first name and if it's true we want to add the predicate to our predicates list so we want to say predicates dot add and now we'll use the criteria builder and when we are searching for the particular string in the database we want to use the like and here we want to use the employee root get first name because we are filtering using the first name and now as the second argument of the like method we want to pass the expression and we want to make it work the way that the user don't have to pass the full first name he can pass for example the first letter on the last few words so we will precede it with the sent and we will concatenate it with the value provided by the user so we'll say employee search criteria over here get first name and we'll at the end append the preset again like this so let's move it to another line maybe like this and here we have to add the semicolon and we want to do the same for the last name so we'll copy this if statement here and we'll paste it and now we of course want to change the get first name to the get last name and also over here we want to get last name instead of the first name and the same goes here so we'll copy it and paste over here our predicates looks good so now we have to return them we go under the if statement and we say return and now we can use the criteria builder and connect our predicates with the end operator like this by calling the end method and now we want to pass our predicates and you want to say to array new predicate and we can pass the size of 0 here and it will be good so basically we are merging our predicates into one final predicate using the end operator and then we are returning it over here so our predicate is ready now we have to use it so we go over here and we'll say criteria query dot where and we'll pass our predicate so right now our data can be filtered using the first name or the last name now we want to sort our results so we will create another helper method and we'll call it set order and it will be called with the three different arguments and it will be employee page the second one will be the criteria query and the third one will be our employee root so let's create this method we'll go over here and we'll say create method and to stick to the order we will move it to the bottom and we'll also take care of the parameters over here this one is quite easy we'll start with the if statement and inside the if statement will check if the employee page dot get sort direction is equal to the sort dot direction dot ascending and if it's true we want to order our results in the ascending order and to do it we want to say criteria query dot order by and over here we use the criteria builder dot ascending or the ascending order and we'll use our employee root dot get and you want to get employee page dot get sort by like this so basically what's going on over here we are retrieving from the employee page the information about the sort by field and we can sort our results using the first name or the last name by default it is the last name and also we are using the criteria builder to specify the order so it can be ascending order or the standing order and in this case it is ascending order we can do the same for the descending and if the direction will be set to descending we want to copy this say else and we'll paste name over here but we'll change the ascending to descending like this so this is basically it our data will be sorted right now so we can go back to our main method we have the filtering we have the sorting so now we can finally create our query to make a request to the database so we'll go over here and we'll create something that is called typed query and it is the generic type so we have to specify the entity for which is this query and in our case it is employee and we'll call it typed query and now we will assign to it the entitymanager.createquery and we'll pass our criteria query as the argument here we have created typed query basing on the criteria query and now we have to set the first result that will be returned so we can use the typed query and we can call the set first result method and we can do it by saying employee page dot get page number and we want to multiply it by employee page dot cat page nice like this we also should define how many entries we want to return and to do this we'll use the typed query set max results method and in our case the max result is equal to the size of the page so you want to say employee page get page size like this next one on our list is the page able object which you should know from my previous video about the paging and sorting where we are using the paging and sorting repository so let's create one we will go over here and it will say page able from the spring framework and it will have the same name and we want to assign to it the result of the get pageable method which we will create and we'll call it with the following argument and it will be the employee page so we want to generate it we'll go over here and hit generate and we'll move it at the bottom over here in the first place we want to create the sort object so we want to say sort with the same name and we assign to it the result of the static method from the sort and the method is called by over here we have to pass few arguments and it will be the employee page get sort direction and the second argument is the employee page dot get sort by since we have the sort object now we can go here and say return and we want to return the page request and call the static method off and inside will provide the argument so it will be employee page dot get page number the next one on the list is the size so you want to say employee page dot get page size and the last one is the sort object so we want to pass the sort like this getpageable method is ready so we can go back to our find all with filters method now we will create the count query which will allow us to retrieve the total amount of entries with the applied predicates in the first place we will create a variable which will store the employees count so say long employees count and we want to assign to it the result of the get employees count method and this method will accept one argument and it will be predicate and we are using the same predicates because we want to get the total count of the employees with the applied filters and we don't want to limit it like we did it over here for the entries let's generate it and it looks good we'll move it to the bottom like this in the body first of all we want to create a new query so it will be the criteria query and this time it will be the type of long and we'll call it count query and like before we want to use the criteria builder and you want to say create query long dot class so this time we have specified that the result of the query will be the long type next step is to create the root so we'll say root and it is generic so it will be the employee type this time and we'll call it count root and we want to assign to it the count query dot from employee dot class and you can see the difference between the regular query and the count query because in this case we are declaring that the result is the long number and we are still working with the employee entity and now we can use our account query over here to call select and thanks to the criteriabuilder.count method we can say that we want to count the number of employees using the count route and we can also apply the predicates using the where method and bypassing the predicate like this we can finish our method by using return statement and now we will use dot entitymanager.com query and we will pass our count query as the argument and will retrieve the result like this it should work we can go back to our main method here and the last thing we have to do over here is to return new page implementation objects so you want to say return new page implementation and it has following arguments the first one will be the typed query which we have created over here so we want to say typed query and we want to call the get result list method next argument is the page a bill which we have created and the last argument is the employees count so we want to say employees count over here our criteria repository is ready so now let's create a service we want to create a new package first so we go over here and we'll call it service now inside we want to create a new class and it will be employee service we want to mark it with the service annotation so screen will create a bin for this and also we want to inject following bins so we want to say private final and the first one will be employee repository interface which we have created and the second one will be private final employee criteria repository now we can generate the constructor and we want it for the both of the repositories and we hit enter and we'll move it to the another line now we can create our methods one for retrieving the employees and one for adding the employees so we'll go over here and the first one would be public page and it is the generic type so we say employee over here and we'll import the page and now we'll call it get employees the second one let's create it it will be public employee and it will be called add employee get employees method will have following parameters so the first one will be employee page with the same name and the second one will be employee search criteria with the same name so over here we are passing the page information and also the information about the filters and all we have to do in the body of our method is to say return and we will use our employee criteria repository and we'll call the find all with filters method which we have implemented and it can be called with the following arguments the first one is the employee object and the second one is the employee search criteria the second method is also quite easy it will accept the employee object like this and also we have to say return but this time we will use the employee repository and we'll say save and we'll pass our employee over here our service is good so we can take care of the controller let's create the package in the first place we'll call it controller and we'll also create a class inside and it will be employee controller we want to mark it with the rest controller annotation in the first place and also we'll specify the global mapping for our methods so we want to say request mapping and here we will provide the path and it will be employees like this we will inject employee service so we want to say private final employee service with the same name and we'll generate the constructor to initialize it we will have two endpoints let's create one for retrieving employees so it will be get mapping and now the methods so it will be public response entity and we will be returning here the page or the employee type and we have to import the page and the name of the method will be get employees we will have two arguments over here the first one will be employee page with the same name and the second one will be employee search criteria also with the same name it's not fitting the screen always zoom out inside our method we want to say return new response entity and we want to use our employee service and we want to call the get employees method and it accepts employees page so we want to say employee page and also the employee search criteria and the second argument of the response entity constructor is the http status so we want to say okay and now the second endpoint for adding the employees so we want to use the post mapping annotation and you want to put it on the public response entity employee method and it will be called add employee our add employee method will accept request body so we want to use the request body annotation over here and this request body will be the type of employee with the same name so we'll receive the employee object from them from the body and all you have to do is to say return new response entity and inside we want to access the employee service and call the ad employee method and we can simply pass the employee object from the request body we also want to specify the status and it will be okay i think we are done with the controller so we can navigate to the palm.xml over here and in the dependencies we will add the swagger ui it looks like a good spot so we'll say dependency over here and we want to add spring fox boot starter from the i o spring fox version 3. it is also worth to click this button over here to reload the maven dependencies i think we can finally start the application so we go to the maven plugins print boot and spring would run and after a few seconds our application should be up and running on the port 8080 it looks good so we can open the swagger ui okay here we are in the web browser we are on the local host port 8080 and we are on the swagger ui slash index.html path we can find our employee controller over here and our two endpoints so let's start with adding some employees so we will be able to work with something you should already know how it works because we did it in the previous video so let's say try it out and we'll remove the id because we don't need it it will be ignored anyway and let's add the employee that will be called john doe and it looks like the art endpoint is working because we got the id1 with the john doe in the response body let's add him two more times and now we'll change it and we'll add term smith and we'll add him three times also okay looks good id is six so we should have six entries let's open the employee's endpoint over here let's try to change the page size to five we have to click trade out first and let's say execute it looks good we have how many entries one two three four five looks good and let's check the metadata we can see the page size is set to five and the total elements is six that's correct because we have added six entries and the total pages is two that's correct so let's go back to the top let's check out if our filtering is working so let's say we want to filter only the dough guys and we'll say execute and we want to scroll down and we have three entries that's good and the total elements is also free and is also correct let's make few more tests so now let's remove do and let's put o e and let's hit execute and we should also get free entries and is also correct so now our like with present worked and the last test let's check out if our sorting is working so we'll sort by id and we hit execute and we have one two three so it is correct okay guys so this is all for today thank you for watching we have learned how to implement paging sorting and filtering using the spring data and criteria uppy it was quite a long video but i hope it will be helpful for some of you and yeah thank you and remember about subscribing to the channel and liking the videos see you next time bye

Channel: CodeForgeYT

Views: 15,477

Keywords: programming, guide, tutorial, java, spring, boot, paging, pagination, sorting, sort, filter, filtering, example, pagingandsortingrepository, with, search, direction, and, jpa, mykong, project, h2, config, database, db, api, swagger, ui, documentation, microservice, web, microservices, entity, data, mapping, repository, persist, save, post, get, annotation, find, rest, developing, girls, starter, maven, dependency, persistance, manager, criteria, builder, query, typed, criteriaquery, criteriabuilder, entitymanager, responseentity, create, table, from

Id: wJBAFZv_KN0

Length: 34min 56sec (2096 seconds)

Published: Mon Aug 24 2020