How to do pagination SpringBoot with Jbctemplate, MySQL

We are going to be working on a topic which is a basic need when doing any app, and it is Pagination.

let's get started creating a product table at https://mockaroo.com/

create table products ( id INT, name VARCHAR(50), code VARCHAR(50) ); insert into products (id, name, code) values (1, 'Hettinger-Goyette', '42549-680'); insert into products (id, name, code) values (2, 'Konopelski-Klein', '49527-724'); insert into products (id, name, code) values (3, 'Smitham, Kuhlman and Balistreri', '53238-003'); insert into products (id, name, code) values (4, 'Hettinger, Weissnat and Goodwin', '0143-9916'); insert into products (id, name, code) values (5, 'Rowe Inc', '42291-898'); insert into products (id, name, code) values (6, 'Ernser-Hauck', '10544-617'); insert into products (id, name, code) values (7, 'Maggio and Sons', '68788-9087'); insert into products (id, name, code) values (8, 'Beier-Leuschke', '40046-0057'); insert into products (id, name, code) values (9, 'Padberg, Hauck and Kerluke', '55154-8261'); insert into products (id, name, code) values (10, 'Lang, Smith and Romaguera', '59115-130'); insert into products (id, name, code) values (11, 'Little LLC', '53174-000'); insert into products (id, name, code) values (12, 'Gorczany and Sons', '54868-5157'); insert into products (id, name, code) values (13, 'Cormier and Sons', '60681-1234'); insert into products (id, name, code) values (14, 'Spencer-Lebsack', '30142-254'); insert into products (id, name, code) values (15, 'Grady, Schinner and Davis', '49349-557'); insert into products (id, name, code) values (16, 'Crist, Fisher and Walsh', '25373-101'); insert into products (id, name, code) values (17, 'Hyatt Inc', '53270-3500'); insert into products (id, name, code) values (18, 'Kuvalis Inc', '0113-0020'); insert into products (id, name, code) values (19, 'Keebler, Kuhlman and Boyer', '0944-3026'); insert into products (id, name, code) values (20, 'Morissette-Bartell', '0517-0745');

let's create a new empty project at https://start.spring.io/ adding the next dependencies: 

*Spring Web Starter

*Spring Boot Actuator

*Spring Boot DevTools

*MySQL Driver

*JDBC API

we are going to be using  Jdbctemplate so that it is quite easy to use for getting the data from the database to make reports.

the purpose of paginating is avoiding getting back hundreds of records in a way if we do not paginate them, they will be hanging out the application, so with this method, we are going be able to get by amounts of records as we need, let's suppose we have a got 100 records to get them back, we can do this by paginating every 10 records, I have created a products table SQL for the purpose of this example which hast got 20 records, we will be paginating them every 5 items, in your project you can change those 5 items to the desired amount.

we are going to make an endpoint which gets us back an array like this:

[     {        "productName":'',        "productId":'',        "totalItems":'',        "currentPage":''        "details":[          {             "day":'',             "quantity":''          }        ]     } ]

the project artifact was called Pagination, so now let's open it with any editor text and under Pagination folder let's create the next folder structure:

Let's get started creating the model we need in where we'll store the information in the folder Models FinalProducts.java

package com.example.Pagination.JDBC.Models; import java.util.HashMap; import java.util.List; public class FinalProducts { public String productName; public String productId; public String totalPages; List<HashMap<String, String>> details; public String getProductName() { return this.productName; } public void setProductName(String productName) { this.productName = productName; } public String getProductId() { return this.productId; } public void setProductId(String productId) { this.productId = productId; } public String getTotalPages() { return this.totalPages; } public void setTotalPages(String totalPages) { this.totalPages = totalPages; } public List<HashMap<String,String>> getDetails() { return this.details; } public void setDetails(List<HashMap<String,String>> details) { this.details = details; } public FinalProducts(String productName, String productId, String totalPages, List<HashMap<String,String>> details) { this.productName = productName; this.productId = productId; this.totalPages = totalPages; this.details = details; } }

pay attention to this to the way we create an array of objects in java using a List of HasMaps like this

List<HashMap<String, String>> details;

now let's create RotationProducts.java a Data Access Object, this will be class which is going to get to the data from the database and returns the data as soon as the Controller calls it

you will see in RotationProducts.java:

Spring @Autowierd Annotation: 

The @Autowired annotation allows you to skip configurations elsewhere of what to inject and just does it for you. Assuming your package is com.mycompany.movies you have to put this tag in your XML (application context file):

