private void runUPDATE(Connection c, String sql) throws SQLException {
logger.stdout("Running update: " + sql);
try (PreparedStatement st = c.prepareStatement(sql)) {
st.executeUpdate();
}
}
private void runSELECT(Connection c, String sql) throws SQLException {
logger.stdout("Running select: " + sql);
try (PreparedStatement st = c.prepareStatement(sql)) {
try (ResultSet rs = st.executeQuery()) {
ResultSetMetaData md = rs.getMetaData();
while (rs.next()) {
StringBuffer line = new StringBuffer();
for (int i = 1; i <= md.getColumnCount(); i++) {
if (i > 1) {
line.append(",");
}
line.append(rs.getObject(i));
}
logger.stdout(line.toString());
}
}
}
}
private void prepareDB(Connection c) throws SQLException {
try {
runUPDATE(c, "CREATE TABLE T1 (ID INT PRIMARY KEY, NAME VARCHAR(200))");
} catch (SQLException ex) {
// see http://db.apache.org/derby/docs/10.8/ref/rrefexcept71493.html
if ("X0Y32".equals(ex.getSQLState())) {
logger.stdout("Table already exists...");
} else {
throw ex;
}
}
runUPDATE(c, "DELETE FROM T1");
runUPDATE(c, "INSERT INTO T1 VALUES (1, 'aa')");
runUPDATE(c, "INSERT INTO T1 VALUES (2, 'bb')");
c.commit();
}
@Override
public CommandOutcome run(Cli cli) {
DataSource dataSource = dataSourceFactoryProvider.get().forName("test1");
try (Connection c = dataSource.getConnection()) {
prepareDB(c);
for (String sql : cli.optionStrings("sql")) {
runSELECT(c, sql);
}
} catch (SQLException ex) {
logger.stderr("Error....", ex);
}
return CommandOutcome.succeeded();
}