mybatis-plus总结

mybatis-plus自定义分页、SQL+wrapper一起使用

   @Override
    public Page<TechnologyEnterpriseDto> pageTechnologyEnterprises(TechnologyEnterprisePageReq dto) {
        QueryWrapper<TechnologyEnterprise> wrapper = new QueryWrapper<>();

        if (StringUtils.isNotBlank(dto.getQualificationLevel())) {
            List<String> ids = this.getIdListByQualificationLevel(dto.getQualificationLevel());
            if (CollectionUtils.isEmpty(ids)) {
                return new Page<>();
            }
            wrapper.in("te.id", ids);
        }

        wrapper.eq(StringUtils.isNotBlank(dto.getEnterpriseType()), "enterprise_type", dto.getEnterpriseType())
                .and(StringUtils.isNotBlank(dto.getKeyword()), w -> {
                    w.like("enterprise_name", dto.getKeyword()).or().like("unified_social_code", dto.getKeyword());
                });
        if (StringUtils.isNotBlank(dto.getAllTextSearch())) {
            String tsQuery = PostgresAllTextSearchUtil.getTsQuery(this.customMapper.selectTsVector(dto.getAllTextSearch()));
            //apply拼接SQL
            wrapper.and(w -> w.apply(String.format("doc @@ to_tsquery('%s')", tsQuery)));
            wrapper.orderByDesc(String.format("ts_rank(doc, to_tsquery('%s'))", tsQuery));
        }
        return this.technologyEnterpriseMapper.selectSelfPage(dto.page(), wrapper);
    }
-------------
//自定义SQL也这么用，在多表关联的时候：@Param(Constant.WRAPPER) Wrapper<TechnologyEnterprise> wrapper
Page<TechnologyEnterpriseDto> selectSelfPage(Page<TechnologyEnterpriseDto> page, @Param(Constant.WRAPPER) Wrapper<TechnologyEnterprise> wrapper);
-------------
<select id="selectSelfPage" resultType="com.lt.yl.mine.entity.dto.TechnologyEnterpriseDto">
        WITH record AS (
            SELECT "id", to_tsvector('ch_part', business_scope)|| to_tsvector('ch_part', good_at_realm) AS doc FROM technology_enterprise
        )SELECT * FROM technology_enterprise te JOIN record ON record."id" = te."id"
        ${ew.customSqlSegment}
</select>

QueryWrapper用法注意
QueryWrapper是最基础的查询方式
LambdaQueryWrapper是jave的特性 lambda表达式查询
LambdaQueryWrapper T是一个泛型啊
当查询的数据只涉及单表的时候用LambdaQueryWrapper、而且用mybatis-plus自带的page分页就可以了
selectSelPage 和R.c 是我自定义的而且一般用来查询多表的

package com.lt.yl.mine.utils;

import com.baomidou.mybatisplus.core.toolkit.support.SFunction;

/**
 * 方便引用
 */
public class R {

    /**
     *  引用LambdaUtils.columnToString
     * @param fn 函数
     * @param <T> 参数类型
     * @param <R> 结果类型
     * @return
     */
    public static <T, R> String c(SFunction<T, R> fn) {
        return LambdaUtils.columnToString(fn);
    }
}
------------------------------------
package com.lt.yl.mine.utils;

import com.baomidou.mybatisplus.core.toolkit.support.SFunction;
import com.lt.yl.mine.exception.ProjectException;

import java.io.Serializable;
import java.lang.invoke.SerializedLambda;
import java.lang.reflect.Method;
import java.util.Map;
import java.util.concurrent.ConcurrentHashMap;

/**
 * jdk1.8 中继承Serializable的函数式接口可通过反射获取SerializedLambda对象
 */
public class LambdaUtils {
    /**
     * 缓存反射过的类
     */
    private static Map<Class, SerializedLambda> CLASS_LAMBDA_CACHE = new ConcurrentHashMap<>();

    private static String GET_METHOD_PREFIX = "get";

    private static String SET_METHOD_PREFIX = "set";

    /**
     * 通过getter/setter获取数据库列名
     * @param fn 函数
     * @param <T> 参数类型
     * @param <R> 结果类型
     * @return
     */
    public static <T, R> String columnToString(SFunction<T, R> fn) {
        SerializedLambda lambda = getSerializedLambda(fn);
        String methodName = lambda.getImplMethodName();
        if (methodName.startsWith(GET_METHOD_PREFIX) || methodName.startsWith(SET_METHOD_PREFIX)) {
            return ConverterUtils.camelToUnderline(methodName.substring(3));
        } else {
            throw new ProjectException("please income a getter/setter method");
        }
    }

