SpringBoot-模糊查询-分页

最新推荐文章于 2024-06-27 11:53:28 发布

堃琪

最新推荐文章于 2024-06-27 11:53:28 发布

阅读量1.6k

点赞数 2

文章标签： spring boot mybatis mysql

本文链接：https://blog.csdn.net/qq_56793001/article/details/128417964

版权

SpringBoot-模糊查询-分页

编写一个Springboot项目增删改查外加一个分页和模糊查询

1.创建一个Spring Initializr项目

在这里插入图片描述

2.选择项目结构

在这里插入图片描述

3.选择自己所需jar包

我所使用的是2.7.7的版本jar包可以先选择这几个后续再添加
在这里插入图片描述

4.编写分页需要导入分页的依赖包

<!--分页依赖-->
<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper-spring-boot-starter</artifactId>
    <version>1.4.1</version>
</dependency>

5.在yml中配置信息

# 配置端口号
server:
  port: 80

# 配置mysql
spring:
  datasource:
    url: jdbc:mysql://localhost:3306/demo?serverTimezone=UTC&characterEncoding=utf-8
    username: root
    password: 123
    driver-class-name: com.mysql.cj.jdbc.Driver
  # thymeleaf  引擎视图
  thymeleaf:
    mode: HTML5     # 设置格式
    cache: false    # 关闭缓存
    prefix: classpath:/templates/   # 前缀
    suffix: .html                   # 后缀

# mybatis动态配置
mybatis:
  #实体类别名
  type-aliases-package: com.kk.pojo
  #扫描mapper配置文件
  mapper-locations: classpath:/mapper/*.xml

# 分页配置
pagehelper:
  helper-dialect: mysql   #数据库类型
  reasonable: true        # 分页参数合理化
  support-methods-arguments: true   #支持通过mapper接口参数来传递分页参数

6.编写代码

数据库表结构：

订单表

字段名	类型	长度	注释
orderid	int	8	订单ID
ordersn	varchar	255	订单编号
userId	int	8	用户ID
orderStatus	int	11	订单状态
payStatus	int	11	支付状态
shippingStatus	int	11	发货状态
orderTime	datetime	0	下单时间

订单操作表

字段名	类型	长度	注释
actionId	int	8	操作ID
ordersn	varchar	255	订单编号
actionUser	int	8	操作人
orderStatus	int	11	订单状态
payStatus	int	11	支付状态
shippingStatus	int	11	发货状态
actionNote	varchar	255	操作记录
actionTime	datetime	0	操作时间
statusDesc	varchar	255	状态描述

1.实体类：

订单信息

/*
* 订单表
* */
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Indent {

    private Integer orderid;            // 订单ID
    private String ordersn;             // 订单编号
    private Integer userId;             // 用户ID
    private Integer orderStatus;        // 订单状态
    private Integer payStatus;          // 支付状态
    private Integer shippingStatus;     // 发货状态
    @DateTimeFormat(pattern = "yyyy-MM-dd")
    private Date orderTime;             // 下单时间
    private IndentUser indentUser;

}

订单操作信息

/*
* 订单操作
* */
@Data
@NoArgsConstructor
@AllArgsConstructor
public class IndentUser {

    private Integer actionId;           // 操作ID
    private String ordersn;             // 订单编号
    private Integer actionUser;         // 操作人
    private Integer orderStatus;        // 订单状态
    private Integer payStatus;          // 支付状态
    private Integer shippingStatus;     // 发货状态
    private String actionNote;          // 操作记录
    @DateTimeFormat(pattern = "yyyy-MM-dd")
    private Date actionTime;            // 操作时间
    private String statusDesc;          // 状态描述

}

2.编写接口方法：

@Mapper
@Repository
public interface IndentMapper {

    /*
    * 查询所有订单信息并分页
    * 模糊查询
    * */
    List<Indent> findAll(@Param("ordersn") String ordersn,
                         @Param("orderStatus") Integer orderStatus,
                         @Param("payStatus") Integer payStatus,
                         @Param("orderTime") String orderTime);

