| <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> |
| |
| <html> |
| <head> |
| <meta http-equiv="content-type" content="text/html; charset=UTF-8"> |
| <meta name="description" content="A tutorial describing how to create a simple, |
| two-tiered web application that connects to a MySQL database in NetBeans IDE"> |
| |
| <meta name="keywords" content="NetBeans, IDE, integrated development environment, |
| MySQL, SQL, structured query language, multithreaded, multi-user database |
| management system, DBMS, open source, JDBC, JSP, JavaServer Pages"> |
| |
| <link rel="stylesheet" type="text/css" href="../../../netbeans.css"> |
| <link rel="stylesheet" type="text/css" href="../../../lytebox.css" media="screen"> |
| |
| <script type="text/javascript" src="../../../images_www/js/lytebox-compressed.js"></script> |
| <script src="../../../images_www/js/listCollapse.js" type="text/javascript"></script> |
| |
| <title>Creating a Simple Web Application Using a MySQL Database - NetBeans IDE Tutorial</title> |
| </head> |
| |
| <body> |
| |
| <!-- Copyright (c) 2009, 2012, Oracle and/or its affiliates. All rights reserved. --> |
| |
| <h1>Creating a Simple Web Application Using a MySQL Database</h1> |
| |
| <div class="articledate">Written by Troy Giunipero</div> |
| |
| <p>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 <a href="http://www.oracle.com/technetwork/java/overview-138580.html">JavaServer Pages</a> (JSP), |
| <a href="http://www.oracle.com/technetwork/java/index-jsp-135995.html">JavaServer Pages Standard Tag Library</a> |
| (JSTL), the <a href="http://docs.oracle.com/javase/tutorial/jdbc/overview/index.html">Java Database |
| Connectivity</a> (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.</p> |
| |
| <p><a href="http://www.mysql.com">MySQL</a> is a popular open source database management |
| system commonly used in web applications due to its speed, flexibility and reliability. |
| MySQL employs SQL, or <em>Structured Query Language</em>, for accessing and processing |
| data contained in databases.</p> |
| |
| <p>This tutorial is a continuation from the <a href="../ide/mysql.html">Connecting to a |
| MySQL Database</a> tutorial and assumes that you have already created a MySQL database |
| named <code>MyNewDatabase</code>, which you have registered a connection for in the |
| NetBeans IDE. The table data used in that tutorial is contained in |
| <a href="https://netbeans.org/projects/samples/downloads/download/Samples%252FJava%2520Web%252Fifpwafcad.sql">ifpwafcad.sql</a> |
| and is also required for this tutorial. This SQL file creates two tables, <code>Subject</code> |
| and <code>Counselor</code>, then populates them with sample data. If needed, save this |
| file to your computer, then open it in the NetBeans IDE and run it on the MySQL database |
| named <code>MyNewDatabase</code>.</p> |
| |
| <p><strong>Contents</strong></p> |
| |
| <img src="../../../images_www/articles/73/netbeans-stamp-80-74-73.png" class="stamp" alt="Content on this page applies to NetBeans IDE 7.2, 7.3, 7.4 and 8.0" title="Content on this page applies to the NetBeans IDE 7.2, 7.3, 7.4 and 8.0" > |
| |
| <ul id="collapsableList"> |
| <li><a href="#planStructure">Planning the Structure</a></li> |
| <li><a href="#createProject">Creating a New Project</a></li> |
| <li><a href="#prepareInterface">Preparing the Web Interface</a> |
| |
| <ul> |
| <li><a href="#welcomePage">Setting up the welcome page</a></li> |
| <li><a href="#responsePage">Creating the response page</a></li> |
| <li><a href="#stylesheet">Creating a stylesheet</a></li> |
| </ul></li> |
| |
| <li><a href="#prepareCommunication">Preparing Communication between the Application and Database</a> |
| <ul> |
| <li><a href="#setUpJDBC">Setting up a JDBC data source and connection pool</a></li> |
| <li><a href="#referenceDataSource">Referencing the data source from the application</a></li> |
| <li><a href="#addJar">Adding the database driver's JAR file to the server</a></li> |
| </ul></li> |
| <li><a href="#addLogic">Adding Dynamic Logic</a> |
| <ul> |
| <li><a href="#addJSTL">Adding the JSTL library to the project's classpath</a></li> |
| <li><a href="#addCode">Implementing JSTL code</a></li> |
| </ul></li> |
| <li><a href="#run">Running the Completed Application</a></li> |
| <li><a href="#troubleshoot">Troubleshooting</a> |
| |
| <ul> |
| <li><a href="#access">Do database resources exist?</a></li> |
| <li><a href="#datasource">Do the connection pool and data source exist on the server?</a></li> |
| <li><a href="#driver">Is the MySQL Connector/J driver accessible to the GlassFish server?</a></li> |
| <li><a href="#password">Is the database password-protected?</a></li> |
| <li><a href="#ping">Are the connection pool properties correctly set?</a></li> |
| </ul></li> |
| |
| <li><a href="#seeAlso">See Also</a></li> |
| </ul> |
| |
| <p><strong>To follow this tutorial, you need the following software and resources.</strong></p> |
| |
| <table> |
| <tbody> |
| <tr> |
| <th class="tblheader" scope="col">Software or Resource</th> |
| <th class="tblheader" scope="col">Version Required</th> |
| </tr> |
| <tr> |
| <td class="tbltd1"><a href="https://netbeans.org/downloads/index.html">NetBeans IDE</a></td> |
| <td class="tbltd1">7.2, 7.3, 7.4, 8.0, Java EE bundle</td> |
| </tr> |
| <tr> |
| <td class="tbltd1"><a href="http://www.oracle.com/technetwork/java/javase/downloads/index.html">Java Development Kit (JDK)</a></td> |
| <td class="tbltd1">version 7 or 8</td> |
| </tr> |
| <tr> |
| <td class="tbltd1"><a href="http://dev.mysql.com/downloads/mysql/">MySQL database server</a></td> |
| <td class="tbltd1">5.x</td> |
| </tr> |
| <tr> |
| <td class="tbltd1">MySQL Connector/J JDBC Driver</td> |
| <td class="tbltd1">version 5.x</td> |
| </tr> |
| <tr> |
| <td class="tbltd1">GlassFish Server Open Source Edition</td> |
| <td class="tbltd1">3.x or 4.x</td> |
| </tr> |
| </tbody> |
| </table> |
| |
| <p><strong class="notes">Notes:</strong></p> |
| |
| <ul> |
| <li>The Java download bundle of the NetBeans IDE enables you to install the GlassFish server. |
| You require the GlassFish server to work through this tutorial.</li> |
| |
| <li>The <a href="http://dev.mysql.com/downloads/connector/j/">MySQL Connector/J |
| JDBC Driver</a>, necessary for communication between Java platforms and the |
| MySQL database protocol, is included in the NetBeans IDE.</li> |
| |
| <li>If you need to compare your project with a working solution, you can |
| <a href="https://netbeans.org/projects/samples/downloads/download/Samples%252FJava%2520Web%252FIFPWAFCAD.zip">download |
| the sample application</a>.</li> |
| </ul> |
| |
| <br> |
| <h2 id="planStructure">Planning the Structure</h2> |
| |
| <p>Simple web applications can be designed using a <em>two-tier architecture</em>, |
| in which a client communicates directly with a server. In this tutorial, a |
| Java web application communicates directly with a MySQL database using the |
| Java Database Connectivity API. Essentially, it is the |
| <a href="http://dev.mysql.com/downloads/connector/j/">MySQL Connector/J JDBC |
| Driver</a> that enables communication between the Java code understood by the |
| application server (the GlassFish server), and any content in SQL, the language understood |
| by the database server (MySQL).</p> |
| |
| <p>The application you build in this tutorial involves the creation of two JSP pages. |
| In each of these pages you use HTML and CSS to implement a simple interface, and |
| apply JSTL technology to perform the logic that directly queries the database |
| and inserts the retrieved data into the two pages. The two database tables, |
| <code>Subject</code> and <code>Counselor</code>, are contained in the MySQL |
| database, <code>MyNewDatabase</code>, which you create by completing the |
| <a href="../ide/mysql.html">Connecting to a MySQL Database</a> tutorial. |
| Consider the following two-tier scenario.</p> |
| |
| <div class="indent"> |
| <a name="appStructure"></a> |
| <img src="../../../images_www/articles/72/web/mysql-webapp/ifpwafcad-structure.png" |
| alt="Diagram depicting the structure of a two-tier web application" |
| title="Sample structure of a two-tier web application" class="margin-around"> |
| </div> |
| |
| <p>The welcome page (<code>index.jsp</code>) presents the user with a simple HTML form. |
| When a browser requests <code>index.jsp</code>, the JSTL code within the page initiates |
| a query on <code>MyNewDatabase</code>. It retrieves data from the <code>Subject</code> |
| database table, and inserts it into to the page before it is sent to the browser. When |
| the user submits his or her selection in the welcome page's HTML form, the submit |
| initiates a request for the response page (<code>response.jsp</code>). Again, the JSTL |
| code within the page initiates a query on <code>MyNewDatabase</code>. This time, it |
| retrieves data from both the <code>Subject</code> and <code>Counselor</code> tables |
| and inserts it into to the page, allowing the user to view data based upon his or her |
| selection when the page is returned to the browser.</p> |
| |
| <p>In order to implement the scenario described above, you develop a simple application |
| for a fictitious organization named IFPWAFCAD, The International Former Professional |
| Wrestlers' Association for Counseling and Development.</p> |
| |
| <div class="indent"> |
| <h4 class="cell">index.jsp</h4> |
| |
| <img src="../../../images_www/articles/72/web/mysql-webapp/index-page.png" |
| alt="index.jsp displayed in a browser" class="b-all margin-around" |
| title="index.jsp displayed in a browser"> |
| |
| <br><br> |
| <h4 class="cell">response.jsp</h4> |
| |
| <img src="../../../images_www/articles/72/web/mysql-webapp/response-page.png" |
| alt="response.jsp displayed in a browser" class="b-all margin-around" |
| title="response.jsp displayed in a browser"> |
| </div> |
| |
| |
| <br> |
| <h2 id="createProject">Creating a New Project</h2> |
| |
| <p>Begin by creating a new Java web project in the IDE:</p> |
| |
| <ol> |
| <li>Choose File > New Project (Ctrl-Shift-N; ⌘-Shift-N on Mac) from |
| the main menu. Select the Java Web category, then select Web Application. Click Next. |
| |
| <br> |
| <img src="../../../images_www/articles/72/web/mysql-webapp/new-project.png" |
| alt="New Project wizard" |
| title="Use the IDE's New Project wizard to create a new project" |
| class="margin-around b-all"> |
| |
| <p>The New Project wizard allows you to create an empty web application in |
| a standard IDE project. The standard project uses an IDE-generated Ant |
| build script to compile, deploy, and run the application.</p></li> |
| |
| <li>In Project Name, enter <strong>IFPWAFCAD</strong>. Also, specify the location |
| for the project on your computer. (By default, the IDE places projects in a |
| <code>NetBeansProjects</code> folder located in your home directory.) Click Next.</li> |
| |
| <li>In the Server and Settings panel, specify the GlassFish server as server which will be |
| used to run the application. |
| |
| <p class="notes"><strong>Note. </strong>The GlassFish server displays in the Server drop-down |
| field if you installed the Java version of the <a href="https://netbeans.org/downloads/index.html">NetBeans IDE</a>. |
| Because the GlassFish server is included in the download, it is automatically |
| registered with the IDE. If you want to use a different server for this project, |
| click the Add button located next to the Server drop-down field, and register |
| a different server with the IDE. However, working with servers other than the GlassFish server |
| is beyond the scope of this tutorial.</p></li> |
| |
| <li>In the Java EE Version field, select <strong>Java EE 5</strong>. |
| |
| <br> |
| <img src="../../../images_www/articles/74/web/mysql-webapp/server-settings.png" |
| alt="New Web Application wizard - Server and Settings" |
| title="Specify server settings in the New Web Application wizard" |
| class="margin-around b-all"> |
| |
| <p> |
| Java EE 6 and Java EE 7 web projects do not require the use of the <code>web.xml</code> deployment |
| descriptor, and the NetBeans project template does not include the <code>web.xml</code> |
| file in Java EE 6 and Java EE 7 projects. However, this tutorial demonstrates how to declare a |
| data source in the deployment descriptor, and it does not rely on any features specific |
| to Java EE 6 or Java EE 7, so you can set the project version to Java EE 5.</p> |
| |
| |
| <p class="notes"><strong>Note.</strong> You could equally set the project version to Java |
| EE 6 or Java EE 7 and then create a <code>web.xml</code> deployment descriptor. (From the New File wizard, |
| select the Web category, then Standard Deployment Descriptor.)</p></li> |
| |
| <li>Click Finish. The IDE creates a project template for the entire application, and opens |
| an empty JSP page (<code>index.jsp</code>) in the editor. The <code>index.jsp</code> file |
| serves as the welcome page for the application.</li> |
| </ol> |
| |
| |
| <h2 id="prepareInterface">Preparing the Web Interface</h2> |
| |
| <p>Begin by preparing the welcome (<code>index.jsp</code>) and response (<code>response.jsp</code>) |
| pages. The welcome page implements an HTML form that is used to capture user data. Both pages |
| implement an HTML table to display data in a structured fashion. In this section, you also |
| create a stylesheet that enhances the appearance of both pages.</p> |
| |
| <ul> |
| <li><a href="#welcomePage">Setting up the welcome page</a></li> |
| <li><a href="#responsePage">Creating the response page</a></li> |
| <li><a href="#stylesheet">Creating a stylesheet</a></li> |
| </ul> |
| |
| <div class="indent"> |
| <h3 id="welcomePage">Setting up the welcome page</h3> |
| |
| <p>Confirm that <code>index.jsp</code> is open in the editor. If the file is not already |
| open, double-click <code>index.jsp</code> under the Web Pages node in the |
| IFPWAFCAD project in the Projects window.</p> |
| |
| <ol> |
| <li>In the editor, change the text between the <code><title></code> |
| tags to: <code>IFPWAFCAD Homepage</code>.</li> |
| |
| <li>Change the text between the <code><h1></code> tags to: <code>Welcome |
| to IFPWAFCAD, the International Former Professional Wrestlers' Association |
| for Counseling and Development!</code>.</li> |
| |
| <li>Open the IDE's Palette by choosing Window > Palette (Ctrl-Shift-8; |
| ⌘-Shift-8 on Mac) from the main menu. Hover your pointer over the |
| Table icon from the HTML category and note that the default code snippet |
| for the item displays. |
| |
| <br> |
| <img src="../../../images_www/articles/72/web/mysql-webapp/palette.png" |
| alt="Palette displaying code snippet when hovering over an item" |
| title="Palette displays code snippet when hovering over an item" |
| class="margin-around b-all"> |
| |
| <br> |
| <span class="tips">You can configure the Palette to your liking - right-click |
| in the Palette and choose Show Big Icons and Hide Item Names to have |
| it display as in the image above.</span></li> |
| |
| <li>Place your cursor at a point just after the <code><h1></code> tags. |
| (This is where you want to implement the new HTML table.) Then, in the Palette, |
| double-click the Table icon.</li> |
| |
| <li>In the Insert Table dialog that displays, specify the following values |
| 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 shown in <strong>bold</strong>): |
| |
| <pre class="examplecode"> |
| <table border="0"> |
| <thead> |
| <tr> |
| <th><strong>IFPWAFCAD offers expert counseling in a wide range of fields.</strong></th> |
| </tr> |
| </thead> |
| <tbody> |
| <tr> |
| <td><strong>To view the contact details of an IFPWAFCAD certified former |
| professional wrestler in your area, select a subject below:</strong></td> |
| </tr></pre></li> |
| |
| <li>For the bottom row of the table, insert an HTML form. To do so, place your |
| cursor between the second pair of <code><td></code> tags, |
| then double-click the HTML form ( |
| <img src="../../../images_www/articles/72/web/mysql-webapp/html-form-icon.png" alt="HTML form icon"> |
| ) icon in the Palette. In the Insert Form dialog, type in <code>response.jsp</code> |
| in the Action text field, then click OK. |
| |
| <br> |
| <img src="../../../images_www/articles/72/web/mysql-webapp/insert-form.png" |
| title="Specify form settings in the Insert Form dialog" alt="Insert Form dialog" |
| class="margin-around b-all"></li> |
| |
| <li>Type in the following content between the <code><form></code> tags |
| (new content shown in <strong>bold</strong>): |
| |
| <pre class="examplecode"> |
| <tr> |
| <td> |
| <form action="response.jsp"> |
| <strong><strong>Select a subject:</strong></strong> |
| </form> |
| </td> |
| </tr></pre> |
| |
| </li> |
| |
| |
| <li>Press Enter to add an empty line after the content you just added and then |
| double-click Drop-down List in the Palette to open the Insert Drop-down dialog box.</li> |
| |
| <li>Type <code>subject_id</code> for |
| the Name text field in the Insert Drop-down dialog and click OK. |
| Note that the code snippet for the drop-down list is added to the form. |
| |
| <p> |
| 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.</p></li> |
| |
| <li>Add a submit button item ( |
| <img src="../../../images_www/articles/72/web/mysql-webapp/submit-button.png" alt="Submit button icon"> |
| ) to a point just after the drop-down list you just added. You can either use |
| the Palette to do this, or invoke the editor's code completion as illustrated in |
| the previous step. In the Insert Button dialog, enter <code>submit</code> for |
| both the Label and Name text fields, then click OK.</li> |
| |
| <li>To format your code, right-click in the editor and choose Format |
| (Alt-Shift-F; Ctrl-Shift-F on Mac). Your code is automatically formatted, |
| and should now look similar to the following: |
| |
| <pre class="examplecode"> |
| <body> |
| <h2>Welcome to <strong>IFPWAFCAD</strong>, the International Former |
| Professional Wrestlers' Association for Counseling and Development! |
| </h2> |
| |
| <table border="0"> |
| <thead> |
| <tr> |
| <th>IFPWAFCAD offers expert counseling in a wide range of fields.</th> |
| </tr> |
| </thead> |
| <tbody> |
| <tr> |
| <td>To view the contact details of an IFPWAFCAD certified former |
| professional wrestler in your area, select a subject below:</td> |
| </tr> |
| <tr> |
| <td> |
| <form action="response.jsp"> |
| <strong>Select a subject:</strong> |
| <select name="subject_id"> |
| <option></option> |
| </select> |
| <input type="submit" value="submit" name="submit" /> |
| </form> |
| </td> |
| </tr> |
| </tbody> |
| </table> |
| </body></pre> |
| |
| <p>To view this page in a browser, right-click in the editor and choose |
| Run File (Shift-F6; Fn-Shift-F6 on Mac). 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.</p> |
| |
| <img src="../../../images_www/articles/72/web/mysql-webapp/browser-output.png" |
| title="index.jsp displays in a browser" alt="index.jsp displayed in a browser" |
| class="margin-around b-all"></li> |
| </ol> |
| |
| |
| <h3 id="responsePage">Creating the response page</h3> |
| |
| <p>In order to prepare the interface for <code>response.jsp</code> you must first |
| create the file in your project. 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 <code>response</code>. Note that Web Pages |
| is currently selected for the Location field, meaning that the file will |
| be created in the project's <code>web</code> directory. This is the same |
| location as where the <code>index.jsp</code> welcome page resides.</li> |
| |
| <li>Accept any other default settings and click Finish. A template for the |
| new <code>response.jsp</code> page is generated and opens in the editor. |
| A new JSP node also displays under Web Pages in the Projects window. |
| |
| <br> |
| <img src="../../../images_www/articles/72/web/mysql-webapp/response-jsp-node.png" |
| class="margin-around b-all" alt="response.jsp node appears in the Projects window" |
| title="response.jsp node appears in the Projects window"></li> |
| |
| <li>In the editor, change the title to: <code>IFPWAFCAD - {placeholder}</code>.</li> |
| |
| <li>Remove the <code><h1>Hello World!</h1></code> line between the |
| <code><body></code> tags, then copy and paste the following HTML table |
| into the body of the page: |
| |
| <pre class="examplecode"> |
| <table border="0"> |
| <thead> |
| <tr> |
| <th colspan="2">{placeholder}</th> |
| </tr> |
| </thead> |
| <tbody> |
| <tr> |
| <td><strong>Description: </strong></td> |
| <td><span style="font-size:smaller; font-style:italic;">{placeholder}</span></td> |
| </tr> |
| <tr> |
| <td><strong>Counselor: </strong></td> |
| <td>{placeholder} |
| <br> |
| <span style="font-size:smaller; font-style:italic;"> |
| member since: {placeholder}</span> |
| </td> |
| </tr> |
| <tr> |
| <td><strong>Contact Details: </strong></td> |
| <td><strong>email: </strong> |
| <a href="mailto:{placeholder}">{placeholder}</a> |
| <br><strong>phone: </strong>{placeholder} |
| </td> |
| </tr> |
| </tbody> |
| </table></pre> |
| |
| <p>To view this page in a browser, right-click in the editor and choose Run File |
| (Shift-F6; Fn-Shift-F6 on Mac). The page compiles, is deployed to the GlassFish server, |
| and opens in your default browser.</p> |
| |
| <img src="../../../images_www/articles/72/web/mysql-webapp/browser-response.png" |
| title="response.jsp displays in a browser" alt="response.jsp displayed in a browser" |
| class="margin-around b-all"></li> |
| </ol> |
| |
| |
| <h3 id="stylesheet">Creating a stylesheet</h3> |
| |
| <p>Create a simple stylesheet that enhances the display of the web interface. This |
| tutorial assumes that you understand how style rules function, and how they affect |
| corresponding HTML elements found in <code>index.jsp</code> and <code>response.jsp</code>.</p> |
| |
| <ol> |
| <li>Open the New File wizard by pressing the New File ( |
| <img src="../../../images_www/articles/72/web/mysql-webapp/new-file-btn.png" alt="New File button"> |
| ) button in the IDE's main toolbar. Select the Web category, then select |
| Cascading Style Sheet and click Next.</li> |
| |
| <li>Type <code>style</code> for CSS File Name and click Finish. The IDE creates |
| an empty CSS file and places it in the same project location as |
| <code>index.jsp</code> and <code>response.jsp</code>. Note that a node for |
| <code>style.css</code> now displays within the project in the Projects |
| window, and the file opens in the editor.</li> |
| |
| <li>In the editor, add the following content to the <code>style.css</code> file: |
| |
| <pre class="examplecode"> |
| body { |
| font-family: Verdana, Arial, sans-serif; |
| font-size: smaller; |
| padding: 50px; |
| color: #555; |
| } |
| |
| h1 { |
| text-align: left; |
| letter-spacing: 6px; |
| font-size: 1.4em; |
| color: #be7429; |
| font-weight: normal; |
| width: 450px; |
| } |
| |
| table { |
| width: 580px; |
| padding: 10px; |
| background-color: #c5e7e0; |
| } |
| |
| th { |
| text-align: left; |
| border-bottom: 1px solid; |
| } |
| |
| td { |
| padding: 10px; |
| } |
| |
| a:link { |
| color: #be7429; |
| font-weight: normal; |
| text-decoration: none; |
| } |
| |
| a:link:hover { |
| color: #be7429; |
| font-weight: normal; |
| text-decoration: underline; |
| }</pre></li> |
| |
| <li>Link the stylesheet to <code>index.jsp</code> and <code>response.jsp</code>. |
| In both pages, add the following line between the <code><head></code> |
| tags: |
| |
| <pre class="examplecode"><link rel="stylesheet" type="text/css" href="style.css"></pre> |
| |
| <span class="tips">To quickly navigate between files that are open in the editor, |
| press Ctrl-Tab, then select the file you are wanting.</span></li> |
| </ol> |
| </div> |
| |
| |
| <br> |
| <h2 id="prepareCommunication">Preparing Communication between the Application and Database</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> |
| |
| <p>After preparing the data source and connection pool for the server, you then need to |
| instruct the application to use the data source. This is typically done by creating |
| an entry in the application's <code>web.xml</code> deployment descriptor. Finally, |
| you need to ensure that the database driver (MySQL Connector/J JDBC Driver) is accessible |
| to the server.</p> |
| |
| <div class="indent"> |
| <div class="feedback-box float-left" style="width: 723px;"> |
| <p><strong class="notes">Important: </strong> From this point forward, you need you |
| ensure that you have a MySQL database instance named <code>MyNewDatabase</code> |
| set up that contains sample data provided in |
| <a href="https://netbeans.org/projects/samples/downloads/download/Samples%252FJava%2520Web%252Fifpwafcad.sql">ifpwafcad.sql</a>. |
| This SQL file creates two tables, <code>Subject</code> and <code>Counselor</code>, then |
| populates them with sample data. If you have not already done this, or if you need help |
| with this task, see <a href="../../docs/ide/mysql.html">Connecting to a MySQL Database</a> |
| before proceeding further.</p> |
| |
| <p>Also, your database needs to be password-protected to create a data source and |
| work with the GlassFish server in this tutorial. If you are using the default MySQL <code>root</code> |
| account with an empty password, you can set the password from a command-line prompt. |
| |
| <br><br> |
| This tutorial uses <code>nbuser</code> as an example password. To set your password |
| to <code><em>nbuser</em></code>, navigate to your MySQL installation's <code>bin</code> |
| directory in the command-line prompt and enter the following:</p> |
| |
| <pre class="examplecode" style="width: 703px;"> |
| shell> mysql -u root |
| mysql> UPDATE mysql.user SET Password = PASSWORD('<em>nbuser</em>') |
| -> WHERE User = 'root'; |
| mysql> FLUSH PRIVILEGES;</pre> |
| |
| <p>For more information, see the official MySQL Reference Manual: |
| <a href="http://dev.mysql.com/doc/refman/5.1/en/default-privileges.html">Securing |
| the Initial MySQL Accounts</a>.</p> |
| </div> |
| </div> |
| <br style="clear: both;"/> |
| |
| <ol> |
| <li><a href="#setUpJDBC">Setting up a JDBC data source and connection pool</a></li> |
| <li><a href="#referenceDataSource">Referencing the data source from the application</a></li> |
| <li><a href="#addJar">Adding the database driver's JAR file to the server</a></li> |
| </ol> |
| |
| <div class="indent"> |
| <h3 id="setUpJDBC">Setting up a JDBC data source and connection pool</h3> |
| |
| <p>The GlassFish Server Open Source Edition contains Database Connection Pooling (DBCP) |
| libraries that provide connection pooling functionality in a way that is |
| transparent to you as a developer. To take advantage of this, you need to |
| configure a <a href="http://docs.oracle.com/javase/tutorial/jdbc/overview/index.html">JDBC</a> |
| (Java Database Connectivity) <em>data source</em> for the server which your |
| application can use for connection pooling.</p> |
| |
| <p class="tips">For more information on JDBC technology, see |
| <a href="http://docs.oracle.com/javase/tutorial/jdbc/basics/index.html">The |
| Java Tutorials: JDBC Basics</a>.</p> |
| |
| <p>You could configure the data source directly within the GlassFish server Admin Console, |
| or, as described below, you can declare the resources that your application needs |
| in a <code>glassfish-resources.xml</code> file. When the application is deployed, the |
| server reads in the resource declarations, and creates the necessary resources.</p> |
| |
| <p>The following steps demonstrate how to declare a connection pool, and a data source |
| that relies on the connection pool. The NetBeans JDBC Resource wizard allows you |
| to perform both actions.</p> |
| |
| <ol> |
| <li>Open the New File wizard by pressing the New File ( |
| <img src="../../../images_www/articles/72/web/mysql-webapp/new-file-btn.png" alt="New File button"> |
| ) button in the IDE's main toolbar. Select the GlassFish server category, then select |
| JDBC Resource and click Next.</li> |
| |
| <li>In step 2, General Attributes, choose the Create New JDBC Connection Pool option, |
| then in the JNDI Name text field, type in <strong>jdbc/IFPWAFCAD</strong>. |
| |
| <br> |
| <img src="../../../images_www/articles/72/web/mysql-webapp/jdbc-resource-wizard.png" |
| title="Specify data source settings in the JDBC Resource wizard" |
| alt="JDBC Resource wizard, step 2: General Attributes" |
| class="b-all margin-around"> |
| <br> |
| <span class="tips">The JDBC data source relies on |
| <a href="http://www.oracle.com/technetwork/java/jndi/index.html">JNDI</a>, |
| the Java Naming and Directory Interface. The JNDI API provides a uniform way for |
| applications to find and access data sources. For more information, see |
| <a href="http://docs.oracle.com/javase/jndi/tutorial/">The JNDI Tutorial</a>.</span></li> |
| |
| <li>Optionally, add a description for the data source. For example, type in: |
| <code>Accesses the database that provides data for the IFPWAFCAD application</code>.</li> |
| |
| <li>Click Next, then click Next again to skip step 3, Additional Properties.</li> |
| |
| <li>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 |
| <code>jdbc:mysql://localhost:3306/MyNewDatabase</code> from the drop-down list. Click Next. |
| |
| <br> |
| <img src="../../../images_www/articles/74/web/mysql-webapp/jdbc-resource-wizard2.png" |
| title="Specify connection pool settings in the JDBC Resource wizard" |
| alt="JDBC Resource wizard, step 4: Choose Database Connection" |
| class="b-all margin-around"> |
| |
| <br><br> |
| <strong class="notes">Note: </strong>The wizard detects any database |
| connections that have been set up in the IDE. Therefore, you need to |
| have already created a connection to the <code>MyNewDatabase</code> database |
| at this point. You can verify what connections have been created by |
| opening the Services window (Ctrl-5; ⌘-5 on Mac) and looking for |
| connection nodes ( |
| <img src="../../../images_www/articles/72/web/mysql-webapp/connection-node-icon.png" |
| alt="database connection node icon"> ) under the Databases category.</li> |
| |
| <li>In Step 5, select <code>javax.sql.ConnectionPoolDataSource</code> in the Resource Type drop-down list. |
| <p>Note that the IDE extracts information from the database |
| connection you specified in the previous step, and sets name-value properties |
| for the new connection pool.</p> |
| |
| <img src="../../../images_www/articles/74/web/mysql-webapp/jdbc-resource-wizard3.png" |
| title="Default values are based on information extracted from the selected database connection" |
| alt="JDBC Resource wizard, step 5: Add Connection Pool Properties" class="b-all margin-around"></li> |
| |
| <li>Click Finish. The wizard generates a <code>glassfish-resources.xml</code> file that |
| contains entries for the data source and connection pool you specified.</li> |
| </ol> |
| |
| <p>In the Projects window, you can open the <code>glassfish-resources.xml</code> file |
| that was created under the Server Resources node and note that, within the <code><resources></code> |
| tags, a data source and connection pool have been declared containing the values you |
| previously specified.</p> |
| |
| <p class="tips">To confirm that a new data source and connection pool are indeed |
| registered with the GlassFish server, you can deploy the project to the server, then locate |
| the resources in the IDE's Services window:</p> |
| |
| <ol> |
| <li>In the Projects window, right-click the IFPWAFCAD project node and |
| choose Deploy. The server starts up if not already running, and the project |
| is compiled and deployed to it.</li> |
| |
| <li id="view-connection-pool">Open the Services window (Ctrl-5; ⌘-5 on Mac) |
| and expand the Servers > GlassFish > Resources > JDBC > JDBC |
| Resources and Connection Pools nodes. Note that the new data source and |
| connection pool are now displayed: |
| |
| <br> |
| <img src="../../../images_www/articles/72/web/mysql-webapp/services-window-glassfish.png" |
| alt="New data source and connection pool displayed in Services window" class="margin-around b-all" |
| title="New data source and connection pool displayed in Services window"> |
| </li> |
| </ol> |
| |
| |
| <h3 id="referenceDataSource">Referencing the data source from the application</h3> |
| |
| <p>You need to reference the JDBC resource you just configured from the web application. |
| To do so, you can create an entry in the application's <code>web.xml</code> deployment |
| descriptor.</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 class="notes"><strong>Note.</strong> If you specified Java EE 6 or Java EE 7 as the Java version when you |
| created the project, you need to create the deployment descriptor file by choosing |
| Web > Standard Deployment Descriptor in the New File wizard.</p> |
| |
| <p>Perform the following steps to reference the data source in the application's deployment |
| descriptor.</p> |
| |
| <ol> |
| <li>In the Projects window, expand the Configuration Files folder and double-click |
| <code>web.xml</code> to open the file in the editor.</li> |
| |
| <li>Click the References tab located along the top of the editor. </li> |
| <li>Expand the Resource References heading and click Add to open the Add Resource Reference dialog.</li> |
| |
| <li>For Resource Name, enter the resource name that you gave when configuring the |
| data source for the server above (<code>jdbc/IFPWAFCAD</code>). </li> |
| <li>Type <strong><code>javax.sql.ConnectionPoolDataSource</code></strong> in the Resource Type field. Click OK. |
| |
| <p>The Description field |
| is optional, but you can enter a human-readable description of the resource, e.g., |
| <code>Database for IFPWAFCAD application</code>. </p> |
| |
| <img src="../../../images_www/articles/74/web/mysql-webapp/add-resource-reference.png" |
| alt="Add Resource Reference dialog" class="margin-around b-all" |
| title="Specify resource properties in the Add Resource Reference dialog"> |
| |
| <p>The new resource is now listed under the Resource References heading.</p> |
| </li> |
| |
| <li>To verify that the resource is now added to the <code>web.xml</code> file, |
| click the Source tab located along the top of the editor. Notice |
| that the following <<code>resource-ref</code>> tags are now included. |
| |
| <pre class="examplecode"> |
| <resource-ref> |
| <description>Database for IFPWAFCAD application</description> |
| <res-ref-name>jdbc/IFPWAFCAD</res-ref-name> |
| <res-type>javax.sql.ConnectionPoolDataSource</res-type> |
| <res-auth>Container</res-auth> |
| <res-sharing-scope>Shareable</res-sharing-scope> |
| </resource-ref></pre> |
| </li> |
| </ol> |
| |
| <h3 id="addJar">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 <code>mysql-connector-java-5.1.6-bin.jar</code> |
| file from the driver's root directory into the library folder of the server you are |
| using. Fortunately, the IDE's server management 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, open the Servers manager (Choose Tools > Servers). |
| The IDE provides a JDBC driver deployment option. If the option is enabled, it |
| initiates a check to determine whether any drivers are required for the server's |
| deployed applications. In the case of MySQL, if the driver is required and it is |
| missing, the IDE's bundled driver is deployed to the appropriate location on the |
| server.</p> |
| |
| <ol> |
| <li>Choose Tools > Servers to open the Servers manager. Select the GlassFish server |
| in the left pane.</li> |
| |
| <li>In the main pane, select the Enable JDBC Driver Deployment option. |
| |
| <br> |
| <img src="../../../images_www/articles/74/web/mysql-webapp/servers-window.png" |
| title="JDBC Driver Deployment option enables automatic driver deployment" |
| class="margin-around b-all" alt="Servers window - the GlassFish server 3.0.1"></li> |
| |
| <li>Before you close the Servers manager, make a note of the path indicated in the Domains |
| folder text field. When you connect to the GlassFish server in the IDE, you are actually |
| connecting to an <em>instance</em> of the application server. Each instance runs |
| applications in a unique domain, and the Domain Name field indicates the name |
| of the domain your server is using. As shown in the image above, the driver JAR |
| file should be located within <code>domain1</code>, which is the default domain |
| created upon installing the GlassFish server.</li> |
| |
| <li>Click Close to exit the Servers manager.</li> |
| |
| <li>On your computer, navigate to the GlassFish server installation directory and drill into |
| the <code>domains</code> > <code>domain1</code> > <code>lib</code> subfolder. |
| Because you should have already deployed the IFPWAFCAD project to the server, |
| you should see the <code>mysql-connector-java-5.1.6-bin.jar</code> file. |
| If you do not see the driver JAR file, perform the following step.</li> |
| |
| <li>Deploy your project to the server. In the IDE's Projects window, choose Deploy |
| from the right-click menu of the project node. You can view progress in the IDE's |
| Output window (Ctrl-4; ⌘-4 on Mac). The output indicates that the MySQL driver |
| is deployed to a location in the GlassFish server. |
| |
| <br> |
| <img src="../../../images_www/articles/72/web/mysql-webapp/output-window.png" |
| title="Output window indicates that the MySQL driver has been deployed" |
| class="margin-around b-all" alt="Output window indicating MySQL driver has been deployed"> |
| |
| <br> |
| Now, if you return to the <code>domain1/lib</code> subfolder on your computer, you can |
| see that the <code>mysql-connector-java-5.1.6-bin.jar</code> file has been automatically |
| added.</li> |
| </ol> |
| </div> |
| |
| |
| <br> |
| <h2 id="addLogic">Adding Dynamic Logic</h2> |
| |
| <p>Returning to the <code>index.jsp</code> and <code>response.jsp</code> placeholders |
| that you created earlier in the tutorial, you can now implement the JSTL code |
| that enables pages to generate content <em>dynamically</em>, i.e., based on user |
| input. To do so, perform the following three tasks.</p> |
| |
| <ol> |
| <li><a href="#addJSTL">Add the JSTL library to the project's classpath</a></li> |
| <li><a href="#implementCode">Implement JSTL code</a></li> |
| </ol> |
| |
| <div class="indent"> |
| <h3 id="addJSTL">Adding the JSTL library to the project's classpath</h3> |
| |
| <p>You can apply the <a href="http://www.oracle.com/technetwork/java/index-jsp-135995.html">JavaServer Pages |
| Standard Tag Library</a> (JSTL) to access and display data taken from the database. |
| The GlassFish server includes the JSTL library by default. |
| You can verify this by expanding the GlassFish Server node under the Libraries |
| node in the Projects window, and |
| searching for the <code>javax.servlet.jsp.jstl.jar</code> library. |
| (Older versions of the GlassFish server use the <code>jstl-impl.jar</code> library.) |
| Because the GlassFish server libraries |
| are by default added to your project's classpath, you do not have to perform any steps |
| for this task.</p> |
| |
| <p>JSTL provides the following four basic areas of functionality.</p> |
| |
| <ul> |
| <li><code>core</code>: common, structural tasks such as iterators and conditionals |
| for handling flow control</li> |
| <li><code>fmt</code>: internationalization and localization message formatting</li> |
| <li><code>sql</code>: simple database access</li> |
| <li><code>xml</code>: handling of XML content</li> |
| </ul> |
| |
| <p>This tutorial focuses on usage of the <code>core</code> and <code>sql</code> |
| tag libraries.</p> |
| |
| <h3 id="implementCode">Implementing JSTL code</h3> |
| |
| <p>Now you can implement the code that dynamically retrieves and displays data for |
| each page. Both pages require that you implement an SQL query that utilizes the |
| data source created earlier in the tutorial.</p> |
| |
| <p>The IDE provides several database-specific JSTL snippets which you can select |
| from the Palette (Ctrl-Shift-8; ⌘-Shift-8 on Mac).</p> |
| |
| <img src="../../../images_www/articles/72/web/mysql-webapp/palette-db.png" |
| title="Choose database-specific JSTL snippets from the Palette" |
| class="margin-around b-all" alt="Database snippets available from the Palette"> |
| |
| |
| <h4>index.jsp</h4> |
| |
| <p>In order to dynamically display the contents of the form in <code>index.jsp</code>, |
| you need to access all <code>name</code>s from the <code>Subject</code> database |
| table.</p> |
| |
| <ol> |
| <li>Hover your mouse over the DB Report item in the Palette. |
| |
| <br> |
| <img src="../../../images_www/articles/72/web/mysql-webapp/db-report.png" |
| title="Type 'db' and press Ctrl-Space to access database-specific JSTL snippets" |
| class="margin-around" alt="Database snippets available from the Palette"> |
| |
| <p>The DB Report item uses the <code><sql:query></code> tag to create |
| an SQL query, then it uses the <code><c:forEach></code> tag to loop |
| through the query's <code>resultset</code> and output the retrieved data.</p></li> |
| |
| <li>Place your cursor above the <code><%@page ... %></code> declaration (line 7), |
| then double-click the DB Report item in the Palette. In the dialog that displays, |
| enter the following details: |
| |
| <ul> |
| <li><strong>Variable Name:</strong> <code>subjects</code></li> |
| <li><strong>Scope:</strong> <code>page</code></li> |
| <li><strong>Data Source:</strong> <code>jdbc/IFPWAFCAD</code></li> |
| <li><strong>Query Statement:</strong> <code>SELECT subject_id, name FROM Subject</code></li> |
| </ul> |
| |
| <img src="../../../images_www/articles/72/web/mysql-webapp/insert-db-report.png" |
| title="Use the Insert DB Report dialog to specify query-specific details" |
| class="margin-around b-all" alt="Insert DB Report dialog"></li> |
| |
| <li>Click OK. The following content is generated in the <code>index.jsp</code> |
| file. (New content shown in <strong>bold</strong>.) |
| |
| <pre class="examplecode"> |
| <strong><%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%> |
| <%@taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql"%></strong> |
| <%-- |
| Document : index |
| Author : nbuser |
| --%> |
| |
| <strong><sql:query var="subjects" dataSource="jdbc/IFPWAFCAD"> |
| SELECT subject_id, name FROM Subject |
| </sql:query> |
| |
| <table border="1"> |
| <!-- column headers --> |
| <tr> |
| <c:forEach var="columnName" items="${subjects.columnNames}"> |
| <th><c:out value="${columnName}"/></th> |
| </c:forEach> |
| </tr> |
| <!-- column data --> |
| <c:forEach var="row" items="${subjects.rowsByIndex}"> |
| <tr> |
| <c:forEach var="column" items="${row}"> |
| <td><c:out value="${column}"/></td> |
| </c:forEach> |
| </tr> |
| </c:forEach> |
| </table></strong> |
| |
| <%@page contentType="text/html" pageEncoding="UTF-8"%> |
| <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" |
| "http://www.w3.org/TR/html4/loose.dtd"></pre> |
| |
| Note that the IDE automatically added <code>taglib</code> directives |
| needed for the JSTL tags used in the generated content (<code><sql:query></code> |
| and <code><c:forEach></code>). A <code>taglib</code> directive |
| declares that the JSP page uses custom (i.e., JSTL) tags, names the tag |
| library that defines them, and specifies their tag prefix.</li> |
| |
| <li>Run the project to see how it displays in a browser. Right-click |
| the project node in the Projects window and choose Run. |
| |
| <p>When you choose Run, the IDE deploys the project to the GlassFish server, |
| the index page is compiled into a servlet, and the welcome page opens in your default browser. |
| The code generated from the DB Report item creates the following table in the welcome page.</p> |
| |
| <img src="../../../images_www/articles/72/web/mysql-webapp/db-report-table.png" |
| alt="Browser displaying table containing data from Subject database table" |
| title="Use DB Report for quick prototyping of database table data" |
| class="margin-around"> |
| |
| <p> |
| As you can see, the DB Report item enables you to quickly test your database |
| connection, and enables you to view table data from the database in your |
| browser. This can be particularly useful when prototyping.</p> |
| |
| <p> |
| The following steps demonstrate how to integrate the generated code into the |
| HTML drop-down list you created earlier in the tutorial.</p></li> |
| |
| <li>Examine the column data in the generated code. Two <code><c:forEach></code> |
| tags are used; one is nested inside the other. This causes the JSP container (i.e., |
| the GlassFish server) to perform a loop on all table rows, and for each row, it loops through |
| all columns. In this manner, data for the entire table is displayed.</li> |
| |
| <li>Integrate the <code><c:forEach></code> tags into the HTML form as follows. |
| The value of each item becomes the <code>subject_id</code>, and the output text |
| becomes the <code>name</code>, as recorded in the database. (Changes are displayed |
| in <strong>bold</strong>). |
| |
| <pre class="examplecode"> |
| <form action="response.jsp"> |
| <strong>Select a subject:</strong> |
| <select name="subject_id"> |
| <strong><c:forEach var="row" items="${subjects.rowsByIndex}"> |
| <c:forEach var="column" items="${row}"></strong> |
| <option <strong>value="<c:out value="${column}"/>"</strong>><strong><c:out value="${column}"/></strong></option> |
| <strong></c:forEach> |
| </c:forEach></strong> |
| </select> |
| <input type="submit" value="submit" name="submit" /> |
| </form></pre> |
| |
| <span class="tips">An alternative, simpler way to integrate the <code><c:forEach></code> |
| tags into the HTML form would be as follows.</span> |
| |
| <pre class="examplecode"> |
| <form action="response.jsp"> |
| <strong>Select a subject:</strong> |
| <select name="subject_id"> |
| <strong><c:forEach var="row" items="${subjects.rows}"></strong> |
| <option <strong>value="${row.subject_id}"</strong>><strong>${row.name}</strong></option> |
| <strong></c:forEach></strong> |
| </select> |
| <input type="submit" value="submit" name="submit" /> |
| </form></pre> |
| |
| <p>In either case, the <code><c:forEach></code> tags loop through all |
| <code>subject_id</code> and <code>name</code> values from the SQL query, and |
| insert each pair into the HTML <code><option></code> tags. In this manner, |
| the form's drop-down list is populated with data.</p></li> |
| |
| <li>Delete the table that was generated from the DB Report item. (Deletion shown |
| below as <strong><strike>strike-through text</strike></strong>.) |
| |
| <pre class="examplecode"> |
| <%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%> |
| <%@taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql"%> |
| <%-- |
| Document : index |
| Created on : Dec 22, 2009, 7:39:49 PM |
| Author : nbuser |
| --%> |
| |
| <sql:query var="subjects" dataSource="jdbc/IFPWAFCAD"> |
| SELECT subject_id, name FROM Subject |
| </sql:query> |
| |
| <strong><strike><table border="1"> |
| <!-- column headers --> |
| <tr> |
| <c:forEach var="columnName" items="${subjects.columnNames}"> |
| <th><c:out value="${columnName}"/></th> |
| </c:forEach> |
| </tr> |
| <!-- column data --> |
| <c:forEach var="row" items="${subjects.rowsByIndex}"> |
| <tr> |
| <c:forEach var="column" items="${row}"> |
| <td><c:out value="${column}"/></td> |
| </c:forEach> |
| </tr> |
| </c:forEach> |
| </table></strike></strong> |
| |
| <%@page contentType="text/html" pageEncoding="UTF-8"%> |
| <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" |
| "http://www.w3.org/TR/html4/loose.dtd"></pre></li> |
| |
| <li>Save your changes (Ctrl-S; ⌘-S on Mac). |
| <li>Refresh the welcome page of the project in your browser. |
| <p>Note that the drop-down list in the browser now |
| contains subject names that were retrieved from the database.</p> |
| |
| |
| |
| <p class="tips">You do not need to redeploy your project because |
| compile-on-save is enabled for your project by default. |
| This means that when you modify and save a file, the file is |
| automatically compiled and deployed and you do not need to recompile the entire project. |
| You can enable and disable compile-on-save for your project in the Compiling category |
| of the Properties window of the project.</p> |
| </li> |
| </ol> |
| |
| <h4>response.jsp</h4> |
| |
| <p>The response page provides details for the counselor who corresponds to the |
| subject chosen in the welcome page. The query you create must select the counselor |
| record whose <code>counselor_id</code> matches the <code>counselor_idfk</code> |
| from the selected subject record.</p> |
| |
| <ol> |
| <li>Place your cursor above the <code><%@page ... %></code> declaration (line 7), |
| and double-click DB Query in the Palette to open the Insert DB Query dialog box.</li> |
| |
| <li>Enter the following details in the Insert DB Query dialog box. |
| |
| <ul> |
| <li><strong>Variable Name:</strong> <code>counselorQuery</code></li> |
| <li><strong>Scope:</strong> <code>page</code></li> |
| <li><strong>Data Source:</strong> <code>jdbc/IFPWAFCAD</code></li> |
| <li><strong>Query Statement:</strong> <code>SELECT * FROM Subject, Counselor WHERE |
| Counselor.counselor_id = Subject.counselor_idfk AND Subject.subject_id = ? |
| <sql:param value="${param.subject_id}"/></code></li> |
| </ul> |
| |
| <img src="../../../images_www/articles/72/web/mysql-webapp/insert-db-query2.png" |
| title="Use the Insert DB Query dialog to specify query-specific details" |
| class="margin-around b-all" alt="Insert DB Query dialog"></li> |
| |
| <li>Click OK. The following content is generated in the <code>response.jsp</code> |
| file. (New content shown in <strong>bold</strong>.) |
| |
| <pre class="examplecode"> |
| <strong><%@taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql"%></strong> |
| <%-- |
| Document : response |
| Created on : Dec 22, 2009, 8:52:57 PM |
| Author : nbuser |
| --%> |
| |
| <strong><sql:query var="counselorQuery" dataSource="jdbc/IFPWAFCAD"> |
| SELECT * FROM Subject, Counselor |
| WHERE Counselor.counselor_id = Subject.counselor_idfk |
| AND Subject.subject_id = ? <sql:param value="${param.subject_id}"/> |
| </sql:query></strong> |
| |
| <%@page contentType="text/html" pageEncoding="UTF-8"%> |
| <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" |
| "http://www.w3.org/TR/html4/loose.dtd"></pre> |
| |
| Note that the IDE automatically added the <code>taglib</code> directive |
| needed for the <code><sql:query></code> tag. Also, note that you |
| used an <code><sql:param></code> tag directly within the query. |
| Because this query relies on the <code>subject_id</code> value that |
| was submitted from <code>index.jsp</code>, you can extract the value |
| using an EL (Expression Language) statement in the form of |
| <code>${param.subject_id}</code>, and then pass it to the |
| <code><sql:param></code> tag so that it can be used in place of |
| the SQL question mark (<code>?</code>) during runtime.</li> |
| |
| <li>Use a <code><c:set></code> tag to set a variable that corresponds |
| to the first record (i.e., row) of the <code>resultset</code> returned |
| from the query. (New content shown in <strong>bold</strong>.) |
| |
| <pre class="examplecode"> |
| <sql:query var="counselorQuery" dataSource="jdbc/IFPWAFCAD"> |
| SELECT * FROM Subject, Counselor |
| WHERE Counselor.counselor_id = Subject.counselor_idfk |
| AND Subject.subject_id = ? <sql:param value="${param.subject_id}"/> |
| </sql:query> |
| |
| <strong><c:set var="counselorDetails" value="${counselorQuery.rows[0]}"/></strong></pre> |
| |
| Although the <code>resultset</code> returned from the query should only |
| contain a single record, this is a necessary step because the page needs |
| to access values from the record using EL (Expression Language) statements. |
| Recall that in <code>index.jsp</code>, you were able to access values from |
| the <code>resultset</code> simply by using a <code><c:forEach></code> |
| tag. However, the <code><c:forEach></code> tag operates by setting a |
| variable for the rows contained in the query, thus enabling you to extract |
| values by including the row variable in EL statements.</li> |
| |
| <li>Add the <code>taglib</code> directive for the JSTL <code>core</code> library |
| to the top of the file, so that the <code><c:set></code> tag is understood. |
| (New content shown in <strong>bold</strong>.) |
| |
| <pre class="examplecode"> |
| <strong><%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%></strong> |
| <%@taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql"%></pre></li> |
| |
| <li>In the HTML markup, replace all placeholders with EL statements code that |
| display the data held in the <code>counselorDetails</code> variable. |
| (Changes below shown in <strong>bold</strong>): |
| |
| <pre class="examplecode"> |
| <html> |
| <head> |
| <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/> |
| <link rel="stylesheet" type="text/css" href="style.css"> |
| <title><strong>${counselorDetails.name}</strong></title> |
| </head> |
| |
| <body> |
| <table> |
| <tr> |
| <th colspan="2"><strong>${counselorDetails.name}</strong></th> |
| </tr> |
| <tr> |
| <td><strong>Description: </strong></td> |
| <td><span style="font-size:smaller; font-style:italic;"><strong>${counselorDetails.description}</strong></span></td> |
| </tr> |
| <tr> |
| <td><strong>Counselor: </strong></td> |
| <td><strong><strong>${counselorDetails.first_name} ${counselorDetails.nick_name} ${counselorDetails.last_name}</strong></strong> |
| <br><span style="font-size:smaller; font-style:italic;"> |
| <em>member since: <strong>${counselorDetails.member_since}</strong></em></span></td> |
| </tr> |
| <tr> |
| <td><strong>Contact Details: </strong></td> |
| <td><strong>email: </strong> |
| <a href="mailto:<strong>${counselorDetails.email}</strong>"><strong>${counselorDetails.email}</strong></a> |
| <br><strong>phone: </strong><strong>${counselorDetails.telephone}</strong></td> |
| </tr> |
| </table> |
| </body> |
| </html></pre></li> |
| </ol> |
| </div> |
| |
| |
| <br> |
| <h2 id="run">Running the Completed Application</h2> |
| |
| |
| <p>You've now completed the application. Try running it again to see how it displays |
| in a browser. Note that because of NetBeans' Compile on Save feature, you do not |
| need to worry about compiling or redeploying the application. When you run a project, |
| you can be sure the deployment contains your latest changes.</p> |
| |
| <p>Click the Run Project ( |
| <img src="../../../images_www/articles/72/web/mysql-webapp/run-project-btn.png" |
| alt="Run Project button"> ) button in the main toolbar. The <code>index.jsp</code> |
| page opens in the IDE's default browser.</p> |
| |
| <p>When <code>index.jsp</code> displays in the browser, select a subject from the |
| drop-down list and click <code>submit</code>. You should now be forwarded to |
| the <code>response.jsp</code> page, showing details corresponding to your selection.</p> |
| |
| <div class="indent"> |
| <img src="../../../images_www/articles/72/web/mysql-webapp/response-display.png" class="margin-around" |
| alt="response.jsp displayed in a browser, showing data retrieved from database" |
| title="response.jsp displayed in a browser, showing data retrieved from database"> |
| </div> |
| |
| <p>This concludes the Creating a Simple Web Application Using a MySQL Database tutorial. |
| This document demonstrated how to create a simple web application that connects to |
| a MySQL database. It also demonstrated how to construct an application using a basic |
| two-tier architecture, and utilized numerous technologies including JSP, JSTL, |
| JDBC, and JNDI as a means of accessing and displaying data dynamically.</p> |
| |
| |
| <br> |
| <h2 id="troubleshoot">Troubleshooting</h2> |
| |
| <p>Most of the problems that occur with the tutorial application are due to communication |
| difficulties between the GlassFish Server Open Source Edition and the MySQL database server. |
| If your application does not display correctly, or if you are receiving a server error, |
| the following examinations may be useful.</p> |
| |
| <ul> |
| <li><a href="#access?">Do database resources exist?</a></li> |
| <li><a href="#datasource?">Do the connection pool and data source exist on the server?</a></li> |
| <li><a href="#driver?">Is the MySQL Connector/J driver accessible to the GlassFish server?</a></li> |
| <li><a href="#password?">Is the database password-protected?</a></li> |
| <li><a href="#ping?">Are the connection pool properties correctly set?</a></li> |
| </ul> |
| |
| <div class="indent"> |
| <h3 id="access">Do database resources exist?</h3> |
| |
| <p>Use the IDE's Services window (Ctrl-5; ⌘-5 on Mac) to ensure that the MySQL |
| server is running, and that <code>MyNewDatabase</code> is accessible and contains |
| appropriate table data.</p> |
| |
| <ul> |
| <li>To connect to the MySQL database server, right-click the MySQL Server node |
| and choose Connect.</li> |
| |
| <li>If a connection node ( <img src="../../../images_www/articles/72/web/mysql-webapp/db-connection-node.png" |
| alt="Database connection node"> ) for <code>MyNewDatabase</code> does not display in |
| the Services window, you can create a connection by right-clicking the MySQL driver |
| node ( <img src="../../../images_www/articles/72/web/mysql-webapp/driver-node.png" |
| alt="Database driver node"> ) and choosing Connect Using. Enter the required details |
| in the dialog that displays. |
| |
| <br> |
| <img src="../../../images_www/articles/72/web/mysql-webapp/new-db-connection-dialog.png" |
| class="margin-around b-all" alt="New Database Connection dialog" |
| title="Establish a database connection in the IDE using the New Database Connection dialog"> |
| |
| <br> |
| <span class="tips">The fields provided in the New Database Connection dialog |
| mirror the URL string entered in the Show JDBC URL option. Therefore, if |
| you know the URL (e.g., <code>jdbc:mysql://localhost:3306/MyNewDatabase</code>) |
| you can paste it into the Show JDBC URL field, and the remaining dialog fields |
| become automatically populated.</span></li> |
| |
| <li>To ensure that the <code>Subject</code> and <code>Counselor</code> tables exist |
| and that they contain sample data, expand the <code>MyNewDatabase</code> connection |
| node ( <img src="../../../images_www/articles/72/web/mysql-webapp/db-connection-node.png" |
| alt="Database connection node"> ) and locate the <code>MyNewDatabase</code> catalog |
| node ( <img src="../../../images_www/articles/72/web/mysql-webapp/db-catalog-node.png" |
| alt="Database catalog node"> ). Expand the catalog node to view existing tables. You |
| can view table data by right-clicking a table node and choosing View Data. |
| |
| <br> |
| <img src="../../../images_www/articles/72/web/mysql-webapp/services-window-view-data.png" |
| class="margin-around b-all" alt="Services window - right-click menu of database table node" |
| title="View table data by choosing View Data from the right-click menu of a database table node"></li> |
| </ul> |
| |
| |
| <h3 id="datasource">Do the connection pool and data source exist on the server?</h3> |
| |
| <p>After deploying the application to the GlassFish server, the <code>glassfish-resources.xml</code> contained |
| in the project should instruct the server to create a JDBC resource and connection pool. |
| You can determine whether these exist from the Servers node in the Services window.</p> |
| |
| <ul> |
| <li>Expand the Servers > the GlassFish Server > Resources node. Expand JDBC Resources |
| to view the <code>jdbc/IFPWAFCAD</code> data source that was created from |
| <code>glassfish-resources.xml</code>. Expand the Connection Pools node to view the |
| <code>IfpwafcadPool</code> connection pool that was created from |
| <code>glassfish-resources.xml</code>. (This is <a href="#view-connection-pool">demonstrated |
| above</a>.)</li> |
| </ul> |
| |
| |
| <h3 id="driver">Is the MySQL Connector/J driver accessible to the GlassFish server?</h3> |
| |
| <p>Make sure that the MySQL Connector/J driver has been deployed to the GlassFish server. (This is |
| discussed in <a href="#addJar">Adding the database driver's JAR file to the server</a>.)</p> |
| |
| <ul> |
| <li>Locate the GlassFish server installation folder on your computer and drill down into |
| the <code>GlassFish domains/domain1/lib</code> subfolder. Here you should find |
| the <code>mysql-connector-java-5.1.6-bin.jar</code> file.</li> |
| </ul> |
| |
| |
| <h3 id="password">Is the database password-protected?</h3> |
| |
| <p>The database needs to be password-protected to enable the GlassFish server data source to work |
| properly in this tutorial. If you are using the default MySQL <code>root</code> |
| account with an empty password, you can set the password from a command-line prompt.</p> |
| |
| <ul> |
| <li>To set your password to <code><em>nbuser</em></code>, navigate to your MySQL |
| installation's <code>bin</code> directory in the command-line prompt and enter |
| the following: |
| |
| <pre class="examplecode" style="width: 703px;"> |
| shell> mysql -u root |
| mysql> UPDATE mysql.user SET Password = PASSWORD('<em>nbuser</em>') |
| -> WHERE User = 'root'; |
| mysql> FLUSH PRIVILEGES;</pre> |
| |
| For more information, see the official MySQL Reference Manual: |
| <a href="http://dev.mysql.com/doc/refman/5.1/en/default-privileges.html">Securing |
| the Initial MySQL Accounts</a>.</li> |
| </ul> |
| |
| |
| <h3 id="ping">Are the connection pool properties correctly set?</h3> |
| |
| <p>Ensure that the connection pool is working correctly for the server.</p> |
| |
| <ol> |
| <li>Open the Services window (Ctrl-5; ⌘-5 on Mac) and expand the Servers node.</li> |
| <li>Right-click the GlassFish server node and choose View Admin Console.</li> |
| <li>Enter the username and password if you are prompted. |
| You can view the username and password in the Servers manager.</li> |
| <li>In the tree on the left side of the console, expand the Resources > JDBC |
| > JDBC Connection Pools > <code>IfpwafcadPool</code> node. Details for the |
| <code>IfpwafcadPool</code> connection pool display in the main window.</li> |
| <li>Click the Ping button. If the connection pool is set up correctly, you will |
| see a '<code>Ping Succeeded</code>' message. |
| |
| <br> |
| <img src="../../../images_www/articles/72/web/mysql-webapp/ping-succeeded.png" |
| class="margin-around b-all" |
| alt="the GlassFish server Admin Console - Connection Pool display" |
| title="Test your connection pool by clicking Ping in the GlassFish server Admin Console"></li> |
| |
| <li>If the ping fails, click the Additional Properties tab and ensure that the |
| listed property values are correctly set.</li> |
| |
| </ol> |
| </div> |
| |
| <br> |
| <div class="feedback-box"><a href="/about/contact_form.html?to=3&subject=Feedback:%20Creating%20Web%20App%20MySQL">Send |
| Us Your Feedback</a></div> |
| |
| <br style="clear:both;"> |
| |
| |
| <h2 id="seealso">See Also</h2> |
| |
| <p>For more information about Java web development, see the following resources.</p> |
| |
| <ul id="collapsableList2"> |
| <li><strong>NetBeans Articles and Tutorials</strong> |
| |
| <ul> |
| <li><a href="../ide/mysql.html">Connecting to a MySQL Database in NetBeans IDE</a>. Covers |
| the basics of working with a MySQL database in the IDE.</li> |
| |
| <li><a href="jsf20-intro.html">Introduction to JavaServer Faces 2.x</a>. An introductory |
| tutorial describing how to use the JSF framework in a Java web project.</li> |
| |
| <li><a href="quickstart-webapps-spring.html">Introduction to the Spring Framework</a>. An |
| introductory tutorial describing how to create an MVC web application using the Spring |
| Framework.</li> |
| </ul></li> |
| |
| <li><strong>Java Database Connectivity (JDBC)</strong> |
| |
| <ul> |
| <li><a href="http://docs.oracle.com/javase/tutorial/jdbc/overview/index.html">JDBC Overview</a></li> |
| <li><a href="http://download.oracle.com/javase/6/docs/technotes/guides/jdbc/getstart/GettingStartedTOC.fm.html">Getting Started with the JDBC API</a></li> |
| <li><a href="http://docs.oracle.com/javase/tutorial/jdbc/basics/index.html">The Java Tutorials: JDBC Basics</a></li> |
| </ul></li> |
| |
| <li><strong>JavaServer Pages Standard Tag Library (JSTL)</strong> |
| |
| <ul> |
| <li><a href="http://www.oracle.com/technetwork/java/index-jsp-135995.html">JavaServer Pages Standard Tag Library</a> (official product page)</li> |
| </ul></li> |
| |
| <li><strong>Java Naming and Directory Interface (JNDI)</strong> |
| |
| <ul> |
| <li><a href="http://www.oracle.com/technetwork/java/jndi-136720.html">Java SE Core Technologies - Java Naming and Directory Interface</a></li> |
| <li><a href="http://docs.oracle.com/javase/jndi/tutorial/">The JNDI Tutorial</a></li> |
| <li><a href="http://docs.oracle.com/javase/tutorial/jndi/index.html">The Java Tutorials: Java Naming and Directory Interface</a></li> |
| </ul></li> |
| |
| </ul> |
| |
| <script type="text/javascript"> |
| <!-- |
| compactMenu('collapsableList', true, '± '); |
| compactMenu('collapsableList2', true, '± '); |
| --> |
| </script> |
| </body> |
| </html> |
