HTML.Template.java

How to use HTML.Template with a database

One of the most common uses of HTML.Template is for webpages populated from a database. We will try and build a template that maps onto records in a database.

Table Structure

For our example, we'll use a student table with the following structure:

The template

For this simple example, all we'll do is get a listing of all students. Our template will look like this:

<table>
<tr><th>Name</th> <th>Roll</th> <th>Year</th> <th>Final Score</th></tr>
<tmpl_loop name="students">
<tr valign="top">
<td><tmpl_var name="name"></td>
<td><tmpl_var name="roll"></td>
<td><tmpl_var name="year"></td>
<td><tmpl_var name="final_score"></td>
</tr>
</tmpl_loop>
</table>

Of course, this would be <tmpl_include>ed into a larger template.

The Java

Assuming you already have a template object called tmpl and a SQL Statement object, this snippet of code will transfer all rows of the table into the template:

String sql = "Select * from STUDENTS order by Roll";

Vector students = new Vector();
try 
{
	ResultSet rs = stmt.executeQuery(sql);
	while(rs.next()) 
	{
		Hashtable h = new Hashtable();
		ResultSetMetaData rsmd = rs.getMetaData();

		int cnt = rsmd.getColumnCount();
		for(int i = 1; i <= cnt; i++) 
		{
			String key = rsmd.getColumnName(i);
			int type = rsmd.getColumnType(i);
			if(type == Types.CHAR || type==Types.VARCHAR)
			{
				h.put(key, rs.getString(i));
			}
			else if(type == Types.NUMERIC)
			{
				h.put(key, "" + rs.getFloat(i));
			}
		}
		students.addElement(h);
	}
} 
catch(SQLException se) 
{
	System.err.println(se);
}

tmpl.setParam("students", students);

If your table has fields of other types, you'll have to test those types as well, and use the appropriate get* method for the ResultSet. Template vars are capable of storing scalar data of type String, Integer, Boolean, int and boolean. Anything else will have to be coerced into a String.

That's it. There's nothing more to it. Of course, if your query were more complex, and you wanted a control break report, your template and your java code would increase proportionally in complexity. I'll leave that for you to figure out, but feel free to send in code snippets that you think will be useful for others.

Also note, since the Template object is case-insensitive by default, we don't really need to worry about matching the case of the table fields with the case of the template variables.