使用JDBI的mapToBean时无法自动映射嵌套类PaCategory的问题

问题根源 #

JDBI的mapToBean方法仅支持扁平结果集到实体类的直接映射，无法自动识别并填充嵌套的PaCategory对象，因此会导致paCategory始终为null。

解决方案一：使用JoinRowMapper关联映射 #

适用于JDBI 3.x及以上版本，通过JoinRowMapper分别映射主实体和嵌套实体，再手动关联：

1. 调整SQL查询，为嵌套实体的字段添加前缀（避免字段名冲突）：

SELECT 
    c.category_id,
    c.name,
    c.pa_category_id,
    pa.pa_category_id AS pa_pa_category_id,
    pa.name AS pname 
FROM category c 
JOIN pa_category pa ON pa.pa_category_id = c.pa_category_id 
WHERE category_id=2

2. 使用JoinRowMapper和BeanMapper实现映射：

List<Category> categories = JDBIConnector.get().withHandle(handle -> {
    return handle.createQuery("SELECT c.category_id,c.name,c.pa_category_id,pa.pa_category_id AS pa_pa_category_id,pa.name AS pname FROM category c JOIN pa_category pa ON pa.pa_category_id = c.pa_category_id WHERE category_id=2")
            .map(JoinRowMapper.join(
                    BeanMapper.of(Category.class),
                    BeanMapper.of(PaCategory.class).withPrefix("pa_")
            ))
            .map(row -> {
                Category category = row.get(Category.class);
                PaCategory paCategory = row.get(PaCategory.class);
                category.setPaCategory(paCategory);
                return category;
            })
            .list();
});

解决方案二：自定义RowMapper手动映射 #

通过实现RowMapper接口，手动读取结果集字段并填充嵌套对象：

1. 自定义RowMapper类：

public class CategoryRowMapper implements RowMapper<Category> {
    @Override
    public Category map(ResultSet rs, StatementContext ctx) throws SQLException {
        Category category = new Category();
        category.setCategoryId(rs.getInt("category_id"));
        category.setPaCategoryId(rs.getInt("pa_category_id"));
        category.setName(rs.getString("name"));

        PaCategory paCategory = new PaCategory();
        paCategory.setPaCategoryId(rs.getInt("pa_category_id"));
        paCategory.setPname(rs.getString("pname"));
        category.setPaCategory(paCategory);

        return category;
    }
}

2. 查询时使用自定义Mapper：

List<Category> categories = JDBIConnector.get().withHandle(handle -> {
    return handle.createQuery("SELECT c.category_id,c.name,c.pa_category_id,pa.pa_category_id,pa.name pname FROM category c JOIN pa_category pa ON pa.pa_category_id = c.pa_category_id WHERE category_id=2")
            .map(new CategoryRowMapper())
            .list();
});

注意事项 #

• 确保SQL查询返回的字段名与实体类的属性名匹配，必要时通过别名调整
• 嵌套对象的属性必须手动赋值，JDBI默认不支持自动嵌套映射

内容的提问来源于stack exchange，提问作者Dương Thanh Phụng