EasyExcel 读取单元格内图片

📅 2024-02-01 15:41 👁️ 5710 💬 0

EasyExcel 读取单元格内图片

1.需求介绍

需要实现Excel数据批量导入,且Excel中某个单元格内存在图片,需要将图片解析上传,并与所在行数据进行绑定,具体数据如:

image-20240201144045786

2.具体实现

2.1 版本介绍

  1. easyexcel:3.3.2
  2. SpringBoot:2.7.1
  3. hutool:5.7.21

2.2 解决思路

参考hutool ExcelPicUtil工具类,通过调用getPicMap方法可以获得Map<String, PictureData>数据,其中key为:行_列,value为:此单元格图片对象

通过学习其源码发现存在一些问题:

  1. 当同一个单元格内存在多张照片,会出现覆盖情况,即先解析出来的图片会被后解析的覆盖,造成图片丢失的情况

  2. 采用双循环的方式解析数据,且获取的PictureData并不符合文件上传需求,若直接调用会存在两次双循环,解析速度变慢

2.3 具体实现

  1. 新增ExcelImageUtil工具类
public class ExcelImageUtil {

    /**
     * 获取工作簿指定sheet中图片列表
     *
     * @param workbook   工作簿{@link Workbook}
     * @param sheetIndex sheet的索引
     * @return 图片映射,键格式:行_列,值:{@link PictureData}
     */
    public static Map<Integer, List<Attachment>> getPicMap(Workbook workbook, int sheetIndex) {
        Assert.notNull(workbook, "Workbook must be not null !");
        if (sheetIndex < 0) {
            sheetIndex = 0;
        }
        // 由于实际业务提供的模板为XLSX,暂时不考虑XLS解析
        if (workbook instanceof HSSFWorkbook) {
            return null;
        } else if (workbook instanceof XSSFWorkbook) {
            try {
                return getRowPicMapXlsx((XSSFWorkbook) workbook, sheetIndex);
            } catch (IOException e) {
                e.printStackTrace();
            }
        } else {
            throw new IllegalArgumentException(StrUtil.format("Workbook type [{}] is not supported!", workbook.getClass()));
        }
        return Collections.emptyMap();
    }

    // -------------------------------------------------------------------------------------------------------------- Private method start

    /**
     * 获取XLS工作簿指定sheet中图片列表
     *
     * @param workbook   工作簿{@link Workbook}
     * @param sheetIndex sheet的索引
     * @return 图片映射,键格式:行_列,值:{@link PictureData}
     */
    //private static Map<Integer, List<Attachment>> getPicMapXls(HSSFWorkbook workbook, int sheetIndex) {
    //    final Map<String, List<Attachment>> picMap = new HashMap<>();
    //    final List<HSSFPictureData> pictures = workbook.getAllPictures();
    //    if (CollectionUtil.isNotEmpty(pictures)) {
    //        final HSSFSheet sheet = workbook.getSheetAt(sheetIndex);
    //        HSSFClientAnchor anchor;
    //        int pictureIndex;
    //        for (HSSFShape shape : sheet.getDrawingPatriarch().getChildren()) {
    //            if (shape instanceof HSSFPicture) {
    //                pictureIndex = ((HSSFPicture) shape).getPictureIndex() - 1;
    //                anchor = (HSSFClientAnchor) shape.getAnchor();
    //                HSSFPictureData hssfPictureData = pictures.get(pictureIndex);
    //                Attachment attachment = new Attachment();
    //                ByteArrayInputStream inputStream = new ByteArrayInputStream(hssfPictureData.getData());
    //                hssfPictureData.get
    //                        name = name.substring(name.lastIndexOf("/") + 1);
    //                InputStream inputStreamPart = packagePart.getInputStream();
    //                attachment.setFileSize(Func.toLong(inputStreamPart.available()));
    //                BladeFile bladeFile = MinioUtil.getMinioTemplate().putFile(name, inputStream);
    //                attachment.setObjName(bladeFile.getName());
    //                attachment.setDisplayName(bladeFile.getOriginalName());
    //                //获取后缀名
    //                attachment.setFileType(FileUtil.extName(bladeFile.getOriginalName()));
    //                picMap.put(StrUtil.format("{}_{}", anchor.getRow1(), anchor.getCol1()), );
    //            }
    //        }
    //    }
    //    return picMap;
    //}

