Hibernate Mapping composite primary key that contains foreign keys

Question

I have 2 classes Rule and Group. A rule is mapped to multiple groups through another class RuleGroupMap. The corresponding tables in DB are like this

CREATE TABLE `rules` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `validity_start_time` timestamp NULL DEFAULT NULL,
  `validity_end_time` timestamp NULL DEFAULT NULL,
  `rule_name` char(25) DEFAULT '',
  `description` text,
  PRIMARY KEY (`id`)
)

CREATE TABLE `group` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL
  PRIMARY KEY (`id`)
)

CREATE TABLE `rule_group_map` (
  `area_id` int(11) NOT NULL DEFAULT '0',
  `group_id` bigint(11) NOT NULL DEFAULT '0',
  `active` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`group_id`,`rule_id`),
  KEY `group` (`group_id`),
  KEY `rule` (`rule_id`),
  CONSTRAINT `group` FOREIGN KEY (`group`) REFERENCES `group` (`id`),
  CONSTRAINT `rule` FOREIGN KEY (`rule_id`) REFERENCES `rules` (`id`)
)

The RuleGroupMap has its PK as a combination of rule_id and group_id. Also, rule_id has a ManyToOne relationship to Rule and group_id has a ManyToOne relationship to Group.

How do I create entities in Hibernate for this scenario?

My current code is as follows and throws a SQLGrammerException.

@Entity
@Table(name = "rules")
public class Rule implements Serializable{

    @Id @GeneratedValue(strategy = IDENTITY)
    @Getter @Setter @Column(name = "id", nullable = false, unique = true)
    private Long Id;

    @Getter @Setter @Column(name = "validity_start_time", nullable = false)
    private Timestamp validity_start;

    @Getter @Setter @Column(name = "validity_end_time", nullable = false) 
    private Timestamp validity_end;

    @Getter @Setter @Column(name = "rule_name") 
    private String ruleName;

    @Getter @Setter @Column(name = "description") 
    private String description;

    @Getter @Setter @OneToMany(mappedBy = "rule") 
    private Set<RuleGroupMap> groupsMap;
}



    @Entity
    @Table(name = "group")
    public class Group implements Serializable {

        @Id
        @Getter @Setter @Column(name = "id", nullable = false) 
        private long id;

        @Getter @Setter @Column(name = "name", nullable = false) 
        private String name;

        @Getter @Setter @OneToMany(mappedBy = "area") 
        private Set<RuleGroupMap> rulesMap;
    }


@Entity
@Table(name = "rule_group_map")
@Data
@IdClass(RuleMapPK.class)
public class RuleGroupMap {

    @Id @ManyToOne @JoinColumn(name="group_id", referencedColumnName = "id") private Group group;

    @Id @ManyToOne @JoinColumn(name="rule_id", referencedColumnName = "id") private Rule rule;

    @Column(name = "active") private boolean active;
}

@Data
public class RuleMapPK implements Serializable {
    @Column(name="group_id") long group;
    @Column(name="rule_id") Long rule;
}

score 1 · Accepted Answer · edited May 23 '17 at 11:45

1

Use MapId annotation on your RuleMap class. You will also be better off using @Embeddable and @EmbeddedId since they are newer and recommended by JPA provider. See How to create a composite primary key which contains a @ManyToOne attribute as an @EmbeddedId in JPA?. and How to map compound primary key in JPA, where part of Primary Key is a Foreign Key.

edited May 23 '17 at 11:45

Community

1
1

answered Apr 01 '16 at 17:38

K.Nicholas

10,956
4
46
66

I have a follow up question to this. I now have an @Embedded property called map in my RuleGroupMap class which links to my PK (with Group_id and Rule_id). I also have another property in the same class of type Rule. When I am creating a new object of RuleGroupMap, should I set both, the Rule property and the map.rule_id property or just the Rule property? – Tejeshwar Singh Apr 02 '16 at 19:14
I think I understand the question. See second link of answer, near the bottom is code on using this type of PK. I think the answer for you is to set only the Rule property since that's what the `MapsId` annotation is about: it sets the map.rule_id for you. – K.Nicholas Apr 02 '16 at 19:49
Thanks! Exactly what I wanted. – Tejeshwar Singh Apr 03 '16 at 12:42

score 0 · Answer 2 · answered Apr 01 '16 at 15:20

In your @IdClass you haven't to declare column names, just declare:

public class RuleMapPK implements Serializable {

    private static final long serialVersionUID = 1L;

    private long group;
    private Long rule;

    //getter & setter - equals & hashcode
}

Hibernate Mapping composite primary key that contains foreign keys

2 Answers2