public static void saveColumns(List<List<ContentColumn>> list) throws SQLException {
QueryRunner run = new QueryRunner();
// save the url first
String sql =
"insert IGNORE into urls (job_id, url, success, create_time, update_time) values (1,?,1,now(),now())";
List<Object[]> paramsList = new ArrayList<Object[]>();
for (List<ContentColumn> subList : list) {
for (ContentColumn column : subList) {
paramsList.add(new Object[] {column.parentUrl});
}
}
Object[][] params = new Object[paramsList.size()][1];
for (int i = 0; i < paramsList.size(); i++) {
params[i] = paramsList.get(i);
}
run.batch(getConnection(), sql, params);
// save the columns
sql =
"INSERT into `columns` (job_id,url,title,value,create_by,create_time,update_by,update_time) values (job_id,?,?,?,NULL,now(),NULL,now())";
paramsList = new ArrayList<Object[]>();
for (List<ContentColumn> subList : list) {
for (ContentColumn column : subList) {
paramsList.add(new Object[] {column.parentUrl, column.name, column.value});
}
}
params = new Object[paramsList.size()][3];
for (int i = 0; i < paramsList.size(); i++) {
params[i] = paramsList.get(i);
}
run.batch(getConnection(), sql, params);
}
private void run() {
final PacketUserData userData = new PacketUserData();
userData.userCode = 123456789;
userData.playerName = "QMAClone";
try {
database.setUserData(userData);
final List<Object[]> params = Lists.newArrayList();
System.out.println("Parsing problems");
database.processProblems(
new ProblemProcessable() {
@Override
public void process(PacketProblem problem) throws Exception {
params.add(
new Object[] {
problem.id,
userData.userCode,
new Timestamp(System.currentTimeMillis()),
"kishibe.dyndns.tv",
problem.toChangeSummary()
});
}
});
System.out.println("Inserting creation logs");
runner.batch(
"INSERT INTO creation_log (PROBLEM_ID, USER_CODE, DATE, MACHINE_IP, SUMMARY) VALUES (?, ?, ?, ?, ?)",
params.toArray(new Object[0][]));
} catch (DatabaseException | SQLException e) {
logger.log(Level.WARNING, "問題更新ログの更新に失敗しました", e);
}
}
/**
* 批量执行指定的SQL语句
*
* @param sql
* @param params
* @return
* @throws DBException
*/
public int[] batch(String sql, Object[][] params) throws SQLException {
try {
printIn(sql);
return queryRunner.batch(getConnection(), sql, params);
} catch (SQLException e) {
throw new SQLException(e);
}
}
/** 부분 커밋하면서 입력한다. ex) batchInsert(dataSource,"QQ", params, 1000); */
public static void batchInsert(
DataSource dataSource, String tableName, List<Object[]> params, int commitInterval) {
Connection conn = null;
try {
conn = dataSource.getConnection();
conn.setAutoCommit(false);
QueryRunner runner = new QueryRunner();
StringAppender appender = new StringAppender();
appender.appendLine(
"SELECT a.TABLE_NAME,a.COLUMN_NAME,COMMENTS,DATA_TYPE,DATA_LENGTH,DATA_PRECISION,DATA_SCALE");
appender.appendLine("FROM user_tab_columns a JOIN USER_COL_COMMENTS b");
appender.appendLine("ON a.COLUMN_NAME = b.COLUMN_NAME AND a.TABLE_NAME = b.TABLE_NAME");
appender.appendLine("WHERE a.TABLE_NAME = ? ");
appender.appendLine("ORDER BY a.TABLE_NAME, COLUMN_ID ");
List<String> columnNames = Lists.newArrayList();
// 걍 한번 쓸거라 일케 함
List<Map<String, Object>> result =
runner.query(conn, appender.toString(), LIST_MAP_HANDLER, tableName);
for (Map<String, Object> value : result) {
columnNames.add(value.get("COLUMN_NAME").toString());
}
String sql =
"INSERT INTO "
+ tableName
+ " ("
+ StringUtil.join(columnNames, ",")
+ ") values ("
+ StringUtil.iterateStr("?", ",", columnNames.size())
+ ")";
List<List<Object[]>> splited = CollectionUtil.splitBySize(params, commitInterval);
for (List<Object[]> inputList : splited) {
Object[][] convertedList = new Object[inputList.size()][];
for (int i = 0; i < convertedList.length; i++) {
Object[] input = inputList.get(i);
Object[] converted = new Object[input.length];
for (int j = 0; j < input.length; j++) {
converted[j] = CONVERTER.convert(input[j]);
}
convertedList[i] = converted;
}
runner.batch(conn, sql, convertedList);
conn.commit();
}
} catch (SQLException e) {
throw new SQLRuntimeException(e);
} finally {
DbUtils.closeQuietly(conn);
}
}
/**
* 批处理
*
* @throws Exception
*/
@Test
public void testBatch() throws Exception {
String sql = "insert into admin (userName,pwd) values(?,?)";
conn = DbUtil.getConnection();
QueryRunner qr = new QueryRunner();
// 批量插入
qr.batch(conn, sql, new Object[][] {{"jack1", "888"}, {"jack2", "999"}});
// 关闭
DbUtils.close(conn);
}
public static int updateBatch(String sql, Object[][] paras) throws SQLException {
Connection connection = getConnect();
int result = 0;
try {
int[] results = queryRunner.batch(connection, sql, paras);
for (int i : results) {
result += i;
}
} catch (SQLException e) {
logger.error(e.getMessage());
throw e;
} finally {
returnConnect(connection);
}
return result;
}
public void run() {
if (this.classifier == null) return;
WebClient client = WebClientFactory.borrowClient();
String[] urls = extractor.getUrls();
final ArrayList<Object[]> valuesToInsert = new ArrayList<Object[]>();
final Date timeNow = new Date();
final int revisionId;
final Annotation[] annotations = extractor.getAnnotations();
try {
revisionId = ScrapeHelper.createRevision(extractor);
HtmlPage page;
for (int i = 0; i < urls.length; i++) {
try {
page = ScrapeHelper.downloadPage(urls[0], client, null);
System.out.println(page.getTitleText());
classifier.classifyPageElements(
page,
new ClassifiedTask() {
public void performTask(int label, DomNode element) {
if (label < annotations.length
&& !element.getNodeName().equalsIgnoreCase("body")) {
valuesToInsert.add(
new Object[] {
annotations[label].getId(), element.asXml(), timeNow, timeNow, revisionId
});
}
}
});
} catch (Exception e) {
System.out.println(e.getMessage());
}
}
Object[][] values = new Object[valuesToInsert.size()][valuesToInsert.get(0).length];
for (int j = 0; j < values.length; j++) values[j] = valuesToInsert.get(j);
queryRunner.batch(
"INSERT INTO scraped_values (annotation_id,value,created_at,updated_at,revision_id) VALUES (?,?,?,?,?)",
values);
} catch (SQLException e1) {
e1.printStackTrace();
}
WebClientFactory.returnClient(client);
}
/** SQL을 직접 사용하는건 다 이쪽이다. 내부 API 호출은 batchInsert와 동일 */
public static void batchUpdate(
DataSource dataSource, String sql, List<Object[]> params, int commitInterval) {
Connection conn = null;
try {
conn = dataSource.getConnection();
conn.setAutoCommit(false);
QueryRunner runner = new QueryRunner();
List<List<Object[]>> splited = CollectionUtil.splitBySize(params, commitInterval);
for (List<Object[]> each : splited) {
runner.batch(conn, sql, each.toArray(new Object[each.size()][]));
conn.commit();
}
} catch (SQLException e) {
throw new SQLRuntimeException(e);
} finally {
DbUtils.closeQuietly(conn);
}
}