Created
May 26, 2013 12:13
-
-
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)
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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); | |
} | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/** For passing argument-to-result transformations around. */ | |
interface ResultExtractor<T> { | |
T extract(ResultSet rs) throws SQLException; | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/** 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