One Step At A Time - Java

Using JSP to extract data from a database involves several steps. Let's dissect each one.

1. First, we need to make sure that all the modules required for a JDBC connection are available to the JSP document. This is accomplished by means of the

<%@ page
...
%>

directive, used to define attributes that affect the JSP document.

<%@ page language="java" import="java.sql.*" %>

The "import" attribute is used to import all the packages and classes required for the script to execute - here, all the packages in the "java.sql.*" tree.

2. Next, it's necessary to declare all the variables required for this scriptlet; we've kept aside some for the results of the SQL query, and also created variables to hold database-specific information, such as the name of the database server, the username and password required to gain access, and the database to use for all queries. This information is used to build a connection string, at a later stage.

3. The next step is to load the JDBC driver required to access a mySQL database - this is accomplished with the statement

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

The name of the driver to be used for a specific database can always be obtained from the documentation you receive with the driver.

4. Now that the drivers have been loaded, it's time to open a connection to the database server. This is accomplished by means of the Connection object and its getConnection() method.

The getConnection() method requires a connection string as argument; this connection string is created by combining the server name, the username and password, and the name of the database to use into a single URL-like string.

// create connection string
conn = "jdbc:mysql://" + host + "/" + db + "?user=" + user + "&password=" +
pass;
// pass database parameters to JDBC driver
Connection Conn = DriverManager.getConnection(conn);

The getConnect() method then returns a connection identifier, which is used for subsequent SQL queries. All communication between JSP and the database server takes place through this connection. In this case, the specific instance of the Connection object is called "Conn".

5. Once a connection to the database is available, the Statement object is used to prepare a SQL statement for execution.

// query statement
Statement SQLStatement = Conn.createStatement();

6. At this point, a query is created

// generate query
String Query = "SELECT uid, fname, lname FROM abook";

and the ResultSet object is used to store the results of the query.

// get result
ResultSet SQLResult = SQLStatement.executeQuery(Query);

7. Once the query has been executed and the results returned, a number of methods can be used to iterate through the result set. The example above uses the next() method, which simply moves forward through the list of records returned by the query. A "while" loop is used to iterate through the result set in combination with the next() method.

// get and display each record
while(SQLResult.next())
{
UId = SQLResult.getString("uid");
FName = SQLResult.getString("fname");
LName = SQLResult.getString("lname");
out.println("<tr><td>" + UId + "</td><td>" + 
FName + "</td><td>" + LName
+ "</td></tr>");
}

Incidentally, the ResultSet object also comes with a handy prev() method, which allows you to display the preceding record.

The getString() method is used to access specific columns in the record currently being examined; these values are stored as strings in the JSP document. In addition to the getString() method, you can also use the getInt(), getTimeStamp() and getBoolean() methods to obtain column values as specific variable types.

8. Finally, each result set returned after a query occupies some amount of memory - and if your system is likely to experience heavy load, it's a good idea to use the various close() methods to free up memory.

// close connection
SQLResult.close();
SQLStatement.close();
Conn.close();

As you can see, connecting to a database through JSP is a little more complicated than the equivalent procedure in PHP. There's not much you can do about this but grin and bear it.{mospagebreak title=What's Your Name?} Now that you know how to connect to a database, let's begin developing the bare bones of the address book application. This first script asks for a user name and then connects to the database to display entries owned by that user.

We'll be using a single page for the entire operation - the "submit" variable (you remember this technique, don't you?) is used to decide whether to display the initial form or the result page. Take a look:

<html>
<head>
<basefont face="Arial">
</head>
<body>
<center>
<%
// check submit state
String submit = request.getParameter("submit");
// form not yet submitted
// display initial page
if(submit == null)
{
%>
<form action="view.jsp" method="GET">
Enter your name:<input type="text" name="name" size="10">
<input type="submit" name="submit" value="Go">
</form>
<%
}
// form submitted, display result
else
{
%>
<%@ page language="java" import="java.sql.*" %>
<%
// get username
String uid = request.getParameter("name");
// define database parameters
String host="localhost";
String user="us867";
String pass="jsf84d";
String db="db876";
String conn;
%>
<h2><% out.println(uid); %>'s Little Black Book</h2>
<hr>
<table border=1 cellspacing=4 cellpadding=4>
<tr>
<td><b>First name</b></td>
<td><b>Last name</b></td>
<td><b>Tel</b></td>
<td><b>Fax</b></td>
<td><b>Email address</b></td>
</tr>
<%
Class.forName("org.gjt.mm.mysql.Driver");
// create connection string
conn = "jdbc:mysql://" + host + "/" + db + "?user=" + user 
+ "&password=" + pass;
// pass database parameters to JDBC driver
Connection Conn = DriverManager.getConnection(conn);
// query statement
Statement SQLStatement = Conn.createStatement();
// generate query
String Query = "SELECT * FROM abook WHERE uid = '" + uid + "'";
// get result
ResultSet SQLResult = SQLStatement.executeQuery(Query);
// display records
// if available
while(SQLResult.next())
{
String FName = SQLResult.getString("fname");
String LName = SQLResult.getString("lname");
String Tel = SQLResult.getString("tel");
String Fax = SQLResult.getString("fax");
String Email = SQLResult.getString("email");
out.println("<tr><td>" + FName + "</td><td>" + 
LName + "</td><td>" + Tel
+ "</td><td>" + Fax + "</td><td>" + 
Email + "</td></tr>");
}
// close connections
SQLResult.close();
SQLStatement.close();
Conn.close();
}
%>
</table>
</center>
</body>
</html>

As you can see, by checking the value of the "submit" variable, we've successfully combined both the initial page and the results page into a single JSP script. This script simply accepts a user name, connects to the database, and displays records for that user (assuming any exist). Log in as "bill", "joe" or "john" to view the records available for that user.

If you don't like the word "null" being displayed in columns which have no data, you can add a few "if" loops to replace it with an empty space.

<%
if (Fax.equals("null"))
{
Fax = "";
}
%>

>>> More Java & J2EE Articles          >>> More By Vikram Vaswani and Harish Kamath, (c) Melonfire

blog comments powered by Disqus