Mapped foreign key in Hibernate Entity

Question

Hi write Spring application, using Spring Security. It's my database for user and account role:

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
);

My Entity classes:

@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;

When I try login in my system i have error:

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

My class implements UserDetailsService:

    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;
    }
}

I think so, my mapped foregin key for tables is bad. Example query from Userreturn users from table, but when i try get user_roles i have above error. Please check correctness my mapped. I using Oracle database and Hiberante.

Answer 1

The problem is, that when you're mapping entities Hibernate expects the foreign key to be the id of the referenced entity, i.e. you should map on user-id instead of username.

Also your entity mapping seems to be wrong: you use a ManyToOne with the target entity being User but the type of the property is String. AFAIK Hibernate would try to assign the user to username, which should fail miserably.

So the table should look like this:

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

And the mapping in UserRole should then be:

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

Plus the reverse mapping in User:

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

As a side note, please keep in mind that id is a special keyword in HQL, i.e. it will always reference an entity's id. If id always is the only property annotated with @Id then it's no problem, but if you change that you can run into problems with queries selecting the wrong data or even failing.