第八，九次作业Springboot+Element：分页+条件查询+xml映射动态sql

zy20040726

于 2024-06-25 15:03:05 发布

阅读量229

点赞数 4

文章标签： mybatis

本文链接：https://blog.csdn.net/zy20040726/article/details/139958773

版权

一、课设

课设题目

我们组的课设题目是：志愿者管理系统
我认领的功能模块是：志愿者管理

二、分页条件查询

页面效果

第一页

第二页

条件查询

代码

准备工作
在pom.xml文件中添加PageHelper分页插件依赖

<!-- PageHelper分页插件   -->
<dependency>
    <groupId>com.github.pagehelper</groupId>
    <artifactId>pagehelper-spring-boot-starter</artifactId>
    <version>1.4.2</version>
</dependency>

controller

package com.wust.controller;

import com.wust.pojo.PageBean;
import com.wust.pojo.Volun;
import com.wust.pojo.Result;
import com.wust.service.VolunService;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

@Slf4j
@RestController
public class VolunController {
    @Autowired
    private VolunService volunService;

    //查询
    @GetMapping("/index")
    public Result select(){
        return Result.success(volunService.selectData());
    }

    //分页查询
    @GetMapping("/voluns/{page}/{pageSize}")
    public Result page(@PathVariable Integer page,
                       @PathVariable Integer pageSize,
                       String name,String identity){
        //记录日志
        log.info("分页查询：{}，{}，{}，{}",page,pageSize,name,identity);
        //调用service分页查询
        PageBean pageBean = volunService.page(page,pageSize,name,identity);
        //响应
        return Result.success(pageBean);
    }

    //新增
    @RequestMapping("/volun")
    public Result insert(@RequestBody Volun volun){
        volunService.insert(volun);
        return Result.success();
    }

    //删除
    @DeleteMapping("/delete/{identity}")
    public Result delete(@PathVariable String identity){
        volunService.delete(identity);
        return Result.success();
    }

    //修改  先查询，再修改
    @RequestMapping("/voluns/{identity}")
    public Result getById(@PathVariable String identity){
        Volun volun = volunService.getById(identity);
        return Result.success(volun);
    }

    @PutMapping("/update")
    public Result update(@RequestBody Volun volun){
        volunService.update(volun);
        return Result.success();
    }
}

mapper

package com.wust.mapper;

import com.wust.pojo.Volun;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface VolunMapper {
    //    @Select("select p_id as id, name, gender, dynasty, title, style from poet")
    List<Volun> selectVolun();

    //    @Insert("insert into poet (name, gender, dynasty, title, style) " +
//            "values (#{name},#{gender},#{dynasty},#{title},#{style})")
    void insert(Volun volun);

    //    @Delete("delete from poet where p_id=#{id}")
    void delete(String identity);

    //    @Select("select p_id as id, name, gender, dynasty, title, style from poet where p_id=#{id}")
    Volun getById(String identity);

    //    @Update("update poet set " +
//            "name=#{name},gender=#{gender},dynasty=#{dynasty},title=#{title},style=#{style} where p_id=#{id}")
    void update(Volun volun);

    //诗人信息 - 条件查询
    public List<Volun> list(@Param("name") String name, @Param("identity") String identity);
}

pojo

package com.wust.pojo;

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

import java.util.List;

//分页查询结果封装
@Data
@AllArgsConstructor
@NoArgsConstructor
public class PageBean {
    private Long total; // 总记录数
    private List rows; // 数据列表
}

package com.wust.pojo;

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

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Result {

    Integer code;
    String msg;
    Object data;

    public static Result success(){
        return new Result(1,"success",null);
    }
    public static Result success(Object data){
        return new Result(1,"success",data);
    }
    public static Result error(String msg){
        return new Result(0,msg,null);
    }
}

package com.wust.pojo;

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

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Volun {
    private String identity;//学号
    private String name;  //姓名
    private Integer gender; //性别
    private Integer age;
    private String phone;
}

service

package com.wust.service;

import com.wust.pojo.PageBean;
import com.wust.pojo.Volun;

import java.util.List;

public interface VolunService {

    PageBean page(Integer page, Integer pageSize, String name, String identity);

    List<Volun> selectData();

    void insert(Volun volun);

    void delete(String identity);

    Volun getById(String identity);

    void update(Volun volun);
}

package com.wust.service.impl;


