The JSP

This JSP script is about as simple as they come. The <jsp:usebean . . . /> instantiates the bean, and the scriptlet directs that bean to do all the work. As you will see, the method runSql( ) handles the database connectivity, builds and executes the SQL, and presents all of the output in a single string variable. In this example, the bean builds the HTML for presentation. It might be better if we moved the HTML generation code to its own JavaBean method or to a JSP Tag Library. For now, however, we'll do it all within the same JavaBean method to make this example as simple as possible. First, lets look at the JSP, and then we'll build the bean.
The JSP 

<html>
<head><title>JSP The Short Course - Lesson 13 </title></head>
<body>
	
  <jsp:useBean id="myBean" scope="page" class="rayexamples.Lesson13" />
  <% out.print ( myBean.runSql() ) ; %>

</body>
</html>


Build a Basic Bean

Nothing fancy here, just a basic bean. We won't even have getter and setter exposed methods. We don't need them, but we do have one exposed method called runSql (). That's where all the work is done. (The complete listing of the JSP and JavaBean are available at the end of the lesson.) Let's start writing our JavaBean.
Build a Basic Bean 
package rayexamples;
import java.sql.* ;
import java.io.Serializable;

public class Lesson13 implements java.io.Serializable 
{ 
  public Lesson13() {}


Add Database Connectivity

In order to connect to a database, we need to use Java-compliant connectivity software, and our choices are to use either the JDBC/ODBC bridge, or the more straightforward JDBC. We're going to use JDBC because it's a proven technology, easier to use, and free of the bugs that continue to plague JDBC/ODBC.

Do you have JDBC (Java Database Connectivity) installed on your computer? Probably, because it is loaded along with the JDK. However, in addition to JDBC you need a JDBC driver to connect to your database. Your database should come with a JDBC driver. If it doesn't contact the database manufacturer to learn more about where you can download a JDBC driver. Sun also maintains a list of available JDBC drivers.

Important Note: When you get your JDBC driver it will have to be installed onto your system. Follow the instructions that come with the JDBC driver. In addition, you will have to install the JDBC driver so that your JSP container will have access to the JDBC driver's class files. In the case of Tomcat, this can be accomplished by copying your JDBC driver's JAR files to the Tomcat Lib directory. Once this is done you will have to restart Tomcat.

Let's take a look at the connection process. We can't assume in this tutorial which database management system you will be using (Oracle, DB2, Sybase, MySQL, etc.), so you'll have to follow your vendor's instructions for creating the the tables for this tutorial. All of our examples use the Adaptive Server Anywhere from Sybase, and we will display the table formats as they appear in the Sybase product so you can create your own version locally. This lesson deals with only one table, the Employee table, which has no key references to address.

emp_id            integer
manager_id        integer
emp_fname         char(20)
emp_lname         char(20)
dept_id           integer
street            char(40)
city              char(20)
state             char(4)
zip_code          char(9)
phone             char(10)
status            char(1)
ss_number         char(11)
salary            numeric(20,3)
start_date        date
termination_date  date
birth_date        date
bene_health_ins   char(1)
bene_life_ins     char(1)
bene_day_care     char(1)
sex               char(1)

Once you've established your Employee table and populated it with some data, you need to do two things: Tell your Java code where the driver is so it can get loaded into memory and then connect to the database.

Class.forName("com.sybase.jdbc2.jdbc.SybDriver") ; The parameter for this strange-looking string will be provided either by your database administrator or found in your database installation documentation. This single statement is all it takes to load a driver to memory.

To connect to the database using the driver, you need the following two lines.

String localDatabase = "jdbc:sybase:Tds:127.0.0.1:2638" ;

jdbc:establishes that we are using the JDBC driver.
sybase:Tds:is the name of the jdbc driver for my database, and it was provided by the Sybase documentation.
127.0.0.1:is the address of your database server, and is dictated by your database installation. This database (Sybase Adaptive Server) exists on my computer, and 127.0.0.1 is its address.
2638is the port where your database listens for data requests. In this case, port 2638 is the standard port that is used by Sybase Adaptive Server database. You can find this port number from the documentation of your database or from your database administrator.

sqlca = DriverManager.getConnection( localDatabase, "dba","sql" ) ; makes the connection. The DriverManager class has many methods available, but the only one you'll need is getConnection(). The first argument in the getConnection() method is the String localDatabase described above.

The last two arguments in the getConnection() method are the user-id and password. The defaults used in the SQL Anywhere database (and most of the other databases) are "dba" and "sql", but these are changed once the database is installed and running. Your installation may take the issue of security a bit more seriously than I have with my single-machine environment, so you'll will need to use your own values here.

To summarize, here's the connectivity code for the JavaBean.

Connectivity Code 
  public String runSql () 
  {
    String		browserOutput  = "";
    Connection	sqlca  = null;
    Statement	sqlStatement  = null;
    ResultSet	myResultSet  = null;
          
    /* Connect to database, run SQL and produce output */
    try 
    {  
      /* Connection to the database */
      Class.forName("com.sybase.jdbc2.jdbc.SybDriver");
      String theDatabase = "jdbc:sybase:Tds:127.0.0.1:2638";
      sqlca = DriverManager.getConnection(theDatabase, "dba","sql");


Set Up and Execute the SQL

JDBC requires you place your SQL statement within a statement object. You retrieve the results of executing your SQL statement into a ResultSet.

The executeQuery() method is used for all SQL queries (select statements). For insert, update and delete statements, you will use the executeUpdate() method.

Here's the SQL and Execution code for the JavaBean.

SQL & Execution Code 
      /* Construct and execute the sql, positioning before the first row in the result 
	  set.  Note that the row selection is limited to those whose emp_id is less than 250.  
	  This is to keep the result set small for display purposes. */
      	sqlStatement   = sqlca.createStatement();
      	myResultSet  =  sqlStatement.executeQuery
        ("select emp_id, " +
        		"emp_lname, " +
        		"city, " +
        		"state," +
        		"zip_code, " +
        		"emp_fname " +
        		"from employee " +
        		"where emp_id < 250  " +
        		"order by emp_lname, emp_fname");


Set Up the Output

These three lines establish the HTML to set up a table for our output.

The first line establishes the table, sets the border to zero thickness (no lines show), centers the table on the user's screen, and sets the width to 75% of the width of the user's browser window.

The second line places a caption above the table. This caption is italicized and bold.

The third line adds the column headings from left to right. Each column heading is a <th> column heading </th> group, but note that the first contains align=left. This causes the current and all subsequent column headings to be left-justified. You can change this when you need a column centered or right-justified, but remember to set it back for following columns.

Establish a Table to Display the Dynamic Output 
      /* Construct the heading for the table */
      browserOutput = 
        "<table border=0 align=center width=75%>" +
        "<caption><i><b>" +
        "Employee Listing</b></i></caption>" ;

      /* Construct the column headings for the table */
      browserOutput += "<th align=left>Emp_id</th>" + 
        "<th>First Name</th>" + 
        "<th>Last Name</th>" +
        "<th>City</th>" + 
        "<th>State</th>" + 
        "<th>Zip</th>" ;


Loop, Reading Until You're Finished

This code loops through the result set, getting each column for the current row, making sure it's a String, and putting it into the presentation table. Please note:

Using the "while" loop with the next( ) method moves you through the result set until you reach the end. It then falls out of the loop and you can close the statement. That's all there is to it.

Create the HTML Output 
      /* Move to next row and & its contents to the html output */ 
      while(myResultSet.next())
      {
        browserOutput += "&ltTR>&ltTD>" + 
          myResultSet.getObject("emp_id").toString() + "</TD>&ltTD>" + 
          myResultSet.getObject("emp_fname").toString()+"</TD>&ltTD>" +
          myResultSet.getObject("emp_lname").toString() + "</TD>&ltTD>" +
          myResultSet.getObject("city").toString() + "</TD>&ltTD>" +
          myResultSet.getObject("state").toString() + "</TD>&ltTD>" +    
          myResultSet.getObject("zip_code").toString() + "</TD></TR>" ;
      }
      sqlStatement.close();
      }


Handle Exceptions

JDBC exception handling is really straightforward, and the following code is really just boiler plate that will take care of everything.

Handle Exceptions 
      catch (SQLException e)
      {
        browserOutput = " Error: SQL error of: " + e.getMessage(); 
      }
      catch (Exception e)
      { 
        browserOutput = " Error: JDBC Class Creation: " + e.getMessage(); 
      }
      finally
      {
      try
      {
        sqlca.close();
      }
      catch(SQLException e)
      {
        browserOutput = " Error: Closing connection: " + e.getMessage(); 
      }
    }


Return the Output to the Browser

Recall that we are developing one long string of HTML to present to the browser, so it's logical that we need to wrap up the HTML in an orderly manner. That's what we're doing here by adding the </table> tag. After that, the string is complete and we have only to return it to the JSP.

Return the Output 
    /* Complete the html and return it to the Java Server Page */
    browserOutput += "</table>" ;
    return browserOutput;
  } 
}

