hibernate native query complex constructor mapping

Question

Java, Spring Data JPA

I have 2 entities:

class Source {
  Integer id;
  String name;
}

class Item {
  Integer id;
  String name;
  Integer sourceId;
}

I need statistic native query result like this:

 select s.id source_id, s.name source_name, count(i.id) item_count
 from source s
 left join item i on s.id = i.source_id
 group by s.id

And i want to have result in Java object MyResult:

class MyResult {
  Source source;
  Integer itemCount;
  MyResult(Source source, Integer itemCount) {...}
}

The closest solution is using @SqlResultSetMapping like this:

@SqlResultSetMapping(
    name = "MyResultMapping",
    entities = {
        @EntityResult(
             entityClass = Source.class,
                fields = {
                    @FieldResult(name = "id", column = "source_id"),
                    @FieldResult(name = "name", column = "source_name"),
                }
        ),
        ... 
        ???
    }
)

OR

@SqlResultSetMapping(
    name = "MyResultMapping",
    classes = {
        @ConstructorResult(
            targetClass = MyResult.class,
                columns = {
                    @ColumnResult(name = "???"),
                    ???
                }
        )
    }
)

With second variant i can use something like this:

MyResult(Integer sourceId, String sourceName, Integer itemsCount) {
    this.source = new Source(sourceId, sourceName);
    this.itemsCount = itemsCount;
}

but i want it to automate with @SqlResultSetMapping... (because my real objects more complex)

score 0 · Accepted Answer · answered Apr 15 '18 at 10:07

With Spring Data JPA it's better to use projections to achieve you need, for example:

public interface SourceWithItemCount {
    Source getSource();
    Integer getItemCount();
}

Then in your Source repository create HQL query method, like this:

public interface SourceRepo extends JpaRepository<Source, Integer> {
    @Query("select s as source, count(i) like itemCount from Source s left join Item i on i.sourceId = s.id group by s"
    List<SourceWithItemCount> getSourcesWithItemCount();
}

Important note is to use aliases for returned values (s as source etc.) it's allows Spring Data JPA to map them to projections properties.

Join on <condition> works from Hibernate version 5.1+ (if I'm not mistaken) so I recommend you to create classic one-to-many relation between your objects, like this, for example:

@Entity
class Source {
    @Id private Integer id;
    private String name;
    @OneToMany @JoinColumn(name = "source_id") private List<Item> items;
}

@Entity
class Item {
    @Id private Integer id;
    private String name;
}

Then create JPQL query method supported by all versions of Hibernate (and other ORM providers):

@Query("select s as source, count(i) like itemCount from Source s left join s.items i group by s"
List<SourceWithItemCount> getSourcesWithItemCount();

thx for projection link and idea! it is looks like useful, but my key problem is native query mapping (my entities without direct relations, and i can't use HQL) — Elegant.Obj, Apr 15 '18 at 11:11
@Elegant.Obj Hibernate 5.1+ supports join between unrelated entities, so you can use HQL. — Cepr0, Apr 17 '18 at 08:49

hibernate native query complex constructor mapping

1 Answers1