<context:component-scan base-package="com.mycompany.movies" />

This tag will do an auto-scanning. Assuming each class that has to become a bean is annotated with a correct annotation like @Component (for simple bean) or @Controller (for a servlet control) or @Repository (for DAO classes) and these classes are somewhere under the package com.mycompany.movies, Spring will find all of these and create a bean for each one. This is done in 2 scans of the classes - the first time it just searches for classes that need to become a bean and maps the injections it needs to be doing, and on the second scan it injects the beans. Of course, you can define your beans in the more traditional XML file or with a @Configuration class (or any combination of the three).

The @Autowired annotation tells Spring where an injection needs to occur. If you put it on a method setMovieFinder it understands (by the prefix set + the @Autowired annotation) that a bean needs to be injected. In the second scan, Spring searches for a bean of type MovieFinder, and if it finds such bean, it injects it to this method. If it finds two such beans you will get an Exception. To avoid the Exception, you can use the @Qualifier annotation and tell it which of the two beans to inject in the following manner:

@Qualifier("redBean")

class Red implements Color {

   // Class code here

}


@Qualifier("blueBean")

class Blue implements Color {

   // Class code here

}

Or if you prefer to declare the beans in your XML, it would look something like this:

<bean id="redBean" class="com.mycompany.movies.Red"/>


<bean id="blueBean" class="com.mycompany.movies.Blue"/>

In the @Autowired declaration, you need to also add the @Qualifier to tell which of the two color beans to inject:

@Autowired

@Qualifier("redBean")

public void setColor(Color color) {

  this.color = color;

}

If you don't want to use two annotations (the @Autowired and @Qualifier) you can use @Resource to combine these two:

@Resource(name="redBean")

public void setColor(Color color) {

  this.color = color;

}

The @Resource (you can read some extra data about it in the first comment on this answer) spares you the use of two annotations and instead you only use one.

I'll just add two more comments:

1. Good practice would be to use @Inject instead of @Autowired because it is not Spring-specific and is part of the JSR-330 standard.
2. Another good practice would be to put the @Inject / @Autowired on a constructor instead of a method. If you put it on a constructor, you can validate that the injected beans are not null and fail fast when you try to start the application and avoid a NullPointerException when you need to actually use the bean.

if you want to know more about @Autowierd annotation go to this link https://www.mkyong.com/spring/spring-auto-wiring-beans-with-autowired-annotation/

Spring @Repository Annotation: 

Spring @Repository annotation is used to indicate that the class provides the mechanism for storage, retrieval, search, update and delete operation on objects.

Spring Repository annotation is a specialization of @Component annotation, so Spring Repository classes are autodetected by spring framework through classpath scanning.

Spring Repository is very close to DAO pattern where DAO classes are responsible for providing CRUD operations on database tables.

Spring NamedParameterJdbcTemplate:

Spring provides another way to insert data by named parameter. In such way, we use names instead of ?(question mark). So it is better to remember the data for the column.

Simple example of named parameter query:

insert into employee values (:id,:name,:salary)  

Spring JdbcTemplate:

Spring provides a nice abstraction on top of JDBC API using JdbcTemplate and also provides great transaction management capabilities using annotation-based approach.

we can take advantage of auto configuration feature and eliminate the need to configure beans by ourselves just setting spring-boot-starter-jdbc on the pom.xml like this

<dependency>

    <groupId>org.springframework.boot</groupId>

    <artifactId>spring-boot-starter-jdbc</artifactId>

</dependency>

to have a better understanding about JdbcTemplate go to this link https://www.javatpoint.com/spring-JdbcTemplate-tutorial

let's create RotationProducts.java

