Creating a Simple Web Application in NetBeans IDE Using MySQL
Contributed and maintained by
.
This document describes how to create a simple distributed web
application that connects to a MySQL database. It also covers
some basic ideas and technologies in web development, such as
Java Server Pages and three-tier architecture. This tutorial
is designed for beginners who have a basic understanding of
Java programming and web development and are looking to apply
their knowledge using MySQL.
MySQL is a popular Open Source
database management system commonly used in web applications due to
its speed, flexibility and reliability. MySQL employs SQL, or
Structured Query Language, for accessing and processing
data contained in databases.
This tutorial continues from the Connecting
to a MySQL Database tutorial and assumes that you already have a
connection to a MySQL database created and configured in NetBeans
IDE. The table data that is included in
ifpwafcad.sql.
is also required for this tutorial. This SQL file creates two tables,
Counselor and Subject, then populates them with
sample data. Save this file to a local directory, then open it in
NetBeans and run it on the MySQL database. In this tutorial,
the database we are working in is named MyNewDatabase.
Expected duration: 40 minutes
The following topics are covered below:
Getting the Software
Before you begin, make sure you have the following software installed
on your computer:
- NetBeans IDE 5.5 (download)
- Java SE Development Kit (JDK™) version 5.0 or higher
(download)
- MySQL database
(download)
- JDBC Driver for MySQL (download)
- Sun Java System Application Server (download)
Note: The Sun Java System Application Server (SJSAS) is
not strictly required to work through this tutorial, as you can use Tomcat,
which is a web server that comes bundled with NetBeans IDE. However, if you
are planning on developing applications in the IDE, the application server
offers a lot of support for tools and technologies that can make a developer's
life easier. Consider downloading the Java
EE 5 Tools Bundle, which includes SJSAS, NetBeans IDE, as well as the
Java SE Development Kit. Find out more by visiting the
SJSAS
product page.
Planning the Structure
Simple web applications are often designed using a three-tier architecture,
in which the user interface, the functional process logic, and the data access
and storage are all maintained independently from each other. In other words,
each of the three tiers, or layers, represents a module which can be
run on its own platform (hence the term 'distributed').
For the application you are building in this tutorial, the Presentation Layer,
or user interface, can be represented by JSP pages which prepare the HTML that is
sent to the client browser. You can code the middle, or Logic, layer with
a few simple Java classes. Finally, the Data Layer can be implemented
using several tables in a MySQL database. Consider the following client-server
scenario:
A welcome page (index.jsp) displays in a browser containing a simple form
allowing a visitor to specify data. When a request is passed to the server containing
the data, a JSP page is accessed (response.jsp), which immediately passes
the specified data to SubjectCounselor.java so that the information retrieval
process can begin. The Java class processes the data and employs AccessDB.java
to prepare an SQL query to be sent to the database. AccessDB.java then connects
to the database and subsequently retrieves data from the Subject and
Counselor tables, as specified by the SQL query. Finally, the return trip
is initiated, and the retrieved data is included in response.jsp
which forms the server's response to the client.
Creating a New Project
In order to implement the scenario described above, you will develop a simple application for
a fictitious organization named IFPWAFCAD, or The International Former Professional
Wrestlers' Association for Counseling and Development. The application enables a user
to choose a counseling subject from a drop-down list (index.jsp), then retrieves
data from the MySQL database and returns the information to the user (response.jsp):
Create a new project in the IDE by performing the following steps:
- Launch NetBeans IDE and choose New Project (Ctrl+Shift+N) from the File menu. Under
Categories select Web; under Projects select Web Application. Click Next.
- In Project Name, enter IFPWAFCAD. From the Server drop-down list, select the
server you plan work with. Leave all other settings at their defaults and click Finish.
Note: If you downloaded the SJSAS but have not yet
registered it in NetBeans IDE, you can do so by clicking the Manage button
to the right of the Server drop-down list. The Server Manager opens, enabling
you to register the new server. For more information, refer to Registering
a Sun Java System Application Server Instance from the IDE's Help Contents (F1).
The IDE creates a project template for the entire application, and opens
an empty JSP page (index.jsp) in the Source Editor. To gain a better
understanding of the structure of the project template, refer to About
Structuring Web Source Files from the IDE's Help Contents (F1).
Preparing the Web Pages
The application's Presentation Layer consists of two JSP pages: the welcome page and the
response page that returns the specified data to the user. You can start by creating
placeholders for these two pages. This means that you will add the HTML now, and add the
JSP-specific code once the Logic Layer has been implemented.
Implementing the Welcome Page
Convert index.jsp into IFPWAFCAD's welcome page:
- Make sure index.jsp is opened in the Source Editor. If it is not already
open, double-click index.jsp from IFPWAFCAD > Web Pages in the Projects
window. Then, in the Source Editor, change the title to IFPWAFCAD Homepage.
- For the body, replace what is listed with the following code:
<body>
<br>
<h2 align="center">Welcome to IFPWAFCAD, the International Former Professional
<br>Wrestlers' Association for Counseling and Development!</h2>
<br><br>
<table width="55%" align="center">
<tr>
<td><strong>IFPWAFCAD offers expert counseling in a wide range of fields.</strong></td>
</tr>
<tr>
<td><br>To view the contact details of an IFPWAFCAD certified former
<br>professional wrestler in your area, select a subject below:</td>
</tr>
<tr>
<td>
<form action="response.jsp" method="post">
<br>
<strong>Select a subject:</strong>
<select name="subject_id" size="1" >
<option value="1">Marriage Guidance</option>
<option value="2">Financial Consultancy</option>
</select>
<input type="submit" name="submit" value="submit">
</form>
</td>
</tr>
</table>
</body>
This creates a simple form inside a table. Later, when you implement the JSP code,
you will replace the sample subjects with a loop that grabs all subject names directly from
the database. Also, note that the form submits to the response.jsp page that you are
about to create.
Implementing the Response Page
To create a placeholder for response.jsp, do the following:
- Right-click the IFPWAFCAD project node in the Projects window and choose New > JSP.
The New JSP File dialog opens.
- In the JSP File Name field, enter response. Note that Web Pages is currently selected
for the Location field, meaning that the file will be created in the same directory as the
welcome page.
- Accept all other defaults and click Finish. A template for the new response.jsp page
is generated and opens in the Source Editor. A new JSP node also displays under Web Pages in the
Projects window:
- In the Source Editor, change the title temporarily to something like
"(Chosen Subject)".
- Next, replace the template's body with the following code:
<body>
<br>
<h2 align="center">(Chosen Subject)</h2>
<br>
<table width="60%" align="center" cellpadding="10">
<tr>
<td valign="top" width="25%"><strong>Description: </strong></td>
<td><em>(subject description)</em><br></td>
</tr>
<tr>
<td valign="top"><strong>Counselor: </strong></td>
<td><span style="font-size:large"><strong>(counselor's name)</strong></span>
<br><span style="align:center">
<em>member since: (a date)</em></span></td>
</tr>
<tr>
<td valign="top"><strong>Contact Details: </strong></td>
<br><strong>phone: </strong><(a telephone number)></td>
</tr>
</table>
</body>
This creates an HTML template for the output that will be generated once you code in the JSP.
Note that all of the fields above that are enclosed in parentheses will be generated dynamically
by accessing the Data Layer.
Deploying to the Server
To understand what the application is going to look like for the user, deploy what
you have so far constructed to the web server in order to see the pages in a browser.
Note that the JSP pages do not contain any JSP code yet, so for now you could simply
change the extensions to .htm and open them individually in a browser. However,
you will need the web server to compile the JSP code as well as the Java classes for
the Logic Layer, so you might as well start using the web server.
Whether you are running NetBeans IDE's bundled Tomcat or SJSAS, once you have the server
registered in the IDE, the process for deploying an application is the same. If you
need to make any changes to server settings in the IDE, choose Tools > Server
Manager from the main menu to open the Server Manager.
To deploy the application to the server:
- From the Projects window, right-click the project node and choose Deploy Project.
NetBeans IDE automatically starts the server (if it has not already been started),
compiles it and then deploys the project to it. You can see any output generated in
the Output window. The output should complete with a BUILD SUCCESSFUL message.
To check that the application has indeed been deployed to the server, open
the Runtime window (Ctrl+5) and expand the Servers node. The servers that
are registered in the IDE are listed here. For Tomcat, expand Web Applications
to view the IPFWAPCAD application compiled on the server. For SJSAS, expand
Applications > Web Applications to view the application.
- To run the project, back in the Projects window choose Run Project from the
right-click menu of the project node. The index.jsp page opens in the
IDE's default browser.
Tip: If you had simply chosen Run Project to begin with, the application
would have automatically been compiled and deployed to the server prior to opening in a
browser.
Implementing the Data Layer
Before coding in the middle Logic Layer, prepare the Data Layer. This
step is divided into a few subtasks:
- Preparing the Database in NetBeans IDE
- Setting up a JDBC Connection Pool
- Referencing the JDBC Resource from the Application
- Adding the Database Driver's JAR File to the Server
Preparing the Database in NetBeans IDE
After completing the Connecting to a MySQL Database tutorial,
you will already have a connection to a MySQL database registered in the IDE. You
should also have two tables, Counselor and Subject, containing
sample data generated from
ifpwafcad.sql.
Setting up a JDBC Connection Pool
In order to specify how the web server allows an application to communicate with the
database, we need to set up a database connection pool. A database connection
pool is basically a group of reusable connections that a server maintains for a specific
database. Web applications requesting a connection to a database obtain that connection
from the pool. When an application closes a connection, the connection is returned to
the pool.
In order to set up a connection pool on the server, a JDBC resource (also called
a data source) must first be created. A JDBC resource provides applications with a connection
to a database. Depending on whether you're using Tomcat or SJSAS, do the following:
Bundled Tomcat Web Server
- Access the Tomcat Administration tool from the Runtime window by expanding the
Servers > Bundled Tomcat > Web Applications nodes (if necessary, start the
server first by choosing Start from the server node's right-click menu). Then
right-click the /admin node and choose Open in Browser. The login page
opens in the IDE's default browser.
- Enter the user name and password for a user that has been assigned to the
"admin" role. If you need to verify this information, check the
tomcat-users.xml file in the conf folder located in the server's
base directory. You can find out the base directory by opening the Server Manager
(Tools > Server Manager), selecting Tomcat from the left pane and viewing the
entry for the Catalina Base field under the Connection tab:
- Once you are logged in, choose Resources > Data Sources from the left column. In the main
window that displays, choose Create New Data Source from the Data Source Actions drop-down
menu. Enter the following values in the corresponding fields:
- JNDI Name: jdbc/connectionPool
- Data Source URL: jdbc:mysql://localhost:3306/MyNewDatabase
- JDBC Driver Class: com.mysql.jdbc.Driver
- User Name: root
- Password: nbuser
To gain a better understanding of this process, see About Connection
Pools in the IDE's Help Contents (F1).
When you are sure that your entered values match those in the screenshot below, click Save.
Then click Commit Changes, and Log Out.
Sun Java System Application Server
Setting up a JDBC connection pool is slightly easier on SJSAS because it can be done
entirely within NetBeans IDE:
- In the Projects window, right-click the project node and choose New >
File/Folder.... Under Categories select Sun Resources; under File Types select JDBC
Resource. Click Next.
- In the General Attributes pane, select the Create New JDBC Connection Pool option,
then enter jdbc/connectionPool for the JNDI Name below. Leave all other settings at
their defaults and click Next. Then click Next again through the Additional Properties
Pane.
- In the Choose Database Connection pane, note that a connection pool name is automatically
provided based on the JNDI name specified above. Make sure the Extract from Existing
Connection option is selected, and select the database connection you are using
(jdbc:mysql://localhost:3306/MyNewDatabase) from the drop-down list. Click Next.
- In the Add Connection Pool Properties pane, leave all settings at their defaults and
click Finish. The new data source and connection pool are created in the project. You can
verify this by expanding the Server Resources node to view both the data source and
connection pool just created:
Although you just created a data source and connection pool in the project, it is still
necessary to register them with the application server. To do so:
- Choose Register from the right-click menus of both data source and connection pool nodes.
In the JDBC Resource Registration dialog that displays, click Register, then click Close.
- To verify that the JDBC resource and connection pool have indeed been created on the
server, you can switch to the Runtime window and choose View Admin Console from the
right-click menu of the Sun Java System Application Server node. The Administration
login page opens in the IDE's default browser.
- Login to the console (by default, user name and password are: admin,
adminadmin). Select Resources from the left column, then in the main window click
JDBC.
- Now, when you explore both the JDBC Resources and Connection Pools pages, you should
see the newly created data source (jdbc/connectionPool) and connection pool
(connectionPool).
Referencing the JDBC Resource from the Application
You now need to reference the JDBC resource you just created from the web application.
This means you have to access both the web application's general deployment descriptor
(web.xml), as well as the server-specific deployment descriptor for the server
you are using (context.xml for Tomcat; sun-web.xml for SJSAS).
Deployment descriptors are XML documents that contain information describing
how an application should be deployed. For example, they are normally used
to specify location and optional parameters of servlets and JSP files, as well
as implement basic security features for your application. For more information,
see Configuring Web Application Deployment Descriptors in the
IDE's Help Contents (F1).
To reference the JDBC resource in the general deployment descriptor:
- In the Projects window, expand the Web Pages > WEB-INF subfolder
and double-click web.xml. A graphical editor for the file
displays in the Source Editor.
- Click the References tab located along the top of the Source Editor.
Expand the Resource References heading, then click Add.... The Add
Resource Reference dialog opens.
- For Resource Name, enter the JNDI name you gave when adding the data source
to the server above (jdbc/connectionPool). For Description, enter
the data source URL (jdbc:mysql://localhost:3306/MyNewDatabase).
Leave all other fields that are filled by default and click OK. The
new resource is added under the Resource References heading:
To verify that the resource is now added to the web.xml file,
click the XML tab located along the top of the Source Editor you'll see
that the following <resource-ref> tags are now included:
<resource-ref>
<description>jdbc:mysql://localhost:3306/MyNewDatabase [root on Default schema]</description>
<res-ref-name>jdbc/connectionPool</res-ref-name>
<res-type>javax.sql.DataSource</res-type>
<res-auth>Container</res-auth>
<res-sharing-scope>Shareable</res-sharing-scope>
</resource-ref>
Depending on whether you are using Tomcat or SJSAS, perform the following steps
to reference the JDBC resource in the server-specific deployment descriptor:
Bundled Tomcat Web Server
- In the Projects window, expand the Web Pages > META-INF subfolder
and double-click context.xml. The file displays in the Source Editor.
- Add the following <ResourceLink> tag, then save the file (changes
made in bold):
<?xml version="1.0" encoding="UTF-8"?>
<Context path="/IFPWAFCAD">
<ResourceLink global="jdbc/connectionPool" name="jdbc/connectionPool" type="javax.sql.DataSource"/>
</Context>
Sun Java System Application Server
- In the Projects window, expand the Web Pages > WEB-INF subfolder and
double-click sun-web.xml. A graphical editor for the file displays
in the Source Editor.
- Click Edit As XML in the upper right corner of the editor. The file displays
in XML format. Enter the following <resource-ref> tags to
the document, e.g. following the closing </jsp-config> tag:
<resource-ref>
<res-ref-name>jdbc/connectionPool</res-ref-name>
<jndi-name>jdbc/connectionPool</jndi-name>
</resource-ref>
Adding the Database Driver's JAR File to the Server
Adding the database driver's JAR file is another step that is vital to enabling
the server to communicate with your database. You need to locate your database
driver's installation directory. If you are continuing from the
Connecting
to a MySQL Database tutorial, you are using
MySQL Connector/J
which you installed to C:\ on your computer. Copy the
mysql-connector-java-5.0.5-bin.jar file in the driver's root directory
and, depending on whether you are using Tomcat or SJSAS, do the following:
Bundled Tomcat Web Server
- Paste the JAR file into Tomcat's common/lib subfolder. The server is by
default found within the enterprise subfolder of the IDE's installation
directory. If you've already started the server, make sure that you restart it
after pasting in the JAR file, so that the server can then load it.
Sun Java System Application Server
- Locate the installation directory of SJSAS and paste the JAR file into the server's
domains > domain1 > lib > ext subfolder.
For example, if you installed the server to C:\, the path is:
C:\Sun\AppServer\domains\domain1\lib\ext. When you connect to the SJSAS in
NetBeans IDE, you are actually connecting to an instance of the application server. Each
instance runs applications in a unique domain, and so here we need to place the JAR
file in domain1, which is the default domain created upon installing SJSAS.
If you've already started the server, make sure that you restart it after pasting in
the JAR file, so that the server can then load it.
Implementing the Logic Layer
Now that you have prepared the Data Layer, start putting the Java classes in place.
As shown in the figure
above, the Logic Layer is comprised of three classes: SubjectName.java,
SubjectCounselor.java, and AccessDB.java. These classes serve two
functions: they interface with the JSP pages by responding to data requests
(SubjectName.java and SubjectCounselor.java), and they interface
with the database by performing queries based on user-specified information
(AccessDB.java). You can proceed according to these two functions:
- Interfacing the JSP Pages
- Interfacing the Database
Interfacing the JSP Pages
SubjectName.java
SubjectName.java enables the index.jsp page to access the subject names
as they are listed in the Subject table. It does this by allowing AccessDB.java
to set the instance variables id and name using the setter methods, then letting
index.jsp access them using the public getter methods. To set up SubjectName.java,
do the following:
- In the Projects window, right-click the project node and choose New > Java Class.
The New Java Class wizard opens.
- Enter SubjectName for the Class Name text field. You should also create a new package
to contain all Java classes for the project. For Package, type in org. Click Finish.
A template for the new class opens in the Source Editor. In the Projects window,
nodes for the new package and class display from Source Packages:
- Now, in the newly created template in the Source Editor, add the following
to the body of the new SubjectName class, then save (Ctrl+S) the file:
private String id;
private String name;
// create setter methods
public void setId(String id){
this.id = id;
}
public void setName(String name){
this.name = name;
}
// create getter methods
public String getId(){
return id;
}
public String getName(){
return name;
}
SubjectCounselor.java
SubjectCounselor.java enables the response.jsp page to access subject and counselor
details from the database based on the subject_id value that is received from the form in
index.jsp. Like SubjectName.java, the class does this by allowing AccessDB.java
to set all instance variables using the public setter methods, then letting response.jsp
access them using the getter methods. To set up SubjectCounselor.java, do the following:
- In the Projects window, right-click the project node and choose New > Java Class.
The New Java Class wizard opens.
- Enter SubjectCounselor for the Class Name text field. Click Finish.
A template for the new class opens in the Source Editor. In the Projects window,
a new class node displays under the org package we created earlier.
- Now, in the newly created template in the Source Editor, add the following
to the body of the new SubjectCounselor class, then save (Ctrl+S) the file:
private String subjectName;
private String description;
private String counselorID;
private String firstName;
private String nickName;
private String lastName;
private String telephone;
private String email;
private String memberSince;
// create setter methods
public void setSubjectName(String subject) {
this.subjectName = subject;
}
public void setDescription(String desc) {
this.description = desc;
}
public void setCounselorID(String counsId) {
this.counselorID = counsId;
}
public void setFirstName(String first) {
this.firstName = first;
}
public void setNickName(String nick) {
this.nickName = nick;
}
public void setLastName(String last) {
this.lastName = last;
}
public void setTelephone(String phone) {
this.telephone = phone;
}
public void setEmail(String email) {
this.email = email;
}
public void setMemberSince(String mem){
this.memberSince = mem;
}
// create getter methods
public String getSubjectName() {
return subjectName;
}
public String getDescription() {
return description;
}
public String getCounselorName() {
String counselorName = firstName + " "
+ nickName + " " + lastName;
return counselorName;
}
public String getMemberSinceDate() {
return memberSince;
}
public String getTelephone() {
return telephone;
}
public String getEmail() {
return email;
}
Interfacing the Database
AccessDB.java
You can code AccessDB.java by focusing on its individual tasks: it must connect to the
database, send a user-specified query, then store data received from the query. Because you are
coding AccessDB.java in piecemeal fashion, you can download a working
version of AccessDB.java
here. That way, if
you get lost at any point, you can compare your code with the saved version.
Let's begin by creating the file and preparing the code that enables the class to establish a
connection to the connection pool we defined above:
- In the Projects window, right-click the project node and choose New > Java Class.
The New Java Class wizard opens.
- Enter AccessDB for the Class Name text field. Click Finish.
A template for the new class opens in the Source Editor. In the Projects window,
a new class node displays under the org package we created earlier.
- In the newly created template in the Source Editor, right-click the canvas
and choose Enterprise Resources > Use Database. In the Choose Database dialog,
select the JNDI name of the data source that we previously defined (jdbc/connectionPool)
from the Data Source drop-down list. Make sure the Generate Inline Lookup Code option is
selected and click Finish. The following code is generated in the Source Editor:
private DataSource getJdbcConnectionPool() throws NamingException {
Context c=new InitialContext();
return (DataSource) c.lookup("java:comp/env/jdbc/connectionPool");
}
This allows the class to connect to the database using the connection pool that we
defined earlier on the web server. Also note that upon creating the
getJdbcConnectionPool() method, the IDE automatically generates import
statements in the Source Editor for the following classes from the
javax.sql and javax.naming API's:
You can now begin coding the database queries that the class performs for both JSP pages.
Start with the query for index.jsp, which involves retrieving the names and
ID's for all subjects listed in the Subject table:
- In the Source Editor for AccessDB.java, enter the following string variable
to an area just beneath the class declaration:
private String sqlSubjectName = "SELECT subject_id, name FROM Subject";
The SQL query allows you to specify what data you want to pull from the database.
- Add the following getSubjectName() method to the class. This step
creates a connection to the connection pool, then connects to the database and performs
the query. It then loops through the result set returned from the database, and creates
an instance of SubjectName to retain the ID and name for each returned row. Each
instance is added to a list, which is finally returned to the object calling the method:
// get subject names from database
public List getSubjectName() throws Exception{
// connection instance
Connection connection = null;
// instance of SubjectName used to retain retrieved data
SubjectName subName = null;
// list to hold all instances of SubjectName
List list = new ArrayList();
try {
// connect to database
DataSource dataSource = getJdbcConnectionPool();
connection = dataSource.getConnection();
// prepare the SQL query to get subject name and id
PreparedStatement ps = connection.prepareStatement(sqlSubjectName);
// set up the result set to retain all queried data
ResultSet rs = ps.executeQuery();
// now loop through the rows from the generated result set
while(rs.next()){
// declare an instance of SubjectName to match
// returned data with class' instance variables
subName = new SubjectName();
String subject_id = rs.getString("subject_id");
String name = rs.getString("name");
// set the data to the variables
subName.setId(subject_id);
subName.setName(name);
// finally, add the subName instance to the list
list.add(subName);
}
} catch(Exception e){
System.out.println(e.getMessage());
// close the connection so it can be returned to
// the connection pool then return the list
} finally{
connection.close();
return list;
}
}
Upon adding the above code for the getSubjectName() method, notice that
various errors appear in the Source Editor, underlining text in red. When you
place your cursor in the underlined text, a light bulb icon displays to the
immediate left of the line. This indicates that the IDE can offer a possible
solution for the error.
- Fix all errors by either clicking the light bulb icon, or pressing Alt-Enter to
open the corresponding tool tip:
There are 5 errors that you should be concerned with, and they all involve
importing classes (and interfaces) into the project:
- The List interface used as the method's returned object requires
the definition from java.util.
- The ArrayList class is employed to implement List, and
you require this class to hold all instances of SubjectName.
- Connection, PreparedStatement, and ResultSet
are all required from the java.sql API to communicate with the
database.
Choose Add Import from the tool tips for each of these, and note that new import
statements are automatically generated in the Source Editor:
Now you can add the code for the query coming from response.jsp. This follows
the same pattern as what was just demonstrated above for index.jsp, i.e.
you create an appropriate SQL query, then you create a method to query the database
and retain the data. In this case however, you need to create 2 queries: the first
accesses the Subject table and retrieves the row corresponding to the ID
selected by the user from the drop-down menu in index.jsp. The second query
then continues the process using the counselor_idfk from the returned subject
row to match the counselor ID from the Counselor table:
- In the Source Editor for AccessDB.java, enter the following 2 string variables
to an area just beneath the class declaration:
private String sqlSubject = "SELECT * FROM Subject WHERE subject_id = ?";
private String sqlCounselor = "SELECT * FROM Counselor WHERE counselor_id = ?";
- Add the following getSubCounselor() method to the class. This creates a
connection to the connection pool, then connects to the database and performs
the 2 queries, as described above. It then creates an instance of SubjectCounselor
to retain all data that is harvested from the 2 result sets:
// get subject data and counselor data for corresponding subject
public SubjectCounselor getSubCounselor(String subjectID) throws Exception{
// instance of SubjectCounselor used to retain data
SubjectCounselor subCoun = new SubjectCounselor();
// connection instance
Connection connection = null;
try {
// connect to database
DataSource dataSource = getJdbcConnectionPool();
connection = dataSource.getConnection();
// prepare the SQL query to get subject data
PreparedStatement ps = connection.prepareStatement(sqlSubject);
ps.setString(1, subjectID);
ResultSet rs = ps.executeQuery();
// this assumes there is only one row in the result set
rs.next();
// match all returned fields with the below variables
String subjectName = rs.getString("name");
String description = rs.getString("description");
String counselorID = rs.getString("counselor_idfk");
// prepare the SQL query to get counselor data
ps = connection.prepareStatement(sqlCounselor);
ps.setString(1, counselorID);
rs = ps.executeQuery();
// this assumes there is only one row in the result set
rs.next();
// match all returned fields with the below variables
String firstName = rs.getString("first_name");
String nickName = rs.getString("nick_name");
String lastName = rs.getString("last_name");
String telephone = rs.getString("telephone");
String email = rs.getString("email");
String memberSince = rs.getString("member_since");
// finally set all variables to their
// equivalents in the SubjectCounselor instance
subCoun.setSubjectName(subjectName);
subCoun.setDescription(description);
subCoun.setCounselorID(counselorID);
subCoun.setFirstName(firstName);
subCoun.setNickName(nickName);
subCoun.setLastName(lastName);
subCoun.setTelephone(telephone);
subCoun.setEmail(email);
subCoun.setMemberSince(memberSince);
} catch(Exception e){
System.out.println(e.getMessage());
} finally{
// close the connection so it can be returned to the
// connection pool then return the SubjectCounselor instance
connection.close();
return subCoun;
}
}
You have now completed the code required for AccessDB.java, and with it all
the necessary steps required to implement the Logic Layer. You may want to compare
your version of AccessDB.java with the
downloadable
version prior to continuing. The only remaining task is to add the JSP code to
your web pages, allowing you to display the data maintained in the instances of
SubjectName and SubjectCounselor.
If you return to the index.jsp and response.jsp placeholders
created earlier in the tutorial, you can add JSP code to enable pages to generate
content dynamically, i.e. based on user input. To add JSP code, you need
to perform the following 3 steps:
- Adding the JSTL Library to the Project's Compilation Classpath
- Adding taglib Directives to the JSP Pages
- Adding the Code
Adding the JSTL Library to the Project's Compilation Classpath
In order to make better use of the JSP resources at our disposal, you are using tags from
the JavaServer Pages Standard Tag
Library (JSTL) to access and display data taken from the Logic Layer. This library
comes bundled with the IDE. You therefore need to make sure the JSTL library is added
to the web project's compilation classpath, then add the relevent taglib
directives to each of the JSP pages. This allows the server we are using to identify
the tags when it reads them from the JSP pages. Depending on whether you are using
Tomcat or SJSAS, do the following:
Bundled Tomcat Web Server
- In the Projects window, right-click the project's Libraries node and choose Add
Library. Select the JSTL 1.1 library and click Add Library.
- Expand the Libraries node now, and you will see 2 new nodes: one for the JSTL
library's standard.jar file and another for the library's jstl.jar
file:
Sun Java System Application Server
You do not need to take any action to add the JSTL library to the project's
compilation classpath if you are using the Sun Java System Application Server.
This is because the JSTL library is already included in the application server's
library. You can verify this by expanding the Libraries > Sun Java System
Application Server nodes. The appserv-jstl.jar node defines all
standard tags in the JSTL library.
Adding taglib Directives to the JSP Pages
Regardless of what server you are using, you need to add the necessary taglib
directives to JSP pages:
- Open both index.jsp and response.jsp in the Source Editor.
Add the following directive to both pages:
Notice that upon creating these pages this directive was automatically generated
as a comment in the JSP template. You can uncomment the directive by removing
the <%-- --%> tags:
Adding the Code
Finally, add the code to each page. You need to access the list of SubjectNames
(for index.jsp) and the SubjectCounselor instance (for response.jsp),
and then display the data in the HTML.
index.jsp
- Add the following code somewhere to the top of the file, e.g. just above the HTML doctype declaration:
<%-- create an instance of AccessDB --%>
<% AccessDB accessDB = new AccessDB(); %>
<%-- create an instance of List, then retrieve SubjectNames --%>
<% List subjNames = accessDB.getSubjectName(); %>
<%-- make list accessible to page --%>
<% request.setAttribute("subjNames", subjNames); %>
You are declaring and setting a List variable to contain the list of SubjectNames
that is returned from calling the getSubjectName() method of AccessDB.
The setAttribute() method is then applied to the subjNames variable so that it
becomes accessible to the page making the request (as demonstrated below when you insert a loop in
the HTML form to extract the contents of subName).
- Add the following JSP import statements to the page:
<%@ page import="org.*" %>
<%@ page import="java.util.List" %>
The first import allows the page to access all classes contained in the org package
you created in the project. The second defines the List class used for the variable
you just declared.
- In the HTML form, between the <select> tags, replace the <option>
tags with the following code (changes made in bold):
<select name="subject_id" size="1" >
<c:forEach var="subName" items="${requestScope.subjNames}" >
<option value="${subName.id}">${subName.name}</option>
</c:forEach>
</select>
Here is the loop. The forEach tag fetches all values from subName and
extracts the name and ID of each subject, using these to populate the form's drop-down list.
Also, note the c: prefix to the forEach tag. This references the JSTL
core library which you added a taglib directive for above.
- Save changes (Ctrl+S), then redeploy the project to the server and try running it again (F6).
This time, when index.jsp displays in the browser, the subject drop-down list should
contain subject names that were retrieved from the database:
response.jsp
- Add the following code somewhere to the top of the file, e.g. just above the
HTML doctype declaration:
<%-- get the value from the form --%>
<% String subjectID = request.getParameter("subject_id"); %>
<%-- create an instance of AccessDB --%>
<% AccessDB accessDB = new AccessDB(); %>
<%-- create an instance of SubjectCounselor using form value --%>
<% SubjectCounselor subCoun = accessDB.getSubCounselor(subjectID); %>
<%-- create variables to hold data from SubjectCounselor --%>
<% String name = subCoun.getSubjectName(); %>
<% String description = subCoun.getDescription(); %>
<% String counselor = subCoun.getCounselorName(); %>
<% String memberSince = subCoun.getMemberSinceDate(); %>
<% String telephone = subCoun.getTelephone(); %>
<% String email = subCoun.getEmail(); %>
Here you are creating a string variable to hold the subject_id, the value
sent from the form from index.jsp. Then, you declare and set a variable
for SubjectCounselor to hold the data returned from calling the
getSubCounselor() method of AccessDB. Finally, you declare and set
a bunch of string variables to hold the data contained in that SubjectCounselor
instance. These string variables are what you will access directly from the HTML in
the page.
- Add the following JSP import statement to the page:
<%@ page import="org.*" %>
As for index.jsp above, you must allow the page to access all classes
contained in the org package of our project.
- Now, in the HTML, replace all the text previously included in parentheses with
the following JSP calls to display the variables. Changes are shown below in
bold:
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title><%= name %></title>
</head>
<body>
<br>
<h2 align="center"><%= name %></h2>
<br>
<table width="60%" align="center" cellpadding="10">
<tr>
<td valign="top" width="25%"><strong>Description: </strong></td>
<td><em><%= description %></em><br></td>
</tr>
<tr>
<td valign="top"><strong>Counselor: </strong></td>
<td><span style="font-size:large"><strong><%= counselor %></strong></span>
<br><span style="align:center">
<em>member since: <%= memberSince %></em></span></td>
</tr>
<tr>
<td valign="top"><strong>Contact Details: </strong></td>
<br><strong>phone: </strong><%= telephone %></td>
</tr>
</table>
</body>
</html>
Prior to sending this page back to the client browser, the server inserts
all values for JSP variables directly into the HTML. You are therefore now able to
access data directly from the Data Layer in the response page.
- Finally, save changes (Ctrl+S), then redeploy the project to the server and try
running it again (F6). When index.jsp displays in the browser, select a
subject from the drop-down list and click submit. You should now be forwarded to
the response.jsp page, showing details corresponding to your selection:
Next Steps
This concludes the Creating a Simple Web Application in NetBeans IDE Using MySQL tutorial.
This tutorial demonstrated how to create a simple distributed web application that
connects to a MySQL database. It also demonstrated how to construct an application
using a basic three-tier architecture, and used JSP as a means of displaying back-end
data to the client browser.
While building the application itself may have seemed like a lot of work for such small
results, you now have a distributed framework in place, meaning that you are now well
positioned for maintaining application data, as well as modifying or eventually expanding
the application to a larger scale.
For related or more advanced tutorials, see the following resources:
top
