[FIXED] Mapping JSON to SQL columns using Spring Boot and Java

Issue

We have a JSON structure as below:

{
    "basecurrency": "USD",
    "data_date": "2022-07-25",
    "data": {
        "AED": 3.671159,
        "AFN": 89.81064,
        "ALL": 114.523046,
        "AMD": 409.987398,
        "ANG": 1.799784,
        "100 more values",
    }
}

We want to push to a MySQL table structure as below:

create table tblData(
   ID INT NOT NULL AUTO_INCREMENT,
   base_currency varchar(10) NOT NULL,
   data_date DATE NOT NULL,
   AED REAL(16,10) NOT NULL,
   AFN REAL(16,10) NOT NULL,
   AGN REAL(16,10) NOT NULL,
   100 MORE COLUMNS,
   PRIMARY KEY (ID)
);

How can we map this JSON in Java and Spring Boot and push it to MySQL? Do we have to create a POJO of 100 or more fields and is there a better way to do it?

Solution

I believe this solution to handle ordering pretty well. Also, if more columns are added to the JSON, it should handle them dynamically as well.

public static void main(String[] args)
        throws FileNotFoundException, IOException, SQLException, ParseException {
    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
    String s = "{\n" + "    \"basecurrency\": \"USD\",\n"
            + "    \"data_date\": \"2022-07-25\",\n" + "    \"data\": {\n"
            + "        \"AED\": 3.671159,\n" + "        \"AFN\": 89.81064,\n"
            + "        \"ALL\": 114.523046,\n" + "        \"AMD\": 409.987398,\n"
            + "        \"ANG\": 1.799784\n" + " }\n" + "}";
    HashMap<String, Object> readValue = new ObjectMapper().readValue(s, HashMap.class);
    System.out.println(readValue);

    List<Entry<String, Double>> data = ((Map<String, Double>) readValue.get("data"))
            .entrySet().stream().sorted((e1, e2) -> e1.getKey().compareTo(e2.getKey()))
            .collect(Collectors.toList());
    StringBuilder sql = new StringBuilder();
    sql.append("insert into tblData (");
    sql.append("basecurrency, ");
    sql.append("data_date ");
    for (Entry<String, Double> e : data) {
        sql.append(",");
        sql.append(e.getKey());
    }
    sql.append(")  values( ");
    sql.append("?, "); // basecurrency
    sql.append("? "); // data_date
    for (Entry<String, Double> e : data) {
        sql.append(",");
        sql.append("?");
    }
    sql.append(")");
    try (PreparedStatement ps = con.prepareStatement(s)) {
        int param = 0;
        ps.setString(param++, (String) readValue.get("basecurrency"));
        ps.setDate(param++, new java.sql.Date(sdf.parse((String) readValue.get("data_date")).getTime()));
        for (Entry<String, Double> e : data) {
            ps.setDouble(param++, e.getValue());
        }
        ps.executeUpdate();
    }
}

As a side note, I would advise you to change the database table to something like this:

create table tblData(
   ID INT NOT NULL AUTO_INCREMENT,
   data_date DATE NOT NULL,
   source_currency varchar(3),
   target_currency varchar(3),
   rate REAL(16,10) NOT NULL,
   PRIMARY KEY (ID)
);

That is a fairly easy POJO which you can map with Hibernate. Then you create one entry per entry in JSON data list. This also has the advantage that you don't have to change the database if you want to support another currency.

Answered By - XtremeBaumer
Answer Checked By - Marilyn (JavaFixing Volunteer)