creating database web applications with eclipse
TRANSCRIPT
-
8/8/2019 Creating Database Web Applications With Eclipse
1/25
Creating Database Web Applications withEclipseSummary
The Eclipse Web Tools and Data Tools Projects deliver a feature-rich environment for developing JavaEE database-driven web applications. This tutorial walks you through the process of creating a simple
database web application using Eclipse WTP/DTP, Tomcat, and the Derby database engine.
By Stephen Schaub, Department of Computer Science, Bob Jones University
Copyright 2007 Stephen Schaub, Source code in this article is made available under the EPL, v1.0, remainder of the
presentation is licensed under Creative Commons Att. Nc Nd 2.5 license
March 13, 2006
Updated January 3, 2008 for Eclipse 3.3
Introduction
Creating database-driven web applications in Java has traditionally involved a steep learning curve. Even
if you already know how to write Java programs, and have a basic understanding of web applications, theJava Enterprise Edition (Java EE) stack is daunting. Learning to use the Servlet API and JavaServer Page
(JSP) technology to generate dynamic content is just the beginning. Installing and configuring an open
source Java EE web application server and a DBMS, and getting them to talk to each other, can require
significant developer effort.
In this article, I will demonstrate how the combination ofEclipse Web Tools Platform,Eclipse Data
Tools Platform,Tomcat, and Derbyhelp to "lower the bar" by virtually eliminating the server
administration issues, allowing developers to focus on the task at hand: building the web application. I
will assume that you understand the basics ofSQL, and are familiar with HTML and basic web
application concepts.
Prerequisites
You will need the following software to build the project:
1. Eclipse IDE for Java EE Developers 3.3
Eclipse can be downloaded fromhttp://www.eclipse.org/downloads/. The Eclipse IDE for Java EE
Developers distribution packages together all of the Web Tools Platform components and their dependencies
in a convenient all-in-one download archive. To install, simply extract the archive to your hard drive.
2. Tomcat 6.0
Available fromhttp://tomcat.apache.org/download-60.cgi. This tutorial was written using version 6.0.14.
Note: If you're a Windows user, I recommend downloading the zip distribution and extracting it, instead of
getting the packaged Tomcat installer, which installs Tomcat as a Windows service (not an appropriate
configuration for use with Eclipse WTP).3. Derby Plugin for Eclipse
Get the Derby Plugin for Eclipse (derby_core_plugin_10.2.2.zip and derby_ui_plugin_1.1.0.zip), available
at http://db.apache.org/derby/derby_downloads.html.
Note: The plugins contain the Derby engine. You don't need to download the standard Derby distribution for
this tutorial.
4. JRE 6.0
Sun's JRE is available fromhttp://java.com/en/download/manual.jsp
Getting Started with Derby
Derby is an open-source pure-Java Database Management System (DBMS). I picked it as the DBMS for
this article because it is freely available, integrates nicely with Eclipse, runs on all platforms that Eclipse
runs on, and, most importantly, is far simpler to install and administer than traditional DBMS's.
http://www.eclipse.org/webtools/http://www.eclipse.org/webtools/http://www.eclipse.org/datatools/http://www.eclipse.org/datatools/http://www.eclipse.org/datatools/http://www.eclipse.org/datatools/http://tomcat.apache.org/http://tomcat.apache.org/http://db.apache.org/derby/http://db.apache.org/derby/http://en.wikipedia.org/wiki/SQLhttp://www.eclipse.org/downloads/http://www.eclipse.org/downloads/http://tomcat.apache.org/download-60.cgihttp://tomcat.apache.org/download-60.cgihttp://tomcat.apache.org/download-60.cgihttp://db.apache.org/derby/derby_downloads.htmlhttp://java.com/en/download/manual.jsphttp://java.com/en/download/manual.jsphttp://www.eclipse.org/datatools/http://www.eclipse.org/datatools/http://tomcat.apache.org/http://db.apache.org/derby/http://en.wikipedia.org/wiki/SQLhttp://www.eclipse.org/downloads/http://tomcat.apache.org/download-60.cgihttp://db.apache.org/derby/derby_downloads.htmlhttp://java.com/en/download/manual.jsphttp://www.eclipse.org/webtools/ -
8/8/2019 Creating Database Web Applications With Eclipse
2/25
Like most popular DBMS's, Derby has a client/server architecture. The Derby engine runs as a server
process, accepting connections from client applications. To use Derby, you start the Derby server, then
you use Java database management tools to connect to the Derby server, create and populate databases,
run queries, and so on. The Derby plugin for Eclipse described in this article integrates the Derby server
controls into Eclipse, so you can start and stop the Derby server from the Eclipse environment. The
plugin also stores the database files in the workspace, simplifying backup.
Installing the Derby plugin for Eclipse is fairly straightforward. Here's how to do it.
1. Unzip the two Derby Eclipse plugins (derby_core_plugin_10.2.2.zip and
derby_ui_plugin_1.1.0.zip) into your eclipse installation folder (ECLIPSE_ROOT). Detailed instructions are
available here:http://db.apache.org/derby/integrate/plugin_howto.html#Installing+the+plug-ins
2. In your ECLIPSE_ROOT/plugins folder, you should have a folder
named org.apache.derby.core_10.2.2. Copy the file derbyclient.jar from that folder to your
TOMCAT_ROOT/lib folder (if you're using Tomcat 5.x, install into TOMCAT_ROOT/common/lib). This
installs the Derby JDBC driver into Tomcat for use in a DataSource.
Eclipse organizes files in the workspace into projects. When you use the Derby plugin for Eclipse, you
create an empty Java project, and then you "Derby enable" it. This project then becomes the repository for
all of the Derby databases that you create in your workspace.
Follow these steps to create a Derby data project in Eclipse:
1. Start Eclipse. If you have an existing Eclipse workspace, I suggest choosing a new workspace
folder for this tutorial.
2. Choose Window > Preferences from the menu to open the Eclipse Preferences dialog. Navigate
to Connectivity > Driver Definitions. Select the Derby 10.2 folder and clickAdd....
http://db.apache.org/derby/integrate/plugin_howto.html#Installing+the+plug-inshttp://db.apache.org/derby/integrate/plugin_howto.html#Installing+the+plug-inshttp://db.apache.org/derby/integrate/plugin_howto.html#Installing+the+plug-ins -
8/8/2019 Creating Database Web Applications With Eclipse
3/25
-
8/8/2019 Creating Database Web Applications With Eclipse
4/25
3. In the New Driver Definition dialog, select Derby Client JDBC Driver and clickOK.
4. In the Provide Driver Details dialog, select the derbyclient.jar file and clickEdit Jar/Zip. Navigate
to the location of the derbyclient.jar file on your system and click OK.
-
8/8/2019 Creating Database Web Applications With Eclipse
5/25
5. Click OK in the Preferences dialog.
6. Create a new Java project by selectingFile > New > Other. SelectJava > Java Projectand
clickNext. Enter the name data for the project, choose the option to create separate source and output
folders, and clickFinish.
-
8/8/2019 Creating Database Web Applications With Eclipse
6/25
7. This project will hold your Derby database for this tutorial. In the Package Explorer, right-click
your new project and chooseApache Derby > Add Apache Derby Nature. This action marks the project as a
Derby project, capable of storing one or more Derby databases.
-
8/8/2019 Creating Database Web Applications With Eclipse
7/25
8. Next, right-click your data project and chooseApache Derby > Start Derby Network Server.
-
8/8/2019 Creating Database Web Applications With Eclipse
8/25
9. This action starts the Derby server. You should see the following message appear in the Eclipse
Console:
10. The Derby server will run as long as you have Eclipse open. If you close Eclipse, the next time
you start Eclipse, you will need to start the Derby server again. The server accepts connections only from the
local host, which is just what you want for a development database.
Creating a Derby Database
Now that you've installed Derby and started the Derby server, you will create a new database to hold the
data for your web application.
To create a new Derby database, you must use a Java DBMS management tool to connect to the Derby
server with a specially formatted connection string that includes the name of the database you want tocreate, and an option that tells the Derby server to create the database. Here's how to accomplish the task
using the Eclipse Data tooling included with the Eclipse IDE for Java EE Developers.
-
8/8/2019 Creating Database Web Applications With Eclipse
9/25
1. Select Window > Open Perspective > Other. Choose Database Development from the list.
2. In the Data Source Explorer view, right-click the Databases folder and chooseNew.
3. In the New Connection Profile dialog, choose SQL Model-JDBC Connection, and clickNext.
(We won't be using the Derby Embedded Database option, which prevents multiple JVM's from concurrently
accessing the database.)
4. On the next page, enter the name sample for your database and clickNext. LeaveAuto-connect atstartup unchecked.
5. Fill out the final page as shown. Note carefully the selections: Choose the Derby Client JDBC
Driver from the driver dropdown; accept the default Database name ("SAMPLE"); use any non-blank User
ID and non-blank password (the password is ignored by Derby).
-
8/8/2019 Creating Database Web Applications With Eclipse
10/25
6. ClickFinish. The connection wizard will display a new connection in the Databases folder
namedsample. Right-click thesample icon and choose Connectto open the connection. This will cause
Derby to create the database. The database files are stored in a folder namedsample in your data project. If
you want to see them, switch to the Java perspective, right-click the data project and choose Refresh.
-
8/8/2019 Creating Database Web Applications With Eclipse
11/25
To create a backup of your Derby database, simply stop the Derby server, make a
copy of the sample folder and its subfolders, and restart the server. Restoring the
database is just as simple: stop the server, replace the sample folder structure with
the backup copy, and restart the server.
Now that the database is created, it's time to create a table for our application and populate it with some
data. The current version of the Data tooling doesn't include any wizards to create a table, so we'll create a
table using SQL Data Definition commands.
To execute SQL commands using the Eclipse Data tooling, you will create a SQL File, which provides an
editor that allows you to enter and execute SQL statements.
1. ChooseFile > New > SQL File. ClickNext, and enter scratch (or whatever) as the filename.
Select sample from the Connection profile name dropdown, and select SAMPLE from the Database
name dropdown to associate the SQL file with your Derby sample database. ClickFinish.
-
8/8/2019 Creating Database Web Applications With Eclipse
12/25
2. A blank SQL editor will open. Copy and paste the following code into the editor:
3. CREATE TABLE app.posts (4. post_id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY(START WITH 1, INCREMENT BY 1),
5. postname VARCHAR(50),6. comments VARCHAR(512) NOT NULL7. );8.9. INSERT INTO app.posts(postname, comments) VALUES('Fred Jones',10. 'Derby is cool, and the Eclipse plugin makes usingit a snap!');
11. INSERT INTO app.posts(postname, comments) VALUES('WilmaHarris',
-
8/8/2019 Creating Database Web Applications With Eclipse
13/25
'Tomcat lets me register DataSources using a file in my webproject? That''s great stuff!');
12. Right-click in the editor and chooseExecute All.
13. The SQL Results tab will appear to show the results of the execution.
14. Now, back on the Data Source Explorer tab, browse to find the table that was created. Right-click
the Posts table and chooseData > Editto view the contents in an editable grid.
-
8/8/2019 Creating Database Web Applications With Eclipse
14/25
Creating a Web Project
Now that the database is in place, we're ready to begin creating our web application. A Java EE web
application consists of a collection of dynamic resources (such as Servlets, JavaServer Pages, and other
Java classes), static resources (HTML pages and images), and configuration files, all organized in a
standardized directory. Eclipse helps you organize your web applications using a type of project called a
Dynamic Web Project. When you create a Dynamic Web Project, you must select a Java EE web
application server, which provides libraries needed by the project.
Follow these steps to create the project.
1. SelectFile > New > Other. Select Web > Dynamic Web Projectand clickNext. Enter the Project
Name demo, and clickNew beside the Target runtime dropdown.
-
8/8/2019 Creating Database Web Applications With Eclipse
15/25
-
8/8/2019 Creating Database Web Applications With Eclipse
16/25
2. Select Tomcat 6.0 and clickNext.
3. Select your Tomcat 6.0 installation folder (the root folder of the extracted Tomcat download
archive). ClickFinish.
-
8/8/2019 Creating Database Web Applications With Eclipse
17/25
4. Back in the New Dynamic Project wizard, clickFinish to create the project.
5. The application will use JSP tag libraries that you must download and install into your project.
Browse tohttp://jakarta.apache.org/site/downloads/downloads_taglibs-standard.cgiand download the
jakarta-taglibs-standard-1.1.2.zip distribution (or, you can get them from thecompleted sample that
accompanies this article). Copy the jstl.jar and standard.jar files from the download archive into your
project's WebContent/WEB-INF/lib folder. When you've done this, you may need to right-click on the
project and chooseRefresh. In the Java EE Perspective's Project Explorer, you should see them listed both
under Web App Libraries and under the WEB-INF/lib folder as shown here (note that if you're using the Java
perspective, with the Package Explorer, you won't see them in the WEB-INF/lib folder):
http://jakarta.apache.org/site/downloads/downloads_taglibs-standard.cgihttp://jakarta.apache.org/site/downloads/downloads_taglibs-standard.cgihttp://jakarta.apache.org/site/downloads/downloads_taglibs-standard.cgihttp://www.eclipse.org/articles/Article-EclipseDbWebapps/demo.ziphttp://www.eclipse.org/articles/Article-EclipseDbWebapps/demo.ziphttp://jakarta.apache.org/site/downloads/downloads_taglibs-standard.cgihttp://www.eclipse.org/articles/Article-EclipseDbWebapps/demo.zip -
8/8/2019 Creating Database Web Applications With Eclipse
18/25
Next, we'll tackle the issue of database connection management for our application. Servlets and JSP
pages that access a database are usually designed to obtain a database connection for each incoming
request, and release the connection when the request has finished processing. Since opening and closing
database connections is usually an expensive operation, an efficient web application makes use of JNDI
connection pooling to speed up database access. The application server maintains a pool of connections to
the database, and makes them available to the web application via a DataSource object.
Since connection pools are managed by the application server, not the web application, configuring
connection pooling can be a pain. Fortunately, Tomcat 6.0 makes it really easy. Tomcat allows the
developer to configure the database connection pool using a configuration file in the web project. We'll
use that feature to simplify this tutorial.
The technique discussed here also works for Tomcat 5.5. If you are using Tomcat 5.0
or older, or another application server, you must consult your application server for
information on configuring a DataSource.
-
8/8/2019 Creating Database Web Applications With Eclipse
19/25
1. ChooseFile > New > File, select the META-INF folder, and enter the name context.xml.
2. Copy and paste the following into your context.xml file (you may have to click on the Source tab
at the bottom of the editor to be able to paste). This defines a DataSource with the name "jdbc/SampleDB".
Our application will retrieve database connections from the pool using this name.
3. 4.5. 6.
If you want to use a different DBMS, simply change the driverClassName and urlto
values appropriate for your database, and make sure you install your DBMS's JDBC
driver in Tomcat's common/lib folder.
Writing the Application
Standard Java EE web applications use servlets and JSPs to generate dynamic content. For this tutorial,we'll create a JSP page to allow the user to interact with the database. You don't have to know Java to
-
8/8/2019 Creating Database Web Applications With Eclipse
20/25
write JSPs; the JSP Standard Tag Library provides all the capabilities a simple database application
needs.
We'll begin with a simple page that displays the comments in the Posts table.
1.ChooseFile > New > File. Fill out the dialog as shown, making sure that the WebContent folder
is highlighted.
2. Next, paste the following code into the page:
3.
4.5. 6. 7.
8. 9.10.
11. 12.
13. Speak To Me, Please14.
-
8/8/2019 Creating Database Web Applications With Eclipse
21/25
15. 16.17. Speak To Me, Please18.19. Welcome to the Acme Corp. feedback site.20.
21. Here's what your fellow workers havesaid:
22. 23. WorkerComment24.
25. 26. SELECT postname, comments FROMapp.posts
27. 28.
29.
30. 31. 32.
33. 34. 35. 36.37. 38.
Notes about the code:
The taglib directives allow the use of the and tags from the JSP Standard Tag Library.The tag references the JNDI DataSource whose attributes are defined in the
context.xml file you created earlier. The JSP engine retrieves a connection from the DataSource for use by
queries later in the page.
The tag executes a query against the database and exposes the results in a JSP page
variable named qryPosts.
The tag iterates over the results of the query. The items expression "${qryPosts.rows}"
references the collection of rows returned from the query and exposed via theqryPosts JSP variable. The
body of the tag emits a row of the HTML table for each row in the query result set. Each time
the body is evaluated, the iterator variable rowreferences the next row of the query result set.
The expression inserts the value of thepostname field of the
current row of the result set.
If you're using an application server other than Tomcat 6.0, and you don't know how
to configure a DataSource, you can embed the connection attributes directly in the
JSP page. You won't get connection pooling, but you can at least get the sample
working by replacing line with the following lines:
If you do this, you may also find it necessary to copy the derbyclient.jar file to your
project's WEB-INF/lib folder to make the JDBC driver class available to your
application.
Testing the Application
-
8/8/2019 Creating Database Web Applications With Eclipse
22/25
We haven't finished the application yet, but let's take a break from coding to test the existing
functionality.
1. Right click on demo.jsp and selectRun As > Run on Server. Select the Tomcat 6.0 server, and
clickFinish.
2. Eclipse starts the Tomcat application server. After the server starts, you should see a page like
this.
Note: If Eclipse has problems starting the server, make sure you don't already have Tomcat running on your
system. If you have an instance of Tomcat running, you should stop it before trying to test your application in
Eclipse.
3. Try adding a new row to the Posts table using the Eclipse table editor I mentioned earlier. Afteryou save the new row, you should be able to click Reload in the web browser, and the new row should
appear.
Letting Users Leave Feedback
This application doesn't allow users to contribute feedback. Let's enhance the JSP page to provide a form
that users can fill out to add comments.
Switch to the JSP editor and copy and paste the following code just above the closing tag:
Your name: (optional)
Your comments:
${comments}
-
8/8/2019 Creating Database Web Applications With Eclipse
23/25
${msg}
When the user clicks the submit button on this form, the name and comments entered by the
user will be submitted to the demo.jsp page for processing. A message indicating success or failure
will be placed in a JSP variable named msgto inform the user of the result of the processing.
Next, we need to write the code to process the form submission. Insert the following code at the top of thepage, after the tag:
INSERT INTO app.posts(postname, comments) VALUES(?,
?)
Notes about the code:
When the user submits the form, his name and comments are submitted to the JSP page. The
statements retrieve those values and place them in local JSP variables named nameand comments.
If this is a form submission, the action parameter will be "Submit", and the code inside the
block will execute to process the submission. On the initial page load, the action parameter will be empty,
and the code inside the block will not execute.
The user may opt to omit his name, but must supply some comments. The test verifies that
the comments variable is not blank.
The tag is used to execute an INSERT, UPDATE, or DELETE statement against the
database. The question marks (?) indicate places in the query where user input is substituted.The tags supply values for the ? placeholders.
The contents of a JSP variable named msgis displayed at the bottom of the form. Here, msgis set to
indicate to the user that the submission was successfully saved.
If the user failed to enter comments, this code is executed, and sets msgto indicate to the user why the
submission could not be successfully processed.
After entering this code, save the changes. Switch back to the browser view and click Reload. You should
see a form appear:
-
8/8/2019 Creating Database Web Applications With Eclipse
24/25
Try entering some comments and clicking Submit. The page should process the submission, and you
should see your comments appear in the table. Check the database table using the table editor; you should
find that the comments have been saved there.
Deploying the Database
When you are ready to deploy the application to a production Tomcat application server, you must copy
the Derby database data folders to the application server computer. You then have a couple of options for
accessing the Derby database from your deployed application.
You can install Derby on the application server computer, and run it in client/server mode. Your application
connects to the database using the same technique as I've demonstrated in this article. The drawback to thisapproach is that you have two server processes to manage: Tomcat and Derby.
You can use Derby in embedded mode. In this scenario, the Derby engine runs inside the Tomcat process.
You don't have to stop and start Derby separately from Tomcat. For more information, refer to the Derby
website.
Summary
The Eclipse Web Tools Project, combined with Tomcat 6.0, Derby, and the Eclipse Data Tools Project,
reduces the burden of server administration, delivering a convenient platform for Java EE database web
application development. This article demonstrated how to construct a simple Java EE web application
using these tools.
-
8/8/2019 Creating Database Web Applications With Eclipse
25/25
Resources
The completed sampleis available. You can import it into Eclipse by renaming it to demo.war,
choosingFile > Import, and selecting Web > WAR File. Note that the database is not included; you must set
that up following the instructions in the article.
If you want to know more about JSP application development, here are some resources that can help.
http://java.sun.com/products/jsp/docs.html
Sun JSP Documentation
http://java.sun.com/products/jsp/jstl/1.1/docs/tlddocs/index.html
JSTL Quick Reference
http://jcp.org/aboutJava/communityprocess/final/jsr052/index2.html
JSTL Specification and Reference
http://www.eclipse.org/webtools/
Eclipse Web Tools Platform
http://www.eclipse.org/datatools/
Eclipse Data Tools Platform
http://tomcat.apache.org/
Tomcathttp://db.apache.org/derby/
Derby
http://en.wikipedia.org/wiki/SQL
SQL
http://www.eclipse.org/articles/Article-EclipseDbWebapps/demo.ziphttp://www.eclipse.org/articles/Article-EclipseDbWebapps/demo.ziphttp://java.sun.com/products/jsp/docs.htmlhttp://java.sun.com/products/jsp/docs.htmlhttp://java.sun.com/products/jsp/jstl/1.1/docs/tlddocs/index.htmlhttp://jcp.org/aboutJava/communityprocess/final/jsr052/index2.htmlhttp://www.eclipse.org/webtools/http://www.eclipse.org/webtools/http://www.eclipse.org/datatools/http://tomcat.apache.org/http://db.apache.org/derby/http://db.apache.org/derby/http://en.wikipedia.org/wiki/SQLhttp://www.eclipse.org/articles/Article-EclipseDbWebapps/demo.ziphttp://java.sun.com/products/jsp/docs.htmlhttp://java.sun.com/products/jsp/jstl/1.1/docs/tlddocs/index.htmlhttp://jcp.org/aboutJava/communityprocess/final/jsr052/index2.htmlhttp://www.eclipse.org/webtools/http://www.eclipse.org/datatools/http://tomcat.apache.org/http://db.apache.org/derby/http://en.wikipedia.org/wiki/SQL