如何在嵌套的SELECT查询中使用具有字段到列映射(键为ID)的BeanMapHandler

Question

我有一个嵌套的SQL查询，用于使用员工ID获取员工详细信息。

现在，我正在使用BeanListHandler以List<Details>的形式获取数据，但希望将其存储为Map<String, Details>，因为我最初传递的ID需要作为关键字，以便轻松检索，而不是每次都用流搜索List。

我尝试过转换为Maps，但我不知道如何将ID映射为String，也不知道如何在最终结果中将原始ID作为列传递给内部Query。

MainTest.java：

String candidateId = "('1111', '2222', '3333', '4444')";
String detailsQuery =
"select PARTNER, BIRTHDT, XSEXM, XSEXF from "schema"."platform.view/table2" where partner IN rn"
+ "(select SID from "schema"."platform.view/table1" where TYPE='BB' and CLASS='yy' and ID IN rn"
+ "(select SID from "schema"."platform.view/table1" where TYPE='AA' and CLASS='zz' and ID IN"
+ candidateId + "rn" + "))";
Map<String, Details> detailsView = queryRunner.query(conn, detailsQuery, new DetailsViewHandler());

Details.java:

public class Details {
private String candidateId;
private String birthDate;
private String maleSex;
private String femaleSex;
// getter and setter
}

DetailsViewHandler.java:

public class DetailsViewHandler extends BeanMapHandler<String, Details> {
public DetailsViewHandler() {
super(Details.class, new BasicRowProcessor(new BeanProcessor(getColumnsToFieldsMap())));
}
public static Map<String, String> getColumnsToFieldsMap() {
Map<String, String> columnsToFieldsMap = new HashMap<>();
columnsToFieldsMap.put("PARTNER", "candidateId");
columnsToFieldsMap.put("BIRTHDT", "birthDate");
columnsToFieldsMap.put("XSEXM", "maleSex");
columnsToFieldsMap.put("XSEXF", "femaleSex");
return columnsToFieldsMap;
}
}

有没有办法在结果中获得ID (candidateId)？在创建键值配对方面，我缺少什么？

Answer 1

从文档https://commons.apache.org/proper/commons-dbutils/apidocs/org/apache/commons/dbutils/handlers/BeanMapHandler.html您正在使用的构造函数

public BeanMapHandler(Class<V> type,
RowProcessor convert)
// Creates a new instance of BeanMapHandler. The value of the first column of each row will be a key in the Map.

以上内容应该有效。

您也可以尝试像一样覆盖createKey

protected K createKey(ResultSet rs)
throws SQLException {
return rs.getString("PARTNER"); // or getInt whatever suits
}