Springboot integrates JdbcTemplate to realize paging query

Springboot integrates JdbcTemplate to realize paging query

1, Foreword

When doing the SpringBoot back-end project, I want to adopt the back-end paging mode. The back-end paging is to process the data at the back-end first and then send it to the front-end. The front-end only needs to access the corresponding page and get the data of the corresponding page. In the writing of back-end paging, both MyBatis and JPA have ready-made back-end paging components, while JdbcTemplate does not. Therefore, take the entity class User as an example to record your learning process.

2, Development tools and environment

  • Computer operating system: Win10

  • Java version: jdk1.0 eight

  • MySQL database version: mysql-8.0.26-winxx64

  • Editor: IntelliJ IDEA 2021.2 Enterprise Edition

  • SpringBoot version: 2.6.3

  • working directory

3, SpringBoot basic configuration

1. Spring initializr settings

2,pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
	xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
	<modelVersion>4.0.0</modelVersion>
	<parent>
		<groupId>org.springframework.boot</groupId>
		<artifactId>spring-boot-starter-parent</artifactId>
		<version>2.6.3</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.example</groupId>
	<artifactId>pagingQuery</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>pagingQuery</name>
	<description>Demo project for Spring Boot</description>
	<properties>
		<java.version>1.8</java.version>
	</properties>
	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-jdbc</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-thymeleaf</artifactId>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>

		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<scope>runtime</scope>
		</dependency>
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<optional>true</optional>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
				<configuration>
					<excludes>
						<exclude>
							<groupId>org.projectlombok</groupId>
							<artifactId>lombok</artifactId>
						</exclude>
					</excludes>
				</configuration>
			</plugin>
		</plugins>
	</build>

</project>

3,application.properties

spring.datasource.username=root
spring.datasource.password=123456
spring.datasource.url=jdbc:mysql://localhost:3306/jdbcTemplate?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
  • The database name I use here is JDBC template
  • MySQL 8. The version of XX needs to set the time zone serverTimezone

4, Preparation - Database

1. Create database and User table

# Create database
CREATE DATABASE `jdbcTemplate`CHARACTER SET utf8 COLLATE utf8_general_ci; 
# Create user table
CREATE TABLE `jdbctemplate`.`user`( `id` INT(4) UNSIGNED NOT NULL, `username` VARCHAR(30) NOT NULL, `password` VARCHAR(30) NOT NULL, `email` VARCHAR(30), `gender` INT(1), `birth` DATE, PRIMARY KEY (`id`) ) ENGINE=INNODB CHARSET=utf8 COLLATE=utf8_general_ci; 

