sql及list去重操作、json操作

一、sql使用场景1：分页查询任务对应的最后一条审核原因

1、查询主sql：用主表task链接审核记录表resion

SELECT * FROM task t LEFT JOIN resion r ON t.id=r.task_id  LEFT JOIN xxxtable;

2、问题分析：一个任务对应多条审核记录，链接查询会产生笛卡尔积，结果集错误。对sql进行改造

SELECT
	* 
FROM
	task t
	LEFT JOIN (
	SELECT
		r.* 
	FROM
		resion r
		INNER JOIN ( SELECT task_id, max( create_time ) create_time FROM resion GROUP BY task_id ) a ON a.task_id = r.task_id 
	AND a.create_time = r.create_time 
	) e ON e.task_id = t.task_id

3、优化：当同一时间出现两条审核记录时依然避免不了数据集错误，所以可以加跟多的条件来保证附表的唯一性

SELECT
	* 
FROM
	task t
	LEFT JOIN (
	SELECT
		r.* 
	FROM
		(
		SELECT
			a.*,
			count( 1 ) onlyValue 
		FROM
			resion a
			INNER JOIN resion b ON a.task_id = b.task_id 
			AND a.create_time <= b.create_time 
		GROUP BY
			a.resion_id 
		) r 
	WHERE
		r.onlyValue = 1 
	) e ON e.task_id = t.task_id

二、list去重处理

1、采用TreeSet来去重

1.1、两次转换

// 这里是没去重的原始数据
List<TaskInfo> taskList=...;  
// 根据id属性去重
Set<TaskInfo> taskSet = new TreeSet<>(Comparator.comparing(TaskInfo::getId));
taskSet.addAll(taskList);
taskList=new ArrayList<TaskInfo>(taskSet);

1.2、流式转换

 List<TaskInfo> newList = taskList.stream().collect(Collectors
  .collectingAndThen(
   Collectors.toCollection(() -> new TreeSet<>(Comparator.comparing(TaskInfo::getId))),
   ArrayList::new));

1.3、多属性联合去重

Set<TaskInfo> taskSet = new TreeSet<>(Comparator.comparing(o -> (o.getId() + "" + o.getDeptId())));
taskSet .addAll(taskList);
taskList=new ArrayList<TaskInfo>(taskSet);

2、采用map处理

2.1、流过滤

List<TaskInfo> taskList = new ArrayList<>();
oldList.stream().filter(distinctByKey(t ->t.getId())).forEach(taskList::add);

static <T> Predicate<T> distinctByKey(Function<? super T, ?> keyExtractor) {
 Map<Object,Boolean> seen = new ConcurrentHashMap<>();
 return t -> seen.putIfAbsent(keyExtractor.apply(t), Boolean.TRUE) == null;
}

2.2、流分组循环取第一个

3、不同对象集合多属性求差集

3.1、用containsKey做过滤

// 大集合
List<UserCommonEntity> userEntity = new ArrayList<>();
// 小集合
List<UserEventEntityDO> userDo = new ArrayList<>();
// 求差集
List<UserCommonEntity> difUser = userEntity.stream.filter(
		//把集合转成以userId+","+userName为key的map
		item -> !userDo.stream().collect(Collectors.toMap(params -> params.getUserId() + "," + params.getUserName(), value -> value))
				//判断该key是否存在
				.containsKey(ruleParams.getUserId() + "," + ruleParams.getUserName())
).collect(Collectors.toList())

3.2、用contains做过滤

//当然我们也可以在userEntity和userDo实体类里边分别定义一个compare字段重写get方法(return userId+","+userName)然后用compare进行比较最终简化如下：
userEntity=userEntity.stream().filter(item -> !userDo.stream().map(UserEventEntityDO::getCompare).collect(Collectors.toList()).contains(item.getCompare())).collect(Collectors.toList());

三、mysql的json操作

1、场景应用：文章主表、标签表，主表里有个字段json用来存放标签id的集合

2、mybatis-plush实体类参考，字段上的 ToListStringHandler.class解析类是拷贝mybatis-plush做简化配置的

