使用Spring RowMapper从JDBC填充类型集合的字段

Question

我试图使用Spring ResultSet将JDBC映射到POJO，如下所示：

public class AdvertisementMapper implements RowMapper<Advertisement> {

    @Override
    public Advertisement mapRow(ResultSet rs, int rowNum) throws SQLException {
        Advertisement advertisement = new Advertisement();
        advertisement.setId(rs.getLong("id"));
        advertisement.setAdvertisementType(AdvertisementType.valueOf(rs.getString("advertisement_type")));
        advertisement.setNeed(Need.valueOf(rs.getString("need")));
        advertisement.setChildcareWorkerType(ChildcareWorkerType.valueOf(rs.getString("childcare_worker_type")));
        advertisement.setChildcareTypes(null);//COLLECTION!
        advertisement.setDayToTimeSlots(null);//COLLECTION!
        advertisement.setLanguages(null);//COLLECTION!
        advertisement.setDescription(rs.getString("description"));
        advertisement.setCreationDate(rs.getDate("creation_date"));
        advertisement.setExpirationDate(rs.getDate("expiration_date"));
        advertisement.setActive(rs.getBoolean("active"));
        advertisement.setValidated(rs.getBoolean("validated"));
        Member member = new Member();
        member.setId(rs.getLong("memberId"));
        advertisement.setMember(member);
        Address address = new Address();
        address.setLat(rs.getDouble("addressLat"));
        address.setLon(rs.getDouble("addressLon"));
        advertisement.setAddress(address);
        advertisement.setFirstAidTraining(Choice.valueOf(rs.getString("first_aid_training")));
        advertisement.setGiveBath(Choice.valueOf(rs.getString("give_bath")));
        advertisement.setPrepareMeals(Choice.valueOf(rs.getString("prepare_meals")));
        advertisement.setHelpWithHomework(Choice.valueOf(rs.getString("help_with_homework")));
        advertisement.setCleaningIroning(Choice.valueOf(rs.getString("cleaning_ironing")));
        advertisement.setVersion(rs.getInt("version"));
        return advertisement;
    }
}

我对三个类型的字段有问题：java.util.Set (我在上面的java代码中向它们添加了一个//COLLECTION!注释)。

下面是SQL：

SELECT * FROM 
advertisement a, 
advertisement_childcare_types act, 
advertisement_day_to_time_slots adtts, 
day_to_time_slot dtts,
advertisement_languages al,
language l
WHERE 
a.id = act.advertisement AND
a.id = adtts.advertisement AND
adtts.day_to_time_slots = dtts.id AND
a.id = al.advertisement AND
al.languages = l.id

我不知道如何从java.util.Set中填充集合/ResultSet字段。

有人能帮忙吗？

Answer 1

由于您没有从结果集中的行到Advertisement实例的一对一映射，所以您将无法使用RowMapper来完成这一任务。您需要使用RowCallbackHandler或ResultSetExtractor<List<Advertisement>>。RowCallbackHandler的优点是您不需要处理调用next()的问题。

下面是一个非线程安全，不是超级高效，而是明确的实现：

class AdvertisementRowcallbackHandler implements RowCallbackHandler {

  Map<Long, Advertisement> results = new HashMap<>();

  @Override
  void processRow(ResultSet rs) SQLException {
    long id = rs.getLong("id");
    Advertisement advertisement = new Advertisement();
    advertisement.setChildcareWorkerTypes(new HashSet<>());
    results.putIfAbsent(id, advertisement);
    advertisement = results.get(id);
    // You could be smart about only setting these if it was absent in the map
    advertisement.setId(id);
    advertisement.setDescription(rs.getString("description"));
    advertisement.getChildcareWorkerTypes().add(rs.getString("worker_type"));
    // ...
  }
}

然后您将得到new ArrayList<>(handler.results.values())的结果。