Springboot 2.x与mysql和Mybatis和PageHelper整合CRUD案例

• 引入pom文件

        <!--mysql-->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.38</version>
            <scope>runtime</scope>
        </dependency>
        <!--引入第三方数据源-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.14</version>
        </dependency>
        <!--mybatis-->
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>1.3.2</version>
        </dependency>
        <!--分页插件-->
        <dependency>
            <groupId>com.github.pagehelper</groupId>
            <artifactId>pagehelper</artifactId>
            <version>5.1.8</version>
        </dependency>

• 配置application.proerties文件

#==============数据库相关配置=====================
#可以自动识别
spring.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.datasource.url=jdbc:mysql:///表名
spring.datasource.username=xxxx
spring.datasource.password=xxxx
spring.datasource.type=com.alibaba.druid.pool.DruidDataSource
# mybatis 下划线转驼峰配置
mybatis.configuration.map-underscore-to-camel-case=true
#增加打印sql语句，一般用于本地开发测试
mybatis.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

• 编写PageHelper配置java文件

import com.github.pagehelper.PageHelper;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import java.util.Properties;
/**
 * mybatis分页配置
 */
@Configuration
public class PageHelperConfig {
    @Bean
    public PageHelper pageHelper(){
        PageHelper pageHelper=new PageHelper();
        Properties properties=new Properties();
        /**
         * 忘记怎么设置属性就查看PageParams类
         */
        properties.setProperty("offsetAsPageNum","true");
        properties.setProperty("rowBoundsWithCount","true");
        properties.setProperty("reasonable","true");
        pageHelper.setProperties(properties);
        return pageHelper;
    }
}

经过配置文件后开始进行编写主要代码(主要以接口开发，没有前端页面,可以自己使用postman工具测试)

1. 先创建包config(配置包)、domain(实体包)、mapper(接口包)、provider(编写动态sql)、service(业务逻辑接口包)、impl(业务逻辑实现包)、controller(控制层包)等包。
2. 创建实体类(Video)

import java.io.Serializable;

/**
 * 视频表
 */
public class Video implements Serializable {

  private Integer id;
  private String title;
  private String summary;
  private String coverImg;
  private Integer viewNum;
  private Integer price;
  private java.util.Date createTime;
  private Integer online;
  private Double point;
  public Integer getId() {
    return id;
  }
  public void setId(Integer id) {
    this.id = id;
  }
  public String getTitle() {
    return title;
  }
  public void setTitle(String title) {
    this.title = title;
  }
  public String getSummary() {
    return summary;
  }
  public void setSummary(String summary) {
    this.summary = summary;
  }
  public String getCoverImg() {
    return coverImg;
  }
  public void setCoverImg(String coverImg) {
    this.coverImg = coverImg;
  }
  public Integer getViewNum() {
    return viewNum;
  }
  public void setViewNum(Integer viewNum) {
    this.viewNum = viewNum;
  }
  public Integer getPrice() {
    return price;
  }
  public void setPrice(Integer price) {
    this.price = price;
  }
  public java.util.Date getCreateTime() {
    return createTime;
  }
  public void setCreateTime(java.util.Date createTime) {
    this.createTime = createTime;
  }
  public Integer getOnline() {
    return online;
  }
  public void setOnline(Integer online) {
    this.online = online;
  }
  public Double getPoint() {
    return point;
  }
  public void setPoint(Double point) {
    this.point = point;
  }

}

3.编写动态sql(VideoProvider)

import cn.jackson.eduvideo.domain.Video;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.jdbc.SQL;

/**
 * video动态sql
 */
public class VideoProvider {

    /**
     * 动态更新sql语句
     * @param video
     * @return
     */
    public String updateVideo(final Video video){
        return new SQL(){{

            /**
             * 条件写法
             */
            UPDATE("video");
            if (video.getTitle()!=null){
                SET("title=#{title}");
            }
            if (video.getSummary()!=null){
                SET("summary=#{summary}");
            }
            if (video.getCoverImg()!=null){
                SET("cover_img=#{coverImg}");
            }
            if (video.getViewNum()!=null){
                SET("view_num=#{viewNum}");
            }
            if (video.getPrice()!=null){
                SET("price=#{price}");
            }
            if (video.getCreateTime()!=null){
                SET("create_time=#{createTime}");
            }
            if (video.getOnline()!=null){
                SET("online=#{online}");
            }
            if (video.getPoint()!=null){
                SET("point=#{point}");
            }
            WHERE("id=#{id}");
        }} .toString();
    }
    /**
    *通过id查找video
    */
    public String videoById(final int id){
        return new SQL(){{
            SELECT("*");
            FROM("video");
            WHERE("id="+id);
        }}.toString();
    }
     
    /**
    *通过id删除video
    */
    public String delVideo(final int id){
        return new SQL(){
            {
                DELETE_FROM("video");
                WHERE("id=#{id}");
            }
        }.toString();
    }

    public String insertVideo(final Video video){
        return new SQL(){
            {
                INSERT_INTO("video");
                if(video.getTitle()!=null){
                    VALUES("title","#{title}");
                }
                if(video.getSummary()!=null){
                    VALUES("summary","#{summary}");
                }
                if(video.getCoverImg()!=null){
                    VALUES("cover_img","#{coverImg}");
                }
                if(video.getViewNum()!=null){
                    VALUES("view_num","#{viewNum}");
                }
                if(video.getPrice()!=null){
                    VALUES("price","#{price}");
                }
                if(video.getCreateTime()!=null){
                    VALUES("create_time","#{createTime}");
                }
                if(video.getOnline()!=null){
                    VALUES("online","#{online}");
                }
                if(video.getPoint()!=null){
                    VALUES("point","#{point}");
                }
            }
        }.toString();
    }
}

