能否通过扩展让Spring Data JDBC默认仓库生成JOIN查询？

Spring Data JDBC的默认Repository方法（比如findAllById）不会自动生成JOIN查询——这是它基于"聚合根"模式的设计决定，默认会对关联实体执行N+1查询。但要实现你需要的LEFT JOIN查询，有几种可行方案：

1. 直接使用@Query自定义查询 #

这是最直接的方式，在Repository接口中通过原生SQL定义JOIN查询：

public interface RootRepository extends CrudRepository<Root, Long> {

    @Query("SELECT r.id, r.name, c.id, c.rootId, c.element FROM Root AS r LEFT JOIN Child AS c ON r.id = c.root_id WHERE r.id = :id")
    List<Object[]> findRootWithChildrenById(@Param("id") Long id);
}

这种方式返回的是Object[]，需要手动将结果映射为Root和Child对象。如果想直接返回Root实体，可以结合自定义RowMapper处理结果映射：

@Query(value = "SELECT r.id, r.name, c.id, c.rootId, c.element FROM Root AS r LEFT JOIN Child AS c ON r.id = c.root_id WHERE r.id = :id",
       rowMapper = RootWithChildrenRowMapper.class)
Root findRootWithChildrenById(@Param("id") Long id);

实现RowMapper：

public class RootWithChildrenRowMapper implements RowMapper<Root> {

    @Override
    public Root mapRow(ResultSet rs, int rowNum) throws SQLException {
        Root root = new Root();
        root.setId(rs.getLong("id"));
        root.setName(rs.getString("name"));

        Set<Child> children = new HashSet<>();
        do {
            Child child = new Child();
            child.setId(rs.getLong("c.id"));
            child.setRootId(rs.getLong("c.rootId"));
            child.setElement(rs.getString("c.element"));
            children.add(child);
        } while (rs.next());

        root.setList(children);
        return root;
    }
}

2. 自定义Repository实现类 #

如果需要在多个地方复用JOIN查询逻辑，可以自定义Repository的实现：

步骤1：定义扩展接口 #

public interface RootRepositoryCustom {
    Root findRootWithChildren(Long id);
}

步骤2：实现扩展接口 #

使用JdbcTemplate执行查询并手动映射结果：

public class RootRepositoryImpl implements RootRepositoryCustom {

    private final JdbcTemplate jdbcTemplate;

    public RootRepositoryImpl(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    @Override
    public Root findRootWithChildren(Long id) {
        String sql = "SELECT r.id, r.name, c.id, c.rootId, c.element FROM Root AS r LEFT JOIN Child AS c ON r.id = c.root_id WHERE r.id = ?";
        return jdbcTemplate.query(sql, new Object[]{id}, rs -> {
            Root root = null;
            Set<Child> children = new HashSet<>();
            while (rs.next()) {
                if (root == null) {
                    root = new Root();
                    root.setId(rs.getLong("r.id"));
                    root.setName(rs.getString("r.name"));
                }
                Child child = new Child();
                child.setId(rs.getLong("c.id"));
                child.setRootId(rs.getLong("c.rootId"));
                child.setElement(rs.getString("c.element"));
                children.add(child);
            }
            if (root != null) {
                root.setList(children);
            }
            return root;
        });
    }
}

步骤3：让主Repository继承扩展接口 #

public interface RootRepository extends CrudRepository<Root, Long>, RootRepositoryCustom {
}

之后就可以通过rootRepository.findRootWithChildren(id)获取JOIN查询的结果。

3. 使用投影（Projection）简化结果映射 #

如果不需要完整的Root实体，只是需要部分字段，可以定义投影接口，Spring Data JDBC会自动映射结果：

public interface RootWithChildrenProjection {
    Long getId();
    String getName();
    List<ChildProjection> getChildren();

    interface ChildProjection {
        Long getId();
        String getElement();
    }
}

然后在Repository中定义查询方法：

public interface RootRepository extends CrudRepository<Root, Long> {
    @Query("SELECT r.id, r.name, c.id AS 'children.id', c.element AS 'children.element' FROM Root AS r LEFT JOIN Child AS c ON r.id = c.root_id WHERE r.id = :id")
    RootWithChildrenProjection findProjectionById(@Param("id") Long id);
}

需要注意的是，Spring Data JDBC的默认拆分查询行为是为了维护聚合根的事务边界和数据一致性。如果你的场景确实需要JOIN查询，上述方案都能满足需求。

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