Hibernate N+1 SELECTs with DTO objects

Question

I have a problem with hibernate where it is doing N+1 SELECTS when using a DTO object.

For example, this JPQL query:

SELECT com.acme.MyDto(t) FROM Thing t

Where the constructor of MyDto is something like:

public MyDto(Thing t) {
   ...
}

Which results in a query of something like:

SELECT t.id FROM thing t WHERE condition

followed by all the single queries for each row, ie:

SELECT t.id, t.column1, t.column2 FROM thing t WHERE t.id = 1
SELECT t.id, t.column1, t.column2 FROM thing t WHERE t.id = 2
SELECT t.id, t.column1, t.column2 FROM thing t WHERE t.id = 3
...

However if the constructor doesn't accept the Entity, but instead each individual column, then hibernate behaves as you would expect, ie:

public MyDto(Integer id, String column1, String column2) {
   ...
}

Then the generated SQL looks like this:

SELECT t.id, t.column1, t.column2 FROM thing t WHERE condition

Aside from creating DTO constructors that takes every column, is there a way to coax hibernate to just select all the columns at once from the beginning?

The table that we're working with has 100+ columns spread across embeddables, so it's pretty annoying to maintain a huge constructor. The table is hugely normalised and has no joins.

Answer 1

Read your question wrong the first time... I don't recall using DTOs if they just take the whole entity and not just some specific columns, so I'm not sure why Hibernate behaves like that when you use a whole entity as a parameter in DTO constructor. Anyway, you could work around it by just gettin the actual Things via a query and then construct the DTOs in a loop, something along the lines of:

public List<ThingDTO> getThingDTOs( ... )
{
    Query query = em().createQuery("FROM Thing t WHERE ...");
    query.setParameter( ... );

    List<Thing> things = query.getResultList();

    List<ThingDTO> thingDTOs = new ArrayList(things.size());
    for(Thing t : things)
    {
        thingDTOs.add(new ThingDTO(t));
    }

    return thingDTOs
}

It ain't pretty, but this way Hibernate should fetch all the needed rows in one go