Hibernate: Join table on a.fk_id = b.id-CodePudding

I'm working on a small project involving the game Re-Volt (1999), I'm trying to make a web page where you can see all the cars with their details (image, engine type, rating, speed etc.). I have a table cars where I the previous mentioned information and it looks like this:

CREATE TABLE public.cars (
    id integer NOT NULL,
    name character varying(25),
    thumbnail_id integer,
    engine_id integer,
    rating_id integer,
    speed integer,
    acc numeric,
    mass numeric
);

I am using Hibernate with Spring Boot, PostgreSQL for the database and Thymeleaf to display the data in a web page. I managed to use Hibernate to pull the data from cars and display it, all good, but now I want to join cars with table thumbnails on cars.thumbnail_id = thumbnails.id and display the column image from table thumbnails instead of thumbnails_id. This is what my thumbnails table looks like:

CREATE TABLE public.thumbnails (
    id integer NOT NULL,
    image character varying(50)
);

And these are my entities:

// Car.java
@Setter
@Getter
@AllArgsConstructor
@NoArgsConstructor
@Entity
@Table(name = "cars")
public class Car {

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

    @OneToOne
    @JoinColumn(name = "id")
    private Thumbnail thumbnail;

    @Column(name = "engine_id")
    private Integer engine_id;

    @Column(name = "rating_id")
    private Integer rating_id;

    @Column(name = "speed")
    private Integer speed;
    @Column(name = "acc")
    private Double acc;
    @Column(name = "mass")
    private Double mass;
}

// Thumbnail.java
@Setter
@Getter
@AllArgsConstructor
@NoArgsConstructor
@Entity
@Table(name = "thumbnails")
public class Thumbnail {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "id")
    private Long id;
    @Column(name = "image")
    private String image;
}

What I don't know how to do is properly code that join in. With my current code Hibernate matches the rows without considering the value of thumbnail_id, it simply does "car_1 = thumbnail_1" even if "car_1" has "thumbnail_id" equal to 12, it still matches it to the first thumbnail and not to the 12th. Can anyone help me out?

Edit: Basically, what I'm trying to achieve through Hibernate is the following SQL query:

SELECT c.name, t.image, c.engine_id, c.rating_id, c.speed, c.acc, c.mass
FROM cars c
JOIN thumbnails t
ON c.thumbnails_id = t.id;

CodePudding user response：

Cars Entity

@Entity
@Table(name = "cars")
public class Car {

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

    @JoinColumn(name = "thumbnail_id", referencedColumnName="id")
    @OneToOne(cascade = CascadeType.ALL)
    @Fetch(FetchMode.JOIN)
    private Thumbnail thumbnail;

    @Column(name = "engine_id")
    private Integer engine_id;

    @Column(name = "rating_id")
    private Integer rating_id;

    @Column(name = "speed")
    private Integer speed;
    @Column(name = "acc")
    private Double acc;
    @Column(name = "mass")
    private Double mass;

Thumbnail Entity

@Entity
@Table(name = "thumbnails")
public class Thumbnail {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    @Column(name = "id")
    private Long id;
    @Column(name = "image")
    private String image;

Since you want to achieve the select with join and in case of one to one association N 1 problem would not allow you to do so, you need to modify your repository as below to add EntityGraph

import java.util.List;

import org.springframework.data.jpa.repository.EntityGraph;
import org.springframework.data.jpa.repository.JpaRepository;

import com.mahesh.tt.model.Car;

public interface TutorialRepository2 extends JpaRepository<Car, Long> {
    
     @Override
        @EntityGraph(attributePaths = {"thumbnail"})
        List<Car> findAll();
}

This will make sure that your thumbnail data is fetched with join and not with multiple select statements.

You can see the query generated in the console

Hibernate:

 select
            car0_.id as id1_0_0_,
            thumbnail1_.id as id1_1_1_,
            car0_.acc as acc2_0_0_,
            car0_.engine_id as engine_i3_0_0_,
            car0_.mass as mass4_0_0_,
            car0_.name as name5_0_0_,
            car0_.rating_id as rating_i6_0_0_,
            car0_.speed as speed7_0_0_,
            car0_.thumbnail_id as thumbnai8_0_0_,
            thumbnail1_.image as image2_1_1_ 
        from
            cars car0_ 
        left outer join
            thumbnails thumbnail1_ 
                on car0_.thumbnail_id=thumbnail1_.id

You can see the result in API response.

You can see the sample data in the database

Cars -

Thumbnail -

CodePudding user response：

I managed to make it work thanks to your comments. What I did was mention thumbnail_id in the @JoinColumn annotation (thanks to @blau comment) and add @MapsId("id") (thanks to @Mahesh comment). So this is what I have in my Car.java class:

// Car.java
@Setter
@Getter
@AllArgsConstructor
@NoArgsConstructor
@Entity
@Table(name = "cars")
public class Car {

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

    @OneToOne
    @JoinColumn(name = "thumbnail_id") // I changed this
    @MapsId("id") // and added this
    private Thumbnail thumbnail;

    @Column(name = "engine_id")
    private Integer engine;

    @Column(name = "rating_id")
    private Integer rating;

    @Column(name = "speed")
    private Integer speed;
    @Column(name = "acc")
    private Double acc;
    @Column(name = "mass")
    private Double mass;
}