demo-PageHelper实现分页增删改查

程序员文章站 2024-02-03 22:05:16

...

先上效果图

1. 工程搭建

技术选型：springboot+通用mapper+thymeleaf+amazeUI（还需要lombok插件）
平台工具：idea2019.3+maven+MySQL5.5+Navicat

1.1数据库设计

demo-PageHelper实现分页增删改查
包括三个属性：id,username,password，其中id设置为主键并自增

1.2依赖坐标

在pom.xml中添加

<dependencies>
        <!-- 分页插件 -->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.2.10</version>
        </dependency>
        <!--mysql-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.46</version>
        </dependency>
        <!--mybatis 通用mapper-->
        <dependency>
            <groupId>tk.mybatis</groupId>
            <artifactId>mapper-spring-boot-starter</artifactId>
            <version>2.1.5</version>
        </dependency>
         <!--jdbc 包含hikariCP-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-jdbc</artifactId>
        </dependency>
        <!--thymeleaf-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-thymeleaf</artifactId>
        </dependency>
          <!--web-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
  		<!--热部署-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
        <!--属性注入bean-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-configuration-processor</artifactId>
            <optional>true</optional>
        </dependency>
        <!--lombok-->
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <!--test-->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.junit.vintage</groupId>
                    <artifactId>junit-vintage-engine</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
    </dependencies>

利用maven项目管理工具自动导入相关依赖

1.3yml相关配置

# 使用springboot自带的数据源hikariCP
spring:
  datasource:
    driver-class-name: com.mysql.jdbc.Driver
    # 其中/eshop对应数据库
    url: mysql://localhost:3306/eshop?characterEncoding=utf8
    username: root
    # yml的bug，对于纯数字类型的密码需要加引号，如果是包含字母的就不需要
    password: '052398'
  thymeleaf: #关闭缓存
    cache: false

# mybatis配置 showSql
mybatis:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
# pagehelper配置    
pagehelper:
  helperDialect: mysql
  reasonable: true # 合理化查询，例如在第一页点击上一页时不会报错
  supportMethodsArguments: true
  params: count=countSql # 需要查出总记录数

2. 测试先行

2.1实体类UserTK

package com.hp.day5pagesearchdemo.domain;

import lombok.Data;
import tk.mybatis.mapper.annotation.KeySql;

import javax.persistence.Id;
import javax.persistence.Table;

@Data//提供getter\setter\tostring方法的注解
@Table(name = "users")// 声明此对象映射到数据库的数据表'users'
public class UserTK {
    @Id//自增
    @KeySql(useGeneratedKeys = true)
    private Integer id;//int改为Integer
    private String username;
    private String password;
	//有参、无参构造可利用注解实现
    public UserTK(Integer id, String username, String password) {
        this.id = id;
        this.username = username;
        this.password = password;
    }

    public UserTK() {
    }
}

2.2通用mapper类

package com.hp.day5pagesearchdemo.mapper;


import com.hp.day5pagesearchdemo.domain.UserTK;
import tk.mybatis.mapper.common.Mapper;

public interface UserMapperXL extends Mapper<UserTK>{
    //此处不需要任何方法
}

嗯，相比jdbc确实很香，~~基本全自动~~

2.3 在启动类上添加扫描注解

package com.hp.day5pagesearchdemo;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import tk.mybatis.spring.annotation.MapperScan;

@MapperScan("com.hp.day5pagesearchdemo.mapper")
@SpringBootApplication
public class Day5pagesearchdemoApplication {

    public static void main(String[] args) {
        SpringApplication.run(Day5pagesearchdemoApplication.class, args);
    }

}

对就是@MapperScan，不加铁定报错

2.4测试类

package com.hp.day5pagesearchdemo;

import com.github.pagehelper.PageHelper;

import com.github.pagehelper.PageInfo;
import com.hp.day5pagesearchdemo.domain.UserTK;
import com.hp.day5pagesearchdemo.mapper.UserMapperXL;
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.util.List;

