We have 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 vlaues",
 }
}

We want to push to a MySQL table structure as below

create table tblData(
   ID INT NOT NULL AUTO_INCREMENT,
   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 to map this JSON in Java/Spring Boot and push it MySQL. Do we have to create a POJO of 100 fields and is there any better way to do it?

Thanks

CodePudding user response：

Step 1: create a wrapper class which defines data as a Map<String, Double>:

public class DataWrapper {
    @JsonProperty("data")
    private Map<String, Double> data;
    
    public DataWrapper() {}
}

Step 2, read your JSON into it, e.g. via Jackson (I've used a limited example here, for brevity):

DataWrapper dataWrapper = objectMapper.readValue("{\"data\": {\"AED\": 3.671159, \"AFN\": 89.81064}}", DataWrapper.class);

Step 3, generate some SQL:

String tableName = "tblData";
String columnNames = String.join(", ", dataWrapper.data.keySet());
String placeholders = dataWrapper.data.keySet().stream().map(key -> ":"   key).collect(Collectors.joining(", "));

String sql = "INSERT INTO %s (%s) VALUES (%s);".formatted(tableName, columnNames, placeholders);

Step 4, pass it to the JdbcTemplate (you've tagged the question as spring-boot, so I'm assuming this is OK):

jdbcTemplate.update(sql, dataWrapper.data());

This strategy removes the need for a POJO with 100 fields. Hope this helps

CodePudding user response：

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 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 1 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