网页分页以及MyBatis的动态拼接sql语句的实例教程

PaperMapper.xml
//where标签

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="demo.mapper.PaperMapper">
    <select id="findAll" resultType="Paper">
        SELECT
            DISTINCT p.*, (SELECT count(*) FROM t_comment WHERE p_id=c.p_id) AS cnt
        FROM
            t_paper AS p LEFT JOIN t_comment AS c
        ON
            p.id=c.p_id
        <where> 

            <if test="title !='null' and title != ''">
                AND title like '%${title}%'
            </if>
            <if test="type !='null' and type != ''">
                AND paper_type=#{type}
            </if>
        </where>
        ORDER BY p.publish_date DESC
        LIMIT #{pageIndex}, #{pageSize}
    </select>
</mapper>

application.yml

server:
  port: 8080
spring:
  # 配置数据源信息
  datasource:
    # 配置数据源类型
    type: com.zaxxer.hikari.HikariDataSource
    # 配置连接数据库信息
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/test01?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true
    username: root
    password: kkkkkk
mybatis-plus:
  type-aliases-package: demo.entity
  configuration:
    # 配置MyBatis日志，执行sql的时候，将sql打印到控制台
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  global-config:
    db-config:
      table-prefix: t_
      id-type: assign_id
  mapper-locations: classpath:mappers/*.xml

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.7.9</version>
		<relativePath/> <!-- lookup parent from repository -->
	</parent>
	<groupId>com.example</groupId>
	<artifactId>web-server</artifactId>
	<version>0.0.1-SNAPSHOT</version>
	<name>web-server</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-web</artifactId>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>

		<!-- mybatis-plus -->
		<dependency>
			<groupId>com.baomidou</groupId>
			<artifactId>mybatis-plus-boot-starter</artifactId>
			<version>3.5.1</version>
		</dependency>
		<!-- lombok -->
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId> <optional>true</optional>
		</dependency>
		<!-- mysql-connector -->
		<dependency>
			<groupId>mysql</groupId>
			<artifactId>mysql-connector-java</artifactId>
			<version>8.0.32</version>
		</dependency>
	</dependencies>

	<build>
		<plugins>
			<plugin>
				<groupId>org.springframework.boot</groupId>
				<artifactId>spring-boot-maven-plugin</artifactId>
			</plugin>
		</plugins>
	</build>

</project>

controller层

package demo.controller;

import demo.entity.HttpResult;
import demo.entity.Paper;
import demo.service.IPaperService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

@RestController
@RequestMapping("/paper")
@CrossOrigin(origins = "*")
public class PaperController {

    @Autowired
    private IPaperService paperService;

    @PostMapping("/save")
    public HttpResult savePaper(@RequestBody Paper paper){
        return paperService.savePaper(paper);
    }

    @PostMapping("/modify")
    public HttpResult modifyPaper(@RequestBody Paper paper){
        return paperService.modifyPaper(paper);
    }

    @GetMapping("/remove")
    public HttpResult remove(Long id){
        return paperService.remove(id);
    }

    @GetMapping("/find_all")
    public HttpResult findAll(String title, String type, Integer pageIndex, Integer pageSize){
        return paperService.findAll(title, type, pageIndex, pageSize);
    }

    @GetMapping("/find_by_id")
    public HttpResult findById(Long id){
        return paperService.findById(id);
    }
}

实体层1

package demo.entity;

import com.baomidou.mybatisplus.annotation.TableField;
import com.fasterxml.jackson.databind.annotation.JsonSerialize;
import com.fasterxml.jackson.databind.ser.std.ToStringSerializer;
import lombok.Data;

@Data
public class Paper {
    @JsonSerialize(using = ToStringSerializer.class)
    private Long id;
    private String title;
    private String author;
    private String paperType;
    private String publishDate;
    private Integer state;
    @TableField(exist = false)
    private Integer cnt;
}

实体层2

package demo.entity;

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

@Data
@AllArgsConstructor
@NoArgsConstructor
public class HttpResult {
    private int code;
    private String msg;
    private Object data;
    private int total;
}

mapper层

package demo.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import demo.entity.Paper;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface PaperMapper extends BaseMapper<Paper> {

    List<Paper> findAll(String title, String type, Integer pageIndex, Integer pageSize);
}

service接口

package demo.service;

import demo.entity.HttpResult;
import demo.entity.Paper;

public interface IPaperService {

    HttpResult savePaper(Paper paper);

    HttpResult modifyPaper(Paper paper);

    HttpResult remove(Long id);

    HttpResult findAll(String title, String type, Integer pageIndex, Integer pageSize);

    HttpResult findById(Long id);
}

service实现类

package demo.service.impl;

import demo.entity.HttpResult;
import demo.entity.Paper;
import demo.mapper.PaperMapper;
import demo.service.IPaperService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

@Service
public class PaperServiceImpl implements IPaperService {

    @Autowired
    private PaperMapper paperMapper;

    @Override
    public HttpResult savePaper(Paper paper) {
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        String dateStr = format.format(new Date());
        paper.setPublishDate(dateStr);
        int cnt = paperMapper.insert(paper);
        HttpResult result = null;
        if(cnt > 0){
            result = new HttpResult(200, "添加论文成功", null, 0);
        } else {
            result = new HttpResult(500, "添加论文失败", null, 0);
        }
        return result;
    }

    @Override
    public HttpResult modifyPaper(Paper paper) {
        int cnt = paperMapper.updateById(paper);
        HttpResult result = null;
        if(cnt > 0){
            result = new HttpResult(200, "修改论文成功", null, 0);
        } else {
            result = new HttpResult(500, "修改论文失败", null, 0);
        }
        return result;
    }

    @Override
    public HttpResult remove(Long id) {
        int cnt = paperMapper.deleteById(id);
        HttpResult result = null;
        if(cnt > 0){
            result = new HttpResult(200, "删除论文成功", null, 0);
        } else {
            result = new HttpResult(500, "删除论文失败", null, 0);
        }
        return result;
    }

    @Override
    public HttpResult findAll(String title, String type, Integer pageIndex, Integer pageSize) {
        List<Paper> all = paperMapper.findAll(title, type, (pageIndex - 1) * pageSize, pageSize);
        HttpResult result = null;
        if(all != null && all.size() > 0){
            result = new HttpResult(200, "查询成功", all, Math.toIntExact(paperMapper.selectCount(null)));
        } else {
            result = new HttpResult(500, "没有更多数据", null, 0);
        }
        return result;
    }

    @Override
    public HttpResult findById(Long id) {
        Paper paper = paperMapper.selectById(id);
        HttpResult result = null;
        if(paper != null){
            result = new HttpResult(200, "查询成功", paper, 0);
        } else {
            result = new HttpResult(500, "没有更多数据", null, 0);
        }
        return result;
    }
}

启动类

package demo;

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

@SpringBootApplication
@MapperScan("demo.mapper")
public class Demo {
    public static void main(String[] args) {
        SpringApplication.run(Demo.class);
    }
}

*********************************************************************************
前端网页
main.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>
    <iframe name="left_frame" src="left.html" scrolling="no" style="width: 150px; height: 700px;"></iframe>
    <iframe name="right_frame" src="paper_list.html" scrolling="no" style="width: 1050px; height: 700px;"></iframe>
</body>
</html>

left.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body style="padding: 0px; margin: 0px; background-color: #5bc0de">
    <div style="width: 150px; height: 700px;">
        <ul>
            <li><a href="paper_list.html" target="right_frame">论文列表</a></li>
        </ul>
    </div>
</body>
</html>

paper_list.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
    <link href="asset/bootstrap-3.3.7-dist/css/bootstrap.min.css" rel="stylesheet">
    <script src="asset/jquery-3.5.1.min.js"></script>
    <script src="asset/bootstrap-3.3.7-dist/js/bootstrap.min.js"></script>
    <script src="asset/vue.min-v2.5.16.js"></script>
    <script src="asset/axios.min.js"></script>
</head>
<body class="container" style="padding: 0px; margin: 0px; background-color: pink">
    <div id="app" style="width: 1050px; height: 700px;">
        <div class="navbar-form">
            主题：
            <input type="text" class="form-control" v-model="paperTitle">
            类型：
            <select class="form-control" v-model="paperType">
                <option value=""></option>
                <option value="应用型">应用型</option>
                <option value="学术型">学术型</option>
            </select>
            <button class="btn btn-link" @click="doSearch">查询</button>
            <button class="btn btn-link" @click="doAdd">新增</button>
        </div>
        <table class="table table-striped">
            <caption>论文列表</caption>
            <thead>
                <tr>
                    <th>编号</th>
                    <th>主题</th>
                    <th>作者</th>
                    <th>类型</th>
                    <th>状态</th>
                    <th>发布日期</th>
                    <th>评论次数</th>
                    <th>操作</th>
                </tr>
            </thead>
            <tbody>
                <tr v-for="p in papeList">
                    <td>{{p.id}}</td>
                    <td>{{p.title}}</td>
                    <td>{{p.author}}</td>
                    <td>{{p.paperType}}</td>
                    <td>{{p.state==0 ? '未发布':'已发布'}}</td>
                    <td>{{p.publishDate}}</td>
                    <td>{{p.cnt}}</td>
                    <td>
                        <button class="btn btn-link" @click="doUpdate(p.id)">修改</button>
                        <button class="btn btn-link" @click="doDelete(p.id)">删除</button>
                    </td>
                </tr>
            </tbody>
        </table>
        <ul class="pagination" v-for="p in pageNum">
            <li v-if="p == pageIndex" class="active"><a href="#" @click="doGO(p)">{{p}}</a></li>
            <li v-else="p == pageIndex"><a href="#" @click="doGO(p)">{{p}}</a></li>
        </ul>
    </div>
<script>
    new Vue({
        el: '#app',
        data: {
            paperTitle: null,
            paperType: null,
            papeList: null,
            //关于分页的参数
            pageIndex: 1, //页码
            pageSize: 5, //每页显示的条数
            pageTotle: 0, //总条数
            pageNum: 0 //共多少页
        },
        methods:{
            //请求论文列表
            requestPaperList(url){
                axios.get(url).then(res=>{
                    console.log(res.data)
                    this.papeList = res.data.data
                    this.pageTotle = res.data.total
                    this.pageNum = Math.ceil(this.pageTotle / this.pageSize)
                })
            },
            doGO(p){
                this.pageIndex = p;
                url = "http://localhost:8080/paper/find_all?pageIndex="+p+"&pageSize="+this.pageSize+"&title="+this.paperTitle+"&type="+this.paperType
                this.requestPaperList(url);
            },
            doUpdate(id){
                window.location.href = "update.html?id="+id
            },
            doDelete(id){
                axios.get("http://localhost:8080/paper/remove?id="+id).then(res=>{
                    console.log(res.data)
                    if(res.data.code == 200){
                        this.pageIndex = 1;
                        url = "http://localhost:8080/paper/find_all?pageIndex="+this.pageIndex+"&pageSize="+this.pageSize+"&title="+this.paperTitle+"&type="+this.paperType
                        this.requestPaperList(url);
                    } else {
                        alert(res.data.msg)
                    }
                });
            },
            doSearch(){
                url = "http://localhost:8080/paper/find_all?pageIndex="+1+"&pageSize="+this.pageSize+"&title="+this.paperTitle+"&type="+this.paperType
                this.requestPaperList(url);
            },
            doAdd(){
                window.parent.right_frame.location="add.html"
            }
        },
        created: function () {
            url = "http://localhost:8080/paper/find_all?pageIndex="+this.pageIndex+"&pageSize="+this.pageSize+"&title="+this.paperTitle+"&type="+this.paperType
            this.requestPaperList(url);
        }
    })
</script>
</body>
</html>

add.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
    <link href="asset/bootstrap-3.3.7-dist/css/bootstrap.min.css" rel="stylesheet">
    <script src="asset/jquery-3.5.1.min.js"></script>
    <script src="asset/bootstrap-3.3.7-dist/js/bootstrap.min.js"></script>
    <script src="asset/vue.min-v2.5.16.js"></script>
    <script src="asset/axios.min.js"></script>
</head>
<body class="container">
    <div id="app">
        <h3>发布论文</h3>
        <div class="navbar-form">
            主题：
            <input type="text" class="form-control" v-model="paperTitle">
        </div>
        <div class="navbar-form">
            作者：
            <input type="text" class="form-control" v-model="paperAuthor">
        </div>
        <div class="navbar-form">
            类型：
            <select class="form-control" v-model="paperType">
                <option value=""></option>
                <option value="应用型">应用型</option>
                <option value="学术型">学术型</option>
            </select>
        </div>
        <div class="navbar-form" >
            状态：
            <select class="form-control" v-model="paperState">
                <option value=""></option>
                <option value="0">未发布</option>
                <option value="1">已发布</option>
            </select>
        </div>
        <div class="navbar-form">
            <button class="btn btn-primary" @click="doSave">添加</button>
        </div>
    </div>
<script>
    new Vue({
        el: '#app',
        data: {
            paperTitle: null,
            paperAuthor: null,
            paperType: null,
            paperState: null
        },
        methods: {
            doSave(){
                axios.post("http://localhost:8080/paper/save", {
                    title: this.paperTitle,
                    author: this.paperAuthor,
                    paperType: this.paperType,
                    state: this.paperState
                }).then(res=>{
                    console.log(res.data)
                    if(res.data.code == 200){
                        window.parent.right_frame.location = "paper_list.html"
                    } else {
                        alert(res.data.msg);
                    }
                })
            }
        }
    })
</script>
</body>
</html>

update.html

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
    <link href="asset/bootstrap-3.3.7-dist/css/bootstrap.min.css" rel="stylesheet">
    <script src="asset/jquery-3.5.1.min.js"></script>
    <script src="asset/bootstrap-3.3.7-dist/js/bootstrap.min.js"></script>
    <script src="asset/vue.min-v2.5.16.js"></script>
    <script src="asset/axios.min.js"></script>
</head>
<body class="container">
    <div id="app">
        <h3>修改论文</h3>
        <div class="navbar-form">
            主题：
            <input type="text" class="form-control" v-model="paperTitle">
        </div>
        <div class="navbar-form">
            作者：
            <input type="text" class="form-control" v-model="paperAuthor">
        </div>
        <div class="navbar-form">
            类型：
            <select class="form-control" v-model="paperType">
                <option value=""></option>
                <option value="应用型">应用型</option>
                <option value="学术型">学术型</option>
            </select>
        </div>
        <div class="navbar-form">
            状态：
            <select class="form-control" v-model="paperState">
                <option value=""></option>
                <option value="0">未发布</option>
                <option value="1">已发布</option>
            </select>
        </div>
        <div class="navbar-form">
            <button class="btn btn-primary" @click="doSave">修改</button>
        </div>
    </div>
<script>
    new Vue({
        el: '#app',
        data: {
            id: null,
            paperTitle: null,
            paperAuthor: null,
            paperType: null,
            paperState: null
        },
        methods: {
            doSave(){
                axios.post("http://localhost:8080/paper/modify", {
                    id: this.id,
                    title: this.paperTitle,
                    author: this.paperAuthor,
                    paperType: this.paperType,
                    state: this.paperState
                }).then(res=>{
                    console.log(res.data)
                    if(res.data.code == 200){
                        window.parent.right_frame.location = "paper_list.html"
                    } else {
                        alert(res.data.msg);
                    }
                })
            }
        },
        created:function () {
            url = window.location.href;
            this.id = url.substring(url.indexOf("id=")+3);
            axios.get("http://localhost:8080/paper/find_by_id?id="+this.id).then(res=>{
                console.log(res.data);
                this.paperTitle = res.data.data.title;
                this.paperAuthor= res.data.data.author;
                this.paperType= res.data.data.paperType;
                this.paperState= res.data.data.state;
            });
        }
    })
</script>
</body>
</html>