代码之家 › 专栏 › 技术社区 › uSeruSher

JPA映射问题

jpa spring-boot

uSeruSher · 技术社区 · 6 年前

我有下列表格

CREATE TABLE APPUSERS (
    APPUSERS_ID INT IDENTITY(1,1),  
    USERNAME VARCHAR(254) NOT NULL,
    PASSWORD VARCHAR(100) NOT NULL,
    PRIMARY KEY (USERNAME)
);

CREATE TABLE ALL_ROLES (
    ROLE_ID INT IDENTITY(1,1), 
    ROLENAME VARCHAR(100) NOT NULL,
    PRIMARY KEY (ROLENAME)
);

CREATE TABLE USER_ROLES(
  USER_ROLE_ID INT IDENTITY(1,1), 
  USERNAME VARCHAR(254) NOT NULL,
  CONSTRAINT FK_USERNAME FOREIGN KEY (USERNAME)     
    REFERENCES APPUSERS (USERNAME),
    ROLENAME VARCHAR(100) NOT NULL,
    CONSTRAINT FK_ROLENAME FOREIGN KEY (ROLENAME)     
    REFERENCES ALL_ROLES (ROLENAME),
    PRIMARY KEY (username,rolename)
)

我已经创建了相应的实体(见下文)和存储库

@Entity
@Table(name = "appusers")
public class User {
    private Long id;
    private String username;
    private String password;
    private String passwordConfirm;
    private Set<Role> roles;

    @Id
    @Column(name="APPUSERS_ID")
    @GeneratedValue(strategy = GenerationType.AUTO)
    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public String getPassword() {
        return password;
    }

    public void setPassword(String password) {
        this.password = password;
    }

    @Transient
    public String getPasswordConfirm() {
        return passwordConfirm;
    }

    public void setPasswordConfirm(String passwordConfirm) {
        this.passwordConfirm = passwordConfirm;
    }

    @ManyToMany
    @JoinTable(name = "USER_ROLES", joinColumns = @JoinColumn(name = "USERNAME"), inverseJoinColumns = @JoinColumn(name = "ROLENAME"))
    public Set<Role> getRoles() {
        return roles;
    }

    public void setRoles(Set<Role> roles) {
        this.roles = roles;
    }

和

    @Entity
@Table(name = "USER_ROLES")
public class Role {
    private Long id;
    @Column(name="USERNAME")
    private String name;
    private Set<User> users;

    @Id
    @Column(name="USER_ROLE_ID")
    @GeneratedValue(strategy = GenerationType.AUTO)
    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @ManyToMany(mappedBy = "roles")
    public Set<User> getUsers() {
        return users;
    }

    public void setUsers(Set<User> users) {
        this.users = users;
    }
}

当我启动应用程序时,会出现以下错误

Foreign key (FKrs04la1w0u7vtog85q1hxlse9:user_roles [rolename])) must have same number of columns as the referenced primary key (user_roles [username,rolename])

我不知道这里有什么问题。任何帮助都非常感谢。

我认为表映射都是正确的,但不确定为什么会发生此错误。

1 回复 | 直到 6 年前

Jonathan JOhx 6 年前

你的代码有几个问题,让我解释一下步骤:

关系多对多,您需要创建一个中间表才能执行此操作,因此您需要修复以下方面:

User entity

@Id
@Column(name = "APPUSERS_ID")
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;     

@ManyToMany(mappedBy = "users")
private Set<Role> roles;    

Role entity

@Id
@Column(name = "USER_ROLE_ID")
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;

@ManyToMany
@JoinTable(name = "role_user",
        joinColumns = { @JoinColumn(name = "role_id") },
        inverseJoinColumns = { @JoinColumn(name = "user_id") })
private Set<User> users;

如果您希望这些实体是由jpa hibernate在数据库上生成的,只需放置以下属性配置(您只需要创建名称为的数据库)。

spring.jpa.hibernate.ddl auto=更新

否则我把脚本留在这里在数据库上执行。

--表:public.appusers

--删除表public.appusers;

CREATE TABLE public.appusers
(
    appusers_id bigint NOT NULL,
    password character varying(255) COLLATE pg_catalog."default",
    password_confirm character varying(255) COLLATE pg_catalog."default",
    username character varying(255) COLLATE pg_catalog."default",
    CONSTRAINT appusers_pkey PRIMARY KEY (appusers_id)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public.appusers
    OWNER to postgres;

--表:public.role_user

--删除表public.role_user;

CREATE TABLE public.role_user
(
    role_id bigint NOT NULL,
    user_id bigint NOT NULL,
    CONSTRAINT role_user_pkey PRIMARY KEY (role_id, user_id),
    CONSTRAINT fkma2afyyxc0mraogwivmj0klfe FOREIGN KEY (role_id)
        REFERENCES public.user_roles (user_role_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT fkmhbomge36ygro6rth9negs1ye FOREIGN KEY (user_id)
        REFERENCES public.appusers (appusers_id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public.role_user
    OWNER to postgres;

--表:public.user_roles

--删除表public.user_roles;

CREATE TABLE public.user_roles
(
    user_role_id bigint NOT NULL,
    username character varying(255) COLLATE pg_catalog."default",
    CONSTRAINT user_roles_pkey PRIMARY KEY (user_role_id)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public.user_roles
    OWNER to postgres;