Show Resultset as HTML table

There are times you simply want to show some result set as a table on the screen.
You do not need anything fancy, just show some tabular data on the screen.

In order to solve this issue I wrote a very small and lightweight Java class named: ResultSetToHtmlTable. Even through this class is doing a quite simple task it still have some nice qualities:

• It is very fast. It doesn't construct any string. It simply writes the table directly to a writer.
• It doesn't use any external libraries. All plain and simple Java.
• It is only a single class.
• It knows to format the data in each column. It supports the following formats:
• Integer - Show 1000 as 1,000
• Float - Show 0.5145235 as 0.51
• Currency - Show 5.2 as $5.2
• Percentage - Show 0.5 as 50%
• Date - Show 12/5/2014 as 5 Dec 2014
• Date and time - Show 12/5/2014 12:00:00 as 5 Dec 2014 12:00:00
• Time - Show only the time part of a date. For example: 13:22:15
• It allows to align the data to the left or right and add titles to columns.
• If no strict column information is supplied is knows to auto detect:
• Column types.
• Column titles - titles are named as column names returned in query.
• Column alignment - Numbers are aligned to the right.

The code of this class is quite simple and straightforward. Here is how it looks like:

package com.todacell.util.sql;

import java.io.IOException;
import java.io.Writer;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.text.DateFormat;
import java.text.Format;
import java.text.NumberFormat;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.Map;

/**
 * @author Bashan
 *         Date: 18/11/2014 15:07
 */
public class ResultSetToHtmlTable {

  public void writeTable(ResultSet rs, Writer writer, Column[] columns) throws IOException, SQLException {
    writer.write("<table>");
    // Write titles
    writer.write("\t<tr>\n");
    for (Column column : columns) {
      writer.write("\t\t<th>" + column.title + "</th>");
    }
    writer.write("\t</tr>\n");

    // Write data
    int cols = columns.length;
    while (rs.next()) {
      writer.write("\t<tr>\n");
      for (int i = 0; i < cols; i++) {
        ColumnType columnType = columns[i].columnType;
        String columnAlign = columnAlignToColumnCss.get(columns[i].columnAlign);
        writer.write("\t\t<td" + columnAlign + ">" + format(columnType, rs.getObject(i + 1)) + "</td>");
      }
      writer.write("\t</tr>");
    }

    writer.write("</table>");
  }

  private static Map<Integer, ColumnType> sqlTypeToColumnType = new HashMap<Integer, ColumnType>();
  private static Map<ColumnType, ColumnAlign> columnTypeTpColumnAlign = new HashMap<ColumnType, ColumnAlign>();
  private static Map<ColumnAlign, String> columnAlignToColumnCss = new HashMap<ColumnAlign, String>();

  static {
    // Map SQL types to column types

    // integer
    sqlTypeToColumnType.put(Types.INTEGER, ColumnType.INTEGER);
    sqlTypeToColumnType.put(Types.BIGINT, ColumnType.INTEGER);
    sqlTypeToColumnType.put(Types.SMALLINT, ColumnType.INTEGER);
    sqlTypeToColumnType.put(Types.TINYINT, ColumnType.INTEGER);
    sqlTypeToColumnType.put(Types.NUMERIC, ColumnType.INTEGER);

    // float
    sqlTypeToColumnType.put(Types.FLOAT, ColumnType.INTEGER);
    sqlTypeToColumnType.put(Types.DOUBLE, ColumnType.INTEGER);
    sqlTypeToColumnType.put(Types.DECIMAL, ColumnType.INTEGER);

    // date
    sqlTypeToColumnType.put(Types.TIME, ColumnType.DATE);
    sqlTypeToColumnType.put(Types.TIMESTAMP, ColumnType.DATE);

    // Map column type to column alignment
    columnTypeTpColumnAlign.put(ColumnType.INTEGER, ColumnAlign.RIGHT);
    columnTypeTpColumnAlign.put(ColumnType.FLOAT, ColumnAlign.RIGHT);
    columnTypeTpColumnAlign.put(ColumnType.CURRENCY, ColumnAlign.RIGHT);
    columnTypeTpColumnAlign.put(ColumnType.DATE, ColumnAlign.LEFT);
    columnTypeTpColumnAlign.put(ColumnType.STRING, ColumnAlign.LEFT);

    // Map column alignment to css style
    columnAlignToColumnCss.put(ColumnAlign.LEFT, "");
    columnAlignToColumnCss.put(ColumnAlign.RIGHT, " style=\"text-align:right\"");
    columnAlignToColumnCss.put(ColumnAlign.CENTER, " style=\"text-align:center\"");
  }

