Skip to main content

JPA: how to alter relation without fetching entire entities?

8 replies [Last post]
Anonymous

Hi there,
is there any way to alter some relation without fetching entire entities?
For example, lets consider this:
person contains address:
class Person:
....
@OneToMany
Set addresses;
....

Now, if I know certain address to remove from person, I have to:
1) fetch person:
Person p = em.find(Person.class, personId);

2) fetch collection of addresses that person contains:
Set addresses = p.getAddresses();

3) get address reference:
Address addressToRemove = em.getReference(Address.class, addressId);

4) remove addressToRemove from collection:
addresses.remove(addressToRemove);

If Address would contain Set persons relation field, then one
would have to fetch address as well and remove "p" from that
collection...

As one can see, step 1,2 and 3 requires massive database
communication, HUGE amount of data does need to be fetched only to
accomplish VERY simple task, in SQL it would bee something like:
DELETE FROM PersonAddresses WHERE person_id = ? and address_id = ?

Is there such an equivalent in JPA?

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@glassfish.dev.java.net
For additional commands, e-mail: users-help@glassfish.dev.java.net

Reply viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.
Witold Szczerba

2007/5/21, glassfish@javadesktop.org :
> If you have the addressId, why not simply remove it like you do in steps 3 and 4? Why fetch the entire list at all?

If I remove that address using em.remove(addressToRemove) then it will
still remain as an object in collection returned by
person.getAddresses() (assuming that entity is hold by L2 cache).
That will eventually result in some exception, when TopLink finds out,
there is address somewhere in L2 cache that does not exist anymore.

>
> If it's going through a join table, then there may be an issue, but you have it listed as a OneToMany, but you don't have a join table. In this simple case, simply removing the address directly should work fine.
>
> Now, updating the join table is much more of a trick, as the join table entries themselves aren't promoted to the model tier at all, and thus really have no exposure outside of the contain object. And you can't easily just call native sql to do the job without flushing out the entire 2nd tier cache, which in turn may be more expensive than loading the relationship itself.
>
> Someone else would have to chime in regarding the affects on the 2nd tier cache if you went after the DB directly (DELETE FROM JOINTABLE WHERE personid = xxx and addressid = yyy).
> [Message sent by forum member 'whartung' (whartung)]
>
> http://forums.java.net/jive/thread.jspa?messageID=218218
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@glassfish.dev.java.net
> For additional commands, e-mail: users-help@glassfish.dev.java.net
>
>

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@glassfish.dev.java.net
For additional commands, e-mail: users-help@glassfish.dev.java.net

mf125085
Offline
Joined: 2005-03-29

Hi Witold,

I assume that your question is about a uni-directional relationship
mapped to a join table. There's currently no way in JPA to remove join
table entries by bulk operations, as join tables are not exposed in
JPA.

The performance of the steps mentioned in your post depends on the JPA
persistence provider. TopLink usually caches relationship fields, so
it might cache the address collection internally, and there should be
no performance penalty retrieving it. Persons and addresses are also
cached, and there should be no database access to get the object
references.

Please try removing the addresses in memory as you described, and
check what queries are generated. The removals from the join table
will be executed at transaction commit.

-- markus.

whartung
Offline
Joined: 2003-06-13

If you have the addressId, why not simply remove it like you do in steps 3 and 4? Why fetch the entire list at all?

If it's going through a join table, then there may be an issue, but you have it listed as a OneToMany, but you don't have a join table. In this simple case, simply removing the address directly should work fine.

Now, updating the join table is much more of a trick, as the join table entries themselves aren't promoted to the model tier at all, and thus really have no exposure outside of the contain object. And you can't easily just call native sql to do the job without flushing out the entire 2nd tier cache, which in turn may be more expensive than loading the relationship itself.

Someone else would have to chime in regarding the affects on the 2nd tier cache if you went after the DB directly (DELETE FROM JOINTABLE WHERE personid = xxx and addressid = yyy).

mvatkina
Offline
Joined: 2005-04-04

As you correctly noted, there is no JPA artifact that corresponds to the join table. Which means that you can execute such query only using a JDBC connection which has no affect on any internal state, PCtxt or 2-nd level cache (I don't think the spec supports executing an update or delete using native query, but even if it does, it doesn't parse such query to understand what it was doing).

Marina Vatkina

Hi Witold,

