Spring_Boot_mybatis plus连接数据库实现增删查改

package cn.ps.entity;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;


@TableName("emp_t")
public class EmpAttr {
  @TableId(type=IdType.AUTO)
  private String empno;
  @TableField("ename")
  private String ename;
  private String sal;
  private String job;
  private String email;
  private String phone;

  public String getEmpno() {
    return empno;
  }

  public void setEmpno(String empno) {
    this.empno = empno;
  }

  public String getEname() {
    return ename;
  }

  public void setEname(String ename) {
    this.ename = ename;
  }

  public String getSal() {
    return sal;
  }

  public void setSal(String sal) {
    this.sal = sal;
  }

  public String getJob() {
    return job;
  }

  public void setJob(String job) {
    this.job = job;
  }

  public String getEmail() {
    return email;
  }

  public void setEmail(String email) {
    this.email = email;
  }

  public String getPhone() {
    return phone;
  }

  public void setPhone(String phone) {
    this.phone = phone;
  }
}

package cn.ps.entity;


import java.util.List;


public class Result {
    private  int code;
    private String msg;
    private int count;
    private List data;

    public int getCode() {
        return code;
    }

    public void setCode(int code) {
        this.code = code;
    }

    public String getMsg() {
        return msg;
    }

    public void setMsg(String msg) {
        this.msg = msg;
    }

    public int getCount() {
        return count;
    }

    public void setCount(int count) {
        this.count = count;
    }

    public List getData() {
        return data;
    }

    public void setData(List data) {
        this.data = data;
    }
}

package cn.ps.controller;

import cn.ps.entity.EmpAttr;
import cn.ps.entity.Result;
import cn.ps.service.EmpService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import javax.validation.Valid;

@RestController
public class EmpController {

  @Autowired
  private EmpService service;
  

  /**
   * 多条件搜索
   * listEmp?ename=a&curPage=1&pageNum=2
   * @param ename
   * @param page
   * @param limit
   */

  @GetMapping(value = "/queryEmp")
  public Result listEmp(String sal,String ename, String page, String limit) throws Exception {
    try {
      Result pb=service.queryEmp(sal,ename,page,limit);
      return pb;
    } catch (Exception e) {      
      e.printStackTrace();
    }
    return null;
  }


  @PostMapping(value = "adEmp")
  public Result addEmp(EmpAttr empAttr) throws Exception {
    Result rt=new Result();
    try{
      service.addEmp(empAttr);
    }catch(Exception e){
      rt.setCode(1);
      rt.setMsg("新增出错"+e.getMessage());
    }
    return rt;
  }


  @DeleteMapping(value = "adEmp/{empno}")
  public Result addEmp(@PathVariable String empno) throws Exception {
    Result rt=new Result();
    try{
      service.deleteEmp(empno);
    }catch(Exception e){
      rt.setCode(1);
      rt.setMsg("删除出错"+e.getMessage());
    }
    return rt;
  }


  @PutMapping(value = "adEmp/{empno}")
  public Result addEmp(@PathVariable String empno, @Valid EmpAttr empAttr ) throws Exception {
    Result rt=new Result();
    empAttr.setEmpno(empno);
    try{
      service.updateEmp(empAttr);
    }catch(Exception e){
      rt.setCode(1);
      rt.setMsg("修改出错"+e.getMessage());
    }
    return rt;
  }
}

package cn.ps.service.imp;

import java.sql.SQLException;
import java.util.List;
import cn.ps.entity.EmpAttr;
import cn.ps.entity.Result;
import cn.ps.mapper.EmpMapper;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;

import cn.ps.service.EmpService;

@Service
public class EmpServiceImpl implements EmpService{
  
  @Autowired
  private EmpMapper empMapper;
  
  public Result queryEmp(String sal,String ename, String curPage, String pageNum ) throws SQLException{
    //第一次访问 没有当前页
    if(StringUtils.isEmpty(curPage)) {
      curPage="1";
    }
    if(StringUtils.isEmpty(pageNum)) {
      pageNum="10";
    }
    if(StringUtils.isEmpty(ename)) {
      ename="";
    }
    if(StringUtils.isEmpty(sal)) {
      sal="";
    }
    
    //转换成int类型
    int curPageIn=Integer.parseInt(curPage);
    int pageNumIn=Integer.parseInt(pageNum);
    QueryWrapper<EmpAttr> qw=new QueryWrapper<>();
    qw.like("ename", ename);
    qw.like("sal", sal);
    IPage<EmpAttr> pageData=empMapper.selectPage(new Page(curPageIn,pageNumIn), qw);
    //List<EmpAttr>queryEmp=empMapper.queryEmp("%"+ename+"%", curPageIn, pageNumIn);
    /*PageInfo pi=new PageInfo(queryEmp);*/
    Result rt=new Result();
    rt.setCode(0);
    rt.setCount((int)pageData.getTotal());
    rt.setData(pageData.getRecords());
    return rt;
  }

