springboot整合mybatis小练习 对员工表的增删改查【连表分页】

对员工表的增删改查【连表分页】
测试接口

项目结构

导入依赖

 <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")