It is not possible to save data to a table(H2 DataBase) using Spring-Data-JPA

I am using H2 database version 2.2.224 and Spring-Data-JPA version 3.3.3. I have a Many-to-Many relationship between the "USERS" and "ROLES" tables.

When I try to save "Entity = USERS" to the H2 Database, I get the following error::
org.h2.jdbc.JdbcSQLSyntaxErrorException: Generated column "PUBLIC.USERS.USER_ID_PK" cannot be assigned.
SQL statement: insert into users (password,login,user_id_pk) values (?,?,?) [90154-224]

In my opinion, this error occurs due to the fact that Spring JPA generates the id itself and tries to insert this id into the database itself, while H2 wants to generate the id itself and insert it into the table.

If I create the "user_id_pk" column in the "USERS" table like this:: "user_id_pk integer PRIMARY KEY" and change the GenerationType in "Entity = USERS", then various errors occur.

1. GenerationType.AUTO :: object references an unsaved transient instance – save the transient instance before flushing: Roles
2. GenerationType.SEQUENCE:: object references an unsaved transient instance – save the transient instance before flushing: Roles
3. GenerationType.IDENTITY:: JdbcSQLIntegrityConstraintViolationException: NULL not allowed for column "USER_ID_PK";

If I create the column "user_id_pk" in the "USERS" table like this:: role_id_pk integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY and change GenerationType in "Entity = USERS", then various errors occur.

1. GenerationType.AUTO :: object references an unsaved transient instance – save the transient instance before flushing: Roles
2. GenerationType.SEQUENCE:: object references an unsaved transient instance – save the transient instance before flushing: Roles
3. GenerationType.IDENTITY:: object references an unsaved transient instance – save the transient instance before flushing: Roles

If I create the column "user_id_pk" in the "USERS" table like this:: role_id_pk integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY and change GenerationType in "Entity = USERS", then various errors occur.

1. GenerationType.AUTO :: Generated column "PUBLIC.USERS.USER_ID_PK" cannot be assigned
2. GenerationType.SEQUENCE:: Generated column "PUBLIC.USERS.USER_ID_PK" cannot be assigned
3. GenerationType.IDENTITY:: object references an unsaved transient instance – save the transient instance before flushing: Roles

I create a SEQUENCE like this::

CREATE SEQUENCE USERS_SEQ START WITH 1 INCREMENT BY 50;

My Tables::

CREATE TABLE PUBLIC.ROLES (
   role_id_pk Integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, 
   role VARCHAR
);

CREATE TABLE PUBLIC.USERS (
   user_id_pk Integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY, 
   login VARCHAR, 
   password VARCHAR
);

My Entity::

@Entity
@Table(name = "users")
public class MyUser {

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

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

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

@ManyToMany
@JoinTable(name = "USERS_ROLES",
           joinColumns = {@JoinColumn(name = "user_id_pk")},
           inverseJoinColumns = {@JoinColumn(name = "role_id_pk")})
private List<Roles> listRoles;

My Entity::

@Entity
@Table(name = "roles")
public class Roles {

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

 @Enumerated(EnumType.STRING)
 @Column(length = 20, name = "role")
 private ERole name;

 @ManyToMany(mappedBy="listRoles")
 private List<MyUser> myUsersList;

You need to sign in to view this answers