Spring Boot - Flatten JSON Entity returned by JPA specification for exporting to excel

Question

I am creating a Spring Boot project for inventory management. I have an Entity called InwardInventory that has one to many relationship with another entity called InwardOutwardList. I am using JPA Specification to filter the Entity InwardInventory and it is working fine. Response I receive after filter is

{
    "inwardInventory": {
        "content": [
            {
                
                "inwardid": 19497,
                "date": "2019-05-28",
                "vehicleNo": "TRUCK",
                "supplierSlipNo": "",
                "ourSlipNo": "",
                "inwardOutwardList": [
                    {
                        "entryid": 19499,
                        "product": {
                            "productName": "Cement",
                          },
                        "quantity": 100.0
                    },
                    {
                        "entryid": 19500,
                        "product": {
                            "productName": "Iron",
                          },
                        "quantity": 30.0
                    }
                ],
                "warehouse": {
                    "warehouseName": "war2"
                },
                "supplier": {
                    "name": "Bright Traders"
                }
            }
        ]
    }
}

Now, I want to export this data to excel. So, I need to flatten this response to something like this.

{
    "inwardInventory": {
        "content": [
            {
                
                "inwardid": 19497,
                "date": "2019-05-28",
                "vehicleNo": "TRUCK",
                "supplierSlipNo": "",
                "ourSlipNo": "",
                "entryid": 19499,
                "productName": "Cement",
                "quantity": 100.0,
                "warehouseName": "war2",
                "name": "Bright Traders"
            },
            {
                
                "inwardid": 19497,
                "date": "2019-05-28",
                "vehicleNo": "TRUCK",
                "supplierSlipNo": "",
                "ourSlipNo": "",
                "entryid": 19500,
                "productName": "Iron",
                "quantity": 30.0,
                "warehouseName": "war2",
                "name": "Bright Traders"
            }
        ]
    }
}

I know that I can do this by iterating over each inward inventory and then nested iteration over each product and creating custom DAO. However, that doesn't seem to be optimised solution.

I also cannot use projections or native queries with custom select columns because they cannot be integrated with jpa specification. Can someone suggest me best approach that can be used to achieve this in most optimal way.

Thank You.

Answer 1

Handled this through custom serializer. Sample code below

@Component
public class OutwardExportSerializer extends StdSerializer<OutwardInventoryExportDAO> 
{
    private static final long serialVersionUID = 1L;
    protected OutwardExportSerializer(Class<OutwardInventoryExportDAO> t) 
    {
        super(t);
    }
    protected OutwardExportSerializer() 
    {
        this(null);
    }
    @Override
    public void serialize(OutwardInventoryExportDAO value, JsonGenerator gen, SerializerProvider provider) throws IOException 
    {
        DateFormat df = new SimpleDateFormat("dd/MM/yyyy");
        for(InwardOutwardList ioList:value.getInwardOutwardList())
        {
            System.out.println("Serialization starts for OutWardID"+value.getOutwardid());
            gen.writeStartObject();
            gen.writeNumberField("outwardid", value.getOutwardid());
            gen.writeStringField("date",df.format(value.getDate()));
            gen.writeStringField("purpose",value.getPurpose()!=null?value.getPurpose():"");
            gen.writeStringField("slipNo",value.getSlipNo()!=null?value.getSlipNo():"");
            gen.writeStringField("product",ioList.getProduct().getProductName()!=null?ioList.getProduct().getProductName():"");
            gen.writeStringField("measurement unit",ioList.getProduct().getMeasurementUnit()!=null?ioList.getProduct().getMeasurementUnit():"");
            gen.writeNumberField("quantity",ioList.getQuantity()!=null?ioList.getQuantity():0.0);
            gen.writeNumberField("closing stock",ioList.getClosingStock()!=null?ioList.getClosingStock():0.0);
            gen.writeStringField("warehouse",value.getWarehouse()!=null?value.getWarehouse():"");
            gen.writeStringField("contractor",value.getContractor()!=null?value.getContractor():"");
            gen.writeStringField("additionalInfo",value.getAdditionalInfo()!=null?value.getAdditionalInfo():"");
            gen.writeStringField("usageLocation",value.getUsageLocation()!=null?value.getUsageLocation():"");
            gen.writeStringField("usageArea",value.getUsageArea()!=null?value.getUsageArea():"");
            gen.writeEndObject();
            System.out.println("Serialization ends for OutWardID"+value.getOutwardid());
        }
        
    }
}