JPQL query returns faulty data

Question

I have troubles with my JPQL/SQL query which returns me faulty data. Query looks like that:

public interface ReviewRepository extends JpaRepository<Review,Long> {
    @Query(value = "SELECT review,r,cr,c FROM Review review " +
            "JOIN FETCH review.rental r " +
            "JOIN FETCH r.carRentals cr " +
            "JOIN FETCH cr.car c " //+
            //"JOIN FETCH r.userRentals ur " +
            //"JOIN FETCH ur.user u"
    )
    List<Review> getFiveLatestReviewsWithUsersAndCars();

As you can see I am connecting three tables together(In the end I want to connect 5). Entities structure:

@Entity
@Table(name = "car")
public class Car {
    @Id
    @Column(name="id")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;
    @Column(name="class")
    private String type;
    @Column(name="brand")
    private String brand;
    @Column(name="fuel")
    private String fuel;
    @Column(name="engine")
    private String engine;
    @Column(name="hp")
    private int hp;
    @Column(name="model")
    private String model;
    @Column(name="sitting_places")
    private byte sittingPlaces;
    @Column(name="rent_price")
    private double price;
    @Column(name="deleted")
    private boolean deleted;
    @Column(name="rate")
    private float rate;
    @OneToOne(mappedBy = "car", fetch = FetchType.LAZY)
    private WebContent webContent;
    @OneToMany(mappedBy = "car", fetch = FetchType.LAZY)
    private Set<CarRental> carRentals;

    public Car() {
    }

    public Car(long id, String type, String brand, String fuel,
               String engine, int hp, String model, byte sittingPlaces,
               double price, boolean deleted, float rate, WebContent webContent,
               Set<CarRental> carRentals) {
        this.id = id;
        this.type = type;
        this.brand = brand;
        this.fuel = fuel;
        this.engine = engine;
        this.hp = hp;
        this.model = model;
        this.sittingPlaces = sittingPlaces;
        this.price = price;
        this.deleted = deleted;
        this.rate = rate;
        this.webContent = webContent;
        this.carRentals = carRentals;
    }

    @Override
    public String toString() {
        return  brand.substring(0,1).toUpperCase() + brand.substring(1) + ' ' +
                model.substring(0,1).toUpperCase() + model.substring(1) + " - " +
                " Engine: " + engine +
                " Fuel: " + fuel +
                " Hp:" + hp +
                " Type: " + type +
                " Sitting Places: " + sittingPlaces;
    }

//equal,getters and setters

@Entity
@Table(name = "rental_car")
public class CarRental {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Long id;
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "rental_id")
    private Rental rental;
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "car_id")
    private Car car;

    public CarRental() {
    }

    public CarRental(Long id, Rental rental, Car car) {
        this.id = id;
        this.rental = rental;
        this.car = car;
    }
//equal,getters and setters

@Entity
@Table(name = "rental")
public class Rental {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Long id;
    @Column(name = "start_date")
    private LocalDateTime startDate;
    @Column(name = "end_date")
    private LocalDateTime endDate;
    @OneToOne(mappedBy = "rental", fetch = FetchType.LAZY)
    private Review review;
    @OneToMany(mappedBy = "rental", fetch = FetchType.LAZY)
    private Set<UserRental> userRentals;
    @OneToMany(mappedBy = "car", fetch = FetchType.LAZY)
    private Set<CarRental> carRentals;

    public Rental() {
    }

    public Rental(Long id, LocalDateTime startDate, LocalDateTime endDate,
                  Review review, Set<UserRental> userRentals, Set<CarRental> carRentals) {
        this.id = id;
        this.startDate = startDate;
        this.endDate = endDate;
        this.review = review;
        this.userRentals = userRentals;
        this.carRentals = carRentals;
    }
//equal,getters and setters

@Entity
@Table(name = "review")
public class Review {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Long id;
    @Column(name = "content")
    private String content;
    @Column(name = "rate")
    private Float rate;
    @Column(name = "date")
    private LocalDateTime date;
    @OneToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "rental_id")
    private Rental rental;

    public Review() {
    }

    public Review(Long id, String content, Float rate, LocalDateTime date, Rental rental) {
        this.id = id;
        this.content = content;
        this.rate = rate;
        this.date = date;
        this.rental = rental;
    }
//equal,getters and setters

If I send it to MySQL I receive data like:

MySQL Result

When I using query at the top in the java code I receive:

Java JPQL code result

I have 6 records where 5 are exactly the same. The only thing I noticed is that Car with id=5 is rented 5 times - maybe this is the reason of exactly 5 times the same object.

If I can clarify it better let me know.

Answer 1

From JPA perspective your query:

    @Query(value = "SELECT review,r,cr,c FROM Review review " +
            "JOIN FETCH review.rental r " +
            "JOIN FETCH r.carRentals cr " +
            "JOIN FETCH cr.car c "
    )
    List<Review> getFiveLatestReviewsWithUsersAndCars();

returns quadruplet (Review,Rental,Set,Car), spring-data somehow manages that and strips off unrelated data, but you are getting duplicates, the correct query would be:

    @Query(value = "SELECT review FROM Review review " +
            "LEFT JOIN FETCH review.rental r " +
            "LEFT JOIN FETCH r.carRentals cr " +
            "LEFT JOIN FETCH cr.car c "
    )
    List<Review> getFiveLatestReviewsWithUsersAndCars();