Java Tutorial - Java Scipt :
Testing the Connection
The other test that is often needed is a test of the data source to make sure that it is working as expected. The following servlet is provided as a simple example of how this can be done. It hard-codes the data source lookup information into the servlet to make the example as simple as possible for explanatory and ease of understanding purposes. In practice, the data source should be extracted form the URL used to invoke the servlet.
import java.io.*;
import java.net.*;
import java.sql.*;
import java.text.DateFormat;
import java.util.Date;
import javax.naming.*;
import javax.servlet.*;
import javax.servlet.http.*;
import javax.sql.DataSource;
public class SnoopJdbc extends HttpServlet
{
private String today()
{
Date now = new Date();
DateFormat dateFormatter = DateFormat.getDateTimeInstance();
return dateFormatter.format( now );
}
public void doGet(
HttpServletRequest request,
HttpServletResponse response)
throws ServletException, IOException
{
Context initCtx = null;
DataSource ds = null;
Connection conn = null;
DatabaseMetaData meta = null;
response.setContentType(“text/plain”);
PrintWriter out = response.getWriter();
out.println( “Today ==>”+today() );
try
{
initCtx = new javax.naming.InitialContext();
ds = (DataSource)initCtx.lookup(“java:comp/env/jdbc/hsqltest”);
conn = ds.getConnection();
meta = conn.getMetaData();
out.println( “Database ==>”+meta.getDatabaseProductName() );
out.println( “DriverName ==>”+meta.getDriverName());
out.println( “DriverVersion ==>”+meta.getDriverVersion());
out.println( “==== Tables ====” );
ResultSet rs = meta.getTables(null, null, “%”, null );
while( rs.next())
{
out.print( rs.getString(“TABLE_TYPE”));
out.print(“:”);
out.println(rs.getString(“TABLE_NAME”));
}
out.println( “================” );
conn.close();
conn = null;
}
catch( Exception e)
{
e.printStackTrace(out);
}
finally
{
out.close();
try
{
if(conn != null)
conn.close();
}catch(SQLException sqle)
{
throw new ServletException( sqle );
}
}
}
}
This servlet is very similar to the standalone Java program that was shown earlier in the Install the MySQL JDBC Driver section. We used this program to test the data source parameters. This new program lists the some of the same database metadata information as that earlier program but adds additional information. The primary differences here are that the program is implemented as a servlet and the connection is retrieved from a data source that was provided via JNDI lookup as shown in the following line of code.
ds = (DataSource)initCtx.lookup(“java:comp/env/jdbc/hsqltest”);
Unlike the earlier program, this one also lists the tables contained within the database. This is done by using the getTables method of the DatabaseMeta- Data object as follows:
ResultSet rs = meta.getTables(null, null, “%”, null );
The getTables method returns a ResultSet, just as a query against the database would do. This ResultSet can be looped through to display all of the table information as follows:
while( rs.next())
{
out.print( rs.getString(“TABLE_TYPE”));
out.print(“:”);
out.println(rs.getString(“TABLE_NAME”));
}
In this case, we are primarily interested in the table type and the table names. The table type tells us if this is a system table, a view, or an ordinary table. This servlet can be deployed with the following fragments added to the
web.xml.
<servlet>
<servlet-name>SnoopJdbc</servlet-name>
<servlet-class>SnoopJdbc</servlet-class>
</servlet
<servlet-mapping>
<servlet-name>SnoopJdbc</servlet-name>
<url-pattern>/servlet/SnoopJdbc</url-pattern>
</servlet-mapping>
When deployed with a Web application named snoopy, the URL for accessing the servlet is:
The result should look something like the following:
Today ==>Feb 17, 2003 10:34:07 PM
Database ==>HSQL Database Engine
DriverName ==>HSQL Database Engine Driver
DriverVersion ==>1.7.1
URL ==>jdbc:hsqldb:c:/openjava/hsqldb/data/hsqltest
==== Tables ====
TABLE:FOO
TABLE:BAR
TABLE:CUSTOMER
TABLE:PRODUCT
TABLE:INVOICE
TABLE:ITEM
================
