Featured image of post 【合集】ORM框架

【合集】ORM框架

Mybatis

【合集】Mybatis

TkMapper

1
2
3
4
5
6
<!--整合tkmapper-->
<dependency>
    <groupId>tk.mybatis</groupId>
    <artifactId>mapper-spring-boot-starter</artifactId>
    <version>2.1.5</version>
</dependency>
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
// 根据fleetId删除
Example example = new Example(BrpContactInfo.class);
Example.Criteria criteria = example.createCriteria();
criteria.andEqualTo("fleetId", fleet.getId());
brpContactInfoMapper.updateByExampleSelective(BrpContactInfo.builder().status("-1").build(), example);

Example example2 = new Example(Dictionary.class);
Example.Criteria criteria2 = example2.createCriteria();
criteria2.andEqualTo("code", "InternalContactType");
criteria2.andEqualTo("codeKey", "app_fleet_manager");
criteria2.andEqualTo("status", "1");
List<Dictionary> dictionaries = dictionaryMapper.selectByExample(example2);

Mybatis Plus

相关文章

Mybatis Plus 官网

配置类:MybatisPlusConfig

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
/**
 * mybatis-plus配置类(下方注释有插件介绍)
 */
@EnableTransactionManagement(proxyTargetClass = true)
@Configuration
@MapperScan(basePackages = "com.zx.alice.mapper")
public class MybatisPlusConfig {

    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        // 数据权限处理
//        interceptor.addInnerInterceptor(dataPermissionInterceptor());
        // 分页插件
        interceptor.addInnerInterceptor(paginationInnerInterceptor());
        // 乐观锁插件
        interceptor.addInnerInterceptor(optimisticLockerInnerInterceptor());
        // 防全表更新与删除插件
        interceptor.addInnerInterceptor(new BlockAttackInnerInterceptor());
        return interceptor;
    }

    //  /**
    //   * 数据权限拦截器
    //   */
    // public PlusDataPermissionInterceptor dataPermissionInterceptor() {
    //     return new PlusDataPermissionInterceptor();
    // }

    /**
     * 分页插件,自动识别数据库类型
     */
    public PaginationInnerInterceptor paginationInnerInterceptor() {
        PaginationInnerInterceptor paginationInnerInterceptor = new PaginationInnerInterceptor();
        // 设置最大单页限制数量,默认 500 条,-1 不受限制
        paginationInnerInterceptor.setMaxLimit(-1L);
        paginationInnerInterceptor.setDbType(DbType.MYSQL);
        // 分页合理化
        paginationInnerInterceptor.setOverflow(false);
        return paginationInnerInterceptor;
    }

    /**
     * 乐观锁插件
     */
    public OptimisticLockerInnerInterceptor optimisticLockerInnerInterceptor() {
        return new OptimisticLockerInnerInterceptor();
    }

    /**
     * 元对象字段填充控制器
     */
    @Bean
    public MetaObjectHandler metaObjectHandler() {
        return new MyMetaObjectHandler();
    }

    /**
     * 使用网卡信息绑定雪花生成器
     * 防止集群雪花ID重复
     */
//    @Bean
//    public IdentifierGenerator idGenerator() {
//        return new DefaultIdentifierGenerator(NetUtil.getLocalhost());
//    }

    /**
     * PaginationInnerInterceptor 分页插件,自动识别数据库类型
     * https://baomidou.com/pages/97710a/
     * OptimisticLockerInnerInterceptor 乐观锁插件
     * https://baomidou.com/pages/0d93c0/
     * MetaObjectHandler 元对象字段填充控制器
     * https://baomidou.com/pages/4c6bcf/
     * ISqlInjector sql注入器
     * https://baomidou.com/pages/42ea4a/
     * BlockAttackInnerInterceptor 如果是对全表的删除或更新操作,就会终止该操作
     * https://baomidou.com/pages/f9a237/
     * IllegalSQLInnerInterceptor sql性能规范插件(垃圾SQL拦截)
     * IdentifierGenerator 自定义主键策略
     * https://baomidou.com/pages/568eb2/
     * TenantLineInnerInterceptor 多租户插件
     * https://baomidou.com/pages/aef2f2/
     * DynamicTableNameInnerInterceptor 动态表名插件
     * https://baomidou.com/pages/2a45ff/
     */

}

Mybatis-Plus 自动填充器

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
/**
 * mybatis-plus 自动填充器
 */