  @Override
  public void addEmp(EmpAttr empAttr) {
    empMapper.insert(empAttr);
  }

  @Override
  public void deleteEmp(String empno) {
    empMapper.deleteById(empno);
  }

  @Override
  public void updateEmp(EmpAttr empAttr) {
    empMapper.updateById(empAttr);
  }
}

package cn.ps.service;

import java.sql.SQLException;


import cn.ps.entity.Result;
import cn.ps.entity.EmpAttr;


public interface EmpService {

  public Result queryEmp(String sal, String ename, String curPage, String pageNum) throws SQLException;

   void addEmp(EmpAttr empAttr);

  void deleteEmp(String empno);

  void updateEmp(EmpAttr empAttr);
}

package cn.ps.utils;

import java.util.List;



public class PagerBean<T> {
  public void calc() {
    
  }
  /**
   * 
   * @param curPage 当前页
   * @param pageNum 每页显示条数
   * @param total 总条数
   */
  public PagerBean(int curPage,int pageNum,int total) {
    //计算上一页
    this.prePage=(curPage==1?1:curPage-1);
    //计算总页数
    this.totalPage=(total%pageNum==0?total/pageNum:total/pageNum+1);
    //计算下一页
    this.nextPage=(curPage==totalPage?totalPage:curPage+1);
    //当前页的索引
    this.startIndex=(curPage-1)*pageNum;
    
    this.total=total;
    
    this.curPage=curPage;
    
    this.pageNum=pageNum;
    
  }
  public int getCurPage() {
    return curPage;
  }
  public void setCurPage(int curPage) {
    this.curPage = curPage;
  }
  public int getPageNum() {
    return pageNum;
  }
  public void setPageNum(int pageNum) {
    this.pageNum = pageNum;
  }
  public int getPrePage() {
    return prePage;
  }
  public void setPrePage(int prePage) {
    this.prePage = prePage;
  }
  public int getNextPage() {
    return nextPage;
  }
  public void setNextPage(int nextPage) {
    this.nextPage = nextPage;
  }
  public int getTotal() {
    return total;
  }
  public void setTotal(int total) {
    this.total = total;
  }
  public int getTotalPage() {
    return totalPage;
  }
  public void setTotalPage(int totalPage) {
    this.totalPage = totalPage;
  }
  public List<T> getData() {
    return data;
  }
  public void setData(List<T> data) {
    this.data = data;
  }
  /**
   * 当前页 查询的默认当前页=1
   * 当前页传递的参数
   */
  private int curPage;
  
  
  /**
   * 每页显示的数据 默认10条
   * 当前页传递的参数
   */
  private int pageNum=10;
  
  /**
   * 根据当前页计算
   * 上一页
   * curPage=1 prePage=1
   * curPage>1 prePage=curPage-1
   * 2    prePage=2-1=1
   */
  private int prePage;
  
  /**
   * 下一页
   * curPage=totalPage  nextPage=totalPage
   * curPage<totalPage  nextPage=curPage
   * 当前页数2 总页数2 下一页2
   * 当前页数1 总页数2 下一页2
   */
  private int nextPage;
  
  
  
  /**
   * 总共多少条
   * 数据库查询
   */
  private int total;
  
  /**
   * 总共多少页
   * 总条数total/每页显示条数pageNum
   * 20/10=2
   * total%pageNum==0?total/pageNum:total/pageNum+1
   */
  private int totalPage;
  
  /**
   * 开始索引
   * startIndex=(curPage-1)*pageNum
   */
  private int startIndex;
  public int getStartIndex() {
    return startIndex;
  }
  public void setStartIndex(int startIndex) {
    this.startIndex = startIndex;
  }
  /**
   * 装载当前页的数据
   */
  private List<T> data;
  
}

package cn.ps;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

@RestController
@SpringBootApplication
//@MapperScan("cn.ps.mapper")
public class EmpMain {
    @RequestMapping("/")
    String home() {
        return "Hello World!";
    }

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

package cn.ps.conf;

import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import com.github.pagehelper.PageHelper;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import java.util.Properties;


//Spring boot方式
@EnableTransactionManagement
@Configuration
@MapperScan("com.baomidou.cloud.service.*.mapper*")
public class ConfigBean {

    /**
     * 分页插件
     */
    @Bean
    public PaginationInterceptor paginationInterceptor() {
        return new PaginationInterceptor();
    }
}

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>Title</title>
    <link rel="stylesheet" href="resource/layui/css/layui.css">
    <script src="resource/layui/layui.js"></script>
    <script src="jquery-3.3.1.js"></script>
    <script type="text/javascript">

