| Mapping / history table with active entry marked [message #760903] |
Mon, 05 December 2011 09:46  |
Thomas
Messages: 9
Registered: December 2011 |
Junior Member |
|
|
Hi,
I am just starting to get into JPA using EclipseLink. I have the following (legacy) data model:
Table A
--------
m_id PK
Table AB
----------
m_id PK, FK
n_id PK
s_id FK
active BOOL
Table B
--------
s_id PK
value
(A has a "history" of B's where the active entry ist marked by AB.active.)
Now I want to define a mapping for attribute "activeB" in class A which should reference the active entry.
[SQL:
select
b.s_id, b.value
from
A a
join AB ab on a.m_id = ab.m_id and ab.active = true
join B b on b.s_id = ab.s_id
;
]
I have tried different approaches but couldn't find a solution. So I would be very happy if someone could give me a hint.
Thanks for your help
Thomas
|
|
|
|
|
| Re: Mapping / history table with active entry marked [message #761292 is a reply to message #761033] |
Tue, 06 December 2011 03:49  |
Thomas
Messages: 9
Registered: December 2011 |
Junior Member |
|
|
Hi James,
thank you for your answer. I already knew the side you mentioned but I wasn't able to adopt it to my problem. If I'm correct the example at the side mentioned has a 1-M mapping between A and B and a list of actual B's is hold in A by using the DescriptorCustomizer.
In my example there is a 1-M mapping between A and AB, an N-M mapping between AB and B and I want a pointer to the single active B in class A. So I have to take into account the additional table AB when definining a relationship between A and the active entry in B.
If I would adopt the example from the side mentioned I would get a pointer to the active B in class AB (not A).
Sorry if I missed something obvious - I would be glad if someone could show me by example how do get this pointer (active B) in class A instead.
Thomas
|
|
|
|
| Re: Mapping / history table with active entry marked [message #761498 is a reply to message #761035] |
Tue, 06 December 2011 09:52 |
Thomas
Messages: 9
Registered: December 2011 |
Junior Member |
|
|
Hi all,
after playing around some more I got the following "solution"
Entities (excerpt):
=================================
@Entity
@Table(name="A")
@Customizer(ActStateFilter.class)
public class A{
@JoinTable(name = "AB", joinColumns = @JoinColumn(name = "m_id", referencedColumnName = "m_id"), inverseJoinColumns = @JoinColumn(name = "s_id", referencedColumnName = "s_id"))
private B activeB;
}
@Entity
@Table(name="B")
public class B{
@Id
@Column(name="s_id", unique=true, nullable=false)
private Integer sID;
@Column(name="value", length=60)
private String value;
}
DescriptorCustomizer:
=================================
public class ActStateFilter implements DescriptorCustomizer {
@Override
public void customize(ClassDescriptor descriptor) throws Exception {
OneToOneMapping mapping = (OneToOneMapping) descriptor.getMappingForAttributeName("activeB");
ExpressionBuilder eb = new ExpressionBuilder(mapping.getReferenceClass());
Expression exp = eb.getTable("A").getField("m_id").equal(eb.getParameter("M_ID")).
and(eb.getTable("AB").getField("m_id").equal(eb.getTable("A").getField("m_id"))).
and(eb.getTable("AB").getField("s_id").equal(eb.getTable("B").getField("s_id")).
and(eb.getTable("AB").getField("active").equal(true)));
mapping.setSelectionCriteria(exp);
}
}
[BTW: Don't ask how long it took me to figure out that parameter M_ID is expected to be written in uppercase letters...]
Now if I execute the following query:
TypedQuery<A> q = em.createQuery("select a from A a where a.activeB.s_id=10", A.class);
I get all A where the active B's s_id is 10 as expected (hoped for).
But if instead I execute the following query:
TypedQuery<A> q = em.createQuery("select a from A a where a.activeB=:state", A.class);
B state = em.find(B.class, 10);
q.setParameter("state", state);
I get "wrong" results (sorry - didn't yet find out a "pattern" for the wrong results).
[EDIT: I get all A for which there is a B in history with s_id = 10 - regardeless of the active s_id]
Could anybody confirm that the entities and the desriptor customizer shown above are correct?
Could anybody explain why the second query yields wrong results?
Thanks again
Thomas
[Updated on: Tue, 06 December 2011 10:00] Report message to a moderator |
|
|
| Re: Mapping / history table with active entry marked [message #761503 is a reply to message #761033] |
Tue, 06 December 2011 09:52 |
 Thomas
Messages: 151
Registered: July 2009 |
Senior Member |
|
|
Hi all,
after playing around some more I got the following "solution"
Entities (excerpt):
=================================
@Entity
@Table(name="A")
@Customizer(ActStateFilter.class)
public class A{
@JoinTable(name = "AB", joinColumns = @JoinColumn(name = "m_id", referencedColumnName = "m_id"), inverseJoinColumns = @JoinColumn(name = "s_id", referencedColumnName = "s_id"))
private B activeB;
}
@Entity
@Table(name="B")
public class B{
@Id
@Column(name="s_id", unique=true, nullable=false)
private Integer sID;
@Column(name="value", length=60)
private String value;
}
DescriptorCustomizer:
=================================
public class ActStateFilter implements DescriptorCustomizer {
@Override
public void customize(ClassDescriptor descriptor) throws Exception {
OneToOneMapping mapping = (OneToOneMapping) descriptor.getMappingForAttributeName("activeB");
ExpressionBuilder eb = new ExpressionBuilder(mapping.getReferenceClass());
Expression exp = eb.getTable("A").getField("m_id").equal(eb.getParameter("M_ID")).
and(eb.getTable("AB").getField("m_id").equal(eb.getTable("A").getField("m_id"))).
and(eb.getTable("AB").getField("s_id").equal(eb.getTable("B").getField("s_id")).
and(eb.getTable("AB").getField("active").equal(true)));
mapping.setSelectionCriteria(exp);
}
}
[BTW: Don't ask how long it took me to figure out that parameter M_ID is expected to be written in uppercase letters...]
Now if I execute the following query:
TypedQuery<A> q = em.createQuery("select a from A a where a.activeB.s_id=10", A.class);
I get all A where the active B's s_id is 10 as expected (hoped for).
But if instead I execute the following query:
TypedQuery<A> q = em.createQuery("select a from A a where a.activeB=:state", A.class);
B state = em.find(B.class, 10);
q.setParameter("state", state);
I get "wrong" results (sorry - didn't yet find out a "pattern" for the wrong results).
Could anybody confirm that the entities and the desriptor customizer shown above are correct?
Could anybody explain why the second query yields wrong results?
Thanks again
Thomas
|
|
|
| Re: Mapping / history table with active entry marked [message #762678 is a reply to message #761503] |
Thu, 08 December 2011 08:58 |
James Sutherland
Messages: 1834
Registered: July 2009 |
Senior Member |
|
|
The difference between the two is that ``select a from A a where a.activeB=:state`` does and object comparison, where as the other joins through the other object.
EclipseLink make an optimization to compare the foreign keys for object comparisons, it seems this is not taking into account the selection criteria.
Please log a bug for this and vote for it.
You can compare on the id as a workaround.
James : Wiki : Book : Blog
|
|
|
|
|
|
Powered by
FUDForum. Page generated in 0.02661 seconds
] 
Search
Help
Register
Login
Home
