如何将JdbcTemplate查询结果转换为List<ClientEntity>实现跨库数据比对

@Getter
@NoArgsConstructor
@AllArgsConstructor
@Immutable
@Entity
@Table(name = "payments")
public class ClientEntity {

    @Id
    private Long id;

    @Column(name = "created_dt")
    private LocalDate created_dt;

    @Column(name = "username")
    private String username;

    @Column(name = "name")
    private String name;

    @Column(name = "amount")
    private Integer amount;

    @Column(name = "status")
    private String status;

    @Column(name = "account")
    private String account;

    @Column(name = "external_id")
    private String external_id;

    @Column(name = "external_status")
    private String external_status;
}

@Component
public class SchedulerService {

    @Autowired
    //private final AttachmentEmail attachmentEmail;
    private final JdbcTemplate jdbc;

    private static final String QUERY = "SELECT pp.id, pp.created_dt, au.username, ss.name, pp.amount,\n" +
            "REPLACE(pp.status, 'SUCCESS', 'Success') AS status, pp.account,\n" +
            "pp.external_id, COALESCE(pp.external_status, null, 'Indefined') AS external_status\n" +
            "FROM payments AS pp\n" +
            "INNER JOIN auth_user AS au ON au.id = pp.creator_id\n" +
            "INNER JOIN services AS ss ON ss.id = pp.service_id\n" +
            "WHERE pp.created_dt::date = (CURRENT_DATE - INTERVAL '1' day)::date\n" +
            "AND ss.name = 'SomeName' AND pp.status = 'SUCCESS'";

    private static final DateTimeFormatter date_format = DateTimeFormatter.ofPattern("dd.MM.yyyy");
    private static final DateTimeFormatter time_format = DateTimeFormatter.ofPattern("HH:mm:ss");

    public SchedulerService(AttachmentEmail attachmentEmail, JdbcTemplate jdbc) {
        //this.attachmentEmail = attachmentEmail;
        this.jdbc = jdbc;
    }

    @Scheduled(fixedRate = 20000)
    public void sendMail() {
        String filename = "select.csv";
        try (FileWriter writer = new FileWriter(filename)) {

            writer.append("id|Data|Time|Initiator|Service|Amout|Payment Status|Props|Identifier|External status").append("\n");

            this.jdbc.query(QUERY, (ResultSet rs) -> writeLine(writer, rs));

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private void writeLine(FileWriter writer, ResultSet rs) {
        try {
            LocalDateTime ldt = rs.getTimestamp("created_dt").toLocalDateTime();
            writer.append(String.valueOf(rs.getLong("id")));
            writer.append('|');
            writer.append(ldt.format(date_format));
            writer.append('|');
            writer.append(ldt.format(time_format));
            writer.append('|');
            writer.append(rs.getString("username"));
            writer.append('|');
            writer.append(rs.getString("name"));
            writer.append('|');
            writer.append(String.valueOf(rs.getBigDecimal("amount")));
            writer.append('|');
            writer.append(rs.getString("status"));
            writer.append('|');
            writer.append(rs.getString("props"));
            writer.append('|');
            writer.append(rs.getString("Identifier"));
            writer.append('|');
            writer.append(rs.getString("external_status"));
            writer.append('\n');
        } catch (Exception e) {
            throw new IllegalStateException(e);
        }
    }
}

目前的业务逻辑是通过JdbcTemplate执行SQL查询payments表关联数据，生成CSV文件后通过邮件发送。为保障发送数据的准确性，需要对两个不同数据库的同逻辑查询结果进行比对。计划将两次SELECT查询的结果映射为ClientEntity对象后存储到List/ArrayList集合中，再通过Comparator实现两个集合中对象的逐一对比，暂未找到将上述JdbcTemplate查询结果转换为对应实体类List集合的实现方法，求可行的实现方案。

有两种常用的实现方式，可根据实际需求选择：

• 方案1：使用Spring自带的BeanPropertyRowMapper（最简方案）
  无需手动编写字段映射逻辑，Spring会自动将查询结果的列与实体类属性做映射，默认支持下划线命名与驼峰命名的自动转换，你的SQL返回列名与实体类属性名完全匹配，可直接使用：

  // 直接调用jdbcTemplate的query方法返回实体类集合
  List<ClientEntity> clientList = jdbc.query(QUERY, new BeanPropertyRowMapper<>(ClientEntity.class));
  

• 方案2：自定义RowMapper（稳定可控，推荐用于生产环境）
  手动实现字段映射逻辑，不受属性名、列名差异影响，也可兼容特殊的字段转换需求：

  // 定义RowMapper映射规则
  RowMapper<ClientEntity> clientRowMapper = (rs, rowNum) -> new ClientEntity(
      rs.getLong("id"),
      rs.getObject("created_dt", LocalDate.class),
      rs.getString("username"),
      rs.getString("name"),
      rs.getInt("amount"),
      rs.getString("status"),
      rs.getString("account"),
      rs.getString("external_id"),
      rs.getString("external_status")
  );
  
  // 执行查询获取集合
  List<ClientEntity> clientList = jdbc.query(QUERY, clientRowMapper);
  

双数据库比对补充说明 #

如果需要对比两个不同数据库的查询结果，分别配置两个对应不同数据源的JdbcTemplate实例，分别执行上述查询得到两个List<ClientEntity>，再按业务规则排序、逐字段比对即可。

内容的提问来源于stack exchange，提问作者Alex