Build Web applications with Eclipse, WTP, and Derby

Building dynamic Web applications is easy using Eclipse, the Web Tools Platform (WTP) for Eclipse, Derby, and Apache Tomcat. Learn how to install and configure all of the required Open Source components, and build a complete Web application using JSPs and servlets to store and retrieve information from a Derby database. This article has been updated to reflect the release of WTP 1.0.

Share:

Susan L. Cline (clines@us.ibm.com), Cloudscape Engineer, IBM, Software Group

Susan Cline works in Cloudscape to develop technical content for Cloudscape developers and users, with an emphasis on the integration of Eclipse and Derby tooling.



09 March 2006 (First published 01 September 2005)

Introduction

Eclipse is an ideal platform for developing Web applications using Java™ technologies. The 3-tier design for building dynamic Web applications is well suited for use with JSPs and Servlets running in a servlet container like Apache Tomcat. The persistent data layer is aptly provided by the Derby database. The Eclipse Web Tools Platform (WTP) project set of tools for developing J2EE™ and Web applications, along with the Derby Eclipse plug-ins, allows for rapid and simplified Web development.

This article discusses some of the functionality provided by the WTP, the Derby database plug-ins, and a complete sample application that uses JSPs, JavaServer Pages Standard Tag Library (JSTL), and Servlets. The sample application is a fictitious and simplified airline flight reservation system.

In order to get the most out of this article, you should understand the basics of JSP, JSTL, and Servlet technology, understand simple SQL, and have some knowledge of Eclipse. Some of the features of the WTP are used in this article, but it is not a comprehensive tutorial of the WTP tools. To learn more on these topics please refer to the list of resources at the end of this article. If you already know some of the background of the WTP and want to get started downloading all of the required software, skip to the section Software requirements. Otherwise, read the next section to learn what the WTP is and how some of these components are used from within Eclipse to develop the sample application.

IBM Cloudscape™ is the commercial release of the Apache Derby open source database. The names are used interchangeably in this article for general terms, unless reference to a specific file or name is used.


Eclipse WTP project

The Eclipse Web Tools Platform (WTP) project allows Eclipse users to develop J2EE Web applications. Included in this platform are multiple editors, graphical editors, natures, builders, a Web Service wizard, database access and query tools, and other components. The project provides a large number of tools. Only a limited number of these tools will be demonstrated in relation to building a Web application using Derby as the back-end database.

The WTP's charter, as defined at www.eclipse.org/webtools, is: "... to build useful tools and a generic, extensible, standards-based tool platform upon which software providers can create specialized, differentiated offerings for producing Web-enabled applications." This article will not discuss building new tools for this platform, but instead as an open platform to build Web applications using Open Source components.

Web standard tools and J2EE standard tools

The WTP is divided into two sub-projects, the Web Standard Tools and the J2EE Standard Tools. The Web Standard Tools (WST) project provides common infrastructure that targets multi-tier Web applications. It provides a server view that allows you to publish resources created from within Eclipse and run them on a server. The WST does not include specific tools for the Java language, or for Web-framework-specific technology.

The J2EE Standard Tools (JST) project provides tools that simplify development for the J2EE APIs, including EJB, Servlet, JSP, JDBC™, Web Services, and many more. The J2EE Standard Tools Project builds on the support for the Server Tools provided by the Web Standard Tools Project, including servlet and EJB containers.

The next section discusses all the software components you need in order to build and run the sample application.


Components of the Web application

The sample application uses the following software components and technologies:

  • Eclipse
    • Use the IDE to write and run the sample application. It is the foundation for developing and building Java applications.
    • Use the Java Development Tools (JDT) included with Eclipse to compile the Java classes that are part of the application.
  • WTP
    • Use the editor to create the JSP files. The editor includes content assist for JSP syntax.
    • Use the Servers view to start and stop the external Tomcat servlet engine.
    • Use the J2EE perspective view to create the Dynamic Web application that assembles and configures the J2EE Web application, including the standard structure and deployment descriptor common to all J2EE Web applications.
    • Create a connection to a Derby database through the Database Explorer view.
  • Derby plug-ins
    • Add the Derby nature to the Dynamic Web project to include the JAR files in the project.
    • Start and stop the Derby network server during application development.
    • Test and run SQL queries using the ij SQL query tool.
    • Set the project's derby.system.home property to point to the database.
  • JavaServer Pages Standard Tag Library (JSTL)
    • This tag library enables JSP-based applications to use a standard tag library to perform common tasks. The sample application uses these tags to perform tasks like iteration and database access. Expression Language (EL), a scripting language, is also used in the JSPs.
  • Apache Tomcat servlet engine
    • Runs the Web application consisting of the JSPs and Servlets.
    • Provides support for the Servlet 2.4 and JSP 2.0 APIs, including support for EL.
    • Provides support for defining the Derby database as a Data Source in the deployment descriptor of the Web application.

Software requirements

The software described in this section is available for download at no cost and must be installed prior to running the examples and building the sample Web application.

Either of the following Java development kits (Tomcat 5.5 requires at least 1.5):

  • IBM SDK version 1.5.x or higher.
  • Sun JDK version 1.5.x or higher.

Eclipse and WTP. You can download one zip that contains the Eclipse SDK, all of the WTP prerequisites, and WTP itself. On Windows®, this file is called wtp-all-in-one-sdk-1.0-win32.zip. If you prefer to download the Linux® distribution, grab the file wtp-all-in-one-sdk-1.0-linux-gtk.tar.gz. You can download either of these files from eclipse.org (see Resources).

In case you already have Eclipse installed, or some of the prerequisites for WTP, the versions of the plug-ins contained in the wtp-all-in-one zip file are listed below for you to compare. Also, the Download page lists these prerequisites for 1.0 WTP, so you need to make sure your components are at least as current as those listed here. If the versions available for download are different from those listed below, get the version recommended at the WTP site.

  • Eclipse 3.1.1, for Windows: eclipse-SDK-3.1.1-win32.zip
  • EMF SDK: emf-sdo-xsd-SDK-2.1.1.zip
  • GEF SDK: GEF-SDK-3.1.1.zip
  • Java EMF Model Runtime: JEM-SDK-1.1.0.1.zip
  • Web Tools Platform: wtp-1.0.zip

