从零搭建开发脚手架 Spring Boot集成Mybatis-plus之二

接上篇
《从零搭建开发脚手架 Spring Boot集成Mybatis-plus之一》

分页查询

首先配置分页插件

    /**
     * 新的分页插件,一缓和二缓遵循mybatis的规则,需要设置 MybatisConfiguration#useDeprecatedExecutor = false 避免缓存出现问题(该属性会在旧插件移除后一同移除)
     */
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.MYSQL));
        return interceptor;
    }

    @Bean
    public ConfigurationCustomizer configurationCustomizer() {
        return configuration -> configuration.setUseDeprecatedExecutor(false);
    }

普通分页

分页查询代码示例

    @GetMapping
    public Response pageAll(@RequestParam(required = false, defaultValue = "1") long current,
                            @RequestParam(required = false, defaultValue = "10") long size,
                            String roadName) {
        Page roadPage = new Page<>(current, size);
        LambdaQueryWrapper<Road> queryWrapper = new QueryWrapper().lambda();
        queryWrapper.like(StringUtils.isNotBlank(roadName), Road::getRoadName, roadName);
        Page pageList = roadService.page(roadPage, queryWrapper);
        return Response.ok(pageList);
    }

自动填充新建时间、更新时间、操作人等属性

• 首先，实现元对象处理器接口：com.baomidou.mybatisplus.core.handlers.MetaObjectHandler

@Slf4j
@Component
public class MyMetaObjectHandler implements MetaObjectHandler {
    @Override
    public void insertFill(MetaObject metaObject) { // Mapper调用insert操作时，进行如下操作
        log.info("start insert fill ...."); // 给entity的属性设置值
        this.strictInsertFill(metaObject, "createTime", () -> LocalDateTime.now(), LocalDateTime.class);
        this.strictInsertFill(metaObject, "operator", String.class, "张三");
    }
    @Override
    public void updateFill(MetaObject metaObject) {//  Mapper调用update操作时，进行如下操作
        log.info("start update fill ...."); // 给entity的属性设置值
        this.strictUpdateFill(metaObject, "updateTime", () -> LocalDateTime.now(), LocalDateTime.class);
        this.strictUpdateFill(metaObject, "operator", String.class, "张三");
    }
}

MetaObjectHandler提供的默认方法的策略均为：

• 如果属性有值则不覆盖。
• 如果填充值为null则不填充。

• 其次，给相应实体类设置注解

注解：指定该属性在对应情况下必有值，如果无值则入库会是null

字段必须声明TableField注解,属性fill选择对应策略,该声明告知Mybatis-Plus需要预留注入SQL字段

原理：判断注入的 insert 和 update 的 sql 脚本是否在对应情况下忽略掉字段的 if 标签生成
…

@Data
@EqualsAndHashCode(callSuper = false)
public class Stake implements Serializable {
    private Long roadId;
    private String stakeName;

    @TableField(fill = FieldFill.INSERT)
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private LocalDateTime createTime;

    @TableField(fill = FieldFill.UPDATE)
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private LocalDateTime updateTime;

    @TableField(fill = FieldFill.INSERT_UPDATE)
    private String operator;

}

整体原理

• 当发生insert或者update的sql脚本时候
• 看下当前发生相关sql 的实体中相应字段的注解
  • 注解FieldFill.INSERT，即动态添加<if test="...">......</if>insert相关字段
  • 注解FieldFill.UPDATE，即动态添加<if test="...">......</if>update相关字段
  • 注解FieldFill.UPDATE，即动态添加<if test="...">......</if>insert和update相关字段

自定义字段类型

类型处理器，用于 JavaType 与 JdbcType 之间的转换，用于 PreparedStatement 设置参数值和从 ResultSet 或 CallableStatement 中取出一个值，本文讲解 mybaits-plus 内置常用类型处理器如何通过TableField注解快速注入到 mybatis 容器中。

如果报xml中五自定义handler的错误，把xml删除，或者在xml中也配置上

自动映射Json数据

@Data
@Accessors(chain = true)
@TableName(autoResultMap = true)
public class User {
    private Long id;

    /**
     * 注意！！ 必须开启映射注解
     *
     * @TableName(autoResultMap = true)
     *
     * 以下两种类型处理器，二选一 也可以同时存在
     *
     * 注意！！选择对应的 JSON 处理器也必须存在对应 JSON 解析依赖包
     */
    @TableField(typeHandler = JacksonTypeHandler.class)
    // @TableField(typeHandler = FastjsonTypeHandler.class)
    private OtherInfo otherInfo;

}

该注解对应了 XML 中写法为

