JDBC Template

1. Basic JDBC Template

public Integer getTotalCount() {
	return jdbcTemplate.queryForObject("SELECT COUNT(*) FROM EMPLOYEE", Integer.class);
}

public Integer modifyEmployeeInformation() {
	return jdbcTemplate.update("INSERT INTO EMPLOYEE VALUES (?, ?, ?, ?)", 5, "Bill", "Gates", "USA");
}

 

2. Named Parameter JDBC Template

- use preparedStatement

public SomeDto getSome(Long id) {
	String query = "SELECT * FROM Some WHERE ID = :id";
	SqlParameterSource namedParameters = new MapSqlParameterSource()
		.addValue("id", id);

	return namedJdbcTemplate.queryForObject(query, namedParameters, String.class);
}

public SomeDto getSome(Long id) {
	String query = "SELECT * FROM EMPLOYEE WHERE id = :id";
	Some some = new Some(id);
	SqlParameterSource namedParameters = new BeanPropertySqlParameterSource(some);

	return namedJdbcTemplate.queryForObject(query, namedParameters, Some.class);
}

 

3. Row Mapper

- resultSet custom mapping

public class SomeRowMapper implements RowMapper<SomeDto> {
	@Override
	public SomeDto mapRow(ResultSet rs, int rowNum) throws SQLException {
		return SomeDto.builder()
			.someId(rs.getLong("someId"))
			.name(rs.getString("someName"))
			.inventoryType(rs.getString("someType"))
			.build();
	}
}

public SomeDto getAllSomes(Long id) {
	String query = "SELECT * FROM EMPLOYEE WHERE ID = :id";
	Map<String, String> argMap = Maps.newHashMap();
	argMap.put("id", "5");

	return namedJdbcTemplate.query(query, argMap, new SomeRowMapper());
}

public List<SomeDto> findByIdBetween(Long startId, Long endId) {
	String query = "SELECT * FROM Some WHERE id > :startId and id < :endId";
	Map<String, Long> argMap = Maps.newHashMap();
	argMap.put("startId", startId);
	argMap.put("endId", endId);

	return jdbcTemplate.query(query, argMap, new BeanPropertyRowMapper<>(SomeDto.class));
}

 

4. RowCallbackHandler

- resultSet custom control

public List<SomeObject> getAllSomeObjects() {
	final List<SomeObject> someObjectList = new ArrayList<SomeObject>();
	jdbcTemplate.query("SELECT QUERY",
		new RowCallbackHandler() {
			@Override
			public void processRow(ResultSet resultSet) throws SQLException {
				SomeObject someObject = new SomeObject(rs);
				someObjectList.add(someObject);
			}
		}
	);
	return someObjectList;
}

 

5. JDBC Template Batch

public int[] batchUpdateUsingJdbcTemplate(List<Employee> employees) {
    return jdbcTemplate.batchUpdate("INSERT INTO EMPLOYEE VALUES (?, ?, ?, ?)",
        new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setInt(1, employees.get(i).getId());
                ps.setString(2, employees.get(i).getFirstName());
                ps.setString(3, employees.get(i).getLastName());
                ps.setString(4, employees.get(i).getAddress();
            }
            @Override
            public int getBatchSize() {
                return 50;
            }
        });
}

SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(employees.toArray());
int[] updateCounts = namedParameterJdbcTemplate.batchUpdate("INSERT INTO EMPLOYEE VALUES (:id, :firstName, :lastName, :address)", batch);
return updateCounts;

 

6. SQL Exception Conversion

public class CustomSQLErrorCodeTranslator extends SQLErrorCodeSQLExceptionTranslator {
    @Override
    protected DataAccessException customTranslate
      (String task, String sql, SQLException sqlException) {
        if (sqlException.getErrorCode() == -104) {
            return new DuplicateKeyException(
                "Custom Exception translator - Integrity constraint violation.", sqlException);
        }
        return null;
    }
}