Skip to content

Instantly share code, notes, and snippets.

Last active January 25, 2021 23:36
Show Gist options
  • Save jeffsheets/e33c521df92f653b1d5339f7a08ad4a3 to your computer and use it in GitHub Desktop.
Save jeffsheets/e33c521df92f653b1d5339f7a08ad4a3 to your computer and use it in GitHub Desktop.
Use Spring JdbcTemplate to easily read data, without Spring Data JPA, and map columns using Column DTO mappings, using a secondary database connection
/** Based nearly completely on this same code from */
public class ColumnRowMapper<T> extends BeanPropertyRowMapper<T> {
private ColumnRowMapper(final Class<T> mappedClass) { super(mappedClass); }
protected String underscoreName(final String name) {
final Column annotation;
final String columnName;
Field declaredField = null;
try {
declaredField = getMappedClass().getDeclaredField(name);
catch (NoSuchFieldException | SecurityException e) {
log.warn("field «{}» not found in «{}».", name, getMappedClass());
if (declaredField == null || (annotation = declaredField.getAnnotation(Column.class)) == null
|| StringUtils.isEmpty(columnName = {
return super.underscoreName(name);
return StringUtils.lowerCase(columnName);
public class SecondaryDBConfig {
@ConfigurationProperties(prefix = "secondary-db.datasource")
public DataSource secondaryDataSource() { return DataSourceBuilder.create().build(); }
public NamedParameterJdbcTemplate secondaryJdbcTemplate() {
return new NamedParameterJdbcTemplate(secondaryDataSource());
public class UserInfoDto {
private String username;
private String displayName;
private String city;
public class SecondarySQLRepository {
private final NamedParameterJdbcTemplate secondaryJdbcTemplate;
public SecondarySQLRepository(@Qualifier("secondaryJdbcTemplate") NamedParameterJdbcTemplate secondaryJdbcTemplate) {
this.secondaryJdbcTemplate = secondaryJdbcTemplate;
public List<UserInfoDto> findUsersByCity(String state) {
String sql = "SELECT usr_name_sys, dis_first_last_name, cityInState FROM user_info WHERE state = :state;";
Map<String, Object> params = Map.of("state", state);
return secondaryJdbcTemplate.query(sql, params, new ColumnRowMapper<>(UserInfoDto.class));
#(only named zz to push to bottom of the gist list)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment