/** Loads a CSV file into a table */
public void loadCSV(String table, File input, boolean clearTable, char separator)
throws IOException, SQLException {
if (clearTable) executeUpdate("DELETE FROM " + table);
ResultSet r = query(limit("SELECT * FROM " + table, 1));
int[] types = new int[r.getMetaData().getColumnCount()];
for (int i = 0; i < types.length; i++) types[i] = r.getMetaData().getColumnType(i + 1);
close(r);
Inserter bulki = newInserter(table, types);
boolean start = true;
for (List<String> values : new CSVLines(input)) {
if (start) {
if (values.size() != types.length) {
throw new SQLException(
"File "
+ input.getName()
+ " has "
+ values.size()
+ " columns, but table "
+ table
+ " has "
+ types.length);
}
start = false;
continue;
}
if (values.size() != types.length) {
Announce.warning(
"Line cannot be read from file", input.getName(), "into table", table, ":\n", values);
continue;
}
bulki.insert((Object[]) values.toArray());
}
bulki.close();
}
/** Runs a user-interface and closes */
public void runInterface() {
Announce.message("Connected to", this);
while (true) {
D.p(
"Enter an SQL query (possibly of multiple lines), followed by a blank line (or just a blank line to quit):");
StringBuilder sql = new StringBuilder();
String s;
while ((s = D.r()).length() != 0) sql.append(s).append("\n");
if (sql.length() == 0) break;
sql.setLength(sql.length() - 1);
Announce.doing("Querying database");
if (sql.length() == 0) break;
try {
ResultSet result = query(sql.toString());
Announce.done();
if (result != null) D.p(describe(result, 50));
} catch (SQLException e) {
Announce.failed();
e.printStackTrace(System.err);
Announce.message("\n\n... but don't give up, try again!");
}
}
Announce.doing("Closing database");
close();
Announce.done();
}