#Insert data into user table
INSERT INTO `jdbctemplate`.`user` (`id`, `username`, `password`, `email`, `gender`, `birth`) VALUES ('1', 'AAA', 'pwd01', 'test01@qq.com', '1', '1999-03-26'); 
INSERT INTO `jdbctemplate`.`user` (`id`, `username`, `password`, `email`, `gender`, `birth`) VALUES ('2', 'BBB', 'pwd02', 'test02@qq.com', '2', '2001-11-15'); 
INSERT INTO `jdbctemplate`.`user` (`id`, `username`, `password`, `email`, `gender`, `birth`) VALUES ('3', 'CCC', 'pwd03', 'test03@qq.com', '3', '2004-07-07'); 
INSERT INTO `jdbctemplate`.`user` (`id`, `username`, `password`, `email`, `gender`) VALUES ('4', 'DDD', 'pwd04', 'test04@qq.com', '0'); 
UPDATE `jdbctemplate`.`user` SET `gender` = '0' WHERE `id` = '2';
UPDATE `jdbctemplate`.`user` SET `gender` = '1' WHERE `id` = '3';
UPDATE `jdbctemplate`.`user` SET `birth` = '1994-07-16' WHERE `id` = '4';
INSERT INTO `jdbctemplate`.`user` (`id`, `username`, `password`, `email`, `gender`, `birth`) VALUES ('5', 'EEE', 'pwd05', 'test05@qq.com', '0', '2002-06-14'); 
INSERT INTO `jdbctemplate`.`user` (`id`, `username`, `password`, `email`, `gender`, `birth`) VALUES ('6', 'FFF', 'pwd06', 'test06@qq.com', '1', '2003-05-08'); 
INSERT INTO `jdbctemplate`.`user` (`id`, `username`, `password`, `email`, `gender`, `birth`) VALUES ('7', 'GGG', 'pwd07', 'test07@qq.com', '1', '1991-12-27'); 
INSERT INTO `jdbctemplate`.`user` (`id`, `username`, `password`, `email`, `gender`, `birth`) VALUES ('8', 'HHH', 'pwd08', 'test08@qq.com', '0', '2006-02-16'); 
INSERT INTO `jdbctemplate`.`user` (`id`, `username`, `password`, `email`, `gender`, `birth`) VALUES ('9', 'III', 'pwd09', 'test09@qq.com', '1', '1998-04-09'); 
INSERT INTO `jdbctemplate`.`user` (`id`, `username`, `password`, `email`, `gender`, `birth`) VALUES ('10', 'JJJ', 'pwd10', 'test10@qq.com', '0', '1997-07-24'); 
INSERT INTO `jdbctemplate`.`user` (`id`, `username`, `password`, `email`, `gender`, `birth`) VALUES ('11', 'KKK', 'pwd11', 'test11@qq.com', '0', '1995-01-28'); 
INSERT INTO `jdbctemplate`.`user` (`id`, `username`, `password`, `email`, `gender`, `birth`) VALUES ('12', 'LLL', 'pwd12', 'test12@qq.com', '1', '1995-01-28'); 
INSERT INTO `jdbctemplate`.`user` (`id`, `username`, `password`, `email`, `gender`, `birth`) VALUES ('13', 'MMM', 'pwd13', 'test13@qq.com', '0', '1995-07-21'); 
INSERT INTO `jdbctemplate`.`user` (`id`, `username`, `password`, `email`, `gender`, `birth`) VALUES ('14', 'NNN', 'pwd14', 'test14@qq.com', '1', '1994-03-25'); 
INSERT INTO `jdbctemplate`.`user` (`id`, `username`, `password`, `gender`, `birth`) VALUES ('15', 'OOO', 'pwd15', '0', '1991-12-30'); 
INSERT INTO `jdbctemplate`.`user` (`id`, `username`, `password`, `email`) VALUES ('16', 'PPP', 'pwd16', 'test16@qq.com'); 
INSERT INTO `jdbctemplate`.`user` (`id`, `username`, `password`, `email`, `gender`) VALUES ('17', 'QQQ', 'pwd17', 'test17@qq.com', '1'); 
INSERT INTO `jdbctemplate`.`user` (`id`, `username`, `password`, `email`, `gender`, `birth`) VALUES ('18', 'RRR', 'pwd18', 'test18@qq.com', '0', '2000-02-25'); 
INSERT INTO `jdbctemplate`.`user` (`id`, `username`, `password`, `email`, `birth`) VALUES ('19', 'SSS', 'pwd19', 'test19@qq.com', '1999-12-31'); 
INSERT INTO `jdbctemplate`.`user` (`id`, `username`, `password`, `email`, `gender`, `birth`) VALUES ('20', 'TTT', 'pwd20', 'test20@qq.com', '1', '2000-07-14'); 
INSERT INTO `jdbctemplate`.`user` (`id`, `username`, `password`, `email`, `gender`, `birth`) VALUES ('21', 'UUU', 'pwd21', 'test21@qq.com', '0', '1995-07-14'); 
INSERT INTO `jdbctemplate`.`user` (`id`, `username`, `password`, `email`, `gender`) VALUES ('22', 'VVV', 'pwd22', 'test22@qq.com', '1'); 
INSERT INTO `jdbctemplate`.`user` (`id`, `username`, `password`, `email`) VALUES ('23', 'WWW', 'pwd23', 'test23@qq.com'); 
INSERT INTO `jdbctemplate`.`user` (`id`, `username`, `password`, `gender`) VALUES ('24', 'XXX', 'pwd24', '0'); 
INSERT INTO `jdbctemplate`.`user` (`id`, `username`, `password`, `email`, `gender`, `birth`) VALUES ('25', 'YYY', 'pwd25', 'test25@qq.com', '1', '1981-11-06');
INSERT INTO `jdbctemplate`.`user` (`id`, `username`, `password`, `email`) VALUES ('26', 'ZZZ', 'pwd26', 'test26@qq.com');
UPDATE `jdbctemplate`.`user` SET `gender` = '0' WHERE `id` = '16';
UPDATE `jdbctemplate`.`user` SET `gender` = '1' WHERE `id` = '19'; 
UPDATE `jdbctemplate`.`user` SET `gender` = '0' WHERE `id` = '23'; 
UPDATE `jdbctemplate`.`user` SET `gender` = '1' WHERE `id` = '26';

