Appending SQL data into datatables using JAVA in JSP

user_10856025 0
Viewed 115

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

SQL Results

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

![Actual Results Needed

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.

Sets of 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)

wrong sql

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

expected outcome

Appreciate the help from all of you.

jsp java
2 Answers

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:

enter image description here

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>");
    }
user_57672180

It's wasteful to do this in Java code, that's what the window functions are for in SQL. If you have a query like SELECT datet, trans, sumtout FROM ... you can use SUM with OVER:

SELECT DISTINCT datet, SUM(sumtout) OVER (PARTITION BY datet)
FROM ...
ORDER BY datet;
user_16025550
Related