@Slf4j
public class MyMetaObjectHandler implements MetaObjectHandler {

    @Override
    public void insertFill(MetaObject metaObject) {
        this.strictInsertFill(metaObject, "createTime", LocalDateTime::now, LocalDateTime.class); // 起始版本 3.3.3(推荐)
        this.strictInsertFill(metaObject, "createBy", this::getLoginUserName, String.class);

        this.strictInsertFill(metaObject, "updateBy", this::getLoginUserName, String.class); // 起始版本 3.3.3(推荐)
        this.strictInsertFill(metaObject, "updateTime", LocalDateTime::now, LocalDateTime.class); // 起始版本 3.3.3(推荐)
    }

    @Override
    public void updateFill(MetaObject metaObject) {
        this.strictUpdateFill(metaObject, "updateTime", LocalDateTime::now, LocalDateTime.class); // 起始版本 3.3.3(推荐)
        this.strictUpdateFill(metaObject, "updateBy", this::getLoginUserName, String.class);
    }

    /**
     * 获取登录用户姓名
     */
    private String getLoginUserName() {
//        LoginUser loginUser;
//        try {
//            loginUser = SecurityUtils.getLoginUser();
//        } catch (Exception e) {
//            log.warn("自动注入警告 => 用户未登录");
//            return "未登录用户";
//        }
//        return loginUser.getUsername();
        return "";
    }

//    @Override
//    public MetaObjectHandler strictFillStrategy(MetaObject metaObject, String fieldName, Supplier<?> fieldVal) {
//        //if (metaObject.getValue(fieldName) == null) 不判断空值情况即可
//        {
//            Object obj = fieldVal.get();
//            if (Objects.nonNull(obj)) {
//                metaObject.setValue(fieldName, obj);
//            }
//        }
//        return this;
//    }
}

自定义批量方法

MyDuplicateSelectiveMethod
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
public class MyDuplicateSelectiveMethod extends AbstractMethod {

    private String methodName = "myDuplicateSelective";

    /**
     * @param methodName 方法名
     * @since 3.5.0
     */
    public MyDuplicateSelectiveMethod(String methodName) {
        super(methodName);
    }

    @Override
    public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
        KeyGenerator keyGenerator = new NoKeyGenerator();
        String sqlScript = "<script>\nINSERT INTO %s %s VALUES %s %s \n</script>";
        String columnScript = SqlScriptUtils.convertTrim(getAllInsertSqlColumnMaybeIf(tableInfo), "(", ")", (String) null, ",");
        String valuesScript = getAllInsertSqlPropertyMaybeIf(tableInfo);
        String sqlDuplicate = sqlDuplicate(tableInfo);
        String sql = String.format(sqlScript, tableInfo.getTableName(), columnScript, valuesScript, sqlDuplicate);


