Hibernate N+1 from select across multiple tables

Answer 1

Unless you've mapped the collections as lazily loaded, when you grab the object, regardless of the additional HQL, its going to generate multiple selects. Change your connection mappings to be lazy loaded. Additionally, unless connectionDetails can never be empty, I suggest you change the last join to a left join.

Answer 2

The first SQL you've posted is the one you've expected (not counting the inner join users that's missing in your "expected" SQL - but it's present in your HQL so that's correct).

The second SQL is (simplified for clarity):

select *
  from RepositoryEntityVersion repository0_
  left outer join ChangeEntry changeentr1_ on repository0_.changeEntry_id=changeentr1_.id
  left outer join Changeset changeset2_ on changeentr1_.changeset_id=changeset2_.id
  left outer join users user3_ on changeset2_.author_id=user3_.id
  left outer join RepositoryEntity repository4_ on changeentr1_.repositoryEntity_id=repository4_.id
  left outer join project project5_ on repository4_.project_id=project5_.id
  left outer join RepositoryEntity repository6_ on repository4_.parent_id=repository6_.id
  left outer join RepositoryEntityVersion repository7_ on changeentr1_.repositoryEntityVersion_id=repository7_.id
  left outer join RepositoryEntity repository8_ on repository7_.repositoryEntity_id=repository8_.id
  left outer join ChangeEntry changeentr9_ on changeentr1_.sourceChangeEntry_id=changeentr9_.id
 where repository0_.id=?

The base table here is RepositoryEntityVersion which is not on your diagram; I'm guessing it's mapped as one-to-many on RepositoryEntity? I'm further guessing that it's mapped for eager fetching, which is where your problem lies.

You need to either map it as lazy or explicitly mention it in your query with join fetch. The latter, however, may be undesirable due to both volume of data that may be involved and (possibly) duplicate root entity instances being returned. distinct doesn't always help; look at the SQL you've posted and you'll see it's being applied to ALL columns returned across all tables thus making it rather pointless.