After creation, the User table is shown in the following figure:

2. Test whether you can connect to the database

package com.example.pagingQuery;

import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;

@SpringBootTest
class PagingQueryApplicationTests {

	//DI injection data source
	@Autowired
	DataSource dataSource;

	@Test
	void contextLoads() throws SQLException {
		//Take a look at the default data source
		System.out.println(dataSource.getClass());
		//Get connection
		Connection connection = dataSource.getConnection();
		System.out.println(connection);
		//Close connection
		connection.close();
	}

}

Whether the test runs successfully. If it is successful, it indicates that the database is successfully connected.

5, Architecture preparation

1. User class

package com.example.pagingQuery.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.Date;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class User {

    private Integer id;
    private String username;
    private String password;
    private String email;
    private Integer gender;//0: female 1: Male
    private Date birth;
}

2,UserDao

package com.example.pagingQuery.dao;

import com.example.pagingQuery.pojo.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;
import org.springframework.web.bind.annotation.GetMapping;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

@Repository
public class UserDao {

    @Autowired
    JdbcTemplate jdbcTemplate;

    //Get all user information
    public List<User> getUserList() throws ParseException {
        String sql = "select * from `user`";
        List<Map<String, Object>> userList = jdbcTemplate.queryForList(sql);

        ArrayList<User> users = new ArrayList<>();
        for (int i = 0; i < userList.size(); i++) {
            int id = ((Number) userList.get(i).get("id")).intValue();
            String username = (String) userList.get(i).get("username");
            String password = (String) userList.get(i).get("password");
            String email = (String) userList.get(i).get("email");
            int gender = ((Number) userList.get(i).get("gender")).intValue();

            SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
            Date birth = dateFormat.parse((String) userList.get(i).get("birth"));

            User user = new User(id, username, password, email, gender, birth);
            users.add(user);
        }

        return users;
    }
}

3. View controller MyMvcConfig

package com.example.pagingQuery.config;

import org.springframework.context.annotation.Configuration;
import org.springframework.web.servlet.config.annotation.EnableWebMvc;
import org.springframework.web.servlet.config.annotation.ViewControllerRegistry;
import org.springframework.web.servlet.config.annotation.WebMvcConfigurer;

@Configuration
public class MyMvcConfig implements WebMvcConfigurer {

    //Add view controller

    @Override
    public void addViewControllers(ViewControllerRegistry registry) {
        registry.addViewController("/").setViewName("userlist");
    }
}

4. Static resources

Static resources use Bootstrap v3 to obtain static resource links (permanently valid):

Link: https://pan.baidu.com/s/1oq17a4uodSNYWwYNMfjIVw
Extraction code: MySQL

5,userlist.html static page

Note: the following is a static page

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
    <!-- introduce Bootstrap v3 Static resources-->
    <link rel="stylesheet" href="../static/bootstrap-3.4.1-dist/css/bootstrap.min.css" th:href="@{/bootstrap-3.4.1-dist/css/bootstrap.min.css}">
    <link rel="stylesheet" href="../static/bootstrap-3.4.1-dist/css/bootstrap.min.css.map" th:href="@{/bootstrap-3.4.1-dist/css/bootstrap.min.css.map}">

    <style>
        .table-wrapper{
            min-height: 300px;
        }

        .paging{
            width: 100%;
            height: 150px;
            position: relative;
        }

        .paging nav{
            display: block;
            margin: auto;
            position: absolute;
            left: 600px;
            top: 30px;
        }
    </style>
