Native @Query doesn't have sufficient mapping power, so it seems that Hibernate native query must be needed.
import java.util.ArrayList; import java.util.List; import javax.persistence.EntityManager; import javax.persistence.PersistenceContext; import org.hibernate.Session; import org.hibernate.transform.BasicTransformerAdapter; import org.springframework.stereotype.Repository; // https://docs.spring.io/spring-data/jpa/docs/2.5.6/reference/html/#repositories.custom-implementations @Repository public class CustomizedARepositoryImpl implements CustomizedARepository { @PersistenceContext private EntityManager entityManager; @Override public List<A> getAll() { // https://docs.jboss.org/hibernate/orm/5.4/userguide/html_single/Hibernate_User_Guide.html#sql-entity-associations-query final Session sess = (Session) entityManager.getDelegate(); final List<A> res = sess // If no duplicate column names, original sql can be used, too. .createNativeQuery("select {a.*},{b.*} from A as a left join B as b on a.ID = b.a ") .addEntity("a", A.class) .addJoin("b", "a.bs") .setResultTransformer(DistinctResultTransformer.INSTANCE) .list(); return res; } // https://stackoverflow.com/q/12071014/4506703 static class DistinctResultTransformer extends BasicTransformerAdapter { private static final long serialVersionUID = 1L; static final DistinctResultTransformer INSTANCE = new DistinctResultTransformer(); @Override public List transformList(final List collection) { final List<Object> res = new ArrayList<>(); for (final Object[] obj : (List<Object[]>) collection) { if (!res.contains(obj[0])) { res.add(obj[0]); } } return res; } } }
Above code executes 1 query:
select a.id as id1_0_0_, a.name as name2_0_0_,b.a as a3_1_0__, b.id as id1_1_0__, b.id as id1_1_1_, b.a as a3_1_1_, b.name as name2_1_1_ from A as a left join B as b on a.ID = b.a
full sample code
You can use some methods avoiding N+1 problem.
Using JPQL fetch, instead of native-query:
@Query("select distinct a from A a left join fetch a.bs") List<A> getAllJpqlFetch();
Above code executes 1 query:
select distinct a0_.id as id1_0_0_, bs1_.id as id1_1_1_, a0_.name as name2_0_0_, bs1_.a as a3_1_1_, bs1_.name as name2_1_1_, bs1_.a as a3_1_0__, bs1_.id as id1_1_0__ from a a0_ left outer join b bs1_ on a0_.id=bs1_.a
diff
Using JPA Criteria fetch, is equivalent to above JPQL:
@Repository public class CustomizedARepositoryImpl implements CustomizedARepository { @PersistenceContext private EntityManager entityManager; @Override public List<A> getAllCriteria() { // https://docs.jboss.org/hibernate/orm/5.4/userguide/html_single/Hibernate_User_Guide.html#criteria-from-fetch final CriteriaBuilder builder = entityManager.getCriteriaBuilder(); final CriteriaQuery<A> criteria = builder.createQuery(A.class); final Root<A> root = criteria.from(A.class); root.fetch("bs", JoinType.LEFT); criteria.select(root).distinct(true); return entityManager.createQuery(criteria).getResultList(); }
Above code executes 1 query:
select distinct a0_.id as id1_0_0_, bs1_.id as id1_1_1_, a0_.name as name2_0_0_, bs1_.a as a3_1_1_, bs1_.name as name2_1_1_, bs1_.a as a3_1_0__, bs1_.id as id1_1_0__ from a a0_ left outer join b bs1_ on a0_.id=bs1_.a
diff
Using @Fetch(FetchMode.SUBSELECT):
import org.hibernate.annotations.Fetch; import org.hibernate.annotations.FetchMode; // ... @Entity public class A { @OneToMany(mappedBy = "a") @Fetch(FetchMode.SUBSELECT) private List<B> bs; // ... }
// findAll() method implementation is auto-generated by Spring Data JPA // https://docs.spring.io/spring-data/jpa/docs/2.5.6/reference/html/#repositories.core-concepts repository.findAll();
Above code executes 2 queries(root entities and their relational entities):
select a0_.id as id1_0_, a0_.name as name2_0_ from a a0_ select bs0_.a as a3_1_1_, bs0_.id as id1_1_1_, bs0_.id as id1_1_0_, bs0_.a as a3_1_0_, bs0_.name as name2_1_0_ from b bs0_ where bs0_.a in (select a0_.id from a a0_)
diff
