How to display data from any SQL Query and any DSN, using a single JSP Tag ?by 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
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
|