        SqlSource sqlSource = this.languageDriver.createSqlSource(this.configuration, sql, modelClass);
        return this.addInsertMappedStatement(mapperClass, modelClass, methodName, sqlSource, new NoKeyGenerator(), null, null);

    }

    /**
     * sqlDuplicate
     *
     * @return
     */
    private String sqlDuplicate(TableInfo tableInfo) {
        StringBuilder sqlDuplicate = new StringBuilder();
        sqlDuplicate.append(" ON DUPLICATE KEY UPDATE ");
        sqlDuplicate.append(" <trim suffixOverrides=\",\"> ");

        List<TableFieldInfo> fieldList = tableInfo.getFieldList();

        fieldList.forEach((tempColumn) -> {
            sqlDuplicate.append("<if test=\"null != list[0].");
            sqlDuplicate.append(tempColumn.getProperty());
            sqlDuplicate.append("\">");
            sqlDuplicate.append(tempColumn.getColumn());
            sqlDuplicate.append(" = values(");
            sqlDuplicate.append(tempColumn.getColumn());
            sqlDuplicate.append("),</if>");
        });
        sqlDuplicate.append("</trim>");
        return sqlDuplicate.toString();
    }

    /**
     * 复写方法
     *
     * @param tableInfo
     * @return
     */
    private String getAllInsertSqlColumnMaybeIf(TableInfo tableInfo) {
        return tableInfo.getKeyInsertSqlColumn(false, true) + (String) tableInfo.getFieldList().stream().map(tableFieldInfo -> {

            String sqlScript = tableFieldInfo.getInsertSqlColumn();
            String property = "list[0]." + tableFieldInfo.getProperty();
            FieldStrategy fieldStrategy = tableFieldInfo.getInsertStrategy();

            if (fieldStrategy == FieldStrategy.NEVER) {
                return null;
            } else if (fieldStrategy == FieldStrategy.IGNORED) {
                return sqlScript;
            } else {
                return fieldStrategy == FieldStrategy.NOT_EMPTY && tableFieldInfo.isCharSequence() ? SqlScriptUtils.convertIf(sqlScript, String.format("%s != null and %s != ''", property, property), false) : SqlScriptUtils.convertIf(sqlScript, String.format("%s != null", property), false);
            }
        }).filter(Objects::nonNull).collect(Collectors.joining("\n"));
    }

    /**
     * 复写insert
     *
     * @param tableInfo
     * @return
     */
    public String getAllInsertSqlPropertyMaybeIf(TableInfo tableInfo) {
        String newPrefix = "list[0].";
        String insertSql = tableInfo.getKeyInsertSqlProperty(false, "record.", true) + (String) tableInfo.getFieldList().stream().map((tableFieldInfo) -> {

            String sqlScript = "#{record." + tableFieldInfo.getProperty() + "},";
            String property = newPrefix + tableFieldInfo.getProperty();
            FieldStrategy fieldStrategy = tableFieldInfo.getInsertStrategy();

            if (fieldStrategy == FieldStrategy.NEVER) {
                return null;
            } else if (fieldStrategy == FieldStrategy.IGNORED) {
                return sqlScript;
            } else {
                return fieldStrategy == FieldStrategy.NOT_EMPTY && tableFieldInfo.isCharSequence() ? SqlScriptUtils.convertIf(sqlScript, String.format("%s != null and %s != ''", property, property), false) : SqlScriptUtils.convertIf(sqlScript, String.format("%s != null", property), false);
            }

        }).filter(Objects::nonNull).collect(Collectors.joining("\n"));

        StringBuilder sql = new StringBuilder();
        sql.append("<foreach collection=\"list\" item=\"record\" separator=\",\" >");
        sql.append("<trim prefix=\"(\" suffix=\")\" suffixOverrides=\",\">");
        sql.append(insertSql);
        sql.append("</trim>");
        sql.append("</foreach>");
        return sql.toString();
    }

}
MyQueryTableColumnsMethod
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
public class MyQueryTableColumnsMethod extends AbstractMethod {

    private String methodName = "myQueryTableColumns";

    /**
     * @param methodName 方法名
     * @since 3.5.0
     */
    public MyQueryTableColumnsMethod(String methodName) {
        super(methodName);
    }

    @Override
    public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
        String sqlScript = "<script>\nshow columns from %s;\n</script>";
        String sql = String.format(sqlScript, tableInfo.getTableName());
        SqlSource sqlSource = this.languageDriver.createSqlSource(this.configuration, sql, modelClass);
        return this.addSelectMappedStatementForOther(mapperClass, methodName, sqlSource, TableColumnsEntity.class);
    }
}
MySelectOneMethod
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
public class MySelectOneMethod extends AbstractMethod {

    private String methodName = "mySelectOne";

    /**
     * @param methodName 方法名
     * @since 3.5.0
     */
    public MySelectOneMethod(String methodName) {
        super(methodName);
    }

    @Override
    public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
        String sqlScript = "<script>\nSELECT %s FROM %s %s %s limit 1\n</script>";
        String sql = String.format(sqlScript, this.sqlSelectColumns(tableInfo, true), tableInfo.getTableName(), this.sqlWhereEntityWrapper(true, tableInfo), this.sqlComment());
        SqlSource sqlSource = this.languageDriver.createSqlSource(this.configuration, sql, modelClass);
        return this.addSelectMappedStatementForTable(mapperClass, methodName, sqlSource, tableInfo);
    }
}
MyUpdateListSelectiveMethod根据id批量修改
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
public class MyUpdateListSelectiveMethod extends AbstractMethod {

    private String methodName = "myUpdateListSelective";

    /**
     * @param methodName 方法名
     * @since 3.5.0
     */
    public MyUpdateListSelectiveMethod(String methodName) {
        super(methodName);
    }