  public void writeTable(ResultSet rs, Writer writer) throws IOException, SQLException {
    ResultSetMetaData meta = rs.getMetaData();
    int cols = meta.getColumnCount();

    Column[] columns = new Column[meta.getColumnCount()];
    for (int i = 0; i < cols; i++) {
      ColumnType columnType = sqlTypeToColumnType.get(meta.getColumnType(i + 1));
      ColumnAlign columnAlign = columnTypeTpColumnAlign.get(columnType);
      columns[i] = new Column(
          columnType != null ? columnType : ColumnType.STRING,
          meta.getColumnName(i + 1),
          columnAlign != null ? columnAlign : ColumnAlign.LEFT);
    }

    writeTable(rs, writer, columns);
  }

  public enum ColumnType {
    STRING, INTEGER, FLOAT, DATE, DATE_TIME, TIME, CURRENCY, PERCENTAGE
  }

  public enum ColumnAlign {
    LEFT, RIGHT, CENTER
  }

  private static NumberFormat integerFormat = NumberFormat.getInstance();
  private static NumberFormat floatFormat = NumberFormat.getInstance();
  private static final NumberFormat percentFormat = NumberFormat.getPercentInstance();
  private static final NumberFormat currencyFormat = NumberFormat.getCurrencyInstance();
  private static final DateFormat dateFormat = new SimpleDateFormat("dd MMM yyyy");
  private static final DateFormat dateTimeFormat = new SimpleDateFormat("dd MMM yyyy hh:mm:ss");
  private static final DateFormat timeFormat = new SimpleDateFormat("hh:mm:ss");

  static {
    floatFormat.setMaximumFractionDigits(2);
    integerFormat.setMaximumFractionDigits(0);
  }

  public String format(ColumnType columnType, Object value) {
    String result;
    if (value != null) {
      if (columnType == ColumnType.STRING) {
        result = value.toString();
      } else {
        Format format;
        if (columnType == ColumnType.FLOAT) {
          format = floatFormat;
        } else if (columnType == ColumnType.CURRENCY) {
          format = currencyFormat;
        } else if (columnType == ColumnType.PERCENTAGE) {
          format = percentFormat;
        } else if (columnType == ColumnType.DATE) {
          format = dateFormat;
        } else if (columnType == ColumnType.DATE_TIME) {
          format = dateTimeFormat;
        } else if (columnType == ColumnType.TIME) {
          format = timeFormat;
        }else {
          format = integerFormat;
        }

        result = format.format(value);
      }
    } else {
      result = "N/A";
    }

    return result;
  }

  public static class Column {
    ColumnType columnType;
    String title;
    ColumnAlign columnAlign;

    public Column(ColumnType columnType, String title, ColumnAlign columnAlign) {
      this.columnType = columnType;
      this.title = title;
      this.columnAlign = columnAlign;
    }
  }
}

