定制软件【数据库】实现批量快速插入大量数据的六种方案

一、(MySQL)通过函数/

1、链接

https://www.jb51.net/article/207999.htm

https://blog.csdn.net/FloraCHY/article/details/117792903

2、代码

  1. -- 定制软件进入数据库
  2. use test;
  3. -- 定制软件显示所有表
  4. show tables;
  5. -- 创建majors表
  6. create table majors(id int, major varchar(255));
  7. -- 定制软件定义结束符$
  8. delimiter "$";
  9. -- 定制软件创建存储过程,定制软件定义存储方法
  10. create procedure batchInsert(in args int)
  11. begin
  12. declare i int default 1;
  13. -- 开启事务(重要!不开的话,100w定制软件数据需要论天算)
  14. start transaction;
  15. while i <= args do
  16. insert into majors(id,major) value(i,concat("软件工程-",i));
  17. set i = i+ 1;
  18. end while;
  19. commit;
  20. end
  21. $
  22.  
  23. -- 调用函数,生成数据
  24. -- 先生成10w条试试,同时输入$, 回车执行
  25. call batchInsert(100000);
  26. $

3、性能

10000定制软件定制软件条数据用了0.9s

100000条，5s执行完

100w条数据用了58s

二、通过jdbc定制软件的批量插入语句(add/executeBatch)

1、链接

http://t.zoukankan.com/lizm166-p-7890168.html

2、代码

  1. //获取要设置的Arp基准的List后,插入Arp基准表中    
  2.     public boolean insertArpStandardList(List<ArpTable> list) {
  3.         Connection conn = null;
  4.         PreparedStatement ps = null;
  5.         ResultSet rs = null;
  6.         //MySql的JDBC连接的url中要加rewriteBatchedStatements参数，并保证5.1.13以上版本的驱动，才能实现高性能的批量插入。
  7.         //优化插入性能，用JDBC的addBatch方法，但是注意在连接字符串加上面写的参数。
  8.         //例如： String connectionUrl="jdbc:mysql://192.168.1.100:3306/test?rewriteBatchedStatements=true" ;
  9.         String sql = "insert into arp_standard(guid, devicebrand, devicename, deviceip, ipaddress, " +
  10.                      "macaddress, createtime) values(?,?,?,?,?,?,?)";
  11.         try{
  12.             conn = DBConnection.getConnection();
  13.             ps = conn.prepareStatement(sql);
  14.             //优化插入第一步设置手动提交  
  15.             conn.setAutoCommit(false); 
  16.             int len = list.size();
  17.             for(int i=0; i<len; i++) {
  18.                 ps.setString(1, list.get(i).getGuid());
  19.                 ps.setString(2, list.get(i).getDeviceBrand());
  20.                 ps.setString(3, list.get(i).getDeviceName());
  21.                 ps.setString(4, list.get(i).getDeviceIp());
  22.                 ps.setString(5, list.get(i).getIpAddress());
  23.                 ps.setString(6, list.get(i).getMacAddress());
  24.                 ps.setString(7, list.get(i).getCreateTime());
  25.                 //if(ps.executeUpdate() != 1) r = false;    优化后，不用传统的插入方法了。
  26.                 //优化插入第二步插入代码打包，等一定量后再一起插入。
  27.                 ps.addBatch(); 
  28.                 //if(ps.executeUpdate() != 1)result = false;
  29.                 //每200次提交一次 
  30.                 if((i!=0 && i%200==0) || i==len-1){//可以设置不同的大小；如50，100，200，500，1000等等  
  31.                     ps.executeBatch();  
  32.                     //优化插入第三步提交，批量插入数据库中。
  33.                     conn.commit();  
  34.                     ps.clearBatch();//提交后，Batch清空。
  35.                 }
  36.             }
  37.         } catch (Exception e) {
  38.             System.out.println("MibTaskPack->getArpInfoList() error:" + e.getMessage());
  39.             return false;   //出错才报false
  40.         } finally {
  41.             DBConnection.closeConection(conn, ps, rs);
  42.         }
  43.         return true;
  44.     }

三、通过多线程执行jdbc过程

1、链接

http://t.zoukankan.com/fangts-p-6813515.html