import com.baomidou.mybatisplus.annotation.*;
import com.ruoyi.system.handler.ToListStringHandler;
import lombok.Data;

import javax.validation.constraints.Size;
import java.io.Serializable;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * <p>
 * 亲子互动主表
 * </p>
 *
 * @author morik
 * @since 2022-07-20
 */
@Data
@TableName(value = "gf_parenting", autoResultMap = true)
public class GfParenting implements Serializable {

    /**
     * 亲子互动唯一标识
     */
    @TableId(value = "id", type = IdType.AUTO)
    private Long id;

    /**
     * 发布人
     */
    @TableField("publish_id")
    private Long publishId;

    /**
     * 标题
     */
    @Size(max = 300)
    @TableField("title")
    private String title;

    /**
     * 内容
     */
    @Size(max = 1000)
    @TableField("content")
    private String content;

    /**
     * 关联的所有标签id
     */
    @TableField(typeHandler = ToListStringHandler.class, value = "tags_ids")
    private List<String> tagsIds;
    /**
     * 内容图片地址
     */
    @TableField(typeHandler = ToListStringHandler.class, value = "image_urls")
    private List<String> imageUrls;
    /**
     * 0 待审核 1审核通过(上线) 2驳回 4下线
     */
    @TableField("status")
    private Integer status;

    /**
     * 手动排序，默认0，创建时间为第二排序规则
     */
    @TableField("sort")
    private Integer sort;

    /**
     * 是否上线  0下线 1上线 （弃用）
     */
    @TableField("is_line")
    private Integer isLine;

    /**
     * 是否删除  0正常 1 删除
     */
    @TableField("is_delete")
    @TableLogic
    private Integer isDelete;

    /**
     * 创建时间
     */
    @TableField(value = "create_time", fill = FieldFill.INSERT)
    private Date createTime;

    /**
     * 更新时间
     */
    @TableField(value = "update_time", fill = FieldFill.INSERT_UPDATE)
    private Date updateTime;


}

3、Handler配置参考

3.1、为了兼容不同List的解析我们先新建一个父类统一处理(如果有两个表的实体字段自定义解析Handler都继承com.baomidou.mybatisplus.extension.handlers.AbstractJsonTypeHandler这个类可能会导致只有一个Handler生效，其他的Handler由于解析的对象不符直接报异常比如：我用户表里边的tageList解析会调用到我订单表里边的orderItemList)

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.fastjson.JSON;
import com.google.gson.Gson;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedJdbcTypes;
import org.apache.ibatis.type.MappedTypes;

import java.lang.reflect.Type;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


@MappedJdbcTypes(JdbcType.VARBINARY)
@MappedTypes({List.class})
public abstract class ListTypeHandler<T> extends BaseTypeHandler<List<T>> {
    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, List<T> parameter, JdbcType jdbcType) throws SQLException {
        String content = CollUtil.isEmpty(parameter) ? null : JSON.toJSONString(parameter);
        ps.setString(i, content);
    }

    @Override
    public List<T> getNullableResult(ResultSet rs, String columnName) throws SQLException {
        return this.getListByJsonArrayString(rs.getString(columnName));
    }

    @Override
    public List<T> getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        return this.getListByJsonArrayString(rs.getString(columnIndex));
    }

    @Override
    public List<T> getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        return this.getListByJsonArrayString(cs.getString(columnIndex));
    }


    private List<T> getListByJsonArrayString(String content) {
        return StrUtil.isBlank(content) ? new ArrayList<>() : new Gson().fromJson(content, getType());
// 根据个人习惯采用Google或者阿里的json转对象工具，我用的是上边Google的
//        return StrUtil.isBlank(content) ? new ArrayList<>() : JSON.parseObject(content, this.specificType());
    }

    /**
     * 具体类型，由子类提供
     *
     * @return 具体类型
     */
//    protected abstract TypeReference<...> specificType();
    protected abstract Type getType();
}

3.2、提供一个List<String>解析的hander

import com.google.gson.reflect.TypeToken;
import com.ruoyi.system.domain.entity.SrcInfo;
import org.apache.ibatis.type.JdbcType;
import org.apache.ibatis.type.MappedJdbcTypes;
import org.apache.ibatis.type.MappedTypes;