    @Override
    public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
        StringBuilder sql = new StringBuilder();
        sql.append("<script>\n");
        sql.append("<foreach collection=\"records\" item=\"record\" index=\"index\" open=\"\" close=\"\" separator=\";\">");
        sql.append(" UPDATE ");
        sql.append(tableInfo.getTableName());
        sql.append(this.sqlSet(true, false, tableInfo, true, "record", "record."));
        sql.append(" WHERE ");
        sql.append(String.format(" %s=#{%s} ", tableInfo.getKeyColumn(), "record." + tableInfo.getKeyProperty()));
        sql.append("</foreach>");
        sql.append("\n</script>");
        SqlSource sqlSource = this.languageDriver.createSqlSource(this.configuration, sql.toString(), modelClass);
        return this.addUpdateMappedStatement(mapperClass, modelClass, methodName, sqlSource);
    }
}
MyInsertListSelectiveMethod
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
public class MyInsertListSelectiveMethod extends AbstractMethod {

    private String methodName = "myInsertListSelective";

    /**
     * @param methodName 方法名
     * @since 3.5.0
     */
    public MyInsertListSelectiveMethod(String methodName) {
        super(methodName);
    }

    @Override
    public MappedStatement injectMappedStatement(Class<?> mapperClass, Class<?> modelClass, TableInfo tableInfo) {
        KeyGenerator keyGenerator = new NoKeyGenerator();
        String sqlScript = "<script>\nINSERT INTO %s %s VALUES %s\n</script>";
        String columnScript = SqlScriptUtils.convertTrim(getAllInsertSqlColumnMaybeIf(tableInfo), "(", ")", (String) null, ",");
        String valuesScript = getAllInsertSqlPropertyMaybeIf(tableInfo);
        String sql = String.format(sqlScript, tableInfo.getTableName(), columnScript, valuesScript);
        SqlSource sqlSource = this.languageDriver.createSqlSource(this.configuration, sql, modelClass);
        return this.addInsertMappedStatement(mapperClass, modelClass, methodName, sqlSource, new NoKeyGenerator(), null, null);

    }

    /**
     * 复写方法
     *
     * @param tableInfo
     * @return
     */
    private String getAllInsertSqlColumnMaybeIf(TableInfo tableInfo) {
        return tableInfo.getKeyInsertSqlColumn(false, true) + (String) tableInfo.getFieldList().stream().map(tableFieldInfo -> {

            String sqlScript = tableFieldInfo.getInsertSqlColumn();
            String property = "list[0]." + tableFieldInfo.getProperty();
            FieldStrategy fieldStrategy = tableFieldInfo.getInsertStrategy();

            if (fieldStrategy == FieldStrategy.NEVER) {
                return null;
            } else if (fieldStrategy == FieldStrategy.IGNORED) {
                return sqlScript;
            } else {
                return fieldStrategy == FieldStrategy.NOT_EMPTY && tableFieldInfo.isCharSequence() ? SqlScriptUtils.convertIf(sqlScript, String.format("%s != null and %s != ''", property, property), false) : SqlScriptUtils.convertIf(sqlScript, String.format("%s != null", property), false);
            }
        }).filter(Objects::nonNull).collect(Collectors.joining("\n"));
    }

    /**
     * 复写insert
     *
     * @param tableInfo
     * @return
     */
    public String getAllInsertSqlPropertyMaybeIf(TableInfo tableInfo) {
        String newPrefix = "list[0].";
        String insertSql = tableInfo.getKeyInsertSqlProperty(false, "record.", true) + (String) tableInfo.getFieldList().stream().map((tableFieldInfo) -> {

            String sqlScript = "#{record." + tableFieldInfo.getProperty() + "},";
            String property = newPrefix + tableFieldInfo.getProperty();
            FieldStrategy fieldStrategy = tableFieldInfo.getInsertStrategy();

            if (fieldStrategy == FieldStrategy.NEVER) {
                return null;
            } else if (fieldStrategy == FieldStrategy.IGNORED) {
                return sqlScript;
            } else {
                return fieldStrategy == FieldStrategy.NOT_EMPTY && tableFieldInfo.isCharSequence() ? SqlScriptUtils.convertIf(sqlScript, String.format("%s != null and %s != ''", property, property), false) : SqlScriptUtils.convertIf(sqlScript, String.format("%s != null", property), false);
            }

        }).filter(Objects::nonNull).collect(Collectors.joining("\n"));

        StringBuilder sql = new StringBuilder();
        sql.append("<foreach collection=\"list\" item=\"record\" separator=\",\" >");
        sql.append("<trim prefix=\"(\" suffix=\")\" suffixOverrides=\",\">");
        sql.append(insertSql);
        sql.append("</trim>");
        sql.append("</foreach>");
        return sql.toString();
    }

}

