[2.0.3-java] Composite primary key and foreign key

[Eduard]

Hello,

I am trying to have entities with composite primary keys that are also referencing other tables. I have tried many different implementations for this (using IdClass or EmbeddedId, MapsId or JoinColumn...) and so far have been unable to make it work.

Here is what my final implementation looks like:

@Entity

public class Follower extends Model {

@EmbeddedId

public FollowerPK pk;

@MapsId("userId")

@ManyToOne

public User user;

@MapsId("followerId")

@ManyToOne

public User follower;

}

@Embeddable

public class FollowerPK implements Serializable{

public Long userId;

public Long followerId;

        

        ..... equals, hashcode and the constructor are also implemented

}

Everything compiles and the evolution script is created correctly (with no duplicate columns and the right primary key and foreign key constraints), but when I try to save an entity that extends the model class with .save() or when I try to execute a prepared statement (there is no anorm for java) I have the following error: ERROR executing DML bindLog[] error[Column 'user_id' specified twice]. 

As I have said I have tried many different implementations of this:

1. using IdClass
2. using JoinColumn instead of MapsId
3. using both MapsId and JoinColumn
4. having: public User user; public Follower follower; in the FollowerPK class.
   

None of them have worked. Does anyone have composite primary keys that also reference other tables and has managed to make it work? I am using the standard Ebean ORM.

I know I could generate an autogenerated id for those entities but for performance reasons I would rather not. 

I am using Innodb for data storage (I guess as most people who use a relational database); if you read the documentation you will find the following: 

"With the InnoDB storage engine, the table data is physically organized to do ultra-fast lookups and sorts based on the primary key column or columns."

Therefore I think in many situations having an autogenerated index (whereby the data would be organized physically in chronological (insert) order) could lead to poor performance (even if you have indexes for your queries). Imagine you have your own version of google finance; one way to implement this would be to have a table asset_price that references an asset (apple share for example) and that has a price for a specific date. Something of the sort:

create table asset_price (

 asset_id bigint,

 date datetime,

 price_in_cents integer);

If you have as id an autogenerated incremented value for this table and you have many different assets in your application when you execute the following query: select * from asset_price where asset_id=1 and datetime>'2012-10-11 00:9:00' (this query could be used to paint a chart of the evolution of the asset price during the day) you will probably have many i/o disk operations (since the distance in the disk between an asset_price and the previous one for a specific asset will be very big). On the other hand if you have as primary key (asset_id, date) you will just have a few i/o disk operations, as data will be "physically organized to do ultra-fast lookups and sorts based on the primary key column or columns". Since the bottleneck to scale most applications is the database I think it would be extremely valuable to know how to do this.

Thanks in advance for your help.

[Vikram]

Hi

It is not compiling as the field userId is specified twice in the mapping, once in the composite key and once in your entity. Create the association with a reference to the field in composite key. Try the following-

@ManyToOne(fetch = FetchType.LAZY)

@JoinColumn(name = "user_id", insertable = false, updatable = false)

public User user;

Vikram

[Eduard]

Great, thanks a lot, it works now! 

I actually tried the @JoinColumn annotation but without insertable=false, updatable=false. I guess it was inserting or updating two times the same column.

Eduard

[Pasha]

After several hours of experiments found no any solution except the following:

- create getter / setter for both `user` and `follower` member of Follower class

- in setters: create a new instance of FollowerPK (it should have default constructor and getters / setters too) and set its userId or followerId explicitly to corresponding User.id value.

Regards,

Pavel.

On Mon, Dec 17, 2012 at 3:33 PM, Pavel Boldyrev <wyx...@gmail.com> wrote:

Hi! 

Did anyone resolve this problem? I'm in the same boat :(

Thanks,

Pavel.

On Wednesday, November 14, 2012 8:17:55 AM UTC-5, Stan wrote:

Eduard,

I'm in the same case than you.
I tried to modify my classes with the Vikram's information but I get the following error message during the save/insert operation:

PersistenceException: Matches for the concatinated key columns where not found? I expect that the concatinated key was null, and this bean does not have ManyToOne assoc beans matching the primary key columns?

Here the code source:

@Entity

public class Follower extends Model {

private static final long serialVersionUID = 1L;

@EmbeddedId

public FollowerPK pk;

@ManyToOne(fetch = FetchType.LAZY)

@JoinColumn(name = "user_id", insertable = false, updatable = false)

public User user;

@ManyToOne(fetch = FetchType.LAZY)

@JoinColumn(name = "user_id", insertable = false, updatable = false)

public User follower;

}

@Embeddable

public class FollowerPK implements Serializable{

        private static final long serialVersionUID = 1L;

        public Long userId;

public Long followerId;

       
public FollowerPK(Long userID, Long followerId) {
this.userId = userId;
this.followerId = followerId;
}

@Override
public boolean equals(Object obj) {
if (obj == null) {
return false;
}
if (getClass() != obj.getClass()) {
return false;
}
final FollowerPK other = (FollowerPK) obj;
if ((this.userId == null) ? (other.userId != null) : !this.userId.equals(other.userId)) {
return false;
}
if ((this.followerId == null) ? (other.followerId != null) : !this.followerId.equals(other.followerId)) {
return false;
}
return true;
}

    @Override
    public int hashCode() {
        int hash = 3;
        hash = 89 * hash + (this.userId != null ? this.userId.hashCode() : 0);
        hash = 89 * hash + (this.followerId != null ? this.followerId.hashCode() : 0);
        return hash;
    }

}

There is something wrong ... but I can't see it :-( Any idea ?
Thanks a lot.

--
 
 

[norbert...@webvalto.hu]

Hello!

@Entity

public class Follower extends Model {

@EmbeddedId

public FollowerPK pk;

@ManyToOne

        @JoinColumn(name = "userId")

public User user;

@ManyToOne

        @JoinColumn(name = "followerId")

public User follower;

}

@Embeddable

public class FollowerPK implements Serializable{

        @Column(name = "userId")

public User userId;

        @Column(name = "followerId")

public User followerId;

        

        ..... equals, hashcode and the constructor are also implemented

}

Try this :)

Regards