Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Accessing foreign key on @ManyToOne - how to avoid DB roundtrip?
Accessing foreign key on @ManyToOne - how to avoid DB roundtrip? [message #767185] Sat, 17 December 2011 09:02 Go to next message
Thipor Kong is currently offline Thipor KongFriend
Messages: 2
Registered: December 2011
Junior Member
Hello,

I observe unnecessary DB accesses when reading only the foreign key of some @ManyToOne association (resp. the primary key of some dependent object). I've checked with EclipseLink 2.1.1, 2.3.2. Hibernate doesn't show this behaviour.

Is there a way to avoid loading the dependent object if I only want to know it's (foreign) key?

This question (stackoverflow.com/questions/3941797/how-can-i-retrieve-the-foreign-key-from-a-jpa-manytoone-mapping-without-hitting/3943372#3943372) was already discussed on Stackoverflow[/url], but no conlusive solution for Eclipselink was provided.

I have two entities Book-n:1->Library:

@Entity
public class Book {
	private long id;
	private Library library;

	@Id
	public long getId() {
		return id;
	}

	public void setId(long id) {
		this.id = id;
	}

	@ManyToOne(cascade=CascadeType.ALL,fetch=FetchType.LAZY)
	public Library getLibrary() {
		return library;
	}

	public void setLibrary(Library library) {
		this.library = library;
	}
}


@Entity
public class Library {
	private long id;
	private String name;
	
	@Id
	public long getId() {
		return id;
	}

	public void setId(long id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}
}


Now, I would like to get the foreign key of the Library. The standard way would simple property access:

Book book = (Book)em.find(Book.class, bookId);
System.out.println("Book: " + book);

long libraryId = book.getLibrary().getId();
System.out.println("Library ID: " + libraryId);


This should'nt need an extrace DB roundtrip, because the foreign key is already available. Nevertheless, the whole Library object is (lazily) loaded (of course I'm using weaving):

[EL Fine]: 2011-12-17 09:31:52.229--ServerSession(394215580)--Connection(1083162319)--Thread(Thread[main,5,main])--SELECT ID, LIBRARY_ID FROM BOOK WHERE (ID = ?)
	bind => [4711]
Book: a548158.jpa.test.data.Book@5189f854
[EL Fine]: 2011-12-17 09:31:52.556--ServerSession(394215580)--Connection(1083162319)--Thread(Thread[main,5,main])--SELECT ID, NAME FROM LIBRARY WHERE (ID = ?)
	bind => [-4310096167072060697]
Library ID: -4310096167072060697


Are there any ways (specific API, configuration, ...) to avoid this unnecessary DB access to the Library DB table? I've checked with Hibernate using extactly the same code, and there it works flawlessly.

Also, just creating a reference to a Library and retrieving the primary key doesn't involve a DB roundtrip:

em.getReference(Library.class, 4711).getId(); // no DB roundtrip here


Thanks and regards,

Thipor

[Updated on: Sat, 17 December 2011 17:20]

Report message to a moderator

Re: Accessing foreign key on @ManyToOne - how to avoid DB roundtrip? [message #768228 is a reply to message #767185] Mon, 19 December 2011 19:49 Go to previous messageGo to next message
James Sutherland is currently offline James SutherlandFriend
Messages: 1939
Registered: July 2009
Location: Ottawa, Canada
Senior Member

The difference is that when you call getLibrary() on Hibernate it returns a proxy, not the real object. When you access it, it then loads the real object and wraps it and forwards requests. There are lots of issues with this approach (i.e. object identity/==, instanceof, etc.), but it does allow them to optimize the getId() call.

In EclipseLink, you always deal with real objects, never proxies (well, except on collections...). When you access the getLibrary() method this method has been weaved to first load the reference object, and you get a real object back.

When you call em.getReference() it returns a real object, but with a fetch-group only including the id, when you access any other field the weaving checks will fill in the rest of the object.

In short, I would recommend you just map the foreign key field as a Basic as well as the ManyToOne if you want access to it without accessing the ManyToOne.

You may also be able to access the weaved field for library that hold a DatabaseValueHolder object that contains a DatabaseRow that contains the foreign key field.


James : Wiki : Book : Blog : Twitter
Re: Accessing foreign key on @ManyToOne - how to avoid DB roundtrip? [message #997350 is a reply to message #767185] Sun, 06 January 2013 19:19 Go to previous messageGo to next message
Andrew Ward is currently offline Andrew WardFriend
Messages: 2
Registered: January 2013
Junior Member
Hi

I have been trying to avoid the unnecessary round-trip as raised by Thipor Kong.

James, you say:
Quote:
In short, I would recommend you just map the foreign key field as a Basic as well as the ManyToOne if you want access to it without accessing the ManyToOne.


I have mapped the primary key as a second attribute (in Thipor's example, this would mean adding "private long libraryID" to Book), however, EclipseLink raised the following exception:

Exception Description: Multiple writable mappings exist for the field [Book.libraryid].  Only one may be defined as writable, all others must be specified read-only.


How do I configure libraryid to be read-only?

(The @ReadOnly annotation is for entities, not attributes).

Why does this matter? Imagine you are processing a large table of transactions. You don't want to force the lazy loading of associated classes (Accounts, Parties, Financial Instruments, etc) just because you need access to the foreign keys.

Thanks
Andrew
Re: Accessing foreign key on @ManyToOne - how to avoid DB roundtrip? [message #997513 is a reply to message #767185] Mon, 07 January 2013 22:53 Go to previous messageGo to next message
Andrew Ward is currently offline Andrew WardFriend
Messages: 2
Registered: January 2013
Junior Member
Hi

By setting insertable and updatable to false, I have been able to make the Library attribute effectively read-only, and the "Multiple writable mappings" error has vanished:

@ManyToOne(fetch=FetchType.LAZY) 
@JoinColumn(name="libraryid", referencedColumnName="id",insertable=false,updatable=false)
private Library library;


This has the desired result. The Library table is now only queried if I call getLibrary. If I just call Book.getLibraryId() there is no round trip!

I am currently debugging some use cases around changing the libraryId, and observing whether the lazily-loaded Library re-loads/re-freshes. (E.g., Book A is in Library X, I then move A to Library Y via Book.setLibraryId() - now, would we expect the next call to getLibrary() to spot the reference has changed, and lazily load Library Y? Well, currently it seems to keep returning X...). I will perform some more experiments before posting further questions.

Thanks
Andrew
Re: Accessing foreign key on @ManyToOne - how to avoid DB roundtrip? [message #997659 is a reply to message #997350] Mon, 07 January 2013 06:05 Go to previous messageGo to next message
Tom Eugelink is currently offline Tom EugelinkFriend
Messages: 807
Registered: July 2009
Senior Member
You can set this on the column annotation with insertable and updatable.

http://www.objectdb.com/api/java/jpa/Column

I have not read your whole use case, but about the exception: What you create is two variables referring to the same field in the database. Suppose you set one variable to "2" and the other to "3", which value is written to the database?

On 2013-01-07 00:39, Andrew Ward wrote:
> Hi
>
> I have been trying to avoid the unnecessary round-trip as raised by Thipor Kong.
>
> James, you say:
> Quote:
>> In short, I would recommend you just map the foreign key field as a Basic as well as the ManyToOne if you want access to it without accessing the ManyToOne.
>
>
> I have mapped the primary key as a second attribute (in Thipor's example, this would mean adding "private long libraryID" to Book), however, EclipseLink raised the following exception:
>
> Exception Description: Multiple writable mappings exist for the field [Book.libraryid]. Only one may be defined as writable, all others must be specified read-only.
>
> How do I configure libraryid to be read-only?
>
> (The @ReadOnly annotation is for entities, not attributes).
>
> Why does this matter? Imagine you are processing a large table of transactions. You don't want to force the lazy loading of associated classes (Accounts, Parties, Financial Instruments, etc) just because you need access to the foreign keys.
>
> Thanks
> Andrew
>
Re: Accessing foreign key on @ManyToOne - how to avoid DB roundtrip? [message #1235408 is a reply to message #768228] Fri, 24 January 2014 09:22 Go to previous message
Marco Quaranta is currently offline Marco QuarantaFriend
Messages: 6
Registered: July 2013
Junior Member
James Sutherland wrote on Mon, 19 December 2011 14:49

You may also be able to access the weaved field for library that hold a DatabaseValueHolder object that contains a DatabaseRow that contains the foreign key field.

Hello James, how can I access the weaved field? Using reflection? Is there a better way?
Thanks
Previous Topic:Problem with Bidirectional relationship and Cache
Next Topic:GF4+MOXy+@XmlNamedObjectGraph to generate JSON
Goto Forum:
  


Current Time: Sun Nov 23 14:46:46 GMT 2014

Powered by FUDForum. Page generated in 0.02779 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software