Multiple tables with QueryDSL

Question

I have three tables connected together and I am trying to pull the data from three tables using queryDSL.

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);

Data

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);

Entities

1. @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. @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;
   

   }

@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;
}

I am trying to run the below QueryDSL to get the whole data from the above tables especially since I am looking for the data from the state table with a set of records of towns.

Case-1

Projection1

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

    private String name;
    private State state;
}

Query

  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);

Result logs and generated queries

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
*** [] --- No response even though I have the data in DB.

Case-2

Projecton2

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

}

Query

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

this one creates the below logs with an exception.

     /* 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_,   <--- Here I am getting issue
            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 - 

My main goal is to use case-1 and get combined data from three entities. The complete SpringBoot project can be found in here

Can anyone help me with how can I achieve this with queryDSL?

Answer 1

First, your model is incorrect. There can be multiple states in a country, so you should rather model the Country<->State relationship with a one-to-many association:

@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 Set<State> states;
}

@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;
  @ManyToOne(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;
}

I would recommend you to look into Blaze-Persistence Entity Views for this though as that will simplify the querying.

I created the library to allow easy mapping between JPA models and custom interface or abstract class defined models, something like Spring Data Projections on steroids. The idea is that you define your target structure(domain model) the way you like and map attributes(getters) via JPQL expressions to the entity model.

A DTO model for your use case could look like the following with Blaze-Persistence Entity-Views:

@EntityView(Country.class)
public interface CountryDto {
    @IdMapping
    Long getId();
    String getName();
    Set<StateDto> getStates();

    @EntityView(State.class)
    interface StateDto {
        @IdMapping
        Long getId();
        String getName();
    }
}

Querying is a matter of applying the entity view to a query, the simplest being just a query by id.

CountryDto a = entityViewManager.find(entityManager, CountryDto.class, id);

The Spring Data integration allows you to use it almost like Spring Data Projections: https://persistence.blazebit.com/documentation/entity-view/manual/en_US/index.html#spring-data-features

Page<CountryDto> findAll(Pageable pageable);

The best part is, it will only fetch the state that is actually necessary!