Derby database Eclipse plug-ins (available from apache.org as zip files -- see Resources). The Apache Derby database recently released version 10.1 of the database engine. The following versions of the plug-ins are required to run on Eclipse 3.1:

  • Derby Core plug-in, Version 10.1.1 or higher (10.1.2 is recommended)
  • Derby UI plug-in, Version 1.1.0

Apache Tomcat. Download Version 5.5.15 (see Resources).

JavaServer Pages Standard Tag Library (JSTL). Download the Standard 1.1 Taglib, jakarta-taglibs-standard-1.1.2.zip, from the Apache Jakarta Project (see Resources).

The sample application source code and WAR file:

  • A WAR file is a Web Application Archive and is the standard unit for packaging and deploying J2EE Web applications. All J2EE-compliant servlet containers accept WAR files and can deploy them. Download the LowFareAir.war file to your file system (see Downloads).
  • Download the zip file LowFareAirData.zip, which contains the Derby database and sample SQL files to access the airlinesDB database. (See Downloads.)

Software configuration

After downloading all required components, you need to configure them so you can start building the application.

Install a JDK

If you do not have a Version 1.5.x or higher JDK, install it. A JDK is required, not just a JRE.

Install Eclipse and the WTP

Install Eclipse by unzipping the wtp-all-in-one-sdk-1.0-win32.zip into a directory where you want Eclipse to reside. If Eclipse is already installed and you downloaded the individual components listed above, unzip those in the Eclipse home directory, since they are all plug-ins and they will install to the plugins directory of Eclipse.

Install and configure Jakarta Tomcat

Unzip or install Jakarta Tomcat to a different directory than your Eclipse installation directory.

Now you need to configure Eclipse to run Tomcat as a server from within Eclipse using the WTP. To do this:

  • Go to http://www.eclipse.org/webtools/ and select the tutorials link under the WTP Community section.
  • From the Tutorials page, select the tutorial called "Building a School Schedule Web Application."
  • Follow all the instructions in the section called "Installing the Tomcat Runtime in Eclipse" for this tutorial. However, select a Tomcat 5.5.15 installation instead of the 5.0.28 install like the instructions say. For the purposes of this sample Web application, you do not need to complete the whole tutorial.

Install the Derby plug-ins

Unzip both of the files (the Derby Core and UI plug-in zip files) to the plugins directory, from the Eclipse home directory. The Derby plug-ins come with a complete tutorial and examples of how to use all of their functionality. To access the help, select Help > Help Contents > Derby Plug-ins User Guide.


Application design

The LowFareAir Web application follows the standard 3-tier design model consisting of a presentation layer, business logic and control layer, and a data or persistence layer. The JSPs, including the JSTL tag libraries, provide the UI or presentation layer. The Servlets and supporting Java classes provide the business logic and control the flow of the application. The Derby database and JavaBeans provide the data layer. The diagram below illustrates this.

Figure 1. Sample application design
Application Design

A note about accessing the data layer from the presentation layer

The presentation layer, represented by the JSPs, should generally not interact directly with the data layer and hence should not be making database queries. The design of this application follows the accepted paradigm, with the exception of the first JSP. For quick prototyping efforts it is acceptable to combine the database access in the view layer, compromising the strict separation of data from view. The first JSP, Welcome.jsp, occupies both the presentation layer and the data layer by using the JSTL SQL library to issue an SQL query from the page.

The other JSPs act as the presentation layer only, and pass all data handling responsibility to the Servlets, which interact with the Derby database. The JSTL SQL library example is shown here, in case you are interested in using this methodology for future prototyping of Web applications, but it is not recommended for production environments.


LowFare Air sample application

The sample application allows new users the chance to register for or existing users to log into the application. Once the user is logged in, numerous flights are presented for booking. Only direct flights are offered, so the flight chosen is checked to see if the origin and destination direct flight is available. If the flight is available the user can choose to book the flight. Finally, the user can see a history of all flights booked through LowFare Air.

The flow of the sample application consists of these steps:

  • User registration or verification
    • The JSPs used in this part of the application are Welcome.jsp, Login.jsp, and Register.jsp
    • LoginServlet acts as the controller -- the user's name is either verified in the APP.USERS table in the Derby database, or inserted into the table.
    • A persistent cookie is set once a successful registration occurs, and the client's user ID is added to the session once a successful login occurs.
  • Flight retrieval and selection
    • Welcome.jsp is used to select the flight, and GetFlights.jsp is used to retrieve the flight.
    • CheckFlightsServlet acts as the controller. If there are flights between the two selected cities, the flight information is passed to GetFlights.jsp. If not, the user is returned to Welcome.jsp to select another flight.
    • If there are flights, the DerbyDatabase class places the flight information retrieved from the database into a JavaBean called FlightsBean.
  • Book the user's flight by updating the Flight History
    • The JSPs used are BookFlights.jsp and GoodBye.jsp. BookFlights.jsp asks the user for final confirmation on the flight they want to book. GoodBye.jsp displays all flights booked for the user with Derby Airlines.
    • UpdateHistoryServlet updates the APP.FlightHistory table with the users name and flight they just booked. The request is then forwarded to GoodBye.jsp.
  • Log out user
    • The final phase of the application is to either log out or to book another flight. The JSPs used are LoggedOut.jsp or, if the user wishes to book another flight, Welcome.jsp.
    • If the user chooses to log out the user ID is removed from the Session object. Therefore the next time the user returns to the site a persistent cookie remains, but the user ID is no longer in the Session object, and therefore the user must log in again.

The figure below shows this same flow pictorially.