</head>
<body>
    <div class="panel panel-default">
        <!-- Default panel contents -->
        <div class="panel-heading">Panel heading</div>
        <div class="panel-body">
            <p><Legend of Zhen Huan forever</p>
        </div>

        <!-- Table -->
        <div class="table-wrapper">
            <table class="table table-striped">
                <thead>
                <tr>
                    <th>Serial number</th>
                    <th>user name</th>
                    <th>password</th>
                    <th>mailbox</th>
                    <th>Gender</th>
                    <th>birthday</th>
                </tr>
                </thead>
                <tbody>
                <tr>
                    <td>1</td>
                    <td>AAA</td>
                    <td>123456</td>
                    <td>test01@qq.com</td>
                    <td>male</td>
                    <td>2001-07-03</td>
                </tr>
                <tr>
                    <td>2</td>
                    <td>BBB</td>
                    <td>123456</td>
                    <td>test02@qq.com</td>
                    <td>male</td>
                    <td>2001-07-03</td>
                </tr>
                <tr>
                    <td>3</td>
                    <td>CCC</td>
                    <td>123456</td>
                    <td>test03@qq.com</td>
                    <td>male</td>
                    <td>2001-07-03</td>
                </tr>
                <tr>
                    <td>4</td>
                    <td>DDD</td>
                    <td>123456</td>
                    <td>test04@qq.com</td>
                    <td>male</td>
                    <td>2001-07-03</td>
                </tr>
                <tr>
                    <td>5</td>
                    <td>EEE</td>
                    <td>123456</td>
                    <td>test05@qq.com</td>
                    <td>male</td>
                    <td>2001-07-03</td>
                </tr>
                <tr>
                    <td>6</td>
                    <td>FFF</td>
                    <td>123456</td>
                    <td>test06@qq.com</td>
                    <td>male</td>
                    <td>2001-07-03</td>
                </tr>
                <tr>
                    <td>7</td>
                    <td>GGG</td>
                    <td>123456</td>
                    <td>test07@qq.com</td>
                    <td>male</td>
                    <td>2001-07-03</td>
                </tr>
                </tbody>
            </table>
        </div>

        <!-- paging -->
        <div class="paging">
            <nav aria-label="Page navigation">
                <ul class="pagination pagination-lg">
                    <li>
                        <a href="#" aria-label="Previous">
                            <span aria-hidden="true">&laquo;</span>
                        </a>
                    </li>
                    <li><a href="#">1</a></li>
                    <li><a href="#">2</a></li>
                    <li><a href="#">3</a></li>
                    <li><a href="#">4</a></li>
                    <li><a href="#">5</a></li>
                    <li>
                        <a href="#" aria-label="Next">
                            <span aria-hidden="true">&raquo;</span>
                        </a>
                    </li>
                </ul>
            </nav>
        </div>
    </div>

</body>
</html>

The static page effect is shown in the following figure:

6. UserController class

package com.example.pagingQuery.controller;

import com.example.pagingQuery.dao.UserDao;
import com.example.pagingQuery.pojo.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;

import java.text.ParseException;
import java.util.List;

@Controller
public class UserController {

    @Autowired
    UserDao userDao;

    @GetMapping("/")
    public String showList(Model model){
        List<User> users = userDao.getUserList();
        model.addAttribute("users",users);
        return "userlist";
    }
}

7,userlist.html dynamic page

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
    <!-- introduce Bootstrap v3 Static resources-->
    <link rel="stylesheet" href="../static/bootstrap-3.4.1-dist/css/bootstrap.min.css" th:href="@{/bootstrap-3.4.1-dist/css/bootstrap.min.css}">
    <link rel="stylesheet" href="../static/bootstrap-3.4.1-dist/css/bootstrap.min.css.map" th:href="@{/bootstrap-3.4.1-dist/css/bootstrap.min.css.map}">

    <style>
        .table-wrapper{
            min-height: 300px;
        }

        .paging{
            width: 100%;
            height: 150px;
            position: relative;
        }

        .paging nav{
            display: block;
            margin: auto;
            position: absolute;
            left: 600px;
            top: 30px;
        }
    </style>