分页数据封装类

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
/**
 * 分页数据封装类
 */
@Data
public class CommonPageVo<T> {

    private Integer pageNum;

    private Integer pageSize;

    private Integer totalPage;

    private Long total;

    private List<T> list;

    /**
     * 将MyBatis Plus 的 Page 转换为  CommonPageVo
     */
    public static <T> CommonPageVo<T> restPage(Page<T> pageResult) {
        CommonPageVo<T> result = new CommonPageVo<>();
        result.setPageNum(Long.valueOf(pageResult.getCurrent()).intValue());
        result.setPageSize(Long.valueOf(pageResult.getSize()).intValue());
        result.setTotal(pageResult.getTotal());
        result.setTotalPage(Long.valueOf(pageResult.getTotal() / pageResult.getSize() + 1).intValue());
        result.setList(pageResult.getRecords());
        return result;
    }

    /**
     * 重设mybatis-plus的Page.records集合
     *
     * @param list 新的list
     * @param page 旧的page
     * @return 装带新的list和原分页数据的page
     */
    public static <E, T> Page<E> resetRecords(List<E> list, Page<T> page) {
        Page<E> newPage = Page.of(page.getCurrent(), page.getSize(), page.getTotal(), page.searchCount());
        newPage.setRecords(list);
        return newPage;
    }

}

实体类

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
@Data
@Builder
@EqualsAndHashCode(callSuper = true)
@ToString(callSuper = true)
@NoArgsConstructor
@AllArgsConstructor
@Accessors(chain = true)
@TableName("demo")
@Schema(name = "Demo", description = "示例")
public class DemoMp extends Model<DemoMp> {

    public static LambdaQueryWrapper<DemoMp> lqw() {
        return new LambdaQueryWrapper<>();
    }

    public static QueryWrapper<DemoMp> qw() {
        return new QueryWrapper<>();
    }

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

    @JsonProperty(access = JsonProperty.Access.READ_ONLY)
    @Schema(description = "排序")
    @TableField("sort")
    private Long sort;

    @Schema(description = "姓名")
    @TableField("`name`")
    private String name;

    @Schema(description = "前台数据pc端")
    @TableField("`value`")
    private String value;

    @Schema(description = "所属的页面 0:备用 1:首页 2:关于我们 3:产品中心 4:大蒜数据中心 5:产品链 6:新闻资讯 7:公益行动 8:联系我们")
    @TableField("`page`")
    private Integer page;

    @Schema(description = "价格")
    @TableField("price")
    private BigDecimal price;

    @JsonProperty(access = JsonProperty.Access.READ_ONLY)
    @Schema(description = "创建时间")
    @TableField(value = "create_time", fill = FieldFill.INSERT)
    private LocalDateTime createTime;

    @JsonProperty(access = JsonProperty.Access.READ_ONLY)
    @Schema(description = "创建者")
    @TableField(value = "create_by", fill = FieldFill.INSERT)
    private String createBy;

    @JsonProperty(access = JsonProperty.Access.READ_ONLY)
    @Schema(description = "更新时间")
    @TableField(value = "update_time", fill = FieldFill.INSERT_UPDATE)
    private LocalDateTime updateTime;

    @JsonProperty(access = JsonProperty.Access.READ_ONLY)
    @Schema(description = "更新者")
    @TableField(value = "update_by", fill = FieldFill.INSERT_UPDATE)
    private String updateBy;

    @Version
    @JsonProperty(access = JsonProperty.Access.READ_ONLY)
    @Schema(description = "版本号")
    @TableField("version")
    private Integer version;

    @TableLogic
    @JsonProperty(access = JsonProperty.Access.READ_ONLY)
    @Schema(description = "是否删除 0:存在 1:删除")
    @TableField("deleted")
    private Integer deleted;

    @Schema(description = "角色id")
    @TableField(exist = false)
    private Long roleId;

    @Schema(description = "角色id列表")
    @TableField(exist = false)
    private List<Long> roleIdList;

    @Schema(description = "角色id列表字符串")
    @TableField(exist = false)
    private String roleIdListString;

    @JsonProperty(access = JsonProperty.Access.READ_ONLY)
    @Schema(description = "角色名称")
    @TableField(exist = false)
    private String roleName;

