Duplicate columns when using EmbeddedId with a ManyToOne mapping with Ebean

Question

I have a model called "EventCheckin" which has a ManyToOne mapping to an "Event" and a "User". The PrimaryKey of the "EventCheckin" table is the id of the user and the id of the event. I'm trying to represent this using an "EmbeddedId" in my EventCheckin model but when I attempt to save an EventCheckin it tries to put the user_id and event_id values into the table twice which obviously fails:

Caused by: org.h2.jdbc.JdbcSQLException: Duplicate column name "USER_ID"; SQL statement:
insert into eventCheckin (event_id, user_id, latitude, longitude, user_id, event
_id) values (?,?,?,?,?,?) [42121-158]

EventCheckin class:

@Entity
@Table(name="eventCheckin")
public class EventCheckin extends Model
{
    @EmbeddedId public CheckinId id;

    @MapsId("userId")
    @JoinColumn(name="user_id")
    @ManyToOne public User user;

    @MapsId("eventId")
    @JoinColumn(name="event_id")
    @ManyToOne public Event event;

    .....
}

CheckinId EmbeddedId class::

@Embeddable 
public class CheckinId implements Serializable
{
    public Long eventId;  
    public String userId;
    .....
}

And my database table for EventCheckin is defined as this:

create table eventCheckin (
    user_id                   varchar(255) not null,
    event_id                  bigint not null,
    latitude                  float,
    longitude                 float,
    constraint pk_eventCheckIn primary key (user_id,event_id),
    foreign key (user_id) references user (email),
    foreign key (event_id) references event (id)
);

Clarissa

This is a schoolbook example of @MapsId, written correctly, and should work for both OpenEJB and Hibernate. – Overspread

Answer 1

It looks like you try to do same thing via @MapsId and @EmbeddedId. One (working) option is to go for IdClass (equals, hashcode, extra attributes etc are cut away):

    @Entity
    public class User {
        @Id public String id;
    }

    @Entity
    public class Event {
        @Id public long id;
    }

    public class CheckinId implements Serializable {
        public Long event;
        public String user;
    }

    @Entity
    @IdClass(CheckinId.class)
    @Table(name="eventCheckin")
    public class EventCheckin {

        @Id
        @JoinColumn(name="user_id")
        @ManyToOne public User user;

        @Id
        @JoinColumn(name="event_id")
        @ManyToOne public Event event;
    }

Daloris

Thanks! But when I do that I still have pretty much the same problem. When trying to retrieve an EventCheckin I get an error which seems to show that the select is not performing a join of the tables: Caused by: org.h2.jdbc.JdbcSQLException: Column "T0.NAME" not found; SQL stateme nt: select t0.name c0, t0.password c1, t0.name c2, t0.description c3, t0.latitude c4 , t0.longitude c5 from eventCheckin t0 where t0.user_id = ? [42122-158] – Clarissa

That's odd. Maybe this problem is then something specific for Play/Ebean. With pale Hibernate/EclipseLink above code works. – Daloris

I'm beginning to think it might be. For now I've added an ID column to EventCheckin but it's frustrating that what I'm trying to do isn't work. thanks again! – Clarissa

Answer 2

This error occurs because columns user_id and event_id are used by composite key and at the same time they are used by @ManyToOne mappings. The solution is to add insertable = false, updatable = false attributes to @Joincolumn annotaion. Here is working code for this solution:

EventChekin.java:

@Entity
@Table(name="eventCheckin")
public class EventCheckin extends Model {

    public EventCheckin() {
        id = new CheckinId();
    }

    @EmbeddedId 
    public CheckinId id;

    @ManyToOne
    @MapsId("userId")
    @JoinColumn(name="user_id", insertable = false, updatable = false)
    private User user;

    @ManyToOne
    @MapsId("eventId")
    @JoinColumn(name="event_id", insertable = false, updatable = false)
    private Event event;

    public void setUser(User aUser) {
        user = aUser;
        id.userId = aUser.getId();
    }

    public void setEvent(Event aEvent) {
        event = aEvent;
        id.eventId = aEvent.getId();
    }
}

Checkin.java

@Embeddable 
public class CheckinId implements Serializable {

    public Long eventId;
    public String userId;

    @Override
    public int hashCode() {
        return eventId.intValue() + userId.length();
    }

    @Override
    public boolean equals(Object obj) {
        if (this == obj) 
            return true;
        CheckinId c = (CheckinId)obj;
        if(c==null)
        return false;
        if (c.eventId.equals(eventId) && c.userId.equals(userId)) {
            return true;
        }
        return false;
    }
}

Lesotho