JPA many-to-many association with extra columns not working with Hibernate


#1

Hi,

we have some issues working with this kind of table configuration. We are using Hibernate version 5.3.6.

We are not able to load data into the mapped class even if the query generated by hibernate looks ok.
We use session.get(class, id)

In order to have the query running properly we had to add the @JoinColumn annotation in @MapsId as by default hibernate add an _ID at the end of the map class.
Note that we have a maximum of 4 rows per table.

We have a jvm error (java 10) when trying to access collection object. We also have these Hibernat error.

10-10-2018 18:37:00.517] [Test worker] WARN org.hibernate.engine.loading.internal.LoadContexts – HHH000100: Fail-safe cleanup (collections) : org.hibernate.engine.loading.internal.CollectionLoadContext@6f4e6518<rs=com.mchange.v2.c3p0.impl.NewProxyResultSet@6178010c [wrapping: null]>
[10-10-2018 18:37:00.526] [Test worker] WARN org.hibernate.engine.loading.internal.CollectionLoadContext – HHH000160: On CollectionLoadContext#cleanup, localLoadingCollectionKeys contained [1] entries
[10-10-2018 18:37:00.527] [Test worker] WARN org.hibernate.engine.loading.internal.LoadContexts – HHH000100: Fail-safe cleanup (collections) : org.hibernate.engine.loading.internal.CollectionLoadContext@3ba2288a<rs=com.mchange.v2.c3p0.impl.NewProxyResultSet@191f363d [wrapping: null]>

Thanks in advance.


#2

There’s no JVM error in your log. Also, there is no mapping, data access code, or SQL logs to understand what the problem is.

For a detailed example of a many-to-many association with extra columns, check out this article.


#3

Hi Vlad,

these are the tables:

– ======= USER TABLE ======== –
CREATE TABLE {dbSchema}.lt_user ( ID SERIAL PRIMARY KEY, login VARCHAR(100) NOT NULL, pwd VARCHAR(100) NOT NULL, startURL VARCHAR(100) NOT NULL DEFAULT '/home', timezone VARCHAR(100) NOT NULL, name VARCHAR(100) NOT NULL, surname VARCHAR(100) NOT NULL, email VARCHAR(100) NULL, mobile VARCHAR(100) NULL, createdby INTEGER NOT NULL DEFAULT 0, appadmin BOOLEAN NOT NULL DEFAULT FALSE, languageID INTEGER NOT NULL REFERENCES {dbSchema}.lt_language(ID) ON UPDATE NO ACTION ON DELETE NO ACTION,
insert_date TIMESTAMP NOT NULL DEFAULT NOW(),
insert_user VARCHAR(100) NOT NULL,
update_date TIMESTAMP NULL,
update_user VARCHAR(100) NULL);

– ======= USERGROUP TABLE ======== –
CREATE TABLE {dbSchema}.lt_usergroup ( ID SERIAL PRIMARY KEY, userID INTEGER NOT NULL REFERENCES {dbSchema}.lt_user(ID) ON DELETE CASCADE ON UPDATE CASCADE,
groupID INTEGER NOT NULL REFERENCES ${dbSchema}.lt_group(ID) ON DELETE CASCADE ON UPDATE CASCADE,
insert_date TIMESTAMP NOT NULL DEFAULT NOW(),
insert_user VARCHAR(100) NOT NULL,
update_date TIMESTAMP NULL,
update_user VARCHAR(100) NULL);

– ======= GROUP TABLE ======== –
CREATE TABLE {dbSchema}.lt_group ( ID SERIAL PRIMARY KEY, deploymentID INTEGER NOT NULL REFERENCES {dbSchema}.lt_deployment(ID) ON UPDATE CASCADE ON DELETE CASCADE,
name VARCHAR(100) NOT NULL,
description TEXT NULL,
isdefault BOOLEAN NOT NULL DEFAULT FALSE,
insert_date TIMESTAMP NOT NULL DEFAULT NOW(),
insert_user VARCHAR(100) NOT NULL,
update_date TIMESTAMP NULL,
update_user VARCHAR(100) NULL);

and these are the mapped classes

UserGroupID:

package model.leitfeld;

import java.io.Serializable;
import java.util.Objects;

import javax.persistence.Column;
import javax.persistence.Embeddable;

