Skip to content

Instantly share code, notes, and snippets.

Embed
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 https://stackoverflow.com/a/52534584/1469525 */
@Slf4j
public class ColumnRowMapper<T> extends BeanPropertyRowMapper<T> {
private ColumnRowMapper(final Class<T> mappedClass) { super(mappedClass); }
@Override
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 = annotation.name())) {
return super.underscoreName(name);
}
return StringUtils.lowerCase(columnName);
}
}
@Configuration
public class SecondaryDBConfig {
@ConfigurationProperties(prefix = "secondary-db.datasource")
@Bean
public DataSource secondaryDataSource() { return DataSourceBuilder.create().build(); }
@Bean
public NamedParameterJdbcTemplate secondaryJdbcTemplate() {
return new NamedParameterJdbcTemplate(secondaryDataSource());
}
}
@Data
public class UserInfoDto {
@Column("usr_name_sys")
private String username;
@Column("dis_first_last_name")
private String displayName;
@Column("cityInState")
private String city;
}
@Repository
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));
}
secondary-db.datasource.driver-class-name=oracle.rdb.jdbc.rdbThin.Driver
secondary-db.datasource.jdbc-url=${DB2_URL:jdbc:rdbThin://example.com:1777/RDB_JDBC_BLAH}
secondary-db.datasource.username=${DB2_USER:dev_user}
secondary-db.datasource.password=${DB2_PASS:dev_pass}
#(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