读取文件内容生成批量插入语句insert sql java demo
原创 running 发表于:2022-07-28 10:29:25
  阅读 :39   收藏   编辑

准备data.txt,数据以tab隔开

1	shanghai
2	beijing
3	dali
4	xizang
5	yunan
6	henan
7	suzhou
8	zhengzhou
9	fuzhou

java 按行读取生成插入语句

public static void main(String[] args) throws Exception {
    try {
      File file = new File("./data.txt");
      File fileOut = new File("./dataOut.txt");
      InputStreamReader input = new InputStreamReader(new FileInputStream(file));
      BufferedReader bf = new BufferedReader(input);
      BufferedWriter writer = new BufferedWriter(new FileWriter(fileOut));
      String rowData;
      int totalRows = 0;
      int batchIndex = 1;
      int split = 5;
      StringBuffer buffer = new StringBuffer();
      while ((rowData = bf.readLine()) != null) {
        if (rowData.trim().length() == 0 ){
          break;
        }
        String[] rows = rowData.split("\t");
        String id = String.valueOf(rows[0]);
        String city = String.valueOf(rows[1]);
        if (totalRows % split == 0) {
          buffer.append("insert into city_table(id, city) values ");
          batchIndex = 1;
        }
        buffer.append(String.format("(%s,'%s')", id, city));
        if (totalRows % split == 0) {
          buffer.append(",");
        } else {
          batchIndex++;
          if (batchIndex == split) {
            buffer.append(";\n\n");
          } else {
            buffer.append(",");
          }
        }
        totalRows++;
      }
      writer.write(buffer.toString());
      writer.flush();
      bf.close();
      input.close();
      System.out.println(String.format("totalRows: %s", totalRows));
    } catch (IOException e) {
      e.printStackTrace();
    }

生成的文件如下,注意最后一行可能需要手动处理下

insert into city_table(id, city) values (1,'shanghai'),(2,'beijing'),(3,'dali'),(4,'xizang'),(5,'yunan');

insert into city_table(id, city) values (6,'henan'),(7,'suzhou'),(8,'zhengzhou'),(9,'fuzhou'),