    /*
    * 删除
    * */
    int delById(Integer orderid);

    /*
    * 删除订单类型
    * */
    int delById2(Integer orderid);

    /*
    * 新增
    * */
    int add(Indent indent);

    /*
    * 新增订单操作信息
    * */
    int add2(IndentUser indentUser);

    /*
    * 根据订单编号查询订单信息
    * */
    Indent findById(Integer orderid);

    /*
    * 修改
    * */
    int save(Indent indent);

    /*
     * 修改订单操作
     * */
    int save2(IndentUser indentUser);

}

3.mapper.xml配置文件 – 使用的连表查询增删改都需要操作两张表的信息

<?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="com.kk.mapper.IndentMapper">

    <resultMap id="list" type="indent">
        <result property="orderid" column="orderid"/>
        <result property="ordersn" column="ordersn"/>
        <result property="userId" column="userId"/>
        <result property="orderStatus" column="orderStatus"/>
        <result property="payStatus" column="payStatus"/>
        <result property="shippingStatus" column="shippingStatus"/>
        <result property="orderTime" column="orderTime"/>
        <association property="indentUser" javaType="indentUser">
            <result property="actionId" column="actionId"/>
            <result property="ordersn" column="uordersn"/>
            <result property="actionUser" column="actionUser"/>
            <result property="orderStatus" column="orderStatus"/>
            <result property="payStatus" column="payStatus"/>
            <result property="shippingStatus" column="shippingStatus"/>
            <result property="actionNote" column="actionNote"/>
            <result property="actionTime" column="actionTime"/>
            <result property="statusDesc" column="statusDesc"/>
        </association>
    </resultMap>

    <select id="findAll" resultMap="list">
        select * from demo.indent i,demo.indentuser u
        <where>
            i.ordersn = u.ordersn
            <if test="ordersn!=null and ordersn!='' ">
                and i.ordersn = ${ordersn}
            </if>
            <if test="orderStatus!=0">
                and i.orderStatus = #{orderStatus}
            </if>
            <if test="payStatus!=0">
                and i.payStatus = #{payStatus}
            </if>
            <if test="orderTime!=null and orderTime!='' ">
                and orderTime = #{orderTime}
            </if>
        </where>
    </select>

    <delete id="delById" parameterType="int">
        delete from indent where orderid = #{orderid}
    </delete>

    <delete id="delById2" parameterType="int">
        delete from demo.indentuser where actionId = #{actionId}
    </delete>

    <insert id="add" parameterType="indent">
        insert into demo.indent (ordersn, userId, orderStatus, payStatus, shippingStatus, orderTime)
        values (#{ordersn}, #{userId}, #{orderStatus}, #{payStatus}, #{shippingStatus}, #{orderTime});
    </insert>

    <insert id="add2" parameterType="indentUser">
        insert into demo.indentuser (ordersn, actionUser, orderStatus, payStatus, shippingStatus, actionNote, actionTime)
        values (#{ordersn}, #{actionUser}, #{orderStatus}, #{payStatus}, #{shippingStatus}, #{actionNote}, #{actionTime});
    </insert>

    <select id="findById" resultMap="list">
        select * from demo.indent i,demo.indentuser u
        where i.ordersn = u.ordersn and i.orderid = #{orderid}
    </select>

    <update id="save" parameterType="indent">
        update demo.indent
        set ordersn = #{ordersn},
        orderStatus = #{orderStatus},
        payStatus = #{payStatus},
        shippingStatus = #{shippingStatus}
        where orderid = #{orderid}
    </update>

    <update id="save2" parameterType="indentUser">
        update demo.indentuser
        set ordersn = #{ordersn},
        orderStatus = #{orderStatus},
        payStatus = #{payStatus},
        shippingStatus = #{shippingStatus}
        where actionId = #{actionId}
    </update>


</mapper>

4.编写service业务层

public interface IndentService {

    /*
     * 查询所有订单信息并分页
     * 模糊查询
     * */
    List<Indent> findAll(String ordersn, Integer orderStatus,
                         Integer payStatus, String orderTime);

    /*
     * 删除
     * */
    int delById(Integer orderid);

    /*
     * 删除订单类型
     * */
    int delById2(Integer orderid);

    /*
     * 新增
     * */
    int add(Indent indent);

    /*
     * 新增订单操作信息
     * */
    int add2(IndentUser indentUser);

    /*
     * 根据订单编号查询订单信息
     * */
    Indent findById(Integer orderid);

    /*
     * 修改
     * */
    int save(Indent indent);

    /*
     * 修改订单操作
     * */
    int save2(IndentUser indentUser);


}

5.service业务层实现类

@Service
public class IndentServiceImpl implements IndentService{

    @Autowired
    private IndentMapper indentMapper;

    @Override
    public List<Indent> findAll(String ordersn, Integer orderStatus,
                                Integer payStatus, String orderTime) {
        return indentMapper.findAll(ordersn, orderStatus, payStatus, orderTime);
    }

    @Override
    public int delById(Integer orderid) {
        return indentMapper.delById(orderid);
    }

    @Override
    public int delById2(Integer orderid) {
        return indentMapper.delById2(orderid);
    }

    @Override
    public int add(Indent indent) {
        return indentMapper.add(indent);
    }

    @Override
    public int add2(IndentUser indentUser) {
        return indentMapper.add2(indentUser);
    }

    @Override
    public Indent findById(Integer orderid) {
        return indentMapper.findById(orderid);
    }

    @Override
    public int save(Indent indent) {
        return indentMapper.save(indent);
    }

    @Override
    public int save2(IndentUser indentUser) {
        return indentMapper.save2(indentUser);
    }
}

6.controller层

使用thymeleaf进行页面跳转注意点：页面必须放在templates下面，这样子才能通过controller层进行页面跳转

@Controller
public class IndentController {

    @Autowired
    private IndentService service;

    @RequestMapping("/toadd")
    public String to(){
        return "add";
    }

    @RequestMapping({"/","/list"})
    public String list(@RequestParam(defaultValue = "") String ordersn,
                       @RequestParam(defaultValue = "") String orderTime,
                       @RequestParam(defaultValue = "0") Integer orderStatus,
                       @RequestParam(defaultValue = "0") Integer payStatus,
                       @RequestParam(defaultValue = "0") Integer pageNo, Model model){
        PageHelper.startPage(pageNo,2);
        List<Indent> list = service.findAll(ordersn, orderStatus, payStatus, orderTime);
        PageInfo<Indent> pageInfo = new PageInfo<>(list);
        model.addAttribute("pageInfo",pageInfo);
        model.addAttribute("ordersn",ordersn);
        model.addAttribute("orderStatus",orderStatus);
        model.addAttribute("payStatus",payStatus);
        model.addAttribute("orderTime",orderTime);
        return "list";
    }

    @PostMapping("/add")
    public String add(Indent indent, IndentUser indentUser){
        System.out.println(indentUser);
        indent.setOrderTime(new Date());
        indent.setUserId(1);
        indentUser.setActionUser(1);
        indentUser.setActionTime(new Date());
        indentUser.setActionNote("当前暂无记录！");
        int add = service.add(indent);
        if (add > 0){
            service.add2(indentUser);
            return "redirect:/list";
        }
        return "add";
    }

    @RequestMapping("/del/{orderid}")
    public String delById(@PathVariable Integer orderid){
        int i = service.delById(orderid);
        if (i > 0){
            service.delById2(orderid);
        }
        return "redirect:/list";
    }

    @RequestMapping("/tosave/{orderid}")
    public String tosave(@PathVariable Integer orderid, Model model){
        Indent indent = service.findById(orderid);
        model.addAttribute("indent",indent);
        return "save";
    }

    @PostMapping("/save")
    public String save(Indent indent,IndentUser indentUser){
        int save = service.save(indent);
        if (save > 0){
            service.save2(indentUser);
            return "redirect:/list";
        }
        return "save";
    }

}

7.编写页面展示 — 一共有三个页面信息

注意点：使用Thymeleaf引擎模板需要引入标签 xmlns:th="http://www.thymeleaf.org"

查询：

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>
<form th:action="@{/list}" method="post">
    <table border="1px" align="center">
        <!--模糊查询条件-->
        <tr align="center">
            <td colspan="9">
                订单编号：<input type="text" name="ordersn">&nbsp;
                订单状态：
                <select name="orderStatus">
                    <option value="0">--请选择--</option>
                    <option value="1">已完成</option>
                    <option value="2">未完成</option>
                </select>&nbsp;
                支付状态：
                <select name="payStatus">
                    <option value="0">--请选择--</option>
                    <option value="1">成功</option>
                    <option value="2">失败</option>
                </select>&nbsp;
                下单时间：<input type="text" name="orderTime">&nbsp;
                <input type="submit" value="查询">
                <a th:href="@{/toadd}"><input type="button" value="新增"></a>
            </td>
        </tr>
        <tr align="center">
            <td>订单编号</td>
            <td>订单状态</td>
            <td>支付状态</td>
            <td>发货状态</td>
            <td>下单时间</td>
            <td>操作记录</td>
            <td>操作时间</td>
            <td>状态描述</td>
            <td>操作</td>
        </tr>
        <tr align="center" th:each="str:${pageInfo.list}">
            <td th:text="${str.ordersn}"></td>
            <td>
                <a th:if="${str.orderStatus == 1}" th:text="已完成"></a>
                <a th:if="${str.orderStatus == 2}" th:text="未完成"></a>
            </td>
            <td>
                <a th:if="${str.payStatus == 1}" th:text="成功"></a>
                <a th:if="${str.payStatus == 2}" th:text="失败"></a>
            </td>
            <td>
                <a th:if="${str.shippingStatus == 1}" th:text="已发货"></a>
                <a th:if="${str.shippingStatus == 2}" th:text="未发货"></a>
            </td>
            <td th:text="${#dates.format(str.orderTime,'yyyy-MM-dd')}"></td>
            <td th:text="${str.indentUser.actionNote}"></td>
            <td th:text="${#dates.format(str.indentUser.actionTime,'yyyy-MM-dd')}"></td>
            <td th:text="${str.indentUser.statusDesc}"></td>
            <td>
                <a th:href="@{'del/'+${str.orderid}}">删除</a>
                <a th:href="@{'tosave/'+${str.orderid}}">修改</a>
            </td>
        </tr>
        <!--分页信息-->
        <tr align="center">
            <td colspan="9">
                当前页：<b th:text="${pageInfo.pageNum}"></b>&nbsp;&nbsp;
                总页数：<b th:text="${pageInfo.pages}"></b>&nbsp;&nbsp;
                总条数：<b th:text="${pageInfo.total}"></b>&nbsp;&nbsp;
                <a th:href="@{'/list?pageNo='+${pageInfo.navigateFirstPage}+'&ordersn='+${ordersn}+'&orderStatus='+${orderStatus}+'&payStatus='+${payStatus}+'&orderTime='+${orderTime}}">首页</a>
                <a th:if="${pageInfo.hasPreviousPage == true}" th:href="@{'/list?pageNo='+${pageInfo.prePage}+'&ordersn='+${ordersn}+'&orderStatus='+${orderStatus}+'&payStatus='+${payStatus}+'&orderTime='+${orderTime}}">上一页</a>
                <a th:if="${pageInfo.hasNextPage == true}" th:href="@{'/list?pageNo='+${pageInfo.nextPage}+'&ordersn='+${ordersn}+'&orderStatus='+${orderStatus}+'&payStatus='+${payStatus}+'&orderTime='+${orderTime}}">下一页</a>
                <a th:href="@{'/list?pageNo='+${pageInfo.navigateLastPage}+'&ordersn='+${ordersn}+'&orderStatus='+${orderStatus}+'&payStatus='+${payStatus}+'&orderTime='+${orderTime}}">末页</a>
            </td>
        </tr>
    </table>
</form>
</body>
</html>

新增：

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>
<form th:action="@{add}" method="post">
    <table border="1px" align="center">
        <tr>
            <td>订单编号：</td>
            <td><input type="text" name="ordersn"></td>
        </tr>
        <tr>
            <td>订单状态：</td>
            <td>
                <select name="orderStatus">
                    <option value="0" selected>--请选择--</option>
                    <option value="1">已完成</option>
                    <option value="2">未完成</option>
                </select>
            </td>
        </tr>
        <tr>
            <td>支付状态：</td>
            <td>
                <select name="payStatus">
                    <option value="0" selected>--请选择--</option>
                    <option value="1">成功</option>
                    <option value="2">失败</option>
                </select>
            </td>
        </tr>
        <tr>
            <td>发货状态</td>
            <td>
                <select name="shippingStatus">
                    <option value="0" selected>--请选择--</option>
                    <option value="1">已发货</option>
                    <option value="2">未发货</option>
                </select>
            </td>
        </tr>
        <tr align="center">
            <td colspan="2">
                <input type="submit" value="新增">
            </td>
        </tr>
    </table>
</form>
</body>
</html>

修改：

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
</head>
<body>
<form th:action="@{/save}" method="post">
    <table border="1px" align="center">
        <tr>
            <input type="hidden" name="orderid" th:value="${indent.orderid}">
            <input type="hidden" name="actionId" th:value="${indent.orderid}">
            <td>订单编号：</td>
            <td><input type="text" name="ordersn" th:value="${indent.ordersn}"></td>
        </tr>
        <tr>
            <td>订单状态：</td>
            <td>
                <select name="orderStatus">
                    <option value="0" selected>--请选择--</option>
                    <option th:value="1" th:selected="${indent.orderStatus == 1}">已完成</option>
                    <option th:value="2" th:selected="${indent.orderStatus == 2}">未完成</option>
                </select>
            </td>
        </tr>
        <tr>
            <td>支付状态：</td>
            <td>
                <select name="payStatus">
                    <option value="0" selected>--请选择--</option>
                    <option th:value="1" th:selected="${indent.payStatus == 1}">成功</option>
                    <option th:value="2" th:selected="${indent.payStatus == 2}">失败</option>
                </select>
            </td>
        </tr>
        <tr>
            <td>发货状态</td>
            <td>
                <select name="shippingStatus">
                    <option value="0" selected>--请选择--</option>
                    <option th:value="1" th:selected="${indent.shippingStatus == 1}">已发货</option>
                    <option th:value="2" th:selected="${indent.shippingStatus == 2}">未发货</option>
                </select>
            </td>
        </tr>
        <tr align="center">
            <td colspan="2">
                <input type="submit" value="修改">
            </td>
        </tr>
    </table>
</form>
</body>
</html>

最后再进行运行测试，运行出结果即可！

页面信息：
在这里插入图片描述

堃琪

关注

2
点赞
踩
9

收藏

觉得还不错? 一键收藏
2
评论
SpringBoot-模糊查询-分页

springboot增删改查模糊查询并分页5.在yml中配置信息6.编写代码数据库表结构：订单表字段名类型长度注释orderidint8订单IDordersnvarchar255订单编号userIdint8用户IDorderStatusint11订单状态payStatusint11支付状态shippingStatusint11发货状态orderTimedatetime0下
复制链接

扫一扫