Recommended Composite Key strategies.

[buck...@gmail.com]

Hi, Rob:

I couldn't find any documentation on how to annotate composite keys.  There seem to be three ways of annotating composite keys as described here.  Which one does ebean support and which one do you recommend?

There are three strategies to use a compound primary key:

• Mark it as @Embeddable and add to your entity class a normal property for it, marked with @Id.
• Add to your entity class a normal property for it, marked with @EmbeddedId.
• Add properties to your entity class for all of its fields, mark them with @Id,and mark your entity class with @IdClass, supplying the class of your primary key class.

Thanks,

Buck

[buck...@gmail.com]

I am able to use @IdClass to handle composite keys and it is working fairly well on a single entity class.   However, when using query bean to eagerly fetch the related entities, I ran into an error "ORA-00911: invalid character".  The use case contains two classes Parent and Child.

public class Parent {

  @Id int key1;

  @Id int key2;

  @OneToMany(cascade = CascadeType.ALL)

  @JoinColumns({@JoinColumn(name="key1", referenceColumnName = "key1"), @JoinColumn(name="key2", referenceColumnName = "key2")})

  List<Child> children;

}

public class Child {

  @Id  int key1;

  @Id  int key2;

  @Id  String name;

  int age;

}

The idClass for Parent has fields key1 and key2.  The idClass for Child has fields key1, key2, name.   

When I try to execute

new QParent()

  .key1.eq(123)

  .fetch("children")

  .findList();

I got the following error (after removing many other lines of the stacktrace).  The query seemed to be correct except the order by phrase at the end "ORDER BY _idClass.key1 and _idClass.key2".  The other old thing is that t0.key1 and t0.key2 appeared twice.

javax.persistence.PersistenceException: Query threw SQLException:ORA-00911: invalid character

Bind value:[123] Query was

SELECT

t0.key1,

t0.key2,

t0.key1,

t0.key2,

t1.key1,

t1.key2,

t1.name,

t1.age,

FROM

PARENT t0

LEFT JOIN CHILD t1 

ON t1.key1 = t0.key1 AND t1.key1 = t0.key2

WHERE t0.key1 = ?

ORDER BY

_idClass.key1,

_idClass.key2 

Caused by: Error : 911, Position : 749, sql = .... REPEATING THE QUERY ABOVE... ,  Error Msg = ORA-00911: invalid character

Thanks for your help.

Buck

[Rob Bygrave]

Logged as https://github.com/ebean-orm/ebean/issues/2317

Fixed in master

I don't think there will be a workaround for this. Are you building ebean locally yourself?

--

---
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.
To view this discussion on the web visit https://groups.google.com/d/msgid/ebean/803d1cd2-3be8-43f8-b5f3-e41338cc2222n%40googlegroups.com.

[buck...@gmail.com]

Rob:

Thanks for fixing it.  We are on version 12.3.1 and we are not building from source.   We will look for the release with the fix when upgrading.

We ran into this issue when trying to implement data access layer for a legacy application and database where many tables have composite primary keys.  Our workaround is to to load parent and children separately.  The use case calls for filtering the parents first then filtering the children (e.g. find the children with age under 21 for the parent who live in Chicago).  Loading them separately is a bit easier to reason about than using joins.

Thanks again.

Buck

[Rob Bygrave]

Cool. Will look to release 12.11.2 in a few days. 

Cheers, Rob.

To view this discussion on the web visit https://groups.google.com/d/msgid/ebean/3fc53c7d-b53f-4ba9-9dc1-02d86ca69230n%40googlegroups.com.