ObjectDB ObjectDB

How to use JOIN FETCH?

#1

The ObjectDB manual (https://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;
  }
}
}
edit
delete
#2

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
edit
delete
#3

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.

edit
delete
#4

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
edit
delete

Reply

To post on this website please sign in.