<result column="other_info" jdbcType="VARCHAR" property="otherInfo" typeHandler="com.baomidou.mybatisplus.extension.handlers.JacksonTypeHandler" />

自动映射空间数据格式

可以看我的另一篇很详细 从零搭建开发脚手架 mybatis自定义字段类型 以Mysql空间数据存储为例

@Data
@EqualsAndHashCode(callSuper = false)
@TableName( autoResultMap = true)
public class ServiceArea implements Serializable {
    @TableId(value = "id", type = IdType.AUTO)
    private Integer id;

    /**
     * 经纬度    格式：X,Y
     */
    @TableField(typeHandler = JacksonTypeHandler.class)
    private double[] location;

    @TableField(typeHandler = GeoPointTypeHandler.class)
    private GeoPoint coordinate;

}

自定义Controller模板

自定义代码模板

//指定自定义模板路径, 位置：/resources/templates/entity2.java.ftl(或者是.vm)
//注意不要带上.ftl(或者是.vm), 会根据使用的模板引擎自动识别
TemplateConfig templateConfig = new TemplateConfig()
    .setEntity("templates/entity2.java");

AutoGenerator mpg = new AutoGenerator();
//配置自定义模板
mpg.setTemplate(templateConfig);

自定义属性注入

InjectionConfig injectionConfig = new InjectionConfig() {
    //自定义属性注入:abc
    //在.ftl(或者是.vm)模板中，通过${cfg.abc}获取属性
    @Override
    public void initMap() {
        Map<String, Object> map = new HashMap<>();
        map.put("abc", this.getConfig().getGlobalConfig().getAuthor() + "-mp");
        this.setMap(map);
    }
};
AutoGenerator mpg = new AutoGenerator();
//配置自定义属性注入
mpg.setCfg(injectionConfig);

entity2.java.ftl
自定义属性注入abc=${cfg.abc}

entity2.java.vm
自定义属性注入abc=$!{cfg.abc}

框架自带的可用参数

Github AbstractTemplateEngine 类中方法 getObjectMap 返回 objectMap 的所有值都可用。

/**
     * 渲染对象 MAP 信息
     *
     * @param tableInfo 表信息对象
     * @return ignore
     */
    public Map<String, Object> getObjectMap(TableInfo tableInfo) {
        Map<String, Object> objectMap;
        ConfigBuilder config = getConfigBuilder();
        if (config.getStrategyConfig().isControllerMappingHyphenStyle()) {
            objectMap = CollectionUtils.newHashMapWithExpectedSize(33);
            objectMap.put("controllerMappingHyphenStyle", config.getStrategyConfig().isControllerMappingHyphenStyle());
            objectMap.put("controllerMappingHyphen", StringUtils.camelToHyphen(tableInfo.getEntityPath()));
        } else {
            objectMap = CollectionUtils.newHashMapWithExpectedSize(31);
        }
        objectMap.put("restControllerStyle", config.getStrategyConfig().isRestControllerStyle());
        objectMap.put("config", config);
        objectMap.put("package", config.getPackageInfo());
        GlobalConfig globalConfig = config.getGlobalConfig();
        objectMap.put("author", globalConfig.getAuthor());
        objectMap.put("idType", globalConfig.getIdType() == null ? null : globalConfig.getIdType().toString());
        objectMap.put("logicDeleteFieldName", config.getStrategyConfig().getLogicDeleteFieldName());
        objectMap.put("versionFieldName", config.getStrategyConfig().getVersionFieldName());
        objectMap.put("activeRecord", globalConfig.isActiveRecord());
        objectMap.put("kotlin", globalConfig.isKotlin());
        objectMap.put("swagger2", globalConfig.isSwagger2());
        objectMap.put("date", new SimpleDateFormat("yyyy-MM-dd").format(new Date()));
        objectMap.put("table", tableInfo);
        objectMap.put("enableCache", globalConfig.isEnableCache());
        objectMap.put("baseResultMap", globalConfig.isBaseResultMap());
        objectMap.put("baseColumnList", globalConfig.isBaseColumnList());
        objectMap.put("entity", tableInfo.getEntityName());
        objectMap.put("entitySerialVersionUID", config.getStrategyConfig().isEntitySerialVersionUID());
        objectMap.put("entityColumnConstant", config.getStrategyConfig().isEntityColumnConstant());
        objectMap.put("entityBuilderModel", config.getStrategyConfig().isEntityBuilderModel());
        objectMap.put("chainModel", config.getStrategyConfig().isChainModel());
        objectMap.put("entityLombokModel", config.getStrategyConfig().isEntityLombokModel());
        objectMap.put("entityBooleanColumnRemoveIsPrefix", config.getStrategyConfig().isEntityBooleanColumnRemoveIsPrefix());
        objectMap.put("superEntityClass", getSuperClassName(config.getStrategyConfig().getSuperEntityClass()));
        objectMap.put("superMapperClassPackage", config.getStrategyConfig().getSuperMapperClass());
        objectMap.put("superMapperClass", getSuperClassName(config.getStrategyConfig().getSuperMapperClass()));
        objectMap.put("superServiceClassPackage", config.getStrategyConfig().getSuperServiceClass());
        objectMap.put("superServiceClass", getSuperClassName(config.getStrategyConfig().getSuperServiceClass()));
        objectMap.put("superServiceImplClassPackage", config.getStrategyConfig().getSuperServiceImplClass());
        objectMap.put("superServiceImplClass", getSuperClassName(config.getStrategyConfig().getSuperServiceImplClass()));
        objectMap.put("superControllerClassPackage", verifyClassPacket(config.getStrategyConfig().getSuperControllerClass()));
        objectMap.put("superControllerClass", getSuperClassName(config.getStrategyConfig().getSuperControllerClass()));
        return Objects.isNull(config.getInjectionConfig()) ? objectMap : config.getInjectionConfig().prepareObjectMap(objectMap);
    }

