java - Hibernate 实体中的映射外键

Question

您好，使用 Spring Security 编写 Spring 应用程序。这是我的用户和帐户角色的数据库:

create table users (

  id int not null primary key,
  username varchar2(20) not null unique,
  password varchar2(20) not null,
  firstName varchar2(20),
  lastName varchar2(20),
  personalId varchar2(11) unique,
  city varchar2(40),
  address varchar2(40),
  email varchar2(30) unique,
  phone varchar2(9) unique,
  enabled number(1) not null
);

create table user_roles (
  id int primary key,
  name varchar2(20) not null,
  username varchar(20) constraint username_fk references users(username) not null
);

我的实体类:

@Entity
@Table(name = "users")
public class User implements Serializable {

    private static final long serialVersionUID = 1L;
    @Id
    @GeneratedValue
    @Column(name = "id")
    private Integer id;
    @NotNull
    @Column(name = "username")
    private String username;
    @NotNull
    @Column(name = "password")
    private String password;
    @Column(name = "firstName")
    private String firstName;
    @Column(name = "lastName")
    private String lastName;
    @Column(name = "personalId")
    private String personalId;
    @Column(name = "city")
    private String city;
    @Column(name = "address")
    private String address;
    @Column(name = "email")
    private String email;
    @Column(name = "phone")
    private String phone;
    @NotNull
    @Column(name = "enabled")
    private int enabled;
    @OneToMany(mappedBy = "username")
    private Set<UserRole> userRoleSet = new HashSet<UserRole>(0);

@Entity
@Table(name = "user_roles")
public class UserRole implements Serializable {

    private static final long serialVersionUID = 1L;

    @Id
    @GeneratedValue
    @Column(name = "id")
    private Integer id;
    @NotNull
    @Column(name = "name")
    private String name;
    @JoinColumn(name = "username")
    @ManyToOne(targetEntity = User.class)
    private String username;

当我尝试登录我的系统时出现错误:

Hibernate: select userrolese0_.username as username3_1_0_, userrolese0_.id as id1_0_0_, userrolese0_.id as id1_0_1_, userrolese0_.name as name2_0_1_, userrolese0_.username as username3_0_1_ from user_roles userrolese0_ where userrolese0_.username=? WARN : org.hibernate.engine.jdbc.spi.SqlExceptionHelper - SQL Error: 1722, SQLState: 42000 ERROR: org.hibernate.engine.jdbc.spi.SqlExceptionHelper - ORA-01722: invalid number

我的类实现了 UserDetailsS​​ervice:

    package pl.piotr.ibank.service;

import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.security.core.GrantedAuthority;
import org.springframework.security.core.authority.SimpleGrantedAuthority;
import org.springframework.security.core.userdetails.User;
import org.springframework.security.core.userdetails.UserDetails;
import org.springframework.security.core.userdetails.UserDetailsService;
import org.springframework.security.core.userdetails.UsernameNotFoundException;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import pl.piotr.ibank.daointerface.UserDao;
import pl.piotr.ibank.model.UserRole;

@Transactional(readOnly = true)
@Service("userDetailsService")
public class MyUserDetailsService implements UserDetailsService {

    @Autowired
    UserDao userDao;

    @Override
    public UserDetails loadUserByUsername(String username)
            throws UsernameNotFoundException {

        pl.piotr.ibank.model.User user = userDao.findByUsername(username);

        List<GrantedAuthority> authorities = buildUserAuthority(user
                .getUserRole());

        return buildUserForAuthentication(user, authorities);

    }

    private List<GrantedAuthority> buildUserAuthority(Set<UserRole> userRoles) {
        Set<GrantedAuthority> setAuths = new HashSet<GrantedAuthority>();

        for (UserRole userRole : userRoles) {
            setAuths.add(new SimpleGrantedAuthority(userRole.getName()));
        }

        List<GrantedAuthority> result = new ArrayList<GrantedAuthority>(
                setAuths);
        return result;
    }

    private UserDetails buildUserForAuthentication(
            pl.piotr.ibank.model.User user, List<GrantedAuthority> authorities) {
        return new User(user.getUsername(), user.getPassword(), true, true,
                true, true, authorities);
    }

    public UserDao getUserDao() {
        return userDao;
    }

    public void setUserDao(UserDao userDao) {
        this.userDao = userDao;
    }
}

我认为是的，我的表映射外键很糟糕。示例查询from User从表中返回用户，但是当我尝试获取 user_roles 时，出现上述错误。请检查我映射的正确性。我使用 Oracle 数据库和 Hiberante。

Answer 1

问题是，当您映射实体时，Hibernate 期望外键是引用实体的 id，即您应该映射 user-id 而不是用户名。

此外，您的实体映射似乎是错误的:您使用目标实体为 User 的 ManyToOne，但属性的类型为 String。 AFAIK Hibernate 会尝试将用户分配给 username，这应该会失败。

所以表格应该如下所示:

create table user_roles (
  id int primary key,
  name varchar2(20) not null,
  userid int constraint userid_fk references users(id) not null
);

UserRole 中的映射应该是:

@JoinColumn(name = "userid")
@ManyToOne
private User user;

加上User中的反向映射:

@OneToMany(mappedBy = "user")
private Set<UserRole> userRoleSet;

顺便说一句，请记住 id 是 HQL 中的特殊关键字，即它将始终引用实体的 id。如果 id 始终是唯一用 @Id 注解的属性，那么这没有问题，但如果您更改它，则可能会遇到查询选择错误数据甚至失败的问题。