java下载csv文件并导入mysql

下载zip文件压缩包

  public void downloadFile() {
        try {
            URL httpUrl = new URL("http://localhost:8080/myFiles/a/download");
            InputStream inputStream = httpUrl.openStream();

            byte[] bytes = new byte[1024];
            int len = 0;
            // 保存的目录
            String fileNameAnPath = "/home/download/file.zip";
            File f = new File(fileNameAnPath);
            if (f.exists()) {
                f.delete();
            }
            // 当已存在file时,如果第二个参数为true,选在向该文件末尾追加
            // FileOutputStream fileOutputStream = new FileOutputStream(f,true);
            FileOutputStream fileOutputStream = new FileOutputStream(f);
            while ((len = inputStream.read(bytes)) != -1) {
                fileOutputStream.write(bytes, 0, len);
            }

        } catch (Exception e) {
            e.printStackTrace();
            System.out.println(e);
        }
    }

解压zip文件

解压工具类

/**
 * 解压zip
 */
public class UnzipUtils {
    /**
     * 递归删除目录及文件
     */
    public static void deleteDir(File file) {
        // 结束递归循环
        if (!file.exists()) {
            return;
        }
        // 如果是目录，请进入内部并递归调用
        if (file.isDirectory()) {
            for (File f : file.listFiles()) {
                // 调用递归
                deleteDir(f);
            }
        }
        // 调用delete删除文件和空目录
        file.delete();
    }

    /**
     * 解压zip压缩文件到指定目录
     *
     * @param zipPath zip压缩文件绝对路径
     * @param descDir 指定的解压目录
     */
    public static void unzipFile(String zipPath, String descDir) throws IOException {
        try {
            File zipFile = new File(zipPath);
            if (!zipFile.exists()) {
                throw new IOException("要解压的压缩文件不存在");
            }
            File pathFile = new File(descDir);
            if (!pathFile.exists()) {
                pathFile.mkdirs();
            } else {
                pathFile.delete();
                pathFile.mkdirs();
            }
            InputStream input = new FileInputStream(zipPath);
            unzipWithStream(input, descDir);
        } catch (Exception e) {
            throw new IOException(e);
        }
    }

    /**
     * 解压
     *
     * @param inputStream
     * @param descDir
     */
    public static void unzipWithStream(InputStream inputStream, String descDir) {
        if (!descDir.endsWith(File.separator)) {
            descDir = descDir + File.separator;
        }
        try (ZipInputStream zipInputStream = new ZipInputStream(inputStream, Charset.forName("UTF-8"))) {
            ZipEntry zipEntry;
            while ((zipEntry = zipInputStream.getNextEntry()) != null) {
                String zipEntryNameStr = zipEntry.getName();
                String zipEntryName = zipEntryNameStr;
                if (zipEntryNameStr.contains("/")) {
                    String str1 = zipEntryNameStr.substring(0, zipEntryNameStr.indexOf("/"));
                    zipEntryName = zipEntryNameStr.substring(str1.length() + 1);
                }
                String outPath = (descDir + zipEntryName).replace("\\\\", "/");
                File outFile = new File(outPath.substring(0, outPath.lastIndexOf('/')));
                if (!outFile.exists()) {
                    outFile.mkdirs();
                }
                if (new File(outPath).isDirectory()) {
                    continue;
                }
                writeFile(outPath, zipInputStream);
                zipInputStream.closeEntry();
            }
            System.out.println("======解压成功=======");
        } catch (IOException e) {
            System.out.println("压缩包处理异常，异常信息{}" + e);
        }
    }

    /**
     * 将流写到文件中
     */
    public static void writeFile(String filePath, ZipInputStream zipInputStream) {
        try (OutputStream outputStream = new FileOutputStream(filePath)) {
            byte[] bytes = new byte[4096];
            int len;
            while ((len = zipInputStream.read(bytes)) != -1) {
                outputStream.write(bytes, 0, len);
            }
        } catch (IOException ex) {
            System.out.println("解压文件时，写出到文件出错");
        }
    }
}

解析csv文件

mybatis-plus

// mapper 添加 Map 数据
Integer insertMapData(@Param("dataMap") Map<String, Object> dataMap);

// xml
<insert id="insertMapData" parameterType="java.util.Map">
    <!-- 注意修改表名 -->
    insert into t_test(
    <foreach collection="dataMap" item="value" index="key" separator=",">
        ${key}
    </foreach>
    )
    values (
    <foreach collection="dataMap" item="value" index="key" separator=",">
        #{value}
    </foreach>
    )
</insert>

service

/**
 * 解析csv文件（手动）
 */
  public List<Map<String, Object>> getCsvMaps(File file) {
        List<Map<String, Object>> list = new ArrayList<>(0);
        InputStreamReader in = null;
        String s = null;
        try {
            in = new InputStreamReader(new FileInputStream(file), "utf-8");
            BufferedReader bufferedReader = new BufferedReader(in);
            String line = null;
            List<String> header = new ArrayList<>(0);
            int flag = 0;
            while ((line = bufferedReader.readLine()) != null) {
                // csv文件的分隔符，这里是以 || 分隔
                String[] split = line.split("\\|\\|");
                // 添加列名
                if (flag == 0) {
                    for (String s1 : split) {
                        header.add(s1);
                    }
                    flag++;
                    continue;
                }
                // 添加列名与数据对应关系
                Map<String, Object> map = new HashMap<>();
                for (int i = 0; i < header.size(); i++) {
                    map.put(header.get(i), "".equals(split[i]) ? null : split[i]);
                }
                list.add(map);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return list;
    }

/**
 * 保存文件数据
 */
public void doSaveFile(){
  // 下载文件
  this.downloadFile();
  // 解压文件
  UnzipUtils.unzipFile("/home/download/file.zip", "/home/download/file");
  File file = new File("/home/download/file/test.csv");
  if (file.exists()) {
      // 解析csv文件
      List<Map<String, Object>> csvMaps = this.getCsvMaps(file);
      for (Map<String, Object> csvMap : csvMaps) {
          // 添加csv文件数据到数据库
          this.baseMapper.insertMapData(csvMap);
      }
  }
}