Skip to content

Instantly share code, notes, and snippets.

@jeffsheets
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 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