With traditional JDBC we got redundant (boilerplate) and error-prone code:
public Account findByNumber(String number ) {
String sql = "select * from accounts " +
"where number = ?";
Account account = null;
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;
try {
conn = dataSource.getConnection();
ps = conn.prepareStatement(sql);
ps.setString(1, number);
rs = ps.executeQuery();
account = mapAccount(rs);
} catch (SQLException e) {
throw new RuntimeException("SQL exception occurred", e);
} finally {
if (rs != null) {
try {
rs.close();
} catch (SQLException ex) { // OMG
}
}
if (ps != null) {
try {
ps.close();
} catch (SQLException ex) { // OMG
}
}
if (conn != null) {
try {
conn.close();
} catch (SQLException ex) { // OMG
}
}
}
return account;
}
With a try with resources we could make it easier, but not too much. Instead, with Spring JdbcTemplate we are using the Template Method Pattern, cleaning the code, eliminating repetitive boilerplate code, avoiding common bugs, and handling exceptions properly:
public Account findByNumber(String number) {
String sql = "select * from accounts where number = ?";
return jdbcTemplate.query(sql, accountExtractor, number);
}
private class AccountExtractor implements ResultSetExtractor<Account> {
public Account extractData(ResultSet rs) throws SQLException, DataAccessException {
return mapAccount(rs);
}
}
Spring acquires the connection, executes the statement, processes the result set, handles the exceptions, and releases the connection. You can query for a single or multiple rows. JdbcTemplate returns each row of a ResultSet as a Map. When expecting a single row use queryForMap, when expecting multiple rows use queryForList, when inserting, updating, or deleting use the update method:
public Map<String, Object> getUser(int id) {
String sql = "select * from users where id = ?";
return jdbcTemplate.queryForMap(sql, id);
}
public List<Map<String, Object>> getUsers() {
String sql = "select * from users";
return jdbcTemplate.queryForList(sql);
}
public int insertUpdateOrDeleteUser(String sql) {
return jdbcTemplate.update(sql);
}
Top comments (0)