SpringBoot+Mybatis+Thymeleaf实现员工增删改

该项目主要是为了实现对员工的数据增删改查的功能
使用框架: SpringBoot, Mybatis, Thymeleaf
数据库: MySQL

1)、项目目录结构图

01.png

2)、controller层的代码
说一下注意事项:如果需要传入带参数,则在url后加{参数名},如:toUpdateEmp/{emp_id}
并且在方法的参数中使用注解@PathVariable() 来标明,否则报错无法启动

package com.zrr.empmgr.controller;

import com.zrr.empmgr.enity.Emp;
import com.zrr.empmgr.service.EmpService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.servlet.ModelAndView;

import java.util.List;

/**
 * @auther ZengSakae
 * @date 2019-12-03 16:03
 */


@RestController
@RequestMapping(value = "/emp")
public class EmpController {

    @Autowired
    private EmpService empService;


    public Emp getEmpById(Integer emp_id) {

        Emp emp = empService.queryEmpById(emp_id);

        return emp;
    }

    @RequestMapping(value = "/queryEmpByCondition")
    public ModelAndView getEmpByCondition(String empName, String sex) {

        ModelAndView mav = new ModelAndView("list");
        List<Emp> emps = null;

        if (null != sex) {
            if ("男".equals(sex)) {
                sex = "M";
            } else if ("女".equals(sex)) {
                sex = "F";
            }
        }
        emps = empService.queryEmpByCondition(empName, sex);

        mav.addObject("emps", emps);
        return mav;
    }

    @RequestMapping(value = "toAddEmp")
    public ModelAndView toAddEmp() {
        ModelAndView mav = new ModelAndView("add");
        return mav;
    }

    @RequestMapping(value = "addEmp")
    public String addEmp(Emp emp) {

        boolean b = empService.addEmp(emp);

        if (b) {
            return "添加成功";
        } else {

            return "添加失败";
        }

    }

    @RequestMapping(value = "toUpdateEmp/{emp_id}")
    public ModelAndView toUpdateEmp(@PathVariable("emp_id") Integer emp_id) {

        ModelAndView mav = new ModelAndView("update");
        Emp emp = empService.queryEmpById(emp_id);
        mav.addObject("emp", emp);

        return mav;
    }

    @RequestMapping(value = "updateEmp")
    public String updateEmp(Emp emp) {

        boolean b = empService.updateEmp(emp);

        if (b) {
            return "修改成功";
        } else {
            return "修改失败";
        }

    }

    @RequestMapping(value = "delEmpById/{emp_id}")
    public String deleteEmp(@PathVariable("emp_id") Integer emp_id) {

        boolean b = empService.delEmpById(emp_id);

        if (b) {
            return "删除成功";
        } else {
            return "删除失败";
        }

    }

}

3)、实体类代码
注意事项: 对时间变量可以使用注解@DateTimeFormat(pattern = "yyyy-MM-dd")
来进行把java的时间类型转换为数据库的时间类型

package com.zrr.empmgr.enity;

import org.springframework.format.annotation.DateTimeFormat;

import java.util.Date;

/**
 * @auther ZengSakae
 * @date 2019-12-03 15:48
 */


public class Emp {

    private Integer emp_id;
    private Integer org_id;
    private String empCode;
    private String empName;
    private String sex;
    @DateTimeFormat(pattern = "yyyy-MM-dd")
    private Date birthday;
    private String postCode;
    private String address;
    private String phone;
    private double wage;

    public Integer getEmp_id() {
        return emp_id;
    }

    public void setEmp_id(Integer emp_id) {
        this.emp_id = emp_id;
    }

    public Integer getOrg_id() {
        return org_id;
    }

    public void setOrg_id(Integer org_id) {
        this.org_id = org_id;
    }

    public String getEmpCode() {
        return empCode;
    }

    public void setEmpCode(String empCode) {
        this.empCode = empCode;
    }

    public String getEmpName() {
        return empName;
    }