Figure 2. Sample application flow
Application Flow

Application FlowApplication FlowApplication Flow


Creating the Web project from the WAR

To understand how to use the various tools included with the WTP and the Derby plug-ins, import the application as a WAR file, the standard packaging unit for Web applications, which is in a JAR file format.

The first step towards building any Web application that uses JSPs or Servlets is to create a Dynamic Web Application. You can create one using the WTP set of tools, and it will automatically create the correct directory structure for J2EE Web applications. Import the WAR file into the Dynamic Web Project folder of the Project Explorer view to create a new Web project.

Launch Eclipse, if it is not already running, and import the WAR file to create a new Dynamic Web Project by following these steps:

  1. Open the J2EE Perspective.
  2. From the Project Explorer view, right-click the Dynamic Web Projects folder.
  3. Select Import, then in the Import window, select WAR file and click Next.
  4. In the WAR Import window, browse to the LowFareAir.war file you downloaded earlier (see Software Requirements above). Name the project LowFareAir, and make sure the Target server is Apache Tomcat V5.5 (which you configured earlier -- see Software configuration above). Click Finish.

Figure 3 shows the last step in the process.

Figure 3. Importing a WAR file to create the Dynamic Web Project
Dynamic Web Project

You also need to import three JAR files that are not included in the WAR file: jstl.jar and standard.jar from the Jakarta taglibs package you downloaded earlier, and the derbyclient.jar file from the Derby core plug-in. Normally a complete WAR file would contain these JAR files, but for demonstration purposes you should know how to import them into the Dynamic Web Project.

To retrieve the JAR files from the Jakarta package, unzip the jakarta-taglibs-standard-1.1.2.zip file. The jstl.jar and standard.jar files are located in the newly created jakarta-taglibs-standard-1.1.2/lib directory. To import them:

  1. Open the Dynamic Web Projects folder. The LowFareAir project you just imported will appear. Expand this folder, and then the WebContent folder.
  2. Right-click the WebContent/WEB-INF/lib folder and select Import. In the Import window, select File System, and then click Next.
  3. Browse to the subdirectory jakarta-taglibs-standard-1.1.2/lib, where you unzipped the taglibs, and select jstl.jar and standard.jar. Make sure you are importing into the LowFareAir/WebContent/WEB-INF/lib directory. Then click Finish.

Now you need to add the derbyclient.jar file to the libraries available to the Web application. Your Web application will use the JDBC driver in derbyclient.jar to make connections to the database.

To import derbyclient.jar:

  1. Right-click the WebContent/WEB-INF/lib folder and select Import. In the Import window, select File System, then click Next.
  2. Browse to the plugins directory under the Eclipse home directory, and then to the directory org.apache.derby.core_10.1.2. Select derbyclient.jar. Make sure you are importing into the LowFareAir/WebContent/WEB-INF/lib directory. Then click Finish.

This completes importing the Web components, including the Java source files and all libraries for the application. Next, import the Derby database airlinesDB, complete with sample data.


Configuring the data layer

To configure the data layer and the tools that access the database for your application:

  1. Add the Apache Derby Nature to the LowFareAir project.
  2. Import the LowFareAirData.zip file into the project. The zip file contains the airlinesDB Derby database, which contains all of the data for the application as well as some sample SQL scripts.
  3. Configure the Web application deployment descriptor, web.xml, to contain a data source pointing to the airlinesDB database.
  4. Set the Derby property derby.system.home to point to the full path of the airlinesDB database. By setting this property, all references to the airlinesDB database in the JDBC connection URLs can just refer to 'airlinesDB' instead of the full file system path.

Adding the Apache Derby nature

The Web application uses a Derby database to store and query flight information for the fictional LowFareAir airlines. An easy way to access and use Derby databases in Eclipse is via the Derby plug-ins.

The Derby plug-ins allow you to add the Derby nature to any Eclipse project. Adding a nature to a project, including a Dynamic Web Project, means that project "inherits" certain functionality and behaviour. Adding the Derby nature adds the Derby database JAR files and the command line tools bundled with Derby to the Eclipse environment.

The Project Explorer view will now show the LowFareAir project you just created.

To add the Derby nature to the LowFareAir project, right-click it and select the menu item Apache Derby > Add Apache Derby nature.

Import LowFareAirData.zip

Included in the source code is airlinesDB, the sample database used for the Web application. This database, along with some sample SQL, needs to be imported into the LowFareAir project. To do this:

  1. Expand the Dynamic Web Projects folder. Right-click the LowFareAir folder and select Import. In the Import window, select Archive file, then click Next.
  2. Browse to LowFareAirData.zip, and make sure that in the left frame, the / directory is checked. This includes the data and sql folders. For the name of the Into folder select LowFareAir, then click Finish.
  3. If the import was successful, the LowFareAir folder should contain two new subfolders: data and sql. The data folder will contain the airlinesDB directory (database).
  4. The sql directory contains three SQL files called airlinesDB.sql, flights.sql, and flighthistory_users.sql.

Now all of the files required for the Web application have been imported, and the LowFareAir project should look similar in structure to Figure 4.

Figure 4. Project Explorer view of LowFareAir project
Project Explorer view of LowFareAir project

Configure web.xml with the Derby data source

The web.xml file contains a data source entry to use the Derby airlinesDB database as a data source. This is not a requirement for any Web application to connect to a Derby database; however, the application uses a data source for the first JSP page for demonstration purposes. The other JSPs do not use the data source, and the Servlets use a standard Java class that use JDBC to connect to the database.

To determine the location of the airlinesDB on the file system, right-click the airlinesDB folder under the data directory of the LowFareAir project and select Properties. The Properties window shows a Location field which has the full file system path to the airlinesDB directory. Copy this string so it can be used in the next step. For instance, this path might be something like: C:\eclipse\workspace\LowFareAir\data\airlinesDB

