JdbcTemplatewithGenericNestedRowMapperwithonetomanyrelationships

JdbcTemplate是Spring Framework提供的一个操作关系型数据库的工具类，可保证程序性能和数据库连接安全。一个常见的需求是在查询结果中存在一对多的关系，此时可以使用泛型嵌套行映射器(Generic Nested Row Mapper)来处理。下面是示例代码，其中Mapper类需要根据实际情况进行调整。

public class Product {
    private int id;
    private String name;
    private List<Order> orders = new ArrayList<>();

    // getters and setters
}

public class Order {
    private int id;
    private int productId;
    private String description;

    // getters and setters
}

public class ProductOrderMapper extends GenericNestedRowMapper<Product, Order> {
    public ProductOrderMapper() {
        super(new BeanPropertyRowMapper<>(Product.class), "id");
    }

    @Override
    protected void addNestObject(Product product, Order order) {
        product.getOrders().add(order);
    }

    @Override
    protected Order createNestObject(ResultSet rs) throws SQLException {
        return new Order(
            rs.getInt("order_id"),
            rs.getInt("product_id"),
            rs.getString("description")
        );
    }
}

public class ProductDao {
    private JdbcTemplate jdbcTemplate;

    public List<Product> getAllProducts() {
        String sql = "SELECT p.*, o.id AS order_id, o.description FROM product p LEFT JOIN orders o "
                   + "ON p.id = o.product_id ORDER BY p.id";
        return jdbcTemplate.query(sql, new ProductOrderMapper());
    }

    // setters
}

其中，ProductOrderMapper继承GenericNestedRowMapper，并在addNestObject和createNestObject方法中处理Product和Order的关系。在ProductDao中，使用jdbcTemplate查询并返回Product的列表，同时自动将与之关联的Order列表一同查询和映射到Product对象中，使用示例代码如下：

ProductDao productDao = new ProductDao();
List<Product> products = productDao.getAllProducts();