return copyTableColumns(source, dest, table, selectQuery, null);
}
public DbTemplate copyTableColumns(DataSource source, DataSource dest, String table, String selectQuery, @Nullable String[] columnNames) {
LOG.debug("Copy table {}", table);
StopWatch watch = profiling.start("previewdb", Level.BASIC);
truncate(dest, table);
Connection sourceConnection = null;
Statement sourceStatement = null;
ResultSet sourceResultSet = null;
Connection destConnection = null;
ResultSet destResultSet = null;
PreparedStatement destStatement = null;
int count = 0;
try {
sourceConnection = source.getConnection();
sourceStatement = sourceConnection.createStatement();
sourceResultSet = sourceStatement.executeQuery(selectQuery);
if (sourceResultSet.next()) {
if (columnNames == null) {
// Copy all columns
columnNames = columnNames(sourceResultSet);
}
int[] columnTypes = columnTypes(sourceResultSet);
destConnection = dest.getConnection();
destConnection.setAutoCommit(false);
String insertSql = new StringBuilder().append("INSERT INTO ").append(table).append("(").append(Joiner.on(",").join(columnNames))
.append(") VALUES(").append(StringUtils.repeat("?", ",", columnNames.length)).append(")").toString();
destStatement = destConnection.prepareStatement(insertSql);
do {
copyColumns(sourceResultSet, destStatement, columnNames, columnTypes);
count++;
destStatement.addBatch();
if (count % BatchSession.MAX_BATCH_SIZE == 0) {
destStatement.executeBatch();
destConnection.commit();
}
} while (sourceResultSet.next());
destStatement.executeBatch();
destConnection.commit();
}
} catch (SQLException e) {
LOG.error("Fail to copy table " + table, e);
throw new IllegalStateException("Fail to copy table " + table, e);
} finally {
watch.stop(" " + count + " rows of " + table + " copied");
DbUtils.closeQuietly(destStatement);
DbUtils.closeQuietly(destResultSet);
DbUtils.closeQuietly(destConnection);
DbUtils.closeQuietly(sourceResultSet);
DbUtils.closeQuietly(sourceStatement);