员工管理系统(SpringBoot+Mybatis+Thymeleaf)
SpringBoot整合CRUD实现员工管理案例,将Mybatis整合到原项目中,加入了数据库,添加了日期选项的控件。
完整源码私信获取!
环境要求
JDK8以上
IDEA
MySQL8
Maven3
需要熟练掌握MySQL数据库,SpringBoot及MyBatis知识,简单的前端知识;
数据库环境
创建案例所使用的数据库
CREATE DATABASE employee
;
USE employee
;
创建登陆用户数据表
DROP TABLE IF EXISTS user
;
CREATE TABLE user
(
id
int(10) NOT NULL,
user_name
varchar(255) NOT NULL COMMENT ‘用户名’,
password
varchar(255) NOT NULL COMMENT ‘密码’,
PRIMARY KEY (id
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
INSERT INTO user
VALUES (1, ‘admin’, ‘123456’);
创建部门信息的数据库表
DROP TABLE IF EXISTS department
;
CREATE TABLE department
(
id
int(10) NOT NULL,
department_name
varchar(255) NOT NULL COMMENT ‘部门名称’,
PRIMARY KEY (id
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
BEGIN;
INSERT INTO department
VALUES (1, ‘市场部’);
INSERT INTO department
VALUES (2, ‘技术部’);
INSERT INTO department
VALUES (3, ‘销售部’);
INSERT INTO department
VALUES (4, ‘客服部’);
INSERT INTO department
VALUES (5, ‘公关部’);
COMMIT;
创建存放员工信息的数据库表
DROP TABLE IF EXISTS employee
;
CREATE TABLE employee
(
id
int(10) NOT NULL AUTO_INCREMENT,
employee_name
varchar(255) NOT NULL COMMENT ‘员工姓名’,
email
varchar(255) NOT NULL COMMENT ‘员工邮箱’,
gender
int(2) NOT NULL COMMENT ‘员工性别’,
department_id
int(10) NOT NULL COMMENT ‘部门编号’,
date
date NOT NULL COMMENT ‘入职日期’,
PRIMARY KEY (id
)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8;
BEGIN;
INSERT INTO employee
VALUES (1, ‘张三’, ‘zhangsan@gmail.com’, 0, 1, ‘2020-05-12’);
INSERT INTO employee
VALUES (2, ‘李四’, ‘lisi@qq.com’, 1, 2, ‘2020-05-05’);
INSERT INTO employee
VALUES (3, ‘王五’, ‘wangwu@126.com’, 0, 3, ‘2020-05-15’);
INSERT INTO employee
VALUES (4, ‘赵六’, ‘zhaoliu@163.com’, 1, 4, ‘2020-04-21’);
INSERT INTO employee
VALUES (5, ‘田七’, ‘tianqi@foxmail.com’, 0, 3, ‘2020-05-14’);
INSERT INTO employee
VALUES (10, ‘王伟’, ‘wangwei@gmail.com’, 1, 3, ‘2020-05-08’);
INSERT INTO employee
VALUES (11, ‘张伟’, ‘zhangwei@gmail.com’, 1, 2, ‘2020-05-11’);
INSERT INTO employee
VALUES (12, ‘李伟’, ‘liwei@gmail.com’, 1, 3, ‘2020-05-18’);
COMMIT;
基本环境搭建
新建Spring项目, 添加Lombok,Spring Web,Thymeleaf,Mybatis,MySQL Driver的支持
相关的pom依赖
org.springframework.boot spring-boot-starter-thymeleaf org.springframework.boot spring-boot-starter-web org.mybatis.spring.boot mybatis-spring-boot-starter 2.1.2 mysql mysql-connector-java runtime org.projectlombok lombok true org.springframework.boot spring-boot-starter-test test org.junit.vintage junit-vintage-engine
建立基本结构和配置框架
com/
|-- godfrey |-- config |-- controller |-- dto |-- mapper |-- pojo |-- service
application.yml里配置数据库连接信息及Mapper映射文件信息
spring:
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/employee?useSSL=true&useUnicode=true&characterEncoding=utf8&serverTimezone=GMT%2B8
username: employee
password: employee123
thymeleaf:
cache: false
messages:
basename: i18n.login
mybatis:
type-aliases-package: com.godfrey.pojo
mapper-locations: classpath:com.godfrey.mapper/*Mapper.xml
configuration:
map-underscore-to-camel-case: true
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
测试数据库连接
package com.godfrey;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
@SpringBootTest
class ApplicationTests {
@Autowired
DataSource dataSource;
@Test
void contextLoads() throws SQLException {
System.out.println("数据源>>>>>>" + dataSource.getClass());
Connection connection = dataSource.getConnection();
System.out.println("连接>>>>>>>>>" + connection);
System.out.println("连接地址>>>>>" + connection.getMetaData().getURL());
connection.close();
}
}
查看输出结果,数据库配置ok
创建pojo实体类
创建User实体
package com.godfrey.pojo;
import lombok.Data;
/**
-
description : 登录验证实体类
-
@author godfrey
-
@since 2020-05-26
*/
@Data
public class User {
private Integer id;
private String userName;
private String password;
}创建Department实体
package com.godfrey.pojo;
import lombok.Data;
/**
-
description : 部门实体类
-
@author godfrey
-
@since 2020-05-26
*/
@Data
public class Department {
private Integer id;
private String departmentName;
}创建Employee实体
package com.godfrey.pojo;
import lombok.Data;
import java.sql.Date;
/**
-
description : 员工实体类
-
@author godfrey
-
@since 2020-05-26
*/
@Data
public class Employee {
private Integer id;
private String employeeName;
private String email;
private Integer gender; //0:女 1:男
private Date date;
private Integer departmentId;
}创建EmployeeDTO实体
package com.godfrey.dto;
import lombok.Data;
import java.sql.Date;
/**
- description : EmployeeDTO
- @author godfrey
- @since 2020-05-26
*/
@Data
public class EmployeeDTO {
private Integer id;
private String employeeName;
private String email;
private Integer gender;
private String departmentName;
private Date date;
}
Mapper层
文件存放目录:
com.godfrey.mapper 相关接口
resources/com.godfrey.mapper 相关mapper.xml
编写User的Mapper接口:UserMapper
package com.godfrey.mapper;
import com.godfrey.pojo.User;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;
/**
-
description : 登录验证查询Mapper
-
@author godfrey
-
@since 2020-05-26
*/
@Mapper
@Repository
public interface UserMapper {
User selectPasswordByName(@Param(“userName”) String userName, @Param(“password”) String password);
}编写接口对应的Mapper.xml文件:UserMapper.xml
编写Department的Mapper接口:DepaertmentMapper
package com.godfrey.mapper;
import com.godfrey.pojo.Department;
import org.apache.ibatis.annotations.Mapper;
import org.springframework.stereotype.Repository;
import java.util.List;
/**
-
description : 部门Mapper
-
@author godfrey
-
@since 2020-05-26
*/
@Mapper
@Repository
public interface DepartmentMapper {List selectAllDepartment();
}编写接口对应的Mapper.xml文件:DepaertmentMapper.xml
编写Employee的Mapper接口:EmployeeMapper
package com.godfrey.mapper;
import com.godfrey.dto.EmployeeDTO;
import com.godfrey.pojo.Employee;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.springframework.stereotype.Repository;
import java.util.List;
/**
-
description : 员工Mapper
-
@author godfrey
-
@since 2020-05-26
*/
@Mapper
@Repository
public interface EmployeeMapper {
//查询全部员工信息
List selectAllEmployeeDTO();//根据id查询员工信息
Employee selectEmployeeById(@Param(“id”) Integer id);//添加一个员工信息
int addEmployee(Employee employee);//修改一个员工信息
int updateEmployee(Employee employee);//根据id删除员工信息
int deleteEmployee(@Param(“id”) Integer id);
}编写接口对应的Mapper.xml文件:EmployeeMapper.xml
<resultMap id="EmployeeDTO" type="com.godfrey.dto.EmployeeDTO">
<id column="id" jdbcType="INTEGER" property="id"/>
<result column="employee_name" jdbcType="VARCHAR" property="employeeName"/>
<result column="email" jdbcType="VARCHAR" property="email"/>
<result column="gender" jdbcType="INTEGER" property="gender"/>
<result column="department_name" jdbcType="VARCHAR" property="departmentName"/>
<result column="date" jdbcType="DATE" property="date"/>
</resultMap>
<select id="selectAllEmployeeDTO" resultMap="EmployeeDTO">
select e.id, e.employee_name, e.email, e.gender, d.department_name, e.date
from employee e,
department d
where e.department_id = d.id;
</select>
<select id="selectEmployeeById" resultType="Employee">
select *
from employee
where id = #{id}
</select>
<insert id="addEmployee" parameterType="Employee">
insert into employee (id, employee_name, email, gender, department_id, date)
values (#{id}, #{employeeName}, #{email}, #{gender}, #{departmentId}, #{date})
</insert>
<update id="updateEmployee" parameterType="Employee">
update employee
set employee_name=#{employeeName},
email=#{email},
gender=#{gender},
department_id=#{departmentId},
date=#{date}
where id = #{id}
</update>
<delete id="deleteEmployee" parameterType="int">
delete
from employee
where id = #{id}
</delete>
Service层
com.wangqiang.service
EmployeeService接口:
package com.godfrey.service;
import com.godfrey.dto.EmployeeDTO;
import com.godfrey.pojo.Employee;
import java.util.List;
/**
-
description : 员工业务层接口
-
@author godfrey
-
@since 2020-05-26
*/
public interface EmployeeService {
//查询全部员工信息
List selectAllEmployeeDTO();
//根据id查询员工信息
Employee selectEmployeeById(int id);
//添加一个员工信息
int addEmployee(Employee employee);
//修改一个员工信息
int updateEmployee(Employee employee);
//根据id删除员工信息
int deleteEmployee(int id);
}EmployeeServiceImpl实现类:
package com.godfrey.service.impl;
import com.godfrey.dto.EmployeeDTO;
import com.godfrey.mapper.EmployeeMapper;
import com.godfrey.pojo.Employee;
import com.godfrey.service.EmployeeService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
/**
-
description : 员工业务层实现类
-
@author godfrey
-
@since 2020-05-26
*/
@Service
public class EmployeeServiceImpl implements EmployeeService {
@Autowired
private EmployeeMapper employeeMapper;@Override
public List selectAllEmployeeDTO() {
return employeeMapper.selectAllEmployeeDTO();
}@Override
public Employee selectEmployeeById(int id) {
return employeeMapper.selectEmployeeById(id);
}@Override
public int addEmployee(Employee employee) {
return employeeMapper.addEmployee(employee);
}@Override
public int updateEmployee(Employee employee) {
return employeeMapper.updateEmployee(employee);
}@Override
public int deleteEmployee(int id) {
return employeeMapper.deleteEmployee(id);
}
}DepartmentService接口
package com.godfrey.service;
import com.godfrey.pojo.Department;
import java.util.List;
/**
-
description : DepartmentService
-
@author godfrey
-
@since 2020-05-27
*/
public interface DepartmentService {
List selectAllDepartment();
}DepartmentImpl实现类:
package com.godfrey.service.impl;
/**
- description : DepartmentServiceImpl
- @author godfrey
- @since 2020-05-27
*/
import com.godfrey.mapper.DepartmentMapper;
import com.godfrey.pojo.Department;
import com.godfrey.service.DepartmentService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.util.List;
@Service
public class DepartmentServiceImpl implements DepartmentService {
@Autowired
private DepartmentMapper departmentMapper;
@Override
public List<Department> selectAllDepartment() {
return departmentMapper.selectAllDepartment();
}
}
UserService接口
package com.godfrey.service;
import com.godfrey.pojo.User;
/**
-
description : User业务层
-
@author godfrey
-
@since 2020-05-26
*/
public interface UserService {
User selectPasswordByName(String userName, String password);
}UserServiceImpl实现类
package com.godfrey.service.impl;
import com.godfrey.mapper.UserMapper;
import com.godfrey.pojo.User;
import com.godfrey.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
/**
-
description : UserServiceImpl
-
@author godfrey
-
@since 2020-05-26
*/
@Service
public class UserServiceImpl implements UserService {
@Autowired
private UserMapper userMapper;@Override
public User selectPasswordByName(String userName, String password) {
return userMapper.selectPasswordByName(userName, password);
}
}测试获取数据情况
@Autowired
EmployeeService employeeService;
@Test
public void test(){
List employees = employeeService.selectAllEmployeeDTO();
for (EmployeeDTO employee : employees) {
System.out.println(employee);
}
}
@Test
public void test2(){
Employee employee = employeeService.selectEmployeeById(1);
System.out.println(employee);
//Employee(id=1, employeeName=张三, email=zhangsan@gmail.com, gender=0, departmentId=1, date=2020-02-12)
}
@Test
public void test3(){
Employee employee = new Employee();
employee.setId(6);
employee.setEmployeeName(“test”);
employee.setEmail(“123@qq.com”);
employee.setDepartmentId(2);
Date date = new Date(2020-02-02);
employee.setDate(date);
employeeService.addEmployee(employee);
Employee employee1 = employeeService.selectEmployeeById(6);
System.out.println(employee1);
//Employee(id=6, employeeName=test, email=123@qq.com, gender=0, departmentId=2, date=1970-01-01)
}
@Test
public void test4(){
Employee employee = new Employee();
employee.setId(6);
employee.setEmployeeName(“test”);
employee.setEmail(“123@qq.com”);
employee.setDepartmentId(3);
Date date = new Date(2020-02-02);
employee.setDate(date);
employeeService.updateEmployee(employee);
Employee employee1 = employeeService.selectEmployeeById(6);
System.out.println(employee1);
//Employee(id=6, employeeName=test, email=123@qq.com, gender=0, departmentId=3, date=1970-01-01)
}
@Test
public void test05(){
employeeService.deleteEmployee(6);
}
@Autowired
private UserService userService;
@Test
public void test06(){
User admin = userService.selectPasswordByName(“admin”,“123456”);
System.out.println(admin);
//User(id=1, name=admin, password=123456)
}
@Autowired
private DepartmentService departmentService;
@Test
public void test07(){
List departments = departmentService.selectAllDepartment();
for (Department department : departments) {
System.out.println(department);
}
}
Controller层
登陆页LoginController
package com.godfrey.controller;
import com.godfrey.pojo.User;
import com.godfrey.service.UserService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import javax.servlet.http.HttpSession;
/**
-
description : 登录Controller
-
@author godfrey
-
@since 2020-05-26
*/
@Controller
public class LoginController {@Autowired
private UserService userService;@RequestMapping(“/login”)
public String login(@RequestParam(“username”)String username,
@RequestParam(“password”)String password,
HttpSession session,
Model model){
User user = userService.selectPasswordByName(username, password);
if ( user != null){
//登录成功!
session.setAttribute(“username”,user.getUserName());
//登录成功!防止表单重复提交,我们重定向
return “redirect:/main.html”;
}else {
//登录失败!存放错误信息
model.addAttribute(“msg”,“用户名或密码错误”);
return “index”;
}
}@GetMapping(“/user/loginOut”)
public String loginOut(HttpSession session){
session.invalidate();
return “redirect:/index.html”;
}
}员工信息Controller
package com.godfrey.controller;
import com.godfrey.dto.EmployeeDTO;
import com.godfrey.pojo.Department;
import com.godfrey.pojo.Employee;
import com.godfrey.service.DepartmentService;
import com.godfrey.service.EmployeeService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import java.util.Collection;
/**
-
description : EmployeeController
-
@author godfrey
-
@since 2020-05-26
*/
@Controller
public class EmployeeController {@Autowired
private EmployeeService employeeService;@Autowired
private DepartmentService departmentService;//查询所有员工,返回列表页面
@GetMapping(“/emp”)
public String list(Model model){
Collection employees = employeeService.selectAllEmployeeDTO();
// 将结果放在请求中
model.addAttribute(“emps”,employees);
return “emp/list.html”;
}//to员工添加页面
@GetMapping(“/add”)
public String toAdd(Model model){
//查出所有的部门,提供选择
Collection departments = departmentService.selectAllDepartment();
model.addAttribute(“departments”,departments);
return “emp/add.html”;
}//员工添加功能,使用post接收
@PostMapping(“/add”)
public String add(Employee employee){
//保存员工信息
employeeService.addEmployee(employee);
//回到员工列表页面,可以使用redirect或者forward
return “redirect:/emp”;
}//to员工修改页面
@GetMapping(“/emp/{id}”)
public String toUpdateEmp(@PathVariable(“id”) Integer id, Model model){
//根据id查出来员工
Employee employee = employeeService.selectEmployeeById(id);
//将员工信息返回页面
model.addAttribute(“emp”,employee);
//查出所有的部门,提供修改选择
Collection departments = departmentService.selectAllDepartment();
model.addAttribute(“departments”,departments);return "emp/update.html";
}
@PostMapping(“/updateEmp”)
public String updateEmp(Employee employee){
employeeService.updateEmployee(employee);
//回到员工列表页面
return “redirect:/emp”;
}@GetMapping(“/delEmp/{id}”)
public String deleteEmp(@PathVariable(“id”)Integer id){
//根据id删除员工
employeeService.deleteEmployee(id);
return “redirect:/emp”;
}
}
完善Config文件
编写Interceptor拦截器配置
package com.godfrey.config;
import org.springframework.web.servlet.HandlerInterceptor;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
/**
- description : Interceptor登录拦截器
- @author godfrey
- @since 2020-05-26
*/
public class MyHandlerInterceptor implements HandlerInterceptor {
@Override
public boolean preHandle(HttpServletRequest request, HttpServletResponse response, Object handler) throws Exception {
Object username = request.getSession().getAttribute(“username”);
if (username == null) {//未登录,返回登录页面
request.setAttribute(“msg”, “没有权限,请先登录”);
request.getRequestDispatcher(“/index.html”).forward(request, response);
return false;
} else {//登录,放行
return true;
}
}
}