提升工作效率工具：SQL批量生成工具思想

提升工作效率：将excel文件数据转为sql,批量生成sql执行脚本

1.适用于此类读取excel数据并加工成sql文件的场景【简单场景易推荐使用工具本身快捷键如idea notepad，vscode  sublime工具自带列选】

easyexcel参考：读Excel | Easy Excel

2.maven 引用

    com.alibaba
    easyexcel
    3.1.1

    org.projectlombok
    lombok
    1.18.12
    provided

话不多说看代码

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;
import lombok.extern.slf4j.Slf4j;

import java.io.File;
import java.io.FileWriter;
import java.util.List;


@Data
@Slf4j
public class User {

    @ExcelProperty(value = "用户名")
    private String name;
    @ExcelProperty(value = "年龄")
    private int age;
    @ExcelProperty(value = "地址")
    private String address;

    public static void main(String[] args) throws Exception {
        List list = EasyExcel.read("C:\Users\haoha\Desktop\user.xls", User.class, null).doReadAllSync();
        System.out.println(list);
        StringBuilder sb = new StringBuilder();
        for (User user : list) {
            sb.append("insert into user(user_name,age,address)value");
            sb.append("(");
            sb.append("'").append(replaceWord(user.getName())).append("',");
            sb.append("'").append(user.getAge()).append("',");
            sb.append("'").append(replaceWord(user.getAddress())).append("',");
            sb.append(");");
            sb.append("rn");
        }
        try (FileWriter writer = new FileWriter(new File("D:\develop\xk\common-module\src\main\java\com\bean\user.sql"));) {
            writer.write(sb.toString());
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        }
    }

    public static String replaceWord(String word) {
        if (word == null) {
            return word;
        }
        return word.replace("'", "''");
    }
}

效果

注意:需要将部分特殊的符号替换，具体看使用的场景