Creating a Web application that accesses a database
In the final exercise, create a Web application that can access a Derby database using a Relational Record List. First create a Web project, and then later add the database display capability to the Web application.
Web application development can be done in the Web perspective. All Web elements, such as JSP pages and Java servlets, are stored in Web projects.
A J2EE application consists of several components: Application Client, Web, and Enterprise JavaBeans (EJBs) modules. Each module is a self-contained unit that can be deployed and run on a server individually or as a group in an enterprise application. You work with a Web module in this exercise.
- From the workbench, select File > New > Project.
- Select Web > Dynamic Web Project > Next.
MyWebProjectas the project name.
- In the EAR Membership section, click New to create a new EAR Application Project.
MyWebProjectEARas the project name. Click Finish. Select No if prompted to switch to the J2EE perspective.
- Click Finish.
- When you are asked to switch to the Web Perspective, click Yes.
The major contents of a Web project are JSP files and servlets. Other Web elements, such as HTML, style sheets, and images, are also stored in a Web project. Figure 14 shows the structure of a Web project, with its different Web elements in different locations.
After you create a Web project, you can see it in the Dynamic Web Projects folder. The Web project is set up in compliance with the J2EE standard to be exported as a WAR file for deployment. Java servlets and other supporting Java classes are stored in the Java Resources folder inside the Java Resources directory. JSP files, HTML pages, style sheets, Java script files, and images are stored in the WebContent directory. The Web Deployment Descriptor, web.xml, contains the deployment configurations of the Web application. It must be present in the Web module to be deployed on a J2EE server, such as WebSphere Application Server.
Figure 14. Structure of a Web project
- Right-click on MyWebProject > New > Web Page.
MyFirstJSPas the file name and make sure JSP is selected as the template. Click Finish. Notice that the JSP file is created under the folder WebContent.
- Modify the JSP file from the editor using the Design or the Source view. Switch to the Source view.
Enter the bold code, shown below, in the editor. This line is called a scriptlet,
which is Java code enclosed by <% and %>. You can enter any
Java code within these brackets. You can also use scriptlets to print
something in a JSP file. For example, <%="hello"%> prints the text
Listing 4. Code for MyFirstJSP.jsp
<BODY> <% String a = "Hello"; a+= "World!!"; %> <%=new java.util.Date() %> <%=a %> <BR> </BODY>
- Press Ctrl-S to save.
With Application Developer you can run the JSP file in the embedded server. Start the server from the Server view and then run the JSP file using the URL http://localhost:9080/MyWebProject/MyFirstJSP.jsp. Alternatively, right-click the JSP file and select Run on Server.
- In the Project Explorer view, right-click the MyFirstJSP.jsp file and select Run As > Run on Server.
Select an existing server and click Finish to automatically add the
project to the server and run the JSP file in the internal browser.
Figure 15. Running the MyFirstJSP
Application Developer has a number of relational database tools, or views, that can be accessed through the Data perspective. Some of the more important views in this perspective are the Data Project Explorer, Database Explorer, and DB Output views.
In the Database Explorer view, connections can be made to a list of supported relational databases. Some of the supported databases are IBM Cloudscape, IBM DB2 Universal Database, Derby, MySql, Microsoft SQL Server, Sybase Enterprise Systems, and Oracle Database. Refer to the product help for a complete list of the supported databases.
In this step, create a database connection to a Derby database that is shipped with Application Developer. A Derby database can use any file system folder as a database. You do not need to create the database before trying to establish a connection. If you select Create the database if required, the Derby database is created before the connection is established.
Derby accepts only one database connection at a time. For example, if a WebSphere Application Server is already connected to the Derby database, an attempt to make a connection from the Database Explorer will fail.
To create a Derby connection:
If the server is running, stop it now or you cannot
establish a database connection.
In the Servers view, right-click on WebSphere Application Server v6.1 and select Stop.
- Switch to the Data perspective. Select Window > Open Perspective > Other. Select the Show all check box and select Data. Click OK. Select OK if asked to enable the Core Database Development capability.
- Right-click Connections in the Database Explorer view, in the bottom left of the perspective, and click New Connection.
- Select Derby 10.1 as shown in Figure 16.
Enter any file system directory in the Database location,
C:\HelloWorldSeries\AddressDB. You do not have to physically create the folder in the file system; just type in a new folder name.
- Click Browse to select the class location. Browse to <RAD_INSTLLED>\runtimes\base_v61\derby\lib\derby.jar as the class location.
- Make sure the Create the database if required box is checked. If it is, the tool sets up the Derby database at that location. Because Derby is a test database, you do not need a user ID and password to connect to it. Enter any user ID in the user information section.
Click Test Connection to see if a connection can be established.
You should see a window saying the connection is successful.
Click Finish to create the connection.
If it returns an error stating the user name is invalid, leave both user ID and password blank.
Figure 16. Creating a Derby database connection
The connection AddressDB is displayed in the Database Explorer view.
Expand the connection to see AddressDB, as shown below.
Figure 17. Database Explorer AddressDB connection
Right now, the database is empty. Use the Data Project Explorer view to create a data model that contains schema and tables. A data model represents relational data objects that may or may not exist in the database. Later in this exercise, the data model will be deployed to an actual database.
- In the Data perspective's Data Project Explorer view, right-click New and click Project.
- Select Data > Data Design Project > Next.
MyDataProjectas the project name and click Finish.
- Right-click MyDataProject and click New > Physical Data Model.
Figure 18. Creating a physical data model
- Select Derby as the Database and 10.1 as the version. Click Finish.
- Expand MyDataProject > Data Models > Database Model.dbm > Schema.
- Create a table named
AddressTable. Right-click on the Schema > Add Data Object > Table. A default table named Table1 is created.
Figure 19. Creating a table
- Select Table1 and go to the Properties view. Change the following:
Figure 20. Modifying table name
- Switch to the Columns tab in the Properties view.
to add a column. Enter
idas the column name. Click the Primary Key check box and select INTEGER as the column type.
- Click New. Enter
nameas the column name. Select VARCHAR as the column type. Change the string length to 100.
Click Add Another. Enter
addressas the column name. Select VARCHAR as the column type. Change the string length to 200.
- Save the file.
Figure 21. Table columns for AddressTable
- In the Data Project Explorer, right-click the newly created AddressTable and click Generate DDL. Click Next three times, accepting all the default values. This generates a DDL file named script.sql that can be executed to create the AddressTable in the database.
Right-click script.sql under the SQL Scripts folder and select Run SQL. This will create the AddressTable in the Derby database.
Figure 22. Run SQL
Select the Use an existing connection option.
Select AddressDB as the database connection.
Figure 23. Run SQL wizard
Expand your connection until you see the Tables folder.
You'll see the AddressTable you just created.
Figure 24. After the schema definition is deployed
Although you have created the table in the database, it has no data. Create an Insert statement that can insert a record into the table.
In the Data Project Explorer, expand MyDataProject.
SQL Scripts and click New > SQL Statement.
CreateRecordas the Statement name. Select INSERT as the Statement template, and click Finish.
Figure 25. Creating an insert statement
- Select the Use existing connection option. Select AddressDB as the database connection. Click Finish.
- Right-click in the middle panel labeled Tables and click Add Table. Select ADDRESSTABLE as the table name. Click OK.
- Click all three check boxes in the AddressTable to select all the columns in AddressTable.
In the bottom panel of the editor, enter
1for the ID column value. Enter
'Jane'as the name value, and
'8200 Warden Ave'as the address value.
- Save the file and close the editor.
Figure 26. Insert Statement editor
In the Data Project Explorer, right-click CreateRecord and click Run SQL.
Select Use an existing connection and AddresDB.
Click Finish. Check in the DB Output view if it ran successfully.
Figure 27. Execute CreateRecord
In the Database Explorer view, expand AddressDB until you see
Right-click AddressTable and select Data > Sample Contents.
Figure 28. Sample contents
Figure 29. Sample contents results
In this step, add a Relational Record List to the MyFirstJSP.jsp to display the table content. A Relational Record List lets you hook up to a database without doing any JDBC coding. Drag and drop the record list to the JSP file to make a read-only list.
- Switch to the Web perspective and open MyFirstJSP.jsp in the editor.
In the editor, switch to the Design view.
From the Palette, expand the Data tab, click on
Relational Record List, and drop it to the bottom of the JSP editor's canvas.
Use the Relational Record List to make multiple rows of data
available to the page, for example all the rows from a table.
Figure 30. Relational Record List
addressTableRecordListas the Name. Click Next.
- Click on New to create a new connection.
- In the New JDBC Connection window, select Use an existing connection and AddressDB, then click Finish.
Select AddressTable from your schema as shown below. Click Finish.
Figure 31. Create a new Relational Record List
- Save the file. Figure 32 shows the final JSP page.
Figure 32. JSP editor with the Relational Record List
Start the server and run the JSP file.
- In the Server view, right-click on WebSphere Application Server v6.1 and select Start.
- In the Project Explorer view, right-click MyFirstJSP.jsp and select Run > Run on Server. Click Finish.
The JSP displays the row from the AddressTable.
Enter more rows to the AddressTable and run the JSP file again.
You should see more rows in the JSP.
Figure 33. Running the JSP file