        layui.use('form',function () {
            var form=layui.form;
            form.on('submit(myForm)',function (data) {
                data.field["page"]=1;
                layui.table.reload('myEmpTable', {
                    url: 'queryEmp'
                    ,where: data.field
                });
                return false;
            });
            form.on('submit(addForm)',function (data) {
                $.ajax({
                    url:'adEmp',
                    dataType:'json',
                    data:data.field,
                    type:'post',
                    success:function (da) {
                        if(da.code==0){
                            alert("新增成功");
                            layer.close(addWindow);
                            layui.table.reload('myEmpTable', {
                                url: 'queryEmp'
                            });
                        }else {
                            alert(da.msg);
                        }
                    }
                });
                return false;
            });


            form.on('submit(updateForm)', function(data){
                data.field["_method"]="put";
                $.ajax({
                    url:'adEmp/'+data.field.empno,
                    dataType:'json',
                    type:'post',
                    data:data.field,
                    success:function (da) {
                        if(da.code==0){
                            alert("修改成功");
                            layer.close(addWindow);
                            layui.table.reload('myEmpTable', {
                                url:'queryEmp'
                            });
                        }else{
                            alert(da.msg);
                        }
                    }
                });
                return false;
            });
        });


        layui.use('table', function(){
            var table = layui.table;
        table.render({
            elem: '#myEmpTable'
            ,height: 312
            ,width:'1200'
            ,url: 'queryEmp' //数据接口
            ,page: true //开启分页
            ,cols: [[ //表头
                {field: 'empno', title: '雇员编号', width:100, sort: true, fixed: 'left'}
                ,{field: 'ename', title: '雇员名称', width:150}
                ,{field: 'sal', title: '薪水', width:150, sort: true}
                ,{field: 'job', title: '职位', width:150}
                ,{field: 'email', title: '邮箱', width: 150}
                ,{field: 'phone', title: '手机号码', width: 150, sort: false}
                ,{field: 'dele', title: '操作', width: 210,templet: function(d){

                        return "<button class='layui-btn' style='line-height: 20px;text-align: center;margin-left: 10px; width: 80px;height: 100%; color: white; background-color: #33ABA0;border: 1px solid #33ABA0;' onclick='deleteEmp("+d.empno+")'>删除</button>"+
                                "<button class='layui-btn' style='line-height: 20px;text-align: center;margin-left: 10px; width: 80px;height: 100%; color: white; background-color: #33ABA0;border: 1px solid #33ABA0;' onclick=updateEmp('"+d.empno+"','"+d.ename+"','"+d.sal+"','"+d.job+"','"+d.email+"','"+d.phone+"')>修改</button>";
                    },}
            ]]
        });
    });

    var addWindow=null;
    function addEmp() {
        layui.use('layer',function (){
            addWindow=layer.open({
                type:1,
                area:['400px','400px'],
                content:$("#addForm")
            });
        });
    }

    function deleteEmp(empno) {
        $.ajax({
            url:'adEmp/'+empno,
            dataType:'json',
            data:{
                "_method":"delete"
            },
            type:'post',
            success:function (da) {
                if(da.code==0){
                    alert("删除成功");
                    layui.table.reload('myEmpTable', {
                        url: 'queryEmp'
                    });
                }else {
                    alert(da.msg);
                }
            }
        });
        return false;
    }



        function  updateEmp(empno,ename,sal,job,email,phone) {
            $("#updateForm input[name=empno]").val(empno);
            $("#updateForm input[name=ename]").val(ename);
            $("#updateForm input[name=sal]").val(sal);
            $("#updateForm input[name=job]").val(job);
            $("#updateForm input[name=email]").val(email);
            $("#updateForm input[name=phone]").val(phone);
            layui.use('layer', function(){
                addWindow=layer.open({
                    type:1,
                    area: ['500px', '400px'],
                    content: $("#updateForm"),
                });
            });
        };
</script>
</head>
<body>
    <br/>
    <form class="layui-form" action="">
    <div class="layui-inline">
        <label class="layui-form-label">雇员名称</label>
        <div class="layui-input-inline" style="width: 200px;">
            <input type="text" name="ename" placeholder="请输入名称" autocomplete="off" class="layui-input">          
        </div>
        <div class="layui-input-inline" style="width: 200px;">         
            <input type="text" name="sal" placeholder="请输入编号" autocomplete="off" class="layui-input">
        </div>
        <div class="layui-input-inline" style="width: 200px;">
            <button class="layui-btn" lay-submit lay-filter="myForm">查询</button>
            <a href="javascript:addEmp()" class="layui-btn">新增</a>
        </div>
    </div>
    </form>

    <table id="myEmpTable" ></table>
</body>
</html>



<form id="addForm" class="layui-form" style="display: none"> <!-- 提示：如果你不想用form，你可以换成div等任何一个普通元素 -->
    <div class="layui-form-item">
        <label class="layui-form-label">雇员名称:</label>
        <div class="layui-input-block">
            <input type="text"  name="ename" lay-verify="required" placeholder="请输入雇员名称" autocomplete="off" class="layui-input">
        </div>
    </div>
    <div class="layui-form-item">
        <label class="layui-form-label">薪水:</label>
        <div class="layui-input-block">
            <input type="text" name="sal" lay-verify="number" placeholder="请输入薪水" autocomplete="off" class="layui-input">
        </div>
    </div>
    <div class="layui-form-item">
        <label class="layui-form-label">职位:</label>
        <div class="layui-input-block">
            <input type="text" name="job" placeholder="请输入职位" autocomplete="off" class="layui-input">
        </div>
    </div>
    <div class="layui-form-item">
        <label class="layui-form-label">邮箱:</label>
        <div class="layui-input-block">
            <input type="text" name="email" lay-verify="email" placeholder="请输入邮箱" autocomplete="off" class="layui-input">
        </div>
    </div>
    <div class="layui-form-item">
        <label class="layui-form-label">联系方式:</label>
        <div class="layui-input-block">
            <input type="text" name="phone" lay-verify="phone" placeholder="请输入联系方式" autocomplete="off" class="layui-input">
        </div>
    </div>
    <div class="layui-form-item">
        <div class="layui-input-block">
            <button class="layui-btn" lay-submit lay-filter="addForm">立即提交</button>
            <button type="reset" class="layui-btn layui-btn-primary">重置</button>
        </div>
    </div>
</form>



<form id="updateForm" class="layui-form" style="display: none"> <!-- 提示：如果你不想用form，你可以换成div等任何一个普通元素 -->
    <input type="hidden" name="empno">
    <div class="layui-form-item">
        <label class="layui-form-label">雇员名称:</label>
        <div class="layui-input-block">
            <input type="text"  name="ename" lay-verify="required" placeholder="请输入" autocomplete="off" class="layui-input">
        </div>
    </div>
    <div class="layui-form-item">
        <label class="layui-form-label">薪水:</label>
        <div class="layui-input-block">
            <input type="text"  name="sal" lay-verify="number" placeholder="请输入" autocomplete="off" class="layui-input">
        </div>
    </div>
    <div class="layui-form-item">
        <label class="layui-form-label">职位:</label>
        <div class="layui-input-block">
            <input type="text"  name="job" placeholder="请输入" autocomplete="off" class="layui-input">
        </div>
    </div>
    <div class="layui-form-item">
        <label class="layui-form-label">邮箱:</label>
        <div class="layui-input-block">
            <input type="text"  name="email" lay-verify="email" placeholder="请输入" autocomplete="off" class="layui-input">
        </div>
    </div>
    <div class="layui-form-item">
        <label class="layui-form-label">联系方式:</label>
        <div class="layui-input-block">
            <input type="text"  name="phone" lay-verify="phone" placeholder="请输入" autocomplete="off" class="layui-input">
        </div>
    </div>
    <div class="layui-form-item">
        <div class="layui-input-block">
            <button class="layui-btn" lay-submit lay-filter="updateForm">立即提交</button>
            <button type="reset" class="layui-btn layui-btn-primary">重置</button>
        </div>
    </div>
</form>

spring.datasource.druid.url=jdbc:mysql://192.168.0.110/mysql
spring.datasource.druid.username=root
spring.datasource.druid.password=123456
spring.datasource.druid.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource

<?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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <groupId>cn.ps</groupId>
    <artifactId>SpringMybatisPlus</artifactId>
    <version>1.0-SNAPSHOT</version>
    <properties>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>
        <maven.compiler.compilerVersion>1.8</maven.compiler.compilerVersion>
    </properties>
    <build>
        <plugins>
            <plugin>
                <groupId>org.apache.tomcat.maven</groupId>
                <artifactId>tomcat7-maven-plugin</artifactId>
                <version>2.2</version>
                <configuration>
                    <uriEncoding>UTF-8</uriEncoding>
                </configuration>
            </plugin>
        </plugins>
    </build>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>1.5.17.RELEASE</version>
    </parent>
    <dependencies>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid-spring-boot-starter</artifactId>
            <version>1.1.10</version>
        </dependency>
        <dependency>
            <groupId>commons-lang</groupId>
            <artifactId>commons-lang</artifactId>
            <version>2.2</version>
        </dependency>
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.0.6</version>
        </dependency>
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper</artifactId>
            <version>4.1.6</version>
        </dependency>
    </dependencies>

</project>