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

阅读更多

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
    
阅读更多

SpringBoot 集成 knife4j (Swagger2)

📅 2021-12-09 17:47 👁️ 873 💬 0

SpringBoot 集成 knife4j (Swagger2)

前提 :本文 spring boot版本为 2.6.1 ,knife4j 版本为:3.0.3

1、初始化项目,导入pom依赖

<!-- https://mvnrepository.com/artifact/com.github.xiaoymin/knife4j-spring-boot-starter -->
<dependency>
    <groupId>com.github.xiaoymin</groupId>
    <artifactId>knife4j-spring-boot-starter</artifactId>
    <version>3.0.3</version>
</dependency>

2、创建Swagger配置类

@Configuration
public class Swagger2Config {



    @Bean
    public Docket createRestApi() {
        return new Docket(DocumentationType.SWAGGER_2)
                .apiInfo(this.apiInfo())
                .select()
                // 这里可以指定扫描包的路径 或者 扫描指定的注解
                .apis(RequestHandlerSelectors.withMethodAnnotation(ApiOperation.class))
                .paths(PathSelectors.any())
                .build().protocols(this.newHashSet("https","http"));
    }

    @SafeVarargs
    private final <T> Set<T> newHashSet(T... ts) {
        return ts.length > 0 ? new LinkedHashSet(Arrays.asList(ts)) : null;
    }


    private ApiInfo apiInfo() {
        return new ApiInfoBuilder()
                .title("Swagger接口文档")
                .description("文档描述")
                .contact( new Contact("xmStudy","https://www.cnblogs.com/XiaoMingStudy1/","123@qq.com"))
                .termsOfServiceUrl("")
                .version("1.0")
                .build();
    }

}

最终项目结构图如下:

image-20211209181859354

3、出现的问题

项目启动失败

image-20211209145058757

4、解决问题

原因分析:在springboot 2.6.0会提示documentationPluginsBootstrapper NullPointerException,具体位置的WebMvcPatternsRequestConditionWrapper中的condition为null。原因是在springboot2.6.0中将SpringMVC 默认路径匹配策略从AntPathMatcher 更改为PathPatternParser,导致出错,解决办法是切换会原先的AntPathMatcher

解决问题:在properties中加上spring.mvc.pathmatch.matching-strategy=ant-path-matcher

5、项目正常启动,访问一下

ip为 ip:port/doc.html, 如 http://localhost:8080/doc.html#/home

image-20211209181922447

6 参考连接

  1. Knife4j 官方文档:https://doc.xiaominfo.com/knife4j/
  2. Github地址:https://github.com/xiaoymin/swagger-bootstrap-ui/issues/396
  3. Gitee地址:https://toscode.gitee.com/xiaoym/knife4j
阅读更多

idea Mybatis mapper配置文件删除SQL语句背景色

📅 2021-09-26 15:06 👁️ 270 💬 0

原样式:

image-20210926144955488

看着很不爽

本文 idea 版本为:idea 2020.3.1,以下操作均基于此版本进行

解决办法

1.去除警告

Settings>Editor>Inspections>SQL>No data sources configured 和 SQL dialect detection

No data sources configured :没有配置数据源

SQL dialect detection:SQL方言检测

image-20210926145946787

去除警告过后还有默写语句存在背景色

image-20210926150043943

2.去除“注入语言”背景色

**Settings>Editor>Color Scheme>Code>Injected language fragement **

image-20210926150349152

最终样式:

image-20210926150421000

阅读更多

Spring data jpa 报错: java.sql.SQLSyntaxErrorExceptoion: Table ' test.hibernate_sequence' doesn`t exists

📅 2021-09-13 11:13 👁️ 188 💬 0

错误信息如图:

image-20210912124916155

错误原因:在自定义主键是没有注明自增策略

解决办法:明确主键自增策略,比如

@Entity
@Table(name = "t_order")
@Data
@AllArgsConstructor
@NoArgsConstructor
@Builder
public class Order implements Serializable {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private String customer;

    private Date orderDate;
}

扩展:Jpa GenerationType的四种类型

  • TABLE :使用一个特定的数据库表格来保存主键
  • SEQUENCE:根据底层数据库序列来生成主键,条件是数据库支持序列
  • IDENTITY:主键由数据库自动生成,数据库设置了自增主键
  • AUTO:主键由程序控制,是Jpa的默认策略,可以不写
阅读更多

Springboot项目 配置数据库连接属性后,启动项目报错

📅 2021-09-13 11:02 👁️ 1024 💬 0

Springboot项目 配置数据库连接属性后,启动项目报错,错误如下:

image-20210912121135415

错误原因分析:

1.连接信息配置错误

当使用properties为配置文件时,如图所示,上面的 spring.datasource.name 这种写法是错误的,应该是username,还有一种情况可以将spring.datasource.driver-class-name 改成 spring.datasource.driverClassName

image-20210912121147828

​ 当使用yaml作为配置文件时,除了需要注意空格的情况,当用户名和密码为数字时,需要小心

​ 如下这个配置将会报错:

spring:
  datasource:
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3306/mytest?useUnicode=true&characterEncoding=utf8&characterSetResults=utf8&useSSL=false
    username: root
    password: 000000

这里的password为000000 ,最终获取的值是0 ,所以连接时将会报错

正确配置如下:

spring:
  datasource:
    driver-class-name: com.mysql.jdbc.Driver
    url: jdbc:mysql://localhost:3306/mytest?useUnicode=true&characterEncoding=utf8&characterSetResults=utf8&useSSL=false
    username: root
    password: "000000"

2.数据库授权失败

当连接信息都配置正确的化,很有可能是数据库授权失败,所以需要进数据库对当前用户授权

  • 从控制台进入mysql

    GRANT ALL PRIVILEGES ON *.* TO '用户名'@'%' IDENTIFIED BY '你的密码' WITH GRANT OPTION;
    
  • 刷新权限表

    FLUSH PRIVILEGES;
    
阅读更多

新建SpringBoot项目报错

📅 2021-09-13 10:42 👁️ 195 💬 0

新建一个Springboot项目时,当勾选了SQL相关的依赖(如引入了jpa 或MyBatis依赖),直接启动项目时报错

image-20210912120159945

原因:没有配置数据库相关的属性,如 url driver 等

解决办法:在application.properties 中设置数据库链接的相关属性

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/mytest?useUnicode=true&characterEncoding=utf-8&autoReconnect=true&useSSL=false
spring.datasource.username=root
spring.datasource.password=root
阅读更多

MySQL报错 SQL ERROR:1064 ,SQLState:42000

📅 2021-09-10 09:36 👁️ 923 💬 0

使用mysql新增数据时报错,具体信息如图所示:

错误原因:
所建的表中 表名或字段名与数据库关键字冲突

解决办法
可以根据报错信息,查看错误的具体位置,找到数据库中对应的字段,查询是否与关键字(不分大小写)一样,如果一样需要进行修改

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