CODEWITHSUNDEEP
javaperformancehibernatejoinhql
Memran
Memran

Reputation: 405

HQL and joins - a faster way?

This part of my model is as follows:

IQCEntity has many Documents
DocumentCategory has many Documents

I am using Hibernate for my ORM.

Now, please consider the following method:

/**
 * Get all documents in the supplied IQCEntity which are in the
 * specified DocumentCategory.
 * @param entity the {@link IQCEntity} which holds the Documents
 * @param category the {@link DocumentCategory} which the Documents belong to
 * @return Collection<{@link Document}>
 */
@SuppressWarnings("unchecked")
public Collection<Document> getDocuments(IQCEntity entity, DocumentCategory category) { 
    String q = "from Document d where d.documentCategory.documentCategoryId = :c and d.entity.entityId = :e";
    Query query = session.createQuery(q);
    query.setParameter("c", category.getDocumentCategoryId());
    query.setParameter("e", entity.getEntityId());
    List<Document> documents = (List<Document>)query.list();
    Collections.sort(documents);
    return documents;
}

This method works, and brings back the correct results, however it seems to be pretty slow.

If I look at the table structure in the database, the Document table has parent ids (of course it does - else how could it join!), documentCategory_documentCategoryId and entity_entityId.

We all know that in SQL the correct results can be achieved without any joins at all. How can the same be done in HQL?

I have tried this: (Note the _ instead of .)

String q = "from Document d where d.documentCategory_documentCategoryId = :c and d.entity_entityId = :e";

but the property is not found.

org.hibernate.QueryException: could not resolve property: documentCategory_documentCategoryId of: com.foo.bar.entities.Document

Is there some way to reference the join fields instead of object references?

Upvotes: 1

Views: 765

Answers (1)

wero
wero

Reputation: 32980

To avoid the joins use the identifier property .id:

String q = "from Document d where d.documentCategory.id = :c and d.entity.id = :e";

But since you also have the referenced objects you can even write a shorter version, using entity and category as parameters:

String q = "from Document d where d.documentCategory = :c and d.entity = :e";
Query query = session.createQuery(q);
query.setParameter("c", category);
query.setParameter("e", entity);

In both versions Hibernate is able to figure out that it actually does not need to join.

Upvotes: 3

Related Questions