import com.github.pagehelper.PageHelper;
import com.wust.mapper.VolunMapper;
import com.wust.pojo.PageBean;
import com.wust.pojo.Volun;
import com.wust.service.VolunService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class VolunServiceImpl implements VolunService {
    @Autowired
    private VolunMapper volunMapper;

    @Override
    public PageBean page(Integer page, Integer pageSize, String name, String identity) {
        //1.设置分页参数
        PageHelper.startPage(page,pageSize);

        //2.执行查询
        List<Volun> volunList = volunMapper.list(name,identity);
        com.github.pagehelper.Page<Volun> p = (com.github.pagehelper.Page<Volun>) volunList;

        //3.封装PageBean对象
        PageBean pageBean = new PageBean(p.getTotal(),p.getResult());
        return pageBean;
    }

    public List<Volun> selectData(){
        return volunMapper.selectVolun();
    }

    public void insert(Volun volun){
        volunMapper.insert(volun);
    }

    public void delete(String identity){
        volunMapper.delete(identity);
    }


    public Volun getById(String identity){
        return volunMapper.getById(identity);
    }

    public void update(Volun volun){
        volunMapper.update(volun);
    }
}

前端

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>xml方式写sql</title>
    <link rel="stylesheet" href="js/element.css">
</head>
<body style="align:center">

<div id="app" style="width: 80%;align:center">
    <h1 align="center">志愿者信息</h1>
    <p align="center">
        <el-form :inline="true" :model="formInline" class="demo-form-inline">
            <el-form-item label="姓名">
                <el-input v-model="formInline.name" placeholder="姓名"></el-input>
            </el-form-item>
            <el-form-item label="学号">
                <el-input v-model="formInline.identity" placeholder="学号"></el-input>
            </el-form-item>
            <el-form-item>
                <el-button type="primary" @click="onSubmit">查询</el-button>
            </el-form-item>
        </el-form>
    </p>
    <el-table
            :data="tableData.filter(data => !search || data.author.toLowerCase().includes(search.toLowerCase()))"
            style="width: 100%;align:center;font-size: 20px">
        <el-table-column
                label="学号"
                prop="identity">
        </el-table-column>
        <el-table-column
                label="姓名"
                prop="name">
        </el-table-column>
        <el-table-column
                label="性别"
                prop="gender">
        </el-table-column>
        <el-table-column
                label="年龄"
                prop="age">
        </el-table-column>
        <el-table-column
                label="电话"
                prop="phone">
        </el-table-column>
        <el-table-column
                align="right">
            <template slot="header" slot-scope="scope">
                <el-input
                        v-model="search"
                        size="mini"
                        placeholder="输入关键字搜索"/>
            </template>
            <template slot-scope="scope">
                <el-button
                        size="mini"
                        @click="handleEdit(scope.$index, scope.row)">Edit</el-button>
                <el-button
                        size="mini"
                        type="danger"
                        @click="handleDelete(scope.$index, scope.row)">Delete</el-button>


            </template>
        </el-table-column>
    </el-table>
    <p align="center">
        <el-pagination
                layout="total, sizes, prev, pager, next, jumper"
                @size-change="handleSizeChange"
                @current-change="handleCurrentChange"
                :current-page="currentPage"
                :page-sizes="[3, 5, 10, 20]"
                :page-size="pageSize"
                :total="total">
        </el-pagination>
    </p>
</div>

<!-- 引入组件库 -->
<script src="js/jquery.min.js"></script>
<script src="js/vue.js"></script>
<script src="js/element.js"></script>
<script src="js/axios-0.18.0.js"></script>


<script>
    new Vue({
        el:"#app",
        data: {
            search: '',
            currentPage: 1,
            pageSize: 4,
            total: null,
            tableData: [],
            formInline: {
                name: '',
                identity: ''
            }
        },
        methods: {
            handleEdit(index, row) {
                console.log(index, row);
            },
            handleDelete(index, row) {
                console.log(index, row);
            },
            handleSizeChange(val) {
                this.pageSize = val;
                this.findAll();
                console.log(`每页 ${val} 条`);

            },
            handleCurrentChange(val) {
                this.currentPage = val;
                this.findAll();
                console.log(`当前页: ${val}`);

            },
            onSubmit() {

                var url = `/voluns/${this.currentPage}/${this.pageSize}?name=${encodeURIComponent(this.formInline.name)}&identity=${encodeURIComponent(this.formInline.identity)}`

                console.log(this.formInline.name);
                console.log(this.formInline.identity);


                axios.get(url)
                    .then(res =>{
                        this.tableData = res.data.data.rows;
                        this.total=res.data.data.total;
                        console.log(this.tableData);
                        console.log(this.total);
                    })
                    .catch(error=>{
                        console.error(error);
                    })

            },

            findAll() {

                var url = `/voluns/${this.currentPage}/${this.pageSize}`

                axios.get(url)
                    .then(res =>{
                        this.tableData = res.data.data.rows;
                        this.total=res.data.data.total;
                        console.log(this.tableData);
                        console.log(this.total);
                    })
                    .catch(error=>{
                        console.error(error);
                    })

            }

        },
        created(){
            this.findAll();
        }

    })


</script>

</body>
</html>

