Spring R2DBC如何结合Query by Example实现多表关联查询返回自定义DTO

ReactiveQueryByExampleExecutor是为对应数据库表的实体类设计的，你的CustomQueryResultDTO没有映射表，所以无法直接为它创建Repository接口。下面是三种可行的实现方案：

方案一：基于主实体QBE查询 + 手动关联映射DTO #

选择一个核心实体（比如Customer），用QBE查询该实体的结果，再通过实体间的关联关系（或数据库外键）关联Account和Address，最后映射到DTO。

步骤1：定义实体类及关联关系 #

假设实体间的关联是：Customer ↔ Account（一对一，外键customer_id），Account ↔ Address（一对一，外键account_id）：

@Table("customer")
public class Customer {
    @Id
    private Long id;
    private String name;
    @Column("account_id")
    private Long accountId;
    // getter/setter
}

@Table("account")
public class Account {
    @Id
    private Long id;
    private String accountNumber;
    @Column("address_id")
    private Long addressId;
    // getter/setter
}

@Table("address")
public class Address {
    @Id
    private Long id;
    private String addressCode;
    // getter/setter
}

步骤2：创建主实体的Repository #

public interface CustomerRepository extends ReactiveCrudRepository<Customer, Long>, ReactiveQueryByExampleExecutor<Customer> {
}

步骤3：Service层实现动态查询与DTO映射 #

@Service
public class QueryService {
    private final CustomerRepository customerRepo;
    private final ReactiveCrudRepository<Account, Long> accountRepo;
    private final ReactiveCrudRepository<Address, Long> addressRepo;

    public QueryService(CustomerRepository customerRepo, 
                        ReactiveCrudRepository<Account, Long> accountRepo, 
                        ReactiveCrudRepository<Address, Long> addressRepo) {
        this.customerRepo = customerRepo;
        this.accountRepo = accountRepo;
        this.addressRepo = addressRepo;
    }

    public Flux<CustomQueryResultDTO> queryWithExample(Customer example) {
        Example<Customer> customerExample = Example.of(example);
        
        return customerRepo.findAll(customerExample)
                .flatMap(customer -> accountRepo.findById(customer.getAccountId())
                        .map(account -> new Tuple2<>(customer, account)))
                .flatMap(tuple -> addressRepo.findById(tuple.getT2().getAddressId())
                        .map(address -> {
                            CustomQueryResultDTO dto = new CustomQueryResultDTO();
                            dto.setAddressCode(address.getAddressCode());
                            dto.setAccountNumber(tuple.getT2().getAccountNumber());
                            dto.setName(tuple.getT1().getName());
                            return dto;
                        }));
    }
}

方案二：QBE生成条件 + 原生SQL关联查询 #

如果关联逻辑复杂，可利用QBE生成查询条件，拼接成原生关联SQL，再通过R2dbcEntityTemplate执行并映射到DTO。

步骤1：利用Example生成查询条件 #

手动解析Example生成SQL where子句（可参考Spring Data R2DBC内部的QueryCreator逻辑扩展）：

private String buildWhereClause(Example<Customer> example) {
    StringBuilder where = new StringBuilder();
    Customer probe = example.getProbe();
    
    if (probe.getName() != null) {
        where.append("customer.name = :name");
    }
    // 按需添加其他字段的条件拼接
    return where.length() > 0 ? " WHERE " + where : "";
}

步骤2：执行原生关联查询并映射DTO #

@Service
public class QueryService {
    private final R2dbcEntityTemplate template;

    public QueryService(R2dbcEntityTemplate template) {
        this.template = template;
    }

    public Flux<CustomQueryResultDTO> queryWithExample(Customer example) {
        Example<Customer> customerExample = Example.of(example);
        String whereClause = buildWhereClause(customerExample);
        
        String sql = """
            SELECT a.address_code, acc.account_number, c.name
            FROM customer c
            JOIN account acc ON c.account_id = acc.id
            JOIN address a ON acc.address_id = a.id
            """ + whereClause;

        Bindings bindings = Bindings.empty();
        if (example.getName() != null) {
            bindings = bindings.bind("name", example.getName());
        }

        return template.getDatabaseClient()
                .sql(sql)
                .bindings(bindings)
                .map(row -> {
                    CustomQueryResultDTO dto = new CustomQueryResultDTO();
                    dto.setAddressCode(row.get("address_code", String.class));
                    dto.setAccountNumber(row.get("account_number", String.class));
                    dto.setName(row.get("name", String.class));
                    return dto;
                })
                .all();
    }
}

方案三：实体关联 + DTO投影（适合固定关联逻辑） #

如果查询的关联关系固定，仅需动态过滤主实体条件，可在实体间建立关联，用DTO作为投影类结合QBE查询。

步骤1：修改实体添加关联 #

@Table("customer")
public class Customer {
    @Id
    private Long id;
    private String name;
    @OneToOne
    @JoinColumn(name = "account_id")
    private Account account;
    // getter/setter
}

@Table("account")
public class Account {
    @Id
    private Long id;
    private String accountNumber;
    @OneToOne
    @JoinColumn(name = "address_id")
    private Address address;
    // getter/setter
}

步骤2：QBE查询并投影到DTO #

public Flux<CustomQueryResultDTO> queryWithExample(Customer example) {
    Example<Customer> customerExample = Example.of(example);
    return customerRepo.findAll(customerExample)
            .map(customer -> {
                CustomQueryResultDTO dto = new CustomQueryResultDTO();
                dto.setName(customer.getName());
                dto.setAccountNumber(customer.getAccount().getAccountNumber());
                dto.setAddressCode(customer.getAccount().getAddress().getAddressCode());
                return dto;
            });
}

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