    /**
     * 获取XLSX工作簿指定sheet中图片列表
     *
     * @param workbook   工作簿{@link Workbook}
     * @param sheetIndex sheet的索引
     * @return 图片映射,键格式:行_列,值:{@link PictureData}
     */
    private static Map<Integer, List<Attachment>> getRowPicMapXlsx(XSSFWorkbook workbook, int sheetIndex) throws IOException {
        final Map<Integer, List<Attachment>> sheetIndexPicMap = new HashMap<>();
        final XSSFSheet sheet = workbook.getSheetAt(sheetIndex);
        XSSFDrawing drawing;
        for (POIXMLDocumentPart dr : sheet.getRelations()) {
            if (dr instanceof XSSFDrawing) {
                drawing = (XSSFDrawing) dr;
                final List<XSSFShape> shapes = drawing.getShapes();
                XSSFPicture pic;
                CTMarker ctMarker;
                for (XSSFShape shape : shapes) {
                    if (shape instanceof XSSFPicture) {
                        pic = (XSSFPicture) shape;
                        ctMarker = pic.getPreferredSize().getFrom();
                        int row = ctMarker.getRow();
                        XSSFPictureData data = pic.getPictureData();
                        Attachment attachment = new Attachment();
                        PackagePart packagePart = data.getPackagePart();
                        String name = packagePart.getPartName().getName();
                        name = name.substring(name.lastIndexOf("/") + 1);
                        InputStream inputStreamPart = packagePart.getInputStream();
                        attachment.setFileSize(Func.toLong(inputStreamPart.available()));
                        try {
                            BladeFile bladeFile = MinioUtil.getMinioTemplate().putFile(name, inputStreamPart);
                            attachment.setObjName(bladeFile.getName());
                            attachment.setDisplayName(bladeFile.getOriginalName());
                            //获取后缀名
                            attachment.setFileType(FileUtil.extName(bladeFile.getOriginalName()));
                        } catch (Exception e) {
                            log.error("MINIO 上传文件失败," + e.getMessage());
                            continue;
                        }
                        // 通过map.computeIfAbsent 方法,如果存在key 则返回已有的List,不存在则new ArrayList
                        // 再通过add()方法,添加当前对象
                        sheetIndexPicMap.computeIfAbsent(row, k -> new ArrayList<>()).add(attachment);
                    }
                    // 其他类似于图表等忽略,see: https://gitee.com/loolly/hutool/issues/I38857
                }
            }
        }
        return sheetIndexPicMap;
    }
}
  1. 解析数据

     public boolean importInspectData(MultipartFile file) throws IOException, ParseException {
            InputStream inputStream = file.getInputStream();
            InputStream inputStream2 = file.getInputStream();
            ExcelReader excelReader = ExcelUtil.getReader(inputStream2);
            Map<Integer, List<Attachment>> attachMap = ExcelImageUtil.getPicMap(excelReader.getWorkbook(), 0);
            List<InspectionTemplateExcel> inspectionTemplateExcelList = EasyExcelUtil.read(inputStream, 0, 1, InspectionTemplateExcel.class);
            if (Func.isEmpty(inspectionTemplateExcelList)) {
                return true;
            }
            int i = 1;
            for (InspectionTemplateExcel excel : inspectionTemplateExcelList) {
                excel.setNo(i++);
            }
    
            Map<String, List<InspectionTemplateExcel>> collect = inspectionTemplateExcelList.stream().collect(Collectors.groupingBy(e -> e.getDiscoverDatetime() + "#" + e.getSegmentId()));
            for (Map.Entry<String, List<InspectionTemplateExcel>> stringListEntry : collect.entrySet()) {
                String key = stringListEntry.getKey();
                String[] split = key.split("#");
                Date date = ExcelDateUtil.parseDate(split[0]);
                List<InspectionTemplateExcel> value = stringListEntry.getValue();
                InspectionTemplateExcel templateExcel = inspectionTemplateExcelList.get(0);
                InspectionTask task = new InspectionTask();
                task.setType(Func.toInt(templateExcel.getInspectType()));
                task.setStartDatetime(date);
                task.setEndDatetime(date);
                task.setPersonId(AuthUtil.getUserName());
                task.setDeptId(SysCache.getDeptName(Func.firstLong(AuthUtil.getDeptId())));
                task.setSource(3);
                save(task);
                Long taskId = task.getId();
                Set<String> diseaseTypes = new HashSet<>();
                for (InspectionTemplateExcel excel : value) {
                    int no = excel.getNo();
                    InspectionBizRecord record = cn.hutool.core.bean.BeanUtil.copyProperties(excel, InspectionBizRecord.class, "discoverDatetime");
                    record.setSource(3);
                    record.setType(excel.getDiseaseType());
                    diseaseTypes.add(excel.getDiseaseType());
                    record.setInspectionTaskId(taskId);
                    if (attachMap.containsKey(no)) {
                        record.setImages(attachMap.get(no));
                    }
                    fillLocations(record);
                    recordService.save(record);
                }
     
            }
    
            return true;
        }
    
阅读更多

EasyExcel 一个模板填充多个sheet

📅 2023-08-03 15:15 👁️ 2002 💬 0

1.版本介绍

  1. easyexcel:3.3.2

  2. SpringBoot:2.7.1

  3. Apache POI:4.1.2

2.背景介绍

现提供一个excel模板(sheet1),导出列表记录时,每个sheet页展示一条记录,最终生成多sheet文件

3.实现代码

注意:代码伪代码

@Slf4j
public class POIUtil {


    /**
    *获取resource下模板文件的文件流
    *
    */
    public static InputStream getResourcesFileInputStream(String fileName) {
        return Thread.currentThread().getContextClassLoader().getResourceAsStream(fileName);
    }
    
    /**
     * 通过模板 生成多sheet
     *
     * @param inputStream
     * @return
     */
    @SneakyThrows
    public static InputStream createSheetFromTemplate(InputStream inputStream, int size) {
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        //原模板只有一个sheet,通过poi复制出需要的sheet个数的模板
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
        //设置模板的第一个sheet的名称
        workbook.setSheetName(0, "sheet1");
        for (int i = 1; i < size; i++) {
            //复制模板,得到第i个sheet
            int num = i + 1;
            workbook.cloneSheet(0, "sheet" + num);
        }
        //写到流里
        workbook.write(bos);
        byte[] bArray = bos.toByteArray();
        InputStream is = new ByteArrayInputStream(bArray);
        return is;
    }

}


public class Application {

     public void listExport(HttpServletResponse response) {
         // 获取导出数据
        List<MyData> myDataList = service.getData();

        InputStream inputStream = POIUtil.getResourcesFileInputStream("template.xlsx");
        // 由模板生成多sheet
        InputStream is = POIUtil.createSheetFromTemplate(inputStream, myDataList.size());
        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).withTemplate(is).build();
        for (int i = 0; i < myDataList.size(); i++) {
             int num = i + 1;
             MyData data = myDataList.get(i);
             WriteSheet writeSheet = EasyExcel.writerSheet("sheet" + num).build();
             excelWriter.fill(data, writeSheet);
         }
         excelWriter.finish()
     }
    
    
}

4.导出结果

image-20230803151409247

阅读更多

EasyExcel 使用自定义注解实现自定义下拉框选项

📅 2023-08-03 14:41 👁️ 3812 💬 0

EasyExcel 使用自定义注解实现自定义下拉框选项

1.版本介绍

  1. easyexcel:3.3.2

  2. SpringBoot:2.7.1

2.具体实现

  1. 定义自定义注解:ExcelSelected
@Documented
@Target({ElementType.FIELD}) //用此注解用在属性上
@Retention(RetentionPolicy.RUNTIME)//注解不仅被保存到class文件中,jvm加载class文件之后,仍然存在;
public @interface ExcelSelected {
    /**
     * 固定下拉内容
     */
    String[] source() default {};

    /**
     * 动态下拉内容
     */
    Class<? extends ExcelDynamicSelect>[] sourceClass() default {};

    /**
     * 设置下拉框的起始行,默认为第二行
     */
    int firstRow() default 1;

    /**
     * 设置下拉框的结束行,默认为最后一行
     */
    int lastRow() default 0x10000;
}
  1. 定义动态获取下拉框数据接口:ExcelDynamicSelect
public interface ExcelDynamicSelect {

    /**
     * 获取动态生成的下拉框可选数据
     * @return 动态生成的下拉框可选数据
     */
    String[] getSource();
}

  1. 自定义注解解析
@Data
@Slf4j
public class ExcelSelectedResolve {
    /**
     * 下拉内容
     */
    private String[] source;

    /**
     * 设置下拉框的起始行,默认为第二行
     */
    private int firstRow;

    /**
     * 设置下拉框的结束行,默认为最后一行
     */
    private int lastRow;