4.接口类(VideoMapper) 本人主要是以动态sql开发，如果不想用的话，就可以直接使用下面我注释的sql语句

import cn.jackson.eduvideo.domain.Video;
import cn.jackson.eduvideo.provider.VideoProvider;
import org.apache.ibatis.annotations.*;

import java.util.List;

/**
 * video数据访问层
 */
public interface VideoMapper {
    @Select("select * from video")
    //结果映射
    /*@Results({
            //驼峰设置   注意：这样写代码太多，直接在applicaion.properties设置mybatis 下划线转驼峰配置
            @Result(column = "cover_img",property = "coverImg"),
            @Result(column = "view_num",property = "viewNum"),
            @Result(column = "create_time",property = "createTime"),
    })*/
    List<Video> findAll();

    //@Select("select * from video where id=#{id}")
    @SelectProvider(type = VideoProvider.class,method = "videoById")
    public Video findId(@Param("id")int id);

    //@Update("update video set title=#{title} where id=#{id}")
    @UpdateProvider(type = VideoProvider.class,method = "updateVideo")
    public int updateVideo(Video video);

    //@Delete("delete from video where id=#{id}")
    @DeleteProvider(type = VideoProvider.class,method = "delVideo")
    public int delVideo(@Param("id")int id);

    /**
     * 保存保存
     *       技巧：保存对象，获取数据库自增id
     *           @Options(useGeneratedKeys=true, keyProperty="id", keyColumn="id")
     *           注意点：使用``,不能使用''
     */
//    @Insert("INSERT INTO video ( `title`, `summary`, " +
//            "`cover_img`, `view_num`, `price`, `create_time`," +
//            " `online`, `point`)" +
//            "VALUES" +
//            "(#{title}, #{summary}, #{coverImg}, #{viewNum}, #{price},#{createTime}," +
//            "#{online},#{point});")
    @InsertProvider(type = VideoProvider.class,method = "insertVideo")
    @Options(useGeneratedKeys=true, keyProperty="id", keyColumn="id")
    public int save(Video video);
}

5.业务逻辑接口(VideoService)

/**
 * 视频业务逻辑
 */
public interface VideoService {

    List<Video> findAll();

    public Video findId(int id);

    public int updateVideo(Video video);

    public int delVideo(int id);

    public int save(Video video);
}

6.业务逻辑实现类(VideoServiceImpl)

import cn.jackson.eduvideo.domain.Video;
import cn.jackson.eduvideo.mapper.VideoMapper;
import cn.jackson.eduvideo.service.VideoService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * 注入@Service相当就是形成一个bean
 */
@Service
public class VideoServiceImpl implements VideoService {

    @Autowired
    private VideoMapper videoMapper;
    @Override
    public List<Video> findAll() {
        return videoMapper.findAll();
    }

    @Override
    public Video findId(int id) {
        return videoMapper.findId(id);
    }

    @Override
    public int updateVideo(Video video) {
        return videoMapper.updateVideo(video);
    }

    @Override
    public int delVideo(int id) {
       return videoMapper.delVideo(id);
    }

    @Override
    public int save(Video video) {
        int row=videoMapper.save(video);
        System.out.println(video.getId());
        return row;
        
        //可以直接 return videoMapper.save(video);
    }
}

7.控制层(VideoController)

import cn.jackson.eduvideo.config.WeChatConfig;
import cn.jackson.eduvideo.domain.Video;
import cn.jackson.eduvideo.mapper.VideoMapper;
import cn.jackson.eduvideo.service.VideoService;
import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import java.util.List;

@RestController
@RequestMapping("/api/v1/video")
public class VideoController {

    @Autowired
    private VideoMapper videoMapper;

    @Autowired
    private VideoService videoService;


    /**
     * 分页接口
     * @param page 当前第几页
     * @param size 每页显示的条数
     * @return
     */
    @GetMapping("page")
    public Object pageVideo(@RequestParam(value = "page",defaultValue = "1")int page,
                            @RequestParam(value = "size", defaultValue = "2")int size){
        PageHelper.startPage(page,size);
        List<Video> list=videoService.findAll();
        PageInfo<Video> info=new PageInfo<>(list);
        return info;
    }

    /**
     * 根据id找视频
     * @param videoId  请求参数@RequestParam中value一般使用带有下划线，如video_id
     * @return
     */
    @GetMapping("find_id")
    //@RequestParam(value = "video_id",required = true)
    public Object findById(@RequestParam(value = "video_id",required = true)int videoId){
        return videoService.findId(videoId);
    }

    /**
     * 根据id更新视频
     * @RequestBody 请求体映射实体类
     *                 需要指定http头为 content-type为application/json charset=utf-8
     * @param video
     * @return
     */
    @PutMapping("update_id")
    public Object updateById(@RequestBody Video video){
        return videoService.updateVideo(video);
    }

    /**
     * 根据id删除视频
     * @param videoId
     * @return
     */
    @DeleteMapping("del_id")
    public Object delById(@RequestParam(value = "video_id",required = true)int videoId){
        return videoService.delVideo(videoId);
    }

    /**
     * 添加视频
     * @param video
     * @return
     */
    @PostMapping("add")
    public Object addVideo(@RequestBody Video video){
        return videoService.save(video);
    }

}

最后打开postman测试

通过自己的端口号测试，默认是8080，比如查找id为1的视频信息

http://localhost:8080/api/v1/video/find_id?video_id=1