    public void setEmpName(String empName) {
        this.empName = empName;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public Date getBirthday() {
        return birthday;
    }

    public void setBirthday(Date birthday) {
        this.birthday = birthday;
    }

    public String getPostCode() {
        return postCode;
    }

    public void setPostCode(String postCode) {
        this.postCode = postCode;
    }

    public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address = address;
    }

    public String getPhone() {
        return phone;
    }

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

    public double getWage() {
        return wage;
    }

    public void setWage(double wage) {
        this.wage = wage;
    }

    @Override
    public String toString() {
        return "Emp{" +
                "emp_id=" + emp_id +
                ", org_id=" + org_id +
                ", empCode='" + empCode + '\'' +
                ", empName='" + empName + '\'' +
                ", sex='" + sex + '\'' +
                ", birthday=" + birthday +
                ", postCode='" + postCode + '\'' +
                ", address='" + address + '\'' +
                ", phone='" + phone + '\'' +
                ", wage=" + wage +
                '}';
    }
}

4)、Service层代码
注意事项: 无~

package com.zrr.empmgr.service;

import com.zrr.empmgr.enity.Emp;
import com.zrr.empmgr.mapper.EmpMapper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * @auther ZengSakae
 * @date 2019-12-03 16:09
 */

@Service
public class EmpService {

    @Autowired
    EmpMapper empMapper;

    public List<Emp> queryAllEmp(){

        return empMapper.queryAllEmp();
    }

    public List<Emp> queryEmpByCondition(String empName, String sex) {

        return empMapper.queryEmpByCondition(empName,sex);
    }

    public boolean addEmp(Emp emp) {

        return empMapper.addEmp(emp);
    }

    public boolean updateEmp(Emp emp) {

        return empMapper.updateEmp(emp);

    }

    public Emp queryEmpById(Integer emp_id) {

        return empMapper.queryEmpById(emp_id);
    }

    public boolean delEmpById(Integer emp_id) {

        return empMapper.delEmpById(emp_id);
    }
}

5)、Mapper
注意事项: 若在mapper.xml中需要使用变量,则需要在此类中用注解@Param来指定名称

package com.zrr.empmgr.mapper;

import com.zrr.empmgr.enity.Emp;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * @auther ZengSakae
 * @date 2019-12-03 16:23
 */

@Repository
public interface EmpMapper {

    List<Emp> queryAllEmp();

    List<Emp> queryEmpByCondition(@Param("empName") String empName,@Param("sex") String sex);

    boolean addEmp(@Param("emp") Emp emp);

    boolean updateEmp(@Param("emp") Emp emp);

    Emp queryEmpById(@Param("emp_id") Integer emp_id);

    boolean delEmpById(@Param("emp_id") Integer emp_id);
}

6)、启动类
注意事项: 需要使用注解@MapperScan("com.zrr.empmgr.mapper") 指定扫描的mapper

package com.zrr.empmgr;

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


@MapperScan("com.zrr.empmgr.mapper")
@SpringBootApplication
public class DemoApplication {

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

}

7)、EmpMapper.xml 代码
注意事项: 这里使用了mysql索引所以语句会比较长