模板修改

• ${table.serviceName?substring(1)} - 删除首字母
• ${table.serviceName?uncap_first} - 首字母大写变小写

package ${package.Controller};

import org.springframework.web.bind.annotation.RequestMapping;
import ${package.Entity}.${entity};
import ${package.Service}.${table.serviceName};
<#if restControllerStyle>
import org.springframework.web.bind.annotation.RestController;
<#else>
import org.springframework.stereotype.Controller;
</#if>
import com.laker.map.ext.framework.Response;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
<#if superControllerClassPackage??>
import ${superControllerClassPackage};
</#if>

/**
* <p>
    * ${table.comment!} 前端控制器
    * </p>
*
* @author ${author}
* @since ${date}
*/
<#if restControllerStyle>
@RestController
<#else>
@Controller
</#if>
@RequestMapping("<#if package.ModuleName?? && package.ModuleName != "">/${package.ModuleName}</#if>/<#if controllerMappingHyphenStyle??>${controllerMappingHyphen}<#else>${table.entityPath}</#if>")
<#if superControllerClass??>
public class ${table.controllerName} extends ${superControllerClass} {
<#else>
public class ${table.controllerName} {
</#if>
    @Autowired
    ${table.serviceName} ${table.serviceName?substring(1)?uncap_first};

    @GetMapping
    @ApiOperation(value = "${table.comment!}分页查询")
    public Response pageAll(@RequestParam(required = false, defaultValue = "1") long current,
                            @RequestParam(required = false, defaultValue = "10") long size) {
        Page roadPage = new Page<>(current, size);
        LambdaQueryWrapper<${table.entityName}> queryWrapper = new QueryWrapper().lambda();
        Page pageList = ${table.serviceName?substring(1)?uncap_first}.page(roadPage, queryWrapper);
        return Response.ok(pageList);
    }

    @PostMapping
    @ApiOperation(value = "新增或者更新${table.comment!}")
    public Response saveOrUpdate(${table.entityName} param) {
        return Response.ok(${table.serviceName?substring(1)?uncap_first}.saveOrUpdate(param));
    }

    @GetMapping("/{id}")
    @ApiOperation(value = "根据id查询${table.comment!}")
    public Response get(@PathVariable Long id) {
        return Response.ok(${table.serviceName?substring(1)?uncap_first}.getById(id));
    }

    @DeleteMapping("/{id}")
    @ApiOperation(value = "根据id删除${table.comment!}")
    public Response delete(@PathVariable Long id) {
        return Response.ok(${table.serviceName?substring(1)?uncap_first}.removeById(id));
    }
}

数据权限实现

看我另一篇博文，很详细从零搭建开发脚手架 基于Mybatis-Plus的数据权限实现

Lambda条件构造器

https://baomidou.com/guide/wrapper.html#abstractwrapper

QueryWrapper(LambdaQueryWrapper) 和 UpdateWrapper(LambdaUpdateWrapper) 的父类
用于生成 sql 的 where 条件, entity 属性也用于生成 sql 的 where 条件
注意: entity 生成的 where 条件与 使用各个 api 生成的 where 条件没有任何关联行为

建议使用Lambda条件构造器，当属性更改时，可以直接反应出来调用关系。不像QueryWrapper一样是静态字符串配置的

Lambda条件构造器:

