JSON 文本文件转 SQL 脚本示例

本示例为“全国行政区划”数据解析，示例资源👉 data.zip
💡 注意：由于目前所在公司团队使用的数据较为陈旧，若阁下有提供最新行政区划数据的需求，请自行寻找相关资源，谢谢。

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.commons.io.FileUtils;
import org.springframework.core.io.ClassPathResource;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileWriter;
import java.io.IOException;

/**
 * Json文本文件转Sql语句工具
 *
 * @author Stone
 */
public class JsonToSqlUtil {

    public static void main(String[] args) throws Exception {
        long startTime = System.currentTimeMillis();
        System.out.println("----------Json文本文件转Sql开始----------");
        jsonToSql();
        System.out.println("----------Json文本文件转Sql结束，耗时：" + (System.currentTimeMillis() - startTime) + "毫秒----------");
    }

    private static void jsonToSql() throws Exception {
        ClassPathResource resource = new ClassPathResource("data.json");
        File file = resource.getFile();
        String data = FileUtils.readFileToString(file);
        //省
        JSONArray provinceArray = JSONArray.parseArray(data);

        //根据文本文件数据格式，遍历解析数据
        for (int i = 0; i < provinceArray.size(); i++) {
            JSONObject province = (JSONObject) provinceArray.get(i);
            System.out.println(province);
            String provinceIdId = IdGen.uuid();
            String provinceCode = province.getString("code");
            String provinceName = province.getString("name");
            String provinceSql = "INSERT INTO SYS_ADMINISTRATIVE_DIVISION (ID, CODE, NAME, PARENT_CODE, PARENT_NAME, DESCRIBE, IS_ENABLE)VALUES('" + provinceIdId + "','" + provinceCode + "','" + provinceName + "','','','','1'); \r\n";
            System.out.println(provinceSql);
            write(provinceSql);
            //市
            JSONArray cityArray = province.getJSONArray("children");
            if (cityArray != null && cityArray.size() > 0) {
                for (int j = 0; j < cityArray.size(); j++) {
                    JSONObject city = (JSONObject) cityArray.get(j);
                    System.out.println(city);
                    String cityId = IdGen.uuid();
                    String cityCode = city.getString("code");
                    String cityName = city.getString("name");
                    String citySql = "INSERT INTO SYS_ADMINISTRATIVE_DIVISION (ID, CODE, NAME, PARENT_CODE, PARENT_NAME, DESCRIBE, IS_ENABLE)VALUES('" + cityId + "','" + cityCode + "','" + cityName + "','" + provinceCode + "','" + provinceName + "','','1'); \r\n";
                    System.out.println(citySql);
                    write(citySql);
                    //区县
                    JSONArray districtArray = city.getJSONArray("children");
                    if (districtArray != null && districtArray.size() > 0) {
                        for (int k = 0; k < districtArray.size(); k++) {
                            JSONObject district = (JSONObject) districtArray.get(k);
                            System.out.println(district);
                            String districtId = IdGen.uuid();
                            String districtCode = district.getString("code");
                            String districtName = district.getString("name");
                            String districtSql = "INSERT INTO SYS_ADMINISTRATIVE_DIVISION (ID, CODE, NAME, PARENT_CODE, PARENT_NAME, DESCRIBE, IS_ENABLE)VALUES('" + districtId + "','" + districtCode + "','" + districtName + "','" + cityCode + "','" + cityName + "','','1'); \r\n";
                            System.out.println(districtSql);
                            write(districtSql);
                        }
                    }
                }
            }
        }
    }

    /**
     * 写入sql文件
     *
     * @param sql
     * @throws IOException
     */
    private static void write(String sql) throws IOException {
        File file;
        file = new File("D:/data.sql");
        if (!file.exists()) {
            file.createNewFile();
        }
        FileWriter fileWriter = new FileWriter(file, true);
        BufferedWriter bufferedWriter = new BufferedWriter(fileWriter);
        bufferedWriter.write(sql);
        bufferedWriter.close();
    }
}

​

英雄留步！走过路过不要错过，既然来了就恭请加入 平等 • 自由 • 奔放 的 黑客派社区，一起学习，相互分享，共同进步吧 😄
注册邀请链接：https://hacpai.com/register?r=Stone