    public String[] resolveSelectedSource(ExcelSelected excelSelected) {
        if (excelSelected == null) {
            return null;
        }

        // 获取固定下拉框的内容
        String[] source = excelSelected.source();
        if (source.length > 0) {
            return source;
        }

        // 获取动态下拉框的内容
        Class<? extends ExcelDynamicSelect>[] classes = excelSelected.sourceClass();
        if (classes.length > 0) {
            try {
                ExcelDynamicSelect excelDynamicSelect = classes[0].newInstance();
                String[] dynamicSelectSource = excelDynamicSelect.getSource();
                if (dynamicSelectSource != null && dynamicSelectSource.length > 0) {
                    return dynamicSelectSource;
                }
            } catch (InstantiationException | IllegalAccessException e) {
                log.error("解析动态下拉框数据异常", e);
            }
        }
        return null;
    }
}

4.继承SheetWriteHandler,实现自定义的处理器

@Data
@AllArgsConstructor
public class SelectedSheetWriteHandler implements SheetWriteHandler {
    private final Map<Integer, ExcelSelectedResolve> selectedMap;

    /**
     * Called before create the sheet
     */
    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

    }

    /**
     * 说明:当下拉选项内容过多时,导出会存在问题,解决办法是新增一个”hidden“sheet页,将下拉框内容写入,然后再具体单元格引用此处的内容
     *		当下拉选项内容不多时,可使用下面的方式
     *		// 设置下拉列表的值
     *       DataValidationConstraint constraint = helper.createExplicitListConstraint(v.getSource());
     *      // 设置约束
     *       DataValidation validation = helper.createValidation(constraint, rangeList);
     */
    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        // 这里可以对cell进行任何操作
        Sheet sheet = writeSheetHolder.getSheet();
        //设置下拉框
        DataValidationHelper helper = sheet.getDataValidationHelper();
        //1.创建一个隐藏的sheet 名称为 hidden
        String hiddenName = "hidden";
        SXSSFWorkbook sw = (SXSSFWorkbook) workbook;
        XSSFSheet hiddenSheet = sw.getXSSFWorkbook().createSheet(hiddenName);
        workbook.setSheetHidden(workbook.getSheetIndex(hiddenName),true);
        selectedMap.forEach((k, v) -> {
            // 设置下拉列表的行: 首行,末行,首列,末列
            CellRangeAddressList rangeList = new CellRangeAddressList(v.getFirstRow(), v.getLastRow(), k, k);
            //获取excel列名
            String excelLine = getExcelLine(k);
            //2.循环赋值
            String[] values = v.getSource();
            generateSelectValue(hiddenSheet,k,values);

            //4.hidden!$H:$1:$H$50  sheet为hidden的 H1列开始H50行数据获取下拉数组
            String refers = hiddenName + "!$" + excelLine +
                    "$1:$" + excelLine + "$" + (values.length);
            //5 将刚才设置的sheet引用到你的下拉列表中
            DataValidationConstraint constraint = helper.createFormulaListConstraint(refers);
            DataValidation validation = helper.createValidation(constraint, rangeList);

            // 设置下拉列表的值
            //DataValidationConstraint constraint = helper.createExplicitListConstraint(v.getSource());
            // 设置约束
            //DataValidation validation = helper.createValidation(constraint, rangeList);
            // 阻止输入非下拉选项的值
            validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
            validation.setShowErrorBox(true);
            validation.setSuppressDropDownArrow(true);
            validation.createErrorBox("提示", "请输入下拉选项中的内容");
            sheet.addValidationData(validation);
        });

    }

    /**
     * @param num 列数
     * @return java.lang.String
     * @Description 返回excel列标A-Z-AA-ZZ
     * @Author chou
     * @Date 2020/9/8
     */
    public static String getExcelLine(int num) {
        String line = "";
        int first = num / 26;
        int second = num % 26;
        if (first > 0) {
            line = (char) ('A' + first - 1) + "";
        }
        line += (char) ('A' + second) + "";
        return line;
    }

    private void generateSelectValue(Sheet sheet,int col,String[] values) {
        for (int i = 0, length = values.length; i < length; i++) {
            Row row = sheet.getRow(i);
            if (row == null) {
                row = sheet.createRow(i);
            }
            row.createCell(col).setCellValue(values[i]);
        }
    }
  1. 新增EasyExcelUtil工具类

    @Slf4j
    public class EasyExcelUtil {
        /**
         * 创建即将导出的sheet页(sheet页中含有带下拉框的列)
         *
         * @param head      导出的表头信息和配置
         * @param sheetNo   sheet索引
         * @param sheetName sheet名称
         * @param <T>       泛型
         * @return sheet页
         */
        public static <T> WriteSheet writeSelectedSheet(Class<T> head, Integer sheetNo, String sheetName) {
            // 解析表头类中的下拉注解
            Map<Integer, ExcelSelectedResolve> selectedMap = resolveSelectedAnnotation(head);
    
            return EasyExcel.writerSheet(sheetNo, sheetName)
                    .head(head)
                    .registerWriteHandler(new SelectedSheetWriteHandler(selectedMap))
                    .build();
        }
    
        /**
         * 解析表头类中的下拉注解
         *
         * @param head 表头类
         * @param <T>  泛型
         * @return Map<下拉框列索引, 下拉框内容> map
         */
        private static <T> Map<Integer, ExcelSelectedResolve> resolveSelectedAnnotation(Class<T> head) {
            Map<Integer, ExcelSelectedResolve> selectedMap = new HashMap<>();
    
            // getDeclaredFields(): 返回全部声明的属性;getFields(): 返回public类型的属性
            Field[] fields = head.getDeclaredFields();
            // 忽略静态常量 如 serialVersionUID
            List<Field> fieldList = Arrays.stream(fields).filter(e -> !Modifier.isStatic(e.getModifiers())).collect(Collectors.toList());
            for (int i = 0; i < fieldList.size(); i++) {
                Field field = fieldList.get(i);
                // 解析注解信息
                ExcelSelected selected = field.getAnnotation(ExcelSelected.class);
                ExcelProperty property = field.getAnnotation(ExcelProperty.class);
                if (selected != null) {
                    ExcelSelectedResolve excelSelectedResolve = new ExcelSelectedResolve();
                    String[] source = excelSelectedResolve.resolveSelectedSource(selected);
                    if (source != null && source.length > 0) {
                        excelSelectedResolve.setSource(source);
                        excelSelectedResolve.setFirstRow(selected.firstRow());
                        excelSelectedResolve.setLastRow(selected.lastRow());
                        if (property != null && property.index() >= 0) {
                            selectedMap.put(property.index(), excelSelectedResolve);
                        } else {
                            selectedMap.put(i, excelSelectedResolve);
                        }
                    }
                }
            }
    
            return selectedMap;
        }
    
    }
    
  2. 具体使用到实体类

    @Data
    @ColumnWidth(14)
    @HeadRowHeight(20)
    @ContentRowHeight(18)
    public class RoadExcel implements Serializable {
    
        private static final long serialVersionUID = 6255824451047479409L;
    
    
        @ExcelProperty("路线编号")
        private String code;
    
        @ExcelProperty("路线全称")
        private String fullName;
    
        @ExcelProperty("路线简称")
        private String briefName;
    
        @ExcelProperty("起点桩号")
        private String startStake;
    
        @ExcelProperty("终点桩号")
        private String endStake;
    
        @ExcelProperty("起点名称")
        private String startPosName;
    
        @ExcelProperty("终点名称")
        private String endPosName;
    
        @ExcelSelected(sourceClass = TechnicalGradeConverter.class)
        private String technicalGrade;
    
        @ExcelSelected(sourceClass = AdministrativeGradeConverter.class)
        private String administrativeGrade;
    
        @ExcelSelected(sourceClass = RoadClassificationConverter.class)
        private String roadClassification;
    
        @ExcelSelected(sourceClass = RoadDirectConverter.class)
        private String direct;
    
        @ExcelProperty("上行方向")
        private String upDirect;
    
        @ExcelProperty("下行方向")
        private String downDirect;
    
    }
    
  3. 具体实现ExcelDynamicSelect接口的类 如 TechnicalGradeConverter

    public class TechnicalGradeConverter implements  ExcelDynamicSelect {
    
        /**
         * 获取动态生成的下拉框可选数据
         *
         * @return 动态生成的下拉框可选数据
         */
        @Override
        public String[] getSource() {
            List<DictBiz> dictBizList = DictBizCache.getList(DICT_CODE);
            return dictBizList.stream().sorted(Comparator.comparingInt(DictBiz::getSort)).map(DictBiz::getDictValue).toArray(String[]::new);
        }
    }
    
    