And here is a simple example of how to use this code in a JSP file:

 <%@ page import="com.todacell.management.SessionSingleton" %>  
 <%@ page import="com.todacell.util.sql.ResultSetToHtmlTable" %>  
 <%@ page import="org.hibernate.Session" %>  
 <%@ page import="java.sql.Connection" %>  
 <%@ page import="java.sql.ResultSet" %>  
 <%@ page import="java.sql.Statement" %>  
 <%@ page contentType="text/html;charset=UTF-8" language="java" %>  
 <html>  
 <head>  
   <title>Daily Deposits</title>  
   <style type="text/css">  
     table {  
       border-collapse: collapse;  
     }  
     table, th, td {  
       border: 1px solid black;  
     }  
     td, th {  
       padding: 5px;  
     }  
   </style>  
 </head>  
 <body>  
 <jsp:include page="k2_menu.jsp" />  
 <h1>Daily Deposits</h1>  
 <%  
   Session hSession = SessionSingleton.instance();  
   Connection conn = hSession.connection();  
   try {  
     Statement stmt = conn.createStatement();  
     ResultSet rs = stmt.executeQuery("select * from k2_deposits(60)");  
     ResultSetToHtmlTable resultSetToHtmlTable = new com.todacell.util.sql.ResultSetToHtmlTable();  
     resultSetToHtmlTable.writeTable(rs, out,  
         new com.todacell.util.sql.ResultSetToHtmlTable.Column[] {  
             new ResultSetToHtmlTable.Column(ResultSetToHtmlTable.ColumnType.DATE, "Date", ResultSetToHtmlTable.ColumnAlign.LEFT),  
             new ResultSetToHtmlTable.Column(ResultSetToHtmlTable.ColumnType.CURRENCY, "Deposits", ResultSetToHtmlTable.ColumnAlign.RIGHT),  
             new ResultSetToHtmlTable.Column(ResultSetToHtmlTable.ColumnType.CURRENCY, "Allocated", ResultSetToHtmlTable.ColumnAlign.RIGHT),  
             new ResultSetToHtmlTable.Column(ResultSetToHtmlTable.ColumnType.CURRENCY, "Deallocated", ResultSetToHtmlTable.ColumnAlign.RIGHT),  
             new ResultSetToHtmlTable.Column(ResultSetToHtmlTable.ColumnType.CURRENCY, "Ad Spend", ResultSetToHtmlTable.ColumnAlign.RIGHT),  
             new ResultSetToHtmlTable.Column(ResultSetToHtmlTable.ColumnType.CURRENCY, "Potential Spend", ResultSetToHtmlTable.ColumnAlign.RIGHT),  
             new ResultSetToHtmlTable.Column(ResultSetToHtmlTable.ColumnType.INTEGER, "New Users", ResultSetToHtmlTable.ColumnAlign.RIGHT)  
         });  
   } finally {  
     if (hSession != null) {  
       hSession.close();  
     }  
   }  
 %>  
 </body>  
 </html>  

And here is how it looks on the browser:

DOWNLOAD: You can download the ResultSetToHtmlTable here

Show Java Quartz Scheduler information

Quartz is a great open source project that allows to do Job Scheduling. Quartz is a production stage open source project that has been around long enough to be trusted. You can integrate it in your applications in a very short time and benefit from simple to complex scheduling requirements.
After you integrate Quartz to your project, you don’t really know what is going on. Which job is currently running/not running, when was the last time a job fired, when will the job will fire again.
In order to know the state of your jobs, Quartz provides several informative methods. We will use these methods in order to visually show Quartz scheduler information. The information will be shown using a JSP page that will dynamically gather Quartz information and build a table of all the current existing jobs.
This is an example of how this page looks like:

 
The Quartz data that is shown in the table is:

• Group – The group of the job (in Quartz every job belongs to a group).
• Job – The name of the job.
• Cron Expression – An expression that describes the times in which the job should run. Note, that not all Quartz jobs use cron expressions for their execution. It is also possible to schedule jobs to run according specific intervals.
• Previous fire time – The last time in which job was executed.
• Next fire time – The next time a job is scheduled to run.
• Stateful – An indication if a job is Stateful. When a job is Stateful, no more than a single instance of that job will run at any given time.
• Interruptable – Indication if the job can be interrupted. A job that can be interrupted should implement InterruptableJob interface.
• Running – An indication if a job is currently running or not. In parenthesis will be shown the number of instances of the job that are currently running.
• Actions – This column allows to do 2 actions with Jobs:
  • Exec – Allows to immediately execute a job without waiting for it to be fired.
  • Pause/Resume – Pause/Resume a job. This will prevent the job from being executed by the scheduler.

In addition to this data, note that a running job is marked as bold and a paused job is marked in red bold.
Let’s have a look at the JSP file that creates this table. It mainly contains 2 parts:

• The first part is a Java code that handles the 2 actions: Exec and Pause/Resume. It is responsible of executing a job if the Exec action was pressed or Pausing/Resuming a job if a job was paused/resumed.
• The second part, is the part that actually gets all the jobs information from Quartz and shows it as a table.

The JSP file that creates this table, gets an instance of Quartz Scheduler class from a singleton named: SchedulerSingleton. You can replace this line of code with whatever way you use to get access to your Scheduler instance.
And here is how the code looks like:

<%@ page import="com.todacell.management.SchedulerSingleton" %>
<%@ page import="org.quartz.*" %>
<%@ page import="java.util.Arrays" %>
<%@ page import="java.util.Date" %>
<%@ page import="java.util.List" %>
<%@ page contentType="text/html;charset=UTF-8" language="java" %>
<html>
<%
try
{
  Scheduler scheduler = SchedulerSingleton.instance();
  String action = request.getParameter("action");
  if (action != null)
  {
    String groupParam = request.getParameter("group");
    String jobParam = request.getParameter("job");
    if (groupParam != null && jobParam != null) 
    {
      if ("exec".equals(action))
      {
        scheduler.triggerJob(jobParam, groupParam);
      }
      else if ("pause".equals(action))
      {
        if (scheduler.getTriggerState(jobParam, groupParam) == Trigger.STATE_PAUSED)
        {
          scheduler.resumeTrigger(jobParam, groupParam);
        }
        else
        {
          scheduler.pauseTrigger(jobParam, groupParam);
        }
      }
      response.sendRedirect("jobs.jsp");
      return;
    }
  }
%>
<head>
<title>Management Jobs</title>
<link rel="stylesheet" type="text/css" href="css.css">
</head>
<body>
<h1>Jobs Information</h1>
Current Time: <b><%= new Date() %></b><br/>
Scheduler State: <b><%= scheduler.isStarted() ? "Running" : "Not Running" %></b><br/><br/>
<table class="tableBorder" cellpadding="4" cellspacing="0">
<tr>
<td class="tdBorder bold center">
Group
</td>
<td class="tdBorder bold center">
Job
</td>
<td class="tdBorder bold center">
Cron Expression
</td>
<td class="tdBorder bold center">
Previous Fire Time
</td>
<td class="tdBorder bold center">
Next Fire Time
</td>
<td class="tdBorder bold center">
Stateful
</td>
<td class="tdBorder bold center">
Interruptable
</td>
<td class="tdBorder bold center">
Running
</td>
<td class="tdBorder bold center">
Actions
</td>
</tr>
<%
  String[] groups = scheduler.getJobGroupNames();
  Arrays.sort(groups);
  List<JobExecutionContext> executingJobs = scheduler.getCurrentlyExecutingJobs();
  for (String group : groups)
  {
    String[] jobs = scheduler.getJobNames(group);
    Arrays.sort(jobs);
    int i = 0;
    for (String job : jobs)
    {
      Trigger trigger = scheduler.getTrigger(job, group);
      JobDetail jobDetail = scheduler.getJobDetail(job, group);
      int numInstances = 0;
      for (JobExecutionContext jobExecutionContext : executingJobs)
      {
        JobDetail execJobDetail = jobExecutionContext.getJobDetail();
        if (execJobDetail.getKey().equals(jobDetail.getKey()))
        {
          numInstances++;
        }
      }
      boolean isPaused = scheduler.getTriggerState(job, group) == Trigger.STATE_PAUSED;
%>
<tr>
<%
if (i == 0)
{
%>
<td class="tdBorder" rowspan="<%= jobs.length %>">
<%= group %>
</td>
<%
}
%>
<td class="tdBorder <%= isPaused ? "error bold" : "" %>">
<%= job %>
</td>
<td class="tdBorder">
<%= trigger != null && trigger instanceof CronTrigger ? ((CronTrigger)trigger).getCronExpression() : "N/A" %>
</td>
<td class="tdBorder">
<%= trigger != null ? (trigger.getPreviousFireTime() != null ? trigger.getPreviousFireTime()  : "N/A") : "N/A" %>
</td>
<td class="tdBorder">
<%= trigger != null ? trigger.getNextFireTime() : "&nbsp;" %>
</td>
<td class="tdBorder center">
<%= StatefulJob.class.isAssignableFrom(jobDetail.getJobClass()) ? "YES" : "NO" %>
</td>
<td class="tdBorder center">
<%= InterruptableJob.class.isAssignableFrom(jobDetail.getJobClass()) ? "YES" : "NO" %>
</td>
<td class="tdBorder center">
<%= numInstances > 0 ? "<b>YES (" + numInstances + ")</b>" : "NO" %>
</td>
<td class="tdBorder center">
<a href="jobs.jsp?action=exec&group=<%= group %>&job=<%= job %>">Exec</a>&nbsp;|&nbsp;<a
href="jobs.jsp?action=pause&group=<%= group %>&job=<%= job %>"><%= isPaused ? "Resume" : "Pause" %></a>
</td>
</tr>
<%
i++;
}
}
%>
</table>
</body>
<%
}
catch (SchedulerException se) {
  se.printStackTrace();
}
%>
</html>

Note that this JSP file uses a style shit file named “css.css”. This css file gives a bit nicer look to the Jobs information page. You can download the “jobs.jsp” file as well as the “css.css” file from here.