How to display data from any SQL Query and any DSN, using a single JSP Tag ?

Sripriya Srinivasan

Overview
In this article, we will learn how to use a single JSP tag for displaying records from the database using any SQL query on any DSN. This article is a natural extension of the previous one 'Accessing Database Using Tags In JSP Pages'. Therefore, if you have not familiarized yourself with the previous article, please do it now, before proceeding with this article.

Now, how do we make this 'generic way of accessing data' possible ? Simple.

Pass all the DSN and SQL query info in one parameter to the JSP Tag. ( you can also use multiple parameters, one for each piece of info. )

In the tag class, let us call it 'GenericDataAccess', we split up the parameter and use the resultant info in appropriate places.

In 'GenericDataAccess' class, we do not use the actual field names anywhere, but we use made up common field names like fld1, fld2, fld3..

Likewise, in 'GenericDataAccessTEI' class ( Tag Extra Info class ) too, we do not define actual field names, but we define fields fld1,fld2.... fld20. ( You may increase the upper limit of 20 to anything you want, depending on the maximum number of fields your SQL query may return. )

When calling the this JSP tag and displaying data in the JSP file, we use <%=fld1%>, <%=fld2%> and so on.

What does this article comprise of ?

The calling JSP Page
First, a look at the calling JSP Page, so that we best understand the tag class code.

<%@ taglib uri="/WEB-INF/tlds/DemoTags.tld" prefix="star" %> <html> <body> <table> <star:GenericDataAccess connString="DSN=jdbc:odbc:Pubs; UID=sa; PWD=password; SQL=SELECT title_id, title FROM Titles;"> <tr> <td><%=fld1%></td> <td><%=fld2%></td> <tr> </star:GenericDataAccess> </table> </body> </html>

Explanation
In the code above please make sure that the value for the connString attribute should be in one line. I had to divide it into two to make it fit in the page. Note:

The 'connString' parameter to the GenericDataAccess tag. UID and PWD can be empty, as will be the case in most Access DSNs.

Use of common field names <%=fld1%> and <%=fld2%> instead of <%=title_id%> and <%=title%>.

GenericDataAccess.java
The code snippets that differ from the previous article are in blue. Create a new GenericDataAccess.java source file in the /WEB-INF/classes/com/stardeveloper/tag/test folder. Copy and paste the following code in it :

package com.stardeveloper.tag.test; import java.io.*; import java.sql.*; import java.util.*; import javax.servlet.JSP.*; import javax.servlet.JSP.tagext.*; public class GenericDataAccess implements BodyTag { // start of class private PageContext pc = null; private BodyContent body = null; private StringBuffer sb = new StringBuffer(); private Connection con = null; private Statement stmt = null; private ResultSet rs = null; private String connString = null; public void setPageContext(PageContext p) { pc = p; } public void setParent(Tag t) { } public Tag getParent() { return null; } public void setConnString(String s) { connString = s; } public String getConnString() { return connString; } public int doStartTag() throws JSPException { try { String DSN = null; String UID = null; String PWD = null; String SQL = null; int tempX = 0; int tempY = 0; /* getting all the vars for connection and rs.. dsn, uid, pwd, sql */ tempX = connString.indexOf("DSN="); tempY = connString.indexOf(";", tempX ); DSN = connString.substring(tempX+4, tempY); tempX = connString.indexOf("UID="); tempY = connString.indexOf(";", tempX ); UID = connString.substring(tempX+4, tempY).trim(); tempX = connString.indexOf("PWD="); tempY = connString.indexOf(";", tempX ); PWD = connString.substring(tempX+4, tempY).trim(); tempX = connString.indexOf("SQL="); tempY = connString.indexOf(";", tempX ); SQL = connString.substring(tempX+4); Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); if ( UID.length() > 0 && PWD.length() > 0 ) con = DriverManager.getConnection(DSN, UID, PWD); else con = DriverManager.getConnection(DSN); stmt = con.createStatement(); rs = stmt.executeQuery(SQL); setVariables(); } catch (SQLException e) { throw new JSPTagException("A SQL Exception Occurred."); } catch (ClassNotFoundException e) { throw new JSPTagException("Jdbc Driver Not Found."); } return EVAL_BODY_TAG; } public void setBodyContent(BodyContent b) { body = b; } public void doInitBody() throws JSPException {} private boolean setVariables() throws JSPException{ try { if ( rs.next() ) { int i = 1; int errFlag = 0; while (i <= 20 && errFlag == 0) { try { String fld = "fld" + i; pc.setAttribute(fld, rs.getObject(i).toString()); i++; } catch (Exception e) { errFlag = 1; } } return true; } else { return false; } } catch (SQLException e) { throw new JSPTagException("SQLException occurred"); } } public int doAfterBody() throws JSPException { try { sb.append(body.getString()); body.clear(); } catch (IOException e) { throw new JSPTagException("Fatal Exception"); } if (setVariables()) return EVAL_BODY_TAG; try { body.getEnclosingWriter().write(sb.toString()); } catch (IOException e) { throw new JSPTagException("Fatal Exception"); } return SKIP_BODY; } public int doEndTag() throws JSPException { try { if ( rs != null ) { rs.close(); rs = null; } if ( stmt != null ) { stmt.close(); stmt = null; } if ( con != null ) { con.close(); con = null; } } catch (SQLException e) {} return EVAL_PAGE; } public void release() { pc = null; body = null; sb = null; } // end of class }

Compile this source file to generate GenericDataAccess.class file.

Explanation

a. connString connString private variable receives the parameter connString from the JSP tag. setConnString() and getConnString() are the corresponding setter and getter methods. ( Reminder: It is a must to make the first character of the parameter Uppercase, when defining these methods. )

b. setVariables()

int i = 1;
int errFlag = 0;

while ( i <= 20 & errFlag == 0 ) 	{
	try {
		String fld = "fld" + i;
		pc.setAttribute(fld, rs.getObject(i).toString());
		i++;
	} catch (Exception e) {
		errFlag = 1;
	}
}