</head>
<body>
    <div class="panel panel-default">
        <!-- Default panel contents -->
        <div class="panel-heading">Panel heading</div>
        <div class="panel-body">
            <p><Legend of Zhen Huan forever</p>
        </div>

        <!-- Table -->
        <div class="table-wrapper">
            <table class="table table-striped">
                <thead>
                <tr>
                    <th>Serial number</th>
                    <th>user name</th>
                    <th>password</th>
                    <th>mailbox</th>
                    <th>Gender</th>
                    <th>birthday</th>
                </tr>
                </thead>
                <tbody>
                <tr th:each="user:${users}">
                    <td th:text="${user.getId()}"></td>
                    <td th:text="${user.getUsername()}"></td>
                    <td th:text="${user.getPassword()}"></td>
                    <td th:text="${user.getEmail()}"></td>
                    <td th:text="${user.getGender()==0?'female':'male'}"></td>
                    <td th:text="${#dates.format(user.getBirth(),'yyyy-MM-dd')}"></td>
                </tr>
                </tbody>
            </table>
        </div>

        <!-- paging -->
        <div class="paging">
            <nav aria-label="Page navigation">
                <ul class="pagination pagination-lg">
                    <li>
                        <a href="#" aria-label="Previous">
                            <span aria-hidden="true">&laquo;</span>
                        </a>
                    </li>
                    <li><a href="#">1</a></li>
                    <li><a href="#">2</a></li>
                    <li><a href="#">3</a></li>
                    <li><a href="#">4</a></li>
                    <li><a href="#">5</a></li>
                    <li>
                        <a href="#" aria-label="Next">
                            <span aria-hidden="true">&raquo;</span>
                        </a>
                    </li>
                </ul>
            </nav>
        </div>
    </div>

</body>
</html>

Run the program to get the dynamic page effect, as shown in the figure below:

At this time, the preparation work is completed. Let's start paging

6, Research on the realization of paging function

1. Create PageList class

First, create a PageList class to represent each page

package com.example.pagingQuery.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.ArrayList;
import java.util.List;

@Data
@AllArgsConstructor
@NoArgsConstructor
public class PageList<T> {

    private int pageSize;   //Maximum data volume per page

    private int dataNumber; //Total data volume of Java class T

    private int pageNumber; //Total pages total pages = (total data volume% maximum data volume per page) = = 0? (total data volume / maximum data volume per page): ((total data volume / maximum data volume per page) + 1)

    private int currentPage; //Current page

    private List<T> dataList = new ArrayList<T>(); //All data of the current page

    public PageList(int currentPage,int pageSize,int dataNumber){
        this.currentPage = currentPage;
        this.pageSize = pageSize;
        pageNumber = (dataNumber%pageSize==0?(dataNumber/pageSize):(dataNumber/pageSize+1));
    }

}
  • currentPage: the current page refers to the target page we want to display when the user clicks a page
  • The main purpose of dataNumber and pageSize is to calculate the total number of pages
  • dataList is used to store the data list we want to present to users

2. UserDao paging method

Add a paging method in UserDao to return the data list according to the single page data size transmitted from the front page and the current page

//The paging function is realized to obtain paging data
//This function is called when the front page clicks the pager
public PageList<User> getUserListByPage(int currentPage,int pageSize){
    //Set the maximum amount of data for the current page and each page
    //Here I set the maximum amount of data per page to 7
    PageList<User> userPageList = new PageList<>(currentPage, pageSize);

    //Get all user data. It is easy to know that the total amount of data is 26
    userPageList.setDataNumber(jdbcTemplate.queryForObject("SELECT count(id) FROM `user`",Integer.class));

    //Determine the display data list of the current page according to the situation of the current page
    if (userPageList.getCurrentPage()==userPageList.getPageNumber()){
        //The current page is the last page of the total page
        userPageList.setDataList(jdbcTemplate.query("SELECT * FROM `user` limit ?,?",new BeanPropertyRowMapper<>(User.class),(currentPage-1)*pageSize,userPageList.getDataNumber()-(currentPage-1)*pageSize-1));
    }else {
        userPageList.setDataList(jdbcTemplate.query("SELECT * FROM `user` limit ?,?",new BeanPropertyRowMapper<>(User.class),(currentPage-1)*pageSize,pageSize));
    }
    return userPageList;
}

3,userlist.html page modification

