本博客只写一些开发中用到的技术点后续会不断更新就当是个笔记了

最新推荐文章于 2023-12-28 21:50:07 发布

原创最新推荐文章于 2023-12-28 21:50:07 发布 · 216 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#java

笔记专栏收录该内容

6 篇文章

订阅专栏

本文介绍了如何使用MyBatis递归查询组织结构，通过SQL获取子节点，并展示了将List<String>转换为Long数组和Long[]的操作。还涵盖了MongoDB的分组查询及排序技巧。

1.mybatis递归查询当前节点下的所有子节点
sql语句:

SELECT
	id 
FROM
	(
	SELECT
		t1.id,
	IF
		( FIND_IN_SET( pid, @pids ) > 0, @pids := CONCAT( @pids, ',', id ), 0 ) AS ischild 
	FROM
		( SELECT id, pid FROM t_org	t WHERE  t.type ='2' and t.status != '2'  ORDER BY pid, id ) t1,
		( SELECT @pids := 233883831175143424 ) t2 
	) t3 
WHERE
	ischild != 0

mapper.xml这么写

<select id="selectIsChild" parameterType="java.lang.Long" resultType="java.lang.Long">
        SELECT
	    id
        FROM
        (
        SELECT
            t1.id,
        IF
            ( FIND_IN_SET( pid, @pids ) > 0, @pids := CONCAT( @pids, ',', id ), 0 ) AS ischild
        FROM
            ( SELECT id, pid FROM t_org	t WHERE  t.type ='2' and t.status != '2'  ORDER BY pid, id ) t1,
            ( SELECT @pids := #{id} ) t2
        ) t3
        WHERE
	    ischild != 0
    </select>

2.JDK1.8 List<String> 转List<Long>

请求参数:
List<String> signupIds;

转化:
思路就是把List转化成stream流然后用调用map转化格式在调用collect重新组装成转化格式后的新List

List<Long> ids = form.getSignupIds().stream().map(Long::valueOf).collect(Collectors.toList());

1.List<String>转Long[]

 List<String> list = course.getKnowledgeCategoryInfo().stream().map(KV::getId).collect(Collectors.toList());
                               
Long[] collect = list.stream().map(Long::valueOf).toArray(Long[] :: new);

MongoDB分组查询并且取最小时间和最大时间

db.train_user_node_study_detail.aggregate([
	{
			$match: {
				"userId": "273850549090826221",
				"trainUserId":"273850548794334856",
				"trainUserNodeId":"273850555844079950"
			}
	},
	{
		$group:{
			_id:{"trainUserId":"$trainUserId","trainUserNodeId":"$trainUserNodeId","courseNodeName":"$courseNodeName",},
			startTime:{$min:"$startTime"},
			endTime:{$max:"$endTime"}
		}
	},
	{
		$sort:{
			startTime:1
		}
	}
])

查询得到的结果
在这里插入图片描述
排序正序是1 倒序是-1
java代码如下:

 public MongoStudyDetailResponseResult queryMongoTrainUserNodeStudyDetail(TrainUserNodeStudyDetailForm form) {
        String uid = UserUtil.getUid();
        Criteria criteria = new Criteria();
        //用户id
        if (StringUtils.isNotEmpty(uid)) {
            criteria.and("userId").is(uid);
        }
        //train_user_id
        if (StringUtils.isNotEmpty(form.getId())) {
            criteria.and("trainUserId").is(form.getId());
        }
        //trainUserNodeId
        if (StringUtils.isNotEmpty(form.getTrainUserNodeId())) {
            criteria.and("trainUserNodeId").is(form.getTrainUserNodeId());
        }
        //聚合查询分组排序操作
        Aggregation aggregation = Aggregation.newAggregation(
                Aggregation.match(criteria),
                Aggregation.group("trainUserId", "trainUserNodeId", "courseNodeName").min("startTime").as("startTime").max("endTime").as("endTime"),
                Aggregation.sort(new Sort(new Sort.Order(Sort.Direction.ASC, "startTime"))));
        AggregationResults<TrainUserNodeStudyDetailEntity> trainUserNodeStudyDetailEntities = mongoTemplate.aggregate(aggregation, "train_user_node_study_detail", TrainUserNodeStudyDetailEntity.class);
        if (CollectionUtil.isEmpty(trainUserNodeStudyDetailEntities.getMappedResults()) || trainUserNodeStudyDetailEntities == null) {
            return new MongoStudyDetailResponseResult(StudyCode.NO_TRAINING, null);
        }
        //trainUserNodeStudyDetail的所有数据
        List<TrainUserNodeStudyDetailEntity> trainUserNodeStudyDetailEntityList = trainUserNodeStudyDetailEntities.getMappedResults();
        for (TrainUserNodeStudyDetailEntity trainUserNodeStudyDetailEntity : trainUserNodeStudyDetailEntityList) {
            String courseNodeName = trainUserNodeStudyDetailEntity.getCourseNodeName();
            //取下标为1的是章节
            List<String> collect = Arrays.stream(courseNodeName.split("&&")).collect(Collectors.toList());
            if (CollectionUtil.isEmpty(collect) || collect.size()<2){
                return new MongoStudyDetailResponseResult(CommonCode.SUCCESS, null);
            }
            trainUserNodeStudyDetailEntity.setCourseNodeName(collect.get(1));
        }
        return new MongoStudyDetailResponseResult(CommonCode.SUCCESS, trainUserNodeStudyDetailEntityList);
    }