Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Mapping / history table with active entry marked
Mapping / history table with active entry marked [message #760903] Mon, 05 December 2011 09:46 Go to next message
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 #761033 is a reply to message #760903] Mon, 05 December 2011 14:43 Go to previous messageGo to next message
James Sutherland is currently offline 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
Re: Mapping / history table with active entry marked [message #761035 is a reply to message #760903] Mon, 05 December 2011 14:43 Go to previous messageGo to next message
James is currently offline 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/
Re: Mapping / history table with active entry marked [message #761292 is a reply to message #761033] Tue, 06 December 2011 03:49 Go to previous messageGo to next message
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 #761294 is a reply to message #761033] Tue, 06 December 2011 03:49 Go to previous messageGo to next message
Thomas is currently offline Thomas
Messages: 151
Registered: July 2009
Senior 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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Thomas is currently offline 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 Go to previous messageGo to next message
James Sutherland is currently offline 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
Re: Mapping / history table with active entry marked [message #762680 is a reply to message #761503] Thu, 08 December 2011 08:58 Go to previous messageGo to next message
James is currently offline James
Messages: 272
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 : http://wiki.eclipse.org/EclipseLink : http://en.wikibooks.org/wiki/Java_Persistence : http://java-persistence-performance.blogspot.com/
Re: Mapping / history table with active entry marked [message #763230 is a reply to message #762680] Fri, 09 December 2011 06:42 Go to previous message
Thomas
Messages: 9
Registered: December 2011
Junior Member
Hi James,

thank you for the information. I have opened bug 366156.

Thomas
Re: Mapping / history table with active entry marked [message #763232 is a reply to message #762678] Fri, 09 December 2011 06:42 Go to previous message
Thomas is currently offline Thomas
Messages: 151
Registered: July 2009
Senior Member
Hi James,

thank you for the information. I have opened bug 366156.

Thomas
Previous Topic:NPE in AbstractRecord.get() after failed insert
Next Topic:EclipseLink's multitenant feature with spring
Goto Forum:
  


Current Time: Sun Apr 20 18:09:20 EDT 2014

Powered by FUDForum. Page generated in 0.04698 seconds