    private static SerializedLambda getSerializedLambda(Serializable fn) {
        SerializedLambda lambda = CLASS_LAMBDA_CACHE.get(fn.getClass());
        if(lambda == null) {
            try {
                Method method = fn.getClass().getDeclaredMethod("writeReplace");
                method.setAccessible(Boolean.TRUE);
                lambda = (SerializedLambda) method.invoke(fn);
                CLASS_LAMBDA_CACHE.put(fn.getClass(), lambda);
            } catch (Exception e) {
                throw new ProjectException("get {} SerializedLambda error", fn.getClass());
            }
        }
        return lambda;
    }
}
-------------------------------------
package com.lt.yl.mine.utils;

import org.apache.commons.lang3.StringUtils;

import java.text.DecimalFormat;
import java.util.*;

public class ConverterUtils {
    /**
     * 将驼峰转化为下划线隔开字符串
     * @param param
     * @return
     */
    public static String camelToUnderline(String param){
        if (param == null || "".equals(param.trim())) {
            return "";
        }
        int len = param.length();
        StringBuilder sb = new StringBuilder(len);
        for (int i = 0; i < len; i++) {
            char c = param.charAt(i);
            if(i == 0 && Character.isUpperCase(c)){
                sb.append(Character.toLowerCase(c));
            }else if(Character.isUpperCase(c) && i != 0) {
                sb.append("_");
                sb.append(Character.toLowerCase(c));
            } else {
                sb.append(c);
            }
        }
        return sb.toString();
    }
    /**
     * 将下滑线隔开的字符串转化为驼峰
     * @param param
     * @return
     */
    public static String underlineToCamel(String param) {
        if (param == null || "".equals(param.trim())) {
            return "";
        }
        int len = param.length();
        StringBuilder sb = new StringBuilder(len);
        for (int i = 0; i < len; i++) {
            char c = param.charAt(i);
            if(i == 0){
                sb.append(Character.toLowerCase(param.charAt(i)));
            }else if (c == '_') {
                if (++i < len) {
                    sb.append(Character.toUpperCase(param.charAt(i)));
                }
            } else {
                sb.append(c);
            }
        }
        return sb.toString();
    }
    public static Map<String ,Object> underlineToCamel(Map<String, Object> map) {
        if (map == null || map.isEmpty()) {
            return map;
        }
        Map<String ,Object> resultMap = new HashMap<>();
        map.forEach((k, v) -> {
            resultMap.put(underlineToCamel(k), v);
        });
        return resultMap;
    }
    public static List<Map<String ,Object>> underlineToCamel(List<Map<String, Object>> mapList) {
        if (mapList == null || mapList.isEmpty()) {
            return mapList;
        }
        List<Map<String ,Object>> resultMapList = new ArrayList<>(mapList.size());
        Map<String, String> mapHandler = new HashMap<>();
        Map<String, Object> firstMap = mapList.get(0);
        Map<String, Object> mappingFirstMap = new HashMap<>();
        firstMap.forEach((k, v) -> {
            String camel = underlineToCamel(k);
            mappingFirstMap.put(camel, v);
            mapHandler.put(k, camel);
        });
        resultMapList.add(mappingFirstMap);
        for (int i = 1; i < mapList.size(); i++) {
            Map<String ,Object> resultMap = new HashMap<>();
            mapList.get(i).forEach((k, v) -> {
                resultMap.put(mapHandler.get(k), v);
            });
            resultMapList.add(resultMap);
        }
        return resultMapList;
    }
    /**
     * parseDoubleDecimal
     * @param value 需要转化的值
     * @return
     */
    public static String parseDouble2Decimal(Object value){
        if(value == null || StringUtils.isBlank(value.toString())){
            return "0";
        }
        double a = Double.parseDouble(value.toString());
        if (a == 0) {
            return  "0";
        }
        return new DecimalFormat("0.00").format(value);
    }
    public static Double parseDouble(String s) {
        if (StringUtils.isNotBlank(s)) {
            return Double.parseDouble(s);
        }
        return null;
    }
    public static String parseString(Object obj) {
        if (obj != null && StringUtils.isNotBlank(obj.toString())) {
            return obj.toString();
        }
        return null;
    }
    public static String toUpperCaseFirstOne(String s) {
        if(Character.isUpperCase(s.charAt(0)))
            return s;
        else
            return Character.toUpperCase(s.charAt(0)) + s.substring(1);
    }
    public static void main(String[] args) {
        Map<String ,Object> map = new HashMap<>();
        map.put("test_date", 21);
        map.put("test_string", 21);
        underlineToCamel(map).keySet().forEach(System.out::println);
    }
}

condition参数用法

