对员工表的增删改查【连表分页】
测试接口
项目结构
导入依赖
<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
</dependency>
<dependency>
<groupId>org.mybatis.spring.boot</groupId>
<artifactId>mybatis-spring-boot-starter</artifactId>
<version>2.2.0</version>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<scope>runtime</scope>
</dependency>
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<optional>true</optional>
</dependency>
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid-spring-boot-starter</artifactId>
<version>1.2.1</version>
</dependency>
<dependency>
<groupId>com.github.pagehelper</groupId>
<artifactId>pagehelper-spring-boot-starter</artifactId>
<version>1.2.13</version>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<scope>test</scope>
</dependency>
</dependencies>
application.yml
#配置端口号
server:
port: 8080
# 配置druid的信息
spring:
datasource:
druid:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/emp?serverTimezone=Asia/Shanghai
username: root
password: root
max-active: 20
initial-size: 5
min-idle: 5
max-wait: 3000
main:
allow-circular-references: true
# 配置mybatis映射文件所在的路径
mybatis:
mapper-locations: classpath:mapper/*.xml
# mybatis输入sql语句
logging:
level:
com.ccr.dao: debug
#pageHelper
pagehelper:
reasonable: true #分页合理化
EmpMapper.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.ccr.dao.EmpDao">
<resultMap id="empMap" type="com.ccr.entity.Emp">
<id property="empId" column="empId"/>
<result property="empName" column="empName"/>
<result column="gender" property="gender" />
<result column="email" property="email" />
<result column="d_Id" property="dId" />
<association property="dId" javaType="com.ccr.entity.Dept">
<id property="deptId" column="deptId"/>
<result property="deptName" column="deptName"/>
</association>
</resultMap>
<!--根据id查询-->
<select id="findById" resultMap="empMap">
SELECT e.empId, e.empName,e.gender,e.email,e.d_Id from tbl_emp e
INNER JOIN tbl_dept t
on t.deptId=e.d_Id
where e.empId=#{empId}
</select>
<!-- 查询所有分页-->
<select id="findAll" resultMap="empMap">
SELECT e.empId, e.empName,e.gender,e.email,e.d_Id from tbl_emp e
INNER JOIN tbl_dept t
on t.deptId=e.d_Id
</select>
<!-- 添加
-->
<insert id="addEmp">
insert into tbl_emp(empName,gender,email,d_Id) values (#{empName},#{gender},#{email},#{dId})
</insert>
<!-- 修改
update tbl_emp set empName=#{empName},gender=#{gender},email=#{email},d_Id=#{dId} where empId=#{empId}
-->
<update id="upEmp">
update tbl_emp set empName=#{empName},gender=#{gender},email=#{email},d_Id=#{dId} where empId=#{empId}
</update>
<!-- 根据id删除-->
<delete id="delEmp">
delete from tbl_emp where empId=#{empId}
</delete>
</mapper>
Emp表
package com.ccr.entity;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.springframework.stereotype.Component;
@Data
@NoArgsConstructor
@AllArgsConstructor
//配置文件前和赋值个的属性名相同
public class Emp {
private Integer empId;
private String empName;
private char gender;
private String email;
private Integer dId;
private Dept dept;
}
Dept表
package com.ccr.entity;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@NoArgsConstructor
@AllArgsConstructor
public class Dept {
private Integer deptId;
private String deptName;
}
Util中的CommonResult
package com.ccr.util;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
@Data
@NoArgsConstructor
@AllArgsConstructor
public class CommonResult {
private Integer code;
private String msg;
private Object data;
}
service
package com.ccr.service;
import com.ccr.entity.Emp;
import com.ccr.util.CommonResult;
public interface EmpService {
// 根据id查询信息
public CommonResult selectById(Integer empId );
// 查询所有分页
public CommonResult selectByPage(Integer page,Integer limit);
// 添加信息
public CommonResult addEmp(Emp emp);
// 修改
public CommonResult upEmp(Emp emp);
// 删除
public CommonResult delEmp(Integer empId);
}
service下的impl的EmpServiceimpl
package com.ccr.service.impl;
import com.ccr.dao.EmpDao;
import com.ccr.entity.Emp;
import com.ccr.service.EmpService;
import com.ccr.util.CommonResult;
import com.github.pagehelper.Page;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import javax.annotation.Resource;
import java.util.List;
@Service(value = "EmpService")
public class EmpServiceimpl implements EmpService {
@Resource
public EmpDao empDao;
@Override
public CommonResult selectById(Integer empId) {
Emp eId = empDao.findById(empId);
return new CommonResult(200,"查询成功",eId);
}
@Override
public CommonResult selectByPage(Integer page, Integer limit) {
PageHelper.startPage(page,limit);
List<Emp> all = empDao.findAll();
PageInfo<Emp> empPageInfo = new PageInfo<>(all);
return new CommonResult(200,"查询成功",empPageInfo);
}
@Override
public CommonResult addEmp(Emp emp) {
int add = empDao.addEmp(emp);
return new CommonResult(200,"添加成功",add);
}
@Override
public CommonResult upEmp(Emp emp) {
int up = empDao.upEmp(emp);
return new CommonResult(200,"修改成功",up);
}
@Override
public CommonResult delEmp(Integer empId) {
int delEmp = empDao.delEmp(empId);
return new CommonResult(200,"删除成功",delEmp);
}
}
dao中的EmpDao
package com.ccr.dao;
import com.ccr.entity.Emp;
import java.util.List;
public interface EmpDao {
// 根据id查询
public Emp findById(int empId);
// 查询所有
public List<Emp> findAll();
// 添加
public int addEmp(Emp emp);
// 修改
public int upEmp(Emp emp);
// 删除
public int delEmp(Integer empId);
}
controller中的EmpController
package com.ccr.controller;
import com.ccr.entity.Emp;
import com.ccr.service.EmpService;
import com.ccr.util.CommonResult;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
@RestController
@RequestMapping("emp")
public class EmpController {
@Autowired
private EmpService empService;
// 根据id查询信息
@GetMapping("/getById")
public CommonResult getById(Integer empId){
return empService.selectById(empId);
}
// 查询所有信息分页
@GetMapping("/getAll")
public CommonResult getAll(Integer page,Integer limit){
return empService.selectByPage(page,limit);
}
// 添加
@PostMapping("/addEmp")
public CommonResult addEmp(Emp emp){
return empService.addEmp(emp);
}
// 修改
@PutMapping("upEmp")
public CommonResult upEmp(Emp emp){
return empService.upEmp(emp);
}
// 删除
@DeleteMapping("delEmp")
public CommonResult delEmp(Integer empId){
return empService.delEmp(empId);
}
}
启动的测试类
package com.ccr;
import org.mybatis.spring.annotation.MapperScan;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
@SpringBootApplication
//为指定包下的接口生产代理实现类
@MapperScan(basePackages = "com.ccr.dao")
public class SpringbootMybatisApplication {
public static void main(String[] args) {
SpringApplication.run(SpringbootMybatisApplication.class, args);
}
}
测试结果
根据id查询的员工信息
查询所有分页
添加员工信息
修改员工信息
删除员工信息
注解总结：
//为指定包下的接口生产代理实现类
@MapperScan(basePackages = "com.ykq.dao")
// 删除
@DeleteMapping("delEmp")
// 修改
@PutMapping("upEmp")
// 添加
@PostMapping("/addEmp")
// 查询所有信息分页
@GetMapping("/getAll")