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