Mybatis plus 学习使用（增删改查，多对多 ，分页 ，如何存储枚举数组）

Mybatis plus 学习使用

最近想把自己的博客mybatis换成plus ,应为基本的增删改查，每次写好麻烦，这边先记录一下，修改的过程，数据结构不是最终的情况的情况。

增删改查

这个是很基本的，官方的文档都有，我这边也满记录一下；
首先建表

CREATE TABLE tag
(
  id   BIGINT(20)  NOT NULL COMMENT '主键ID',
  name VARCHAR(60) NULL DEFAULT NULL COMMENT '标签名',
  PRIMARY KEY (id)
);

添加依赖

<!-- mybatis plus -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.3.1.tmp</version>
        </dependency>
        <!-- Druid连接池包 -->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>druid</artifactId>
            <version>1.1.22</version>
        </dependency>

spring:
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/xxx?useUnicode=true&characterEncoding=UTF-8&serverTimezone=UTC
    username: xxx
    password: xxx
    type: com.alibaba.druid.pool.DruidDataSource

import com.baomidou.mybatisplus.extension.activerecord.Model;
import lombok.Data;
import lombok.EqualsAndHashCode;

@Data
@EqualsAndHashCode(callSuper = true)
public class Tag extends Model<Tag> {

    private static final long serialVersionUID = 1L;
    private String id;
    /**
     * 标签名
     */
    private String name;


}

public interface TagMapper extends BaseMapper<Tag> {

}

这里我出现了一个问题,如果id 用long型的话，返回给前端数字会变化，原因的话我这边理解是数字太大，前端接收有影响所以这边就改为String了。
添加config文件（分页这边也配置了）

@Configuration
@MapperScan("com.XXXXX")
public class MybatisPlusConfig {
    @Bean
    public PaginationInterceptor paginationInterceptor() {
        PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
        // 设置请求的页面大于最大页后操作， true调回到首页，false 继续请求  默认false
        // paginationInterceptor.setOverflow(false);
        // 设置最大单页限制数量，默认 500 条，-1 不受限制
        // paginationInterceptor.setLimit(500);
        // 开启 count 的 join 优化,只针对部分 left join
        paginationInterceptor.setCountSqlParser(new JsqlParserCountOptimize(true));
        return paginationInterceptor;
    }
}

MapperScan 里面的就是之前TagMapper 的文件夹位置。
定义增删改查

public interface ITagService extends IService<Tag> {
    Tag createTag(String name);

    void deleteTag(String id);

    Tag changeTag(String id, String name);

    List<Tag> findByName(String name);

}

@Service
@Transactional
public class TagServiceImpl extends ServiceImpl<TagMapper, Tag> implements ITagService {


    @Override
    public Tag createTag(String name) {
        List<Tag> tags = getBaseMapper().selectList(new QueryWrapper<Tag>().lambda().eq(Tag::getName, name));
        if (tags.size() == 0) {
            Tag tag = new Tag();
            tag.setName(name);
            tag.insert();
            return tag;
        }
        return tags.get(0);
    }

    @Override
    public void deleteTag(String id) {
        getBaseMapper().deleteById(id);
    }

    @Override
    public Tag changeTag(String id, String name) {
        List<Tag> tags = getBaseMapper().selectList(new QueryWrapper<Tag>().lambda().eq(Tag::getName, name));
        if (tags.size() > 0) {
            throw new BizException("-1", "修改后的标签已经存在");
        }
        Tag tag = getBaseMapper().selectById(id);
        tag.setName(name);
        getBaseMapper().insert(tag);
        return tag;
    }

    @Override
    public List<Tag> findByName(String name) {
        return getBaseMapper().selectList(new QueryWrapper<Tag>().lambda().like(Tag::getName, name));
    }
}

多对多的情况

在添加一个Article表，一个Article可以有多个tag 一个tag有多个Article，

CREATE TABLE article
(
  id              BIGINT(20)  NOT NULL COMMENT '主键ID',
  title           VARCHAR(60) NULL DEFAULT NULL COMMENT '姓名',
  author          VARCHAR(30),
  publish_date    TIMESTAMP   NULL,
  update_date     TIMESTAMP   NULL,
  article_content TEXT,
  article_tabloid TEXT,
  last_article_id BIGINT(20),
  next_article_id BIGINT(20),
  likes           BIGINT(20),
  PRIMARY KEY (id)
);
alter table article
  add column play_count BIGINT default 0 not null;

create table article_tag
(
  article_id bigint not null,
  tag_id     bigint null,
  constraint FK_article_id
    foreign key (article_id) references article (id),
  constraint FK_tag_id
    foreign key (tag_id) references tag (id)
);

@Data
@EqualsAndHashCode(callSuper = true)
public class Article extends Model<Article> {

    private static final long serialVersionUID = 1L;

    private String id;

    private String title;

    private String author;

    private LocalDateTime publishDate;

    private LocalDateTime updateDate;

    private String articleContent;

    private String articleTabloid;

    private String lastArticleId;

    private String nextArticleId;

    private Long likes;

    private Long playCount;
}
@Data
@EqualsAndHashCode(callSuper = true)
@Accessors(chain = true)
public class ArticleTag extends Model<ArticleTag> {

