| Mapping / history table with active entry marked [message #760903] |
Mon, 05 December 2011 09:46  |
Eclipse User |
|
|
|
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 #761498 is a reply to message #761035] |
Tue, 06 December 2011 09:52  |
| Eclipse User |
|
|
|
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] by Moderator |
|
|
| Re: Mapping / history table with active entry marked [message #761503 is a reply to message #761033] |
Tue, 06 December 2011 09:52 |
| Eclipse User |
|
|
|
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
|
|
|
|
|
|
|
Powered by
FUDForum. Page generated in 0.05542 seconds
Home