Using session.get() API returning incorrect results

Hibernate ORM
1

I have a bi-directional many to many assocation. Movies ↔ MovieEmployees ↔ Employee

@Entity
@Table(name = "movies")
public class Movie {
	@EmbeddedId
	private MoviePrimaryKey moviePrimaryKey;
	
	@OneToMany(
			mappedBy = "movie",
			orphanRemoval = true,
			fetch = FetchType.EAGER		
		)
	List<MovieEmployee> employees = new ArrayList<>();	

@Entity
@Table(name = "movies_employees")
public class MovieEmployee {
	@Id
	@ManyToOne(fetch = FetchType.LAZY)
	private Movie movie;
	@Id
	@ManyToOne(fetch = FetchType.EAGER)
	private Employee employee;
	@Column(name = "job_position")
	private String jobPosition;
	@Column
	private String role;
	
	public MovieEmployee() {

@Entity
@Table(name = "employees")
public class Employee {
	@EmbeddedId
	private EmployeePrimaryKey employeePrimaryKey;
	@Column(name = "birth_date")
	private LocalDate birthDate;
	@Column(name = "country_of_origin")
	private String countryOfOrigin;
	private String synopsis;
	@Column(name = "popularity_ranking")
	private Integer popularityRanking;
	@Column(name = "img_location")
	private String imgLocation;
	
	@OneToMany(
			mappedBy = "employee",
			orphanRemoval = true,
			fetch = FetchType.LAZY
		)
	List<MovieEmployee> movieEmployees = new ArrayList<>();

Using the .get API

		Session session = sessionFactory.openSession();
		Transaction transaction = session.beginTransaction();
		Movie movie = session.get(Movie.class, moviePrimaryKey);
		session.close();

The returned movie will contain duplicate MovieEmployee objects in the employees field in the Movie entity. I’ve turned on SQL statements used and verified that statement against the database and it does NOT return any duplicates. Any idea why I would get duplicate values? The sql statement is as follows:

Hibernate: 
    select
        m1_0.original_title,
        m1_0.release_date,
        m1_0.alternative_title,
        m1_0.box_office,
        m1_0.budget,
        m1_0.hook,
        m1_0.img_location,
        m1_0.motion_picture_rating,
        m1_0.motion_picture_rating_desc,
        m1_0.running_time,
        m1_0.score,
        m1_0.synopsis,
        m1_0.themes,
        e1_0.movie_original_title,
        e1_0.movie_release_date,
        e1_0.employee_identical_name_id,
        e1_0.employee_name,
        e2_0.identical_name_id,
        e2_0.name,
        e2_0.birth_date,
        e2_0.country_of_origin,
        e2_0.img_location,
        e2_0.popularity_ranking,
        e2_0.synopsis,
        e1_0.job_position,
        e1_0.role 
    from
        movies m1_0 
    left join
        movies_employees e1_0 
            on m1_0.original_title=e1_0.movie_original_title 
            and m1_0.release_date=e1_0.movie_release_date 
    left join
        employees e2_0 
            on e2_0.identical_name_id=e1_0.employee_identical_name_id 
            and e2_0.name=e1_0.employee_name 
    where
        (
            m1_0.original_title, m1_0.release_date
        ) in ((?, ?))
2

Are you sure you updated Hibernate to the latest version 6.6.4? If so, and you still have the problem, please create an issue in our issue tracker with a test case template that reproduces the issue.

3

Before I do that quick question - does the join table’s (in this case MovieEmployee) primary key need to be a unique identifier? In this case the pk for the two duplicate rows are identical. The non primary key columns is what differentiates them.

4

Of course, the columns for the fields annotated with @Id must form a unique tuple i.e. as far as I understand your model, you need primary key (movie_original_title, movie_release_date)

5

Ok so I was able to fix my issue but I don’t really understand 100%. My junction table in my database didn’t have a primary key constraint, and my JPA annotated class, as you mentioned has its’ key as (movie_original_title, movie_release_date). I have two rows in my junction table that have the same primary key. When I run the sql query that hibernate runs, I do see all the correctly returned rows, but it appears that when hibernate converts those returned rows it is unable to resolve the two rows with identical primary keys. Any idea why that is?

The solution for me was adding a primary key constraint to the junction table in my database, and adding the additional columns or “role” and “jobPosition” with @Id annotations so that from the perspective of the entities each entity in the junction table will have unique primary keys.

6

There is nothing to “convert”, Hibernate ORM simply treats every row that is returned as an element for a collection fetch. Since you’re using a list, you get duplicates. If you don’t want duplicates because the join condition is based on a unique key (which it should), then you should use a Set instead.

7

Let me clarify before my “fix” that the list had a row completely missing, and had instead replaced this missing row with a duplicate row. This row that was duplicated had the same pk as the one that was missing (i.e. the junction table did not enforce uniqueness for the pk)