百万级Josn文件去重后高效插入oracle

• 示例数据

• {"OrderID":"01","OrderNo":"A001001"}
  {"OrderID":"02","OrderNo":"A001002"}
  {"OrderID":"03","OrderNo":"A001003"}
  {"OrderID":"02","OrderNo":"A001002"}
  {"OrderID":"04","OrderNo":"A001004"}
  .....

   

• 实体类

• public class Order{
  
      private String orderId;
      private String orderNo;
  
  
      public String getOrderId() {
          return orderId;
      }
  
      public void setOrderId(String orderId) {
          this.orderId = orderId;
      }
  
      public String getOrderNo() {
          return orderNo;
      }
  
      public void setOrderNo(String orderNo) {
          this.orderNo = orderNo;
      }
  
      @Override
      public String toString() {
          return "Order{" +
                  "orderId='" + orderId + '\'' +
                  ", orderNo='" + orderNo + '\'' +
                  '}';
      }
  }

   

• 测试类

  /**
   * Copyright (C), 2015-2019,
   * FileName: GsonTest
   * Author:   admin
   * Date:     2019-09-03 17:35
   * Description:
   * History:
   * <author>          <time>          <version>          <desc>
   * 作者姓名           修改时间           版本号              描述
   */
  
  import com.alibaba.fastjson.JSON;
  import javax.swing.*;
  import java.io.BufferedReader;
  import java.io.FileInputStream;
  import java.io.InputStreamReader;
  import java.sql.*;
  import java.util.HashMap;
  import java.util.Map;
  
  /**
   * 〈关闭表日志写入〉
  
   * 重复的写入一个表；不重复的写入另一个表
   * @author admin
   * @create 2019-09-03
   * @since 1.0.0
   */
  public class GsonJdbcTest2 extends JFrame {
  
  
      public final String url = "jdbc:oracle:thin:@XXX.xxx.xxx.xxx:orcl";
      public final String username = "m_test";
      public final String password = "oracle";
      public final String classdriver = "oracle.jdbc.OracleDriver";
  
      public Connection connlog;
      public Statement stmtlog;
  
      public Connection connRept;
      public Connection conn;
      public PreparedStatement  stmtRept;
      public PreparedStatement  stmt;
  
      public GsonJdbcTest2(){
          try {
              Class.forName(classdriver);
          } catch (ClassNotFoundException e) {
              // TODO Auto-generated catch block
              e.printStackTrace();
          }
      }
  
      public static void main(String[] args) {
          System.out.println("开始执行:");
          GsonJdbcTest2 t = new GsonJdbcTest2();
          t.Tablenologging();
          t.josnToData("E:\\IDEASOURCE\\text\\data1000000.json");
          t.Tablelogging();
      }
  
      public void close() {
          try {
              if (stmt != null) stmt.close();
              if (conn != null) conn.close();
          } catch (SQLException e) {
              e.printStackTrace();
          }
      }
  
      public void closeRept() {
          try {
              if (stmtRept != null) stmtRept.close();
              if (connRept != null) connRept.close();
          } catch (SQLException e) {
              e.printStackTrace();
          }
      }
  
  
      public long josnToData(String localFile) {
  
          long start=System.currentTimeMillis();
  
          try {
              connRept = DriverManager.getConnection(url, username, password);
              connRept.setAutoCommit(false);
  
              conn = DriverManager.getConnection(url, username, password);
              conn.setAutoCommit(false);
  
              stmtRept = connRept.prepareStatement("INSERT /*+ append */ INTO TAB_ORDERTEST_REPT(orderId,orderNo) VALUES (?,?)");
              stmt = conn.prepareStatement("INSERT /*+ append */ INTO TAB_ORDERTEST(orderId,orderNo) VALUES (?,?)");
  
              Map<String, Order> map = new HashMap<>();
              Gson gson = new Gson();
              String lineTxt = null;
              InputStreamReader read = new InputStreamReader(new FileInputStream(localFile), "UTF-8");
              BufferedReader bufferedReader = new BufferedReader(read);
              Integer i =0;
              Integer j =0;
              while ((lineTxt = bufferedReader.readLine()) != null) {
                  Order order = JSON.parseObject(lineTxt, Order.class);
                  if (map.containsKey(order.getOrderId())) {
                      //获取重复的
                      j = j+1;
                      stmtRept.setString(1,order.getOrderId());
                      stmtRept.setString(2,order.getOrderNo());
                      stmtRept.addBatch();
                  } else {
                      i = i+1;
                      stmt.setString(1,order.getOrderId());
                      stmt.setString(2,order.getOrderNo());
                      stmt.addBatch();
                      map.put(order.getOrderId(), order);
                  }
                  if (j % 10000 == 0 && j != 0) {
                      stmtRept.executeBatch();
  //                    stmtRept.clearBatch();
                      connRept.commit();
                  }
                  if (i % 10000 == 0 && i != 0) {
                      stmt.executeBatch();
  //                    stmt.clearBatch();
                      conn.commit();                   
                  }
              }
              stmtRept.executeBatch();
              //    stmtRept.clearBatch();
               connRept.commit();
  
              stmt.executeBatch();
               //   stmt.clearBatch();
              conn.commit();
  
              closeRept();
              close();
  
          } catch (Exception e) {
              e.printStackTrace();
          }
          long end=System.currentTimeMillis();
          System.out.println("执行时长:" + Long.toString(end-start));
          return end-start;
      }
  
      public void Tablenologging() {
          try {
  
              connlog = DriverManager.getConnection(url, username, password);
              connlog.setAutoCommit(false);
              stmtlog = connlog.createStatement();
  
              String sqllog = "alter table tab_ordertest nologging";
              String sqllog2 = "alter table tab_ordertest_rept nologging";
  
              stmtlog.addBatch(sqllog);
              stmtlog.addBatch(sqllog2);
              stmtlog.executeBatch();
              connlog.commit();
  
              if (stmtlog != null) stmtlog.close();
              if (connlog != null) connlog.close();
  
  
          }catch (Exception e) {
              e.printStackTrace();
          }
  
  
      }
      public void Tablelogging() {
          try {
  
              connlog = DriverManager.getConnection(url, username, password);
              connlog.setAutoCommit(false);
              stmtlog = connlog.createStatement();
  
              String sqllog3 = "alter table tab_ordertest logging";
              String sqllog4 = "alter table tab_ordertest_rept logging";
  
              stmtlog.addBatch(sqllog3);
              stmtlog.addBatch(sqllog4);
              stmtlog.executeBatch();
              connlog.commit();
  
              if (stmtlog != null) stmtlog.close();
              if (connlog != null) connlog.close();
  
  
          }catch (Exception e) {
              e.printStackTrace();
          }
  
  
      }
  
  
  
  }