Extract Data with DataTables

A tabular display of data on a Web application is nothing new, nor is the
ongoing struggle by developers to make it more and more intuitive and
functional-selectable, editable and sortable. The DataGrid components, such as
the one provided by ASP.NET, have been a boon for developers. Whether it's to
pull data from a database, sort or update data, everything can be done by
writing just a few lines of tags and even fewer lines of code. Here we look at a
DataGrid-like component called DataTable provided by JavaServer Faces technology
for J2EE Web applications. 

In the first part of this series, (MyFaces, page 81, PCQuest, July 2005) we
walked you through setting up MyFaces (an open source implementation of JSF
technology) and coding a small JSF Web application. For this article, we assume
that you have already set up MyFaces on PCQLinux 2005. 

Prerequisites 

For the code in this article we will retrieve data from a MySQL database
named 'contact' and display it in the grid. To set up MySQL on PCQLinux
2005, refer to the MySQL section in the article JDBC Drivers, page 76, PCQuest,
March 2005.

Next, create the database 'contact' as:

mysqladmin -u root -p create contact

When prompted, type in the password for the MySQL root user. Next, issue:

mysql -u root -p contact 

Again supply the password and you will be dropped into a mysql>prompt. At
this prompt, create a table named 'info' as:

create table info(name varchar(20), address varchar(50),email varchar(30));

Populate the table with data as:

insert into info (name,address,email) values ('ab','Delhi','ab@foo.com');

Insert some more contact info by issuing the above insert statement with
different names, addresses and e-mail ids.

A simple datatable 

Save the following content in a file named datatable.jsp in /opt/tomcat/webapps/myfaces
directory. 

<%@ taglib uri="http://java.sun.com/jsf/html"
prefix="h"%>

<%@ taglib uri="http://java.sun.com/jsf/core"
prefix="f"%> 







border="1"> 

 



 

 

 







 

 

 







 

 

 

 

As explained in the first part of the series that we carried in our July 2005
issue, all JSF tags including the datatable tag 

must lie between the and tag pair. For the value
attribute of the datatable, we need to supply the results 

of the database query. We'll do this through a JavaBean. Each column to be
displayed is wrapped between and tags.

Also note the use of the tag with the 'name' attribute set to
header (name=“header”). The tag when used with a datatable,
with the name set as header, will render a heading to the column ()
containing it. 

Within this the value of each field retrieved from the database is displayed as:

.

Here 'varname' is the name specified by the 'var' attribute of the datatable
tag-'rs' in this case. The name of the column is the actual name of the column
as retrieved by the SELECT query in the JavaBean. 

The JavaBean

Below is a simple JavaBean named DatabaseBean that retrieves data from the
Info table. Note that we have used MySQL JDBC drivers which are bundled with a
Tomcat installation on PCQLinux 2005. 

import java.sql.*;

import javax.servlet.jsp.jstl.sql.*;

public class DatabaseBean{



public Result getTable(){

try{

Class.forName("org.gjt.mm.mysql.Driver");

String connectionStr = "jdbc:mysql://localhost/contact";

Connection conn =
DriverManager.getConnection(connectionStr,"root","delta");

Statement stmt = conn.createStatement(); 

ResultSet rs = stmt.executeQuery("select * from info"); 

return ResultSupport.toResult(rs);

}

catch(Exception e){

return null; 

}

}

}

The noteworthy statement above is:

return ResultSupport.toResult(rs);

This statement returns a JSTL (Java Standard Tag Library) 

Result object suited for a datatable. There are several ways to pass the
database result to datatable, which include arrays, collection, scroll
insensitive Resultset and as the JSTL Result object. 

We don't need to delve deeper into these. The only tip to remember is using
the JSTL Result, which is the easiest amongst the other options. 

For more on the Result object, go to http://www.nakov.com /inetjava/lectures/part-3-webapps/InetJava-3.11-JSTL.html.
Next, save the above lines of code in a file named DatabaseBean.java and compile
it as:

javac -classpath /opt/tomcat/webapps/myfaces/WEB-INF/lib/jstl.jar
DatabaseBean.java

Place the resulting DatabaseBean.class file in the directory /opt/tomcat/webapps/myfaces/WEB-INF/lib/classes

Declare the bean 

Next we must declare the JavaBean in the file named facesconfig.xml in the
directory myfaces/WEB-INF. Append the 

following lines between the and tag
pair. 

dbBean

DatabaseBean

session

All is done by now. To see the DataTable at work, start/restart Tomcat as:

/opt/tomcat/bin/catalina.sh stop

/opt/tomcat/bin/catalina.sh start

Key in the following URL in your Web browser (Mozilla or Konqueror):
http://127.0.0.1:8080/myfaces/datatable.jsf. You will be able to see a simple
table displaying the retrieved rows from the database. 

Besides the display, the table is quite non-functional. You cannot sort or
edit the data. Next month we will see how to achieve that with the DataTable.

Shekhar Govindarajan, IT4Enterprise