@SpringBootTest
class Day5pagesearchdemoApplicationTests {
	//实现通用mapper对象的实例化并自动注入
    @Autowired
    private UserMapperXL userMapperXL;

    @Test
    void contextLoads() {
        //执行分页：PageHelper.startPage方法必须放在最前
        PageHelper.startPage(1,5);
        //该方法影响了下行的查询所有数据操作，并将分页所需要的参数封装为对象
        List<UserTK> userTKS = userMapperXL.selectAll();
        System.out.println(userTKS);
        //推荐的方式：将查询结果集userTKS封装到pageInfo对象中
        PageInfo<UserTK> pageInfo = new PageInfo<>(userTKS);
        //输出pageInfo
        System.out.println(pageInfo);

    }


}

测试OK，成功从数据库拿到分页数据，可以放到Tomcat下耍耍了
demo-PageHelper实现分页增删改查
推荐使用PageInfo封装的原因，能够获取我们分页所需要的相关数据，包含了分页信息，元组信息等

PageInfo {
	pageNum = 1, pageSize = 5, size = 5, startRow = 1, endRow = 5, total = 30, pages = 6, 
	list = Page {count = true, pageNum = 1, pageSize = 5, startRow = 0, endRow = 5, total = 30, pages = 6, reasonable = true, pageSizeZero = false}
    [UserTK(id = 1, username = 张飞, password = 14989), UserTK(id = 2, username = 司马懿, password = 04648489), UserTK(id = 3, username = 马良, password = 599), UserTK(id = 4, username = 庞统, password = 7254), UserTK(id = 5, username = 赵子龙, password = 8896)], prePage = 0, nextPage = 2, isFirstPage = true, isLastPage = false, hasPreviousPage = false, hasNextPage = true, navigatePages = 8, navigateFirstPage = 1, navigateLastPage = 6, navigatepageNums = [1, 2, 3, 4, 5, 6]
}

2.5工程结构

demo-PageHelper实现分页增删改查

3. 分页查询

3.1实现思路

查询分页数据并显示
demo-PageHelper实现分页增删改查
删除一条用户数据：

前台添加前往后端URL的超链接，并传递欲删除的用户id即可
<a href="/delete/'+ userTK.id +' ">删除 </a>
后端接受用户id删除后返回PageIndex.html

userMapperXL.deleteByPrimaryKey(id);
        return "PageIndex";

3.2前台页面

PageIndex.html

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>分页展示</title>
    <!-- 一.引入amazeui的css和js-->
    <link rel="stylesheet" th:href="@{/amazeui/assets/css/amazeui.css}"/>
    <script  th:src="@{/amazeui/assets/js/jquery.min.js}"></script>
    <script  th:src="@{/amazeui/assets/js/amazeui.js}"></script>
    <script  th:src="@{/amazeui/pagination/amazeui-pagination.js}"></script>
</head>
<body>
<h1 style="text-align: center">员工信息表</h1>
<!--相关属性见 http://amazeui.shopxo.net/css/grid/ -->
<div>
    <div class="am-g">
        <div class="am-u-lg-8 am-u-lg-centered">
            <table class="am-table am-table-bordered am-table-striped  am-table-hover am-table-centered">
                <tr class="am-primary">
                    <th>编号</th>
                    <th>姓名</th>
                    <th>密码</th>
                    <th>操作</th>
                </tr>
                <tbody id="tbody">

                </tbody>

            </table>
            <a href="/insert">
                <input type="button" value="添加员工" class="am-btn am-btn-primary" id="submit">
            </a>

        </div>
    </div>
</div>
<!-- 分页,详见 http://amazeui.shopxo.net/css/pagination/  -->
<div class="am-container">
    <ul class="am-pagination am-pagination-centered">
    </ul>