package com.example.Pagination.JDBC.Dao; import java.util.*; import com.example.Pagination.JDBC.Models.FinalProducts; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.jdbc.core.JdbcTemplate; import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate; import org.springframework.stereotype.Repository; import org.springframework.transaction.annotation.Transactional; @Transactional @Repository public class RotationProducts{   @Autowired   private NamedParameterJdbcTemplate namedParameterJdbcTemplate;   @Autowired   JdbcTemplate jdbcTemplate;     public List<FinalProducts> getRecordwithPagination(int page){       Random random = new Random();       String query="";       int limitItemsPerPage=5;       List<FinalProducts> finalProducts = new ArrayList<>();       String queryTotalItems = "select count(id) from products ";       int totalItems = jdbcTemplate.queryForObject(queryTotalItems, Integer.class);       int totalPages = (totalItems/5)-1;       query="select * from products LIMIT "+limitItemsPerPage+" OFFSET "+page*limitItemsPerPage;       List<Map<String, Object>> rows = jdbcTemplate.queryForList(query);       List<HashMap<String, String>> newList = new ArrayList<>();       String productName="";       String productId="";       String dayWeeks[] ={"Monday","Thuesday","Wenedsay","Thursday","Friday","Saturday","Sunday"};       for (Map row : rows) {           HashMap<String, String> map = new HashMap<String, String>();           productName=row.get("name").toString();           productId=row.get("code").toString();           newList.clear();           for(int a=0;a<=6;a++){               map.put("day", dayWeeks[a]);               map.put("quantity",String.valueOf(random.nextInt(100)));               newList.add(new HashMap(map));           }                   finalProducts.add(new FinalProducts(productName,productId,String.valueOf(totalPages),newList));               }       return finalProducts;   } }

Pay attention to the method getRecordwithPagination(int page) 

we have got a query select count(id) from products which count us the total amount of items

and we have got  int totalPages = totalItems/5; as you can see we are dividing

totalItems / 5, so 5 is the number of items you want to paginate per page

let's create now the Controller GetData.java as you already know controllers contain the business logic of an application. Here, the @Controller annotation is used to mark the class as the controller.

@RequestMapping is one of the most common annotations used in Spring Web applications. This annotation maps HTTP requests to handler methods of MVC and REST controllers.

let's look at the differences between @Controller and @RestController annotation

The @RestController annotation in Spring MVC is nothing but a combination of @Controller and @ResponseBody annotation. It was added into Spring to make the development of RESTful Web Services in Spring framework easier. If you are familiar with the REST web services you know that the fundamental difference between a web application and a REST API is that the response from a web application is generally view (HTML + CSS + JavaScript)  because they are intended for human viewers while REST API just returns data in form of JSON or XML because most of the REST clients are programs. This difference is also obvious in the @Controller and @RestController annotation.

The job of @Controller is to create a Map of the model object and find a view but @RestController simply return the object and object data is directly written into HTTP response as JSON or XML.

This can also be done with traditional @Controller and use @ResponseBody annotation but since this is the default behavior of RESTful Web services, Spring introduced @RestController which combined the behavior of @Controller and @ResponseBody together.

ResponseEntity represents the whole HTTP response: status code, headers, and body. Because of it, we can use it to fully configure the HTTP response.

Since we specify the response status programmatically, we can return with different status codes for different scenarios: like HttpStatus.BAD_REQUEST, HttpStatus.OK, HttpStatus.NO_CONTENT

@PathVariable is a Spring annotation which indicates that a method parameter should be bound to a URI template variable.

package com.example.Pagination.JDBC.Controllers; import com.example.Pagination.JDBC.Dao.RotationProducts; import com.example.Pagination.JDBC.Models.FinalProducts; import java.util.*; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.http.HttpStatus; import org.springframework.http.ResponseEntity; import org.springframework.stereotype.Controller; import org.springframework.web.bind.annotation.GetMapping; import org.springframework.web.bind.annotation.PathVariable; import org.springframework.web.bind.annotation.RequestMapping; import org.springframework.web.bind.annotation.RestController; @RestController @RequestMapping("get") public class GetData{    @Autowired    RotationProducts rotationProducts;    @GetMapping("products/{id}")    public ResponseEntity<List<FinalProducts>> getRotationProducts(@PathVariable("id") Integer id){        List<FinalProducts> rt = rotationProducts.getRecordwithPagination(id);        return new ResponseEntity<List<FinalProducts>>(rt,HttpStatus.OK);    } }

let's edit the application.properties

server.port=4525 spring.datasource.driver = com.mysql.jdbc.Driver spring.datasource.url = jdbc:mysql://localhost:3306/data?serverTimezone=UTC spring.datasource.username = root spring.datasource.password = 123456

Let's run it and let's open POSTMAN to test it, let's make a get to the endpoint http://localhost:4525/get/products/0 it says the totalPages:3, now change the endpoint that ends in zero by another page http://localhost:4525/get/products/1 and so on, the next post is going to be about linking this pagination with VueJS

you can get the whole project on GitHub https://github.com/juandavidmarin368/SpringBoot-with-Pagination