Re: [ebean] ManyToMany - Mapping a Join Table with Additional Columns

[Rob Bygrave]

At a quick glace you probably need to use @EmbeddedId rather than two @Id and the @IdClass.

On 17 August 2012 03:26, Rigobert <sglo...@gmail.com> wrote:

Dear ebeaners,

First, I work with ebean throw the Playframework. I encounter a problem.
two classes have a ManyToMany relationship, but the relational join table has additional data. For example if Employee has a ManyToMany with Project but the PROJ_EMP join table also has an IS_TEAM_LEAD column. In this case the best solution is to create a class that models the join table. So a ProjectAssociation class would be created. It would have a ManyToOne to Employee and Project, and attributes for the additional data. Employee and Project would have a OneToMany to the ProjectAssociation.


@Entity
public class Employee {
  @Id
  private long id;
  ...
  @OneToMany(mappedBy="employee")
  private List<ProjectAssociation> projects;
  ...
}


@Entity
public class Project {
  @Id
  private long id;
  ...
  @OneToMany(mappedBy="project")
  private List<ProjectAssociation> employees;
  ...
}


@Entity
@Table(name="PROJ_EMP")
@IdClass(ProjectAssociationId.class)
public class ProjectAssociation {
  @Id
  private long employeeId;
  @Id
  private long projectId;
  @Column("IS_PROJECT_LEAD")
  private boolean isProjectLead;

  @ManyToOne
  @PrimaryKeyJoinColumn(name="EMPLOYEEID", referencedColumnName="ID")
  private Employee employee;

  @ManyToOne
  @PrimaryKeyJoinColumn(name="PROJECTID", referencedColumnName="ID")
  private Project project;
  ...
}

public class ProjectAssociationId implements Serializable {

  private long employeeId;

  private long projectId;
  ...

  public int hashCode() {
    return (int)(employeeId + projectId);
  }

  public boolean equals(Object object) {
    if (object instanceof ProjectAssociationId) {
      ProjectAssociationId otherId = (ProjectAssociationId) object;
      return (otherId.employeeId == this.employeeId) && (otherId.projectId == this.projectId);
    }
    return false;
  }
}

When I execute the sample, I got the following stacktrace :

Caused by: javax.persistence.PersistenceException: ERROR executing DML bindLog[] error[Duplicate column name "USER_ID";
SQL statement:\n insert into UserProperty (user_id, property_id, type, user_id, property_id) values (?,?,?,?,?) [42121-158]]
        at com.avaje.ebeaninternal.server.persist.dml.DmlBeanPersister.execute(DmlBeanPersister.java:116) ~[ebean.jar:na]
        at com.avaje.ebeaninternal.server.persist.dml.DmlBeanPersister.insert(DmlBeanPersister.java:76) ~[ebean.jar:na]
        at com.avaje.ebeaninternal.server.persist.DefaultPersistExecute.executeInsertBean(DefaultPersistExecute.java:91) ~[ebean.jar:na]
        at com.avaje.ebeaninternal.server.core.PersistRequestBean.executeNow(PersistRequestBean.java:527) ~[ebean.jar:na]
        at com.avaje.ebeaninternal.server.core.PersistRequestBean.executeOrQueue(PersistRequestBean.java:557) ~[ebean.jar:na]
        at com.avaje.ebeaninternal.server.persist.DefaultPersister.insert(DefaultPersister.java:404) ~[ebean.jar:na]
Caused by: org.h2.jdbc.JdbcSQLException: Duplicate column name "USER_ID"; SQL statement:
insert into UserProperty (user_id, property_id, type, user_id, property_id) values (?,?,?,?,?) [42121-158]
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:329) ~[h2.jar:1.3.158]
        at org.h2.message.DbException.get(DbException.java:169) ~[h2.jar:1.3.158]
        at org.h2.message.DbException.get(DbException.java:146) ~[h2.jar:1.3.158]
        at org.h2.command.Parser.parseColumnList(Parser.java:780) ~[h2.jar:1.3.158]
        at org.h2.command.Parser.parseInsert(Parser.java:958) ~[h2.jar:1.3.158]
        at org.h2.command.Parser.parsePrepared(Parser.java:375) ~[h2.jar:1.3.158]

For me, the error is logic but I don't know how to perform this case with the ebean framework. Anyone gets some idea or sample ?
Thanks in advance for your help.