3.导出结果

image-20230803143855186

阅读更多

Java 8 Stream基础操作汇总

📅 2022-11-18 15:54 👁️ 73 💬 0

Java 8 Stream操作汇总

// User实体类    
@Data
public class User {


    /**
     * 姓名
     */
    private String name;


    /**
     * 年龄
     */
    private Integer age;


    /**
     * 性别 1-男 2-女
     */
    private Integer sex;
    
    
    /**
     * 身高
     */
    private Long height;

    /**
     * 体重
     */
    private Double weight;


    /**
     * 体重
     */
    private BigDecimal money;


}
// 获取用户列表
 public static List<User> getUserList() {

        User user1 = new User("小王", 18, 1,180L,60D, BigDecimal.valueOf(2000L));
        User user2 = new User("小红", 17, 2,170L,55D, BigDecimal.valueOf(1000L));
        User user3 = new User("小张", 19, 1,160L,50D, BigDecimal.valueOf(3000L));
        User user4 = new User("小蓝", 16, 2,150L,51D, BigDecimal.valueOf(4000L));
        return new ArrayList<>(Arrays.asList(user1, user2, user3, user4));
 }

1.分组

要求:按性别分组

List<User> userList = getUserList();
Map<Integer, List<User>> collect = userList.stream()
    .filter(e -> e.getSex() != null)
    .collect(Collectors.groupingBy(User::getSex));
     .collect(Collectors.groupingBy(User::getSex, Collectors.counting()));
/**
Result:	key=1 value=[user1,user3]
	key=2 value=[user2,user4]
**/

注意:分组字段(sex)不能为null,如果存在null会出现:element cannot be mapped to a null key错误

2.分组统计

要求:统计不同性别人员个数

 Map<Integer, Long> collect1 = userList.stream()
     .filter(e -> e.getSex() != null)
     .collect(Collectors.groupingBy(User::getSex, Collectors.counting()));
/**
Result:	key=1 value=2
		key=2 value=2
**/

// 分组后 取时间最新的一条数据
List<MemberSolutionVo> list = new ArrayList<>();

Map<String, MemberSolutionVo> collect = list.stream().collect(
                    Collectors.groupingBy(MemberSolutionVo::getUuid,
                            Collectors.collectingAndThen(Collectors.reducing((c1, c2) -> c1.getSolutionId().intValue() > c2.getSolutionId().intValue() ? c1 : c2),
                                    Optional::get)));
list = new ArrayList<>(collect.values());

3.分组求和

要求:统计不同性别人员年龄之和

Map<Integer, Integer> collect = userList.stream()
    .filter(e -> e.getSex() != null)
    .collect(Collectors.groupingBy(User::getSex, Collectors.summingInt(User::getAge)));
/**
Result:	key=1 value=37
	key=2 value=33
**/

注意:

  1. 分组求和时,所调用的方法与求和字段有关,如 年龄字段类型为Integer,对应Collectors.summingInt(User::getAge)
Integer 类型	==> Collectors.summingInt(User::getAge)
Double 类型	==>	Collectors.summingDouble(User::getWeight)
Long 类型		==> Collectors.summingLong(User::getHeight)
Bigdecimal 类型	==> Collectors.reducing(BigDecimal.ZERO, User::getMoney, BigDecimal::add)
eg.	userList.stream()
    .filter(e -> e.getSex() != null)
    .collect(Collectors.groupingBy(User::getMoney, Collectors.reducing(BigDecimal.ZERO, User::getMoney, BigDecimal::add)));

4.最大最小值

要求:查询年龄最大最小的用户

// 最大
User user = userList.stream().max(Comparator.comparingInt(User::getAge)).get();
// 最小
User user = userList.stream().min(Comparator.comparingInt(User::getAge)).get();

5.排序

要求:按年龄排序

// sort默认为升序(从小到大)
List<User> collect = userList.stream().sorted(Comparator.comparing(User::getAge)).collect(Collectors.toList());

// 降序排序
List<User> collect = userList.stream()
    .sorted(Comparator.comparing(User::getAge,Collections.reverseOrder()))
    .collect(Collectors.toList());
// 多条件排序
 List<User> collect= userList.stream().sorted(Comparator.comparing(User::getAge, Collections.reverseOrder())
                .thenComparing(User::getName, Collections.reverseOrder())
                .thenComparing(User::getHeight, Collections.reverseOrder())).collect(Collectors.toList());

6.交集

要求:两个用户列表中 姓名相同的用户

anyMathch:集合中,只有一个匹配 就返回true,否则返回false

public static List<User> getUserList1() {

    User user1 = new User("小王", 18, 1,180L,60D, BigDecimal.valueOf(2000L));
    User user2 = new User("小红", 16, 2,170L,55D, BigDecimal.valueOf(1000L));
    User user3 = new User("小张", 19, 1,160L,50D, BigDecimal.valueOf(3000L));
    User user4 = new User("小蓝", 16, 2,150L,51D, BigDecimal.valueOf(4000L));
    return new ArrayList<>(Arrays.asList(user1, user2, user3, user4));
}

public static List<User> getUserList2() {

    User user1 = new User("小王", 18, 1,180L,60D, BigDecimal.valueOf(2000L));
    User user2 = new User("小蓝", 16, 2,170L,55D, BigDecimal.valueOf(1000L));
    return new ArrayList<>(Arrays.asList(user1, user2));
}


