Show me code

记录各种功能的实现方式

Java

1、工具类私有化构造方法

private StringUtils() {
    throw new IllegalStateException("Utility class");
}

2、获取前一个月的时间节点

// 输出的结果不正确
new Date(currentTimeMillis - 30 * 24 * 60 * 60 * 1000);

问题原因: 30 * 24 * 60 * 60 * 1000 的值已经超过了 int 的最大值，导致计算结果不正确。

解决方案: 只需要在 1000 加上 L 即可. new Date(currentTimeMillis - 30 * 24 * 60 * 60 * 1000L);

3、Collectors.map() 报错

问题原因: map 中的 value 不允许为空.

解决方法: 使用 map 的重载方法.

Map<String, String> map = list.stream()
                        .collect(HashMap::new,(m,v)-> m.put(v.getId(),v.getName()),HashMap::putAll);

4、根据某个字段 join 成一个字符串

String orgIds = orgList.stream().map(SysBasicOrgDto::getId)
                        .collect(Collectors.joining(","));

5、Map 用法

1、compute

compute() 方法对 hashMap 中指定 key 的值进行重新计算。如果 key 不存在，会将 key 和 value 存入 hashMap 中。

使用 compute 方法可以简化代码

if (dayMountMap.containsKey(day)) {
    MutablePair<Integer, Integer> pair = dayMountMap.get(day);
    if (isCompleted) {
        pair.setLeft(pair.getLeft() + trend.getAmount());
        pair.setRight(pair.getRight() + trend.getAmount());
    } else {
        pair.setRight(pair.getRight() + trend.getAmount());
    }
} else {
    dayMountMap.put(day, MutablePair.of(isCompleted ? trend.getAmount() : 0,
        trend.getAmount()));
}

简化后

dayMountMap.compute(day,(key,pair)->{
   if(pair == null){
       pair = MutablePair.of(0,0);
   }
    int completedIncrement = isCompleted ? trend.getAmount() : 0;
    pair.setLeft(pair.getLeft() + completedIncrement);
    pair.setRight(pair.getRight() + trend.getAmount());
    return pair;
});

6、保留指定位小数

RoundingMode.HALF_UP 四舍五入

double d = 12.3456;
BigDecimal b = new BigDecimal(d);
d = b.setScale(1, RoundingMode.HALF_UP).doubleValue();
System.out.println(d);

MyBatis-Plus

1、新增

baseMapper.insert(org);

2、删除

int delete = historyRecordMapper.deleteById(id);

3、修改

UpdateWrapper<SysOrg> updateWrapper = new UpdateWrapper<>();
updateWrapper.eq("id", orgId)
    .set("udate", new Date())
    .set("uoperator", String.valueOf(request.getAttribute(JwtUtil.JWT_USERID)))
    .set("delete_status", OrmConstant.DELETE_STATUS_DELETE);
baseMapper.update(null, updateWrapper);

4、查询

简单查询

QueryWrapper<SysOrg> existQueryWrapper = new QueryWrapper<SysOrg>()
            .eq("name", org.getName())
            .eq("parent_id", org.getParentId());
SysOrg sysOrg = baseMapper.selectOne(existQueryWrapper);

复杂查询1

SELECT * FROM user WHERE status = 1 AND (name = 'John' OR name = 'Doe') ORDER BY create_time DESC

QueryWrapper<User> queryWrapper = new QueryWrapper<>();
queryWrapper
    .eq("status", 1)  // WHERE status = 1
    .and(i -> i.eq("name", "John").or().eq("name", "Doe"))  // AND (name = 'John' OR name = 'Doe')
    .orderByDesc("create_time");  // ORDER BY create_time DESC

复杂查询2

SELECT COUNT( * ) FROM point_param WHERE ((param_type <> ? AND addr IN (?)) OR (param_type = ? AND addr = ? AND bit = ?))

QueryWrapper<PointParam> query = new QueryWrapper<>();
query.or(wrapper -> wrapper.ne("param_type", "0302")
        .in("addr", list.stream().map(PointParamVO::getAddr).collect(Collectors.toList())));
list.stream().filter(vo -> "0302".equals(vo.getParamType())).forEach(vo -> {
    query.or(wrapper -> wrapper.eq("param_type", "0302")
            .eq("addr", vo.getAddr())
            .eq("bit", vo.getBit())
    );
});

5、查询总条数

