524 words

How to use JOIN FETCH?

#1
2012-08-21 13:41

The ObjectDB manual (http://www.objectdb.com/java/jpa/query/jpql/from) gives an example of using JOIN FETCH to avoid excessive round trips to the database:

SELECT c FROM Country c JOIN FETCH c.capital

I've tried a few variants of the following example, and haven't been able to get this to work.  See the code below: I'm looking for a single line to be printed out (for the single person in the database), with 2 addresses.  Instead, I get two lines printed (the same person, printed twice), with the address shown as null in each case.  If I don't close the entity manager, then I get the correct addresses (obviously fetched lazily), but still 2 results.  What should I be doing to get what I want out of this query?

Note that I need to get the fetch mode specified in the query, not in the entity annotations, since I'm looking at ObjectDB as a possible replacement for an existing system where lots of queries run over the same set of objects, returning different sets of data.  The same relationship will need to be retrieved both eagerly and lazily in different queries.

package testing;
 
import java.io.File;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
 
import javax.persistence.Entity;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.MapKeyTemporal;
import javax.persistence.OneToMany;
import javax.persistence.Persistence;
import javax.persistence.TemporalType;
import javax.persistence.TypedQuery;
 
public class Eager2 {
public static void main(String[] args) throws Exception {
  File file = new File("eager.odb");
  if (file.exists())
  {
   file.delete();
  }
  file.createNewFile();
  EntityManagerFactory emf = Persistence.createEntityManagerFactory("eager.odb");
  EntityManager em = emf.createEntityManager();
  em.getTransaction().begin();
  Person p = new Person();
  p.setName("Peter Smith");
  Address address1 = new Address("123 Acacia Avenue");
  p.addAddress(new SimpleDateFormat("yyyyMMdd").parse("20000101"),address1);
  em.persist(address1);
  Address address2 = new Address("654 Olive Road");
  p.addAddress(new SimpleDateFormat("yyyyMMdd").parse("20100101"),address2);
  em.persist(address2);
  em.persist(p);
  em.getTransaction().commit();
  TypedQuery<Address> aq = em.createQuery("select a from Address a",Address.class);
  System.out.println(aq.getResultList()); // Prints out both addresses OK.
  TypedQuery<Person> q = em.createQuery(
        "select p from Person p join fetch p.addressHistory",Person.class);
  List<Person> results = q.getResultList();
  em.close();
  emf.close();
  for (Person person : results) // Executes loop body twice.
  {
   System.out.println("PERSON:" + person); // Address is shown as null.
  }
}
 
@Entity
public static class Person
{
  @Id @GeneratedValue private long id;
  private String name;
  @OneToMany
  @MapKeyTemporal(TemporalType.DATE)
  private Map<Date,Address> addressHistory = new HashMap<Date,Address>();
 
  public long getId() {
   return id;
  }
  public String getName() {
   return name;
  }
  public void setName(String name) {
   this.name = name;
  }
  public Map<Date,Address> getAddressHistory() {
   return addressHistory;
  }
  public void addAddress(Date start,Address address) {
   addressHistory.put(start,address);
  }
  public String toString() {
   return name + ":" + addressHistory;
  }
}
 
@Entity
public static class Address {
  @Id @GeneratedValue private long id;
  private String address;
 
  public long getId() {
   return id;
  }
  public Address(String address) {
   this.address = address;
  }
  public String getAddress()
  {
   return address;
  }
  public String toString() {
   return address;
  }
}
}
JonMoore
JonMoore's picture
Joined on 2012-08-21
User Post #1
#2
2012-08-21 18:48

If the only problem is result duplication then this is normal for JOIN FETCH and you can remove duplication by using SELECT DISTINCT.

ObjectDB Support
ObjectDB - Fast Object Database for Java (JPA/JDO)
support
support's picture
Joined on 2010-05-03
User Post #1,206
#3
2012-08-22 09:03

The main problem is that it doesn't FETCH.  If I close the entity manager after executing the query, the address history map referenced in the FETCH clause is null.  To get the object graph I want is going to incur the N+1 selects problem, which isn't going to be acceptable from a performance point of view.

JonMoore
JonMoore's picture
Joined on 2012-08-21
User Post #2
#4
2012-08-22 20:47

A similar problem is discussed in this forum thread and this issue, and a fix was added to build 2.4.1_06.

What ObjectDB version are you using?

ObjectDB Support
ObjectDB - Fast Object Database for Java (JPA/JDO)
support
support's picture
Joined on 2010-05-03
User Post #1,207

Post Reply

Please read carefully the posting instructions - before posting to the ObjectDB website.

  • You may have to disable pop up blocking in order to use the toolbar (e.g. in Chrome).
  • Use ctrl + right click to open the browser context menu in the editing area (e.g. for using a browser spell checker).
  • To insert formatted lines (e.g. Java code, stack trace) - select a style in the toolbar and then insert the text in the new created block.
  • Avoid overflow of published source code examples by breaking long lines.
  • You may mark in paragraph code words (e.g. class names) with the code style (can be applied by ctrl + D).
  • Long stack traces (> 50 lines) and complex source examples (> 100 lines) should be posted as attachments.
Attachments:
Maximum file size: 32 MB
Cancel