日常代码记录

发表于 | 分类于

1. mybatis的n+1（1次主查询会伴随着n次副查询，即collection查询）

场景：要查询Meeting对象，同时需要关联查询出每一个meeting对象对应的List和List。如果按照普通的查询 查询，会导致n+1查询。

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
@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@TableName("xn_meeting")
public class Meeting implements Serializable {

    private static final long serialVersionUID = 1L;

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

    /**
     * 会议名称
     */
    private String name;

    /**
     * 会议创建人
     */
    private Long createUserId;

    /**
     * 会议资料集合
     */
    @TableField(exist = false)
    private List<FileEntity> fileList;

    /**
     * 参会人员详细信息
     */
    @TableField(exist = false)
    private List<MeetingUsersEntity> userList;

}
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
@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@TableName("xn_file")
public class FileEntity implements Serializable {

    private static final long serialVersionUID = 1L;

    private String uuid;

    /**
     * 资料名称
     */
    private String fileName;

    /**
     * 文件大小
     */
    private Long fileSize;

    /**
     * 文件类型
     */
    private String fileType;

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
@Data
@EqualsAndHashCode(callSuper = true)
@Accessors(chain = true)
public class MeetingUsersEntity extends User implements Serializable {

    private static final long serialVersionUID = 1L;

    /**
     * 参会人员是否参会标识   0:参会 1：不参会
     */
    private Integer meetingStatus;

