使用QueryDSL关联查询三张表数据遇问题，求解决方案

问题描述 #

我有三张关联的数据库表，想要用QueryDSL查询这三张表的组合数据，核心需求是获取包含对应Town记录集合的State表数据。

数据库表结构 #

DROP TABLE IF EXISTS countries;

CREATE TABLE countries(id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100), data VARCHAR(100));

DROP TABLE IF EXISTS states;

CREATE TABLE states(id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100), count VARCHAR(100), co_id BIGINT );

DROP TABLE IF EXISTS towns;

CREATE TABLE towns(town_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100), people_count VARCHAR(100), st_id BIGINT);

测试数据 #

INSERT INTO countries (id, name, data) VALUES (1, 'USA', 'wwf');
INSERT INTO countries (id, name, data) VALUES (2, 'France', 'football');
INSERT INTO countries (id, name, data) VALUES (3, 'Brazil', 'rugby');
INSERT INTO countries (id, name, data) VALUES (4, 'Italy', 'pizza');
INSERT INTO countries (id, name, data) VALUES (5, 'Canada', 'snow');

INSERT INTO states (id, name, count, co_id) VALUES (1, 'arizona', '1000', 1);
INSERT INTO states (id, name, count, co_id) VALUES (2, 'texas', '400', 4);
INSERT INTO states (id, name, count, co_id) VALUES (3, 'ottwa', '3000', 1);
INSERT INTO states (id, name, count, co_id) VALUES (4, 'paulo', '222', 3);
INSERT INTO states (id, name, count, co_id) VALUES (5, 'paris', '544', 1);

INSERT INTO towns (town_id, name, people_count, st_id) VALUES (1, 'arizona', '1000', 1);
INSERT INTO towns (town_id, name, people_count, st_id) VALUES (2, 'texas', '400', 2);
INSERT INTO towns (town_id, name, people_count, st_id) VALUES (3, 'fff', '3000', 1);
INSERT INTO towns (town_id, name, people_count, st_id) VALUES (4, 'fsdd', '222', 3);
INSERT INTO towns (town_id, name, people_count, st_id) VALUES (5, 'fsfdds', '544', 3);

JPA实体类 #

1. Country实体 #

@Entity
@Table(name = "countries")
@Setter
@Getter
public class Country {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Long countryId;

    @Column(name = "name")
    private String name;

    @Column(name = "data")
    private String data;

    @OneToOne(mappedBy = "country")
    private State stateJoin;

}

2. State实体 #

@Entity
@Table(name = "states")
@Setter
@Getter
public class State {

  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  @Column(name = "id")
  private Long stateId;

  @Column(name = "name")
  private String name;

  @Column(name = "count")
  private String count;

  @Column(name = "co_id")
  private Long countryId;

  @OneToOne(cascade = CascadeType.ALL)
  @JoinColumn(name = "co_id", referencedColumnName = "id", updatable = false, insertable = false)
  private Country country;

  @OneToMany(cascade = CascadeType.ALL)
  @JoinColumn(name = "state_id", referencedColumnName = "id")
  private Set<Town> towns;
}

3. Town实体 #

@Entity
@Table(name = "towns")
@Setter
@Getter
public class Town {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "town_id")
    private Long townId;

    @Column(name = "name")
    private String name;

    @Column(name = "people_count")
    private String peopleCount;

    @Column(name = "st_id")
    private Long stateId;
}

失败的查询尝试 #

案例1 #

投影类SecondDto

@Component
@Data
@AllArgsConstructor
@NoArgsConstructor
public class SecondDto {

    private String name;
    private State state;
}

查询代码

QCountry country = QCountry.country;
QState state = QState.state;
JPAQuery query = new JPAQuery(entityManager);

List<SecondDto> result1 = query
        .select(Projections.constructor(SecondDto.class, country.name, country.stateJoin))
        .from(country)
        .join(country.stateJoin, QState.state)
        .join(state.towns, QTown.town)
        .fetch();

System.out.println("*** "+ result1);

结果与SQL

Hibernate: 
    /* select
        country.name,
        country.stateJoin 
    from
        Country country   
    inner join
        country.stateJoin as state   
    inner join
        state.towns as town */ select
            country0_.name as col_0_0_,
            country0_.id as col_1_0_,
            state1_.id as id1_1_,
            state1_.count as count2_1_,
            state1_.co_id as co_id3_1_,
            state1_.name as name4_1_ 
        from
            countries country0_ 
        inner join
            states state1_ 
                on country0_.id=state1_.co_id 
        inner join
            towns towns2_ 
                on state1_.id=towns2_.state_id
*** [] --- 数据库中有数据但查询无结果。

案例2 #

投影类Dto

@Component
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Dto {
    private String name;
    private Set<Town> towns;

}

查询代码

List<Dto> result = query
        .select(Projections.constructor(Dto.class, state.name, state.towns))
        .from(state)
        .join(state.towns, QTown.town)
        .fetch();

结果与异常

/* select
        state.name,
        state.towns 
    from
        State state   
    inner join
        state.towns as town */ select
            state0_.name as col_0_0_,
            . as col_1_0_,   <--- 此处出现问题
            towns2_.town_id as town_id1_2_,
            towns2_.name as name2_2_,
            towns2_.people_count as people_c3_2_,
            towns2_.st_id as st_id4_2_ 
        from
            states state0_ 
        inner join
            towns towns1_ 
                on state0_.id=towns1_.state_id 
        inner join
            towns towns2_ 
                on state0_.id=towns2_.state_id