    private static final long serialVersionUID = 1L;

    private String articleId;

    private String tagId;


}
@Data
@ToString(callSuper = true)
@EqualsAndHashCode(callSuper = true)
public class ArticlePlus extends Article {
    private List<Tag> tags;
}

/**
 * <p>
 * Mapper 接口
 * </p>
 *
 * @author dzl
 * @since 2020-04-27
 */
public interface ArticleMapper extends BaseMapper<Article> {

    ArticlePlus findArticlePlusById(@Param("id") String id);

}

这边要配置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.dzl.blog2.mapper.ArticleMapper">

    <resultMap id="ArticlePlus" type="com.dzl.blog2.model.ArticlePlus">
        <id column="id" property="id"/>
        <result column="title" property="title"/>
        <result column="author" property="author"/>
        <result column="publishDate" property="publishDate"/>
        <result column="updateDate" property="updateDate"/>
        <result column="articleContent" property="articleContent"/>
        <result column="articleTabloid" property="articleTabloid"/>
        <result column="lastArticleId" property="lastArticleId"/>
        <result column="nextArticleId" property="nextArticleId"/>
        <result column="likes" property="likes"/>
        <result column="playCount" property="playCount"/>
        <collection property="tags" ofType="com.dzl.blog2.entity.Tag" columnPrefix="t_">
            <id column="id" property="id"/>
            <result column="name" property="name"/>
        </collection>
    </resultMap>
    <select id="findArticlePlusById" resultMap="ArticlePlus">
        select a.id,
        title,
        author,
        publish_date,
        update_date,
        article_content,
        article_tabloid,
        last_article_id,
        next_article_id,
        likes,
        play_count,
        t.id as t_id,
        t.name as t_name
        from article a
        left join article_tag t1 on a.id = t1.article_id
        left join tag t on t1.tag_id = t.id
        <where>
            a.id = #{id}
        </where>
    </select>
</mapper>

像之前创建service 一样，这边就直接添加方法了

 @Override
    public ArticlePlus detail(String id) {
        return getBaseMapper().findArticlePlusById(id);
    }

查找分页

还是根据ArticlePlus  来分页
在文章的mapper上添加

  List<ArticlePlus> findAllArticle(@Param("key_word") String keyWord, IPage<ArticlePlus> myPage);

    <select id="findAllArticle" resultMap="ArticlePlus">
        select a.id,
        title,
        author,
        publish_date,
        update_date,
        article_content,
        article_tabloid,
        last_article_id,
        next_article_id,
        likes,
        play_count,
        t.id as t_id,
        t.name as t_name
        from article a
        left join article_tag t1 on a.id = t1.article_id
        left join tag t on t1.tag_id = t.id
        <where>
            <if test="key_word!=null and key_word!=''">
                title like concat(concat('%',#{key_word}),'%')
                or article_content like concat(concat('%',#{key_word}),'%')
                or author like concat(concat('%',#{key_word}),'%')
                or t.name like concat(concat('%',#{key_word}),'%')
            </if>
        </where>
    </select>

@Override
    public PageBody<ArticlePlus> findAll(ArticleSearchInput input) {
        IPage<ArticlePlus> page = new Page<>(input.getCurrent(), input.getSize());
        List<ArticlePlus> allArticle = getBaseMapper().findAllArticle(input.getKeyWord(), page);
        return new PageBody<>(page, allArticle);
    }

有点奇怪，返回的是list，所以我自己写了PageBody包一下

/**
 * 分页统一返回数据结构
 *
 * @param <T>
 */

@Data
public class PageBody<T> {
    private Long page;

    private Long size;

    private Long total;

    private Long current;

    private List<T> content;

    public PageBody(IPage page, List<T> content) {
        this.page = page.getPages();
        this.current = page.getCurrent();
        this.size = page.getSize();
        this.total = page.getTotal();
        this.content = content;
    }

}

如何存储枚举数组

基本上的思路是转json存到数据库里，原本打算用list 存，但是发现存是可以存，但是取不出来，所以转成数组

@EqualsAndHashCode(callSuper = true)
@Data
@Accessors(chain = true)
@TableName(autoResultMap = true)
public class User extends Model<User>{

    private static final long serialVersionUID = 1L;
    private String id;
    /**
     * 姓名
     */
    private String name;

    private String password;

    private LocalDateTime createTime;

    private String avatar;

    private String phone;
	//重点是这里
    @TableField(typeHandler = JacksonTypeHandler.class)
    private Role[] roles;
}
@Getter
public enum Role {
    ARTICLE,
    TAG,
    USER
}

create table user
(
  id          bigint      not null comment '主键ID'
    primary key,
  name        varchar(30) null comment '姓名',
  password    text        null,
  create_time timestamp   null,
  avatar      text        null,
  phone       varchar(30) null,
  roles       text        null
);

其他添加就不说了，老样子

添加config文件

/**
 * @author miemie
 * @since 2019-11-28
 */
@Component
public class MpJsonConfig implements CommandLineRunner {
    @Override
    public void run(String... args) throws Exception {
        JacksonTypeHandler.setObjectMapper(new ObjectMapper());
        GsonTypeHandler.setGson(new Gson());

    }
}

即可