QueryWrapper<SysUser> queryWrapper = new QueryWrapper<>();
String userName = vo.getUserName();
String userCode = vo.getUserCode();
String email = vo.getEmail();
String mobile = vo.getMobile();
if (StringUtils.isNotEmpty(userName)) {
    queryWrapper.or().eq("user_name", userName);
}
if (StringUtils.isNotEmpty(userCode)) {
    queryWrapper.or().eq("user_code", userCode);
}
if (StringUtils.isNotEmpty(email)) {
    queryWrapper.or().eq("email", email);
}
if (StringUtils.isNotEmpty(mobile)) {
    queryWrapper.or().eq("mobile", mobile);
}
Integer count = this.baseMapper.selectCount(queryWrapper);

6、自动填充字段

https://baomidou.com/guides/auto-fill-field/

需要实现一个 MetaObjectHandler 接口，并将这个实现类交由 Spring 容器管理。

@Slf4j
@Component
public class MyMetaObjectHandler implements MetaObjectHandler {

    @Override
    public void insertFill(MetaObject metaObject) {
        log.info("开始插入填充...");
        this.strictInsertFill(metaObject, "createTime", LocalDateTime.class, LocalDateTime.now());
    }

    @Override
    public void updateFill(MetaObject metaObject) {
        log.info("开始更新填充...");
        this.strictUpdateFill(metaObject, "updateTime", LocalDateTime.class, LocalDateTime.now());
    }
}

需要在实体类的字段中加上 @TableField 注解，并设置 fill 属性

public class User {
    @TableField(fill = FieldFill.INSERT)
    private String createTime;

    @TableField(fill = FieldFill.UPDATE)
    private String updateTime;

    // 其他字段...
}

7、分页

EasyExcel

1、导出 Excel

前台发送 HTTP 请求

