Accessing Database using Tags in JSP Pages

Overview
In this tutorial we will learn how to access database and display data from it's tables using JDBC in JSP tags. We have already discussed JDBC and learned how to access database in 'Displaying Records from Database'. So what is new in this tutorial? well we will learn how to use JDBC in JSP tags and then more importantly how to iterate in JSP tags and show off all the records. This is important and as you go through it you'll realize that I was right.

In the last article on JSP tags; ' Introducing Scripting Variables in JSP Tags', we learned how to declare page-level variables from JSP tags. This is the same technique we will use in this tutorial.

One more thing that you'll learn is to use the BodyTag interface. In the previous two JSP tag articles we've been using Tag interface. BodyTag is important when you have to manipulate the body content ( remember body content is the content between starting and ending tags ) or have to iterate and each time show different body content.

What is BodyTag interface ?
As I just said, BodyTag interface extends Tag interface and you can find both of these interfaces in the javax.servlet.jsp.tagext package. A JSP tag class *must* implement one of these interfaces to be used as a tag. You can do it directly which is more efficient if you know what you are doing ( which is what we have been doing and will do in this tutorial too ) or you can extend TagSupport or BodyTagSupport classes which provide default implementation for these interfaces respectively.

What is different in BodyTag interface ?
BodyTag interface contains all the six methods that Tag interface has, plus it has got three additional methods which let you do two tasks :

• Access and manipulate body content of the tag.
• Iterate repeatidly as many times as you want.

Now this you cannot do alone with the Tag interface. Since we are going to access body content of the tag, let's be clear what is body content.

<prefix:tagName>
	This is the body content of prefix:tagName tag.
</prefix:tagName>

In the code above, the text in maroon is the body content for prefix:tagName tag. Since everything in Java is represented as objects, this body content has also an associated class, called as BodyContent.

Let's see what are the different methods in BodyTag interface :

	setPageContext(PageContext p)
	setParent(Tag t)
	getParent()
	doStartTag()
	setBodyContent(BodyContent b)
	doInitBody()
	doAfterBody()
	doEndTag()
	release()

The three new methods in BodyTag interface have been marked in maroon above. I'll assume here that you have read my earlier articles on the basics of JSP tags, if not then I suggest you do that. Ok, setPageContext() and setParent() are called by the JSP page to make us available PageContext and parent Tag objects. If a tag has no parent tag then this setParent() method is useless. Then there is a corresponding getParent() method.

After the first three comes the doStartTag() method. This is called when the start of tag is reached. This method will be called only once. Till now everything is same as was in Tag interface.

Now come the three new methods. setBodyContent() is called by JSP page to make us available the body content of the tag in the form of BodyContent class. BodyContent class extends JspWriter class and thus can be used to write output back to the client. Once body content is set we move forward. Remember if there is no body content for that tag ( i.e. the tag is empty ), setBodyContent() will not be called.

doInitBody() like setBodyContent() will only be called if there is any body content of the tag.

doAfterBody() is called when body content of the tag has been evaluated. Now is the time to access and manipulate the body content if we want to. So this method is really important. There are two return options for this tag; EVAL_BODY_TAG and SKIP_BODY. Returning EVAL_BODY_TAG will cause body content of the tag to be evaluated again, so doAfterBody() will be called again. As long as you keep returning EVAL_BODY_TAG, doAfterBody() will be called again and again. The second return option is SKIP_BODY which once returned causes the loop to end and doAfterBody() will not be called again.

Remember that like above two methods, doAfterBody() will not be called if tag has no body content or doStartTag() returned SKIP_BODY.

doEndTag() is called when end of the tag is reached. release() is the last method JSP tag class to be called. In this method you can release the resources you might have accumulated in above methods.

DataAccessTag Class
Create a new DataAccessTag.java source file in the /WEB-INF/classes/com/stardeveloper/tag/test folder. Copy 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 final class DataAccessTag implements BodyTag {

	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;

	public void setPageContext(PageContext p) {
		pc = p;
	}

	public void setParent(Tag t) {}

	public Tag getParent() { return null; }

	public int doStartTag() throws JspException {
		String path = "jdbc:odbc:Names";
		String sql = "SELECT ID, first_name, last_name FROM Names";
		try {

		Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		con = DriverManager.getConnection(path);

		stmt = con.createStatement();
		rs = stmt.executeQuery(sql);

		setVariables();

		} catch (SQLException e) {
		throw new JspTagException("An SQLException 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 JspTagException {
	try {
		if (rs.next()) {

		pc.setAttribute("id", rs.getObject(1).toString());
		pc.setAttribute("first_name", rs.getObject(2).toString());
		pc.setAttribute("last_name", rs.getObject(3).toString());
			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 IOException!");
		}
		
		if(setVariables()) {
			return EVAL_BODY_TAG;
		}

		try {
			body.getEnclosingWriter().write(sb.toString());
		} catch (IOException e) {
			throw new JspTagException("Fatal IOException!");
		}

		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;
	}
}

Now save DataAccessTag.java and compile it to generate DataAccessTag class file.

<prefix:tagName>
	This is the body content of prefix:tagName tag.
</prefix:tagName>

	setPageContext(PageContext p)
	setParent(Tag t)
	getParent()
	doStartTag()
	setBodyContent(BodyContent b)
	doInitBody()
	doAfterBody()
	doEndTag()
	release()

package com.stardeveloper.tag.test;

import java.io.*;
import java.sql.*;
import java.util.*;
import javax.servlet.jsp.*;
import javax.servlet.jsp.tagext.*;

public final class DataAccessTag implements BodyTag {

	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;

	public void setPageContext(PageContext p) {
		pc = p;
	}

	public void setParent(Tag t) {}

	public Tag getParent() { return null; }

	public int doStartTag() throws JspException {
		String path = "jdbc:odbc:Names";
		String sql = "SELECT ID, first_name, last_name FROM Names";
		try {

		Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
		con = DriverManager.getConnection(path);

		stmt = con.createStatement();
		rs = stmt.executeQuery(sql);

		setVariables();

		} catch (SQLException e) {
		throw new JspTagException("An SQLException 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 JspTagException {
	try {
		if (rs.next()) {

		pc.setAttribute("id", rs.getObject(1).toString());
		pc.setAttribute("first_name", rs.getObject(2).toString());
		pc.setAttribute("last_name", rs.getObject(3).toString());
			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 IOException!");
		}
		
		if(setVariables()) {
			return EVAL_BODY_TAG;
		}

		try {
			body.getEnclosingWriter().write(sb.toString());
		} catch (IOException e) {
			throw new JspTagException("Fatal IOException!");
		}

		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;
	}
}