List<User> userList1 = getUserList1();
List<User> userList2 = getUserList2();
List<User> collect1 = userList1.stream()
    .filter(u1 ->
            userList2.stream().anyMatch(u2 -> u2.getName().equals(u1.getName()))
           )
    .collect(Collectors.toList());

7.对象集合去重

根据对象中的某个字段去重

ArrayList<User> collect = 
    userList1.stream()
    .collect(
    	Collectors.collectingAndThen(
           		Collectors.toCollection(() -> new TreeSet<>(Comparator.comparing(User::getName))), ArrayList::new));

8.差集

   List<User> collect = userList1.stream().filter(u1 ->
            userList2.stream().noneMatch(u2 -> u2.getName().equals(u1.getName()))
        ).collect(Collectors.toList());

9.List转Map

     // key=id  value=name
     Map<String, String> collect= userList1.stream().collect(Collectors.toMap(User::getId,User::getName));
    
     Map<String, String> collect1= userList1.stream().collect(Collectors.toMap(user-> {
            return user.getCode() + "/" + user.getBriefName();  //(Id1+Id2)作为key
        }, v1 -> Func.toStr(v1.getId())));
阅读更多

Swagger使用Map接受参数时,页面如何显示具体参数及说明

📅 2022-10-10 15:52 👁️ 3451 💬 0

Swagger使用Map接受参数时,页面如何显示具体参数及说明

1.需求说明

项目为:SpringBoot+Knife+MyBatisPlus

后端使用Map接受参数,要求在swagger页面上显示具体的参数名称、类型及说明

2.解决方案

1.参数数量少

当Map接受参数数量少时,可以使用Swagger自带的注解 @ApiImplicitParams+@ApiImplicitParam,具体如下:

@GetMapping("/list")
@ApiImplicitParams({
    @ApiImplicitParam(name = "code", value = "字典编号", paramType = "query", dataType = "string"),
    @ApiImplicitParam(name = "dictValue", value = "字典名称", paramType = "query", dataType = "string")
})
@ApiOperationSupport(order = 2)
@ApiOperation(value = "列表", notes = "传入dict")
public R list(@ApiIgnore @RequestParam Map<String, Object> dict) {
  
}

2.参数数量大且有具体的实体类或DTO

  1. 自定义注解 @ApiGlobalModel

    package com.zjjg.dlbp.config.annotation;
    
    /**
     * @author 石智铭
     * @Date 2022-10-10
     */
    
    import java.lang.annotation.ElementType;
    import java.lang.annotation.Retention;
    import java.lang.annotation.RetentionPolicy;
    import java.lang.annotation.Target;
    
    /**
     * Swagger扩展注解
     * 用于 application/json请求
     * 并使用诸如Map或JSONObject等非具体实体类接收参数时,对参数进行进一步描述
     */
    @Target({
            ElementType.METHOD, ElementType.PARAMETER})
    @Retention(RetentionPolicy.RUNTIME)
    public @interface ApiGlobalModel {
        /**
         * 字段集合容器
         *
         * @return Global model
         */
        Class<?> component();
    
        /**
         * 分隔符
         *
         * @return separator
         */
        String separator() default ",";
    
        /**
         * 实际用到的字段
         * 可以是字符串数组,也可以是一个字符串 多个字段以分隔符隔开: "id,name"
         * 注意这里对应的是component里的属性名,但swagger显示的字段名实际是属性注解上的name
         *
         * @return value
         */
        String[] value() default {
        };
    }
    
    
    1. 编写处理注解对应的插件

      package com.zjjg.dlbp.config;
      
      import cn.hutool.core.util.IdUtil;
      import com.fasterxml.classmate.TypeResolver;
      import com.zjjg.dlbp.config.annotation.ApiGlobalModel;
      import io.swagger.annotations.ApiModelProperty;
      import javassist.*;
      import javassist.bytecode.AnnotationsAttribute;
      import javassist.bytecode.ConstPool;
      import javassist.bytecode.annotation.Annotation;
      import javassist.bytecode.annotation.StringMemberValue;
      import lombok.AllArgsConstructor;
      import org.apache.commons.lang3.StringUtils;
      import org.slf4j.Logger;
      import org.slf4j.LoggerFactory;
      import org.springblade.core.tool.utils.Func;
      import org.springframework.core.annotation.Order;
      import org.springframework.stereotype.Component;
      import springfox.documentation.schema.ModelRef;
      import springfox.documentation.spi.DocumentationType;
      import springfox.documentation.spi.service.ParameterBuilderPlugin;
      import springfox.documentation.spi.service.contexts.ParameterContext;
      
      import java.util.*;
      import java.util.stream.Collectors;
      
      /**
       * @author 石智铭
       * @Date 2022-10-10
       * 将map入参匹配到swagger文档中
       * plugin加载顺序,默认是最后加载
       */
      @Component
      @Order
      @AllArgsConstructor
      public class ApiGlobalModelBuilder implements ParameterBuilderPlugin {
          private static final Logger logger = LoggerFactory.getLogger(ApiGlobalModelBuilder.class);
      
          private final TypeResolver typeResolver;
      
          @Override
          public void apply(ParameterContext context) {
      
              try {
                  // 从方法或参数上获取指定注解的Optional
                  Optional<ApiGlobalModel> optional = context.getOperationContext().findAnnotation(ApiGlobalModel.class);
                  if (!optional.isPresent()) {
      
                      optional = context.resolvedMethodParameter().findAnnotation(ApiGlobalModel.class);
                  }
                  if (optional.isPresent()) {
                      Class originClass = context.resolvedMethodParameter().getParameterType().getErasedType();
                      String name = originClass.getSimpleName() + "Model" + IdUtil.objectId();
                      ApiGlobalModel apiAnnotation = optional.get();
                      String[] fields = apiAnnotation.value();
                      String separator = apiAnnotation.separator();
                      ClassPool pool = ClassPool.getDefault();
                      CtClass ctClass = pool.makeClass(name);
                      ctClass.setModifiers(Modifier.PUBLIC);
                      //处理 javassist.NotFoundException
                      pool.insertClassPath(new ClassClassPath(apiAnnotation.component()));
                      CtClass globalCtClass = pool.getCtClass(apiAnnotation.component().getName());
                      // 将生成的Class添加到SwaggerModels
                      context.getDocumentationContext()
                              .getAdditionalModels()
                              .add(typeResolver.resolve(createRefModel(fields,separator,globalCtClass,ctClass)));
                      // 修改Json参数的ModelRef为动态生成的class
                      context.parameterBuilder()
                              .parameterType("body")
                              .modelRef(new ModelRef(name)).name(name).description("body");
                  }
              } catch (Exception e) {
                logger.error("@ApiGlobalModel Error",e);
              }
          }
      
      
          @Override
          public boolean supports(DocumentationType delimiter) {
      
              return true;
          }
      
      
          /**
           * 根据fields中的值动态生成含有Swagger注解的javaBeen modelClass
           */
          private Class createRefModel(String[] fieldValues,String separator,CtClass origin,CtClass modelClass) throws NotFoundException, CannotCompileException, ClassNotFoundException {
              List<CtField> allField=getAllFields(origin);
              List<CtField> modelField;
              if (Func.isEmpty(fieldValues)){
                  modelField = allField;
              }else {
                  List<String> mergeField = merge(fieldValues, separator);
                  modelField = allField.stream().filter(e->mergeField.contains(e.getName())).collect(Collectors.toList());
              }
              createCtFields(modelField, modelClass);
              return modelClass.toClass();
          }
      
          public void createCtFields(List<CtField> modelField, CtClass ctClass) throws CannotCompileException, ClassNotFoundException, NotFoundException {
              for (CtField ctField : modelField) {
                  CtField field = new CtField(ClassPool.getDefault().get(ctField.getType().getName()), ctField.getName(), ctClass);
                  field.setModifiers(Modifier.PUBLIC);
                  ApiModelProperty annotation = (ApiModelProperty) ctField.getAnnotation(ApiModelProperty.class);
                  String apiModelPropertyValue = java.util.Optional.ofNullable(annotation).map(s -> s.value()).orElse("");
                  //添加model属性说明
                  if (StringUtils.isNotBlank(apiModelPropertyValue)) {
                      ConstPool constPool = ctClass.getClassFile().getConstPool();
                      AnnotationsAttribute attr = new AnnotationsAttribute(constPool, AnnotationsAttribute.visibleTag);
                      Annotation ann = new Annotation(ApiModelProperty.class.getName(), constPool);
                      ann.addMemberValue("value", new StringMemberValue(apiModelPropertyValue, constPool));
                      attr.addAnnotation(ann);
                      field.getFieldInfo().addAttribute(attr);
                  }
                  ctClass.addField(field);
              }
          }
      
          /**
           * 获取本类及其父类的字段属性 字段属性去重
           * @param clazz 当前类对象
           * @return 字段数组
           */
          public List<CtField> getAllFields(CtClass clazz) throws NotFoundException {
              List<CtField> fieldList = new ArrayList<>();
              while (clazz != null){
                  fieldList.addAll(new ArrayList<>(Arrays.asList(clazz.getDeclaredFields())));
                  clazz = clazz.getSuperclass();
              }
      
              return fieldList.stream().collect(Collectors.collectingAndThen(
                      Collectors.toCollection(() -> new TreeSet<CtField>(Comparator.comparing(CtField::getName))), ArrayList::new)
              );
          }
      
          /**
           * 字符串列表 分隔符 合并
           * A{"a","b,c","d"} => B{"a","d","b","c"}
           *
           * @param arr arr
           * @return list
           */
          private List<String> merge(String[] arr, String separator) {
              List<String> tmp = new ArrayList<>();
              Arrays.stream(arr).forEach(s -> {
                  if (s.contains(separator)) {
                      tmp.addAll(Arrays.asList(s.split(separator)));
                  } else {
                      tmp.add(s);
                  }
              });
              return tmp;
          }
      
      }
      
      
      
      1. 在具体Controller上添加注解
      	/**
      	 * 修改
      	 */
      	@PostMapping("/update")
      	@ApiOperationSupport(order = 2)
      	@ApiOperation(value = "修改结构物:DLBP-0002-002", notes = "修改结构物:DLBP-0002-002")
      	@ApiGlobalModel(component = SlopeDTO.class)
      	public R update(@RequestBody Map<String,Object> slopeDTO) {
      		return R.status(slopeService.updateSlope(slopeDTO));
      	}
      
      
      1. 存在问题
        1. 无法展示实体类中对象属性或对象集合