That's it! You've got all the information you need to add dynamically-generated content from a database. Now you can take a look at the Lesson13 JavaBean in its final form.

The Bean

The JavaBean 
package rayexamples;
import java.sql.* ;
import java.io.Serializable;

public class Lesson13 implements java.io.Serializable 
{ 
  public Lesson13() {}

  public String runSql () 
  {
    String      browserOutput  = "";
    Connection    sqlca  = null;
    Statement    sqlStatement  = null;
    ResultSet    myResultSet  = null;
          
    /* Connect to database, run SQL and produce output */
    try 
    {  
      /* Connection to the database */
      Class.forName("com.sybase.jdbc2.jdbc.SybDriver");
      String theDatabase = "jdbc:sybase:Tds:127.0.0.1:2638";
      sqlca = DriverManager.getConnection(theDatabase, "dba","sql");

      /* Construct and execute the sql, positioning before the 
        first row in the result set */
      sqlStatement   = sqlca.createStatement();
      myResultSet  =  sqlStatement.executeQuery
        ("select emp_id, " +
        "emp_lname, " +
        "city, " +
        "state," +
        "zip_code, " +
        "emp_fname " +
        "from employee " +
        "where emp_id < 250  " +
        "order by emp_lname, emp_fname");

      /* Construct the heading for the table */
      browserOutput = 
        "&lttable border=0 align=center width=75%>" +
        "&ltcaption>&lti>&ltb>" +
        "Employee Listing</b></i></caption>" ;

      /* Construct the column headings for the table */
      browserOutput += "&ltth align=left&gtEmp_id</th>" + 
        "&ltth&gtFirst Name</th>" + 
        "&ltth&gtLast Name</th>" +
        "&ltth&gtCity</th>" + 
        "&ltth&gtState</th>" + 
        "&ltth&gtZip</th>" ;

      /* Move to next row and & its contents to the html output */ 
      while(myResultSet.next())
      {
        browserOutput += "&ltTR>&ltTD>" + 
          myResultSet.getObject("emp_id").toString() + "</TD>&ltTD>" + 
          myResultSet.getObject("emp_fname").toString()+"</TD>&ltTD>" +
          myResultSet.getObject("emp_lname").toString() + "</TD>&ltTD>" +
          myResultSet.getObject("city").toString() + "</TD>&ltTD>" +
          myResultSet.getObject("state").toString() + "</TD>&ltTD>" +    
          myResultSet.getObject("zip_code").toString() + "</TD></TR>" ;
      }
      sqlStatement.close();
      }
      catch (SQLException e)
      {
        browserOutput = " Error: SQL error of: " + e.getMessage(); 
      }
      catch (Exception e)
      { 
        browserOutput = " Error: JDBC Class creation: " + e.getMessage(); 
      }
      finally
      {
      try
      {
        sqlca.close();
      }
      catch(SQLException e)
      {
        browserOutput = " Error: Closing connection: " + e.getMessage(); 
      }
    }
    /* Complete the html and return it to the Java Server Page */
    browserOutput += "</table>" ;
    return browserOutput;
  } 
}


The Output

How the Output Looks
Employee Listing
Emp_idFirst NameLast NameCityStateZip
191Jeannette Bertrand Acton MA 01720
160Robert Breault Milton MA 02186
129Philip Chin Atlanta GA 30339
105Matthew Cobb Waltham MA 02154
195Marc Dill Milton MA 02186
247Kurt Driscoll Waltham MA 02154
184Melissa Espinoza Stow MA 01775
207Jane Francis Concord MA 01742
249Rodrigo Guevara Framingham MA 01701
148Julie Jordan Winchester MA 01890
243Natasha Shishov Waltham MA 02154
102Fran Whitney Needham MA 02192
What Have We Accomplished?

Plenty! We've taken the biggest step of all by joining JSP, JavaBeans and a database to display dynamic content.


Mission Accomplished . . . What's Next?