You can call addressToRemove.getPersons().clear() to clear the other side. There
might be persistence providers who can help you with changing relationships
without fetching all related instances, but it's beyond the spec. If you use
TopLink Essentials, it's very important to always maintain all the relationships
from all sides because the 2-nd level cache stores it in the same way as the EM
knew it at the time of commit, and it's being copied over to the new EM (PCtx)
as-is (i.e. no relationship management performed for you by the provider).

regards,
-marina

Witold Szczerba wrote:
> Hi there,
> is there any way to alter some relation without fetching entire entities?
> For example, lets consider this:
> person contains address:
> class Person:
> ....
> @OneToMany
> Set

addresses;
> ....
>
> Now, if I know certain address to remove from person, I have to:
> 1) fetch person:
> Person p = em.find(Person.class, personId);
>
> 2) fetch collection of addresses that person contains:
> Set
addresses = p.getAddresses();
>
> 3) get address reference:
> Address addressToRemove = em.getReference(Address.class, addressId);
>
> 4) remove addressToRemove from collection:
> addresses.remove(addressToRemove);
>
> If Address would contain Set persons relation field, then one
> would have to fetch address as well and remove "p" from that
> collection...
>
> As one can see, step 1,2 and 3 requires massive database
> communication, HUGE amount of data does need to be fetched only to
> accomplish VERY simple task, in SQL it would bee something like:
> DELETE FROM PersonAddresses WHERE person_id = ? and address_id = ?
>
> Is there such an equivalent in JPA?
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: users-unsubscribe@glassfish.dev.java.net
> For additional commands, e-mail: users-help@glassfish.dev.java.net
>

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@glassfish.dev.java.net
For additional commands, e-mail: users-help@glassfish.dev.java.net

Witold Szczerba

> You can call addressToRemove.getPersons().clear() to clear the other side. There

No, I cant:
1) address does not know about person, so there is no getPersons()
2) even if address would have getPersons() collection, I still cannot
use that, because:
2a) that would clear all persons from address, not the only one I want
2b) even if I would like to remove every person from that address, I
would have to em.find each person that was in that collection and
search that address inside getAddresses() collection for EACH person..
that means huge, multimega extra massive amount of data that need to
be queried and downloaded from database, where in SQL there is only
one, tiny
"DELETE FROM >jointable< where X AND Y"
or
"UPDATE forExample:Address where forExample:person_id=X"

The only reasonable solution for that would be an option to clear
selected entity from L2cache, so I could update database and clear
from L2 only somehow related entities.

Right now, there is NO acceptable solution for altering entities
relationship, if one have to do it on large number of entities. If I
want to remove certain addresses from, lets say 30000 persons, there
might be 100 or 200k queries send to database.

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@glassfish.dev.java.net
For additional commands, e-mail: users-help@glassfish.dev.java.net

mvatkina
Offline
Joined: 2005-04-04

You can prefetch Persons for those Addresses that you need to delete if you do WHERE AddressId IN(x, y, z, ...). Then you won't go to the database for any more selects, and if a provider supports batching, all the updates will not take too long.

But yes, there is no easy way to do it in a single step.

Witold Szczerba

2007/5/22, glassfish@javadesktop.org :
> You can prefetch Persons for those Addresses that you need to delete if you do WHERE AddressId IN(x, y, z, ...). Then you won't go to the database for any more selects, and if a provider supports batching, all the updates will not take too long.

What do you mean by "WHERE Addrss in (x,y,z...)"
If I have few hundreds or few thousands entities to alter, how am I
suppose to build such a query? Using StringBuilder which will be few
thousands characters long? Is it going to go through entity manager?
If "Person" entity is big, is it going to fit in memory?
That will certainly not work.

>
> But yes, there is no easy way to do it in a single step.

It is actually very common task one might need to do in any database
application and it is not going to work using JPA.

All this JPA works only in tutorials, not in real apps. The problem is
that I've trusted JPA already and now I have huge problems and
performance issues :/

My DTO -> Entity methods of service beans are even 100 lines long just
to map quite simple FirmDTO to be "translated" and merged into "Firm"
entity... (do you want me to paste such a method here?) I am hopeless.

---------------------------------------------------------------------
To unsubscribe, e-mail: users-unsubscribe@glassfish.dev.java.net
For additional commands, e-mail: users-help@glassfish.dev.java.net