condition参数，它是一个布尔型的参数，意思就是是否将该sql语句（像in()、like()）加在总sql语句上
首先我们自己来实现一个和condition参数一样功能的方法。
查询username包含字符k，并且age属于[22 , 40 , 30 ]。

    @Test
    public void selectList(){
       String username = "k";
       List<Integer> ageList = Arrays.asList(22 , 40 , 30);
       List<User> userList = userMapper.selectList(condition(username , ageList));
       userList.forEach(System.out::println);
    }

    public QueryWrapper<User> condition(String username , List<Integer> ageList){
        QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();
        if(!StringUtils.isEmpty(username)){
            userQueryWrapper.like("username" , username);
        }
        if(!CollectionUtils.isEmpty(ageList)){
            userQueryWrapper.in("age" , ageList);
        }
        return userQueryWrapper;
    }
//等同于---------------
    public QueryWrapper<User> condition(String username , List<Integer> ageList){
        QueryWrapper<User> userQueryWrapper = new QueryWrapper<>();
//        if(!StringUtils.isEmpty(username)){
//            userQueryWrapper.like("username" , username);
//        }
//        if(!CollectionUtils.isEmpty(ageList)){
//            userQueryWrapper.in("age" , ageList);
//        }
        userQueryWrapper.like(!StringUtils.isEmpty(username) , "username" , username)
                        .in(!CollectionUtils.isEmpty(ageList) , "age" , ageList);
        return userQueryWrapper;
    }

常用字段自动填充

一、填充字段处理

//使用@TableField注解标记实体类中的哪些字段需要填充：
@Data
public class User {
    private Long id;
    private String name;
    private Integer age;
    private String email;

    @TableField(fill = FieldFill.INSERT)
    private Date createTime;
    @TableField(fill = FieldFill.INSERT_UPDATE)
    private Date updateTime;
}

FieldFill是一个枚举，用于指定在何种情况下会自动填充，有如下几种可选值：

DEFAULT：默认不处理
INSERT：插入时自动填充字段
UPDATE：更新时自动填充字段
INSERT_UPDATE：插入和更新时自动填充字段

二、自定义填充默认数值
编写公共字段填充处理器类，该类继承了MetaObjectHandler类，重写 insertFill和updateFill方法，我们在这两个方法中获取需要填充的字段以及默认填充的值。

填充处理器MyMetaObjectHandler在Spring Boot中需要声明@Component或@Bean注入
strictInsertFill和strictUpdateFill方法第二个参数写的是实体类里的属性名，不是对应数据库字段名。

@Component
public class MyMetaObjectHandler implements MetaObjectHandler {

    @Override
    public void insertFill(MetaObject metaObject) {
        this.strictInsertFill(metaObject, "createTime", Date.class, new Date());
        this.strictInsertFill(metaObject, "updateTime", Date.class, new Date());
    }

    @Override
    public void updateFill(MetaObject metaObject) {
        this.strictUpdateFill(metaObject, "updateTime", Date.class, new Date());
    }
}
----------
如果是3.3.0后面的版本，比如3.3.1.8，也可以改用下面更简单的写法（3.3.0不要用该方法，有bug）

@Component
public class MyMetaObjectHandler implements MetaObjectHandler {

    @Override
    public void insertFill(MetaObject metaObject) {
        this.fillStrategy(metaObject, "createTime", new Date());
        this.fillStrategy(metaObject, "updateTime", new Date());
    }

    @Override
    public void updateFill(MetaObject metaObject) {
        this.fillStrategy(metaObject, "updateTime", new Date());
    }
}
------------------
在一些比较旧的版本，为填充字段设置值的API如下，3.3.0之后已经不建议使用

this.setFieldValByName("createTime",new Date(),metaObject);
 this.setFieldValByName("updateTime",new Date(),metaObject);

Lambda表达式

Optional方法

如果对象即可能是 null 也可能是非 null，你就应该使用 ofNullable() 方法：

检查是否有值的另一个选择是 ifPresent() 方法。该方法除了执行检查，还接受一个Consumer(消费者) 参数，如果对象不是空的，就对执行传入的 Lambda 表达式

代码中改写

Optional.ofNullable( //非空判断
    this.fundAccountMapper.selectById(dto.getFundAccountId())) //对查询出的值做非空判断
    .ifPresent(fa -> { //拿到集合里面的每个对象
            dto.setCompanyName(fa.getCompanyName());
            dto.setSucc(fa.getSucc());
            dto.setFundAccount(fa.getFundAccount());
            dto.setKsmc(
                Optional.ofNullable(this.tKsInfoMapper.selectById(fa.getMineId()))
                .map(TKsInfo::getKsmc)//返回一个数据Ksmc
                .orElse(null));//空就返回null

MP分页bug记录

使用自带分页的时候，where语句的条件必须放在select字段里，不然会找不到