This document describes how to create a simple web application that connects
to a MySQL database server. It also covers some basic ideas and technologies
in web development, such as ™ (JSP), ™ (JSTL), the Java Database Connectivity™
(JDBC) API, and two-tier, client-server architecture. This tutorial is designed for
beginners who have a basic understanding of web development and are looking to
apply their knowledge using a MySQL database.
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 tutorial and assumes that you already have a
connection to a MySQL database created and registered in NetBeans IDE.
The table data that is included in
ifpwafcad.sql.
is also required for this tutorial. This SQL file creates two tables,
Subject and Counselor, then populates them with
sample data. Save this file to a local directory, then open it in
NetBeans IDE and run it on the MySQL database. The database used in
this tutorial is named MyNewDatabase.
Expected duration: 30 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 6.0, Web and Java EE installation
()
- MySQL database server
(download)
Notes:
- The Web and Java EE installation enables you to optionally install the
GlassFish V2 application server and the Apache Tomcat servlet container 6.0.14.
You must install one of these to work through this tutorial.
- The MySQL Connector/J
JDBC Driver, necessary for communication between Java platforms
and the MySQL database protocol, is included in NetBeans IDE 6.0.
- If you are installing NetBeans IDE for the first time, you also need to
download and install the (JDK). The JDK includes the Java Runtime
Environment (JRE), as well as various tools and API's necessary for
development in Java.
Planning the Structure
Simple web applications can be designed using a two-tier architecture,
in which the application communicates directly with a data source using
the Java Database Connectivity API. A user's requests are sent to a database,
and the results are sent directly back to the user. Two-tier architectures
can be easily mapped to a client-server configuration, where a user's browser
serves as the client, and a remote database reachable over the Internet
corresponds to the server.
The application you build in this tutorial involves the creation of two JavaServer
Pages. In each of these pages, you add basic HTML to implement a simple interface,
followed by SQL tags provided by JSTL technology in order to query the database.
Consider the following client-server scenario:
The welcome page (index.jsp) presents the user with a simple HTML form.
When a client requests the index.jsp page, the JSP code contained
therein is parsed, and data from the Subject database table is gathered, added
to the page, and sent to the client. The user makes a selection in the provided
HTML form and submits, which causes the client to make a request for
response.jsp. When response.jsp is parsed, data from both the
Subject and Counselor tables is gathered and inserted into the page. Finally,
the page is returned to the client and the user views data based upon his or
her selection.
Creating a New Project
In order to implement the scenario described above, you 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):
index.jsp
response.jsp
Create a new project in the IDE:
- Start NetBeans IDE and choose File > New Project (Ctrl-Shift-N) from
the main 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.
The IDE creates a project template for the entire application, and opens
an empty JSP page (index.jsp) in the Source Editor. index.jsp
serves as the entry point for the application. The new project is structured
according to Sun
guidelines.
Preparing the Interface
Begin by preparing a simple interface for the two pages. Both index.jsp
and response.jsp implement an HTML table to display data in a structured
fashion. index.jsp also requires an HTML form that includes a drop-down
list.
index.jsp
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.
- In the Source Editor, change the text between the </tt>
tags to <strong>IFPWAFCAD Homepage</strong>.
<br><br>
Also change the text between the <tt><h2></tt> tags to <strong>Welcome
to IFPWAFCAD, the International Former Professional Wrestlers' Association
for Counseling and Development!</strong>.
<br><br>
<strong class="notes">Note: </strong>For further content, you can either
add HTML elements to the page using the IDE's palette, as demonstrated
below, or just copy and paste the code provided in step 8 directly into
your <tt>index.jsp</tt> page.</li><li>Open the IDE's Palette by choosing Window > Palette (Ctrl-Shift-8) from
the main menu. Hover your pointer over the Table icon from the HTML category
and note that the default code for the item displays:
<br><br>
<img src="http://www.netbeans.org/images/articles/60/web/mysql-webapp/palette.png" alt="Palette displaying default code when hovering over an item" title="Palette displaying default code when hovering over an item">
<br><br>
Click the icon, and drag and drop a table into the page in the Source
Editor, to a point just after the <tt><h2></tt> tags. In the
Insert Table dialog that displays, specify the following criteria, then
click OK:
<br><br>
<ul><li><strong>Rows</strong>: 2</li><li><strong>Columns</strong>: 1</li><li><strong>Border Size</strong>: 0</li></ul>
The HTML table code is generated and added to your page.</li><li>Add the following content to the table heading and the cell of
the first table row (new content in <strong>bold</strong>):
<pre class="examplecode"><table border="0"><br> <thead><br> <tr><br> <th><strong>IFPWAFCAD offers expert counseling in a wide range of fields.</strong></th><br> </tr><br> </thead><br> <tbody><br> <tr><br> <td><strong>To view the contact details of an IFPWAFCAD certified former<br> professional wrestler in your area, select a subject below:</strong><br> </td><br> </tr><br> </pre></li><li>For the bottom row of the table, drag and drop an HTML form from the
Palette into the page, directly between the second pair of <tt><td></tt>
tags. In the Action text box, type in <strong>response.jsp</strong>, then
click OK.</li><li>Type in the following text between the <tt><form></tt> tags of the
form you just created:
<pre class="examplecode"><strong>Select a subject:</strong><br> </pre></li><li>Drag and drop a drop-down list from the Palette to a point just beneath the
text you just added. In the Insert Drop-down dialog that displays,
type in <strong>subject_id</strong> for the Name text field, and
click OK. The number of options for the drop-down is currently not
important. Later in the tutorial you will add JSTL tags that dynamically
generate options based on the data gathered from the Subject database
table. Click OK, and note that the code for the drop-down list is
added to the form.</li><li>Add a submit button to the form by dragging a button from the Palette
to a point just after the drop-down list you just added. Enter
<strong>submit</strong> for both the Label and Name text fields,
then click OK.</li><li>To format your code, right-click in the Source Editor, and choose Format
(Alt-Shift-F). Your code is automatically formatted, and should now look
similar to the following:
<pre class="examplecode"><body><br> <h2>Welcome to <strong>IFPWAFCAD</strong>, the International Former<br> Professional Wrestlers' Association for Counseling and Development!<br> </h2><br><br> <table border="0"><br> <thead><br> <tr><br> <th>IFPWAFCAD offers expert counseling in a wide range of fields.</th><br> </tr><br> </thead><br> <tbody><br> <tr><br> <td>To view the contact details of an IFPWAFCAD certified former<br> professional wrestler in your area, select a subject below:<br> </td><br> </tr><br> <tr><br> <td><br> <form action="response.jsp"><br> <strong>Select a subject:</strong><br> <select name="subject_id"><br> <option></option><br> </select><br> <input type="submit" value="submit" name="submit" /><br> </form><br> </td><br> </tr><br> </tbody><br> </table><br><script type="text/javascript" src="/js/jquery.qqFace.js"></script>
</body><br> </pre>
To view this page in a browser, right-click in the Source Editor and choose
Run File (Shift-F6). When you do this, the JSP page is automatically
compiled and deployed to your server. The IDE opens your default browser
to display the page from its deployed location:
<br><br>
<a href="http://www.netbeans.org/images/articles/60/web/mysql-webapp/browser-output.png" title="Click to ENLARGE" onclick="return openwin('../../../images/articles/60/web/mysql-webapp/browser-output.png',758,276)">
<img src="http://www.netbeans.org/images/articles/60/web/mysql-webapp/browser-output-small.png" alt="output of index.jsp displayed in a browser" class="b-all"></a></li></ol>
<h3>response.jsp</h3>
<p>In order to prepare the interface for <tt>response.jsp</tt> you must first
create the file in your application. Note that most of the content that
displays in this page is generated dynamically using JSP technology.
Therefore, in the following steps you add <em>placeholders</em> which you
will later substitute for the JSP code.</p>
<ol><li>Right-click the IFPWAFCAD project node in the Projects window and choose
New > JSP. The New JSP File dialog opens.</li><li>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 <tt>index.jsp</tt> welcome page.</li><li>Accept all other defaults and click Finish. A template for the new
<tt>response.jsp</tt> page is generated and opens in the Source Editor.
A new JSP node also displays under Web Pages in the Projects window:
<br><br>
<img src="http://www.netbeans.org/images/articles/60/web/mysql-webapp/response-jsp-node.png" class="b-all" alt="response.jsp node appears in the Projects window" title="response.jsp node appears in the Projects window"></li><li>In the Source Editor, change the title to <strong>IFPWAFCAD -
{placeholder}</strong>.</li><li>Copy and paste the following HTML table into the body of the page:
<pre class="examplecode"><table border="0"><br> <thead><br> <tr><br> <th colspan="2">{placeholder}</th><br> </tr><br> </thead><br> <tbody><br> <tr><br> <td><strong>Description: </strong></td><br> <td><span style="font-size:smaller; font-style:italic;">{placeholder}</span></td><br> </tr><br> <tr><br> <td><strong>Counselor: </strong></td><br> <td>{placeholder}<br> <br><br> <span style="font-size:smaller; font-style:italic;"><br> member since: {placeholder}</span><br> </td><br> </tr><br> <tr><br> <td><strong>Contact Details: </strong></td><br> <td><strong>email: </strong><br> <a href="mailto:{placeholder}">{placeholder}</a><br> <br><strong>phone: </strong>{placeholder}<br> </td><br> </tr><br> </tbody><br></table><br> </pre>
To view this page in a browser, right-click in the Source Editor
and choose Run File (Shift-F6). The page is compiled, deployed to
your server, and opened in your default browser. Your <tt>response.jsp</tt>
page should display similar to the following:
<br><br>
<img src="http://www.netbeans.org/images/articles/60/web/mysql-webapp/browser-response-page.png" alt="output of response.jsp displayed in a browser" class="b-all" title="output of response.jsp displayed in a browser">
</li></ol>
<br>
<h3>style.css</h3>
<p>Create a simple stylesheet that enhances the interface display:</p>
<ol><li>Right-click the IFPWAFCAD project node in the Projects window and choose
New > Other. In the New File wizard, make sure Web is selected under
Categories, then choose Cascading Style Sheet and click Next.</li><li>Type <strong>style</strong> for CSS File Name and click Finish. The IDE
creates an empty CSS file and places it in the same project location as
<tt>index.jsp</tt> and <tt>response.jsp</tt>. Note that <tt>style.css</tt>
now displays within the project in the Projects window, and the file
opens in the Source Editor.</li><li>In the Source Editor, add the following content to the <tt>style.css</tt>
file:
<pre class="examplecode">body {<br> font-family: Verdana, Arial, sans-serif;<br> font-size: smaller;<br> padding: 50px;<br> color: #555;<br>}<br><br>h2 {<br> text-align: left;<br> letter-spacing: 6px;<br> font-size: 1.4em;<br> color: #be7429;<br> font-weight: normal;<br> width: 450px;<br>}<br><br>table {<br> width: 550px;<br> padding: 10px;<br> background-color: #c5e7e0;<br>}<br><br>th {<br> text-align: left;<br> border-bottom: 1px solid;<br>}<br><br>td {<br> padding: 10px;<br>}<br><br>a:link {<br> color: #be7429;<br> font-weight: normal;<br> text-decoration: none;<br>}<br><br>a:link:hover {<br> color: #be7429;<br> font-weight: normal;<br> text-decoration: underline;<br>}<br> </pre></li><li>Link the stylesheet to <tt>index.jsp</tt> and <tt>response.jsp</tt>:
<br><br>
In both pages, add the following line between the <tt><head></tt>
tags:
<pre class="examplecode"><link rel="stylesheet" type="text/css" href="style.css"><br> </pre>
</li></ol>
<p>This document assumes that you understand how the stylesheet functions,
and how it affects corresponding HTML elements found in <tt>index.jsp</tt>
and <tt>response.jsp</tt>.</p>
<p><span class="tips"></span>When working with CSS in the IDE, you can take
advantage of the CSS Style Builder and CSS Preview. Together, these tools
provide extensive support for creating style rules and viewing elements
when coupled with style attributes.</p>
<p>For example, place your cursor within the <tt>h2</tt> rule in <tt>style.css</tt>,
then open CSS Preview (Window > Other):</p>
<div class="indent">
<img src="http://www.netbeans.org/images/articles/60/web/mysql-webapp/css-preview.png" alt="CSS Preview renders sample text according to style rules" class="b-all" title="CSS Preview renders sample text according to style rules">
</div>
<br>
<p>CSS Preview demonstrates how an element renders in a browser. Also note that
the preview automatically refreshes as you make changes to a rule, providing
a real-time textual representation of style elements from the IDE.</p>
</div>
<br>
<h2>Setting up a Connection Pool</h2>
<p>The most efficient way to implement communication between the server and database is
to set up a database <em>connection pool</em>. Creating a new connection for each
client request can be very time-consuming, especially for applications that continuously
receive a large number of requests. To remedy this, numerous connections are created
and maintained in a connection pool. Any incoming requests that require access to the
application's data layer use an already-created connection from the pool. Likewise,
when a request is completed, the connection is not closed down, but returned to the
pool.</p>
<ol><li></li><li></li><li></li></ol>
<div class="indent">
<h3>Setting up a JNDI Datasource</h3>
<p>Both GlassFish and Tomcat contain Database Connection Pooling (DBCP) libraries that
provide connection pooling functionality in a way that is transparent to you as a
developer. In either case, you need to configure a
<em> Datasource</em> for the
server that creates an interface which your application can use for connection
pooling. Depending on whether you're using GlassFish or Tomcat, do the following:</p>
<h4>GlassFish</h4>
<p>The IDE provides enhanced support for GlassFish, enabling you to specify
resources using a wizard:</p>
<ol><li>In the Projects window, right-click the Server Resources node and
choose New > Other. The New File wizard opens. Under Categories,
select GlassFish. Under File Types, select JDBC Resource. Click Next.</li><li>Under General Attributes, choose the Create New JDBC Connection Pool
option, then in the JNDI Name text field, type in
<strong>jdbc/IFPWAFCAD</strong>. Click Next.</li><li>Click Next again to skip Additional Properties, then in Step 4, type
in <strong>IfpwafcadPool</strong> for JDBC Connection Pool Name.
Make sure the Extract from Existing Connection option is selected,
and choose <tt>jdbc:mysql://localhost:3306/MyNewDatabase</tt> from
the drop-down list. Click Next.</li><li>Accept all default settings for Add Connection Pool Properties, then
click Finish to exit the wizard.</li></ol>
<p>By completing the wizard, you declared a new datasource and connection pool
for the application. In the Projects window, open the newly created Server
Resources > <tt>sun-resources.xml</tt> file and note that <tt><resources></tt>
tags have been added which include the data you specified in the wizard.</p>
<p><span class="tips"></span>To confirm that a new datasource and connection pool
are indeed registered with GlassFish, you can deploy the project to the server,
then locate the resources in IDE's Services window:</p>
<ol><li>In the Projects window, right-click the IFPWAFCAD project node and
choose Undeploy and Deploy.</li><li>Open the Services window (Ctrl-5) and expand the Servers > GlassFish >
Resources > JDBC > JDBC Resources and Connection Pools nodes.
Note that the new datasource and connection pool are now displayed:
<br><br>
<img src="http://www.netbeans.org/images/articles/60/web/mysql-webapp/services-window-glassfish.png" alt="new datasource and connection pool displayed in Services window" title="new datasource and connection pool displayed in Services window">
</li></ol>
<h4>Tomcat</h4>
<p>Configure a JNDI Datasource in Tomcat by adding a declaration for your resource to
the application's <tt>context.xml</tt> file. This is the application's <em>context
container</em>, which enables you to specify application meta-data necessary for
the server in order to deploy and run the application. There are various locations
where you can specify context elements, such as your server's global
<tt>$CATALINA_HOME/conf/context.xml</tt> file. By adding resource declarations to
your application's <tt>context.xml</tt> file however, you limit the resource to that
application, and do not need to configure anything within the server itself. For
more information, see the
.</p>
<ol><li>From the Projects window, open the application's context container
in the Source Editor by double-clicking the Web Pages > META-INF >
<tt>context.xml</tt> file.</li><li>Add the following <tt><Resource></tt> tags (changes below in
<strong>bold</strong>) as a declaration for the JNDI resource. Your
<tt>context.xml</tt> file should now look as follows:
<pre class="examplecode"><?xml version="1.0" encoding="UTF-8"?><br><br><Context path="/IFPWAFCAD"><strong><br> <Resource name="jdbc/IFPWAFCAD" auth="Container" type="javax.sql.DataSource"<br> maxActive="100" maxIdle="30" maxWait="10000"<br> username="root" password="nbuser" driverClassName="com.mysql.jdbc.Driver"<br> url="jdbc:mysql://localhost:3306/MyNewDatabase"/></strong><br></Context><br> </pre>
For a more thorough explanation of JNDI DataSource configuration, including a
description of the parameters used in the above resource declaration, refer to
the Apache Tomcat 6.0
.
</li></ol>
<h3>Referencing the Datasource from the Application</h3>
<p>You need to reference the JNDI resource you just configured from the web application.
To do so, you can create an entry in the application's <em>deployment descriptor</em>
(<tt>web.xml</tt>).</p>
<p>Deployment descriptors are XML-based text files that contain information describing
how an application is to be deployed to a specific environment. For example, they
are normally used to specify application context parameters and behavioral patterns,
security settings, as well as mappings for servlets, filters and listeners.</p>
<p>To reference the JNDI Datasource in the application's deployment descriptor:</p>
<ol><li>In the Projects window, expand the Web Pages > WEB-INF subfolder
and double-click <tt>web.xml</tt>. A graphical editor for the file
displays in the Source Editor.</li><li>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.</li><li>For Resource Name, enter the resource name that you gave when configuring the
JNDI Datasource for the server above (<tt>jdbc/IFPWAFCAD</tt>). For Description,
enter the resource URL (<tt>jdbc:mysql://localhost:3306/MyNewDatabase</tt>).
Note that the default resource type is <tt>javax.sql.DataSource</tt>. Leave all
fields that are provided by default and click OK. The new resource is added
under the Resource References heading:
<br><br>
<a href="http://www.netbeans.org/images/articles/60/web/mysql-webapp/deployment-descriptor.png" title="Click to ENLARGE" onclick="return openwin('../../../images/articles/60/web/mysql-webapp/deployment-descriptor.png',818,269)">
<img src="http://www.netbeans.org/images/articles/60/web/mysql-webapp/deployment-descriptor-small.png" class="b-all" alt="new JDBC resource listed in the deployment descriptor"></a>
<br><br>
To verify that the resource is now added to the <tt>web.xml</tt> file,
click the XML tab located along the top of the Source Editor you'll see
that the following <<tt>resource-ref</tt>> tags are now included:
<pre class="examplecode"><resource-ref><br> <description>jdbc:mysql://localhost:3306/MyNewDatabase</description><br> <res-ref-name>jdbc/IFPWAFCAD</res-ref-name><br> <res-type>javax.sql.DataSource</res-type><br> <res-auth>Container</res-auth><br> <res-sharing-scope>Shareable</res-sharing-scope><br></resource-ref><br> </pre>
</li></ol>
<br>
<h3>Adding the Database Driver's JAR File to the Server</h3>
<p>Adding the database driver's JAR file is another step that is vital to enabling
the server to communicate with your database. Ordinarily, you would need to
locate your database driver's installation directory and copy the
<tt>mysql-connector-java-5.x-bin.jar</tt> file from the driver's root directory
into the library folder of the server you are using. Fortunately, the IDE is
able to detect at deployment whether the JAR file has been added - and if not,
it does so automatically.</p>
<p>In order to demonstrate this, deploy your application to the server you are
using (in the Projects window, choose Undeploy and Deploy from the right-click
menu of the project node) and, depending on the server you are using, do the
following:</p>
<h4>GlassFish</h4>
<ul><li>Locate the GlassFish installation directory and navigate to the <tt>domains</tt>
> <tt>domain1</tt> > <tt>lib</tt> > subfolder. Note that the
<tt>mysql-connector-java-5.x-bin.jar</tt> file has been added here. When you
connect to GlassFish 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 the JAR file is located in <tt>domain1</tt>, which is the default
domain created upon installing GlassFish.</li></ul>
<h4>Tomcat</h4>
<ul><li>Open the Servers window (Tools > Servers from main menu). Make sure Apache
Tomcat is selected in the left panel, then click the Classes tab. Note that
a JAR file for a MySQL driver is added to the library:
<br><br>
<a href="http://www.netbeans.org/images/articles/60/web/mysql-webapp/tomcat-jar.png" title="Click to ENLARGE" onclick="return openwin('../../../images/articles/60/web/mysql-webapp/tomcat-jar.png',804,560)">
<img src="http://www.netbeans.org/images/articles/60/web/mysql-webapp/tomcat-jar-small.png" alt="Servers window showing JAR files contained in Tomcat lib subfolder"></a>
<br><br>
<strong class="notes">Note: </strong> The files listed in the Classes tab
correspond to the Tomcat installation directory's <tt>lib</tt> subfolder.</li></ul>
</div>
<br>
<h2>Adding Dynamic Logic</h2>
<p>If you return to the <tt>index.jsp</tt> and <tt>response.jsp</tt> placeholders
created earlier in the tutorial, you can add JSP and JSTL code to enable pages
to generate content <em>dynamically</em>, i.e. based on user input. To do so,
you need to perform the following 3 steps:</p>
<ol><li></li><li></li><li></li></ol>
<div class="indent">
<h3>Adding the JSTL Library to the Project's Classpath</h3>
<p>In order to make better use of the JSP resources at your disposal, you can make use
of the (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 <tt>taglib</tt>
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
GlassFish or Tomcat, do the following:</p>
<h4>GlassFish</h4>
<p>Do nothing! GlassFish includes the JSTL library in its own library. You can verify
this by expanding the Libraries > GlassFish node. The <tt>appserv-jstl.jar</tt>
file defines all standard tags in the JSTL library.</p>
<h3>Adding <tt>taglib</tt> Directives to the JSP Pages</h3>
<p>Regardless of what server you are using, you need to add the necessary <tt>taglib</tt>
directives to JSP pages:</p>
<ul><li>Open both <tt>index.jsp</tt> and <tt>response.jsp</tt> in the Source Editor.
Add the following directives to the top of both pages:
<pre class="examplecode"><%@taglib uri="" prefix="c"%><br><%@taglib uri="" prefix="sql"%><br> </pre>
</li></ul>
<h4>Tomcat</h4>
<ol><li>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.</li><li>Expand the Libraries node and note that two new JAR files have been added:
the JSTL library's <tt>standard.jar</tt>, and the <tt>jstl.jar</tt>.
You can further expand the <tt>standard.jar</tt> file to view JSTL's four
basic areas of functionality:
<br>
<table>
<tbody><tr>
<td class="f-page-cell"><img src="http://www.netbeans.org/images/articles/60/web/mysql-webapp/jstl-library.png" alt="JSTL library added to Libraries node in Projects window"></td>
<td class="cell">The JSTL library includes four basic areas of functionality:
<br><br>
<ul><li><tt></tt>:
common, structural tasks such as iteration and conditionals</li><li><tt></tt>:
internationalization and locale-sensitive formatting tags</li><li><tt></tt>:
SQL tags</li><li><tt></tt>:
manipulation of XML documents</li></ul>
</td>
</tr>
</tbody></table>
</li></ol>
<h3>Adding JSP and JSTL Code</h3>
<p>Finally, add the code to each page. Both pages require that you implement an
SQL query that utilizes the JSTL <sql:query> tags and the datasource
created earlier in the tutorial.</p>
<h4>index.jsp</h4>
<p>In order to dynamically display the contents of the form in <tt>index.jsp</tt>,
you need to access all <tt>name</tt>s from the Subject database table:</p>
<ol><li>Add the following SQL query beneath the <tt>taglib</tt> directives you
added in the previous step:
<pre class="examplecode"><sql:query var="subjects" dataSource="jdbc/IFPWAFCAD"><br> SELECT subject_id as id, name FROM Subject<br></sql:query><br> </pre>
The JSTL <tt></tt>
tags enable you to use SQL query language directly in a JSP page. A
<tt>resultset</tt> is generated from the query, and the acquired data
can then be inserted into the page using an iterator tag
(<tt></tt>)
from the JSTL <tt>core</tt> library.</li><li>Replace the empty <tt><option></tt> tags in the HTML form with the
following iterator (changes in <strong>bold</strong>):
<pre class="examplecode"><select name="subject_id"><strong><br> <c:forEach var="subject" items="${subjects.rows}"><br> <option value="${subject.id}">${subject.name}</option><br> </c:forEach></strong><br></select><br> </pre>
<br>
The <tt>forEach</tt> tag loops through all <tt>id</tt> and <tt>name</tt> values from
the generated <tt>resultset</tt>, and inserts each pair into the HTML <tt>option</tt>
tags. In this manner, the form's drop-down list is populated with data.</li><li>Save changes (Ctrl+S), then right-click in the Source Editor and choose Run File
(Shift-F6). The file is compiled and deployed to the server, and <tt>index.jsp</tt>
renders in the browser. The drop-down list now contains subject names that were
retrieved from the database:
<br><br>
<img src="http://www.netbeans.org/images/articles/60/web/mysql-webapp/drop-down-list.png" alt="subject drop-down list displayed in a browser window">
</li></ol>
<h4>response.jsp</h4>
<p>For <tt>response.jsp</tt>, you need to access data from both the <tt>Subject</tt>
and <tt>Counselor</tt> tables that correspond to the submitted <tt>id</tt> provided
by the user. This is accomplished using an SQL query and the datasource created
earlier in the tutorial:</p>
<ol><li>Add the following SQL query beneath the <tt>taglib</tt> directives you
added in the previous step:
<pre class="examplecode"><sql:query var="counsSubjRs" maxRows="1" dataSource="jdbc/IFPWAFCAD"><br> SELECT s.name, s.description,<br> CONCAT(c.first_name," ",c.nick_name ," ",c.last_name) as counselor,<br> c.member_since as memberSince, c.telephone, c.email<br> FROM Subject as s, Counselor as c<br> WHERE c.counselor_id = s.counselor_idfk<br> AND s.subject_id = ? <sql:param value="${param.subject_id}"/><br></sql:query><br> </pre>
A <tt>resultset</tt> named <tt>counsSubjRs</tt> is generated from the above
SQL query, which contains all data associated with the <tt>subject_id</tt>
submitted by the user. Note that data from the <tt>Counselor</tt> table is
acquired by mapping <tt>counselor_idfk</tt> from <tt>Subject</tt> to
<tt>counselor_id</tt> from <tt>Counselor</tt>.</li><li>Add the following declaration beneath the SQL query from the previous step:
<pre class="examplecode"><c:set var="counsSubj" scope="request" value="${counsSubjRs.rows[0]}"/><br> </pre>
The <tt></tt>
tag enables you to set the <tt>resultset</tt> to a scoped variable, so that
you can retrieve its contents at a later stage.
</li><li>In the HTML, replace all <em>placeholders</em> with JSP code that allows you
to retrieve and display the data held in the <tt>counsSubj</tt> <tt>resultset</tt>
(Changes below shown in <strong>bold</strong>):
<pre class="examplecode"><html><br> <head><br> <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/><br> <link rel="stylesheet" type="text/css" href="style.css"><br> <title><strong>${counsSubj.name}</strong>
${counsSubj.name} |
|---|
Description: |
${counsSubj.description} |
Counselor: |
${counsSubj.counselor}
member since: ${counsSubj.memberSince} |
Contact Details: |
email:
${counsSubj.email}
phone: ${counsSubj.telephone} |
