Springboot-案例 增删改查二

准备

前端程序、后端工程（web/mybatis/mysql/lombok）、数据库

开发规范

GET：查询
POST：新增
PUT：修改
DELETE：删除
Result.java

@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);
    }
}

示例1：查询 删除 增加部门

@RestController
@Slf4j
//优化请求路径
@RequestMapping("/depts")
public class DeptController {

    @Autowired
    private DeptService deptService;

    @GetMapping
    public Result list(){
        log.info("查询部门信息");
        List<Dept> list = deptService.list();
        return Result.success(list);
    }
    @DeleteMapping("/{id}")
    public Result delete(@PathVariable Integer id){
        log.info("删除部门{}",id);
        deptService.delete(id);
        return Result.success();
    }

    /*
    * 页面请求参数 @RequestBody
    * */
    @PostMapping
    public Result add(@RequestBody Dept dept){
        log.info("新增部门{}",dept);
        deptService.add(dept);
        return Result.success();
    }
}


@Service
public class DeptServiceImpl implements DeptService {
    @Autowired
    private DeptMapper deptMapper;
    @Override
    public List<Dept> list() {
        return deptMapper.list();
    }

    @Override
    public void delete(Integer id) {
        deptMapper.delete(id);
    }

    @Override
    public void add(Dept dept) {
        dept.setCreateTime(LocalDateTime.now());
        dept.setUpdateTime(LocalDateTime.now());
        deptMapper.add(dept);
    }
}


public interface DeptService {
    List<Dept> list();

    void delete(Integer id);

    void add(Dept dept);
}

@Mapper
public interface DeptMapper {
    @Select("select * from dept")
    List<Dept> list();

    @Delete("delete from dept where id =#{id}")
    void delete(Integer id);

   @Insert("insert into dept(name,create_time,update_time) values (#{name},#{createTime},#{updateTime} )" )
    void add(Dept dept);
}

示例2：员工管理

2.1分页查询（SQL语句查询）

-- 分页查询
-- 参数1:起始索引 = (页码-1)*每页需要展示的数据数
-- 参数2 : 每页展示的数据数
select * from emp limit 0,5;
select * from emp limit 5,5;

@Mapper
public interface EmpMapper {
    @Select("select count(*) from emp")
    public Long count();

    /**
     * 分页查询
     * @param start
     * @param pageSize
     * @return
     */
    @Select("select * from emp limit #{start},#{pageSize}")
    public List<Emp> page(Integer start,Integer pageSize);
}

@Service
public class EmpServiceImpl implements EmpService {
    @Autowired
    private EmpMapper empMapper;
    @Override
    public PageBean page(Integer page, Integer pageSize) {
        PageBean pageBean = new PageBean();
        pageBean.setTotal(empMapper.count());
        pageBean.setRows(empMapper.page((page-1)*pageSize,pageSize));
        return pageBean;
    }
}

分页插件 pageHelper

		<dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper-spring-boot-starter</artifactId>
            <version>1.4.3</version>
        </dependency>

 @Override
    public PageBean page(Integer page, Integer pageSize) {
        PageHelper.startPage(page,pageSize);
        List<Emp> list = empMapper.list();
        Page<Emp> p= (Page<Emp>) list;
        return new PageBean(p.getTotal(),p.getResult());
    }

2.2 条件分页查询员工（动态SQL）

select * from emp where username like concat('%','朱','%') and gender =1 and entrydate
    between '2023-10-01' and '2023-10-20' order by update_time desc ;

@RestController
@Slf4j
public class EmpController {
    @Autowired
    private EmpService empService;
    //分页查询 分页参数 page没传递默认1  pageSize 默认10 日期参数 前面加 @DateTimeFormat(pattern = "yyyy-MM-dd")
    @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("分页查询,参数{},{},{},{},{},{}",page,pageSize,name,gender,begin,end);
        PageBean pageBean = empService.page(page,pageSize,name,gender,begin,end );
        return Result.success(pageBean);
    }
    
}

@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) {
        PageHelper.startPage(page,pageSize);
        List<Emp> list = empMapper.list(name, gender, begin, end);
        Page<Emp> p= (Page<Emp>) list;
        return new PageBean(p.getTotal(),p.getResult());
    }

}

public interface EmpService {
    PageBean page(Integer page, Integer pageSize ,String name, Short gender, LocalDate begin, LocalDate end);
}

@Mapper
public interface EmpMapper {
    public List<Emp> list(String name, Short gender, LocalDate begin, LocalDate end);
}

xml
    <select id="list" resultType="com.example.demo.pojo.Emp">
        select *
        from emp
        <where>
            <if test="name != null and name!='' ">
                username 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>
            order by update_time desc
        </where>

    </select>

POST 请求错误400