@Embeddable
public class UserGroupId implements Serializable {

private static final long serialVersionUID = 1107308333299204739L;

@Column(name = "userID", nullable = false)
private int userID;

@Column(name = "groupID", nullable = false)
private int groupID;

public UserGroupId(int userID, int groupID) {
    this.userID = userID;
    this.groupID = groupID;
}

public int getUserID() {
    return userID;
}

public void setUserID(int userID) {
    this.userID = userID;
}

public int getGroupID() {
    return groupID;
}

public void setGroupID(int groupID) {
    this.groupID = groupID;
}

@Override
public boolean equals(Object o) {
    if (this == o) return true;
    if (o == null || getClass() != o.getClass()) 
        return false;
    UserGroupId that = (UserGroupId) o;
    return Objects.equals(userID, that.userID) && 
            Objects.equals(groupID, that.groupID);
}

@Override
public int hashCode() {
    return Objects.hash(userID, groupID);
}

}

UserGroup:

package model.leitfeld;

import java.io.Serializable;
import java.util.Objects;

import javax.persistence.Embedded;
import javax.persistence.EmbeddedId;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.MapsId;
import javax.persistence.Table;

@Entity(name = “UserGroup”)
@Table(name = “lt_usergroup”, schema = “leitfeld2”)
public class UserGroup implements Auditable, Serializable {

private static final long serialVersionUID = -8357867400518906296L;

@EmbeddedId
private UserGroupId id;

@Embedded
protected Audit audit;

@ManyToOne(fetch = FetchType.EAGER)
@MapsId("userID")
@JoinColumn(name = "userID", referencedColumnName = "id", nullable = false)
private User user;

@ManyToOne(fetch = FetchType.EAGER)
@MapsId("groupID")
@JoinColumn(name = "groupID", referencedColumnName = "id", nullable = false)
private Group group;

public UserGroup(User user, Group group) {
    this.user = user;
    this.group = group;
    this.id = new UserGroupId(user.getId(), group.getId());
}

public UserGroupId getUserGroupIdid() {
    return id;
}

public void setUserGroupIdid(UserGroupId userGroupIdid) {
    this.id = userGroupIdid;
}

public User getUser() {
    return user;
}

public void setUser(User user) {
    this.user = user;
}

public Group getGroup() {
    return group;
}

public void setGroup(Group group) {
    this.group = group;
}

@Override
public boolean equals(Object o) {
if (this == o) return true;
if (o == null || getClass() != o.getClass())
return false;
UserGroup that = (UserGroup) o;
return Objects.equals(user, that.user) &&
Objects.equals(group, that.group);
}

@Override
public int hashCode() {
    return Objects.hash(user, group);
}

@Override
public Audit getAudit() {
    return audit;
}

@Override
public void setAudit(Audit audit) {
    this.audit = audit;
}

}

Group:

package model.leitfeld;

import java.util.HashSet;
import java.util.Objects;
import java.util.Set;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.OneToMany;
import javax.persistence.Table;
import javax.persistence.UniqueConstraint;

import org.hibernate.annotations.Fetch;
import org.hibernate.annotations.FetchMode;
import org.hibernate.annotations.NaturalId;

@Entity(name = “Group”)
@Table(name = “lt_group”, schema = “leitfeld2”,
uniqueConstraints = {
@UniqueConstraint(columnNames = “ID”),
@UniqueConstraint(columnNames = “name”)})
//@NaturalIdCache
//@Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
public class Group extends TableTemplate {

private static final long serialVersionUID = -4591267153931382954L;

@ManyToOne
@Fetch(FetchMode.JOIN)
@JoinColumn(name = "deploymentID", nullable = false, updatable = false)
private Deployment deployment;

@NaturalId (mutable = false)
@Column(name = "name", length = 100, updatable = false, unique = true, nullable = false)
private String name;

@Column(name = "description", columnDefinition="TEXT")
private String description;

@Column(name = "isdefault", columnDefinition = "BOOLEAN DEFAULT FALSE")
private boolean isdefault;

@OneToMany(mappedBy = "group",
        //cascade = CascadeType.ALL,
        orphanRemoval = true)
private Set<UserGroup> users = new HashSet<>();

public Group() {}

public Group(String name) {
    this.name = name;
}

public Deployment getDeployment() {
    return deployment;
}

public void setDeployment(Deployment deployment) {
    this.deployment = deployment;
}

public String getDescription() {
    return description;
}

public void setDescription(String description) {
this.description = description;
}

public boolean isIsdefault() {
    return isdefault;
}

public void setIsdefault(boolean isdefault) {
    this.isdefault = isdefault;
}

public String getName() {
    return name;
}

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

public Set<UserGroup> getUsers() {
    return users;
}

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

@Override
public boolean equals(Object o) {
    if (this == o) return true;
    if (o == null || getClass() != o.getClass()) return false;
    Group tag = (Group) o;
    return Objects.equals(name, tag.name);
}

@Override
public int hashCode() {
    return Objects.hash(name);
}

}

