目录
<<回到导览
Mybatis综合案例-部门、员工管理
1.准备工作
1.1.环境搭建
-
准备数据库表(dept、emp)
-- 部门管理 create table dept( id int unsigned primary key auto_increment comment '主键ID', name varchar(10) not null unique comment '部门名称', create_time datetime not null comment '创建时间', update_time datetime not null comment '修改时间' ) comment '部门表'; insert into dept (id, name, create_time, update_time) values(1,'学工部',now(),now()),(2,'教研部',now(),now()),(3,'咨询部',now(),now()), (4,'就业部',now(),now()),(5,'人事部',now(),now()); -- 员工管理(带约束) create table emp ( id int unsigned primary key auto_increment comment 'ID', username varchar(20) not null unique comment '用户名', password varchar(32) default '123456' comment '密码', name varchar(10) not null comment '姓名', gender tinyint unsigned not null comment '性别, 说明: 1 男, 2 女', image varchar(300) comment '图像', job tinyint unsigned comment '职位, 说明: 1 班主任,2 讲师, 3 学工主管, 4 教研主管, 5 咨询师', entrydate date comment '入职时间', dept_id int unsigned comment '部门ID', create_time datetime not null comment '创建时间', update_time datetime not null comment '修改时间' ) comment '员工表'; INSERT INTO emp (id, username, password, name, gender, image, job, entrydate,dept_id, create_time, update_time) VALUES (1,'jinyong','123456','金庸',1,'1.jpg',4,'2000-01-01',2,now(),now()), (2,'zhangwuji','123456','张无忌',1,'2.jpg',2,'2015-01-01',2,now(),now()), (3,'yangxiao','123456','杨逍',1,'3.jpg',2,'2008-05-01',2,now(),now()), (4,'weiyixiao','123456','韦一笑',1,'4.jpg',2,'2007-01-01',2,now(),now()), (5,'changyuchun','123456','常遇春',1,'5.jpg',2,'2012-12-05',2,now(),now()), (6,'xiaozhao','123456','小昭',2,'6.jpg',3,'2013-09-05',1,now(),now()), (7,'jixiaofu','123456','纪晓芙',2,'7.jpg',1,'2005-08-01',1,now(),now()), (8,'zhouzhiruo','123456','周芷若',2,'8.jpg',1,'2014-11-09',1,now(),now()), (9,'dingminjun','123456','丁敏君',2,'9.jpg',1,'2011-03-11',1,now(),now()), (10,'zhaomin','123456','赵敏',2,'10.jpg',1,'2013-09-05',1,now(),now()), (11,'luzhangke','123456','鹿杖客',1,'11.jpg',5,'2007-02-01',3,now(),now()), (12,'hebiweng','123456','鹤笔翁',1,'12.jpg',5,'2008-08-18',3,now(),now()), (13,'fangdongbai','123456','方东白',1,'13.jpg',5,'2012-11-01',3,now(),now()), (14,'zhangsanfeng','123456','张三丰',1,'14.jpg',2,'2002-08-01',2,now(),now()), (15,'yulianzhou','123456','俞莲舟',1,'15.jpg',2,'2011-05-01',2,now(),now()), (16,'songyuanqiao','123456','宋远桥',1,'16.jpg',2,'2007-01-01',2,now(),now()), (17,'chenyouliang','123456','陈友谅',1,'17.jpg',NULL,'2015-03-21',NULL,now(),now()); -
创建springboot工程，引入对应的起步依赖(web、mybatis、mysq（驱动、lombok)
-
配置文件application.properties中引入mybatis的配置信息，准备对应的实体类
-
准备对应的Mapper、Service（接口、实现类）)、Controller基础结构
// Dept @Data @NoArgsConstructor @AllArgsConstructor public class Dept { private Integer id; private String name; private LocalDateTime createTime; private LocalDateTime updateTime; }// Emp public class Emp { private Integer id; private String username; private String password; private String name; private String gender; private String image; private Short job; private LocalDate entrydate; private Integer deptId; private LocalDateTime createTime; private LocalDateTime updateTime; }
1.2.开发规范
-
rest (REpresentational State Transfer)，表述性状态转换，它是一种软件架构风格
描述功能模块通常使用复数，表示此类资源
操作类型 对应请求方式 GET 查 POST 增 PUT 改 DELETE 删
-
前后端交互统一响应结果(pojo/Result.java)
package com.jia.pojo; import lombok.AllArgsConstructor; import lombok.Data; import lombok.NoArgsConstructor; @Data @NoArgsConstructor @AllArgsConstructor public class Result { private Integer code;//响应码，1 代表成功; 0 代表失败 private String msg; //响应信息 描述字符串 private 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); } }
2.部门管理
2.1.查询部门
-
DeptController
@Slf4j // logback记录日志 @RestController public class DeptController { @RequestMapping("/depts") public Result delete() { log.info("查询全部部门数据"); return Result.success(); } }查询成功
logback控制台输出
-
controller: 指定请求方式为Get，并调用service@Slf4j // logback记录日志 @RestController public class DeptController { @Autowired private DeptService deptService; // @RequestMapping(value = "/depts", method = GET) @GetMapping("/depts") // 衍生注解 public Result DeptController() { log.info("查询全部部门数据"); List<Dept> deptList = deptService.list(); return Result.success(deptList); } } -
service：调用mapper查询数据public interface DeptService { // 查询全部部门数据 List<Dept> list(); }// impl @Service public class DeptServiceImpl implements DeptService { @Autowired private DeptMapper deptMapper; @Override public List<Dept> list() { return deptMapper.list(); } } -
mapper：查询数据@Mapper public interface DeptMapper { // 查询全部部门数据 @Select("select * from dept") List<Dept> list(); }
2.2.前后端联调
-
启动nginx前端服务
浏览器访问http://localhost:90/
数据显示成功：
2.3.删除部门
-
controller利用@PathVariable注解获取路径参数
@DeleteMapping("/depts/{id}") public Result delete(@PathVariable Integer id) { log.info("根据id删除部门：{}", id); deptService.delete(id); return Result.success(); } -
servicepublic interface DeptService { // 删除部门 void delete(Integer id); }// impl @Service public class DeptServiceImpl implements DeptService { @Autowired private DeptMapper deptMapper; @Override public void delete(Integer id) { deptMapper.deleteById(id); } } -
mapper@Mapper public interface DeptMapper { // 根据id删除部门 @Delete("delete from dept where id = #{id}") void deleteById(Integer id); }
2.4.新增部门
-
controller@Slf4j // logback记录日志 @RestController public class DeptController { @Autowired private DeptService deptService; @PostMapping("/depts") public Result insert(@RequestBody Dept dept) { log.info("添加部门"); deptService.insert(dept); return Result.success(); } } -
servicepublic interface DeptService { // 添加 void insert(Dept dept); }// impl @Service public class DeptServiceImpl implements DeptService { @Autowired private DeptMapper deptMapper; @Override public void insert(Dept dept) { dept.setCreateTime(LocalDateTime.now()); dept.setUpdateTime(LocalDateTime.now()); deptMapper.insert(dept); } } -
mapper@Mapper public interface DeptMapper { // 插入部门 @Insert("insert into dept( name, create_time, update_time) value (#{name}, #{createTime}, #{updateTime})") void insert(Dept dept); }
3.员工管理
3.1.分页查询
-
前端传递的参数
当前页码：page
每页记录数：pageSize
-
后台给前端返回的数据：
数据列表
总记录数
-
创建PageBean类
@Data @AllArgsConstructor @NoArgsConstructor public class PageBean { private Long total; // 总记录数 private List<Emp> rows; // 返回的数据列表 }
-
controller@Slf4j @RestController public class EmpController { @Autowired private EmpService empService; // 分页查询 @GetMapping("/emps") public Result page(@RequestParam(defaultValue = "1") Integer page, @RequestParam(defaultValue = "5") Integer pageSize) { log.info("分页查询员工数据"); PageBean pageBean = empService.page(page, pageSize); return Result.success(pageBean); } } -
servicepublic interface EmpService { PageBean page(Integer page, Integer pageSize); }// impl @Service public class EmpServiceImpl implements EmpService { @Autowired private EmpMapper empMapper; @Override public PageBean page(Integer page, Integer pageSize) { // 1.获取总记录数 long count = empMapper.count(); // 2.获取分页查询结果列表 Integer start = (page - 1) * pageSize; List<Emp> empList = empMapper.page(start, pageSize); // 3.封装PageBean对象 System.out.println("111111111111111111" + empList); return new PageBean(count, empList); } } -
mapper@Mapper public interface EmpMapper { @Select("select count(*) from emp") long count(); @Select("select * from emp limit #{start},#{pageSize}") List<Emp> page(Integer start, Integer pageSize); }
3.2.PageHelper插件
配置分页插件依赖
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.4.7</version>
</dependency>
-
通过PageHelper插件改写分页查询代码
service@Override public PageBean page(Integer page, Integer pageSize) { // 1.设置分页参数 PageHelper.startPage(page, pageSize); // 2.执行查询 List<Emp> empList = empMapper.empList(); Page<Emp> p = (Page<Emp>) empList; // 3.封装PageBean对象 return new PageBean(p.getTotal(), p.getResult()); }mapper@Mapper public interface EmpMapper { @Select("select * from emp") List<Emp> empList(); }
3.3.分页条件查询
-
controller@Slf4j @RestController public class EmpController { @Autowired private EmpService empService; // 分页查询 @GetMapping("/emps") public Result page(@RequestParam(defaultValue = "1") Integer page, @RequestParam(defaultValue = "5") Integer pageSize, String name, Short gender, @DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate begin, @DateTimeFormat(pattern = "yyyy-MM-dd") LocalDate end) { log.info("分页查询员工数据"); PageBean pageBean = empService.page(page, pageSize, name, gender, begin, end); return Result.success(pageBean); } } -
servicepublic interface EmpService { PageBean page(Integer page, Integer pageSize, String name, Short gender, LocalDate begin, LocalDate end); }// impl @Service public class EmpServiceImpl implements EmpService { @Autowired private EmpMapper empMapper; @Override public PageBean page(Integer page, Integer pageSize, String name, Short gender, LocalDate begin, LocalDate end) { // 1.设置分页参数 PageHelper.startPage(page, pageSize); // 2.执行查询 List<Emp> empList = empMapper.empList(name, gender, begin, end); Page<Emp> p = (Page<Emp>) empList; // 3.封装PageBean对象 return new PageBean(p.getTotal(), p.getResult()); } } -
mapper@Mapper public interface EmpMapper { @Select("select * from emp where name like concat('%', #{name},'%') and gender = #{gender} and entrydate between #{begin} and #{end} order by update_time desc") List<Emp> empList(String name, Short gender, LocalDate begin, LocalDate end); }由于查询语句比较复杂，我们还可以将查询语句写入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.jia.mapper.EmpMapper"> <!-- resultType：单条记录所封装的类型 --> <select id="empList" resultType="com.jia.pojo.Emp"> select * from emp <where> <if test="name != null and name = ''"> name like concat('%', #{name},'%') </if> <if test="gender != null"> and gender = #{gender} </if> <if test="begin != null and end != null"> and entrydate between #{begin} and #{end} </if> </where> order by update_time desc </select> </mapper>
3.4.删除员工
-
controller@Slf4j @RestController public class EmpController { @Autowired private EmpService empService; @DeleteMapping("/emps/{ids}") public Result delete(@PathVariable List<Integer> ids) { log.info("批量删除操作, ids={}", ids); empService.deleteByIds(ids); return Result.success(); } } -
servicepublic interface EmpService { void deleteByIds(List<Integer> ids); }// impl @Override public void deleteByIds(List<Integer> ids) { empMapper.deleteByIds(ids); } -
mapper@Mapper public interface EmpMapper { void deleteByIds(List<Integer> ids); }<?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.jia.mapper.EmpMapper"> <delete id="deleteByIds"> delete from emp where id in <foreach collection="ids" item="id" separator="," open="(" close=")"> #{id} </foreach> </delete> </mapper>
3.5.新增员工
-
controller@PostMapping("/emps") public Result add(@RequestBody Emp emp) { empService.add(emp); return Result.success(); } -
servicepublic interface EmpService { void add(Emp emp); }@Service public class EmpServiceImpl implements EmpService { @Override public void add(Emp emp) { emp.setCreateTime(LocalDateTime.now()); emp.setUpdateTime(LocalDateTime.now()); empMapper.add(emp); } } -
mapper@Mapper public interface EmpMapper { void add(Emp emp); }<?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.jia.mapper.EmpMapper"> <insert id="add"> insert into emp (username, name, gender, image, job, entrydate, dept_id, create_time, update_time) values (#{username}, #{name}, #{gender}, #{image}, #{job}, #{entrydate}, #{deptId}, #{createTime}, #{updateTime}) </insert> </mapper>
3.6.根据id查询
-
controller@Slf4j @RestController public class EmpController { @Autowired private EmpService empService; @GetMapping("/emps/{id}") public Result getById(@PathVariable Integer id) { log.info("根据ID查询员工信息"); return Result.success(empService.getById(id)); } } -
servicepublic interface EmpService { Emp getById(Integer id); }// impl @Service public class EmpServiceImpl implements EmpService { @Autowired private EmpMapper empMapper; @Override public Emp getById(Integer id) { return empMapper.getById(id); } } -
mapper@Mapper public interface EmpMapper { @Select("select * from emp where id = #{id}") Emp getById(Integer id); }
3.7.修改员工
-
controller@Slf4j @RestController public class EmpController { @Autowired private EmpService empService; @PutMapping("/emps") public Result update(@RequestBody Emp emp) { empService.update(emp); return Result.success(); } } -
servicepublic interface EmpService { void update(Emp emp); }// xml @Service public class EmpServiceImpl implements EmpService { @Autowired private EmpMapper empMapper; @Override public void update(Emp emp) { empMapper.update(emp); } } -
mapper@Mapper public interface EmpMapper { void update(Emp emp); }<update id="update"> update emp <set> <if test="username != null">username = #{username},</if> <if test="password != null">password = #{password},</if> <if test="name != null">name = #{name},</if> <if test="gender != null">gender = #{gender},</if> <if test="image != null">image = #{image},</if> <if test="job != null">job = #{job},</if> <if test="job != null">entrydate = #{job},</if> <if test="deptId != null">dept_id = #{deptId},</if> <if test="updateTime != null">update_time = #{updateTime}</if> </set> where id = #{id} </update>
扫一扫
693
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