    @Schema(description = "角色名称列表")
    @TableField(exist = false)
    private List<String> roleNameList;

    @Schema(description = "角色名称列表字符串")
    @TableField(exist = false)
    private String roleNameListString;

    @Override
    public Serializable pkVal() {
        return this.id;
    }
}

Mapper

1
2
3
4
@Mapper
public interface DemoMpMapper extends BaseMapper<DemoMp> {

}

常用查询构造器的 SQL拼接方法

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
HashMap<SFunction<DemoMp, ?>, Object> params = new HashMap<>();
params.put(DemoMp::getCreateBy, "admin");
params.put(DemoMp::getUpdateBy, null);

String deptId = "1";
List<DemoMp> UserList = demoMpMapper.selectList(
        // new QueryWrapper<DomainExample>().lambda()
        new LambdaQueryWrapper<DemoMp>()
                .select(DemoMp::getId, DemoMp::getSort, DemoMp::getCreateTime)
                // .select(i -> i.getProperty().startsWith("test"))
                // .select(DomainExample.class, i -> i.getProperty().startsWith("test"))
                .allEq(true, params, false) // 参数3默认为true,false时不拼接"and update_by is null"
                .allEq(true, (k, v) -> true, params, false) // 参数2:BiPredicate
                .eq(true, DemoMp::getDeleted, 0L) // 等于
                .ne(true, DemoMp::getDeleted, 1L) // 不等于
                .gt(true, DemoMp::getDeleted, -1L) // 大于
                .ge(true, DemoMp::getDeleted, -1L) // 大于等于
                .lt(true, DemoMp::getDeleted, 9L) // 小于
                .le(true, DemoMp::getDeleted, 9L) // 小于等于
                .between(true, DemoMp::getCreateTime, LocalDateTime.now().minusYears(1), LocalDateTime.now().plusYears(1))
                .notBetween(true, DemoMp::getCreateTime, LocalDateTime.now().minusYears(2), LocalDateTime.now().minusYears(1))
                // Date类型 today筛选
                .ge(DemoMp::getCreateTime, Date.from(LocalDateTime.now().atZone(ZoneId.systemDefault()).toInstant()))
                .lt(DemoMp::getCreateTime, Date.from(LocalDateTime.now().plusDays(1).atZone(ZoneId.systemDefault()).toInstant()))
                // LocalDateTime类型 today筛选
                .ge(DemoMp::getCreateTime, LocalDate.now())
                .lt(DemoMp::getCreateTime, LocalDate.now().plusDays(1))
                .like(true, DemoMp::getCreateBy, "dm")
                .notLike(true, DemoMp::getCreateBy, "不模糊匹配")
                .likeLeft(true, DemoMp::getCreateBy, "min") // like '%min'
                .notLikeLeft(true, DemoMp::getCreateBy, "左模糊") // like '%左模糊'
                .likeRight(true, DemoMp::getCreateBy, "ad") // like 'ad%'
                .notLikeRight(true, DemoMp::getCreateBy, "右模糊") // like '右模糊%'
                .isNull(true, DemoMp::getSort) // sort is null
                .isNotNull(true, DemoMp::getSort) // sort is not null
                .in(true, DemoMp::getVersion, Arrays.asList(1L, 2L, 3L))
                .in(true, DemoMp::getVersion, 1L, 2L, 3L)
                .notIn(true, DemoMp::getVersion, Arrays.asList(9L, 9L, 9L))
                .notIn(true, DemoMp::getVersion, 9L, 9L, 9L)
                .inSql(true, DemoMp::getVersion, "1,5,7,7")
                .inSql(true, DemoMp::getVersion, "select version from alice_tree where version > 0")
                .notInSql(true, DemoMp::getVersion, "9,9")
                .notInSql(true, DemoMp::getVersion, "select version from alice_tree where version < 0")
                .groupBy(true, Arrays.asList(DemoMp::getId, DemoMp::getSort)) // group by id,sort
                // .groupBy(true, DomainExample::getId, DomainExample::getSort)
                .groupBy(true, DemoMp::getId)
                .having(true, "sum(version) >= 1") // having sum(age) > 2 having必须跟groupBy一起使用
                .having(true, "sum(version) > {0}", 2) // having sum(age) > 2 having必须跟groupBy一起使用
                .and(true, i -> i.ne(true, DemoMp::getVersion, 0L).or().eq(true, DemoMp::getVersion, 1L)) // and 拼接
                .or(true, i -> i.ne(true, DemoMp::getVersion, 0L).eq(true, DemoMp::getVersion, 1L)) // or 嵌套
                .nested(true, i -> i.ne(true, DemoMp::getVersion, 0L).eq(true, DemoMp::getVersion, 1L)) // 正常嵌套 不带 AND 或者 OR (version <> 0 and version = 1)
                .func(i -> {
                    if (true) {
                        i.eq(DemoMp::getId, 1L);
                    } else {
                        i.ne(DemoMp::getId, 1L);
                    }
                }) // func 方法(主要方便在出现if...else下调用不同方法能不断链)
                .apply(Objects.nonNull(deptId), "FIND_IN_SET ('" + deptId + "', ancestors )") // 拼接 sql {@link https://baomidou.com/pages/10c804/#apply)
                .apply("date_format(create_time,'%Y-%m-%d') = '2023-03-24'")// date_format(creat_time,'%Y-%m-%d') = '2023-03-24'")
                .last("limit 1") // 无视优化规则直接拼接到 sql 的最后, 只能调用一次,多次调用以最后一次为准 有sql注入的风险,请谨慎使用
                .exists(true, "select id from table where age = 1") // exists (select id from table where age = 1)
                .exists(true, "select id from table where age = {0}", 1) // exists (select id from table where age = 1)
                .notExists(true, "select id from table where age = 1") // not exists (select id from table where age = 1)
                .notExists(true, "select id from table where age = {0}", 1) // not exists (select id from table where age = 1)
                .orderByAsc(true, Arrays.asList(DemoMp::getId, DemoMp::getSort))
                // .orderByAsc(true, DomainExample::getId, DomainExample::getSort)
                .orderByDesc(true, Arrays.asList(DemoMp::getId, DemoMp::getSort))
                // .orderByDesc(true, DomainExample::getId, DomainExample::getSort)
                .orderBy(true, true, Arrays.asList(DemoMp::getId, DemoMp::getSort)) // order by id ASC,sort ASC
                // .orderBy(true, true, DomainExample::getId, DomainExample::getSort) // order by id ASC,sort ASC
                .orderBy(true, true, Collections.singletonList(DemoMp::getId)));

