SpringBoot教程（二）使用Mybatis实现增删改查

接着上回SpringBoot教程（一）RESTful API

确保application.properties文件作了数据库相关配置

spring.datasource.driver-class-name = com.mysql.cj.jdbc.Driver

spring.datasource.url = jdbc:mysql://192.168.1.1:3306/curd
spring.datasource.username = root
spring.datasource.password = 123456

数据表ad

CREATE TABLE `ad`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci ROW_FORMAT = Dynamic;

创建实体类Ad.java

package com.example.curd.domain;


import javax.validation.constraints.*;
import java.util.Date;


public class Ad {
    private Integer id;
    @NotBlank(message = "名称不能为空")
    private String name;

    private Date createTime;

    public Ad() {
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Date getCreateTime() {
        return createTime;
    }

    public void setCreateTime(Date createTime) {
        this.createTime = createTime;
    }

}

这里的Ad类做了验证处理，所以pom.xml文件要加入spring-boot-starter-validation依赖

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-validation</artifactId>
        </dependency>

创建Ad的服务接口AdService.java

package com.example.curd.service;

import com.example.curd.domain.Ad;

import java.util.List;

public interface AdService {
    //新增
    Integer insert(Ad ad);
    //更新
    Integer update(Ad ad);
    //删除
    Integer delete(Integer id);

    //查询全部
    List<Ad> findAll();

    //根据id查询
    Ad findById(Integer id);
    //根据name查询
    Ad findByName(String name);
}

创建Ad的服务实现类AdServiceImpl.java

package com.example.curd.serviceimpl;


import com.example.curd.domain.Ad;
import com.example.curd.mapper.AdMapper;
import com.example.curd.service.AdService;

import org.springframework.stereotype.Service;
import java.util.List;

@Service("AdServiceImpl")
public class AdServiceImpl implements AdService {

    AdMapper adMapper;

    public AdServiceImpl(AdMapper adMapper) {
        this.adMapper = adMapper;
    }
    @Override
    public Integer insert(Ad ad) {
        return adMapper.insert(ad);
    }

    @Override
    public Integer update(Ad ad) {
        return adMapper.update(ad);
    }

    @Override
    public Integer delete(Integer id) {
        return adMapper.delete(id);
    }

    @Override
    public List<Ad> findAll() {
        //throw new CustomException(ResultCode.DATA_ALREADY_EXISTED);
        //return adMapper.findAll();
        return adMapper.all();
    }

    @Override
    public Ad findById(Integer id) {
        return adMapper.findById(id);
    }

    @Override
    public Ad findByName(String name) {
        return null;
    }

}

Ad的Mybatis映射类AdMapper.java

package com.example.curd.mapper;

import com.example.curd.domain.Ad;
import com.example.curd.provider.AdProvider;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface AdMapper {
    //新增
    @Insert("INSERT INTO `ad` (name,create_time) VALUES (#{name},now())")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    Integer insert(Ad ad);
    //更新
    @Update("UPDATE `ad` set name = #{name} WHERE id = #{id}")
    Integer update(Ad ad);
    //删除数据
    @Delete("DELETE FROM `ad` WHERE id=#{id}")
    Integer delete(Integer id);
    //查询全部
    @Select("SELECT * FROM `ad`")
    List<Ad> findAll();

    //查询全部(使用Provider方式）
    @SelectProvider(type = AdProvider.class, method = "all")
    List<Ad> all();

    //根据id查找
    @Select("SELECT * FROM `ad` WHERE id = #{id}")
    Ad findById(Integer id);

}

Ad的Provider类AdProvider.java

package com.example.curd.provider;

import org.apache.ibatis.jdbc.SQL;

public class AdProvider {
    public String all() {
        //return "SELECT * FROM `ad` WHERE 1";
        //动态方式
        return new SQL() {
            {
                SELECT("*");
                FROM("ad");
                WHERE("1");
            }
        }.toString();
    }
}

Ad的控制器类AdController.java

package com.example.curd.controller;

import com.example.curd.domain.Ad;
import com.example.curd.service.AdService;
import org.springframework.validation.annotation.Validated;
import org.springframework.web.bind.annotation.*;

import javax.annotation.Resource;

import java.util.List;


@RestController
@RequestMapping("/ad")
public class AdController {

    @Resource(name="AdServiceImpl")
    private AdService adService;

    @GetMapping("/test")
    public Object test(){
        return "test";
    }

    //增
    //加上@Validated表示需要进行参数验证
    @PostMapping("/insert")
    public Integer insert(@Validated @RequestBody Ad ad) {
        return adService.insert(ad);
    }
    //删
    @DeleteMapping("/delete")
    public Integer delete(@RequestParam Integer id) {
        return adService.delete(id);
    }
    //改
    @PutMapping("/update")
    public Integer update(@RequestBody Ad ad) {
        return adService.update(ad);
    }
    //查
    @GetMapping("/list")
    public List<Ad> list(){
        return adService.findAll();
    }
    @GetMapping("/detail")
    public Ad detail(@RequestParam Integer id){
        return adService.findById(id);
    }

}

IDEA提供了HTTP客户端方便我们测试接口请求

在项目的根目录下创建http文件夹

创建配置文件http-client.env.json

{
  "dev": {
    "host": "http://localhost:8081"
  }
}

新增ad.http文件

### 增
POST {{host}}/ad/insert
Content-Type:application/json

{
  "name": "kvkvkv"
}
### 删
DELETE {{host}}/ad/delete?id=18
### 改
PUT {{host}}/ad/update
Content-Type:application/json

{
  "id":20,
  "name": "Tom20"
}
### 查
GET {{host}}/ad/list

多个api之间使用3个#换行分隔开