How to map SQL data into Java JSON object and JSON array?

Question

I am working in Java to build JSON objects and want to send them into a JSON array all because of unsorted data into my database. I am not able to map the data into right format. I think I am lacking in logic.

Below is the image of my database table, which is in MySQL:

What I want to create is a JSON format for this table which looks like this:

  [
  {
    "1": "450",
    "2": "495",
    "OUTLET": "TOTTAL2",
    "BILLDATE": "",
    "TOTAL": "945"
  },
  {
    "1": "10",
    "2": "15",
    "OUTLET": "Ol1",
    "BILLDATE": "08-21-2018",
    "TOTAL": "25"
  },
  {
    "1": "20",
    "2": "25",
    "OUTLET": "ol1",
    "BILLDATE": "08-22-2018",
    "TOTAL": "45"
  },
  {
    "1": "30",
    "2": "35",
    "OUTLET": "ol1",
    "BILLDATE": "08-23-2018",
    "TOTAL": "65"
  },
  {
    "1": "40",
    "2": "45",
    "OUTLET": "ol2",
    "BILLDATE": "08-21-2018",
    "TOTAL": "85"
  },
  {
    "1": "50",
    "2": "55",
    "OUTLET": "ol2",
    "BILLDATE": "08-22-18",
    "TOTAL": "105"
  },
  {
    "1": "60",
    "2": "65",
    "OUTLET": "ol2",
    "BILLDATE": "08-23-2018",
    "TOTAL": "125"
  },
  {
    "1": "70",
    "2": "75",
    "OUTLET": "ol3",
    "BILLDATE": "08-21-2018",
    "TOTAL": "145"
  },
  {
    "1": "80",
    "2": "85",
    "OUTLET": "ol3",
    "BILLDATE": "08-22-2018",
    "TOTAL": "165"
  },
  {
    "1": "90",
    "2": "95",
    "OUTLET": "ol3",
    "BILLDATE": "08-23-201818",
    "TOTAL": "185"
  }
]

The above JSON is my desired output. But I am not able to map the data in loop or I am not getting the logic. I just want to create a JSON format for the html table I have also provided the json that I want.

The place where I am lagging is in coding and thinking of logic that how could I loop the data to get desired format:

And this is the table which i want to make

I have knowledge of GSON in java to parse json (send json in Java) so I can do that the only thing I want is to like how can I code forth.

Here is my Java code code by which I am getting the first header

    String TotalAmountWithDateSql = "quwry1";
//          System.out.println("TotalAmountWithDateSql"+TotalAmountWithDateSql);
            String GrandTotalSql = "query2";
//          System.out.println("grandTotal"+GrandTotalSql);

            try {
                con = DBConnection.createConnection();
                statement = con.createStatement();

                ResultSet resultSet = statement.executeQuery(GrandTotalSql);

                while (resultSet.next()) {

                    map.put("OUTLET/HOURS", "  ALL");
                    GrandTotal = resultSet.getLong("TOTAL");
                    map.put("TOTAL", GrandTotal);

                    resultSet = statement.executeQuery(TotalAmountWithDateSql);
                    while (resultSet.next())

                    {

                        BillTime = resultSet.getString("TIME");
                        NetAmtWithTime = resultSet.getLong("AMOUNT");
                        map.put(BillTime, NetAmtWithTime);
                    }
                    list.add(map);
                    str = gson.toJson(list);
                }
                System.out.println("value  " + str);
                response.setContentType("application/json");
                response.getWriter().write(str);

from this Java code I am getting

Now I want to call data below this like the JSON I have uploaded, but this is only giving me the first header:

Answer 1

Let's take a step back and try to do everything in SQL. MySQL can calculate grand totals using WITH ROLLUP (similar to standard SQL GROUPING SETS), you don't need two round trips. Also, MySQL supports (to some extent) standard SQL/JSON, so you don't have to do any formatting in Java.

First, let's try to write that SQL query without producing JSON:

SELECT
  CASE 

    -- The label of the grand total
    WHEN GROUPING(outlet) = 1 THEN 'TOTAL2'

    -- Prevent printing the outlet on rows 2+ per outlet group
    WHEN row_number() OVER (PARTITION BY outlet ORDER BY billdate) = 1 THEN outlet 
  END AS outlet,
  billdate,
  SUM(netAmount) AS `Total1/Ho...`,

  -- This is just a silly assumption based on your data.
  -- Your actual formula might be more involved
  SUM(CASE WHEN mod(hours, 2) = 1 THEN netAmount END) AS `1`,
  SUM(CASE WHEN mod(hours, 2) = 0 THEN netAmount END) AS `2`

  -- Possibly more?
FROM t
GROUP BY outlet, billdate WITH ROLLUP

-- Get only the grand total or ordinary groupings
HAVING GROUPING(outlet) = 1 OR GROUPING(billdate) = 0

-- Possibly add ORDER BY here to get the desired ordering if that's required

Next, let's produce JSON

For simplicity, I will not rewrite the entire query above, but put it in a CTE, which is a handy tool to structure SQL:

WITH
  t AS (
    -- Copy paste previous query here
  )
SELECT
  json_arrayagg(json_object(
     '1', t.`1`,
     '2', t.`2`,
     'OUTLET', t.outlet,
     'BILLDATE', t.billdate,
     'TOTAL', t.total
  ))
FROM t

dbfiddle here

Note, you could also use a third party library like jOOQ, which can export data as JSON out of the box, or which supports SQL/JSON functions (disclaimer, I work for the company behind jOOQ).

Answer 2

This is pretty simple indeed. You need to fetch all records and create a list which contains map object where each map object will contain those five key value pairs that you want.

Following is the kind of code you will need to write,

public static void main(String[] args) throws IOException {
    createJson();
}

public static List<Map<String, String>> getMapList(ResultSet rs) throws SQLException {
    List<Map<String, String>> mapList = new ArrayList<Map<String,String>>();

    while(rs.next()) {
        Map<String, String> map = new LinkedHashMap<String, String>();
        map.put("1", rs.getString("col1"));
        map.put("2", rs.getString("col2"));
        map.put("OUTLET", rs.getString("outlet"));
        map.put("BILLDATE", rs.getString("billdate"));
        map.put("TOTAL", rs.getString("total"));
        mapList.add(map);
    }
    return mapList;
}

public static void createJson() {
    List<Map<String, String>> mapList = new ArrayList<Map<String,String>>();

    Map<String, String> map1 = new LinkedHashMap<String, String>();
    map1.put("1", "50");
    map1.put("2", "55");
    map1.put("OUTLET", "ol2");
    map1.put("BILLDATE", "08-22-18");
    map1.put("TOTAL", "105");

    Map<String, String> map2 = new LinkedHashMap<String, String>();
    map2.put("1", "60");
    map2.put("2", "65");
    map2.put("OUTLET", "ol3");
    map2.put("BILLDATE", "08-23-18");
    map2.put("TOTAL", "125");

    mapList.add(map1);
    mapList.add(map2);

    String json = new Gson().toJson(mapList);
    System.out.println(json);
}

Hope this helps.