Open web.xml (under the WebContent/WEB-INF directory) and browse to this section of it while viewing in Source mode (the entry in the param-value section has been modified with a line break for readability, but the URL should be a continuous line):

Listing 1. Web.xml context-param section
<context-param>
 <param-name>javax.servlet.jsp.jstl.sql.dataSource</param-name>
   <param-value>
   jdbc:derby://localhost:1527/C:/eclipse/workspace/LowFareAir/data/ /
   airlinesDB;user=a;password=b;,
   org.apache.derby.jdbc.ClientDriver
 </param-value>
</context-param>

Change the value in the <param-value> section to the database URL for your environment, using the full path to the airlinesDB database you just copied and save the file. Without editing this correctly, the first page of the application (Welcome.jsp) will fail. Also, you need to start the network server before running Welcome.jsp, since the URL shown above attempts to access the Network Server using the Derby Client driver. Note: Either forward or backward slashes can be used for the database connection URL in a Windows environment.

Set derby.system.home for the project

The next step to configuring the Derby database environment from within Eclipse is to edit the Derby system property called derby.system.home to point to the location of the airlinesDB database. This allows you to connect to airlinesDB using the Derby plug-ins without specifying the full file system path to the database. Only the name airlinesDB needs to be listed in the database connection URL.

Use the path to the airlinesDB directory you copied earlier, just slightly modified, to set derby.system.home.

  1. Right-click the LowFareAir project and select Properties.
  2. In the left side of the Properties window for the LowFareAir project, select Apache Derby.
  3. On the right side are the Apache Derby properties that you can change. The Derby System Property called derby.system.home is currently set to the default value (.). Change it to point to the full path of the directory where the airlinesDB directory resides. Note: You can also modify the port that the network server listens on, in the port property.
    Edit the value for the derby.system.home property to the full path of your data directory. Paste in the string you copied above, and remove the trailing \airlinesDB. So given the example path from earlier, the derby.system.home property would be: C:\eclipse\workspace\LowFareAir\data. Note: Do not enter the name of the database directory itself -- it should be the directory where the database directory resides, in this case data, not the airlinesDB directory itself.
  4. Finally click OK to save the setting for the project.

Next you'll start the Derby Network Server, make a connection to the airlinesDB database and issue some SQL using the ij tool provided with the Derby plug-ins.

Starting the Derby network server and running ij

Since you are about to run some queries against the tables in the airlinesDB, it's useful to know which tables you have and how they are defined. These are shown below. The SQL file, airlinesDB.sql, was used to create the database. Do not run airlinesDB.sql again unless you delete the old database and want to recreate all of the tables in a new database.

Listing 2. Create table statements for the airlinesDB database
CREATE TABLE APP.CITIES
(
 CITY_ID          INTEGER NOT NULL constraint cities_pk primary key,
 CITY_NAME        VARCHAR(24) NOT NULL,
 COUNTRY          VARCHAR(26) NOT NULL,
 AIRPORT          VARCHAR(26),
 LANGUAGE         VARCHAR(16),
 COUNTRY_ISO_CODE CHAR(2) 
);

CREATE TABLE APP.FLIGHTS
(
 FLIGHT_ID      CHAR(6) NOT NULL,
 SEGMENT_NUMBER INTEGER NOT NULL,
 ORIG_AIRPORT   CHAR(3),
 DEPART_TIME    TIME,
 DEST_AIRPORT   CHAR(3),
 ARRIVE_TIME    TIME,
 MEAL           CHAR(1) CONSTRAINT MEAL_CONSTRAINT 
 CHECK (meal IN ('B', 'L', 'D', 'S')),
 FLYING_TIME    DOUBLE PRECISION,
 MILES          INTEGER,
 AIRCRAFT       VARCHAR(6),
 CONSTRAINT FLIGHTS_PK Primary Key (FLIGHT_ID, SEGMENT_NUMBER)
);

CREATE TABLE APP.FLIGHTAVAILABILITY
(
 FLIGHT_ID              CHAR(6) NOT NULL ,
 SEGMENT_NUMBER         INTEGER NOT NULL ,
 FLIGHT_DATE            DATE NOT NULL ,
 ECONOMY_SEATS_TAKEN    INTEGER DEFAULT 0,
 BUSINESS_SEATS_TAKEN   INTEGER DEFAULT 0,
 FIRSTCLASS_SEATS_TAKEN INTEGER DEFAULT 0,
 CONSTRAINT FLIGHTAVAIL_PK Primary Key 
 (FLIGHT_ID, SEGMENT_NUMBER, FLIGHT_DATE),
 CONSTRAINT FLIGHTS_FK2 Foreign Key (FLIGHT_ID, SEGMENT_NUMBER)
 REFERENCES FLIGHTS (FLIGHT_ID, SEGMENT_NUMBER)
);

CREATE TABLE APP.FLIGHTHISTORY
(
 ID             INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
 USERNAME       VARCHAR(26) NOT NULL,
 FLIGHT_ID      CHAR(6) NOT NULL,
 ORIG_AIRPORT   CHAR(3) NOT NULL,
 DEST_AIRPORT   CHAR(3) NOT NULL,
 BEGIN_DATE     CHAR(12),
 CLASS          CHAR(12)
);

CREATE TABLE APP.USERS 
(
 ID             INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY,
 USERNAME       VARCHAR(40) NOT NULL,
 PASSWORD       VARCHAR(20)
);

Now start the Derby network server by right-clicking the LowFareAir project and choosing Apache Derby > Start Derby Network Server. The console view should state the server is ready to accept connections on the port specified in the Derby properties Network Server settings. Open the sql folder and right-click the flights.sql file. Select Apache Derby > Run SQL Script using 'ij'.

The console window will show the output of the three SQL statements contained in the flights.sql file. If a connection was not made, check to make sure the network server has been started, and that derby.system.home has been set to the full path to the data directory under the LowFareAir folder.


The WTP data tools -- An alternative

