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