阅读更多

MyBatis-plus 新增时List转String 查询时String转list

📅 2022-10-10 15:28 👁️ 3674 💬 0

MyBatis-plus 新增时List转String 查询时String转list

1. 需求说明

项目为:SpringBoot+MyBatisPlus

采用实体类接受参数,有一个参数为List ,对应的数据库字段为nvachar,要求新增时将List序列化为String插入数据库中,查询时将String转成

List

2.具体操作

  1. 新增自定义的typeHandler,继承BaseTypeHandler,具体代码如下:
package com.zjjg.dlbp.config;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import org.apache.ibatis.type.BaseTypeHandler;
import org.apache.ibatis.type.JdbcType;
import org.springblade.core.tool.utils.ObjectUtil;

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

/**
 * List<Integer> ==>  string
 * @Date 2022-10-08
 */
public class ListToStringHandle extends BaseTypeHandler<List> {


    @Override
    public void setNonNullParameter(PreparedStatement preparedStatement, int i, List list, JdbcType jdbcType) throws SQLException {
        if (ObjectUtil.isNotEmpty(list)){
            preparedStatement.setString(i, JSON.toJSONString(list));
        }else {
            preparedStatement.setString(i,null);
        }

    }

    @Override
    public List getNullableResult(ResultSet resultSet, String s) throws SQLException {
        String result = resultSet.getString(s);
        return result ==null? new ArrayList<>():JSONArray.parseArray(result);
    }

    @Override
    public List getNullableResult(ResultSet resultSet, int i) throws SQLException {
        String result = resultSet.getString(i);
        return result ==null? new ArrayList<>():JSONArray.parseArray(result);
    }

    @Override
    public List getNullableResult(CallableStatement callableStatement, int i) throws SQLException {
        String result = callableStatement.getString(i);
        return result ==null? new ArrayList<>():JSONArray.parseArray(result);
    }
}

  1. 在数据库对应实体类字段上增加注解

        @TableField(jdbcType = JdbcType.VARCHAR,typeHandler = ListToStringHandle.class)
        private List<Integer> protectiveFacility;
    
        @TableField(jdbcType = JdbcType.VARCHAR,typeHandler = ListToStringHandle.class)
        private List<Integer> drainageFacility;
    
  2. 在mapper.xml中 对应字段上添加typeHandler

     <result column="drainage_facility" property="drainageFacility" typeHandler="com.zjjg.dlbp.config.ListToStringHandle"/>
     <result column="protective_facility" property="protectiveFacility" typeHandler="com.zjjg.dlbp.config.ListToStringHandle"/>
    
  3. 在application.yml文件中,增加 type-handlers-package配置,设置typeHandle所在包位置

    mybatis-plus:
    	type-handlers-package: com.app.config
    
阅读更多

Linux MySQL5.7 主从搭建