The WTP has a rich set of database tools that allow users to connect to, browse, and issue SQL against Derby, DB2®, Informix®, MySql, Oracle, SQL Server, and Sybase databases. In this section you'll connect to the Derby Network Server using the Derby Network Client driver, and learn how to use some of these tools as an alternative to using the Derby plug-ins.

In the J2EE perspective, select Window > Show View > Other. In the Show View window, select Data > Database Explorer and then click OK. The Database Explorer view will appear in the bottom right part of the workspace. Right-click somewhere in this view, and select New Connection.

The wizard that appears is the New Connection wizard. Uncheck the Use default naming convention checkbox, and name the connection Derby10.1. Under the Select a database manager section, expand the Derby item in the tree and select the 10.1 version of the database system.

In release V10.1 of Derby, a new open source client driver -- derbyclient.jar -- is the recommended way to connect to the network server.

The image below shows the values for each field in my environment. The table following also lists the sample settings.

Figure 5. The New Connection wizard of the WTP Database Explorer
Creating a new connection
Table 1. Sample values for a Derby 10.1 connection using the Derby Client Driver
ParameterValue
Connection nameDerby10.1
Database managerDerby 10.1
JDBC driverDerby Client JDBC Driver
DatabaseC:\eclipse\workspace\LowFareAir\data\airlinesDB
CheckboxesUncheck both check boxes (Create the database and Upgrade)
Hostlocalhost
Port number1527
JDBC driver classorg.apache.derby.jdbc.ClientDriver
Class locationC:\eclipse\plugins\org.apache.derby.core_10.1.2\derbyclient.jar
Connection URLjdbc:derby://localhost:1527/C:\eclipse\workspace\LowFareAir\data\airlinesDB
User IDslc (any non-empty value)
Passwordslc (any non-empty value)

Configuring authentication for Derby databases is available; however, this has not been set up for the airlinesDB database. Use any (non-null) value for user ID and password, and click the Test Connection button. If the network server is running on port 1527, the test should be successful. If it is not, make sure the network server is running on the port specified in the connection URL, and check to make sure all of the values are appropriate for your environment.

Since the network server is used to connect to the airlinesDB database, multiple JVMs can access it. This means ij, the Database Explorer, and a client application external to Eclipse could all connect to and query the tables in the database.

Once the test connection is successful, click the Next button.

In the Specify Filter window, uncheck the Disable filter checkbox, select Selection and check the APP schema. Then click Finish.

Figure 6. Specifying the filter for the Derby 10.1 connection
Specifying the Filter

Now the Database Explorer view shows the connection to the airlinesDB database. Expand the tree and browse to the FLIGHTS table under the Tables folder for the APP schema. Right-click the FLIGHTS table, and select Data > Sample Contents.

Figure 7. Sample contents in the Database Explorer
Sampling the contents of the FLIGHTS table

The Data Output view will now appear with the rows in the FLIGHTS table.

Figure 8. The Data Output view
Data Output view of the FLIGHTS table

Another feature of the Database Explorer view is the ability to insert, delete, and update rows in tables. The Table Editor provides the ability to modify data in a table. If you want to add another row to the FLIGHTS table, right-click it in the Database Explorer view, and select Data > Edit. Enter a new row, putting values in each column as shown below. Notice on the FLIGHTS tab the asterisk that precedes the word FLIGHTS. This indicates the editor has been changed since the last time it was saved.

Figure 9. The Table Editor
The FLIGHTS table in the Table Editor

To insert the row into the table, save the editor by selecting File > Save or using the shortcut, Ctrl + S. The Data Output view's Messages tab shows the data was inserted successfully.

Figure 10. Successfully inserting a row into the FLIGHTS table
The Data Output view showing a successful insertion into the FLIGHTS table

Other features of the Database Explorer view include the ability to Extract and Load tables, open an SQL Editor to issue ad-hoc SQL, and a Generate DDL option that is very useful to generate SQL scripts that you can use to create entire database schemas or a subset of the schema. You can explore all of these options on your own by right-clicking on the schema object (a table, view, index, or schema) to see which options are available for that particular object.


Exploring the View Layer and JSPs

Now you can start looking at the JSPs in the application. Referring to the flow of the application in Figure 2, the first JSP page is Welcome.jsp. From the Project Explorer, open up the Welcome.jsp file under the WebContent folder. The code for this page is shown in sections below. Note: Some code listings may have a backslash character, "\", to indicate the line of code is continuous but has been formatted for readability.

Listing 3. Importing the Core and SQL taglibs in Welcome.jsp
<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
<%@taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%>

The first two lines include the taglib directives available to the JSP page, so that you can use the core and SQL JSTL tag libraries.

The next section makes use of the core tag library to test if any cookies are set in the user's browser. If there are none, the JSP page is forwarded to Register.jsp. If there is at least one cookie, then a check is made to see if the cookie with the name of derbyCookie is set. If the derbyCookie exists then the next test is to check if the Session object contains a user ID. If not, the user is forwarded to Login.jsp to log in to the application.

If the user does have a user ID in the Session object, the user is allowed to proceed and processing continues on the Welcome.jsp page.

Listing 4. Testing if cookies are set in Welcome.jsp
<HTML>
<HEAD>
<TITLE>Derby Airlines</TITLE>
</HEAD>
<BODY>

<c:choose>
 <c:when test="${empty cookie}">
  <jsp:forward page="Register.jsp" />
   </c:when>
 <c:otherwise>
<!--  if the derbyCookie has been set but the username is not in \
the session object -->
   <c:forEach var="cookieVal" items="${cookie}">
    <c:if test="${cookieVal.key == 'derbyCookie'}">
     <c:if test="${empty sessionScope.username}">
      <jsp:forward page="Login.jsp" />
     </c:if>
    </c:if>
   </c:forEach>
 </c:otherwise>
</c:choose>

