How can we assign a field of user table say id that is primary key, to another table as a primary key? using hibernate

user_17140876 0
Viewed 66

See I dont want any mapping but what I want is just a simple thing that is....

I have two Entity

  1. Tasks(taskid,taskname.....,userId)
  2. User_Credentials(userId,password)

So What I want is userId of User Credentials used as a foreign key in Task table.

So when I fire an api to save the task I have to also pass the all details of user Credentials That I dont want.

    {
    "taskName": "Test",
    "taskStatus": "Open",
    "taskDueDate": "2022-04-15",
    "taskCreatedDate": "2022-04-10",
    "taskDescription": "Demo",
    "taskPriority": "High",
    "isTaskActive": "Yes",
    "userCredentials":{
        "associateId":"108",
        "password":"something@9122"
    }
  }

What I want is

   {
    "taskName": "Test",
    "taskStatus": "Open",
    "taskDueDate": "2022-04-15",
    "taskCreatedDate": "2022-04-10",
    "taskDescription": "Demo",
    "taskPriority": "High",
    "isTaskActive": "Yes",
    "userCredentials":"108"

}

That I want to post and if userCredentials id 108 is not in User Credentials Table so it pop up an error and if it is there it will save just like a foreign key concept.

So please tell me ho to do that.

spring-data-jpa spring-boot hibernate spring sql
1 Answers

You can use ElementCollection to achieve this.

In Tasks entity

@Id
@Column(name = "task_id")
private Integer id;

@ElementCollection  
@CollectionTable(name = "task_user", joinColumns = @JoinColumn(name = "task_id", nullable = false))  
@Column(name = "user_id", nullable = false)  
private List<Integer> userCredentials;

In UserCredentials entity

@Id  
@Column(name = "user_id")  
private Integer id;

UserCredentials is an independent entity with no mapping with Tasks. You will now be able to pass UserCredentials like this userCredentials: [108] in the Tasks.

Add the following in your application.yml to generate the create.sql script with ddl commands for all the entities. ddl-auto: validate will restrict hibernate from creating the tables on its own.

spring:
  jpa:
    hibernate:
      ddl-auto: validate
    properties:
      javax:
        persistence:
          schema-generation:
            create-source: metadata
            scripts:
              action: create
              create-target: create.sql
    generate-ddl: true

The following is the ddl command to add foreign key constraints for tables user_credentials and tasks. Execute it alongwith the create.sql in any DB client.

CREATE TABLE "task_user"
(   "user_id" integer ,
    "task_id" integer,
    PRIMARY KEY ("user_id","task_id"),
    CONSTRAINT user_id_fkey
        FOREIGN KEY ("user_id") REFERENCES "user_credentials" ("user_id"),
    CONSTRAINT task_id_fkey
        FOREIGN KEY ("task_id") REFERENCES "tasks" ("task_id")
);

Note: The create.sql already has a task_user create command but it needs to be replaced with the above command.

user_170652580
Related