Saving nested JSON data into a MySQL database using Hibernate

Question

I am strucked with this issue. I have created a POJO for nested JSON and I am getting data in MarketPrice object where marketPrices is an ArrayList which has two elements.

This is MarketPrice POJO class and actually I need to save it into the MarketPrice table. I.e, entire JSON object. But I have two entities. How can this be possible?

MarketPrice.java

@Entity
@Table(name = "MarketPrice")

public class MarketPrice {

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name = "itemId")
private Long itemId;

@Column(name = "analysisDate")
private Date analysisDate;

@Column(name = "marketName")
private String marketName;

@Column(name = "category")
private String category;

@Column(name = "marketPlace")
private String marketPlace;

@Column(name = "state")
private String state;

@Column(name = "district")
private String district;


public ArrayList<Items> marketPrices;

Items.java

public class Items implements Serializable {

    private static final long serialVersionUID = -2428562977284114465L;

    @Id
    @Column(name="id")
    private int id;

    @Column(name = "itemName")
    private String itemName;

    @Column(name = "unitofPrice")
    private String unitofPrice;

    @Column(name = "minimumPrice",columnDefinition = "Float(10,2)")
    private Float minimumPrice;

    @Column(name = "maximumPrice",columnDefinition = "Float(10,2)")
    private Float maximumPrice;

This is my nested JSON data I'm getting from at the server side in the controller:

JSON data in marketPrices

{
    "marketPrices": [{
        "itemName": "Mango",
        "unitofPrice": "Kg",
        "minimumPrice": "10",
        "maximumPrice": "20"
    }, {
        "itemName": "Grapes",
        "unitofPrice": "Kg",
        "minimumPrice": "30",
        "maximumPrice": "40"
    }],
    "state": "xyz",
    "district": 4,
    "marketPlace": 5001,
    "marketName": "pmc",
    "category": "Fruits"
}

Controller.java

@RequestMapping(value = {"/saveAnalysis"} , method = RequestMethod.POST,consumes = "application/json")
@ResponseBody
public MarketPrice bulkSaveMarketAnalysis(@RequestBody 
        String marketPrices, HttpServletResponse response,
        HttpServletRequest request) throws JsonProcessingException, IOException, JSONException{

    MarketPrice marketPrice1 = new MarketPrice();
    System.out.println("Json Data"+marketPrices);//here am getting valid nested json from UI
    Gson gson = new Gson();
    MarketPrice marketPrice = gson.fromJson(marketPrices, MarketPrice.class);//converting it into Entity type all values are present in it.
    //Am strucked after this,How to save nested json into DB.

    String marketDataResponse =  analyserService.saveListOfMarketPrice(marketPrice);
    marketPrice1.setStatusMessage("success");
    return marketPrice1;
}

DAO.java

public String saveListOfMarketPrice(MarketPrice marketPrice) {
    System.out.println("In Analyser DAO fro bulk saving");
    final Session session = getSession();
    session.beginTransaction();
    marketPrice.setAnalysisDate(new Date());
    for (Items item : marketPrice.marketPrices) {
       marketPrice.currentItem = item;
       marketPrice.setItemName(marketPrice.currentItem.getItemName());
       marketPrice.setUnitofPrice(marketPrice.currentItem.getUnitofPrice());
       marketPrice.setMinimumPrice(marketPrice.currentItem.getMinimumPrice());
       marketPrice.setMaximumPrice(marketPrice.currentItem.getMaximumPrice());
        session.save(marketPrice);
    }
    session.getTransaction().commit();
    session.close();
    return "success";
}

After making these changes to DAO it saved finally Thank you.

as i understood, conversion from json to pojo is correct ? this analyserService is your DAO.java ? Lastly, i don't think you require to make for loop for saving each marketprice. Saving the marketPrice object will save parent object and child object both in one instance (as far i know). — Ankit, Apr 19 '17 at 05:57
but you need to create relation between MarketPlace and Items Classes like onetomany — Ankit, Apr 19 '17 at 06:03
@Hema, your `public class Items` is missing the `@Table` annotation. — Aruna, Apr 19 '17 at 06:19
@Hema, Also, remove the for loop in `saveListOfMarketPrice` method and just add `session.save(marketPrice);` after `session.beginTransaction();` and do commit and close the session. — Aruna, Apr 19 '17 at 06:20
@Aruna, All the details should be saved in MarketPrice table only,so is '@Table' annotation needed for Items class..? — Hema, Apr 19 '17 at 06:24
@Hema, In that case, you don't need a `@ManyToOne` annotation. What is the column name to store `public ArrayList marketPrices` ? — Aruna, Apr 19 '17 at 06:27

score 1 · Answer 1 · edited May 23 '17 at 12:17

You should create one bigger entity for the whole Json ant it will have a list of you marketPrice objects @onetomany. Read about that annotation. And also parse your whole incoming object at once not only the list of marketPrices inside. You can read how to parse your whole object here: GSON parsing without a lot of classes

You need something like: JsonObject rootObj = parser.parse(json).getAsJsonObject();

Then you should describe that structure in an entity:

@Entity
@Data
@Table(name = "your_table")
public class YourEntity{
//you should describe your parameters here too
//of the parsed json. it has other data in it not only the list of `MarketPrices`...