The code below checks to see if the parameter called nodirectflights is set to true when this page is posted. If you look at the code further down, the action for the form is to post to the CheckFlightsServlet. If the CheckFlightsServlet does not have any direct flights based on the origin and destination the user selects, the parameter nodirectflights is set to true, the user is returned to this page, and this snippet of code will display the message about no direct flights being available.

Listing 5. Displaying a message if direct flights are unavailable in Welcome.jsp
<c:if test="${nodirectflights == 'true'}" >
<p>
There were no direct flights between <font color="blue" size="5">
${cityNameFrom}</font> and <font color="blue" size="5">
${cityNameTo}</font>. <br> 
Please select another flight.
</p>
</c:if>

You can use the JSTL core out tag to display the parameters passed to JSPs in either the session or request objects. After a successful login, the user ID is placed in the Session object, and displayed on the Welcome.jsp page when the user accesses this page. Also, notice the use of the jsp:include tag to include the CalendarServlet which displays the current month, day, and year, as well as a drop down box to select the departure date.

Listing 6. After a successful login
<H3>
Welcome to Derby Air, <c:out value="${username}" />!</H3>
<p>
Please proceed to select a flight.
</p>
<form action="CheckFlightsServlet" method="post">

<table width="50%">
<tr>
 <td valign="top">
  <b>Departure Date:</b><br>
  <jsp:include page="/CalendarServlet">
  	<jsp:param name="type" value="orig" />
  </jsp:include>
 </td>

Here is the section that makes use of the JSTL SQL library. Before using the sql:query tag the way it is shown below, you have already set up the DataSource in the web.xml file for the application. That entry will be discussed later. For now, notice how easy this tag is to use. The query is issued against the CITIES table to return the values contained in the city_name, country, and airport columns of the APP.CITIES table. The results of the query are put in a variable called cities, which is a javax.servlet.jsp.jstl.sql.Result object. The Result object has a method called getRows() that returns all rows contained in it. The rows are returned as an array of java.util.SortedMap objects.

Listing 7. Using the SQL taglib
<sql:query var="cities">
 SELECT CITY_NAME, COUNTRY, AIRPORT FROM APP.CITIES ORDER BY \
 CITY_NAME, COUNTRY
</sql:query>

Iteration over the rows contained in the cities Result object is shown below using the core forEach tag. For each iteration through the array, the variable named city contains a SortedMap object. The Expression Language allows you to access the value of each column in each row by referring to the column name in the specific SortedMap object representing that row in the database.

Listing 8. Outputting the result of the SQL query
<td>
 <b>Origin:</b><br>
 <select name="from" size="4">
 <c:forEach var="city" items="${cities.rows}">
 <option value="${city.airport}"> ${city.city_name}, ${city.country}\ 
 </option>
</c:forEach>
 </select>
 <br><br>
 </td>
</tr>

The rest of the page is not shown here. It outputs the Destination drop down box the same way the Origin destination is generated above, and then provides a button for the user to submit the query to check for the flights between the Origin and Destination.


Examining the flow of control and running the application

Before you can run LowFare Air, you need to start the Derby Network Server, if it is not already running. Right-click the LowFareAir folder, and select Apache Derby > Start Derby Network Server.

Now right-click the Welcome.jsp file in the Project Explorer view. Select Run As > Run On Server.

Figure 11. Running Welcome.jsp on the Tomcat server
Running Welcome.jsp

This brings up the Run on Server wizard. Proceed through the wizard as follows:

  1. For the Server's host name, select localhost. For the server type, expand the Apache folder and select Tomcat V5.5. For the server runtime, select Apache Tomcat V5.5.

    By default Tomcat version 5.5 or higher requires Java 5 (1.5) or higher. However, you can use Java 1.4 by following the instructions in RUNNING.txt included in the Tomcat distribution. In this example, I have a 1.5 version of the JDK installed. Tomcat will fail to start if you are not using a 1.5 version of Java, or you have not configured it to use 1.4.

    Next, check the Set server as project default checkbox. Click the Next button.
  2. In the Add and Remove Projects window, verify that the LowFareAir project is listed in the Configured Projects area. If it is not, and it appears in the Available Projects category, move it to the Configured Projects category. Click Finish.

This will start the external Tomcat server and launch a browser window to run the JSP on. For Windows, the default is to launch an internal browser from within Eclipse. On Linux it will default to the external browser.

To configure the launch of an external browser:

  1. Select Window > Preferences.
  2. Select the General tree item, then Web Browser.
  3. Select the Use external Web browser button, then choose from the browsers available in the list.
  4. Click OK to set the preference.

It is always a good idea to check your Web pages with different browsers. They do behave differently when rendering some HTML elements, as well as in their default handling of cookies.

As described above, the first time you launch Welcome.jsp, it will redirect you to the Register.jsp page. The derbyCookie has not been set, so Welcome.jsp redirects you to Register.jsp to create a user ID and password. See Figure 12.

Figure 12. Register.jsp with new user ID entry
Register.jsp

When you enter a user ID and password and click the Register New User button, the values are passed to the LoginServlet class. Open up this Java class now, located under the LowFareAir > Java Resources > JavaSource > com.ibm.sample folder and package structure.

The doPost method first parses the incoming parameters, including the user ID and password you just set in Register.jsp.

