Appending SQL data into datatables using JAVA in JSP

Question

In my SQL Server I have the following result sets after all the condition filtering and sum query execution.

I would like to be shown like this in my page (refer to the screenshot below).

I have tried the below JAVA code that gave me the results that I appended into my datatables.

<%
ArrayList<String[]> rows = sqlq.querySQL();
String rowsetdate = new String();
String rowres1 = new String();
    for(String[] rowset : rows) { 
      rowsetdate = rowset[0];
      rowres1 = rowres1 + rowset[1]+ ",";
        for(String rowres2 : rowset) {
        rowres1 = rowres1 + rowres2 + ",";
         }
      rowres1 = rowres1.substring(0, rowres1.length()-1);
      rowres1 = rowres1 + "|";
     }
rowres1 = rowres1.substring(0, rowres1.length()-1);
%>

<tr>
<td><%if (rowres1  == null) out.print(""); else out.print(rowres1);%></td>
</tr>

sqlq.querySQL() is used to send my SQL query to JDBC in order for me to send query to my DB.

Photo below is the appended data in my datatables after the code execution, on the left is the Date and on the right is the data.

I tried some different code,

<%
ArrayList<String[]> rows = sqlq.querySQL();
 for(String[] rowset : rows) {
<tr>
<td><%if (rowset[0]  == null) out.print(""); else out.print(rowset[0]);%></td>
<td><%if (rowset[1]  == null) out.print(""); else out.print(rowset[1]);%></td>
</tr>
}
%>

which did not achieve my expected results also, it returns the data like how I see it in my SSMS (check screenshot below)

What did I do wrong and how should I do it to get my expected outcome? (screenshot below)

Appreciate the help from all of you.

Answer 1

You can use a Map that its key is date and its value again is a Map. Inner Map uses trans as key and sumtot as value.

    Map<String, Map<String, String>> mapByDate = new HashMap<>();
    TreeSet<String> allTrans = new TreeSet<>();

    for (String[] row : rows) {
      Map<String, String> mapByDateAndTrans = mapByDate.get(row[0]);
      if (mapByDateAndTrans == null) {
        mapByDateAndTrans = new HashMap<>();
      }
      mapByDateAndTrans.put(row[1], row[2]);
      mapByDate.put(row[0], mapByDateAndTrans);
      allTrans.add(row[1]);
    }

Here is a sample code to print data as you might expect:

    System.out.println("Date/Trans: " + allTrans);
    for (Map.Entry<String, Map<String, String>> mapByDateEntry : mapByDate.entrySet()) {
      System.out.print(mapByDateEntry.getKey() + ": ");
      Map<String, String> mapByTrans = mapByDateEntry.getValue();
      for (String trans : allTrans) {
        String sumtot = mapByTrans.get(trans);
        if (sumtot != null) {
          System.out.print("[ " + sumtot + " ]");
        } else {
          System.out.print("[   ]");
        }
      }
      System.out.println();
    }

The output:

Date/Trans: [11200, 11201, 11202]
2019-07-02: [ 136 ][ 18 ][ 14 ]
2019-07-03: [ 164 ][ 10 ][ 8 ]

Or we can generate an HTML table content:

    StringBuilder tableBuilder = new StringBuilder("<table border = 1>");
    // table header
    tableBuilder.append("<tr>");
    tableBuilder.append("<th>date/trans</th>");
    for (String trans : allTrans) {
      tableBuilder.append("<th>").append(trans).append("</th>");
    }
    tableBuilder.append("</tr>");

    // table rows
    for (Map.Entry<String, Map<String, String>> mapByDateEntry : mapByDate.entrySet()) {
      tableBuilder.append("<tr>");
      tableBuilder.append("<td>").append(mapByDateEntry.getKey()).append("</td>");
      Map<String, String> mapByTrans = mapByDateEntry.getValue();
      for (String trans : allTrans) {
        String sumtot = mapByTrans.get(trans);
        if (sumtot != null) {
          tableBuilder.append("<td>").append(sumtot).append("</td>");
        } else {
          tableBuilder.append("<td></td>");
        }
      }
      tableBuilder.append("<tr>");
    }
    tableBuilder.append("</table>");

    System.out.println(tableBuilder.toString());

The Output:

<table border = 1><tr><th>date/trans</th><th>11200</th><th>11201</th><th>11202</th></tr><tr><td>2019-07-02</td><td>136</td><td>18</td><td>14</td><tr><tr><td>2019-07-03</td><td>164</td><td>10</td><td>8</td><tr></table>

If we save generated output as an HTML file, It maybe your desired result (screenshot below). Also you can change the code to be used in JSP:

To have an ordered Map by natural order of its keys, a TreeMap can be used. So to print the data ordered by date, we can construct a new TreeMap containing the mapByDate data:

    TreeMap<String, Map<String, String>> sortedMapByDate = new TreeMap<>(mapByDate);
    // table rows
    for (Map.Entry<String, Map<String, String>> mapByDateEntry : sortedMapByDate.entrySet()) {
      tableBuilder.append("<tr>");
      tableBuilder.append("<td>").append(mapByDateEntry.getKey()).append("</td>");
      Map<String, String> mapByTrans = mapByDateEntry.getValue();
      for (String trans : allTrans) {
        String sumtot = mapByTrans.get(trans);
        if (sumtot != null) {
          tableBuilder.append("<td>").append(sumtot).append("</td>");
        } else {
          tableBuilder.append("<td></td>");
        }
      }
      tableBuilder.append("<tr>");
    }