Accessing @JoinTable in many-to-many relationship in Springboot JPA

Question

I've created an API that has actor, movie and category entities. Actor and movie are connected by many-to-many relationship that maps to a join table called movie_actor and category is connected with movie by one-to-many relationship.

I'm trying to write a native query that returns an integer that would represent the amount of movies from a specific category where specific actor has played so for example query would return 2 if actor played in 2 different sci-fi movies. I have no problem doing that from the database level where I can see the join table movie_actor but that table remains unaccessible in my api because it's not a separate entity. How can I create it that it automatically maps actor and movie ids as the movie_actor table ?

Here is an example code that works for me in the H2 Database:

SELECT COUNT(*) FROM MOVIE M JOIN MOVIE_ACTOR MA on M.MOVIE_ID = MA.MOVIE_ID WHERE ACTOR_ID = 1 AND CATEGORY_ID = 1

Here are my entities:

Actor:

@Entity
@Data
@Table(name = "actor")
@AllArgsConstructor
@NoArgsConstructor
public class Actor {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "actor_id")
private long actorId;

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

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

@Nullable
@ManyToMany(mappedBy = "actors", fetch = FetchType.EAGER)
@JsonBackReference
private List<Movie> movies = new ArrayList<>();

public Actor(String name, String surname){
    this.name = name;
    this.surname = surname;
}
}

Movie:

@Entity
@Data
@Table(name = "movie")
@AllArgsConstructor
@NoArgsConstructor
public class Movie {

@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "movie_id")
private long movieId;

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

@ManyToMany
@JoinTable(
        name = "movie_actor",
        joinColumns = @JoinColumn(name = "movie_id"),
        inverseJoinColumns = {@JoinColumn(name = "actor_id")}
)
@JsonManagedReference
private List<Actor> actors = new ArrayList<>();

@ManyToOne
@JoinColumn(name = "CATEGORY_ID")
@JsonManagedReference
private Category category;
}

Answer 1

So you have to make it accessible in your API. One option would be to map the intersection table movie_actor to the entity MovieActor and split ManyToMany relationship between Actor and Movie to OneToMany relationship with MovieActor, like that:

@Entity
@Data
@Table(name = "movie_actor")
@AllArgsConstructor
@NoArgsConstructor
public class MovieActor {
    @EmbeddedId
    private MovieActorId productOrderId = new MovieActorId();

    @ManyToOne(cascade = CascadeType.ALL, optional = false)
    @MapsId("movieId")
    @JoinColumn(name = "product_id", nullable = false)
    private Movie movie;

    @ManyToOne(cascade = CascadeType.ALL, optional = false)
    @MapsId("actorId")
    @JoinColumn(name = "order_id", nullable = false)
    private Actor actor;

    public void addMovieActor(Movie aMovie, Actor aActor) {
        movie = aMovie;
        actor = aActor;

        aMovie.getMovieActors().add(this);
        aActor.getMovieActors().add(this);
    }
}

@Embeddable
@Getter
@Setter
public class MovieActorId  implements Serializable {
    @Column(name = "movie_id")
    private Long movieId;

    @Column(name = "actor_id")
    private Long actorId;
}

@Entity
@Data
@Table(name = "actor")
@AllArgsConstructor
@NoArgsConstructor
public class Actor {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "actor_id")
    private long actorId;

    @OneToMany(mappedBy = "actor")
    private List<MovieActor> movieActors = new ArrayList<>();
}

@Entity
@Data
@Table(name = "movie")
@AllArgsConstructor
@NoArgsConstructor
public class Movie {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "movie_id")
    private long movieId;

    @OneToMany(mappedBy = "movie")
    private List<MovieActor> movieActors = new ArrayList<>();
}

Now you can access the intersection table MovieActor inside the query. You can even add more columns to this table if you want.