1、没有使用数据库连接池(主要思想是分页查询、分批插入)
package util;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class BatchProcess {
public static void batchInsert(int m, int n) {
long start = System.currentTimeMillis();
String insertSql = "INSERT INTO LIJING727_ORIGDATA(DATA0,DATA1,"
+ "DATA2,DATA3,DATA4,DATA5,DATA6,DATA7,DATA8,DATA9) VALUES "
+ "(?,?,?,?,?,?,?,?,?,?)";
for (int i = 0; i < m; i++) {
Connection conn = DbConnStatic.getConnection();
try {
PreparedStatement pstmt = conn.prepareStatement(insertSql);
for (int j = 0; j < n; j++) {
pstmt.setInt(1, i * n + j + 1);
pstmt.setString(2, "test" + 2);
pstmt.setString(3, "test" + 3);
pstmt.setString(4, "test" + 4);
pstmt.setString(5, "test" + 5);
pstmt.setString(6, "test" + 6);
pstmt.setString(7, "test" + 7);
pstmt.setString(8, "test" + 8);
pstmt.setString(9, "test" + 9);
pstmt.setString(10, "test" + 10);
pstmt.addBatch();
}
pstmt.executeBatch();
pstmt.close();
// conn.close();
// DbConnection.freeConnection();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
long end = System.currentTimeMillis();
System.out
.println("批量插入数据" + m + "*" + n + " 用时" + (end - start) / 1000f);
}
public static void deleteAllData() {
long start = System.currentTimeMillis();
Connection conn = DbConnStatic.getConnection();
try {
Statement stmt = conn.createStatement();
String sql1 = "DELETE FROM LIJING727_ORIGDATA";
//String sql2 = "DELETE FROM LIJING727_TEMPDATA";
stmt.execute(sql1);
//stmt.execute(sql2);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
long end = System.currentTimeMillis();
System.out.println("批量删除所有数据 用时" + (end - start) / 1000f);
}
public static void transferData(int m, int n) {
long start = System.currentTimeMillis();
String insertSql = "INSERT INTO LIJING727_TEMPDATA(DATA0,DATA1,"
+ "DATA2,DATA3,DATA4,DATA5,DATA6,DATA7,DATA8,DATA9) VALUES "
+ "(?,?,?,?,?,?,?,?,?,?)";
String selectSql = "SELECT * FROM (SELECT rownum r,a.* "
+ "FROM LIJING727_ORIGDATA a WHERE rownum <= ?) WHERE r >= ?";
for (int i = 0; i < m; i++) {
Connection insertConn = new DbConn().getConnection();
Connection selectConn = new DbConn().getConnection();
try {
PreparedStatement selectPstmt = selectConn.prepareStatement(selectSql);
PreparedStatement insertPstmt = insertConn.prepareStatement(insertSql);
selectPstmt.setInt(1, (i+1) * n);
selectPstmt.setInt(2, i * n + 1);
ResultSet rs = selectPstmt.executeQuery();
while (rs.next()) {
insertPstmt.setInt(1, rs.getInt(2));
insertPstmt.setString(2, rs.getString(3));
insertPstmt.setString(3, rs.getString(4));
insertPstmt.setString(4, rs.getString(5));
insertPstmt.setString(5, rs.getString(6));
insertPstmt.setString(6, rs.getString(7));
insertPstmt.setString(7, rs.getString(8));
insertPstmt.setString(8, rs.getString(9));
insertPstmt.setString(9, rs.getString(10));
insertPstmt.setString(10, rs.getString(11));
insertPstmt.addBatch();
}
insertPstmt.executeBatch();
insertPstmt.close();
selectPstmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
long end = System.currentTimeMillis();
System.out
.println("批量转换" + m + "*" + n + " 用时" + (end - start) / 1000f);
}
}
2、使用数据库连接池
package util;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import oracle.jdbc.driver.OraclePreparedStatement;
public class MultiThreadProcess {
public static void batchInsert(int m, int n) throws ClassNotFoundException,
SQLException, InterruptedException {
ExecutorService exec = Executors.newCachedThreadPool();
DbPool dbPool = new DbPool();
dbPool.createPool();
long start = System.currentTimeMillis();
for (int i = 0; i < m; i++) {
exec.execute(new BatchInsertTask(dbPool, i, n, start));
// Thread thread = new Thread(new BatchInsertTask(dbPool, i, n,
// start));
// thread.join(); // 线程挂接,当前线程阻塞,thread执行完后,主线程才能执行
// exec.execute(thread);
// thread.start();
}
exec.shutdown();
}
public static void batchTransfer(int m, int n)
throws ClassNotFoundException, SQLException, InterruptedException {
ExecutorService exec = Executors.newCachedThreadPool();
DbPool dbPool = new DbPool();
dbPool.createPool();
long start = System.currentTimeMillis();
for (int i = 0; i < m; i++) {
exec.execute(new BatchTransferTask(dbPool, i, n, start));
}
exec.shutdown();
}
}
class BatchInsertTask implements Runnable {
DbPool dbPool;
int threadNum = 0;
int batchNum = 0;
long start;
BatchInsertTask(DbPool dbPool, int threadNum, int batchNum, long startTime) {
this.dbPool = dbPool;
this.threadNum = threadNum;
this.batchNum = batchNum;
start = startTime;
}
@Override
public void run() {
String insertSql = "INSERT INTO LIJING727_ORIGDATA(DATA0,DATA1,"
+ "DATA2,DATA3,DATA4,DATA5,DATA6,DATA7,DATA8,DATA9) VALUES "
+ "(?,?,?,?,?,?,?,?,?,?)";
try {
Connection conn = dbPool.getConnection();
OraclePreparedStatement pstmt = (OraclePreparedStatement) conn
.prepareStatement(insertSql);
for (int j = 0; j < batchNum; j++) {
pstmt.setInt(1, threadNum * batchNum + j + 1);
pstmt.setString(2, "test" + 2);
pstmt.setString(3, "test" + 3);
pstmt.setString(4, "test" + 4);
pstmt.setString(5, "test" + 5);
pstmt.setString(6, "test" + 6);
pstmt.setString(7, "test" + 7);
pstmt.setString(8, "test" + 8);
pstmt.setString(9, "test" + 9);
pstmt.setString(10, "test" + 10);
pstmt.addBatch();
}
pstmt.executeBatch();
pstmt.close();
dbPool.returnConnection(conn);
long end = System.currentTimeMillis();
System.out.println("批量插入数据用时" + (end - start) / 1000f);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
class BatchTransferTask implements Runnable {
DbPool dbPool;
int threadNum = 0;
int batchNum = 0;
long start;
BatchTransferTask(DbPool dbPool, int threadNum, int batchNum, long startTime) {
this.dbPool = dbPool;
this.threadNum = threadNum;
this.batchNum = batchNum;
start = startTime;
}
@Override
public void run() {
String insertSql = "INSERT INTO LIJING727_TEMPDATA(DATA0,DATA1,"
+ "DATA2,DATA3,DATA4,DATA5,DATA6,DATA7,DATA8,DATA9) VALUES "
+ "(?,?,?,?,?,?,?,?,?,?)";
String selectSql = "SELECT b.DATA0,b.DATA1,b.DATA2,b.DATA3,b.DATA4,"
+ "b.DATA5,b.DATA6,b.DATA7,b.DATA8,b.DATA9"
+ " FROM (SELECT rownum r,"
+ "a.DATA0,a.DATA1,a.DATA2,a.DATA3,a.DATA4,a.DATA5,a.DATA6,"
+ "a.DATA7,a.DATA8,a.DATA9"
+ " FROM LIJING727_ORIGDATA a WHERE rownum <= ?) b"
+ " WHERE r >= ?";
// String selectSql = "SELECT b.DATA0,b.DATA1,b.DATA2,b.DATA3,b.DATA4,"
// + "b.DATA5,b.DATA6,b.DATA7,b.DATA8,b.DATA9"
// + " FROM (SELECT rownum r,a.*"
// + " FROM LIJING727_ORIGDATA a WHERE rownum <= ?) b"
// + " WHERE r >= ?";
try {
Connection insertConn = dbPool.getConnection();
Connection selectConn = dbPool.getConnection();
OraclePreparedStatement selectPstmt = (OraclePreparedStatement) selectConn
.prepareStatement(selectSql);
OraclePreparedStatement insertPstmt = (OraclePreparedStatement) insertConn
.prepareStatement(insertSql);
selectPstmt.setInt(1, (threadNum + 1) * batchNum);
selectPstmt.setInt(2, threadNum * batchNum + 1);
ResultSet rs = selectPstmt.executeQuery();
while (rs.next()) {
insertPstmt.setInt(1, rs.getInt(1));
insertPstmt.setString(2, rs.getString(2));
insertPstmt.setString(3, rs.getString(3));
insertPstmt.setString(4, rs.getString(4));
insertPstmt.setString(5, rs.getString(5));
insertPstmt.setString(6, rs.getString(6));
insertPstmt.setString(7, rs.getString(7));
insertPstmt.setString(8, rs.getString(8));
insertPstmt.setString(9, rs.getString(9));
insertPstmt.setString(10, rs.getString(10));
insertPstmt.addBatch();
}
rs.close();
insertPstmt.executeBatch();
insertPstmt.close();
selectPstmt.close();
dbPool.returnConnection(insertConn);
dbPool.returnConnection(selectConn);
long end = System.currentTimeMillis();
System.out.println("批量转换数据用时" + (end - start) / 1000f);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
如果数据量很大,造成JVM溢出,可以在查询时的是分次处理,减少JVM的负担。
package util;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import oracle.jdbc.driver.OraclePreparedStatement;
public class MultiBatchProcess {
public static void batchInsert(int threadNum, int timeNum, int batchNum)
throws ClassNotFoundException, SQLException, InterruptedException {
ExecutorService exec = Executors.newCachedThreadPool();
DbPool dbPool = new DbPool();
dbPool.createPool();
long start = System.currentTimeMillis();
for (int i = 0; i < threadNum; i++) {
exec.execute(new BatchInsert(dbPool, i, timeNum, batchNum, start));
}
exec.shutdown();
}
public static void batchTranfer(int threadNum, int timeNum, int batchNum)
throws ClassNotFoundException, SQLException, InterruptedException {
ExecutorService exec = Executors.newCachedThreadPool();
DbPool dbPool = new DbPool();
dbPool.createPool();
long start = System.currentTimeMillis();
for (int i = 0; i < threadNum; i++) {
exec
.execute(new BatchTransfer(dbPool, i, timeNum, batchNum,
start));
}
exec.shutdown();
}
}
class BatchTransfer implements Runnable {
DbPool dbPool;
int threadNo = 0;
int timeNum = 0;
int batchNum = 0;
long start = 0;
BatchTransfer(DbPool dbPool, int threadNum, int timeNum, int batchnum,
long start) {
this.dbPool = dbPool;
this.threadNo = threadNum;
this.timeNum = timeNum;
this.batchNum = batchnum;
this.start = start;
}
@Override
public void run() {
String insertSql = "INSERT INTO LIJING727_TEMPDATA(DATA0,DATA1,"
+ "DATA2,DATA3,DATA4,DATA5,DATA6,DATA7,DATA8,DATA9) VALUES "
+ "(?,?,?,?,?,?,?,?,?,?)";
String selectSql = "SELECT b.DATA0,b.DATA1,b.DATA2,b.DATA3,b.DATA4,"
+ "b.DATA5,b.DATA6,b.DATA7,b.DATA8,b.DATA9"
+ " FROM (SELECT rownum r,"
+ "a.DATA0,a.DATA1,a.DATA2,a.DATA3,a.DATA4,a.DATA5,a.DATA6,"
+ "a.DATA7,a.DATA8,a.DATA9"
+ " FROM LIJING727_ORIGDATA a WHERE rownum <= ?) b"
+ " WHERE r >= ?";
// String selectSql = "SELECT b.DATA0,b.DATA1,b.DATA2,b.DATA3,b.DATA4,"
// + "b.DATA5,b.DATA6,b.DATA7,b.DATA8,b.DATA9"
// + " FROM (SELECT rownum r,a.*"
// + " FROM LIJING727_ORIGDATA a WHERE rownum <= ?) b"
// + " WHERE r >= ?";
try {
Connection insertConn = dbPool.getConnection();
Connection selectConn = dbPool.getConnection();
OraclePreparedStatement selectPstmt = (OraclePreparedStatement) selectConn
.prepareStatement(selectSql);
OraclePreparedStatement insertPstmt = (OraclePreparedStatement) insertConn
.prepareStatement(insertSql);
for (int i = 0; i < timeNum; i++) {
selectPstmt.setInt(1, threadNo * timeNum * batchNum + (i + 1)
* batchNum);
selectPstmt.setInt(2, threadNo * timeNum * batchNum + i
* batchNum + 1);
ResultSet rs = selectPstmt.executeQuery();
while (rs.next()) {
insertPstmt.setInt(1, rs.getInt(1));
insertPstmt.setString(2, rs.getString(2));
insertPstmt.setString(3, rs.getString(3));
insertPstmt.setString(4, rs.getString(4));
insertPstmt.setString(5, rs.getString(5));
insertPstmt.setString(6, rs.getString(6));
insertPstmt.setString(7, rs.getString(7));
insertPstmt.setString(8, rs.getString(8));
insertPstmt.setString(9, rs.getString(9));
insertPstmt.setString(10, rs.getString(10));
insertPstmt.addBatch();
}
rs.close();
insertPstmt.executeBatch();
insertPstmt.clearBatch();
}
insertPstmt.close();
selectPstmt.close();
dbPool.returnConnection(insertConn);
dbPool.returnConnection(selectConn);
long end = System.currentTimeMillis();
System.out.println("批量转换数据用时" + (end - start) / 1000f);
} catch (SQLException e) {
e.printStackTrace();
}
}
}
class BatchInsert implements Runnable {
DbPool dbPool;
int threadNo = 0;
int timeNum = 0;
int batchNum = 0;
long start = 0;
BatchInsert(DbPool dbPool, int threadNum, int timeNum, int batchnum,
long start) {
this.dbPool = dbPool;
this.threadNo = threadNum;
this.timeNum = timeNum;
this.batchNum = batchnum;
this.start = start;
}
@Override
public void run() {
String insertSql = "INSERT INTO LIJING727_ORIGDATA(DATA0,DATA1,"
+ "DATA2,DATA3,DATA4,DATA5,DATA6,DATA7,DATA8,DATA9) VALUES "
+ "(?,?,?,?,?,?,?,?,?,?)";
try {
Connection conn = dbPool.getConnection();
OraclePreparedStatement pstmt = (OraclePreparedStatement) conn
.prepareStatement(insertSql);
for (int i = 0; i < timeNum; i++) {
for (int j = 0; j < batchNum; j++) {
pstmt.setInt(1, threadNo * timeNum * batchNum + i
* batchNum + j + 1);
pstmt.setString(2, "test" + 2);
pstmt.setString(3, "test" + 3);
pstmt.setString(4, "test" + 4);
pstmt.setString(5, "test" + 5);
pstmt.setString(6, "test" + 6);
pstmt.setString(7, "test" + 7);
pstmt.setString(8, "test" + 8);
pstmt.setString(9, "test" + 9);
pstmt.setString(10, "test" + 10);
pstmt.addBatch();
}
pstmt.executeBatch();
pstmt.clearBatch();
}
pstmt.close();
dbPool.returnConnection(conn);
long end = System.currentTimeMillis();
System.out.println("批量插入数据用时" + (end - start) / 1000f);
} catch (SQLException e) {
e.printStackTrace();
}
}
}