三、 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.wust.mapper.VolunMapper">
    <!--    查询-->
    <select id="selectVolun" resultType="com.wust.pojo.Volun">
        select * from volun
    </select>

    <!--    条件分页查询-->
    <select id="list" resultType="com.wust.pojo.Volun">
        select * from volun
        <where>
            <if test="name != null and name != '' ">
                name like concat('%', #{name},'%')
            </if>
            <if test="identity != null and identity != '' ">
                and identity like concat('%',#{identity},'%')
            </if>
        </where>
    </select>

    <!--    新增-->
    <insert id="insert">
        insert into volun (identity ,name ,gender,age,phone)
        values (#{identity},#{name},#{gender},#{age},#{phone})
    </insert>

    <!--    删除-->
    <delete id="delete">
        delete from volun where identity =#{identity}
    </delete>

    <!--    修改 先查询-->
    <select id="getById" resultType="com.wust.pojo.Volun">
        select identity ,name,gender,age,phone
        from volun
        where identity=#{identity}
    </select>

    <!--    再查询-->
    <update id="update">
        update volun
        set identity=#{identity},name=#{name},gender=#{gender},age=#{age},phone=#{phone}
        where identity = #{identity}
    </update>
</mapper>

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <title>Title</title>
  <script src="./js/vue.js"></script>
  <script src="./js/axios-0.18.0.js"></script>

</head>
<body>
<h1 align="center">志愿者信息列表</h1>
<div id="app">
</div>

</body>

<script>
  new Vue({
    el:"#app",
    data() {

    },

    methods:{

      delete:function (identity) {
        var _thisd = this;
        var url = `voluns/${this.identity}`  //注意这里是反引号
        if (window.confirm("确定要删除该条数据吗？？？")){
          axios.delete(url)
                  .then(function (response) {
                    alert("删除成功")
                    // _thisd.findAll();
                    location.href = 'index.html'
                  })
                  .catch(function (error) {
                    console.log(error);
                  });
        }
      }

    },
    created() {
      // 获得参数id值
      this.identity = location.href.split("?identity=")[1]
      // 通过id查询详情
      this.delete();
    },
  })


</script>

</html>

<!DOCTYPE html>
<html lang="en">
<head>
  <meta charset="UTF-8">
  <title>编辑</title>
  <script src="./js/vue.js"></script>
  <script src="./js/axios-0.18.0.js"></script>

</head>
<body>
<div id="app" align="center">
  <table border="1">
    <tr>
      <td>学号</td>
      <td><input type="text" v-model="this.identity"> </td>
    </tr>
    <tr>
      <td>姓名</td>
      <td><input type="text" v-model="volun.name"> </td>
    </tr>
    <tr>
      <td>性别</td>
      <td>
        <input type="radio" name="gender" v-model="volun.gender" value="1"> 男
        <input type="radio" name="gender" v-model="volun.gender" value="2"> 女
      </td>
    </tr>
    <tr>
      <td>年龄</td>
      <td><input type="text" v-model="volun.age"> </td>
    </tr>
    <tr>
      <td>电话</td>
      <td><input type="text" v-model="volun.phone"> </td>
    </tr>
    <tr>
      <td></td>
      <td><input type="button" @click="update" value="更新"> </td>

    </tr>
  </table>
  {{volun}}
</div>


</body>
<script>
  new Vue({
    el: '#app',
    data: {
      identity: '',
      volun: {},        //详情
    },
    methods: {
      getById() {
        //${this.identity}
        //   var url = `peotfindById/${this.id}`  //注意这里是反引号
        var url = `voluns/${this.identity}`
        // 当您想要引用当前对象或类的实例的 id 属性时，使用 this.id。
        //当您想要引用一个名为 id 的变量（无论它是在函数外部定义的，还是作为参数传递的），使用 id。
        //反引号（backticks，也称为模板字符串或模板字面量）是ES6（ECMAScript 2015）中引入的一种新字符串字面量功能，
        // 它允许您在字符串中嵌入表达式。反引号用`（键盘上通常位于Tab键上方）来界定字符串的开始和结束。
        axios.get(url)
                .then(response => {
                  var baseResult = response.data
                  if(baseResult.code == 1) {
                    this.volun = baseResult.data
                  }
                })
                .catch( error => {})
      },
      update() {

        var url = 'voluns'
        axios.put(url,this.volun)
                .then(res => {
                  var baseResult = res.data
                  if(baseResult.code == 1) {
                    // 成功
                    location.href = 'index.html'
                  } else {
                    // 失败
                    alert(baseResult.message)
                  }
                })
                .catch(err => {
                  console.error(err);
                })
      },


    },
    created() {
      // 获得参数id值
      this.identity = location.href.split("?identity=")[1]
      // 通过id查询详情
      this.getById()
    },

  })



</script>

</html>