Is it possible specify to set an id auto increment not null primary key into a @ManyToMany relations-CodePudding

I am working on a Spring Boot application using Spring Data JPA and Hibernate to mapping preexisting database tables. I am finding some difficulties with a @ManyToMany relationship because the related relationship table has a bigint autoincrement NOT NULL PK.

Basically this is the relationship table on my database:

CREATE TABLE IF NOT EXISTS public.portal_user_user_type
(
    id bigint NOT NULL,
    portal_user_id_fk bigint NOT NULL,
    user_type_id_fk bigint NOT NULL,
    CONSTRAINT portal_user_user_type_pkey PRIMARY KEY (id),
    CONSTRAINT portal_user_user_type_to_portal_user FOREIGN KEY (portal_user_id_fk)
        REFERENCES public.portal_user (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT portal_user_user_type_to_user_type FOREIGN KEY (user_type_id_fk)
        REFERENCES public.user_type (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

The previous table is my Many To Many association table between the portal_user table and ** user_type** table. This because an user can have multiple user types and a user types can be related to different users.

So I have mapped it into my User class (mapping the portal_user table), in this way:

@SpringBootTest()
@ContextConfiguration(classes = GetUserWsApplication.class)
@TestMethodOrder(OrderAnnotation.class)
public class UserRepositoryTest {
    
    @Autowired
    private UsersRepository userRepository;
    
    
    @Test
    @Order(1)
    public void testInsertUser() {
    
        User user = new User("Mario", null, "Rossi", 'M', new Date(), "XXX", "[email protected]", "329123456", new Date());
        
        Set<Address> addressesList = new HashSet<>();
        addressesList.add(new Address("Italy", "RM", "00100", "Via XXX 123", "near YYY", user));
        
        user.setAddressesList(addressesList);
        
        Set<UserType> userTypesList = new HashSet<>();
        UserType userType1 = new UserType("ADMIN", "Admin user type !!!");
        UserType userType2 = new UserType("USER", "Just a simple user...");
        
        userTypesList.add(userType1);
        userTypesList.add(userType2);
        
        user.setUserTypes(userTypesList);
        
        userRepository.save(user);
        assertTrue(true);
        
    }
    
}

The problem is this test method, when the save() method is performed I obtain this output with the following exception:

Hibernate: 
    insert 
    into
        portal_user
        (birthdate, contact_number, created_at, e_mail, first_name, middle_name, sex, surname, tex_code) 
    values
        (?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: 
    insert 
    into
        address
        (country, notes, province, street, fk_user_id, zip_code) 
    values
        (?, ?, ?, ?, ?, ?)
Hibernate: 
    insert 
    into
        user_type
        (description, type_name) 
    values
        (?, ?)
Hibernate: 
    insert 
    into
        user_type
        (description, type_name) 
    values
        (?, ?)
Hibernate: 
    insert 
    into
        portal_user_user_type
        (portal_user_id_fk, user_type_id_fk) 
    values
        (?, ?)
2021-11-08 12:58:50.859  WARN 10724 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: 23502
2021-11-08 12:58:50.863 ERROR 10724 --- [           main] o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: null value in column "id" of relation "portal_user_user_type" violates not-null constraint
  Detail: Failing row contains (null, 13, 1).
2021-11-08 12:58:50.958  INFO 10724 --- [           main] o.h.e.j.b.internal.AbstractBatchImpl     : HHH000010: On release of batch it still contained JDBC statements

This because it seems that it cannot insert the record into my portal_user_user_type many to many relation table because the ID field of this table is NOT NULL.

I know that a possible solution could be remove this ID primary key field and create a composite PK (composed my two fields: portal_user_id_fk and user_type_id_fk). It whould work but I prefear try to mantein this separate id PK field.

Is it possible to specify to my @ManyToMany annotation to generate an autoincrement id field? Or what could be a possible solution? (I know that I can also try to implement a Many To Many relathionship using two @OneToMany and another entity for the association table, but I prefear don't use this approach to avoid to complicate too much)

CodePudding user response：

You cannot add an auto increment property to your @ManyToMany annotation directly, instead you should add an auto increment ID to your joined database table and add a @GeneratedValue annotation with an Identity generation type as follows:

@Entity
@Table(name = "portal_user_user_type")
public class PortalUserUserType{

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

...

CodePudding user response：

Just make your id in the portal_user_user_type table auto-incremented: id bigint NOT NULL GENERATED ALWAYS AS IDENTITY.... Then Hibernate will generate a valid insert statement without mentioning an id.