一、(MySQL)通过函数/存储过程

1、链接

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

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

2、代码

-- 进入数据库use test;-- 显示所有表show tables;-- 创建majors表create table majors(id int, major varchar(255));-- 定义结束符$delimiter "$";-- 创建存储过程,定义存储方法create procedure batchInsert(in args int)begindeclare i int default 1;-- 开启事务(重要!不开的话,100w数据需要论天算)start transaction;while i <= args doinsert into majors(id,major) value(i,concat("软件工程-",i));set i = i+ 1;end while;commit;end$ -- 调用函数,生成数据-- 先生成10w条试试,同时输入$, 回车执行call batchInsert(100000);$

3、性能

10000条数据用了0.9s

100000条，5s执行完

100w条数据用了58s

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

1、链接

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

2、代码

//获取要设置的Arp基准的List后,插入Arp基准表中public boolean insertArpStandardList(List list) {Connection conn = null;PreparedStatement ps = null;ResultSet rs = null;//MySql的JDBC连接的url中要加rewriteBatchedStatements参数，并保证5.1.13以上版本的驱动，才能实现高性能的批量插入。//优化插入性能，用JDBC的addBatch方法，但是注意在连接字符串加上面写的参数。//例如： String connectionUrl="jdbc:mysql://192.168.1.100:3306/test?rewriteBatchedStatements=true" ;String sql = "insert into arp_standard(guid, devicebrand, devicename, deviceip, ipaddress, " + "macaddress, createtime) values(?,?,?,?,?,?,?)";try{conn = DBConnection.getConnection();ps = conn.prepareStatement(sql);//优化插入第一步设置手动提交conn.setAutoCommit(false); int len = list.size();for(int i=0; igetArpInfoList() error:" + e.getMessage());return false; //出错才报false} finally {DBConnection.closeConection(conn, ps, rs);}return true;}

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

1、链接

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

2、代码

