一、(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)
begin
declare i int default 1;
-- 开启事务(重要!不开的话,100w数据需要论天算)
start transaction;
while i 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会更好些
PreparedStatement pst = (PreparedStatement) conn.prepareStatement("");//准备执行语句
// 外层循环，总提交事务次数
for (int i = 1; i 四、一次性插入多条记录
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 (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 五、通过定时器实现定时执行
public static Boolean insertBatchFixTime(int numOfInsert, int timePerEpoch) {
Timer timer = new Timer();
timer.schedule(new TimerTask() {
@Override
public 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 