        Page roadPage = new Page<>(current, size);
        LambdaQueryWrapper<Board> queryWrapper = new QueryWrapper().lambda();
        queryWrapper.eq(roadId != null, Board::getRoadId, roadId);
        queryWrapper.eq(deptId != null, Board::getDeptId, deptId);
        Page pageList = boardService.page(roadPage, queryWrapper);

普通条件构造器:

        Page roadPage = new Page<>(current, size);
        QueryWrapper<Board> queryWrapper = new QueryWrapper();
        queryWrapper.eq(roadId != null, "road_id", roadId);
        queryWrapper.eq(deptId != null, "dept_id", deptId);
        Page pageList = boardService.page(roadPage, queryWrapper);

通用Wrapper

条件	解释	例子
allEq	全部相等	例1: allEq({id:1,name:"老王",age:null})—>id = 1 and name = '老王' and age is null 例2: allEq({id:1,name:"老王",age:null}, false)—>id = 1 and name = '老王'
eq	等于 =	eq(“name”, “老王”)--->name = ‘老王’
ne	不等于 <>	ne(“name”, “老王”)--->name <> ‘老王’
gt	大于 >	gt(“age”, 18)--->age > 18
ge	大于等于 >=	ge(“age”, 18)--->age >= 18
lt	小于 <	lt(“age”, 18)--->age < 18
le	小于等于 <=	le(“age”, 18)--->age <= 18
between	BETWEEN 值1 AND 值2	between(“age”, 18, 30)--->age between 18 and 30
notBetween	NOT BETWEEN 值1 AND 值2	notBetween(“age”, 18, 30)--->age not between 18 and 30
like	LIKE ‘%值%’	like(“name”, “王”)--->name like ‘%王%’
notLike	NOT LIKE ‘%值%’	notLike(“name”, “王”)--->name not like ‘%王%’
likeLeft	LIKE ‘%值’	likeLeft(“name”, “王”)--->name like ‘%王’
likeRight	LIKE ‘值%’	likeRight(“name”, “王”)--->name like ‘王%’
isNull	字段 IS NULL	isNull(“name”)--->name is null
isNotNull	字段 IS NOT NULL	isNotNull(“name”)--->name is not null
in	字段 IN (value.get(0), value.get(1), …)	in(“age”,{1,2,3})--->age in (1,2,3)
notIn	NOT IN (value.get(0), value.get(1), …)	notIn(“age”,{1,2,3})--->age not in (1,2,3)
inSql	字段 IN ( sql语句 )	例: inSql("age", "1,2,3,4,5,6")—>age in (1,2,3,4,5,6) 例: inSql("id", "select id from table where id < 3")—>id in (select id from table where id < 3)
notInSql	字段 NOT IN ( sql语句 )	例: notInSql("age", "1,2,3,4,5,6")—>age not in (1,2,3,4,5,6) 例: notInSql("id", "select id from table where id < 3")—>id not in (select id from table where id < 3)
groupBy	分组：GROUP BY 字段, …	groupBy(“id”, “name”)--->group by id,name
orderByAsc	排序：ORDER BY 字段, … ASC	orderByAsc(“id”, “name”)--->order by id ASC,name ASC
orderByDesc	排序：ORDER BY 字段, … DESC	例: orderByDesc("id", "name")—>order by id DESC,name DESC
orderBy	排序：ORDER BY 字段, …	例: orderBy(true, true, "id", "name")—>order by id ASC,name ASC
having	HAVING ( sql语句 )	例: having("sum(age) > 10")—>having sum(age) > 10 例: having("sum(age) > {0}", 11)—>having sum(age) > 11
func	func 方法(主要方便在出现if…else下调用不同方法能不断链)	例: func(i -> if(true) {i.eq("id", 1)} else {i.ne("id", 1)})
or 拼接or	主动调用or表示紧接着下一个方法不是用and连接!(不调用or则默认为使用and连接)	eq(“id”,1).or().eq(“name”,“老王”)--->id = 1 or name = ‘老王’
OR 嵌套or		or(i -> i.eq(“name”, “李白”).ne(“status”, “活着”))--->or (name = ‘李白’ and status <> ‘活着’)
and	AND 嵌套	例: and(i -> i.eq("name", "李白").ne("status", "活着"))—>and (name = '李白' and status <> '活着')
nested	正常嵌套 不带 AND 或者 OR	nested(i -> i.eq(“name”, “李白”).ne(“status”, “活着”))--->(name = ‘李白’ and status <> ‘活着’)
apply	拼接 sql,该方法可用于数据库函数 动态入参的params对应前面applySql内部的{index}部分.这样是不会有sql注入风险的,反之会有!	例: apply("id = 1")—>id = 1 例: apply("date_format(dateColumn,'%Y-%m-%d') = '2008-08-08'")—>date_format(dateColumn,'%Y-%m-%d') = '2008-08-08'") 例: apply("date_format(dateColumn,'%Y-%m-%d') = {0}", "2008-08-08")—>date_format(dateColumn,'%Y-%m-%d') = '2008-08-08'")
last	无视优化规则直接拼接到 sql 的最后 只能调用一次,多次调用以最后一次为准 有sql注入的风险,请谨慎使用	last("limit 1")
exists	拼接 EXISTS ( sql语句 )	例: exists("select id from table where age = 1")—>exists (select id from table where age = 1)
notExists	拼接 NOT EXISTS ( sql语句 )	例: notExists("select id from table where age = 1")—>not exists (select id from table where age = 1)