import java.io.Serializable;
import java.lang.reflect.Type;
import java.util.List;

@MappedTypes({List.class})
@MappedJdbcTypes({JdbcType.VARCHAR})
public class ToListStringHandler extends ListTypeHandler<String> implements Serializable {
    private static final long serialVersionUID = 5157322812663088534L;

    @Override
    protected Type getType() {
        return new TypeToken<List<String>>() {
        }.getType();
    }
}

3.3、如果还有其他的List<T>可以按一下方式提供

import com.google.gson.reflect.TypeToken;
import com.ruoyi.system.domain.entity.SrcInfo;

import java.lang.reflect.Type;
import java.util.List;


public class SrcUrlHandler extends ListTypeHandler<SrcInfo> {
    @Override
    protected Type getType() {
        return new TypeToken<List<SrcInfo>>() {
        }.getType();
    }

}

4、最后在yml中加入Handler扫描位置配置就可以用了

# MyBatis Plus配置
mybatis-plus:
  # 配置ToListStringHandler类所在的包
  type-handlers-package: com.xxxx.handler

5、sql查询参考(把json数组作为连接的in条件)

SELECT
	d.tagsNames,
	c.* 
FROM
	gf_parenting c
	LEFT JOIN (
	SELECT
		GROUP_CONCAT( s.NAME ) tagsNames,
		t.id 
	FROM
		gf_parenting t
	INNER JOIN gf_tag s ON JSON_CONTAINS( t.tags_ids, CONVERT ( s.id, CHAR ) ) = 1 
	) d ON d.id = c.id

6.如果要知道json数组中是否同时包含标签id为1,2,3的条件判断可以这样写

//  前边[1,2,3]是海字段的值 后边是针都写成字符
where JSON_CONTAINS( t.tags_ids,  CONVERT ( s.id, CHAR )) )
where JSON_CONTAINS( t.tags_ids, JSON_ARRAY('1','3','2') )
// 如果海的格式是["1","2","3"],针就要用concat_ws拼接双引号
JSON_CONTAINS( t.tags_ids, concat_ws( s.id, '"', '"', NULL ) ) 

7、json其他操作

    查询表达式：对象为json列->'$.键', 数组为json列->'$.键[index]'

 对象为JSON_EXTRACT(json列 , '$.键')，数组为JSON_EXTRACT(json列 , '$.键[index]')

  JSON_UNQUOTE函数可以将字符引号去掉

  JSON_INSERT()插入新值

  JSON_SET()插入新值，并覆盖已经存在的值

  JSON_REPLACE()只替换存在的值

  JSON_REMOVE()删除JSON元素

8、备注：mybatis原生使用json配置

8.1、实体类字段可以是数组、对象、list

// 关联的老师id
private List<String> teacherIds;
// 关联的学生id
private String[] studentIds;
// 关联的教室详情信息
private ClassRoomInfo classRoomInfo;
// 关联的学科详情列表
private List<DisciplineInfo> disciplineInfo;

8.2、xml中的配置

<!--解析sql查询的resultMap映射中配置 jdbcType 和 typeHandler-->
<result property="groupIds" column="group_ids" jdbcType="JAVA_OBJECT"
                typeHandler="com.baomidou.mybatisplus.extension.handlers.JacksonTypeHandler"/>
<!--插入以及更新的时候在字段后边指定typeHandler-->
<if test="groupIds != null">
                #{groupIds,typeHandler=com.baomidou.mybatisplus.extension.handlers.JacksonTypeHandler},
            </if>

<if test="classRoomInfo!= null">class_room_info=
                #{classRoomInfo,typeHandler=com.baomidou.mybatisplus.extension.handlers.JacksonTypeHandler},
            </if>


<!--查询就参考5、6、7-->

8.3、用@Select("SELECT * FROM gf_parenting c .........")的方式配置

@Results({@Result(property="groupIds",column="group_ids",jdbcType="JAVA_OBJECT"
            typeHandler="com.baomidou.mybatisplus.extension.handlers.JacksonTypeHandler")})