package cn.rojao.utils.batch;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import javax.sql.DataSource;
import java.sql.*;
import java.util.List;
/**
* 大数据导入工具类
*/
public class JDBCBatch {
private static Logger logger = LoggerFactory.getLogger(JDBCBatch.class);
private JDBCBatch() {
}
private volatile static DataSource dataSource;
public static void init(DataSource dataSource0) {
if (dataSource == null) {
dataSource = dataSource0;
}
}
public static int insertBatch(List<Object[]> list,int sqlCount, String... sql) throws SQLException {
AbstractBatch abstractBatch = null;
try {
if (sql != null && sql.length == 1)
abstractBatch = new BatchNoGroup();
else if (sql.length == 2)
abstractBatch = new BatchGroup();
else
abstractBatch = new BatchGroup2();
return abstractBatch.insertBatch(list,sqlCount, sql);
} catch (SQLException e) {
throw new SQLException(e);
} finally {
try {
abstractBatch.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
public static void insertBatchIn(List<Object[]> list,int sqlCount, String... sql) throws SQLException {
AbstractBatch abstractBatch = new BatchNoGroupIn();
try {
abstractBatch.insertBatch(list,sqlCount, sql);
} catch (SQLException e) {
throw new SQLException(e);
} finally {
try {
abstractBatch.close();
} catch (SQLException e) {
e.printStackTrace();
}
}
}
static abstract class AbstractBatch {
Connection connection = null;
/**
* insert into table values(?,?,?)
*/
PreparedStatement pStatement = null;
/**
* insert into group_table select id ,groupId from table where field = ?
*/
PreparedStatement pStatement2 = null;
/**
* insert into table select id ,(select id from table where field = ? )
* select id from table where field in ()
*/
PreparedStatement pStatement3 = null;
Statement statement = null;
/**
* @return
* @throws SQLException
*/
Connection getConnection() throws SQLException {
return JDBCBatch.dataSource.getConnection();
}
/**
* @throws SQLException
*/
void close() throws SQLException {
if (statement != null) {
statement.close();
}
if (pStatement != null) {
pStatement.close();
}
if (pStatement2 != null) {
pStatement2.close();
}
if (pStatement3 != null) {
pStatement3.close();
}
if (connection != null) {
connection.setAutoCommit(Boolean.TRUE);
connection.close();
}
}
/**
* 批量插入
*
* @param list 占位符 后一个占位符用于检索id
* @param sql
*/
abstract int insertBatch(List<Object[]> list,int sqlCount,String... sql) throws SQLException;
}
static class BatchNoGroup extends AbstractBatch {
/**
* list -> object[] {?,?,?}对应占位符
*
* @param list 占位符
* @param sql
* @return
* @throws SQLException
*/
@Override
int insertBatch(List<Object[]> list,int sqlCount, String... sql) throws SQLException {
//Connection conn;
int count = 0;
String batchSql = sql[0];
connection = getConnection();
connection.setAutoCommit(Boolean.FALSE);
pStatement = connection.prepareStatement(batchSql, PreparedStatement.RETURN_GENERATED_KEYS);
long start = System.currentTimeMillis();
for (int i = 0; i < list.size(); i++) {
for (int j = 1, k = 0; k < list.get(0).length - 1; j++, k++) {//
Object obj = list.get(i)[k];
if (obj instanceof Integer) {
pStatement.setInt(j, (Integer) obj);
} else if (obj instanceof String) {
pStatement.setString(j, (String) obj);
} else if (obj instanceof Long) {
pStatement.setLong(j, (Long) obj);
} else if (obj == null) {
pStatement.setNull(j, java.sql.Types.INTEGER);
}
}
pStatement.addBatch();
if (i % sqlCount == 0 && i != 0) {
pStatement.executeBatch();
ResultSet result = pStatement.getGeneratedKeys();
while (result.next()) {
count++;
}
connection.commit();
System.out.println("insert rows :" + i + ".....");
}
}
pStatement.executeBatch();
ResultSet result = pStatement.getGeneratedKeys();
while (result.next()) {
count++;
}
connection.commit(); // 提交
logger.info("============================= 插入" + count + "条数据用了" + (System.currentTimeMillis() - start) / 1000 + "秒 =============================");
return count;
}
}
static class BatchGroup extends AbstractBatch {
/**
* object[] {?,?,?}对应占位符 最后一个占位符用于检索分组id
*
* @param list 占位符
* @param sql
* @return
* @throws SQLException
*/
@Override
int insertBatch(List<Object[]> list,int sqlCount ,String... sql) throws SQLException {
//Connection conn;
int count = 0;
String batchSql0 = sql[0];
String batchSql1 = sql[1];
connection = getConnection();
connection.setAutoCommit(Boolean.FALSE);
pStatement = connection.prepareStatement(batchSql0, PreparedStatement.RETURN_GENERATED_KEYS);
pStatement2 = connection.prepareStatement(batchSql1, PreparedStatement.RETURN_GENERATED_KEYS);//分组sql
long start = System.currentTimeMillis();
int length;
for (int i = 0; i < list.size(); i++) {
for (int j = 1, k = 0; j < (length = list.get(0).length) + 1; j++, k++) {//替换占位符
Object obj = list.get(i)[k];
if (k < length - 1) {
if (obj instanceof Integer) {
pStatement.setInt(j, (Integer) obj);
} else if (obj instanceof String) {
pStatement.setString(j, (String) obj);
} else if (obj instanceof Long) {
pStatement.setLong(j, (Long) obj);
} else if (obj == null) {
pStatement.setNull(j, java.sql.Types.INTEGER);
}
} else {//替换分组占位符
Object o = list.get(i)[k];
String[] split = o.toString().split("\\|\\|");
for (int n = 1, m = 0; m < split.length; n++, m++) {
pStatement2.setString(n, split[m]);
}
没有合适的资源?快使用搜索试试~ 我知道了~
资源推荐
资源详情
资源评论
收起资源包目录
JDBCBatch.rar (1个子文件)
JDBCBatch.java 17KB
共 1 条
- 1
资源评论
CNZYYH
- 粉丝: 39
- 资源: 12
上传资源 快速赚钱
- 我的内容管理 展开
- 我的资源 快来上传第一个资源
- 我的收益 登录查看自己的收益
- 我的积分 登录查看自己的积分
- 我的C币 登录后查看C币余额
- 我的收藏
- 我的下载
- 下载帮助
安全验证
文档复制为VIP权益,开通VIP直接复制
信息提交成功