Use existing table joins for Hibernate Entity Criteria

Question

Suppose I have an entity like the following, where each of the sets is a separate entity that has a foreign key relationship to the PersonEntity primary key.

PersonEntity - PK: person_id
   @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER, mappedBy = "person", orphanRemoval = true)
   Set<AddressEntity> addresses    
   @OneToMany(cascade = CascadeType.ALL, fetch = FetchType.EAGER, mappedBy = "person", orphanRemoval = true)
   Set<NameEntity> nameParts       

Both the AddressEntity and NameEntity have a PersonEntity, which is the FK relationship expressed in entity form.

All tables also have a field called the tenant_id which they are partitioned on.

If I create HibernateCriteria like follows:

final Criteria criteria = sessionFactory.getCurrentSession().createCriteria(PersonEntity.class, "p");
criteria.add(Restrictions.eq("p.personId", personId));
criteria.add(Restrictions.eq("p.tenantId", tenantId));

I get SQL like:

select ALL_ATTRIBUTES_SNIPPED
FROM person this_
LEFT OUTER JOIN address addresses2_
ON this_.person_id=addresses2_.person_id
LEFT OUTER JOIN name nameparts4_
ON this_.person_id=nameparts4_.person_id
WHERE this_.person_id=?
AND this_.tenant_id=?

Looking at the explain plan, I see that this checks all the partitions when doing the join. That's unnecessary as it just needs to look in one partition.

What I would like to would be to add additional restrictions on all tables, such that all of them are restricted by tenant_id. So the SQL might look like this:

  select ALL_ATTRIBUTES_SNIPPED
    FROM person this_
    LEFT OUTER JOIN address addresses2_
    ON this_.person_id=addresses2_.person_id
    LEFT OUTER JOIN name nameparts4_
    ON this_.person_id=nameparts4_.person_id
    WHERE this_.person_id=?
    AND this_.tenant_id=?
    AND addresses2_.tenant_id =?
    AND nameparts4_.tenant_id =?

However, I can't seem to figure out how to create criteria to perform this. When I try something like the following:

final Criteria criteria = sessionFactory.getCurrentSession().createCriteria(PersonEntity.class, "p")
    .createAlias("addresses", "address", JoinType.LEFT_OUTER_JOIN)
    .createAlias("nameParts", "namePart", JoinType.LEFT_OUTER_JOIN)

criteria.add(Restrictions.eq("p.personId", personId));
criteria.add(Restrictions.eq("p.tenantId", tenantId));
criteria.add(Restrictions.eq("address.tenantId", tenantId));
criteria.add(Restrictions.eq("namePart.tenantId", tenantId));

I get SQL that looks like this:

select ALL_ATTRIBUTES_SNIPPED
FROM person this_
LEFT OUTER JOIN address addresses2_
ON this_.person_id=addresses2_.person_id
LEFT OUTER JOIN name nameparts4_
ON this_.person_id=nameparts4_.person_id
LEFT OUTER JOIN address addresses3_
ON this_.person_id=addresses3_.person_id
LEFT OUTER JOIN name nameparts1_
ON this_.person_id=nameparts1_.person_id
WHERE this_.person_id=?
and this_.tenant_id = ?
and addresses3_.tenant_id = ?
and nameparts1_.tenant_id = ?

As you can see, the tables are joined twice.

How can I create a restriction that uses the original tables? I don't see how I would be able to supply a restriction that would access the existing joins. I tried something like p.addresses.tenantId but it said addresses was not recognized.

Edit: I have largely resolved the query issue, by placing this line on the Set in PersonEntity and on the PersonEntity in the set entity (ie, AddressEntity).

@JoinColumns(value={
        @JoinColumn(name="PERSON_ID", referencedColumnName="PERSON_ID", insertable=false, updatable=false), 
        @JoinColumn(name="TENANT_ID", referencedColumnName="TENANT_ID", insertable=false, updatable=false)
        })

I also removed the mappedBy attribute for those columns.

This forces a join on both the person_id and tenant_id and makes the explain plan cost significantly better (as well as real world performance). However, I'm not sure if this is a real solution because it introduces a new problem.

My problem now is that when I try to create a PersonEntity, I get the following error:

12:09:26.672 WARN  [main] org.hibernate.engine.jdbc.spi.SqlExceptionHelper - SQL Error: 1400, SQLState: 23000  
12:09:26.672  ERROR [main] org.hibernate.engine.jdbc.spi.SqlExceptionHelper - ORA-01400: cannot insert NULL into ("USER"."ADDRESS"."PERSON_ID")

This occurs even though the SQL shows the person insert occurred just before the attempt at the address insert. It seems like the person_id is not being passed along to be placed into the address insert. How could I force Hibernate to do that? Previously, it just happened automatically (from my point of view).

I am using the sequence generator to create my primary keys, if that matters.

Answer 1

What resolved this for me was adding these lines to the collections in the PersonEntity and then on the PersonEntity field in the child entity classes (AddressEntity, NameEntity):

@ManyToOne(fetch = FetchType.EAGER)    
@JoinColumns(value={
            @JoinColumn(name = "PERSON_ID", referencedColumnName = "PERSON_ID", nullable = false), 
            @JoinColumn(name = "TENANT_ID", referencedColumnName = "TENANT_ID", nullable = false)
            })
public PersonEntity getPerson() {
        return personEntity;
}

That worked for the query but I couldn't do inserts or updates, so the other thing I had to do was make sure that the existing tenantId field had insertable=false and updateable=false, like so:

@Column(name = "TENANT_ID", insertable = false, updatable = false)
public String getTenantId() {
    return tenantId;
}

Then, executing the criteria in the original question would result in all child tables having a join on the PERSON_ID and the TENANT_ID, exactly as I wanted.

This changed my estimated cost from 2525 to 15 on the explain plan since it could go straight to the correct partition instead of looping through them.