List Results Mapping Spring JPA

Question

I use PostgreSQL and I have these tables, product and product_media with relation OneToMany on product with product_media. I want to retrieve a list with product which each of them contains a list of product_media. And I have two options in my mind in order to retrieve them from DB.

First solution is initially retrieve the list of product and then iterate the retrieved list and execute query in order to retrieve the list of product_media.

Query1:

select * from product as p where p.status=1;

Retrieve List and then iterate this list and execute this query:

select * from product_media as pm where pm.product_id=?

Second is to implement join in query and retrieve all data from my DB. Query:

select * from product as p Join product_media as pm on (p.id=pm.product_id)

Retrieve a complex list with all data. The problem of second option is to do not know an elegant way to map this list into an object which has the format below. Do you know how can map automatically the results into this format?

product:[
    {
       id:1,
       name:'Pro1',
       medias:[
         {
            id:1,
            uuid:'asdfi-asdf-rg-fgsdf-do'
         },
         {
            id:2,
            uuid:'asdfi-asdf-rg-fgsdf-do'
         }    
       ]
    },
    {
       id:2,
       name:'Pro2',
       medias:[
         {
            id:5,
            uuid:'asdfi-asdf-rg-fgsdf-do'
         },
         {
             id:7,
             uuid:'asdfi-asdf-rg-fgsdf-do'
         }    
       ]
    }
]

Answer 1

I think the second variant is the better option. After fetching the object tree from the database you can do something like the following to achieve what you are posted above:

1. Assuming your entities are defined as follows:

   Product.java

   public class Product {
   
       private long id;
       private String name;
       private List<ProductMedia> mediaList;
   
   
       public Product() {
           mediaList = new ArrayList<ProductMedia>();
       }
   
       public Product(long id, String name) {
           this.id = id;
           this.name = name;
           mediaList = new ArrayList<ProductMedia>();
       }
       // getters + setters
   }
   

   ProductMedia.java

   public class ProductMedia {
       private long id;
       private String uuid;
   
       public ProductMedia() { }
   
       public ProductMedia(long id, String uuid) {
           this.uuid = uuid;
       }
       // getters + setters
   }
   

2. Using the Jackson library you can generate output as follows:

   public class JsonTest {
   
       public static void main(String[] args) throws IOException {
   
           ObjectMapper mapper = new ObjectMapper();
   
           mapper.enable(SerializationFeature.INDENT_OUTPUT);
   
           Product prod = new Product(1, "p1");
   
           ProductMedia pm = new ProductMedia(1, "uuid1");
           ProductMedia pm2 = new ProductMedia(2, "uuid2");
   
           prod.getMediaList().add(pm);
           prod.getMediaList().add(pm2);
   
   
           Product prod1 = new Product(2, "p2");
   
           ProductMedia pm3 = new ProductMedia(3, "uuid3");
           ProductMedia pm4 = new ProductMedia(4, "uuid4");
   
           prod1.getMediaList().add(pm3);
           prod1.getMediaList().add(pm4);
   
           Product[] pList = {prod, prod1};
   
           mapper.writeValue(System.out, pList);
       }
   }
   

   In this example, I am writing the output onto the console. But you are not restricted to it; you can write to a file passing in a FileOutputStream.

To be able to run this example you need to add the dependency; if you use Maven you can add the following into your POM:

    <dependency>
        <groupId>com.fasterxml.jackson.core</groupId>
        <artifactId>jackson-databind</artifactId>
        <version>2.7.4</version>
    </dependency>

Otherwise add the jar of the dependency into your project build path.

Answer 2

If your response is not in json format you can try below

There is a many-to-many relationship between Product and Media.

Product_Media is a helper table to maintain many-to-many relationship between Product and Media entities.

Product entity:

@Entity(name = "product")
public class Product {

    @Id
    @GeneratedValue
    private Long product_id;

    @Column
    private String name;

    @ManyToMany(cascade = { CascadeType.MERGE }, fetch = FetchType.EAGER)
    @JoinTable(name = "product_media", joinColumns = {
            @JoinColumn(name = "product_id", table = "product") }, inverseJoinColumns = {
                    @JoinColumn(name = "media_id", table = "media") })
    List<Media> medias;

}

Media entity

@Entity(name = "media")
public class Media {

    @Id
    @GeneratedValue
    private Long media_id;

    @Column
    private String name;

}

SQL generated by Hibernate

select
    product0_.product_id as product_1_1_0_,
    product0_.name as name2_1_0_,
    medias1_.product_id as product_1_1_1_,
    media2_.media_id as media_id2_2_1_,
    media2_.media_id as media_id1_0_2_,
    media2_.name as name2_0_2_ 
from
    product product0_ 
left outer join
    product_media medias1_ 
        on product0_.product_id=medias1_.product_id 
left outer join
    media media2_ 
        on medias1_.media_id=media2_.media_id 
where
    product0_.product_id=?

If the relationship is one-to-many, change entities like below

Media Entity

@Entity(name = "media")
public class Media {

    @Id
    @GeneratedValue
    private Long id;

    @Column
    private String name;

    @ManyToOne
    @JoinColumn(name = "product_id", referencedColumnName = "id", nullable = false, updatable = false)
    private Product product;

    public Media() {

    }
}

Product Entity

@Entity(name = "product")
public class Product {

    @Id
    @GeneratedValue
    private Long id;

    @Column
    private String name;

    @OneToMany(fetch = FetchType.EAGER, cascade = CascadeType.ALL, mappedBy = "product")
    List<Media> medias;
}

Hibernate generated SQL

select
    product0_.id as id1_2_0_,
    product0_.name as name2_2_0_,
    medias1_.product_id as product_3_2_1_,
    medias1_.id as id1_0_1_,
    medias1_.id as id1_0_2_,
    medias1_.name as name2_0_2_,
    medias1_.product_id as product_3_0_2_ 
from
    product product0_ 
left outer join
    media medias1_ 
        on product0_.id=medias1_.product_id 
where
    product0_.id=?