    /**
     * 会议室名称
     */
    private String roomName;

}

查询优化:使用left join关联查询，而不是多次查询。前提是查询中需要使用as注明字段的名称，方可通过下面的方式进行一一匹配

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
<select id="questMeeting" resultMap="meetingFileMap">
    select xm.id, xm.name as meetingName,xu2.name as createUserName,xmr.name as meetingRoomName,
    xu.id as userId,xu.name as uName,xu.username as username,xu.phone_number as phoneNumber,xmu.status as meetingStatus,xf.uuid as xmfUuid,xf.file_name as fileName,xf.source_type as sourceType,xf.file_size as fileSize
    from xn_meeting xm
    left join xn_meeting_users xmu on xmu.meeting_id = xm.id
    left join xn_user xu on xu.id = xmu.user_id and xu.deleted = 0
    left join xn_user xu2 on xu2.id = xm.create_user_id
    left join xn_meeting_room xmr on xmr.id = xm.meeting_room_id
    left join (select * from xn_file where source_type = #{sourceType} and source_id = #{id} and deleted = 0) xf on
    xf.source_id = xm.id
    where xm.deleted = 0 and xm.id = #{id}
</select>

<resultMap id="meetingFileMap" type="com.aiidc.xn.meeting.entity.Meeting">
    <id property="id" column="id"/>
    <result property="name" column="meetingName"/>
    <collection property="userList" column="id" ofType="com.aiidc.xn.user.entity.User">
        <id property="id" column="userId"/>
        <result property="name" column="uName"/>
        <result property="meetingStatus" column="meetingStatus"/>
    </collection>
    <collection property="fileList" column="id" ofType="com.aiidc.xn.filestore.entity.FileEntity" resultMap="fileMap"/>
</resultMap>

<resultMap id="fileMap" type="com.aiidc.xn.filestore.entity.FileEntity">
    <id property="uuid" column="xmfUuid"/>
    <result property="fileName" column="fileName"/>
    <result property="fileSize" column="fileSize"/>
</resultMap>

其中：

property:子查询返回的结果集
column：子查询时所需要的查询条件
select：子查询的sql对应的id

但这种方式不适合加上分页条件，即如果查询的结果需要使用分页，则不能使用该方式。

另：将查询出的结果作为另一个查询的条件：

1
2
3
4
5
6
7
<collection property="systemId" column="{pid=id}" select="getAllSystemByUserId"/>

<select id="getAllSystemByUserId" resultType="com.aiidc.xn.user.entity.XnSystem">
    select xs.id as id,xs.name as name from xn_system_user xsu
    left join xn_system xs on xsu.system_id = xs.id
    where xsu.user_id = #{id}
</select>

2.Mybatis Plus and与or并用情况

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
Dimension one = this.getOne(Wrappers.<Dimension>lambdaQuery()
    .and(e -> e.eq(Dimension::getCreateTime, "官方还让他让他人员同意"))
    .and(e -> {e.eq(Dimension::getDimName, "名称").or().eq(Dimension::getId, "111");})
);

//        if (StringUtils.isNotBlank(keyword)) {
//            query.and(e->{
//                e.like(Case::getName, keyword)
//                        .or().like(Case::getSerialNumber, keyword)
//                        .or().like(Case::getBmsah, keyword)
//                        .or().like(Case::getApplicant, keyword)
//                        .or().like(Case::getCaseFrom, keyword)
//                        .or().like(Case::getRespondent, keyword)
//                        .or().like(Case::getUndertaker, keyword);
//            });
//        }
//        if(StringUtils.isNotBlank(currentUser)){
//            query.and(e->e.eq(Case::getCreateUser,currentUser));
//        }

3.minio文件服务器

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
@Override
public InputStream download(String bucket, String objectName) throws FileDownloadException, FileNotExistException {
    InputStream stream;
    try {
        exist(bucket, objectName);
        stream = minioClient.getObject(bucket, objectName);
        return stream;
    } catch (MinioException | NoSuchAlgorithmException | IOException | InvalidKeyException | XmlPullParserException | FileServerAccessException e) {
        throw new FileDownloadException(bucket, objectName, e);
    }
}

@Override
public boolean exist(String bucket, String objectName) throws FileServerAccessException, FileNotExistException {
    try {
        minioClient.statObject(bucket, objectName);
        return true;
    } catch (ErrorResponseException e) { //不存在是会抛出异常的
        ErrorCode errorCode = e.errorResponse().errorCode();
        if (errorCode == ErrorCode.NO_SUCH_KEY || errorCode == ErrorCode.NO_SUCH_OBJECT) {
                log.error("文件{}在存储桶{}中不存在！", objectName, bucket);
                return false;
        }
        log.error("查询文件是否存在报错：", e);
        throw new FileNotExistException(bucket, objectName, e);
    } catch (MinioException | IOException | InvalidKeyException | NoSuchAlgorithmException | XmlPullParserException e) {
        log.error("访问文件服务器出错，文件：{}，文件桶：{}，错误：{}", objectName, bucket, e);
        throw new FileServerAccessException("", e);
    }
}

   下载：从minio下载文件时，注意文件的路径，即objectName参数不是文件的全路径，而只是文件的相对路径。
eg：http://172.16.16.222:9000/mxzf/public/image/20200429/1588144931194000854/test.jpg的test.jpg图片，通过minio下载的话。objectName参数应该为：public/image/20200429/1588144931194000854/test.jpg。
   而类似http://172.16.16.222:9000/mxzf/public/image/20200429/1588144931194000854/test.jpg这种资源路径，可以直接通过：

1
2
URL image = new URL("http://172.16.16.222:9000/mxzf/public/image/20200429/1588144931194000854/test.jpg");
BufferedReader in = new BufferedReader(new InputStreamReader(image.openStream()));

这种直接获取对应的文件信息。

4.多字段排序问题

   实际场景：获取会议信息时，有根据创建时间排序，会议开始时间排序的需求，如果在sql语句层面去解决，需要进行多次判断，且不易维护。
   解决措施：在Vo实体类中建立容器：LinkedHashSet sorts。通过判断容器里面的元素，来达到拼接sql的作用。其中的Sort类为MeetPageVo 的内部类：

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
public class MeetPageVo extends PageRequest {
    @ApiModelProperty("排序规则，默认为创建时间降序排序")
    private LinkedHashSet<Sort> sorts;

    @ApiModel("排序")
    @Setter
    @Getter
    public static class Sort {
        @ApiModelProperty("排序字段：创建时间：CREATE_TIME,开始时间：START_TIME")
        private SortField field = SortField.CREATE_TIME;
        @ApiModelProperty("排序方向：升序ASC,降序DESC")
        private Direction direction = Direction.DESC;

        public Sort() {
        }

        public Sort(SortField field, Direction direction) {
            this.field = field;
            this.direction = direction;
        }

        @Override
        public boolean equals(Object o) {
            if (this == o) return true;
            if (o == null || getClass() != o.getClass()) return false;
            Sort sort = (Sort) o;
            return field == sort.field && direction == sort.direction;
        }

        @Override
        public int hashCode() {
            return Objects.hash(field, direction);
        }
    }

    public enum SortField {
        /**  创建时间 */
        CREATE_TIME,
        /** 开始时间 */
        START_TIME;
    }

    public enum Direction {
        DESC, ASC;
    }

}

然后在业务层进行设置：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
LinkedHashSet<MeetPageVo.Sort> sorts = meetPageVo.getSorts();
LinkedHashSet<MeetPageVo.Sort> querySorts = new LinkedHashSet<>();

if (CollectionUtils.isEmpty(sorts)) {//如果排序容器中没有元素，就设置默认排序方式：创建时间倒序排序
    querySorts.add(new MeetPageVo.Sort(MeetPageVo.SortField.CREATE_TIME, MeetPageVo.Direction.DESC));
} else {
    MeetPageVo.Sort querySort;
    // 遍历容器中的排序字段 以及对应的排序方式
    for (MeetPageVo.Sort sort : sorts) {
        querySort = new MeetPageVo.Sort();
        MeetPageVo.SortField field = sort.getField();
        querySort.setField(Objects.isNull(field) ? MeetPageVo.SortField.CREATE_TIME : field);

        MeetPageVo.Direction direction = sort.getDirection();
        querySort.setDirection(Objects.isNull(direction) ? MeetPageVo.Direction.DESC : direction);

        querySorts.add(querySort);
	}
}
meetQueryVo.setSorts(querySorts);

在sql层进行拼接：

1
2
3
4
order by
<foreach collection="sorts" item="sort" separator=",">
    xm.${sort.field} ${sort.direction}
</foreach>