SqlBuilder 代码

需求背景

SqlBuilder：创建一个可java -jar运行的Java应用程序，实现读取Excel表格数据生成批量SQL。（用在权限Excel导入数据库表结构更佳）

技术点

• jdk17
• SpringBoot 3.3.0
• maven 3.8.8
• Hutool 5.8.28

下载资源包后需自行替换jdk、springboot、maven版本

代码实现

项目结构如下：

pom.xml文件

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>3.3.0</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.lirusen</groupId>
    <artifactId>SqlBuilder</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>SqlBuilder</name>
    <description>SqlBuilder</description>
    <url/>
    <licenses>
        <license/>
    </licenses>
    <developers>
        <developer/>
    </developers>
    <scm>
        <connection/>
        <developerConnection/>
        <tag/>
        <url/>
    </scm>
    <properties>
        <java.version>17</java.version>
    </properties>
    <dependencies>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.apache.commons/commons-lang3 -->
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.12.0</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/cn.hutool/hutool-all -->
        <dependency>
            <groupId>cn.hutool</groupId>
            <artifactId>hutool-all</artifactId>
            <version>5.8.28</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<!--        hutool-5.x的poi-ooxml 版本需高于 4.1.2-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>5.2.3</version>
        </dependency>
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
                <configuration>
                    <!-- 替换为你的主类全限定名 -->
                    <mainClass>com.lirusen.sqlbuilder.SqlBuilder</mainClass>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

SqlBuilder.class

package com.lirusen.sqlbuilder;

import cn.hutool.core.annotation.Alias;
import cn.hutool.poi.excel.ExcelReader;
import cn.hutool.poi.excel.ExcelUtil;
import lombok.Getter;
import lombok.Setter;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.nio.charset.StandardCharsets;
import java.util.List;

/**
 * SqlBuilder：2.0
 * <br>完善成自己需要的导出结构，执行package，
 * <br>将生成的jar包和Excel放置 项目根目录下，
 * <br>打开命令窗口 执行 "java -jar sql-builder-0.0.1-SNAPSHOT.jar"命令
 *
 * @author lirusen
 * @since 2024/6/20
 */
public class SqlBuilder {

    /**
     * 重置颜色
     */
    public static final String ANSI_RESET = "\u001B[0m";

    /**
     * 红色
     */
    public static final String ANSI_RED = "\u001B[31m";

    /**
     * 绿色
     */
    public static final String ANSI_GREEN = "\u001B[32m";

    public static void main(String[] args) {
        // TODO Excel文件名
        String fileName = "oCPX广告3~5月赔付明细-保留两位小数.xlsx";
        // TODO 指定sheet名称
        String sheetName = "广告主id对应的赔付金额";

        // 项目文件夹根目录
        String basePath = System.getProperty("user.dir") + "\\";
        // 上传的文件路径
        String uploadFilePath = basePath + fileName;
        // 导出SQL的文件路径
        String sqlFilePath = fileName.replace(".xlsx", ".sql");

        File file = new File(uploadFilePath);
        if (!file.exists()) {
            System.out.println(ANSI_RED + "文件【" + fileName + "】应当在" + basePath + "目录下" + ANSI_RESET);
            return;
        }

        try (ExcelReader reader = ExcelUtil.getReader(file, sheetName);
             BufferedWriter writer = new BufferedWriter(new FileWriter(sqlFilePath, StandardCharsets.UTF_8))) {

            List<ExcelData> dataList = reader.readAll(ExcelData.class);

            StringBuilder sb = handleToGenerateSql(dataList);

            writer.write(String.valueOf(sb));

            System.out.println(ANSI_GREEN + "生成SQL成功，输出位置：" + basePath + sqlFilePath + ANSI_RESET);
        } catch (Exception e) {
            throw new RuntimeException("生成SQL失败，" + e.getMessage());
        }
    }

    private static StringBuilder handleToGenerateSql(List<ExcelData> dataList) {
        // 广告主数量
        int advertiserCount = dataList.size();
        // 总赔付金额
        long totalAmountBy1000000 = 0L;

        StringBuilder sb = new StringBuilder();

        // TODO 处理数据逻辑，生成SQL，输出成.sql文件，涉及到金额建议使用BigDecimal处理
        for (ExcelData data : dataList) {
            BigDecimal singleAmount = new BigDecimal(data.getCompensationAmount());
            long singleAmountBy1000000 = singleAmount.multiply(new BigDecimal(1000000)).longValue();
            totalAmountBy1000000 += singleAmountBy1000000;
            String insertSql = String.format("insert into table_1 (advertiserId, advertiserName, compensationAmount) values ('%s', '%s', %d);\n",
                    data.getAdvertiserId(), data.getAdvertiserName(), singleAmountBy1000000);
            sb.append(insertSql);
        }
        String descStr = String.format("-- 广告主数量：%s，总赔付金额：%.2f元， %d微",
                advertiserCount, new BigDecimal(totalAmountBy1000000).divide(BigDecimal.valueOf(1000000), 2, RoundingMode.HALF_UP).doubleValue(), totalAmountBy1000000);
        sb.append(descStr);
        return sb;
    }

    @Setter
    @Getter
    // TODO 可替换成接收Excel数据的实体类
    static class ExcelData {
        /**
         * 广告主id
         */
        @Alias("广告主id")
        private String advertiserId;

        /**
         * 广告主名称
         */
        @Alias("广告主名称")
        private String advertiserName;

        /**
         * 赔付金额
         */
        @Alias("赔付金额")
        private String compensationAmount;

        public ExcelData() {
        }

    }
}

如图放置文件和打包后的jar包

小结

提示：这里可以添加总结

jar包提出来的原因： IDEA运行Java代码时是以target所在目录路径为工作目录，java -jar 执行只会以运行jar包的所在目录路径为工作目录，故为保持运行代码和执行jar所读取和生成文件的路径一致，不会提示找不到文件，将打包好的jar包从target目录里提出来放置项目的根目录下，即使项目执行了 mvn -clean命令也不会影响后续可执行的jar包。