Hibernate: Foreign key has the wrong number of columns

Question

I have defined a many-to-many relationship between my two entity classes User and Permission. User has a primary key composite of username and countyId, and my Permission table has a regular integer Id. The table UserPermission has the three foreign keys as its primary key: username, countyId and permissionId.

Since this is a legacy database, I won't have the opportunity to do the Right Thing(™) and make an integer primary key on User.

I've defined the many-to-many relationship like this in User.class:

@ManyToMany(targetEntity=Permission.class, cascade={ CascadeType.PERSIST, CascadeType.MERGE } )
@JoinTable(name="tblUserPermission",
joinColumns = { @JoinColumn(name="username"), @JoinColumn(name="countyId") },
inverseJoinColumns = { @JoinColumn(name="permissionId") })
private Collection<Permission> permissions;

Permission.class says this:

@ManyToMany( cascade = {CascadeType.PERSIST, CascadeType.MERGE}, mappedBy = "permissions", targetEntity = User.class )
private Collection<User> users;

I thought this was the way to go, but when I fire up my Spring context that uses Hibernate 3, I get:

Caused by: org.hibernate.AnnotationException: A Foreign key refering com.mydomain.data.entities.User from com.mydomain.data.entities.Permission has the wrong number of column. should be 1

What have I done wrong in my annotation? It should be 2, not 1.

---

Update:

Arthur suggested I add referencedColumnName, but that gave me a new exception:

Caused by: org.hibernate.AnnotationException: referencedColumnNames(username, countyId) of com.mydomain.data.entities.Permission.permissions referencing com.mydomain.data.entities.User not mapped to a single property

On his request, here follow the code: Permission.Class:

package com.mydomain.data.entities;

import java.io.Serializable;
import java.util.Collection;
import javax.persistence.*;
import org.hibernate.annotations.ForeignKey;

@Entity
@Table(name = "tblPermission")
public class Permission extends PublishableEntityImpl implements Serializable, Cloneable {

    private static final long serialVersionUID = 7155322069731920447L;

    @Id
    @Column(name = "PermissionId", length = 8, nullable = false)
    private String PermissionId = "";

    @ManyToOne(fetch=FetchType.LAZY)
    @JoinColumn(name = "CountyId", nullable = false)
    @ForeignKey(name="FK_CountyID")
    private County county;

    @Column(name = "Permission", nullable = true)
    private Integer permission = 1;

    @ManyToMany( cascade = {CascadeType.PERSIST, CascadeType.MERGE},
             mappedBy = "Permissions",
             targetEntity = Item.class )
    private Collection<Item> items;

    @ManyToMany( cascade = {CascadeType.PERSIST, CascadeType.MERGE},
             mappedBy = "Permissions",
             targetEntity = User.class )
    private Collection<User> users;

    /** Getters and Setters **/
}

and User.class

package com.mydomain.data.entities;

import java.util.*;
import java.io.Serializable;
import javax.persistence.*;
import org.hibernate.annotations.ForeignKey;
import org.hibernate.annotations.IndexColumn;
import org.springframework.security.core.GrantedAuthority;
import org.springframework.security.core.authority.GrantedAuthorityImpl;
import org.springframework.security.core.userdetails.UserDetails;

@Entity
@Table(name = "tblUser")
public class User extends PublishableEntityImpl implements Serializable, Cloneable {

    @Id
    @Column(name = "CountyId", nullable = false)
    private Integer countyId;

    @Id
    @Column(name = "Username", length = 25, nullable = false)
    private String username;

    @ManyToOne(fetch=FetchType.LAZY)
    @JoinColumn(name = "CountyId", nullable = false, insertable=false, updatable=false)
    @ForeignKey(name="FK_CountyID")
    private County county;

    @Column(name = "Name", length = 50, nullable = true)
    private String name;

    @Column(name = "Password", length = 30, nullable = true)
    private String password;

    @Column(name = "Role", nullable = false)
    private Integer role;

    @ManyToMany(targetEntity=Permission.class,
            cascade={ CascadeType.PERSIST, CascadeType.MERGE } )
    @JoinTable(name="tblUserPermission",
            joinColumns = { @JoinColumn(name="Username", referencedColumnName="Username"), @JoinColumn(name="CountyId", referencedColumnName="CountyId") },
            inverseJoinColumns = { @JoinColumn(name="PermissionId", referencedColumnName="PermissionId") })
   private Collection<Permission> permissions;

    @OneToMany(fetch=FetchType.LAZY, mappedBy="county")
    @IndexColumn(name="version")
    private List<Version> versions;

    /** Getters and setters **/
}

Cheers

Nik

Solution

In order to solve referencedColumnName exception

In User put

@ManyToMany(cascade={CascadeType.PERSIST, cascadeType.MERGE})
private Collection<Permission> permissions;

And in Permission

@ManyToMany(mappedBy="permissions")
@JoinTable(name="tblUserPermission",
 joinColumns={@JoinColumn(name="permissionId", referencedColumnName="permissionId")},
 inverseJoinColumns={
 @JoinColumn(name="username", referencedColumnName="username"),                         
 @JoinColumn(name="countyId", referencedColumnName="countyId")})
private Collection<User> users;

UserId class

public class UserId implements Serializable {

    private String username;

    private Integer countyId;

    // getter's and setter's

    public boolean equals(Object o) {

        if(o == null)
            return false;

        if(!(o instanceof UserId))
            return false;

        UserId id = (UserId) o;
        if(!(getUsername().equals(id.getUsername()))
            return false;

        if(!(getCountyId().equals(id.getCountyId()))
            return false;

        return true;
    }

    public int hachcode() {
       // hashcode
    }

}

Then in User class put

@Entity
@Table(name="tblUser")
@IdClass(UserId.class)
public class User ... {

    @Id
    private String username;

    @Id
    private Integer countyId;

}

regards,

Comments

[niklassaers]

So just to be clear, name is the table column in the join-table while referencedColumnName is the table column in the entity-table? Anyway, adding referencedColumnName I get "Caused by: org.hibernate.AnnotationException: referencedColumnNames(username, countyId) of com.mydomain.data.entities.Permission.permissions referencing com.mydomain.data.entities.User not mapped to a single property" which is funny becuase 'permissions' belongs to the class User, not the class Permission

[niklassaers]

BTW, why did you remove targetEntity=Permission.class?

[Arthur Ronald]

Just a moment. If you declare generuc type Collection<Permission> and Collection<User> targetEntity is not necessary. I will check out your first doubt

[Arthur Ronald]

mappedBy in Permission class should be permission, not "P"ermission

[Arthur Ronald]

In User class, remove nullable=false in county property

[niklassaers]

Hi, I tried swapping the join/inverse join around like you suggested. That gave me: "Caused by: org.hibernate.MappingException: Unable to find column with logical name: PermissionId in org.hibernate.mapping.Table(tblUser) and its related supertables and secondary tables" That sounds like a clue, because PermissionId should be in tblUserPermission, not tblUser

[Arthur Ronald]

User has a composite primary key. You MUST provide a UserId class I will show you UserId class

[niklassaers]

mappedBy="Permissions" was a typo in my translation from Danish to English, sorry about that :-)

[niklassaers]

In tblUser, countyId is defined as not null as it's a part of the primary key, that's why nullable = false is set in that class. Why should I remove that?

[Arthur Ronald]

@niklassaers When two properties share the same column, it is a good idea put information about it in just one property. Otherwise, Hibernate can complain some errors. In that case, remove nullable from County property in User class, not from the primary key countyId property

[niklassaers]

Thanks you very much for your help so far, I updated the source but then I got: "Caused by: org.hibernate.AnnotationException: Column name countyId of com.mydomain.data.entities.User not found in JoinColumns.referencedColumnName". I'll have to log off now, but I'll get back to you this evening. Thank you again VERY much for all your help

[Arthur Ronald]

It is a bug in Hibernate. See what Emmanuel (co-founder of Hibernate) says about it (at the bottom of the page): forum.hibernate.org/viewtopic.php?p=2339261 Solved, added to original answer

[Arthur Ronald]

And if possible put annotation configuration in getter method instead of member field. Hibernate makes use of proxy objects because of performance issues. And Automatic dirty checking feature (hits ths database only when necessary) also makes use of proxy objects.

[niklassaers]

Thank you very much, Arthur. :-) I didn't know about setting the annotation on the getter method, I assumed it was for the variable that was supposed to be a property

[niklassaers]

Are you sure this bug that was around in January 2007 is still around?

[niklassaers]

Just to be clear: you solved my problems and exceptions in your solution, and gave me a lot of really good advice at the same time. Thank you SOOO much for your time! :-)

[Arthur Ronald]

I think it occurs when you have a referencedColumnName in more than one property - private Integer countyId; and private County county; Anyway is a Hibernate bug.

[Arthur Ronald]

Just a correction: you can use cascade attribute in both sides, even in a bidirectional relationship. Sorry my mistake