QueryWrapper

类型	解释	例子
select	设置查询字段	例: select("id", "name", "age") 例: select(i -> i.getProperty().startsWith("test"))

UpdateWrapper

类型	解释	例子
set	SQL SET 字段	例: set("name", "老李头") 例: set("name", "")—>数据库字段值变为空字符串 例: set("name", null)—>数据库字段值变为null
setSql	设置 SET 部分 SQL	例: setSql("name = '老李头'")

枚举字段

1.声明枚举

方式一： 使用 @EnumValue 注解枚举属性 完整示例

@Getter
public enum GradeEnum {

    PRIMARY(1, "小学"),  SECONDORY(2, "中学"),  HIGH(3, "高中");

    GradeEnum(int code, String descp) {
        this.code = code;
        this.descp = descp;
    }

    @EnumValue//标记数据库存的值是code
    private final int code;
    //。。。
}

方式二： 枚举属性，实现 IEnum 接口如下：

public enum AgeEnum implements IEnum<Integer> {
    ONE(1, "一岁"),
    TWO(2, "二岁"),
    THREE(3, "三岁");
    
    private int value;
    private String desc;
    
    @Override
    public Integer getValue() {
        return this.value;
    }
}

2.配置扫描枚举

mybatis-plus:
    # 支持统配符 * 或者 ; 分割
    typeEnumsPackage: com.baomidou.springboot.entity.enums

3.使用示例

数据库实体使用：

public class User {
    /**
     * 名字
     * 数据库字段: name varchar(20)
     */
    private String name;
    
    /**
     * 年龄，IEnum接口的枚举处理
     * 数据库字段：age INT(3)
     */
    private AgeEnum age;
        
        
    /**
     * 年级，原生枚举（带{@link com.baomidou.mybatisplus.annotation.EnumValue}):
     * 数据库字段：grade INT(2)
     */
    private GradeEnum grade;
}

4.前端JSON

https://mp.baomidou.com/guide/enum.html#jackson

实战经验总结lambda查询、更新、模糊查询等示例

lambda查询

        Manager one = managerService.getOne(
                             Wrappers.<Manager>lambdaQuery()
                               .eq(loginDto.getUserNo() != null, Manager::getUserNo, loginDto.getUserNo()));

lambda更新

        boolean update = managerService.update(Wrappers.<Manager>lambdaUpdate().eq(Manager::getUserNo, changePwdDto.getUserNo())
                .eq(Manager::getPassWord, changePwdDto.getOldPwd())
                .set(Manager::getPassWord, changePwdDto.getNewPwd()));

自定义sql 注解@select+like

    @Select("SELECT\n" +
            "\t s.*,st.name student_name \n" +
            "FROM\n" +
            "\tt_sign s\n" +
            "\tINNER JOIN t_student st ON s.wxid = st.wx_id\n" +
            "\twhere s.time like  CONCAT('%',#{date},'%')\n and s.course_id = #{courseId}" +
            "\t")
    List<Sign> selectByCourseIdAndDate(Long courseId, String date);

自定义结果映射 驼峰命名自动映射

student_name – studentName

    @TableId(value = "id", type = IdType.AUTO)
    private Long id;

    private Long courseId;

    private String wxid;

    private String time;

    @TableField(exist = false)
    private String studentName;

自定义sql分页

    @Select("SELECT\n" +
            "\t c.*,cr.start_time record_start_time \n" +
            "FROM\n" +
            "\tt_course c\n" +
            "\tINNER JOIN t_course_record cr ON c.id = cr.course_id\n" +
            "\t where c.teacher_id = #{teacherId} order by start_time desc")
    Page<Course> selectByTeacherId(Page page, Long teacherId);