Listing 9. The doPost method of the LoginServlet class
protected void doPost(HttpServletRequest request,
 HttpServletResponse response) throws ServletException, IOException
{
 String user = request.getParameter("username");
 String password = request.getParameter("password");
 // Register.jsp set the parameter newuser
 String newUser = request.getParameter("newuser");
 String loggedOut = request.getParameter("loggedOut");

Then it connects to the Derby database by calling the getConnInstance() method on the DerbyDatabase class. The getConnInstance method returns a singleton java.sql.Connection to the database.

Listing 10. Setting the conn variable to the singleton Connection object of the DerbyDatabase class
Connection conn = DerbyDatabase.getConnInstance();

The next section of code determines if the user is new or not, and either selects the user's ID from the database if they already exist, or adds it to the database if they do not.

Listing 11. Processing new users in the LoginServlet class
// the user is not new, so look up the username and password
// in the APP.USERS table

if (newUser == null || newUser.equals(""))
{
 sql = "select * from APP.USERS where username = '" + user
        + "' and password = '" + password + "'";
 String[] loginResults = DerbyDatabase.runQuery(conn, sql);

 // if the query was successful one row should be returned
 if (loginResults.length == 1)
 {
  validUser = true;
 }
}
// the user is new, insert the username and password into 
// the APP.USERS table
else
{
 sql = "insert into APP.USERS (username, password) values " + "('"
 + user + "', '" + password + "')";
 int numRows = DerbyDatabase.executeUpdate(conn, sql);
 if (numRows == 1)
 {
  validUser = true;
 }
}

To verify that the user ID Susan was added to the APP.USERS table, bring up ij, the SQL tool from the Derby plug-in, to query the APP.USERS table.

To bring up ij, right-click the LowFareAir folder, then select Apache Derby > ij (Interactive SQL). Connect to the airlinesDB database by issuing this connect statement from ij and running the query select * from APP.USERS; to see if the user ID you entered into your browser appears. In this example, the user ID is Susan.

Note: Version 3.1 of Eclipse has different behaviour than previous versions in regards to where the cursor is in the console view. The cursor is always positioned at the beginning of the line. Although it looks a little odd, if you start typing at the ij prompt, the cursor will reposition to the correct location, right after the 'j' in ij.

Listing 12. Connecting to the airlinesDB database
connect 'jdbc:derby://localhost:1527/airlinesDB';
select * from APP.USERS;

The output from ij is shown below.

Figure 13. ij results from APP.USERS table
APP.USERS table results

Register.jsp passed those values correctly and LoginServlet.java inserted the user ID and password correctly into the table. The user ID and password of 'slc' and 'slc' were already in the table.

The rest of the code in LoginServlet.java deals with incorrect user IDs and passwords, and then forwards the results to Welcome.jsp if everything is correct. The next image shows Welcome.jsp with an Origin of Albuquerque and a Destination of Los Angeles. Note that the user ID was passed from LoginServlet.java to Welcome.jsp for display.

Figure 14. Welcome.jsp after successful login
Welcome.jsp after login

Welcome.jsp posts to CheckFlightsServlet.java. Open up CheckFlightsServlet.java. The notable thing about this servlet is that after parsing the incoming parameters, it calls the DerbyDatabase method origDestFlightList(). This method returns an array of FlightsBean objects.

Listing 13. The CheckFlightsServlet class
Connection conn = DerbyDatabase.getConnInstance();

FlightsBean[] fromToFlights = \
 DerbyDatabase.origDestFlightList(conn, from, to);

Once the FlightsBean array has been populated, CheckFlightsServlet places the results in the session with the variable name of fromToFlights.

Listing 14. Placing the array of FlightsBean into the session object
request.getSession().setAttribute("fromToFlights", fromToFlights);

Now open the DerbyDatabase.java class to see what this method does. Some of the code has been slightly reformatted for ease of viewing.

Listing 15. Examining the origDestFlightList method in the DerbyDatabase class
public static FlightsBean[] origDestFlightList(Connection conn, \
 String origAirport, String destAirport)
{
 String query = "select flight_id, segment_number, orig_airport, " +
 "depart_time, dest_airport, arrive_time, meal, flying_time, miles," +
 "aircraft from app.flights where ORIG_AIRPORT = ? AND " +
 "DEST_AIRPORT = ?";
 List list = Collections.synchronizedList(new ArrayList(10));
        
 try
 {            
  PreparedStatement prepStmt = conn.prepareStatement(query);
  prepStmt.setString(1, origAirport);
  prepStmt.setString(2, destAirport);
  ResultSet results = prepStmt.executeQuery();

  while(results.next())
  {
   String flightId = results.getString(1);
   String segmentNumber = results.getString(2);
   String startAirport = results.getString(3);
   String departTime = results.getString(4);
   String endAirport = results.getString(5);
   String arriveTime = results.getString(6);
   String meal = results.getString(7);
   String flyingTime = String.valueOf(results.getDouble(8));
   String miles = String.valueOf(results.getInt(9));
   String aircraft = results.getString(10);
                
   list.add(new FlightsBean(flightId, segmentNumber, startAirport,
   departTime, endAirport, arriveTime, meal, flyingTime, miles, aircraft));
  }
  results.close();
  prepStmt.close();
 }
 catch (SQLException sqlExcept)
 {
  sqlExcept.printStackTrace();
 }
        
 return (FlightsBean[])list.toArray(new FlightsBean[list.size()]);
}

The origDestFlightList method issues an SQL query using a PreparedStatement, and places the results in a FlightsBean[] array. One of the FlightsBean constructors is shown below.

Listing 16. One of the FlightsBean constructors
public FlightsBean(String flight_id, String segNumber, 
 String origAirport, String depart_time, String destAirport, 
 String arrive_time, String food, String flying_time, 
 String mile, String jet)
{
 flightId = flight_id;
 segmentNumber = segNumber;
 startAirport = origAirport;
 departTime = depart_time;
 endAirport = destAirport;
 arriveTime = arrive_time;
 meal = food;
 flyingTime = flying_time;
 miles = mile;
 aircraft = jet;
}

Once the origDestFlightList method populates the FlightsBean array, processing continues in the CheckFlightsServlet servlet and the results are forwarded to the GetFlights.jsp page.

In the next section, you'll use the JSP debugger feature of the WTP to look at the values returned in the FlightsBean when selecting a flight.

The next section will start the Tomcat server in debug mode, so go ahead and stop it now. To do this, select the Servers view tab in the lower right section of the workspace. Then right-click in the Tomcat server line and select Stop.

Figure 15. Stopping the Tomcat server from the WTP Servers view
Stopping Tomcat

Using the JSP debugger

Stepping back from the code a minute, here is a summary of what you have seen by exploring and configuring the sample application:

  • WTP
    • Used the Database Explorer view to configure a connection to a Derby 10.1 database using the Derby Client driver.
    • Sampled the contents of the FLIGHTS table from the Database Explorer view.
    • Added a row to the FLIGHTS table from the Database Explorer view, using the Data > Open menu option.
    • Started the Tomcat server using the Run As > Run On Server option from a JSP file.
    • Opened and examined a JSP in the JSP editor.
    • Stopped the Tomcat server using the Servers view.
  • Derby plug-in
    • Added the Apache Derby nature to a Dynamic Web Project.
    • Configured Derby system properties using the Project Properties menu.
    • Ran entire SQL scripts via ij.
    • Issued SQL commands via ij.
    • Started and stopped the Derby Network Server.

Now let's look at the JSP debugging capabilities of the WTP.

At this point there is at least one valid user in the Derby airlinesDB APP.USERS table, and you may have added another. Before you run through the application again, set a breakpoint in the GetFlights.jsp page first, and then start the Tomcat server in debug mode.

To set the breakpoint, open GetFlights.jsp, and right-click in the grey area to the left of the line of code which starts with <c:set var="myradiobutton". Select Toggle Breakpoints as shown below.

Figure 16. Setting a breakpoint in GetFlights.jsp
Setting a breakpoint

The breakpoint will appear as a blue dot in the grey area on the left. Now from the Project Explorer (making sure the Derby Network Server is still running), right-click Welcome.jsp and select Debug As > Debug On Server. The Tomcat server will now start up in Debug mode and bring up the Welcome.jsp page prompting for a user ID and password.

Enter the user ID and password you entered previously, or a user ID value of slc with a password of slc. If you did not delete the cookie already set, you will be allowed to select a flight from Welcome.jsp without having to log in again. Not all of the flights listed in the Origin to Destination are direct. Select an Origin of Albuquerque and a Destination of Los Angeles, since that flight is direct. Then finish by clicking Submit Query.

At this point, Eclipse should prompt you to switch to the Debug perspective. Confirm the perspective switch. When the debug perspective appears, the Variables view on the upper right of the workspace will appear. In the bottom left, the GetFlights.jsp editor view will also appear, showing where you set the breakpoint.

The Variables view may not be populated with values immediately. You might need to go to the Debug view and select the thread that has been suspended. Once you expand the suspended thread and select GetFlights.jsp in the Debug view as shown below, the Variables view should be populated.

In the Variables view, look for your core JSTL when tag. Expand the tree for the _jspx_th_c_when_0=WhenTag item. See Figure 17.

Figure 17. Examining variables from the JSP debug perspective
Examining variables

Notice the reference to the parent tag ChooseTag from within the WhenTag. Expand the parent ChooseTag, and contained within it is the parent ForEachTag. Expand that ForEachTag and finally expand the item variable which equals FlightsBean. If you have been successful in following this, your Variables view will look something like what is shown below.

Figure 18. FlightsBean values in Debug mode
FlightsBean values in Debug mode

This shows the values set in the FlightsBean object, which were selected with a departing airport of Albuquerque and a destination airport of Los Angeles. The JSP debugger can be extremely useful when troubleshooting Web applications, and in particular in examining variables as shown here.

Now click the Resume button on the top left menu item, which looks like a green arrow, to step past the breakpoint. The browser will display the output of the GetFlights.jsp page. In the browser, select the available flight (flight number AA1111), and click the Book Flight button.

The next page gives you one last chance to either book the flight or check for other flights. Go ahead and click the Book Flight button. The next page will appear similar to what is shown below.

Figure 19. Flight History
Flight History Browser view

When you clicked Book Flight on the previous screen, a row was inserted into the APP.FLIGHTHISTORY table. Since you have learned how to use both ij and the Database Explorer, you can verify that the row was actually inserted into the table.

At this point in the application, the user can either return to select a new flight, or log out of the application.


Summary

In setting up and configuring the WTP platform, you have used the external Tomcat server, debugged a JSP file, configured a connection to a 10.1 Derby database using the Database Explorer, and used the Database Explorer to browse a table and insert a row.

You've learned about the use of the Derby plug-ins, including adding the Apache Derby nature, starting and stopping the Network Server, using ij to run SQL scripts and issue ad-hoc commands, as well as setting the derby.system.home property.

You configured the web.xml file for the Web application to use Derby as a data source.

Finally, you used the JSTL SQL tag library to issue queries against the Derby database you configured as a data source.

I hope this article has provided a solid foundation for using the numerous tools available within WTP and the Derby plug-ins. Starting from these fundamentals, you can develop robust Web applications using Derby as the data store.


Downloads

DescriptionNameSize
Derby Database files for the Web ApplicationLowFareAirData.zip  ( HTTP | FTP )216KB
LowFare Air war fileLowFareAir.war  ( HTTP | FTP )31KB

Resources

Learn

  • Derby: The Apache Derby site contains online documentation, downloads (source and binary), and integration information with other open source projects. Both a developers and users mailing list is available to subscribe to or browse as archives.
  • Derby Plug-ins: The Apache Derby Integration area contains a section, Eclipse Plug-ins, which has a brief presentation on the Derby plug-ins and a Lab on how to use them available for download.
  • JSTL: An excellent series of articles on developerWorks, A JSTL primer, is a great place to start learning about the JSTL.
  • WTP: The WTP Web site is host to all things WTP. In particular, the tutorials are helpful and subscribing to the newsgroup can help when problems are encountered.
  • The Cloudscape information center contains all of the Cloudscape documentation on-line.
  • developerWorks provides numerous technical articles related to Cloudscape.

Get products and technologies

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=93337
ArticleTitle=Build Web applications with Eclipse, WTP, and Derby
publish-date=03092006