测试:selectList、selectCount、selectMaps、selectObjs、selectPage

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
List<DemoMp> users = demoMpMapper.selectList(null);

Long aLong = demoMpMapper.selectCount(new LambdaQueryWrapper<>());

List<Map<String, Object>> selectMaps = demoMpMapper.selectMaps(new QueryWrapper<DemoMp>()
        .select("count(*) as count")
        .select("max(id) as maxAge")
        .select("min(id) as minAge")
        .select("sum(id) as sumAge")
        .select("avg(id) as avgAge"));

List<Object> selectedObjs = demoMpMapper.selectObjs(new LambdaQueryWrapper<>());

// queryWrapper.eq("age", 21);  // 年龄等于18
Page<DemoMp> page = new Page<>(pageNum, pageSize);
LambdaQueryWrapper<DemoMp> lqw = DemoMp.lqw();
Page<DemoMp> pageResult = demoMpMapper.selectPage(page, null);
Page<DemoMp> pageResult2 = demoMpMapper.selectPage(page, lqw);

List<DemoMp> records = pageResult.getRecords();    // 获取总记录列表
records.forEach(System.out::println);    // 遍历
pageResult.getPages();// 总页数
pageResult.getTotal();// 总记录数
pageResult.getCurrent();// 当前页码
pageResult.getSize();// 每页记录数
pageResult.hasNext();// 是否有下一页
pageResult.hasPrevious();// 是否有上一页

测试:防全表更新与删除 插件

1
2
3
4
5
6
7
// UPDATE alice_tree SET update_time=?, update_by=? WHERE id=? AND deleted=0
demoMpMapper.update(DemoMp.builder().id(100L).build(),
        new LambdaUpdateWrapper<DemoMp>()
                .set(true, DemoMp::getId, 100L, "javaType=Long")
                .set(true, DemoMp::getName, "zhangxin")
                .setSql(true, "updateTime = '2023-03-20 00:00:00'"));
// 执行会报错:Prohibition of table update operation(禁止全表更新。)
皖ICP备2024056275号-1
发表了78篇文章 · 总计148.99k字
本站已稳定运行