2、代码

  1. package tenThreadInsert;
  2.  
  3. import java.sql.Connection;
  4. import java.sql.DriverManager;
  5. import java.sql.PreparedStatement;
  6. import java.sql.SQLException;
  7. import java.util.Date;
  8.  
  9. public class MyThread extends Thread{
  10.                 public void run() {
  11.                      String url = "jdbc:mysql://127.0.0.1/teacher"; 
  12.                      String name = "com.mysql.jdbc.Driver"; 
  13.                      String user = "root"; 
  14.                      String password = "123456"; 
  15.                     Connection conn = null; 
  16.                     try {
  17.                         Class.forName(name);
  18.                         conn = DriverManager.getConnection(url, user, password);//获取连接 
  19.                         conn.setAutoCommit(false);//关闭自动提交，不然conn.commit()运行到这句会报错
  20.                     } catch (ClassNotFoundException e1) {
  21.                         e1.printStackTrace();
  22.                     } catch (SQLException e) {
  23.                         e.printStackTrace();
  24.                     }
  25.                 // 开始时间
  26.                 Long begin = new Date().getTime();
  27.                 // sql前缀
  28.                 String prefix = "INSERT INTO test_teacher (t_name,t_password,sex,description,pic_url,school_name,regist_date,remark) VALUES ";
  29.                 try {
  30.                     // 保存sql后缀
  31.                     StringBuffer suffix = new StringBuffer();
  32.                     // 设置事务为非自动提交
  33.                     conn.setAutoCommit(false);
  34.                     // 比起st，pst会更好些
  35.                     PreparedStatement  pst = (PreparedStatement) conn.prepareStatement("");//准备执行语句
  36.                     // 外层循环，总提交事务次数
  37.                     for (int i = 1; i <= 10; i++) {
  38.                         suffix = new StringBuffer();
  39.                         // 第j次提交步长
  40.                         for (int j = 1; j <= 100000; j++) {
  41.                             // 构建SQL后缀
  42.                             suffix.append("('" +i*j+"','123456'"+ ",'男'"+",'教师'"+",'www.bbb.com'"+",'Java大学'"+",'"+"2016-08-16 14:43:26"+"','备注'" +"),");
  43.                         }
  44.                         // 构建完整SQL
  45.                         String sql = prefix + suffix.substring(0, suffix.length() - 1);
  46.                         // 添加执行SQL
  47.                         pst.addBatch(sql);
  48.                         // 执行操作
  49.                         pst.executeBatch();
  50.                         // 提交事务
  51.                         conn.commit();
  52.                         // 清空上一次添加的数据
  53.                         suffix = new StringBuffer();
  54.                     }
  55.                     // 头等连接
  56.                     pst.close();
  57.                     conn.close();
  58.                 } catch (SQLException e) {
  59.                     e.printStackTrace();
  60.                 }
  61.                 // 结束时间
  62.                 Long end = new Date().getTime();
  63.                 // 耗时
  64.                 System.out.println("100万条数据插入花费时间 : " + (end - begin) / 1000 + " s"+"  插入完成");
  65.     }  
  66. }
  67.  测试代码
  68.  
  69. package tenThreadInsert;
  70.  
  71. public class Test {
  72.  
  73.     public static void main(String[] args) {
  74.         for (int i = 1; i <=10; i++) {
  75.               new MyThread().start();
  76.             }
  77.     }
  78.  
  79. }

四、一次性插入多条记录

1、原理

MySQL：

INTO Persons (LastName, Address) VALUES ('Wilson', 'Champs-Elysees'),('Gates', 'Champs-Elysees')

Oracle：

insert into 表名 (字段1)

select '1' from dual

union all

select '2' from dual

2、代码

