The JSP Files - Parts 1 to 8: Tagged and Bagged

One Step At A Time

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.
Copyright Melonfire, 2000. All rights reserved.