Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
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