User:

package model.leitfeld;

import java.util.HashSet;
import java.util.Iterator;
import java.util.Objects;
import java.util.Set;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.OneToMany;
import javax.persistence.Table;
import javax.persistence.UniqueConstraint;

import org.hibernate.annotations.NaturalId;

@Entity(name = “User”)
@Table(name = “lt_user”, schema = “leitfeld2”,
uniqueConstraints = {
@UniqueConstraint(columnNames = “ID”),
@UniqueConstraint(columnNames = “login”)})
//@NaturalIdCache
//@Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
public class User extends TableTemplate {

private static final long serialVersionUID = -2972566452818946612L;

private static final String DEFAULT_URL = "/home";

@NaturalId (mutable = false)
@Column(name = "login", length = 100, updatable = false, unique = true, nullable = false)
private String login;

@Column(name = "pwd", length = 100, nullable = false)
private String pwd;

@Column(name = "timezone", length = 100, nullable = false)
private String timezone;

@Column(name = "name", length = 100, nullable = false, updatable = false)
private String surname;

@Column(name = "surname", length = 100, nullable = false, updatable = false)
private String name;

@Column(name = "email", length = 100)
private String email;

@Column(name = "mobile", length = 100)
private String mobile;

@Column(name = "createdby", columnDefinition = "INTEGER DEFAULT 0", updatable = false)
private int createdby;

@Column(name = "appadmin", columnDefinition = "BOOLEAN DEFAULT FALSE", updatable = false)
private boolean appadmin;

@Column(name = "startURL", length = 100, nullable = false)
private String starturl;

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = “languageID”, nullable = false)
private Language language;

@OneToMany(
        mappedBy = "user",
        //cascade = CascadeType.ALL,
        orphanRemoval = true)
private Set<UserGroup> groups = new HashSet<>();

public User() {
    starturl = DEFAULT_URL;
}

public User(String login) {
    this.login = login;
    starturl = DEFAULT_URL;
}

public String getStarturl() {
    return starturl;
}

public void setStarturl(String starturl) {
    this.starturl = starturl;
}

public String getLogin() {
    return login;
}

public void setLogin(String login) {
    this.login = login;
}

public String getPwd() {
    return pwd;
}

public void setPwd(String pwd) {
    this.pwd = pwd;
}

public String getSurname() {
    return surname;
}

public void setSurname(String surname) {
    this.surname = surname;
}

public String getName() {
    return name;
}

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

public String getEmail() {
    return email;
}

public void setEmail(String email) {
    this.email = email;
}

public String getMobile() {
    return mobile;
}

public void setMobile(String mobile) {
    this.mobile = mobile;
}

public int getCreatedby() {
    return createdby;
}

public void setCreatedby(int createdby) {
    this.createdby = createdby;
}

public boolean isAppadmin() {
    return appadmin;
}

public void setAppadmin(boolean appadmin) {
    this.appadmin = appadmin;
}

public Language getLanguage() {
    return language;
}

public void setLanguage(Language language) {
this.language = language;
}

public String getTimezone() {
    return timezone;
}

public void setTimezone(String timezone) {
    this.timezone = timezone;
}

public Set<UserGroup> getGroups() {
    return groups;
}

public void setGroups(Set<UserGroup> groups) {
    this.groups = groups;
}

public void addGroup(Group group) {
    UserGroup userGroup = new UserGroup(this, group);
    groups.add(userGroup);
    group.getUsers().add(userGroup);
}

public void removeGroup(Group group) {
    for (Iterator<UserGroup> iterator = groups.iterator(); iterator.hasNext();) {
        UserGroup userGroup = iterator.next();
        if (userGroup.getUser().equals(this) && userGroup.getGroup().equals(group)) {
            iterator.remove();
            userGroup.getGroup().getUsers().remove(userGroup);
            userGroup.setUser(null);
            userGroup.setGroup(null);
        }
    }
}

@Override
public boolean equals(Object o) {
    if (this == o) return true;
    if (o == null || getClass() != o.getClass()) return false;
    User user = (User) o;
    return Objects.equals(login, user.login);
}

@Override
public int hashCode() {
    return Objects.hash(login);
}

}

The Java error was an exception while creating method, but only catched by Eclipse.


#4

So, this is not a Hibernate problem.