I have a Spring project that uses JPA with Hibernate and MySQL and a database with three tables: Users, Roles, and join table User Roles. Could you please tell me whether I should use inverseForeignKey attribute in the inverseJoinColumn or keep it as it is (simply foreignKey)?
@Entity
@Table(name = "users")
public class User
{
// Class contains some other variables and methods that are irrelevant to this question
// All annotations are from javax.persistence package
@ManyToMany(cascade = CascadeType.ALL)
@JoinTable(name = "user_roles",
joinColumns = @JoinColumn(name = "user_id", foreignKey = @ForeignKey(name = "FK_users_user_roles_user_id")),
inverseJoinColumns = @JoinColumn(name = "role_id", foreignKey = @ForeignKey(name = "FK_roles_user_roles_role_id")))
private Set<Role> roles;
}
SQL scripts for all tables:
CREATE TABLE `users` (
`user_id` int(11) NOT NULL AUTO_INCREMENT,
`email` varchar(255) NOT NULL,
`username` varchar(255) NOT NULL,
`password` varchar(255) NOT NULL,
`points` bigint(11) NOT NULL,
`rank` varchar(255) DEFAULT NULL,
`registered_at` date DEFAULT NULL,
`avatar` varchar(255) DEFAULT NULL,
`full_name` varchar(255) DEFAULT NULL,
`info` varchar(255) DEFAULT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `roles` (
`role_id` int(11) NOT NULL AUTO_INCREMENT,
`role` varchar(255) DEFAULT NULL,
PRIMARY KEY (`role_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
CREATE TABLE `user_roles` (
`user_id` int(11) NOT NULL,
`role_id` int(11) NOT NULL,
PRIMARY KEY (`user_id`,`role_id`),
CONSTRAINT `FK_roles_user_roles_role_id` FOREIGN KEY (`role_id`) REFERENCES `roles` (`role_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_users_user_roles_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
You can simply use referencedColumnName
to refer the primary key of the parent tables in join table. Your @ManyToMany
mapping can be like below.
@ManyToMany(cascade = CascadeType.ALL)
@JoinTable(name = "user_roles",
joinColumns = @JoinColumn(name = "user_id", referencedColumnName="user_id"),
inverseJoinColumns = @JoinColumn(name = "role_id", referencedColumnName="role_id"))
private Set<Role> roles;
Please refer here