Fetch Id only in a JPA TupleQuery

Isn`t it possible too fetch only the id from a foreign object.

Simple example:

class Person {
   int id;
   String name;
   @JoinColumn(name = "ADDRESS_ID", referencedColumnName = "ID")
   @ManyToOne(fetch = FetchType.LAZY)
   Address addressesId
}

class Address {
  int id;
  String city;
}

CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
CriteriaQuery<Tuple> q = criteriaBuilder.createTupleQuery();
Root<Person> from = q.from(Person.class);
q.multiselect(from.get(Person_.name),
              from.get(Person_.AdresseId);
TypedQuery<Tuple> query = em.createQuery(q);
List<Tuple> resultList = query.getResultList();

I need only the ID from Address which is of course present in Person table, but JPA generate automatically an inner join query and entities with a null Address are omitted. The join is unnecessary. Is there a way to only fetch the id from Address in this TupleQuery?

Xanas

Answers


JPA can only use the mappings provided to return data, and since the address foreign key within Person is mapped using an object reference mapping, any inclusion of it in queries requires a join in JPA. The only way to get nulls included would be to use a left outer join as Koitoer describes below.

The alternative though is to create another read-only basic mapping for the addressId foreign key within the Person entity.

class Person {
   int id;
   String name;
   @JoinColumn(name = "ADDRESS_ID", referencedColumnName = "ID")
   @ManyToOne(fetch = FetchType.LAZY)
   Address address;
   @Column(name ="ADDRESS_ID", insertable=false, updatable=false)
   private Long addressId;
}

This would allow you to use the 'address' attribute when you want an Address object instance, and the 'addressId' in queries when you only need the foriegn key value and not a full Address entity:

CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
CriteriaQuery<Tuple> q = criteriaBuilder.createTupleQuery();
Root<Person> from = q.from(Person.class);
q.multiselect(from.get(Person_.name),
              from.get(Person_.AddressId);
TypedQuery<Tuple> query = em.createQuery(q);
List<Tuple> resultList = query.getResultList();

would return the name and addressId value from the Person table without any joins.


Need Your Help

Is there any plugin which will help me validate model on client-side?

javascript ruby-on-rails validation client-side

I need to validate form on client-side and I was wondering if there is a rails plugin which will help me in this task? Maybe it will build javascript needed to validate certain model from ActiveRec...

How can I programmatically connect to a remote database server that requires an SSH tunnel?

c# .net mysql ssh ssh-tunnel

I'm working on an ETL using C# in which extracts data from a remote MySQL database that requires an SSH tunnel to connect. I currently have it working such that I first create my SSH tunnel manually