package tenThreadInsert; import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.SQLException;import java.util.Date; public class MyThread extends Thread{public void run() { String url = "jdbc:mysql://127.0.0.1/teacher";String name = "com.mysql.jdbc.Driver";String user = "root";String password = "123456"; Connection conn = null; try {Class.forName(name);conn = DriverManager.getConnection(url, user, password);//获取连接 conn.setAutoCommit(false);//关闭自动提交，不然conn.commit()运行到这句会报错} catch (ClassNotFoundException e1) {e1.printStackTrace();} catch (SQLException e) {e.printStackTrace();}// 开始时间Long begin = new Date().getTime();// sql前缀String prefix = "INSERT INTO test_teacher (t_name,t_password,sex,description,pic_url,school_name,regist_date,remark) VALUES ";try {// 保存sql后缀StringBuffer suffix = new StringBuffer();// 设置事务为非自动提交conn.setAutoCommit(false);// 比起st，pst会更好些PreparedStatementpst = (PreparedStatement) conn.prepareStatement("");//准备执行语句// 外层循环，总提交事务次数for (int i = 1; i <= 10; i++) {suffix = new StringBuffer();// 第j次提交步长for (int j = 1; j <= 100000; j++) {// 构建SQL后缀suffix.append("('" +i*j+"','123456'"+ ",'男'"+",'教师'"+",'www.bbb.com'"+",'Java大学'"+",'"+"2016-08-16 14:43:26"+"','备注'" +"),");}// 构建完整SQLString sql = prefix + suffix.substring(0, suffix.length() - 1);// 添加执行SQLpst.addBatch(sql);// 执行操作pst.executeBatch();// 提交事务conn.commit();// 清空上一次添加的数据suffix = new StringBuffer();}// 头等连接pst.close();conn.close();} catch (SQLException e) {e.printStackTrace();}// 结束时间Long end = new Date().getTime();// 耗时System.out.println("100万条数据插入花费时间 : " + (end - begin) / 1000 + " s"+"插入完成");}} 测试代码package tenThreadInsert; public class Test { public static void main(String[] args) {for (int i = 1; i <=10; i++) {new MyThread().start();}} }

四、一次性插入多条记录

1、原理

MySQL：

INSERT 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)调用

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

(2)jdbcutils

package com.boulderaitech.utils;import java.sql.*;import java.util.Arrays;public class JDBCUtil {private String user;private String pass;private String url;private Connection conn = null;//连接对象private ResultSet rs = null;//结果集对象private Statement sm = null;/** * 构造函数获得数据库用户名和密码 * * @param user * @param pass */public JDBCUtil(String user, String pass) {this.user = user;this.pass = pass;this.url = "jdbc:oracle:thin:@//172.16.5.162:1521/helowin";}/** * 连接数据库 * * @return */public Connection createConnection() {String sDBDriver = "oracle.jdbc.driver.OracleDriver";try {Class.forName(sDBDriver).newInstance();conn = DriverManager.getConnection(url, user, pass);} catch (Exception e) {System.out.println("数据库连接失败");e.printStackTrace();}return conn;}/** * 关闭数据库 * * @param conn */public void closeConnection(Connection conn) {try {if (conn != null) {conn.close();}} catch (Exception e) {System.out.println("数据库关闭失败");e.printStackTrace();}}/** * 插入数据 * * @param insert 插入语句 * @return */public int insert(String insert) {conn = createConnection();//String insert = "insert into t_department values('D004','金融部')";int re = 0;try {conn.setAutoCommit(false);//事物开始sm = conn.createStatement();re = sm.executeUpdate(insert);if (re {try {sm.executeUpdate(x);} catch (SQLException e) {e.printStackTrace();}});conn.commit();//插入正常sm.close();closeConnection(conn);return re;} catch (Exception e) {e.printStackTrace();}closeConnection(conn);return 0;}/** * 查询语句 * 返回结果集 * * @param select * @return */public ResultSet selectSql(String select) {conn = createConnection();try {sm = conn.createStatement();rs = sm.executeQuery(select);return rs;} catch (Exception e) {e.printStackTrace();}return null;}/** * 根据结果集输出 * * @param rs */public void printRs(ResultSet rs) {int columnsCount = 0;boolean f = false;try {if (!rs.next()) {return;}ResultSetMetaData rsmd = rs.getMetaData();columnsCount = rsmd.getColumnCount();//数据集的列数for (int i = 0; i < columnsCount; i++) {System.out.print(rsmd.getColumnLabel(i + 1) + "/n"); //输出列名}System.out.println();while (!f) {for (int i = 1; i <= columnsCount; i++) {//System.out.print(rs.getString(i)+"/t");//逻辑处理String name = rs.getString("NAME");System.out.print(rs.getString("NAME") + "/n");}System.out.println();if (!rs.next()) {f = true;}}rs.close();} catch (Exception e) {e.printStackTrace();}closeConnection(conn);}/** * 插入数据 * * @param update 更新语句 * @return */public int update(String update) {conn = createConnection();//String insert = "insert into t_department values('D004','金融部')";int re = 0;try {conn.setAutoCommit(false);//事物开始sm = conn.createStatement();re = sm.executeUpdate(update);if (re < 0) { //插入失败conn.rollback();//回滚sm.close();closeConnection(conn);return re;}conn.commit();//插入正常sm.close();closeConnection(conn);return re;} catch (Exception e) {e.printStackTrace();}closeConnection(conn);return 0;}public int insertMany(String sql) {conn = createConnection();int re = 0;try {conn.setAutoCommit(false);//事物开始sm = conn.createStatement();re = sm.executeUpdate(sql);if (re < 0) { //插入失败conn.rollback();//回滚sm.close();closeConnection(conn);return re;}conn.commit();//插入正常sm.close();closeConnection(conn);return re;} catch (Exception e) {e.printStackTrace();}closeConnection(conn);return 0;}}

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

public static Boolean insertBatchFixTime(int numOfInsert, int timePerEpoch) {Timer timer = new Timer();timer.schedule(new TimerTask() {@Overridepublic void run() {insertManyByOne(numOfInsert);}}, 0L, timePerEpoch * 1000L);System.out.println("当前线程：" + Thread.currentThread().getName() + " 当前时间" + LocalDateTime.now());return true;}

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

public static Boolean insertBatchFixCircle(int numOfEachInsert, int numOfEpoch) {LocalDateTime start = LocalDateTime.now();for (int i = 0; i < numOfEpoch; i++) {insertManyByOne(numOfEachInsert);}System.out.println("共插入" + numOfEachInsert * numOfEpoch+"条数据");LocalDateTime end = LocalDateTime.now();System.out.println("共耗时" + Duration.between(start, end).toMillis() + "ms");return true;}