Hibernate criteria: Joining table without a mapped association

Question

I'd like to use Hibernate's criteria api to formulate a particular query that joins two entities. Let's say I have two entities, Pet and Owner with a owner having many pets, but crucially that association is not mapped in the Java annotations or xml.

With hql, I could select owners that have a pet called 'fido' by specifying the join in the query (rather than adding a set of pets to the owner class).

Can the same be done using hibernate criteria? If so how?

Thanks, J

Premillenarian

Answer 1

My understanding is that if you do this using HQL, you are creating a Cartesian join with a filter, rather than an inner join. Criteria queries do not support doing this.

Porty

David is correct on this, you cannot do this with a Criteria you can do it with HSQL – Bebel

Does anyone know if it is still impossible to use criteria queries to join unmapped entities? – Downatheel

I think it is possible using HQL with clause – Counteract

@Suganthan No it's not possible, just tested at work as we had similar issue. – Ambrotype

@DavidM an upvote because this is clear and correct, and because I really don't understand why the downvote! – Ambrotype

@Tarik, its fine, but long back I had a similiar experience where I can able to solve the issue using with clause – Counteract

@Suganthan yes, functionally. But it still generate a CROSS JOIN... anyway that was long time ago for you to remember :) – Ambrotype

Answer 2

This is indeed possible with criteria:

DetachedCriteria ownerCriteria = DetachedCriteria.forClass(Owner.class);
ownerCriteria.setProjection(Property.forName("id"));
ownerCriteria.add(Restrictions.eq("ownername", "bob"));

Criteria criteria = getSession().createCriteria(Pet.class);
criteria.add(Property.forName("ownerId").in(ownerCriteria));

Update: This actually performs a sub-query instead of a join but it allows you to use Criteria on two entities that do not have a hibernate relationship defined.

Kurrajong

why there are so many up-votes? It still two different criteria right? – Kwarteng

Problem is this is not a join, but subquery - that means that you can't order your results by a column from first criteria. – Destinydestitute

I guess it becomes sub-query instead of a join – Kwarteng

I had to do a query between a table and a view and there was no explicit association. This worked fine. – Corticate

How do i retrieve the result? Can i get it as List<Owner,List<Pet>> ? – Duma

Agree with Jonas Stensved. – Leonoraleonore

I think @Pierre is right, although we should ALL be careful when using detached criterias or subqueries. Sometimes, we only need to join those tables in order to filter the results and not to retrieve the joined entity. Check the question. Fortyrunner asked how to filter owners with restrictions over unmapped relationships, not how to make an inner join. – Kentiga

If owner and pet is mapped in java, are these codes doing join query? – Agone

Answer 3

In NHibernate you can use subqueries which are defined as DetachedCriteria. Not sure if it works the same in Java, most probably it is the same:

DetachedCriteria pets = DetachedCriteria.For<Pet>("pet")
  .SetProjection(Projections.Property("pet.ownername"))
  .Add(/* some filters */ );

session.CreateCriteria(typeof(Owner))
  .Add(Subqueries.PropertyIn("name", pets);

Assumed that it is joined using the name of the owner.

Invention

Answer 4

Criterion ownerCriterion = Restrictions.sqlRestriction(SELECT ownerId FROM   Owner WHERE ownerName ='bob');
Criteria criteria = getSession().createCriteria(Pet.class);
criteria.createCriteria("ownerId").add(ownerCriterion);

Vexillum

Answer 5

In this time this is possible in following manner:

I'm using Spring data JPA 2.1.10. I have two entities:

@Entity
@Getter
@Setter
public class TaskManagement {
  private Long id;
  private String title;
  //Some other attributes
}


@Entity
@Getter
@Setter
public class Tagging {
  private Long id;
  private String name;
  private String taskManagementId;
  //Some other attributes
}

Tasks can have Some tags. but there isn't any relation between TaskManagement and Tag entities.

To search taskmanagements i used TaskManagementSpecification like this:

public class TaskManagementSpecification implements Specification<TaskManagement> {
  private final TaskManagementSearchDto taskManagementSearchDto;

  @Override
  public Predicate toPredicate(Root<TaskManagement> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
    List<Predicate> andClause = new ArrayList<>();

    //To Cross join TaskManagement to Tagging that they are unrelated
    if (!CollectionUtils.isEmpty(taskManagementSearchDto.getTagIds())) {
      Root<Tagging> taggingRoot = criteriaQuery.from(Tagging.class);

      andClause.add(criteriaBuilder.equal(taggingRoot.get("taskManagementId"), root.get(TaskManagement_.ID)));        
    }
  }
}

The result query is:

select foo
from task_management taskmanage0_
     cross join tagging tagging1_
where tagging1_.task_mangement_id = taskmanage0_.id

Ausgleich

Answer 6

There's a SQLCriterion, which you can give arbitrary SQL, and add to your Criteria. In the SQL string, the token {alias} "will be replaced by the alias of the root entity."

Overmeasure

how to do this? any example? – Pacifistic

No example or (at least) link provided. – Falstaffian

SQLCriterion has protected constructor – Ramer