    @OneToMany(mappedBy = "yourEntity",
            cascade = {CascadeType.PERSIST, CascadeType.MERGE},
            fetch = FetchType.LAZY, orphanRemoval = true)
    private List<MarketPrice> prices;

.....

Aruna · Accepted Answer · 2017-04-19T08:15:02.220

1

As discussed in the comments, you can modify your code as below to make it work as expected.

MarketPrice.java

@Entity
@Table(name = "MarketPrice")

public class MarketPrice {

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name = "itemId")
private Long itemId;

@Column(name = "analysisDate")
private Date analysisDate;

@Column(name = "marketName")
private String marketName;

@Column(name = "category")
private String category;

@Column(name = "marketPlace")
private String marketPlace;

@Column(name = "state")
private String state;

@Column(name = "district")
private String district;

 @Transient
 public Items currentItem;

@Column(name = "itemName")
public String getItemName() {
    return this.currentItem.itemName;
}

@Column(name = "unitofPrice")
public String getUnitofPrice() {
    return this.currentItem.unitofPrice;
}

@Column(name = "minimumPrice",columnDefinition = "Float(10,2)")
public Float getMinimumPrice() {
    return this.currentItem.minimumPrice;
}

@Column(name = "maximumPrice",columnDefinition = "Float(10,2)")
public Float getMaximumPrice() {
    return this.currentItem.maximumPrice;
}

@Transient
public ArrayList<Items> marketPrices;

Items.java

public class Items implements Serializable {

    private static final long serialVersionUID = -2428562977284114465L;

    @Id
    @Column(name="id")
    private int id;

    public String itemName;

    public String unitofPrice;

    public Float minimumPrice;

    public Float maximumPrice;

DAO.java

public String saveListOfMarketPrice(MarketPrice marketPrice) {
        System.out.println("In Analyser DAO fro bulk saving");
        final Session session = getSession();
        session.beginTransaction();
        for (Items item : marketPrice.marketPrices) {
           marketPrice.currentItem = item;
           session.save(marketPrice);
        }
        session.getTransaction().commit();
        session.close();
        return "success";
    }

edited Apr 19 '17 at 08:15

answered Apr 19 '17 at 06:38

Aruna

11,959
3
28
42

I have added previously saved DB format. and i need save in that format only.Even i don want marketPrice column also. – Hema Apr 19 '17 at 06:50
org.hibernate.TransactionException: Transaction not successfully started ............and all columns which are in ITEMS class are saving NULL values – Hema Apr 19 '17 at 07:52
I have moved the line `session.getTransaction().commit();` out of `for` loop. – Aruna Apr 19 '17 at 08:15
itemName,unitofPrice,minimumPrice and maxPrice are saving as NULL values – Hema Apr 19 '17 at 08:29
Can you confirm whether the json is correctly set to the `marketPrices` object as per your comment `//converting it into Entity type all values are present in it`? – Aruna Apr 19 '17 at 08:29
Yes all values are there – Hema Apr 19 '17 at 08:30
Can you able to debug the for loop after setting the currentItem? – Aruna Apr 19 '17 at 08:38
insert into MarketPrice (analysisDate, category, district, marketName, marketPlace, state) values (?, ?, ?, ?, ?, ?) Hibernate generated query,here no itemName unitofPrice maxPrice and minPrice parameters only – Hema Apr 19 '17 at 08:43
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/142054/discussion-between-hema-and-aruna). – Hema Apr 19 '17 at 08:43
Thank you so much, You are a techie Star.. Finally the values saved into DB. with lots n lots of ur help. But left with one issue too:( – Hema Apr 19 '17 at 12:31
Great, have you fixed it? – Aruna Apr 19 '17 at 12:48
Yes fixed it,Means its saving same record multiple times. May be issue in for eachloop i guess – Hema Apr 19 '17 at 12:54
Great, I left the office and was out after that. So can't able to help you after that and it's 11pm in Sydney now. Try some more otherwise, will look out tomorrow. The reason why it may save the same record multiple time could be because of updating the same object again and again as hibernate may have the generated Id in the memory. So you can create a new object inside the for loop by cloning the existing object and try to update it. Anyways, FYI I am not a Java developer but basically a dot net and NodeJS developer :-) just was trying to help you as you explicitly called me :-) Good luck. – Aruna Apr 19 '17 at 13:02

Saving nested JSON data into a MySQL database using Hibernate

2 Answers2

Linked