📅 2022-04-21 13:59 👁️ 236 💬 0

前提准备

  1. 两台服务器上均已安装好MySQL5.7,
  2. 主服务器MySQL需要先建好数据库
  3. 从服务器MySQL不需要建库建表,否则会报错

1.修改master服务器配置

1.1 修改my.cnf配置文件

文件路径为:/etc/my.cnf

vim /etc/my.cnf
##必须启用二进制日志,后缀为mysql-bin 即可,如 /usr/local/mysql/data/mysql-bin 
log-bin=mysql-bin 
# 服务器唯一id 默认是1
server.id=1

#不需要同步的数据库,可设置多个
binlog-ignore-db=table1
# 需要同步的数据库,可以设设置多个
bin-do-db=test

修改保存后,重启mysql,使配置生效

命令为:service mysqld restart

1.2 创建同步用的账号

[root@cico-211-93 ~]# mysql -u root -p ##登录mysql 回车输入密码
mysql> create user '用户名'@'Slave ip' identified by '密码';
mysql> grant all privileges on *.* to '用户名'@'Slave ip' identified by '密码' with grant option;
mysql>flush privileges; ##刷新配置
mysql>show master status; ##查询master状态,记住File Position字段
+------------------+----------+-------------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB      | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+-------------------+------------------+-------------------+
| mysql-bin.000005 |  3986982 |     test          |                  |                   |
+------------------+----------+-------------------+------------------+-------------------+

2.修改Slave服务器配置

[root@cico-211-153 ~]# mysql -u root -p ##登录mysql 回车输入密码
mysql> change master to  master_host='master ip',master_posr=3306,master_user='user',master_password='password',master_log_file='mysql-bin.000005',master_log_pos='3986982';
## 回车不报错时,则执行成功,否则为不成功,需要重新检查
mysql> start slave; ## 开启复制
mysql> show slave status\G ##查看主从状态

image-20220421135541368

只有: Slave_IO_Running: Yes; Slave_SQL_Running: Yes 均为YES才说明配置成功

阅读更多

docker-compose 搭建 Elasticsearch + kibana

📅 2022-04-19 13:36 👁️ 1596 💬 0

docker-compose 搭建 Elasticsearch + kibana

本文记录使用docker-compose 搭建Elasticsearch + kibana 的过程,记录必要步骤以及所经历的坑

1.版本介绍

  • jdk:1.8
  • elasticsearch:7.8.0
  • kibana:7.8.0
  • docker-compose:1.28.6
  • Linux: CentOS Linux release 7.9.2009

2.前提准备

  1. 调整用户的mmap计数,否则启动es时会出现内存不足的情况

    # 查看当前限制
    sysctl vm.max_map_count
    vm.max_map_count = 65530
    

    临时修改,不需要重启(重启后 失效)

    sysctl -w vm.max_map_count=262144
    

    永久修改,修改后需要重启生效

    vi /etc/sysctl.cof
    # 增加 如下内容
    vm.max_map_count = 262144
    
  2. 创建目录及对应文件夹,修改文件夹的权限

    在home文件夹下新建es文件夹 ,在es文件夹中新建 data 、logs、plugins文件夹 及 docke-compose.yml 文件

    image-20220419110240102

    修改data logs 文件夹的权限 (不添加权限,后续es启动时会报错 如:Error opening log file 'logs/gc.log': Permission denied)

    chmod 777 -R ./logs
    chmod 777 -R ./data
    
  3. 创建网络环境

    docker network create elk
    

3.docker-compose.yml

version: "3.2"
services:
  elasticsearch:
    image: elasticsearch:7.8.0
    container_name: elasticsearch
    restart: always
    volumes:
      - ./data:/usr/share/elasticsearch/data
      - ./logs:/usr/share/elasticsearch/logs
      - ./plugins:/usr/share/elasticsearch/plugins
    ports:
      - 9200:9200
    environment:
      # 开启内存锁定
      - bootstrap.memory_lock=true
      # 修改jvm内存
      - "ES_JAVA_OPTS=-Xms512m -Xmx512m"
     # 指定单节点启动
      - discovery.type=single-node
    ulimits:
      # 取消内存相关限制  用于开启内存锁定
     memlock:
        soft: -1
        hard: -1

  kibana:
    image: kibana:7.8.0
    container_name: kibana
    restart: always
    ports:
      - 5601:5601
    environment:
      ELASTICSEARCH_HOSTS: http://elasticsearch:9200
      I18N_LOCALE: zh-CN
  es-head:
    image: mobz/elasticsearch-head:5-alpine
    container_name: es-head
    restart: always
    ports:
      - 9100:9100
networks:
  default:
    external:
     ## 此处名称与上面创建的网络名称一致 
     name: elk  
# 以后台的方式运行容器。不会在终端上打印运行日志
docker-compoes up -d 

#停止
docke-compose down -v

4.关闭防护墙

如果Kibana出现 Unable to revive connection: https://elasticsearch:9200/这种错误,需要关闭防火墙

#查看防火墙状态
[root@centos es]# systemctl status firewalld
● firewalld.service - firewalld - dynamic firewall daemon
   Loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)
   Active: inactive (dead)
     Docs: man:firewalld(1)
#临时关闭防护墙
systemctl stop firewalld

#永久关闭
systemctl disable firewalld

5.测试访问

elasticsearch:localhost:9200

image-20220419112415070

Kibana:http://localhost:5601/

image-20220419112509355

6.安装ik分词器

在es/plugins文件夹下新建ik文件夹,下载,解压,重启即可

#下载es 对应版本ik
wget https://github.com/medcl/elasticsearch-analysis-ik/releases/download/v7.8.0/elasticsearch-analysis-ik-7.8.0.zip
# 解压
unzip elasticsearch-analysis-ik-7.8.0.zip

重启后查看es日志已经加载了ik分词器插件

image-20220419133442746

备注:虚拟机基于virtual box管理 还需要创建端口转发

image-20220419113012958

阅读更多

SpringBoot 自定义logback.xml配置

📅 2022-04-15 14:16 👁️ 514 💬 0

通用的自定义日志配置,放在resource文件夹下,文件名称改为logback-spring.xml