It is mainly to modify the pager and use JS for page processing

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
    <!-- introduce Bootstrap v3 Static resources-->
    <link rel="stylesheet" href="../static/bootstrap-3.4.1-dist/css/bootstrap.min.css" th:href="@{/bootstrap-3.4.1-dist/css/bootstrap.min.css}">
    <link rel="stylesheet" href="../static/bootstrap-3.4.1-dist/css/bootstrap.min.css.map" th:href="@{/bootstrap-3.4.1-dist/css/bootstrap.min.css.map}">

    <style>
        .table-wrapper{
            min-height: 300px;
        }

        .paging{
            width: 100%;
            height: 150px;
            position: relative;
        }

        .paging nav{
            display: block;
            margin: auto;
            position: absolute;
            left: 600px;
            top: 30px;
        }
    </style>
</head>
<body>
    <div class="panel panel-default">
        <!-- Default panel contents -->
        <div class="panel-heading">Panel heading</div>
        <div class="panel-body">
            <p><Legend of Zhen Huan forever</p>
        </div>

        <!-- Table -->
        <div class="table-wrapper">
            <table class="table table-striped">
                <thead>
                <tr>
                    <th>Serial number</th>
                    <th>user name</th>
                    <th>password</th>
                    <th>mailbox</th>
                    <th>Gender</th>
                    <th>birthday</th>
                </tr>
                </thead>
                <tbody>
                <tr th:each="userByPage:${usersByPage}">
                    <td th:text="${userByPage.getId()}"></td>
                    <td th:text="${userByPage.getUsername()}"></td>
                    <td th:text="${userByPage.getPassword()}"></td>
                    <td th:text="${userByPage.getEmail()}"></td>
                    <td th:text="${userByPage.getGender()==0?'female':'male'}"></td>
                    <td th:text="${#dates.format(userByPage.getBirth(),'yyyy-MM-dd')}"></td>
                </tr>
                </tbody>
            </table>
        </div>

        <!-- paging -->
        <div class="paging">
            <!-- Customize a container for storing pageNumber Data and let this container display:none;-->
            <div id="pageNumber" th:text="${pageNumber}" style="display: none"></div>
            <nav aria-label="Page navigation">
                <ul id="sorter" class="pagination pagination-lg">

                </ul>
            </nav>
        </div>
    </div>

<script>
    window.onload = function (){
        //Get pageNumber
        let pageNumber = document.getElementById("pageNumber");
        let number = parseInt(pageNumber.innerText);

        //Get ul object
        let sorter = document.getElementById("sorter");
        sorter.innerHTML += '<li><a href="#" aria-label="Previous"><span aria-hidden="true">&laquo;</span></a></li>';
        for (let i = 0; i < number; i++) {
            sorter.innerHTML += '<li><a href="' + (i+1)+ '">' + (i+1) + '</a></li>';
        }
        sorter.innerHTML += '<li><a href="#" aria-label="Previous"><span aria-hidden="true">&raquo;</span></a></li>';
    }
</script>
</body>
</html>

4. UserController modification

package com.example.pagingQuery.controller;

import com.example.pagingQuery.dao.UserDao;
import com.example.pagingQuery.pojo.PageList;
import com.example.pagingQuery.pojo.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;

import java.text.ParseException;
import java.util.List;

@Controller
public class UserController {

    @Autowired
    UserDao userDao;

    @GetMapping("/")
    public String showList(Model model){
        List<User> users = userDao.getUserList();
        model.addAttribute("users",users);
        return "userlist";
    }

    @GetMapping("/user/{pageSize}/{currentPage}")
    public PageList<User> UserPageList(@PathVariable("pageSize") int pageSize,@PathVariable("currentPage") int currentPage){
        return userDao.getUserListByPage(currentPage,pageSize);
    }

    @GetMapping({"/user/listByPage/{currentPage}","localhost:8080/user/listByPage/{currentPage}"})
    public String showListByPage(@PathVariable("currentPage") int currentPage,Model model){
        PageList<User> userPageList = UserPageList(7, currentPage);

        //Give it a different name and distinguish it from showList
        List<User> usersByPage = userPageList.getDataList();
        model.addAttribute("usersByPage",usersByPage);

        //Get total pages
        int pageNumber = userPageList.getPageNumber();
        model.addAttribute("pageNumber",pageNumber);

        return "userlist";
    }

}

5. Display renderings

Effect picture 1

Effect drawing 2

Effect figure 3

Effect figure 4

Keywords: Java IntelliJ IDEA Spring Boot

Added by unerd.co.uk on Fri, 28 Jan 2022 18:37:43 +0200

Popular Keywords

©2024 Programming VIP