public BulkSQLLoader_Direct(Connection conn, String schema, String table, String[] fieldNames)
throws RemoteException {
super(conn, schema, table, fieldNames);
try {
prevAutoCommit = conn.getAutoCommit();
if (prevAutoCommit) conn.setAutoCommit(false);
String quotedTable = SQLUtils.quoteSchemaTable(conn, schema, table);
String[] columns = new String[fieldNames.length];
for (int i = 0; i < fieldNames.length; i++)
columns[i] = SQLUtils.quoteSymbol(conn, fieldNames[i]);
baseQuery =
String.format(
"INSERT INTO %s(%s) VALUES ", quotedTable, StringUtils.join(",", columns));
rowQuery = "(" + StringUtils.mult(",", "?", fieldNames.length) + ")";
rowBuffer = new Vector<Object[]>(setQueryRowCount(DEFAULT_BUFFER_SIZE));
} catch (SQLException e) {
throw new RemoteException("Error initializing BulkSQLLoader_Direct", e);
}
}
public static void copyCsvToDatabase(
Connection conn, String csvPath, String sqlSchema, String sqlTable)
throws SQLException, IOException {
String query = null;
String formatted_CSV_path = csvPath.replace('\\', '/');
String dbms = conn.getMetaData().getDatabaseProductName();
Statement stmt = null;
String quotedTable = SQLUtils.quoteSchemaTable(conn, sqlSchema, sqlTable);
try {
if (dbms.equalsIgnoreCase(SQLUtils.MYSQL)) {
stmt = conn.createStatement();
// ignoring 1st line so that we don't put the column headers as the first row of data
query =
String.format(
"load data local infile '%s' into table %s fields terminated by ',' enclosed by '\"' lines terminated by '\\n' ignore 1 lines",
formatted_CSV_path, quotedTable);
stmt.executeUpdate(query);
stmt.close();
} else if (dbms.equalsIgnoreCase(SQLUtils.POSTGRESQL)) {
query = String.format("COPY %s FROM STDIN WITH CSV HEADER", quotedTable);
((PGConnection) conn).getCopyAPI().copyIn(query, new FileInputStream(formatted_CSV_path));
}
} catch (SQLException e) {
throw new SQLExceptionWithQuery(query, e);
} finally {
SQLUtils.cleanup(stmt);
}
}
