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:

I downvoted because no attempt was made, e.g. writing a SELECT statement, retrieve data using JDBC, JPA, or other means, manipulate the data into the desired form, generating JSON from some in-memory data structure. In short, as question currently stands, it is way too broad. — Andreas
– Andreas, Commented Oct 17, 2018 at 5:38
@Andreas i am using hash map from one query i am calling the outlets and storing them into list and running the next query on the size of that 1st query to get dates but i am not able to loop them into {}. — manish thakur
– manish thakur, Commented Oct 17, 2018 at 5:42
Don't try to explain in text in a comment. Edit the question, explain there and show the code you do have and explain where specifically you are stuck. — Andreas
– Andreas, Commented Oct 17, 2018 at 5:43

Lukas Eder · Accepted Answer · 2020-11-20 14:20:13Z

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).

Pushpesh Kumar Rajwanshi · Accepted Answer · 2018-10-17 05:52:56Z

1

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.

answered Oct 17, 2018 at 5:52

Pushpesh Kumar Rajwanshi

18.4k2 gold badges22 silver badges39 bronze badges

4 Comments

Pushpesh Kumar Rajwanshi Over a year ago

then even that you can write them in hashmap keys just like I hardcoded. Instead of hardcoding "1" and "2" you can write rs.getString("yourcolumnname")

Pushpesh Kumar Rajwanshi Over a year ago

Sure, let me know if you get stuck anywhere. You can run my code as it is and it will output the json string exactly as you want.

Pushpesh Kumar Rajwanshi Over a year ago

Your schema was not clear as the image you attached doesn't show all columns. If you can share the schema or even query I should be able to help you better.

manish thakur Over a year ago

Let us continue this discussion in chat.

Collectives™ on Stack Overflow

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

2 Answers 2

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

Next, let's produce JSON

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

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

Next, let's produce JSON

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related