curl --location 'http://localhost:8888/api/historyRecord/export-history-record' `
--header 'Access-Token: eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJyb2xlIjoiMSIsImxvY2tUaW1lIjo1OTk5NDAsInRva2VuVHlwZSI6IjAiLCJ1c2VyTmFtZSI6ImFkbWluIiwiZXhwIjoxNzE3NjY1MzczLCJ1c2VySUQiOiJhZG1pbiIsImlhdCI6MTcxNzA2MDU3M30.YOJVnPi9JuUuKbj1t-wBT1fXGiEYxTRMJlWUFb02_0c' `
--header 'Content-Type: application/json' `
--data '{
    "pointId":"2593",
    "startTime": "2024-05-30 17:42:00",
    "endTime": "2024-05-30 17:43:00",
    "headList": [
        "值",
        "时间"
    ],
    "headKeyList": [
        "dataValue",
        "occrentDate"
    ]
}'

Controller 接收请求

@ApiOperation(value = "导出历史数据")
@PostMapping(value = "/exportXxxx")
public void exportXxxx(@RequestBody XxxxVo xxxVo, HttpServletResponse resp)throws Exception {
    xxxxService.exportXxxx(xxxVo , resp) ;
}

Service 实现导出的功能

@Override
public void exportHistoryData(HistoryRecordQueryExportVo queryExportVo, HttpServletResponse response) throws Exception {
    // head列表
    List<List<String>> excelHead = new ArrayList<>();
    List<String> headList = queryExportVo.getHeadList();
    headList.forEach(headStr -> {
        List<String> temp = new ArrayList<>();
        temp.add(headStr);
        excelHead.add(temp);
    });

    String filename = "历史数据导出-";
    DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
    String startDateStr = queryExportVo.getStartTime().format(dateTimeFormatter);
    String endDateStr = queryExportVo.getEndTime().format(dateTimeFormatter);
    if (startDateStr.equals(endDateStr)) {
        filename += startDateStr;
    } else {
        filename += startDateStr + "-" + endDateStr;
    }
    // 设置文本内省
    response.setContentType("application/octet-stream");
    // 设置字符编码
    response.setCharacterEncoding("utf-8");
    // 设置响应头
    response.setHeader("Content-disposition", "attachment;filename=" + URLEncoder.encode(filename, "utf-8") + ".xlsx");

    ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), HistoryRecordDTO.class)
            // 字段列表
            .includeColumnFiledNames(queryExportVo.getHeadKeyList())
            .build();
    WriteSheet writeSheet = EasyExcel.writerSheet(0, "sheet0").registerWriteHandler(new HistoryRecordExcelStyleHandler()).build();
    writeSheet.setHead(excelHead);
    HistoryRecordQuery query = new HistoryRecordQuery();
    query.setStartTime(queryExportVo.getStartTime());
    query.setEndTime(queryExportVo.getEndTime());

    ListParam listParam = new ListParam();
    listParam.setPointId(queryExportVo.getPointId());
    listParam.setStartTime(queryExportVo.getStartTime());
    listParam.setEndTime(queryExportVo.getEndTime());

    List<HistoryRecordDTO> list = baseMapper.selectListByDate(listParam);
    excelWriter.write(list, writeSheet);

    excelWriter.finish();
}

EasyExcel 样式处理器

public class HistoryRecordExcelStyleHandler extends AbstractCellStyleStrategy {

    protected CellStyle headCellStyle;

    protected WriteCellStyle headWriteCellStyle;

    protected WriteCellStyle contentWriteCellStyle;

    protected CellStyle contentCellStyle;

    protected Sheet sheet;

    /**
     * 记录此字符是否出现，如果第一次出现，则设置宽度，否则不设置，避免合并列时太宽
     */
    private Set<String> isFirst = new HashSet<>();

    /**
     * 默认高度
     */
    protected static final int DEFAULT_HEIGHT = 300;

    private static final int MAX_WIDTH = 255;

    @Override
    protected void initCellStyle(Workbook workbook) {
        headWriteCellStyle = new WriteCellStyle();
        WriteFont headWriteFont = new WriteFont();
        headWriteCellStyle.setWriteFont(headWriteFont);
        headCellStyle = StyleUtil.buildHeadCellStyle(workbook, headWriteCellStyle);

        contentWriteCellStyle = new WriteCellStyle();
        contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
        contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
        contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
        contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
        contentWriteCellStyle.setWrapped(true);
        contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        contentCellStyle = StyleUtil.buildContentCellStyle(workbook, contentWriteCellStyle);
        contentCellStyle.setAlignment(HorizontalAlignment.CENTER);
        contentCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

        sheet = workbook.getSheet("sheet0");
    }

    @Override
    protected void setHeadCellStyle(Cell cell, Head head, Integer relativeRowIndex) {
        cell.setCellStyle(headCellStyle);
        cell.getRow().setHeight((short) (2 * DEFAULT_HEIGHT));
        int columnWidth = cell.getStringCellValue().getBytes().length;
        if (columnWidth > MAX_WIDTH) {
            columnWidth = MAX_WIDTH;
        }
        if (!isFirst.contains(cell.getStringCellValue())) {
            //统一加3的宽度，显得不紧凑
            sheet.setColumnWidth(cell.getColumnIndex(), (columnWidth + 6) * 256);
            isFirst.add(cell.getStringCellValue());
        }

        if ("时间".equals(cell.getStringCellValue())) {
            sheet.setColumnWidth(cell.getColumnIndex(), (columnWidth + 25) * 256);
        }
    }

    @Override
    protected void setContentCellStyle(Cell cell, Head head, Integer relativeRowIndex) {
        cell.setCellStyle(contentCellStyle);
        cell.getRow().setHeight((short) (2 * DEFAULT_HEIGHT));
    }

    @Override
    public void afterCellCreate(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Cell cell,
                                Head head, Integer relativeRowIndex, Boolean isHead) {
        // 非 head 时，给 cell 设置样式
        if (!isHead) {
            setContentCellStyle(cell, head, relativeRowIndex);
        }
    }
}

MyBatis Plus 查询数据

数据库为 Sqlite3

<select id="selectListByDate" resultMap="HistoryRecordMap">
    SELECT
        id , point_id , data_value ,
        strftime( '%Y-%m-%d %H:%M:%S ' ,occrent_date) as occrent_date
    FROM
        history_record
    WHERE
        point_id = #{listParam.pointId}
     and occrent_date between #{listParam.startTime} and #{listParam.endTime}

    order by  occrent_date desc
</select>

实体类

数据库表字段

@Data
@ApiModel
public class HistoryRecordDTO  {

    private Integer id;

    private Integer pointId;

    private BigDecimal dataValue;

    @DateTimeFormat(pattern = DATE_TIME_WITH_LINE)
    @JsonFormat(pattern = DATE_TIME_WITH_LINE, timezone = "GMT+8")
    private String occrentDate;
}

查询参数

@Data
@ApiModel
public class ListParam {

    @ApiModelProperty("测点id")
    private String pointId;

    @ApiModelProperty("开始时间")
    @DateTimeFormat(pattern = DATE_TIME_WITH_LINE)
    @JsonFormat(pattern = DATE_TIME_WITH_LINE, timezone = "GMT+8")
    private LocalDateTime startTime;

    @ApiModelProperty("结束时间")
    @DateTimeFormat(pattern = DATE_TIME_WITH_LINE)
    @JsonFormat(pattern = DATE_TIME_WITH_LINE, timezone = "GMT+8")
    private LocalDateTime endTime;
}

在导出时，只导出部分字段

使用 includeColumnFiledNames 方法，将需要导出的字段的名称传入。

 ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), HistoryRecordDTO.class)
                // 字段列表
                .includeColumnFiledNames(queryExportVo.getHeadKeyList())
                .build();

设置表头样式

实现 AbstractCellStyleStrategy 类

WriteSheet writeSheet = EasyExcel.writerSheet(0, "sheet0")
        // 自定义样式
        .registerWriteHandler(new HistoryRecordExcelStyleHandler())
        .build();

2、导入 Excel

实现 ReadListener 接口

@Slf4j
@Component
@Scope(ConfigurableBeanFactory.SCOPE_PROTOTYPE)
public class SysUserExcelReadListener implements ReadListener<SysUserExcelData> {

    @Autowired
    private SysUserServiceImpl sysUserService;

    @Autowired
    private ISysRoleService roleService;

    /**
     * 数据
     */
    @Getter
    private List<SysUserExcelData> datas = new ArrayList<>();

    @Override
    public void onException(Exception exception, AnalysisContext context) throws Exception {
        throw exception;
    }

    /**
     * 每一行数据解析都会来调用
     * @param data
     *            one row value. Is is same as {@link AnalysisContext#readRowHolder()}
     * @param context
     *            analysis context
     */
    @Override
    public void invoke(SysUserExcelData data, AnalysisContext context) {

        validateUserCode(data);

        String roleName = data.getMainRole();
        String roleId = roleService.getRoleIdByName(roleName);
        if (StringUtils.isEmpty(roleId)) {
            throw new InheExceptionBase(500, "角色" + roleName + "不存在!");
        }
        data.setMainRole(roleId);
        datas.add(data);
    }

    /**
     * 读取完所有数据后的操作
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        List<InsertSysUserVo> list = SysUserConvert.INSTANCE.toInsVoList(datas);
        sysUserService.batchInsertOrUpdate(list);
    }

    @Override
    public boolean hasNext(AnalysisContext context) {
        return true;
    }

    @Override
    public void invokeHead(Map headMap, AnalysisContext context) {

    }

    /**
     * 校验用户信息
     * @param data 用户信息
     */
    private void validateUserCode(SysUserExcelData data) {
        String userName = data.getUserName();
        if (StringUtils.isEmpty(userName)) {
            throw new InheExceptionBase(-10000, "用户名称不允许为空！");
        }
        String userCode = data.getUserCode();
        if (StringUtils.isEmpty(userCode)) {
            throw new InheExceptionBase(-10000, "用户编码不允许为空！");
        }
        String mainRole = data.getMainRole();
        if (StringUtils.isEmpty(mainRole)) {
            throw new InheExceptionBase(-10000, "主角色不允许为空！");
        }
        String department = data.getDepartment();
        if (StringUtils.isEmpty(department)) {
            throw new InheExceptionBase(-10000, "部门不允许为空！");
        }
        String position = data.getPosition();
        if (StringUtils.isEmpty(position)) {
            throw new InheExceptionBase(-10000, "职位不允许为空！");
        }
        String mobile = data.getMobile();
        if (StringUtils.isEmpty(mobile)) {
            throw new InheExceptionBase(-10000, "手机号不允许为空！");
        }
        String email = data.getEmail();
        if (StringUtils.isEmpty(email)) {
            throw new InheExceptionBase(-10000, "邮箱不允许为空！");
        }
    }

}

导入的方法

@Service
public class ExcelSysUserServiceImpl implements IExcelSysUserService {

    @Autowired
    private ApplicationContext context;

    public SysUserExcelReadListener getSysUserExcelReadListener() {
        return context.getBean(SysUserExcelReadListener.class);
    }

    @Override
    public R importSysUserExcel(MultipartFile multipartFile) throws IOException {
        SysUserExcelReadListener readListener = getSysUserExcelReadListener();

        EasyExcelFactory.read(multipartFile.getInputStream(), SysUserExcelData.class, readListener)
            .sheet(0) //指定读取第一个工作表
            .headRowNumber(1)  //指定标题行在第一行
            .doRead();

        return R.ok(true,"导入成功！");
    }
}

每行数据对象

@Data
public class SysUserExcelData {

    @ExcelProperty(value = "User Code", index = 0)
    private String userCode;

    @ExcelProperty(value = "User Name", index = 1)
    private String userName;

    @ExcelProperty(value = "Main Role", index = 2)
    private String mainRole;

    @ExcelProperty(value = "Position", index = 3)
    private String position;

    @ExcelProperty(value = "E-mail", index = 4)
    private String email;

    @ExcelProperty(value = "Phone Number", index = 5)
    private String mobile;

    @ExcelProperty(value = "Department", index = 6)
    private String department;

    @ExcelProperty(value = "Description", index = 7)
    private String description;
}

Spring

1、获取 application.yml 中的配置的参数

通过 @Value("aaa.bbb")