[nio-8080-exec-3] .w.s.m.s.DefaultHandlerExceptionResolver : Resolved [org.springframework.web.method.annotation.MethodArgumentTypeMismatchException: Failed to convert value of type 'java.lang.String' to required type 'java.time.LocalDate'; nested exception is org.springframework.core.convert.ConversionFailedException: Failed to convert from type [java.lang.String] to type [@org.springframework.format.annotation.DateTimeFormat java.time.LocalDate] for value [2023-10-1]; nested exception is java.lang.IllegalArgumentException: Parse attempt failed for value [2023-10-1]]

注意：postman请求发送日期：严格要求yyyy-MM-dd 格式 2023-10-01

2.3 新增员工

 @PostMapping
    public Result save(@RequestBody Emp emp){
        log.info("新增员工:{}",emp);
        empService.save(emp);
        return Result.success();
    }

@Insert("insert into emp (username,password,gender,image,job,entrydate,dept_id,create_time,update_time) values (#{username},#{password},#{gender},#{image},#{job},#{entrydate},#{deptId},#{createTime},#{updateTime})")
    void insert(Emp emp);

文件上传

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>文件上传</title>
</head>
<body>
<form action="/upload" method="post" enctype="multipart/form-data">
    姓名:<input type="text" name="username"><br>
    年龄:<input type="text" name="age"><br>
    头像:<input type="file" name="image"><br>
    <input type="submit" value="提交">
</form>
</body>
</html>

本地存储

@RestController
@Slf4j
public class UploadController {
    @PostMapping("/upload")
    public Result upload(String username, Integer age, MultipartFile image) throws IOException {
        log.info("文件上传 {},{},{}",username,age,image);
        String name = image.getOriginalFilename();//原始文件名
        String s = UUID.randomUUID().toString();
        image.transferTo(new File("E:\\"+s+name.substring(name.lastIndexOf("."))));
        return Result.success();
    }
}

在Springboot 文件上传默认单个最大文件大小为1M。需要上传大文件，配置:

#单个最大上传文件大小
spring.servlet.multipart.max-file-size=10MB
#单个请求最大上传大小（一次可上传多个）
spring.servlet.multipart.max-request-size=100MB

阿里云存储oss

依赖

<dependency>
    <groupId>com.aliyun.oss</groupId>
    <artifactId>aliyun-sdk-oss</artifactId>
    <version>3.15.1</version>
</dependency>

java 9.0以上
<dependency>
    <groupId>javax.xml.bind</groupId>
    <artifactId>jaxb-api</artifactId>
    <version>2.3.1</version>
</dependency>
<dependency>
    <groupId>javax.activation</groupId>
    <artifactId>activation</artifactId>
    <version>1.1.1</version>
</dependency>
<!-- no more than 2.3.3-->
<dependency>
    <groupId>org.glassfish.jaxb</groupId>
    <artifactId>jaxb-runtime</artifactId>
    <version>2.3.3</version>
</dependency>

@Component
public class AliOSSUtils {
    private String endpoint = "https://oss-cn-hangzhou.aliyuncs.com";
    private String accessKeyId = "";
    private String accessKeySecret = "";
    private String bucketName = "";

    public String upload(MultipartFile file) throws IOException {
        //获取上传文件输入流
        InputStream inputStream = file.getInputStream();
        //避免文件覆盖
        String originalFilename = file.getOriginalFilename();
        String fileName = UUID.randomUUID().toString()+originalFilename.substring(originalFilename.lastIndexOf("."));

        OSS ossClient = new OSSClientBuilder().build(endpoint, accessKeyId,accessKeySecret);
        ossClient.putObject(bucketName,fileName,inputStream);
        String url = endpoint.split("//")[0]+"//"+bucketName+"."+endpoint.split("//")[1]+"/"+fileName;
        ossClient.shutdown();
        return url;

    }
}
 @Autowired
    private AliOSSUtils aliOSSUtils;
    @PostMapping("/upload")
    public Result upload( MultipartFile image) throws IOException {
        String url = aliOSSUtils.upload(image);
        return Result.success(url);
    }

2.4 修改员工

查找

 //路径参数 id 用@PathVariable
    @GetMapping("/{id}")
    public Result getById(@PathVariable Integer id){
        Emp emp = empService.getById(id);
        return Result.success(emp);
    }
    
 	@Override
    public Emp getById(Integer id) {
        return empMapper.getById(id);

    }
    
    Emp getById(Integer id);

    @Select("select * from emp where id=#{id}")
    Emp getById(Integer id);

修改

	//根据id根新员工信息
    @PutMapping
    public Result updateById(@RequestBody Emp emp){
        empService.updateById(emp);
        return Result.success();
    }
 	@Override
    public void updateById(Emp emp) {
        emp.setUpdateTime(LocalDateTime.now());
        empMapper.updateById(emp);
    }
    void updateById(Emp emp);