</div>
</body>
<script>
    //发送异步请求，访问controller，返回分页数据
    //封装为函数，方便执行分页模块递归调用
    showUsers(1);
    function showUsers(pageNum) {
        $.post("/userTKPage",{"pageNum":pageNum},function (data) {
            console.log(data)
            //字符串拼接HTML标签
            $("#tbody").html("");//清空tbody数据
            $.each(data.list, function (PageIndex, userTK) {
                //console.log(PageIndex + ":" + userTK.id + "---" + userTK.username);
                var tr = '<tr><td>' + userTK.id + '</td>' +
                    '<td>' + userTK.username + '</td>' +
                    '<td>' + userTK.password + '</td>' +
                    '<td>' + '<a href="/delete/'+ userTK.id +' ">删除 | </a><a href="/update/'+ userTK.id +'">修改</a></td>' +
                    '</tr>';
                //将tr放到tbody中
                $('#tbody').append(tr);

            });
            //5.创建分页，生成分页的ul-li
            var pagination = new Pagination({
                //json格式
                wrap: $('.am-pagination'),//存放分页内容
                count: parseInt(data.pages),//总页数
                current: parseInt(data.pageNum),//当前页
                prevText: '上一页', // prev 按钮的文本内容
                nextText: '下一页', // next 按钮的文本内容
                callback: function (current) { // 每一个页数按钮的回调事件
                    console.log(current);
                    showUsers(current)
                }
            });
        })
    }
</script>
</html>

3.3后端控制

PageController.java

package com.hp.day5pagesearchdemo.controller;

import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.hp.day5pagesearchdemo.domain.UserTK;
import com.hp.day5pagesearchdemo.mapper.UserMapperXL;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@Controller
//@RestController
public class PageController {
    @Autowired
    private UserMapperXL userMapperXL;

    @RequestMapping("/delete/{id}")
    public String deleteById(@PathVariable Integer id){
        userMapperXL.deleteByPrimaryKey(id);
        return "PageIndex";
    }

    @RequestMapping("/userTKPage")
    @ResponseBody
    //给参数设置默认值
    public PageInfo<UserTK> userTKPage(@RequestParam(defaultValue = "1") Integer pageNum,
                                       @RequestParam(defaultValue = "5") Integer pageSize){
        //1.开始分页
        PageHelper.startPage(pageNum,pageSize);
        System.out.println(pageNum);
        //2.查询所有数据
        List<UserTK> userTKS = userMapperXL.selectAll();
        //3.将数据封装到PageInfo
        return new PageInfo<UserTK>(userTKS);
    }
}

4. 更新用户信息

4.1实现思路

demo-PageHelper实现分页增删改查

首先在PageIndex.html页面点击修改，传入userTK.id到/update{id}后台，
在/update{id}后台，通过mapper对象userMapperXL将查询到userTK.id对应的userTK对象并存入域对象（model）中，并跳转到updateUser.html页面，
在该页面利用thymeleaf模版语法th:value="${userTK.id}"取出纯在域对象中的数据，修改完信息点击确认按钮后，发送post请求将id,username,password数据信息发送到/UpdateUser后台，进行更新数据库操作后返回PageIndex.html形成闭环
相关知识如下图

4.2前台页面

UpdateUser.html

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">

<head>
    <meta charset="UTF-8">
    <title>员工信息修改|增加</title>
    <!-- 一.引入amazeui的css和js-->
    <link rel="stylesheet" th:href="@{/amazeui/assets/css/amazeui.css}"/>
    <script  th:src="@{/amazeui/assets/js/jquery.min.js}"></script>
    <script  th:src="@{/amazeui/assets/js/amazeui.js}"></script>
    <script  th:src="@{/amazeui/pagination/amazeui-pagination.js}"></script>
</head>
<body>
<h1 style="text-align: center">信息修改</h1>
<!--相关属性见 http://amazeui.shopxo.net/css/grid/ -->
<div class="am-g">
    <div class="am-u-lg-5 am-u-lg-centered">
        <form class="am-u-lg-3 am-u-lg-centered" id="data-post">
            编号<input type="text" id="id" th:value="${userTK.id}"  class="am-form-field am-radius" READONLY>  <!-- 只读 -->
            名称<input type="text" id="username" th:value="${userTK.username}"  class="am-form-field am-radius">
            密码<input type="text" id="password" th:value="${userTK.password}"  class="am-form-field am-radius">
            </br>
            <input type="button" th:value="确认修改" class="am-btn am-btn-primary" id="submit">
        </form>
    </div>