<?xml version="1.0" encoding="UTF-8"?>
<configuration scan="true" scanPeriod="60 seconds">

    <!-- 彩色日志依赖的渲染类 -->
    <conversionRule conversionWord="clr" converterClass="org.springframework.boot.logging.logback.ColorConverter"/>
    <conversionRule conversionWord="wex"
                    converterClass="org.springframework.boot.logging.logback.WhitespaceThrowableProxyConverter"/>
    <conversionRule conversionWord="wEx"
                    converterClass="org.springframework.boot.logging.logback.ExtendedWhitespaceThrowableProxyConverter"/>
    <!-- 彩色日志格式 -->
    <property name="CONSOLE_LOG_PATTERN"
              value="${CONSOLE_LOG_PATTERN:-%clr(%d{yyyy-MM-dd HH:mm:ss.SSS}){faint} %clr(${LOG_LEVEL_PATTERN:-%5p}) %clr(${PID:- }){magenta} %clr(---){faint} %clr([%15.15t]){faint} %clr(%-40.40logger{39}){cyan} %clr(:){faint} %m%n${LOG_EXCEPTION_CONVERSION_WORD:-%wEx}}"/>


    <!-- 控制台输出 -->
    <appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">
        <encoder class="ch.qos.logback.classic.encoder.PatternLayoutEncoder">
            <pattern>${CONSOLE_LOG_PATTERN}</pattern>
            <charset>utf8</charset>
        </encoder>
    </appender>

    <!-- 生成INFO日志文件 -->
    <appender name="INFO" class="ch.qos.logback.core.rolling.RollingFileAppender">
        <rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
            <!-- 日志文件输出的文件名 -->
            <FileNamePattern>target/log/info-%d{yyyy-MM-dd}.log</FileNamePattern>
            <!--单个文件的最大大小-->
            <maxFileSize>64MB</maxFileSize>
            <!--日志文件保留天数-->
            <maxHistory>30</maxHistory>
        </rollingPolicy>
        <encoder class="ch.qos.logback.classic.encoder.PatternLayoutEncoder">
            <pattern>%n%d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] [%X{traceId}] [%logger{50}] %n%-5level: %msg%n</pattern>
        </encoder>
        <!-- 打印日志级别 -->
        <filter class="ch.qos.logback.classic.filter.LevelFilter">
            <level>INFO</level>
            <onMatch>ACCEPT</onMatch>
            <onMismatch>DENY</onMismatch>
        </filter>
    </appender>

    <!-- 生成ERROR日志文件 -->
    <appender name="ERROR" class="ch.qos.logback.core.rolling.RollingFileAppender">
        <rollingPolicy class="ch.qos.logback.core.rolling.TimeBasedRollingPolicy">
            <!-- 日志文件输出的文件名 -->
            <FileNamePattern>target/log/error-%d{yyyy-MM-dd}.log</FileNamePattern>
            <!--单个文件的最大大小-->
            <maxFileSize>64MB</maxFileSize>
            <!--日志文件保留天数-->
            <maxHistory>30</maxHistory>
        </rollingPolicy>
        <encoder class="ch.qos.logback.classic.encoder.PatternLayoutEncoder">
            <pattern>%n%d{yyyy-MM-dd HH:mm:ss.SSS} [%thread] [%X{traceId}] [%logger{50}] %n%-5level: %msg%n</pattern>
        </encoder>
        <!-- 打印日志级别 -->
        <filter class="ch.qos.logback.classic.filter.LevelFilter">
            <level>ERROR</level>
            <onMatch>ACCEPT</onMatch>
            <onMismatch>DENY</onMismatch>
        </filter>
    </appender>



    <!-- 日志输出级别 -->
    <root level="INFO">
        <appender-ref ref="STDOUT"/>
        <appender-ref ref="INFO"/>
        <appender-ref ref="ERROR"/>
    </root>

    <logger name="net.sf.ehcache" level="INFO"/>
    <logger name="druid.sql" level="INFO"/>


</configuration>

阅读更多

Springboot+Screw 自动生成数据库文档

📅 2022-04-14 16:15 👁️ 231 💬 0

Springboot+Screw 自动生成数据库文档

1.新建Springboot项目添加依赖

<!-- 自动生成数据库文档-->
<dependency>
    <groupId>cn.smallbun.screw</groupId>
    <artifactId>screw-core</artifactId>
    <version>1.0.5</version>
</dependency>
<!-- 必须添加此依赖 否则 DataSource Bean 不能找到-->
<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jdbc</artifactId>
</dependency>

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <scope>runtime</scope>
</dependency>

2.配置数据库连接

## MYSQL
spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://127.0.0.1:3306/jsjt-dev?useUnicode=true&characterEncoding=utf-8&autoReconnect=true&failOverReadOnly=false&useSSL=false&serverTimezone=Asia/Shanghai
spring.datasource.username=root
spring.datasource.password=root

3.具体代码

package com.xmstdy.screw;

import cn.smallbun.screw.core.Configuration;
import cn.smallbun.screw.core.engine.EngineConfig;
import cn.smallbun.screw.core.engine.EngineFileType;
import cn.smallbun.screw.core.engine.EngineTemplateType;
import cn.smallbun.screw.core.execute.DocumentationExecute;
import cn.smallbun.screw.core.process.ProcessConfig;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.ApplicationArguments;
import org.springframework.boot.ApplicationRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.ApplicationContext;

import javax.sql.DataSource;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

@SpringBootApplication
public class Application implements ApplicationRunner {

    @Autowired
    ApplicationContext applicationContext;

    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }

    @Override
    public void run(ApplicationArguments args) throws Exception {

        DataSource dataSourceMysql = applicationContext.getBean(DataSource.class);

        //模板引擎配置 生成文件配置
        EngineConfig engineConfig = EngineConfig.builder()
                // 生成文件路径
                .fileOutputDir("E://")
                // 打开目录
                .openOutputDir(false)
                // 文件类型
                .fileType(EngineFileType.WORD)
                // 生成模板实现
                .produceType(EngineTemplateType.freemarker).build();

        // 生成文档配置(包含以下自定义版本号、描述等配置连接),文档名称拼接:数据库名_描述_版本.扩展名
        Configuration config = Configuration.builder()
                .title("数据库文档")
                // 版本号
                .version("1.0.0")
                // 描述
                .description("数据库设计文档")
                // 数据源
                .dataSource(dataSourceMysql)
                // 模板引擎配置
                .engineConfig(engineConfig)
                // 加载配置:想要生成的表、想要忽略的表
                .produceConfig(getProcessConfig())
                .build();
        // 执行生成
        new DocumentationExecute(config).execute();
    }

    /**
     * 配置想要生成的表+ 配置想要忽略的表
     *
     * @return 生成表配置
     */
    public static ProcessConfig getProcessConfig() {
        // 忽略表名
        List<String> ignoreTableName = Arrays.asList("");

        return ProcessConfig.builder()
                //根据名称指定表生成
                .designatedTableName(new ArrayList<>())
                //根据表前缀生成
                .designatedTablePrefix(new ArrayList<>())
                //根据表后缀生成
                .designatedTableSuffix(new ArrayList<>())
                //忽略表名
                .ignoreTableName(ignoreTableName)
                .build();
    }

}

4.文档展示

image-20220414160849616

image-20220414160940049

阅读更多
点击右上角即可分享
微信分享提示