private static void checkIfTableExists(final Connection connection, final DatabaseTable table)
throws SQLException {
final StringBuilder sb = new StringBuilder();
sb.append("SELECT * FROM ").append(table.toString()).append(" WHERE " + KEY_COLUMN + " = '0'");
Statement statement = null;
ResultSet resultSet = null;
try {
statement = connection.createStatement();
resultSet = statement.executeQuery(sb.toString());
} finally {
close(statement);
close(resultSet);
}
}
private static void close(final Statement statement) {
if (statement != null) {
try {
statement.close();
} catch (SQLException e) {
LOGGER.error("unexpected error during close statement object " + e.getMessage(), e);
}
}
}
private static void initTable(
final Connection connection, final DatabaseTable table, final DBConfiguration dbConfiguration)
throws DatabaseException {
try {
checkIfTableExists(connection, table);
LOGGER.trace("table " + table + " appears to exist");
} catch (SQLException e) { // assume error was due to table missing;
{
final StringBuilder sqlString = new StringBuilder();
sqlString.append("CREATE table ").append(table.toString()).append(" (").append("\n");
sqlString
.append(" " + KEY_COLUMN + " ")
.append(dbConfiguration.getColumnTypeKey())
.append("(")
.append(KEY_COLUMN_LENGTH)
.append(") NOT NULL PRIMARY KEY,")
.append("\n");
sqlString
.append(" " + VALUE_COLUMN + " ")
.append(dbConfiguration.getColumnTypeValue())
.append(" ");
sqlString.append("\n");
sqlString.append(")").append("\n");
LOGGER.trace(
"attempting to execute the following sql statement:\n " + sqlString.toString());
Statement statement = null;
try {
statement = connection.createStatement();
statement.execute(sqlString.toString());
LOGGER.debug("created table " + table.toString());
} catch (SQLException ex) {
LOGGER.error("error creating new table " + table.toString() + ": " + ex.getMessage());
} finally {
close(statement);
}
}
{
final String indexName = table.toString() + "_IDX";
final StringBuilder sqlString = new StringBuilder();
sqlString.append("CREATE index ").append(indexName);
sqlString.append(" ON ").append(table.toString());
sqlString.append(" (").append(KEY_COLUMN).append(")");
Statement statement = null;
LOGGER.trace(
"attempting to execute the following sql statement:\n " + sqlString.toString());
try {
statement = connection.createStatement();
statement.execute(sqlString.toString());
LOGGER.debug("created index " + indexName);
} catch (SQLException ex) {
LOGGER.error("error creating new index " + indexName + ": " + ex.getMessage());
} finally {
close(statement);
}
}
}
}