[Rigobert]

Thanks for your help, but I received the same error :

@Entity

@Table(name="PROJ_EMP")

@IdClass(ProjectAssociationId.class)

public class ProjectAssociation {

  @EmbeddedId

  public EmployeeProjectPk pk = new EmployeeProjectPk();

  @Column("IS_PROJECT_LEAD")
  private boolean isProjectLead;

  @ManyToOne
  @PrimaryKeyJoinColumn(name="EMPLOYEEID", referencedColumnName="ID")
  private Employee employee;

  @ManyToOne
  @PrimaryKeyJoinColumn(name="PROJECTID", referencedColumnName="ID")
  private Project project;
  ...
}

@Embeddable

public class ProjectAssociationId implements Serializable {

 
  private long employeeId;
 
  private long projectId;
  ...
 
  public int hashCode() {
    return (int)(employeeId + projectId);
  }
 
  public boolean equals(Object object) {
    if (object instanceof ProjectAssociationId) {
      ProjectAssociationId otherId = (ProjectAssociationId) object;
      return (otherId.employeeId == this.employeeId) && (otherId.projectId == this.projectId);
    }
    return false;
  } 
}

The stacktrace :

Caused by: javax.persistence.PersistenceException: ERROR executing DML bindLog[] error[Duplicate column name "USER_ID";

SQL statement:\n insert into UserProperty (user_id, property_id, archetype, user_id, property_id) values (?,?,?,?,?) [42121-158]]

        at com.avaje.ebeaninternal.server.persist.dml.DmlBeanPersister.execute(DmlBeanPersister.java:116) ~[ebean.jar:na]

        at com.avaje.ebeaninternal.server.persist.dml.DmlBeanPersister.insert(DmlBeanPersister.java:76) ~[ebean.jar:na]

        at com.avaje.ebeaninternal.server.persist.DefaultPersistExecute.executeInsertBean(DefaultPersistExecute.java:91) ~[ebean.jar:na]

        at com.avaje.ebeaninternal.server.core.PersistRequestBean.executeNow(PersistRequestBean.java:527) ~[ebean.jar:na]

        at com.avaje.ebeaninternal.server.core.PersistRequestBean.executeOrQueue(PersistRequestBean.java:557) ~[ebean.jar:na]

        at com.avaje.ebeaninternal.server.persist.DefaultPersister.insert(DefaultPersister.java:404) ~[ebean.jar:na]

Caused by: org.h2.jdbc.JdbcSQLException: Duplicate column name "USER_ID"; SQL statement:

insert into UserProperty (user_id, property_id, archetype, user_id, property_id) values (?,?,?,?,?) [42121-158]

        at org.h2.message.DbException.getJdbcSQLException(DbException.java:329) ~[h2.jar:1.3.158]

        at org.h2.message.DbException.get(DbException.java:169) ~[h2.jar:1.3.158]

        at org.h2.message.DbException.get(DbException.java:146) ~[h2.jar:1.3.158]

        at org.h2.command.Parser.parseColumnList(Parser.java:780) ~[h2.jar:1.3.158]

        at org.h2.command.Parser.parseInsert(Parser.java:958) ~[h2.jar:1.3.158]

        at org.h2.command.Parser.parsePrepared(Parser.java:375) ~[h2.jar:1.3.158]

[Gonzalo Oviedo Lambert]

Hola, se me produce el mismo error, es una pena, llevo varias horas invertidas, no puede ser que Eben tenga estos problemas con Play, por favor, alguien haga algo!.

Se me produce lo mismo con @OneToOne @Embeddable and @EmbeddedId Imposible realizar un PK,FK. Tampoco es ;mode=MySql. Igual pasa.

[Brandon Chen]

Does this problem solved? I get same errors and use @EmbeddedId as key based on ebean 4.6.2

[Rob Bygrave]

The developer in this original case tried to use @IdClass and instead should have been using @EmbeddedId.  

So yes, use @EmbeddedId is the answer.

--

---
You received this message because you are subscribed to the Google Groups "Ebean ORM" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ebean+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Brandon Chen]

I found a solution for this problem.

1. use @EmbeddedId and @Embeddable key as Entity Id

2. add insertable = false, updatable = false in @JoinColumn

Thanks Rob.