Home > Net >  How to create a composite key of one foreign key and one primary key Spring Data JPA
How to create a composite key of one foreign key and one primary key Spring Data JPA

Time:12-31

I am using Spring Boot to build an API. It is an API for a punishment system. Within the database there are two tables used for rules, these are the Rules table and the Community table. enter image description here

The rule Id is an integer and the community Id is a foreign key from the communities table. There is expected to be 0, 1 or Many rules for a community while a rule is made by exactly one community.

This is the behaviour I'm looking to replicate in my Spring Boot application.

So far I have the following code:

Community.java

@Entity // Maps to database
public class Community {
    @Id
    @Column(name = "id", length = 16, unique = true, nullable = false)
    private UUID id;

    @Column(name = "username", length = 20, nullable = false)
    private String name;

Rule.java

@Entity // Maps to database
public class Rule {

    @EmbeddedId
    private RuleId ruleId;

    @Column(name = "description", length = 50, nullable = false)
    private String description;

RuleId.java

@Embeddable
public class RuleId implements Serializable {

    private UUID communityId;
    private Integer ruleId;

    public RuleId(UUID communityId, Integer ruleId) {
        this.communityId = communityId;
        this.ruleId = ruleId;
    }

I have read through some threads on stack already which is where I have the embedded idea from but I am still confused about how I would get this to work with my scenario. Any help is greatly appreciated just to clear this confusion up.

Cheers :)

CodePudding user response:

You can use annotation @JoinColumn(...) and specify the relationship, in your case: @OneToOne(...)

Then you can simply define your Rule entity with only Long id or int id and as:

@Entity
public class Rule {
    
    @Id // map as id field
    @GeneratedValue(strategy = GenerationType.AUTO) // Auto generate id when update db
    @Column(name = "Rule_Id") // map the column field
    private Long ruleId;
    
    // Simply add annotation to map foreign key
    @OneToOne
    @JoinColumn(name = "community_id_fk", referencedColumnName = "community_id")
    private Community community;    

    // more field ...
    @Column(name = "description", length = 50, nullable = false)
    private String description;
}

For More guide you can see https://www.baeldung.com/jpa-one-to-one

CodePudding user response:

I solved this by taking the one to one relationship from Zukaru's answer.

What I wanted was a composite key comprised from community_id and rule_id. Community Id is a foreign key to the primary key on the Communities table.

To implement this I had 2 classes. Rule and RulePrimaryData.

Rule.java

@Entity // Maps to database
@Table(name = "rules")
@Data
@NoArgsConstructor
public class Rule {

    @EmbeddedId
    private RulePrimaryData id;

    @OneToOne
    @JoinColumn(name = "community_id_fk", referencedColumnName = "community_id", insertable = false, updatable = false)
    private Community communities;

    @Column(name = "description", length = 50, nullable = false)
    private String description;

    public Rule(RulePrimaryData ruleId, String description) {
        this.id = ruleId;
        this.description = description;
    }
}

An embedded Id was used to make the composite key containing the UUID and Integer.

RulePrimaryData.java

@Embeddable
@NoArgsConstructor
@Data //Getters, setters, constructors
public class RulePrimaryData implements Serializable {

    @Column(name = "community_id_fk", nullable = false)
    private UUID communityId;

    @Column(name = "rule_id", nullable = false)
    private Integer ruleId;

    public RulePrimaryData(UUID communityId, int ruleId) {
        this.communityId = communityId;
        this.ruleId = ruleId;
    }
}

Finally, the relationship was a One to One which I initially thought was a Many to One. In the definition I joined the community_id_fk column from the EmbeddedId to the community_id column in the Communities table.

  • Related