Spring Boot JPA how to implement foreign Key between users and authorities tables?

Question

Forgive me if this is an obvious question.

My application uses LDAP authentication, but loads user authorities from it's database, which works perfectly well. Now I'm trying to create JPA entities to manage the users and authorities though a REST interface.

How do I implement the following schema using JPA?

create table users(
    username varchar(50) not null primary key,
    password varchar(50) not null,
    enabled boolean not null
);

create table authorities (
    username varchar(50) not null,
    authority varchar(50) not null,
    constraint fk_authorities_users foreign key(username) references users(username)
);

create unique index ix_auth_username on authorities (username, authority)

This is what I've created so far:

User.java:

@Entity
@Data
@Table(name = "users", uniqueConstraints = {@UniqueConstraint(columnNames = {"username"})})
public class User {
    @Id
    @NotNull
    @Column(length=50)
    private String username;

    @NotNull
    private String password;

    @NotNull
    boolean enabled;
}

Authority.java:

@Entity
@Data
@Table(name = "authorities", uniqueConstraints = {@UniqueConstraint(columnNames = {"username"})})
public class Authority {
    @Id
    @NotNull
    @Column(length=50)
    private String username;

    @NotNull
    private String authority;
}

UserRepository:

public interface UserRepository extends CrudRepository<User, Long> { }

Maybe because I don't understand the correction application of that annotation - but I am trying to make the username column as the primary key, as per the SQL as that start of my question. — simbro, Aug 24 '18 at 12:30
Have a look at this question about composite keys : https://stackoverflow.com/questions/3585034/how-to-map-a-composite-key-with-hibernate — Arnaud, Aug 24 '18 at 12:33

score 2 · Answer 1 · answered Nov 30 '18 at 10:03

Use JPA annotations manyToOne and oneToMany :

User.java

@Entity
@Data
@Table(name = "users", uniqueConstraints = {@UniqueConstraint(columnNames = {"username"})})
public class User {
    @Id
    @NotNull
    @Column(length=50)
    private String username;

    @NotNull
    private String password;

    @NotNull
    boolean enabled;

    @OneToMany(mappedBy = "user", fetch = FetchType.LAZY, orphanRemoval = false)
    private List<Authority> listAuthorities = new ArrayList<>();

}

Authority.java

@Entity
@Data
@Table(name = "authorities", uniqueConstraints = {@UniqueConstraint(columnNames = {"username"})})
public class Authority {

@Id       
private Long id; 

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "user.username")
private User user;

@NotNull
private String authority;
}

`@JoinColumn(name = "user.username")` . Why is it **user** and not **users** as tahe table name? — Tal Barda, Mar 29 '23 at 20:34

score 0 · Answer 2 · answered Aug 24 '18 at 12:24

If I understand correctly you want data spread between two tables, where IDs would be shared. I think this is a good use of @SecondaryTable annotation. See the docs: https://docs.oracle.com/javaee/7/api/javax/persistence/SecondaryTable.html

In essence, you specify a secondary table at the class level with @SecondaryTable annotation, and then you annotate any field that should go to that table with @Column(table="xxx"). In your case authorities is the secondary table. So something along the lines of:

@Entity
@Data
@Table(name = "users", uniqueConstraints = {@UniqueConstraint(columnNames = {"username"})})
@SecondaryTables({
    @SecondaryTable(name="authorities")
})
public class User {
    @Id
    @NotNull
    @Column(length=50)
    private String username;

    @NotNull
    private String password;

    @NotNull
    boolean enabled;

    @NotNull
    @Column(table="authorities")
    private String authority;
}

Thanks for your answer - I've actually taken a different approach though - it's too hard to make JPA try to re-create the exact schema that is required by JdbcDaoImpl. Instead I'm overriding the LdapAuthoritiesPopulator. — simbro, Aug 28 '18 at 12:35

Spring Boot JPA how to implement foreign Key between users and authorities tables?

2 Answers2