(1)调用

  1. public static Boolean insertManyByOne(int num) {
  2.         String sql = GenSqlUtil.genInsManySql(num);
  3.         // System.out.println(sql);
  4.         jdbcUtils.insertMany(sql);
  5.         System.out.println("共插入" + num + "条数据");
  6.         return true;
  7.     }
  8.  
  9.  
  10. public static String genInsManySql(int num) {
  11.         String sql = "INSERT INTO TEST.\"ABANK\" ";
  12.         for (int i = 0; i < num; i++) {
  13.             sql = sql.concat("select '1', 'CH', '9999', 'Zürcher Kantonalbank', " +
  14.                     "'ZKBKCHZZ80A', ' ', TO_DATE('2009-11-10 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'), " +
  15.                     "TO_DATE('1599-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'), " +
  16.                     "TO_DATE('2017-07-12 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'), " +
  17.                     "'ADMIN', TO_DATE('1599-12-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'), " +
  18.                     "'ADMIN', TO_TIMESTAMP('2021-04-23 08:54:05.000', 'SYYYY-MM-DD HH24:MI:SS:FF3'), " +
  19.                     "TO_TIMESTAMP('"+ dateFormat.format(calendar.getTime()) +
  20.                     "', 'SYYYY-MM-DD HH24:MI:SS:FF3'), " +
  21.                     "HEXTORAW('"+ RandNumGenUtil.genDefLenStr(15) +"') from dual");
  22.             if (i != num -1) {
  23.                 sql = sql.concat(" union all \");
  24.             }
  25.         }
  26.         return sql;
  27.     }

(2)jdbcutils

  1. package com.boulderaitech.utils;
  2.  
  3. import java.sql.*;
  4. import java.util.Arrays;
  5.  
  6. public class JDBCUtil {
  7.     private String user;
  8.     private String pass;
  9.     private String url;
  10.  
  11.     private Connection conn = null;//连接对象
  12.     private ResultSet rs = null;//结果集对象
  13.     private Statement sm = null;
  14.  
  15.     /**
  16.      * 构造函数获得数据库用户名和密码
  17.      *
  18.      * @param user
  19.      * @param pass
  20.      */
  21.     public JDBCUtil(String user, String pass) {
  22.         this.user = user;
  23.         this.pass = pass;
  24.         this.url = "jdbc:oracle:thin:@//172.16.5.162:1521/helowin";
  25.     }
  26.  
  27.     /**
  28.      * 连接数据库
  29.      *
  30.      * @return
  31.      */
  32.     public Connection createConnection() {
  33.         String sDBDriver = "oracle.jdbc.driver.OracleDriver";
  34.         try {
  35.             Class.forName(sDBDriver).newInstance();
  36.             conn = DriverManager.getConnection(url, user, pass);
  37.         } catch (Exception e) {
  38.             System.out.println("数据库连接失败");
  39.             e.printStackTrace();
  40.         }
  41.         return conn;
  42.     }
  43.  
  44.     /**
  45.      * 关闭数据库
  46.      *
  47.      * @param conn
  48.      */
  49.     public void closeConnection(Connection conn) {
  50.         try {
  51.             if (conn != null) {
  52.                 conn.close();
  53.             }
  54.         } catch (Exception e) {
  55.             System.out.println("数据库关闭失败");
  56.             e.printStackTrace();
  57.         }
  58.     }
  59.  
  60.     /**
  61.      * 插入数据
  62.      *
  63.      * @param insert 插入语句
  64.      * @return
  65.      */
  66.     public int insert(String insert) {
  67.         conn = createConnection();
  68.         //String insert = "insert into t_department values('D004','金融部')";
  69.         int re = 0;
  70.         try {
  71.             conn.setAutoCommit(false);//事物开始
  72.  
  73.             sm = conn.createStatement();
  74.             re = sm.executeUpdate(insert);
  75.             if (re < 0) {               //插入失败
  76.                 conn.rollback();      //回滚
  77.                 sm.close();
  78.                 closeConnection(conn);
  79.                 return re;
  80.             }
  81.             conn.commit();            //插入正常
  82.             sm.close();
  83.             closeConnection(conn);
  84.             return re;
  85.         } catch (Exception e) {
  86.             e.printStackTrace();
  87.         }
  88.         closeConnection(conn);
  89.         return 0;
  90.     }
  91.  
  92.     /**
  93.      * 批量插入数据
  94.      */
  95.     public int insertBatch(String[] sql) {
  96.         conn = createConnection();
  97.         //String insert = "insert into t_department values('D004','金融部')";
  98.         int re = 0;
  99.         try {
  100.             conn.setAutoCommit(false);//事务开始
  101.             sm = conn.createStatement();
  102.             Arrays.stream(sql).forEach(x->{
  103.                 try {
  104.                     sm.executeUpdate(x);
  105.                 } catch (SQLException e) {
  106.                     e.printStackTrace();
  107.                 }
  108.             });
  109.             conn.commit();            //插入正常
  110.             sm.close();
  111.             closeConnection(conn);
  112.             return re;
  113.         } catch (Exception e) {
  114.             e.printStackTrace();
  115.         }
  116.         closeConnection(conn);
  117.         return 0;
  118.     }
  119.  
  120.     /**
  121.      * 查询语句
  122.      * 返回结果集
  123.      *
  124.      * @param select
  125.      * @return
  126.      */
  127.     public ResultSet selectSql(String select) {
  128.         conn = createConnection();
  129.         try {
  130.             sm = conn.createStatement();
  131.             rs = sm.executeQuery(select);
  132.             return rs;
  133.         } catch (Exception e) {
  134.             e.printStackTrace();
  135.         }
  136.         return null;
  137.     }
  138.  
  139.     /**
  140.      * 根据结果集输出
  141.      *
  142.      * @param rs
  143.      */
  144.     public void printRs(ResultSet rs) {
  145.         int columnsCount = 0;
  146.         boolean f = false;
  147.         try {
  148.             if (!rs.next()) {
  149.                 return;
  150.             }
  151.             ResultSetMetaData rsmd = rs.getMetaData();
  152.             columnsCount = rsmd.getColumnCount();//数据集的列数
  153.             for (int i = 0; i < columnsCount; i++) {
  154.                 System.out.print(rsmd.getColumnLabel(i + 1) + "/n"); //输出列名
  155.             }
  156.             System.out.println();
  157.  
  158.             while (!f) {
  159.                 for (int i = 1; i <= columnsCount; i++) {
  160.                     //System.out.print(rs.getString(i)+"/t");
  161.                     //逻辑处理
  162.                     String name = rs.getString("NAME");
  163.  
  164.                     System.out.print(rs.getString("NAME") + "/n");
  165.                 }
  166.                 System.out.println();
  167.                 if (!rs.next()) {
  168.                     f = true;
  169.                 }
  170.             }
  171.             rs.close();
  172.         } catch (Exception e) {
  173.             e.printStackTrace();
  174.         }
  175.         closeConnection(conn);
  176.     }
  177.  
  178.     /**
  179.      * 插入数据
  180.      *
  181.      * @param update 更新语句
  182.      * @return
  183.      */
  184.     public int update(String update) {
  185.         conn = createConnection();
  186.         //String insert = "insert into t_department values('D004','金融部')";
  187.         int re = 0;
  188.         try {
  189.             conn.setAutoCommit(false);//事物开始
  190.  
  191.             sm = conn.createStatement();
  192.             re = sm.executeUpdate(update);
  193.             if (re < 0) {               //插入失败
  194.                 conn.rollback();      //回滚
  195.                 sm.close();
  196.                 closeConnection(conn);
  197.                 return re;
  198.             }
  199.             conn.commit();            //插入正常
  200.             sm.close();
  201.             closeConnection(conn);
  202.             return re;
  203.         } catch (Exception e) {
  204.             e.printStackTrace();
  205.         }
  206.         closeConnection(conn);
  207.         return 0;
  208.     }
  209.  
  210.     public int insertMany(String sql) {
  211.         conn = createConnection();
  212.         int re = 0;
  213.         try {
  214.             conn.setAutoCommit(false);//事物开始
  215.  
  216.             sm = conn.createStatement();
  217.             re = sm.executeUpdate(sql);
  218.             if (re < 0) {               //插入失败
  219.                 conn.rollback();      //回滚
  220.                 sm.close();
  221.                 closeConnection(conn);
  222.                 return re;
  223.             }
  224.             conn.commit();            //插入正常
  225.             sm.close();
  226.             closeConnection(conn);
  227.             return re;
  228.         } catch (Exception e) {
  229.             e.printStackTrace();
  230.         }
  231.         closeConnection(conn);
  232.         return 0;
  233.     }
  234. }

五、通过定时器实现定时执行

  1. public static Boolean insertBatchFixTime(int numOfInsert, int timePerEpoch) {
  2.         Timer timer = new Timer();
  3.         timer.schedule(new TimerTask() {
  4.             @Override
  5.             public void run() {
  6.                 insertManyByOne(numOfInsert);
  7.             }
  8.         }, 0L, timePerEpoch * 1000L);
  9.         System.out.println("当前线程：" + Thread.currentThread().getName() + " 当前时间" + LocalDateTime.now());
  10.         return true;
  11.     }

六、通过循环实现批量插入

  1. public static Boolean insertBatchFixCircle(int numOfEachInsert, int numOfEpoch) {
  2.         LocalDateTime start = LocalDateTime.now();
  3.         for (int i = 0; i < numOfEpoch; i++) {
  4.             insertManyByOne(numOfEachInsert);
  5.         }
  6.         System.out.println("共插入" + numOfEachInsert * numOfEpoch+"条数据");
  7.         LocalDateTime end = LocalDateTime.now();
  8.         System.out.println("共耗时" + Duration.between(start, end).toMillis() + "ms");
  9.         return true;
  10.     }
网站建设定制开发 软件系统开发定制 定制软件开发 软件开发定制 定制app开发 app开发定制 app开发定制公司 电商商城定制开发 定制小程序开发 定制开发小程序 客户管理系统开发定制 定制网站 定制开发 crm开发定制 开发公司 小程序开发定制 定制软件 收款定制开发 企业网站定制开发 定制化开发 android系统定制开发 定制小程序开发费用 定制设计 专注app软件定制开发 软件开发定制定制 知名网站建设定制 软件定制开发供应商 应用系统定制开发 软件系统定制开发 企业管理系统定制开发 系统定制开发