</div>
</body>
<script>
    //提交事件
    $("#submit").click(function () {
        $.post("/updateUser",{"id":$("#id").val(),
            "username":$("#username").val(),
            "password":$("#password").val(),},function (data) {
            window.location.href="/PageIndex"
        })
    })

</script>
</html>

4.3后端控制

@RequestMapping("/update/{id}")
    public String JumpToAdd(@PathVariable Integer id,Model model){
        UserTK userTK = userMapperXL.selectByPrimaryKey(id);
        model.addAttribute("userTK",userTK);
        return "UpdateUser";
    }
//异步请求加载数据
@RequestMapping("/updateUser")
    //@ResponseBody
    public String addUser(@RequestParam(value ="id") Integer id,
                          @RequestParam String username,
                          @RequestParam String password){
        System.out.println("处理开始");
        userMapperXL.updateByPrimaryKey( new UserTK( id,username,password ) );
        return "PageIndex";
    }

5. 添加用户信息

5.1实现思路

与更新操作一致，省去通过userTK.id查询数据的过程

首先在PageIndex.html页面点击添加用户，
经/insert后台跳转到InsertUser.html页面，
在该页面添加完信息点击确认按钮后，发送post请求将username,password（此处id为null，在数据库中会自增）数据信息发送到/insertUser后台，进行读入数据库操作后返回PageIndex.html形成闭环

5.2前台页面

PageInsert.html

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">

<head>
    <meta charset="UTF-8">
    <title>员工信息修改|增加</title>
    <!-- 一.引入amazeui的css和js-->
    <link rel="stylesheet" th:href="@{/amazeui/assets/css/amazeui.css}"/>
    <script  th:src="@{/amazeui/assets/js/jquery.min.js}"></script>
    <script  th:src="@{/amazeui/assets/js/amazeui.js}"></script>
    <script  th:src="@{/amazeui/pagination/amazeui-pagination.js}"></script>
</head>
<body>
<h1 style="text-align: center">添加员工</h1>
<!--相关属性见 http://amazeui.shopxo.net/css/grid/ -->
<div class="am-g">
    <div class="am-u-lg-5 am-u-lg-centered">
        <form class="am-u-lg-3 am-u-lg-centered" id="data-post">
            编号<input type="text" id="id"   class="am-form-field am-radius" READONLY>  <!-- 只读 -->
            名称<input type="text" id="username"   class="am-form-field am-radius">
            密码<input type="text" id="password"   class="am-form-field am-radius">
            </br>
            <input type="button" th:value="确认添加" class="am-btn am-btn-primary" id="submit">
        </form>
    </div>
</div>
</body>
<script>
    //提交事件
    $("#submit").click(function () {
        //发送ajax请求       
        $.post("/insertUser",{
            "username":$("#username").val(),
            "password":$("#password").val(),},function (data) {
            window.location.href="/PageIndex"
        })
    })

</script>
</html>

5.3后端控制

 @RequestMapping("/insert")
    public String JumpToInsert(){
        return "PageInsert";
    }

    @RequestMapping("/insertUser")
    public String insertUser(@RequestParam String username,
                             @RequestParam String password){
        userMapperXL.insert( new UserTK( null,username,password ) );
        return "PageIndex";
    }

demo-PageHelper实现分页增删改查

目录

1. 工程搭建

1.1数据库设计

1.2依赖坐标

1.3yml相关配置

2. 测试先行

2.1实体类UserTK

2.2通用mapper类

2.3 在启动类上添加扫描注解

2.4测试类

2.5工程结构

3. 分页查询

3.1实现思路

3.2前台页面

3.3后端控制

4. 更新用户信息

4.1实现思路

4.2前台页面

4.3后端控制

5. 添加用户信息

5.1实现思路

5.2前台页面

5.3后端控制