Eclipse Community Forums: EclipseLink » Mapping / history table with active entry marked

Help

Home

Home » Eclipse Projects » EclipseLink » Mapping / history table with active entry marked

Show: Today's Messages :: Show Polls :: Message Navigator

Mapping / history table with active entry marked [message #760903]

Mon, 05 December 2011 14: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

Report message to a moderator

Re: Mapping / history table with active entry marked [message #761033 is a reply to message #760903]

Mon, 05 December 2011 19:43

James Sutherland

Messages: 1939
Registered: July 2009
Location: Ottawa, Canada

Senior Member

You can do this using a DescriptorCustomizer and an Expression, see,

http://wiki.eclipse.org/EclipseLink/Examples/JPA/MappingSelectionCriteria

James : Wiki : Book : Blog : Twitter

Report message to a moderator

Re: Mapping / history table with active entry marked [message #761035 is a reply to message #760903]

Mon, 05 December 2011 19:43

James

Messages: 272
Registered: July 2009

Senior Member

You can do this using a DescriptorCustomizer and an Expression, see,

http://wiki.eclipse.org/EclipseLink/Examples/JPA/MappingSelectionCriteria
--
James : http://wiki.eclipse.org/EclipseLink : http://en.wikibooks.org/wiki/Java_Persistence : http://java-persistence-performance.blogspot.com/

Report message to a moderator

Re: Mapping / history table with active entry marked [message #761292 is a reply to message #761033]

Tue, 06 December 2011 08: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

Report message to a moderator

Re: Mapping / history table with active entry marked [message #761294 is a reply to message #761033]

Tue, 06 December 2011 08:49

Thomas

Messages: 151
Registered: July 2009

Senior Member

Report message to a moderator

Re: Mapping / history table with active entry marked [message #761498 is a reply to message #761035]

Tue, 06 December 2011 14: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 15: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 14: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

Report message to a moderator

Re: Mapping / history table with active entry marked [message #762678 is a reply to message #761503]

Thu, 08 December 2011 13:58

James Sutherland

Messages: 1939
Registered: July 2009
Location: Ottawa, Canada

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 : Twitter

Report message to a moderator