07-01-2023 14:33:02 [restartedMain] WARN  org.hibernate.engine.jdbc.spi.SqlExceptionHelper.logExceptions - SQL Error: 42001, SQLState: 42001
07-01-2023 14:33:02 [restartedMain] ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper.logExceptions - Syntax error in SQL statement "/* select state.name, state.towns\000afrom State state\000a  inner join state.towns as town */ select state0_.name as col_0_0_, [*]. as col_1_0_, towns2_.town_id as town_id1_2_, towns2_.name as name2_2_, towns2_.people_count as people_c3_2_, towns2_.st_id as st_id4_2_ from states state0_ inner join towns towns1_ on state0_.id=towns1_.state_id inner join towns towns2_ on state0_.id=towns2_.state_id"; expected "*, INTERSECTS (, NOT, EXISTS, UNIQUE, INTERSECTS"; SQL statement:
/* select state.name, state.towns
from State state
  inner join state.towns as town */ select state0_.name as col_0_0_, . as col_1_0_, towns2_.town_id as town_id1_2_, towns2_.name as name2_2_, towns2_.people_count as people_c3_2_, towns2_.st_id as st_id4_2_ from states state0_ inner join towns towns1_ on state0_.id=towns1_.state_id inner join towns towns2_ on state0_.id=towns2_.state_id [42001-214]
07-01-2023 14:33:02 [restartedMain] INFO  org.springframework.boot.autoconfigure.logging.ConditionEvaluationReportLoggingListener.logMessage - 

解决方案 #

第一步：修复实体类关联错误 #

现有实体的关联关系与数据库实际结构不匹配，先修正：

1. Country与State的关联修正：数据库中一个Country对应多个State，将@OneToOne改为@OneToMany：

@Entity
@Table(name = "countries")
@Setter
@Getter
public class Country {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Long countryId;

    @Column(name = "name")
    private String name;

    @Column(name = "data")
    private String data;

    // 修正为一对多关联
    @OneToMany(mappedBy = "country")
    private List<State> states;
}

2. State与Town的关联字段修正：State中@OneToMany的外键应匹配数据库的st_id，而非state_id：

@Entity
@Table(name = "states")
@Setter
@Getter
public class State {

  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  @Column(name = "id")
  private Long stateId;

  @Column(name = "name")
  private String name;

  @Column(name = "count")
  private String count;

  @Column(name = "co_id")
  private Long countryId;

  @OneToOne(cascade = CascadeType.ALL)
  @JoinColumn(name = "co_id", referencedColumnName = "id", updatable = false, insertable = false)
  private Country country;

  // 修正外键字段为st_id
  @OneToMany(cascade = CascadeType.ALL)
  @JoinColumn(name = "st_id", referencedColumnName = "id")
  private Set<Town> towns;
}

第二步：正确的QueryDSL查询实现 #

方案1：直接查询State实体（自动加载关联Town）

如果不需要自定义DTO，直接查询State并通过fetchJoin强制加载关联的Town集合：

QState state = QState.state;
QTown town = QTown.town;

List<State> states = new JPAQuery<>(entityManager)
        .select(state)
        .from(state)
        .leftJoin(state.towns, town).fetchJoin() // 强制加载关联的towns集合
        .leftJoin(state.country).fetchJoin() // 如需加载关联的Country可添加
        .fetch();

方案2：投影到自定义DTO（包含Country名称、State及Town集合）

针对原需求的SecondDto，修正DTO后编写查询：

// 修正后的SecondDto
@Data
@AllArgsConstructor
@NoArgsConstructor
public class SecondDto {
    private String countryName;
    private State state;
}

// 查询代码
QCountry country = QCountry.country;
QState state = QState.state;
QTown town = QTown.town;

List<SecondDto> result = new JPAQuery<>(entityManager)
        .select(Projections.constructor(SecondDto.class, country.name, state))
        .from(country)
        .join(country.states, state) // 使用修正后的一对多关联
        .leftJoin(state.towns, town).fetchJoin() // 强制加载Town集合
        .fetch();

方案3：投影到包含Town集合的DTO

针对原案例2的Dto，由于QueryDSL无法直接将集合作为构造参数生成有效SQL，可先查询State再手动映射：

QState state = QState.state;
QTown town = QTown.town;

List<Dto> dtoList = new JPAQuery<>(entityManager)
        .select(state)
        .from(state)
        .leftJoin(state.towns, town).fetchJoin()
        .fetch()
        .stream()
        .map(s -> new Dto(s.getName(), s.getTowns()))
        .collect(Collectors.toList());

错误原因分析 #

1. 案例1无结果：原实体中Country与State的@OneToOne关联与数据库一对多结构不匹配，导致关联查询无法匹配数据；同时内连接会过滤掉无Town的State，但核心问题是关联关系错误。
2. 案例2SQL语法错误：SQL本身不支持直接返回集合类型，QueryDSL无法将state.towns直接作为构造参数生成有效SQL，必须通过fetchJoin加载关联集合后再映射到DTO。

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