Skip to content

Instantly share code, notes, and snippets.

@holyjak
Created May 26, 2013 12:13
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save holyjak/5652624 to your computer and use it in GitHub Desktop.
Save holyjak/5652624 to your computer and use it in GitHub Desktop.
Wonders of Code - Straightforward vs. Structured, Non-repetitive Code: Which Would You Choose? (DB-Backed Set) - DRY & SRP (http://wp.me/p3ui6n-Q)
public class PersistentDatafileSetStructured extends AbstractSet<Datafile> {
public static enum StorageType {Hive, JDBC};
private final StatementExecutor db;
private final String tableName;
private final StorageType storageType;
private final String selectAll;
private final String countOneDatafileSql;
private final String countAllSql;
private final String insertSql;
public PersistentDatafileSetStructured(Configuration conf, String tableName, StorageType storageType) {
this.db = new StatementExecutor(conf);
this.tableName = tableName;
this.storageType = storageType;
countAllSql = "select count(*) from datafiles where "
+ "table_name = '" + tableName + "' "
+ "and storage_type = '" + storageType + "' ";
countOneDatafileSql = countAllSql
+ "and filename = ? "
+ "and timestamp = ? ";
insertSql = "insert into datafiles "
+ "(filename, timestamp, table_name, storage_type) "
+ "values (?, ?, "
+ "'" + tableName + "', "
+ "'" + storageType + "')";
selectAll = "select filename, timestamp from datafiles where "
+ "table_name = '" + tableName + "' "
+ "and storage_type = '" + storageType + "'";
}
@Override
public boolean add(Datafile e) {
return db.executeInsertStatement(insertSql, e.getFilename(), e.getTimestamp());
}
@Override
public boolean contains(Object o) {
Datafile e = (Datafile) o;
return executeSelectCount(countOneDatafileSql, e.getFilename(), e.getTimestamp()) > 0;
}
@Override
public int size() {
return executeSelectCount(countAllSql);
}
@Override
public Iterator<Datafile> iterator() {
// Extractor ResultSet => Iterator over Datafiles
ResultExtractor<Iterator<Datafile>> datafileListExtractor = new ResultExtractor<Iterator<Datafile>>() {
public Iterator<Datafile> extract(ResultSet results) throws SQLException {
List<Datafile> resultList = new ArrayList<>();
while (results.next()) {
resultList.add(new Datafile(results.getString(1), results.getLong(2)));
}
return resultList.iterator();
}
};
return db.executeSelectStatement(datafileListExtractor, selectAll);
}
private int executeSelectCount(String sql, Object... params) {
ResultExtractor<Integer> oneIntExtractor = new ResultExtractor<Integer>() {
public Integer extract(ResultSet rs) throws SQLException {
rs.next(); // count(*) always returns 1 row
return rs.getInt(1);
}
};
return db.executeSelectStatement(oneIntExtractor, sql, params);
}
}
/** For passing argument-to-result transformations around. */
interface ResultExtractor<T> {
T extract(ResultSet rs) throws SQLException;
}
/** A generic select/insert/update/create statement executor, independent of business logic. */
class StatementExecutor {
public static final String JDBC_URL = "jdbc.url";
public static final String JDBC_USERNAME = "jdbc.username";
public static final String JDBC_PASSWORD = "jdbc.password";
private final Configuration conf;
public StatementExecutor(Configuration conf) {
this.conf = conf;
}
public boolean executeInsertStatement(String sql, Object... params) {
// Inserts ignore PK violations and return false when they happen
return executeUpdateStatementOrFail(true, sql, params);
}
public boolean executeUpdateStatement(String sql, Object... params) {
// Updates should not ignore PK violations
return executeUpdateStatementOrFail(false, sql, params);
}
private boolean executeUpdateStatementOrFail(boolean ignorePkViolation, String sql, Object... params) {
try (Connection connection = createConnection();
PreparedStatement statement = connection.prepareStatement(sql)) {
for (int i = 0; i < params.length; i++) {
statement.setObject((i+1), params[i]);
}
return statement.executeUpdate() == 1;
} catch (SQLException | IOException e) {
// For inserts it is enough to return false when not inserted due to integrity constraints
if (e instanceof SQLIntegrityConstraintViolationException && ignorePkViolation) {
return false;
}
throw new RuntimeException("Error executing '" + sql + "' with " + Arrays.toString(params), e);
}
}
public <T> T executeSelectStatement(ResultExtractor<T> extractor, String sql, Object... params) {
try (Connection connection = createConnection();
PreparedStatement statement = connection.prepareStatement(sql)) {
for (int i = 0; i < params.length; i++) {
statement.setObject((i+1), params[i]);
}
return extractor.extract(statement.executeQuery());
} catch (SQLException | IOException e) {
throw new RuntimeException("Error executing " + sql + " with " + Arrays.toString(params), e);
}
}
private Connection createConnection() throws IOException {
try {
return DriverManager.getConnection(conf.get(JDBC_URL),
conf.get(JDBC_USERNAME, ""),
conf.get(JDBC_PASSWORD, ""));
} catch (Exception e) {
throw new IOException("Exception creating connection to "
+ conf.get(JDBC_URL), e);
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment