CODEWITHSUNDEEP
javahibernatejpaspring-data
Amakak
Amakak

Reputation: 31

How do I map 1 column of a SINGLE table query to a COLLECTION using Spring JPA/Hibernate

Id like to know if there's a way of doing something like the Stream API groupingBy Collector. I know how to select one column (the question was marked as duplicated and I don't belive it is) I want to do a grouping (like the groupingBy collector of the java Stream API) not a group By. Suppose you have a table (ONE table) like:

+---------------------+
+       myTable       +
+----------+----------+
+ column_A | column_B +
+----------+----------+
+        1 |        1 +
+        1 |        2 +
+        1 |        3 +
+        2 |        1 +
+----------+----------+

And I would like to have something like

@Entity
@Table("MyTable")
public class MyEntity {
  @Column("column_A")
  private int a;
  
  @Column("column_B") ?
  private List<Integer> b; //normanly this wuould just be int b, butI want a list of all b's that have the same a
}

with a repo like

public interface MyCrudRepo extends CrudRepository<MyEntity,Integer>{
  List<MyEntity> findByA();
}

or

public interface MyCrudRepo extends CrudRepository<MyEntity,Integer>{
  @Query("SELECT m.a,m.b FROM MyEntity m")
  List<MyEntity> customFind();
}

This is not necessarily how it should look at the end, anything that works similar to this is fine. I have looked into projections but all examples use 2 different tables.

Upvotes: 1

Views: 853

Answers (2)

Biscoito
Biscoito

Reputation: 1

It's possible to refer the same table using annotations (some kind of hack):

@ElementCollection  
@CollectionTable(name = "table_name")

Example:

@Data
@Entity
@Table(name = "parent")
public class Parent {
    @Id
    private long id;

    private String name;

    @ElementCollection
    @CollectionTable(
        name = "parent",
        joinColumns = @JoinColumn(name = "id"))
    private List<Child> children;


    @Data
    @Embeddable
    public static class Child {
        @Column(name = "child_id")
        private long id;

        @Column(name = "child_name")
        private String name;
    }
}

Columns in the table:

id, name, child_id, child_name

But... you'll get several copies from repository (one object for row), so use Set to get rid of them Set.copyOf(repository.findAll())

Hibernate will be querying database to get children for each parent.

P.S. Should work with @OneToMany annotation too, possibly, if create two entities for one table, didn't check that

Upvotes: 0

JMSilla
JMSilla

Reputation: 1449

There is no a specific way to do what you need using JPQL. Instead, you can use a Hibernate ResultTransformer, that allows yo to transform the query results to whatever you need. This is a code solution, and is similar to implementing the grouping using the Stream API in the list result and return a map containing the aggregated data.

Upvotes: 1

Related Questions