<?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.zrr.empmgr.mapper.EmpMapper">

    <resultMap id="EmpResultMap" type="com.zrr.empmgr.enity.Emp">
        <result column="emp_id" jdbcType="INTEGER" property="emp_id" />
        <result column="org_id" jdbcType="INTEGER" property="org_id" />
        <result column="empCode" jdbcType="VARCHAR" property="empCode" />
        <result column="empName" jdbcType="VARCHAR" property="empName" />
        <result column="sex" jdbcType="VARCHAR" property="sex"/>
        <result column="birthday" jdbcType="DATE" property="birthday"/>
        <result column="postCode" jdbcType="VARCHAR" property="postCode"/>
        <result column="address" jdbcType="VARCHAR" property="address"/>
        <result column="phone" jdbcType="VARCHAR" property="phone"/>
        <result column="wage" jdbcType="DOUBLE" property="sex"/>
    </resultMap>

    <select id="queryEmpById" resultType="com.zrr.empmgr.enity.Emp">
        select *
        from emp e
        where e.emp_id = #{emp_id}
    </select>

    <select id="queryAllEmp" resultType="com.zrr.empmgr.enity.Emp" >
        select *
        from emp
        order by emp_id
    </select>

    <select id="queryEmpByCondition"  resultType="com.zrr.empmgr.enity.Emp">
        select *
        from emp e
        where 1=1
            <if test="'' != empName or '' != sex">
                <if test="'' != empName">
                    and  e.empName like concat(#{empName,jdbcType=VARCHAR},'%')
                    <if test="'' != sex">
                        and  e.sex = #{sex,jdbcType=VARCHAR}
                        order by e.empName,e.sex
                    </if>
                </if>
                <if test="'' == empName and '' != sex">
                    and  e.sex = #{sex,jdbcType=VARCHAR}
                    order by e.emp_id
                </if>
            </if>
            <if test="'' == empName and '' == sex ">
                order by e.emp_id
            </if>
    </select>

    <insert id="addEmp" parameterType="com.zrr.empmgr.enity.Emp">
        insert into
        emp
        values(#{emp.emp_id},#{emp.org_id},#{emp.empCode},#{emp.empName},#{emp.sex},#{emp.birthday},#{emp.postCode},#{emp.address},#{emp.phone},#{emp.wage})
    </insert>

    <update id="updateEmp" parameterType="com.zrr.empmgr.enity.Emp">
        update emp e
        set e.org_id = #{emp.org_id},e.empcode = #{emp.empCode},e.empname = #{emp.empName},e.sex = #{emp.sex},e.birthday = #{emp.birthday},e.postcode = #{emp.postCode},e.address = #{emp.address},e.phone = #{emp.phone},e.wage = #{emp.wage}
        where e.emp_id = #{emp.emp_id}
    </update>

    <delete id="delEmpById" parameterType="com.zrr.empmgr.enity.Emp">
        delete from emp e
        where e.emp_id = #{emp_id}
    </delete>

</mapper>

8)、springboot配置文件 application.yml

#spring 配置数据库的信息
spring:
  datasource:
    username: root
    password: Ab123456
    url: jdbc:mysql://localhost:3306/emp
    driver-class-name: com.mysql.jdbc.Driver

    initialSize: 5
    minIdle: 5
    maxActive: 20
    maxWait: 60000
    timeBetweenEvictionRunsMillis: 60000
    minEvictableIdleTimeMillis: 300000
    validationQuery: SELECT 1 FROM DUAL
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    poolPreparedStatements: true
    #   配置监控统计拦截的filters，去掉后监控界面sql无法统计，'wall'用于防火墙
    filters: stat,wall,log4j
    maxPoolPreparedStatementPerConnectionSize: 20
    useGlobalDataSourceStat: true
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500

  thymeleaf:
    cache=false
# 指定读取的mapper.xml 路径
mybatis:
  mapper-locations: classpath:mapping/*Mapper.xml
#开启sql语句日志
logging:
  level:
      com.zrr.empmgr.mapper.*: debug

9)、相关的html页面, 使用了thymeleaf
index.html

<!DOCTYPE html>
<!-- saved from url=(0052)http://getbootstrap.com/docs/4.0/examples/dashboard/ -->
<html lang="en" xmlns:th="http://www.thymeleaf.org">

<head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>

<body>
    <h1>欢迎使用员工管理系统</h1>
    <form action="/emp/queryEmpByCondition" method="post">
        <h3>输入查询条件</h3>
        姓名: <input name="empName" type="text">
        性别: <input name="sex" type="text">
        <input type="submit" value="查询">
    </form>
</body>

</html>

============================================================
list.html

<!DOCTYPE html>
<!-- saved from url=(0052)http://getbootstrap.com/docs/4.0/examples/dashboard/ -->
<html lang="en" xmlns:th="http://www.thymeleaf.org">

    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    </head>

    <body>
    <form action="" method="post" id="listform">
        <div>
            <table>
                <thead>
                <tr>
                    <th>员工ID</th>
                    <th>机构ID</th>
                    <th>员工编号</th>
                    <th>员工姓名</th>
                    <th>性别</th>
                    <th>出生日期</th>
                    <th>邮政编码</th>
                    <th>联系地址</th>
                    <th>联系电话</th>
                    <th>员工薪资</th>
                    <th>操作</th>
                </tr>
                </thead>
                <tbody>
                <tr th:each="emp:${emps}">
                    <td th:text="${emp.emp_id}">1,001</td>
                    <td th:text="${emp.org_id}">Lorem</td>
                    <td th:text="${emp.empCode}">ipsum</td>
                    <td th:text="${emp.empName}">dolor</td>
                    <td th:text="${emp.sex == 'M'? '男':'女'}">sit</td>
                    <td th:text="${#dates.format(emp.birthday, 'yyyy-MM-dd')}">sit</td>
                    <td th:text="${emp.postCode}">sit</td>
                    <td th:text="${emp.address}">sit</td>
                    <td th:text="${emp.phone}">sit</td>
                    <td th:text="${emp.wage}">sit</td>
                    <th>
                        <a href="#" th:href="@{/emp/toAddEmp/}">添加</a>

                    </th>
                    <th>
                        <a href="#" th:href="@{'/emp/toUpdateEmp/'+${emp.emp_id}}">修改</a>
                    </th>
                    <th>
                        <a href="#" th:href="@{'/emp/delEmpById/'+${emp.emp_id}}">删除</a>
                    </th>
                </tr>
                </tbody>
            </table>
        </div>
    </form>

    </body>

</html>

==============================================================
add.html

<!DOCTYPE html>
<!-- saved from url=(0052)http://getbootstrap.com/docs/4.0/examples/dashboard/ -->
<html lang="en" xmlns:th="http://www.thymeleaf.org">

    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    </head>

    <body>
    <form action="/emp/addEmp" method="post">

        员工ID<input type="text" name="emp_id"><hr>
        机构ID<input type="text" name="org_id"><hr>
        员工编号<input type="text" name="empCode"><hr>
        员工姓名<input type="text" name="empName"><hr>
        性别<input type="text" name="sex"><hr>
        出生日期<input type="text" name="birthday"><hr>
        邮政编码<input type="text" name="postCode"><hr>
        联系地址<input type="text" name="address"><hr>
        联系电话<input type="text" name="phone"><hr>
        员工薪资<input type="text" name="wage"><hr>

        <button type="submit">提交</button>
    </form>
    </body>

</html>

==============================================================
update.html

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>更新员工信息</title>
</head>
<body>
<form action="/emp/updateEmp" method="post" id="listform">
    员工ID: <input type="text" name="emp_id" th:value="${emp.emp_id}" readonly="readonly"> <br/>
    机构ID: <input type="text" name="org_id" th:value="${emp.org_id}"> <br/>
    员工编号: <input type="text" name="empCode" th:value="${emp.empCode}"> <br/>
    员工姓名: <input type="text" name="empName" th:value="${emp.empName}"> <br/>
    性别: <input type="text" name="sex" th:value="${emp.sex == 'M'? '男':'女'}"> <br/>
    出生日期: <input type="text" name="birthday" th:value="${#dates.format(emp.birthday, 'yyyy-MM-dd')}"> <br/>
    邮政编码: <input type="text" name="postCode" th:value="${emp.postCode}"> <br/>
    联系地址: <input type="text" name="address" th:value="${emp.address}">
    联系电话: <input type="text" name="phone" th:value="${emp.phone}"> <br/>
    员工薪资: <input type="text" name="wage" th:value="${emp.wage}"> <br/>
    <button type="submit">提交</button>

</form>
</body>
</html>

好了以上就是所有的内容~