void updateById(Emp emp);
<update id="updateById">
        update emp
        <set>
            <if test="username != null and username !=''">
                username = #{username},
            </if>
            <if test="password != null and  password !=''">
                password = #{password},
            </if>
            <if test="gender !=null ">
                gender = #{gender},
            </if>
            <if test="image != null and  image !=''">
                image = #{image},
            </if>
            <if test="job!=null">
                job = #{job},
            </if>
            <if test="entrydate != null">
                entrydate = #{entrydate},
            </if>
            <if test="deptId != null">
                dept_id = #{deptId},
            </if>
            <if test="updateTime != null">
                update_time = #{updateTime}
            </if>
        </set>
        where id = #{id}
    </update>

参数配置 properties

yml配置

application.yml 或者application.yaml

server:
  port: 9000

这里插入图片描述](https://img-blog.csdnimg.cn/5ae2da4c42e44a86be204497c3719ac8.png)

yml语法

#定义对象 /Map集合
user:
  name: 明太祖
  age: 22

# 数组 List set
hobby:
  - sing
  - jump
  - rap
  - basketball

spring properties文件替换yml

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/mybatis?useSSL=false&characterEncoding=UTF-8
spring.datasource.username=root
spring.datasource.password=1234
mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl
mybatis.configuration.map-underscore-to-camel-case=true

spring.servlet.multipart.max-file-size=10MB
spring.servlet.multipart.max-request-size=100MB
aliyun.oss.endpoint=https://oss-cn-hangzhou.aliyuncs.com
aliyun.oss.accessKeyId=
aliyun.oss.accessKeySecret=
aliyun.oss.bucketName=

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/mybatis?useSSL=false&characterEncoding=UTF-8
    username: root
    password: 1234
  servlet:
    multipart:
      max-file-size: 10MB
      max-request-size: 100MB
#log-impl 日志 cam 驼峰命名
mybatis:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
    map-underscore-to-camel-case: true

#文件上传 multipa

#自定义
aliyun:
  oss:
    endpoint: https://oss-cn-hangzhou.aliyuncs.com
    accessKeyId: 
    accessKeySecret: 
    bucketName: 

@ConfigurationProperties

@value(“${ }”)太繁琐

使用示例:

@Component
public class AliOSSUtils {
    @Value("${aliyun.oss.endpoint}")
    private String endpoint;
    @Value("${aliyun.oss.accessKeyId}")
    private String accessKeyId ;
    @Value("${aliyun.oss.accessKeySecret}")
    private String accessKeySecret ;
    @Value("${aliyun.oss.bucketName}")
    private String bucketName ;

    public String upload(MultipartFile file) throws IOException {
        //获取上传文件输入流
        InputStream inputStream = file.getInputStream();
        //避免文件覆盖
        String originalFilename = file.getOriginalFilename();
        String fileName = UUID.randomUUID().toString()+originalFilename.substring(originalFilename.lastIndexOf("."));

        OSS ossClient = new OSSClientBuilder().build(endpoint, accessKeyId,accessKeySecret);
        ossClient.putObject(bucketName,fileName,inputStream);
        String url = endpoint.split("//")[0]+"//"+bucketName+"."+endpoint.split("//")[1]+"/"+fileName;
        ossClient.shutdown();
        return url;

    }
}

@Component
public class AliOSSUtils {
    @Autowired
    private AliOSSProperties aliOSSProperties;
    
    public String upload(MultipartFile file) throws IOException {
        String endpoint = aliOSSProperties.getEndpoint();
        String accessKeyId = aliOSSProperties.getAccessKeyId();
        String accessKeySecret = aliOSSProperties.getAccessKeySecret();
        String bucketName = aliOSSProperties.getBucketName();
        //获取上传文件输入流
        InputStream inputStream = file.getInputStream();
        //避免文件覆盖
        String originalFilename = file.getOriginalFilename();
        String fileName = UUID.randomUUID().toString()+originalFilename.substring(originalFilename.lastIndexOf("."));

        OSS ossClient = new OSSClientBuilder().build(endpoint, accessKeyId,accessKeySecret);
        ossClient.putObject(bucketName,fileName,inputStream);
        String url = endpoint.split("//")[0]+"//"+bucketName+"."+endpoint.split("//")[1]+"/"+fileName;
        ossClient.shutdown();
        return url;

    }
}

@Data
@Component
@ConfigurationProperties(prefix = "aliyun.oss")
public class AliOSSProperties {
    private String endpoint;
    private String accessKeyId;
    private String accessKeySecret;
    private String bucketName;

}

警告

引入依赖

	<dependency>
           <groupId>org.springframework.boot</groupId>
           <artifactId>spring-boot-configuration-processor</artifactId>
     </dependency>