Why Hibernate is not performing the JOIN on this MANY TO MANY association table using @ManyToMany an-CodePudding

I am working on a Spring Boot application using Spring Data JPA and Hibernate mapping and I have the following problem.

I have this network table:

CREATE TABLE IF NOT EXISTS public.network
(
    id bigint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    name character varying(50) COLLATE pg_catalog."default" NOT NULL,
    description text COLLATE pg_catalog."default",
    CONSTRAINT network_pkey PRIMARY KEY (id)
)

and this chain table:

CREATE TABLE IF NOT EXISTS public.chain
(
    id bigint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    name character varying(50) COLLATE pg_catalog."default" NOT NULL,
    fk_chain_type bigint NOT NULL,
    description text COLLATE pg_catalog."default",
    CONSTRAINT chain_pkey PRIMARY KEY (id),
    CONSTRAINT "chain_to_chain_type_FK" FOREIGN KEY (fk_chain_type)
        REFERENCES public.chain_type (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        NOT VALID
)

These 2 tables are related to each other by a MANY TO MANY relationship, implmemented by this network_chain table:

CREATE TABLE IF NOT EXISTS public.network_chain
(
    id bigint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 ),
    fk_network_id bigint NOT NULL,
    fk_chain_id bigint NOT NULL,
    CONSTRAINT network_chain_pkey PRIMARY KEY (id),
    CONSTRAINT chain_id_fk FOREIGN KEY (fk_chain_id)
        REFERENCES public.chain (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        NOT VALID,
    CONSTRAINT network_id_fk FOREIGN KEY (fk_network_id)
        REFERENCES public.network (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        NOT VALID
)

Basically the fk_network_id field represents the id of a specific record into the network table while the fk_chain_id represents the id of a specific record into the chain table.

I mapped these DB tables with the following Hibernate entity classes, first of all I created this Network class mapping the network table:

@Entity
@Table(name = "network")
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@JsonIgnoreProperties({"hibernateLazyInitializer", "handler"})
public class Network implements Serializable {

    private static final long serialVersionUID = -5341425320975462596L;
    
    @Id
    @Column(name = "id")
    @GeneratedValue(strategy=GenerationType.IDENTITY)
    private Integer id;
    
    @Column(name = "name")
    private String name;
    
    @Column(name = "description")
    private String description;
    
    @ManyToMany(cascade = { CascadeType.MERGE })
    @JoinTable(
        name = "network_chain", 
        joinColumns = { @JoinColumn(name = "fk_network_id") }, 
        inverseJoinColumns = { @JoinColumn(name = "fk_chain_id") }
    )
    Set<Chain> chainList;

}

As you can see it contains the @ManyToMany annotation using the @JoinTable annotation in order to join my network table with my chain table (mapped by the Chain entity class) using the previous network_chain table (implementing the MANY TO MANY relationship).

So in this @JoinTable annotation I am specifying:

The merging table implementing the MANY TO MANY relationship: network_chain.
the two FK on this table that are fk_network_id and fk_chain_id.

Then I have this Spring Data JPA repository class named NetworkRepository:

public interface NetworkRepository extends JpaRepository<Network, Integer> {
    
    /**
     * Retrieve a Network object by its ID
     * @param id of the network
     * @return the retrieve Network object
     */
    Network findById(String id);
    
    /**
     * Retrieve a Network object by its name
     * @param name of the network
     * @return a Network object
     */
    Network findByName(String name);
    
    /**
     * Retrieve the list of all the possible networks
     * @return a List<Network> object: the list of the all the networks
     */
    List<Network> findAll();

}

Finally I created a JUnit test class containing a method in order to test the previous repository findAll() method, this one:

@SpringBootTest()
@ContextConfiguration(classes = GetUserWsApplication.class)
@TestMethodOrder(OrderAnnotation.class)
public class NetworkTest {
    
    @Autowired
    private NetworkRepository networkRepository;
    
    /**
     * Retrieve the networks list
     */
    @Test
    @Order(1)
    public void findAllTest() {
        
        List<Network> networksList = this.networkRepository.findAll();
        
        assertTrue(networksList.size() == 5, "It retrieved 5 networks");
        
        Set<Chain> networkChain = networksList.get(0).getChainList();
        
        assertTrue(networkChain != null, "The network chains list are not null");
    }

}

The problem is that the findAll() method execute this SQL statement( I can see it into my stacktrace):

Hibernate: 
    select
        network0_.id as id1_6_,
        network0_.description as descript2_6_,
        network0_.name as name3_6_ 
    from
        network network0_

and retrieve the expected List object but as you can see in the following printscreen my chainList field give an error:

It seems that it have not retrieved the chainList from my MANY TO MANY table (infact thre previous Hibernate statement seems not perform any join on the network_chain and then chain tables).

I also tried to directly access to this field by this line (my idea was that maybe Hibernate performed this join when the access to this field is explicitly performed:

Set<Chain> networkChain = networksList.get(0).getChainList();

It seems that this com.sun.jdi.InvocationException exceptions happens when I try to retrieve this field. Basically it seems that the JOINS between the MANY TO MANY table and the on the chain table is never performed.

Why? What is wrong with my code? What am I missing? How can I try to fix it?

CodePudding user response：

This is happening because you have an extra column (id) in the table (network_chain) which is responsible for the many-to-many relationship.

The @JoinTable annotation will support the definition of only 2 fields, usually the ids of the 2 tables. The 2 ids together, will be the primary key for the join table, also known as a composite primary key.

In your case, you will have to create an entity to represent the table network_chain.

@Entity
@Table(name = "network_chain")
public class NetworkChain {

   @Id
   @Column(name = "id")
   @GeneratedValue(strategy=GenerationType.IDENTITY)
   private Integer id;

   @ManyToOne
   private Network network;
   
   @ManyToOne
   private Chain chain;    
}

public class Network implements Serializable {
    //somewhere in the code
    @OneToMany
    List<NetworkChain> networkChains;
}

public class Chain implements Serializable {
    //somewhere in the code
    @OneToMany
    List<NetworkChain> networkChains;
}

Now, the entity NetworkChain has the extra column id as the primary key and hibernate will do the proper joins to fetch the data.

Out of topic but not sure why there is a javadoc for the findAll() method which is usually part of the JpaRepository and weird findById as well..