How To Make A Simple Web Page

Creating a Uncomplicated Web Application Using a MySQL Database

• Planning the Construction
  • index.jsp
  • response.jsp
• Creating a New Project
• Preparing the Web Interface
  • Setting upwards the welcome page
  • Creating the response page
  • Creating a stylesheet
• Preparing Communication between the Application and Database
  • Setting upwardly a JDBC data source and connection pool
  • Referencing the information source from the application
  • Adding the database driver's JAR file to the server
• Calculation Dynamic Logic
  • Adding the JSTL library to the project's classpath
  • Implementing JSTL code
• Running the Completed Application
• Troubleshooting
  • Practice database resources exist?
  • Practice the connection pool and data source be on the server?
  • Is the MySQL Connector/J driver accessible to the GlassFish server?
  • Is the database countersign-protected?
  • Are the connection puddle properties correctly set?
• See Also

Written by Troy Giunipero

This certificate describes how to create a simple web awarding that connects to a MySQL database server. It also covers some basic ideas and technologies in web development, such every bit JavaServer Pages (JSP), JavaServer Pages Standard Tag Library (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 utilize their knowledge using a MySQL database.

MySQL is a popular open source database management system normally used in web applications due to its speed, flexibility and reliability. MySQL employs SQL, or Structured Query Language, for accessing and processing information contained in databases.

This tutorial is a continuation from the Connecting to a MySQL Database tutorial and assumes that you have already created a MySQL database named MyNewDatabase, which you lot have registered a connection for in the NetBeans IDE. The table data used in that tutorial is contained in ifpwafcad.sql and is also required for this tutorial. This SQL file creates two tables, Subject and Counselor, and then populates them with sample data. If needed, save this file to your computer, then open information technology in the NetBeans IDE and run it on the MySQL database named MyNewDatabase.

Figure 1. Content on this page applies to the NetBeans IDE seven.2, seven.3, 7.4 and 8.0

To follow this tutorial, y'all need the post-obit software and resources.

Software or Resource	Version Required
NetBeans IDE	7.two, seven.3, vii.4, eight.0, Java EE package
Java Evolution Kit (JDK)	version 7 or viii
MySQL database server	5.x
MySQL Connector/J JDBC Commuter	version 5.x
GlassFish Server Open Source Edition	3.x or 4.10

Notes:

• The Coffee download bundle of the NetBeans IDE enables you to install the GlassFish server. You crave the GlassFish server to work through this tutorial.

• The MySQL Connector/J JDBC Driver, necessary for communication betwixt Coffee platforms and the MySQL database protocol, is included in the NetBeans IDE.

• If y'all need to compare your project with a working solution, you tin can download the sample application.

Planning the Structure

Simple web applications can exist designed using a 2-tier architecture, in which a client communicates directly with a server. In this tutorial, a Java web application communicates straight with a MySQL database using the Java Database Connectivity API. Essentially, it is the MySQL Connector/J JDBC Driver that enables advice between the Java code understood past the application server (the GlassFish server), and any content in SQL, the linguistic communication understood by the database server (MySQL).

The application you build in this tutorial involves the cosmos of two JSP pages. In each of these pages you lot use HTML and CSS to implement a elementary interface, and employ JSTL applied science to perform the logic that straight queries the database and inserts the retrieved information into the two pages. The two database tables, Subject and Counselor, are contained in the MySQL database, MyNewDatabase, which you create by completing the Connecting to a MySQL Database tutorial. Consider the following two-tier scenario.

image::images/ifpwafcad-structure.png[championship="Sample construction of a two-tier web application"]

The welcome page (index.jsp) presents the user with a simple HTML class. When a browser requests alphabetize.jsp, the JSTL lawmaking within the folio initiates a query on MyNewDatabase. It retrieves data from the Subject field database tabular array, and inserts information technology into to the page before it is sent to the browser. When the user submits his or her selection in the welcome page'southward HTML grade, the submit initiates a request for the response page (response.jsp). Again, the JSTL code within the page initiates a query on MyNewDatabase. This time, information technology retrieves data from both the Bailiwick and Advisor tables and inserts information technology into to the page, allowing the user to view data based upon his or her selection when the page is returned to the browser.

In club to implement the scenario described in a higher place, you develop a simple application for a fictitious organisation named IFPWAFCAD, The International Sometime Professional Wrestlers' Association for Counseling and Evolution.

alphabetize.jsp

Figure 2. index.jsp displayed in a browser

response.jsp

Figure 3. response.jsp displayed in a browser

Creating a New Project

Begin by creating a new Java web project in the IDE:

1. Choose File > New Project (Ctrl-Shift-Due north; ⌘-Shift-North on Mac) from the main menu. Select the Coffee Web category, then select Spider web Application. Click Side by side.

Figure iv. Use the IDE's New Project wizard to create a new project

The New Project magician 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.

1. In Project Name, enter IFPWAFCAD. Likewise, specify the location for the project on your computer. (By default, the IDE places projects in a NetBeansProjects folder located in your dwelling directory.) Click Next.

2. In the Server and Settings panel, specify the GlassFish server as server which will be used to run the application.

*Note. *The GlassFish server displays in the Server drop-down field if you installed the Java version of the NetBeans IDE. Because the GlassFish server is included in the download, it is automatically registered with the IDE. If you want to utilise a dissimilar server for this project, click the Add push button located side by side to the Server drop-downwardly field, and register a unlike server with the IDE. Yet, working with servers other than the GlassFish server is beyond the scope of this tutorial.

1. In the Java EE Version field, select Java EE 5.

Figure v. Specify server settings in the New Web Application wizard

Java EE 6 and Java EE 7 web projects exercise not require the utilise of the web.xml deployment descriptor, and the NetBeans project template does not include the web.xml file in Java EE six and Java EE 7 projects. All the same, 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 vii, so you tin gear up the projection version to Java EE 5.

Note. You lot could as set the project version to Java EE half dozen or Coffee EE 7 and then create a web.xml deployment descriptor. (From the New File sorcerer, select the Spider web category, then Standard Deployment Descriptor.)

1. Click Finish. The IDE creates a projection template for the entire application, and opens an empty JSP page (index.jsp) in the editor. The index.jsp file serves as the welcome page for the application.

Preparing the Web Interface

Brainstorm by preparing the welcome (index.jsp) and response (response.jsp) pages. The welcome page implements an HTML grade that is used to capture user data. Both pages implement an HTML table to brandish data in a structured fashion. In this section, you also create a stylesheet that enhances the advent of both pages.

• Setting upward the welcome folio

• Creating the response page

• Creating a stylesheet

Setting upwardly the welcome folio

Confirm that index.jsp is open up in the editor. If the file is not already open, double-click index.jsp nether the Web Pages node in the IFPWAFCAD projection in the Projects window.

1. In the editor, change the text between the <championship> tags to: IFPWAFCAD Homepage.

2. Alter the text between the <h1> tags to: Welcome to IFPWAFCAD, the International Former Professional person Wrestlers' Clan for Counseling and Evolution!.

3. Open the IDE's Palette by choosing Window > Palette (Ctrl-Shift-viii; ⌘-Shift-8 on Mac) from the main carte du jour. Hover your arrow over the Table icon from the HTML category and notation that the default lawmaking snippet for the item displays.

Figure 6. Palette displays code snippet when hovering over an item

You tin configure the Palette to your liking - right-click in the Palette and cull Prove Big Icons and Hide Item Names to have it display as in the image above.

1. Identify your cursor at a signal just afterwards the <h1> tags. (This is where you want to implement the new HTML table.) Then, in the Palette, double-click the Table icon.

2. In the Insert Table dialog that displays, specify the post-obit values then click OK:

   • Rows: 2

   • Columns: 1

   • Border Size: 0 The HTML table code is generated and added to your page.

3. Add the following content to the tabular array heading and the cell of the first tabular array row (new content shown in bold):

                  <tabular array 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 bailiwick below:*</td>         </tr>                

1. For the bottom row of the table, insert an HTML form. To do so, identify your cursor betwixt the second pair of <td> tags, then double-click the HTML form ( ) icon in the Palette. In the Insert Form dialog, type in response.jsp in the Action text field, then click OK.

Figure seven. Specify form settings in the Insert Form dialog

1. Type in the following content between the <form> tags (new content shown in bold):

                  <tr>     <td>         <form action="response.jsp">             *<strong>Select a field of study:</strong>*         </grade>     </td> </tr>                

1. Press Enter to add an empty line after the content y'all but added and then double-click Drop-down List in the Palette to open the Insert Drop-downward dialog box.

2. Type subject_id for the Proper noun text field in the Insert Drop-down dialog and click OK. Notation that the lawmaking snippet for the driblet-down listing is added to the form.

The number of options for the drop-downwards is currently not important. Later in the tutorial you will add together JSTL tags that dynamically generate options based on the information gathered from the Subject database table.

1. Add a submit button particular ( ) to a point just after the drop-downwards list you but added. Y'all tin can either use the Palette to do this, or invoke the editor's code completion every bit illustrated in the previous step. In the Insert Push dialog, enter submit for both the Label and Name text fields, then click OK.

2. 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 like to the following:

                  <body>     <h2>Welcome to <strong>IFPWAFCAD</potent>, the International Onetime         Professional Wrestlers' Association for Counseling and Development!     </h2>      <table edge="0">         <thead>             <tr>                 <th>IFPWAFCAD offers adept counseling in a wide range of fields.</th>             </tr>         </thead>         <tbody>             <tr>                 <td>To view the contact details of an IFPWAFCAD certified sometime                     professional person wrestler in your area, select a subject beneath:</td>             </tr>             <tr>                 <td>                     <class action="response.jsp">                         <strong>Select a subject field:</strong>                         <select name="subject_id">                             <option></pick>                         </select>                         <input type="submit" value="submit" name="submit" />                     </class>                 </td>             </tr>         </tbody>     </table> </body>                

To view this page in a browser, right-click in the editor and cull 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.

Effigy 8. alphabetize.jsp displays in a browser

Creating the response page

In society to prepare the interface for response.jsp you must start create the file in your projection. Notation that virtually of the content that displays in this page is generated dynamically using JSP technology. Therefore, in the post-obit steps y'all add placeholders which you will afterward substitute for the JSP code.

1. Right-click the IFPWAFCAD project node in the Projects window and cull New > JSP. The New JSP File dialog opens.

2. In the JSP File Proper name field, enter response. Note that Spider web Pages is currently selected for the Location field, meaning that the file will be created in the projection's web directory. This is the aforementioned location as where the index.jsp welcome page resides.

3. Have whatsoever other default settings and click End. A template for the new response.jsp folio is generated and opens in the editor. A new JSP node as well displays under Spider web Pages in the Projects window.

Figure ix. response.jsp node appears in the Projects window

1. In the editor, change the championship to: IFPWAFCAD - {placeholder}.

2. Remove the <h1>Hi World!</h1> line between the <body> tags, then re-create and paste the following HTML table into the trunk of the page:

                  <tabular array border="0">     <thead>         <tr>             <th colspan="2">{placeholder}</thursday>         </tr>     </thead>     <tbody>         <tr>             <td><stiff>Description: </stiff></td>             <td><bridge style="font-size:smaller; font-style:italic;">{placeholder}</span></td>         </tr>         <tr>             <td><strong>Advisor: </strong></td>             <td>{placeholder}                 <br>                 <bridge manner="font-size:smaller; font-manner:italic;">                 member since: {placeholder}</bridge>             </td>         </tr>         <tr>             <td><strong>Contact Details: </strong></td>             <td><stiff>electronic mail: </strong>                 <a href="mailto:{placeholder}">{placeholder}</a>                 <br><potent>telephone: </strong>{placeholder}             </td>         </tr>     </tbody> </table>                

To view this page in a browser, correct-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.

Figure 10. response.jsp displays in a browser

Creating a stylesheet

Create a uncomplicated stylesheet that enhances the display of the web interface. This tutorial assumes that y'all sympathise how manner rules part, and how they affect corresponding HTML elements found in alphabetize.jsp and response.jsp.

1. Open up the New File wizard by pressing the New File ( ) button in the IDE's main toolbar. Select the Web category, and then select Cascading Style Sheet and click Next.

2. Type style for CSS File Proper noun and click Finish. The IDE creates an empty CSS file and places it in the same projection location as index.jsp and response.jsp. Note that a node for style.css now displays inside the project in the Projects window, and the file opens in the editor.

3. In the editor, add the following content to the style.css file:

                  body {     font-family: Verdana, Arial, sans-serif;     font-size: smaller;     padding: 50px;     colour: #555; }  h1 {     text-align: left;     alphabetic character-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 {    colour: #be7429;    font-weight: normal;    text-decoration: underline; }                

1. Link the stylesheet to alphabetize.jsp and response.jsp. In both pages, add the following line between the <caput> tags:

                  <link rel="stylesheet" type="text/css" href="way.css">                

To speedily navigate betwixt files that are open in the editor, press Ctrl-Tab, then select the file you are wanting.

Preparing Advice between the Application and Database

The most efficient way to implement communication betwixt the server and database is to prepare a database connection pool. Creating a new connexion for each customer request can be very time-consuming, peculiarly 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 admission to the application's information layer employ an already-created connection from the pool. Likewise, when a request is completed, the connection is non closed downwardly, but returned to the pool.

Afterward preparing the data source and connection puddle for the server, you lot so demand to instruct the application to use the data source. This is typically washed past creating an entry in the application's spider web.xml deployment descriptor. Finally, you need to ensure that the database commuter (MySQL Connector/J JDBC Driver) is accessible to the server.

*Important: * From this point forward, you lot demand you ensure that you have a MySQL database case named MyNewDatabase prepare that contains sample data provided in ifpwafcad.sql. This SQL file creates two tables, Bailiwick and Counselor, then populates them with sample data. If y'all accept not already done this, or if you need help with this task, encounter Connecting to a MySQL Database before proceeding further.

Too, your database needs to be countersign-protected to create a information source and work with the GlassFish server in this tutorial. If you are using the default MySQL root account with an empty password, you can set the password from a control-line prompt.

This tutorial uses nbuser as an example password. To gear up your countersign to nbuser , navigate to your MySQL installation's bin directory in the control-line prompt and enter the following:

                shell> mysql -u root mysql> UPDATE mysql.user Prepare Password = PASSWORD('_nbuser_')     ->     WHERE User = 'root'; mysql> FLUSH PRIVILEGES;              

1. Setting up a JDBC information source and connection pool

2. Referencing the data source from the application

3. Adding the database commuter's JAR file to the server

Setting up a JDBC data source and connectedness pool

The GlassFish Server Open Source Edition contains Database Connexion Pooling (DBCP) libraries that provide connection pooling functionality in a manner that is transparent to you lot every bit a developer. To take advantage of this, yous need to configure a JDBC (Coffee Database Connectivity) data source for the server which your application can use for connection pooling.

You could configure the data source directly inside the GlassFish server Admin Console, or, every bit described below, you tin declare the resources that your application needs in a glassfish-resources.xml file. When the application is deployed, the server reads in the resources declarations, and creates the necessary resources.

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 yous to perform both actions.

1. Open the New File magician by pressing the New File ( ) button in the IDE'south main toolbar. Select the GlassFish server category, then select JDBC Resources and click Side by side.

2. In step 2, General Attributes, choose the Create New JDBC Connexion Pool option, so in the JNDI Proper noun text field, type in jdbc/IFPWAFCAD.

Figure eleven. Specify data source settings in the JDBC Resource wizard

The JDBC data source relies on JNDI, the Coffee Naming and Directory Interface. The JNDI API provides a compatible style for applications to find and access information sources. For more information, run into The JNDI Tutorial.

1. Optionally, add together a description for the information source. For example, type in: Accesses the database that provides information for the IFPWAFCAD application.

2. Click Next, and so click Side by side again to skip footstep 3, Boosted Properties.

3. In Step four, type in IfpwafcadPool for JDBC Connexion Puddle Name. Make sure the Extract from Existing Connectedness option is selected, and choose jdbc:mysql://localhost:3306/MyNewDatabase from the drop-down list. Click Next.

Figure 12. Specify connexion pool settings in the JDBC Resources sorcerer

*Note: *The wizard detects any database connections that take been prepare upward in the IDE. Therefore, yous need to have already created a connection to the MyNewDatabase database at this point. You lot can verify what connections have been created by opening the Services window (Ctrl-5; ⌘-5 on Mac) and looking for connexion nodes ( ) nether the Databases category.

1. In Pace 5, select javax.sql.ConnectionPoolDataSource in the Resource Blazon drop-down listing.

Note that the IDE extracts data from the database connection you lot specified in the previous step, and sets name-value properties for the new connection pool.

Figure thirteen. Default values are based on data extracted from the selected database connection

1. Click Finish. The sorcerer generates a glassfish-resources.xml file that contains entries for the data source and connection pool yous specified.

In the Projects window, yous can open the glassfish-resources.xml file that was created under the Server Resources node and note that, inside the <resources> tags, a data source and connexion pool have been declared containing the values you previously specified.

To confirm that a new data source and connection pool are indeed registered with the GlassFish server, yous can deploy the project to the server, then locate the resources in the IDE's Services window:

1. In the Projects window, correct-click the IFPWAFCAD projection node and cull Deploy. The server starts upwards if not already running, and the projection is compiled and deployed to it.

2. Open the Services window (Ctrl-5; ⌘-5 on Mac) and expand the Servers > GlassFish > Resource > JDBC > JDBC Resources and Connection Pools nodes. Note that the new data source and connection pool are now displayed:

Effigy 14. New data source and connectedness pool displayed in Services window

Referencing the data source from the application

You need to reference the JDBC resource yous just configured from the spider web awarding. To practise so, you tin can create an entry in the awarding's web.xml deployment descriptor.

Deployment descriptors are XML-based text files that incorporate information describing how an application is to exist deployed to a specific environment. For example, they are unremarkably used to specify application context parameters and behavioral patterns, security settings, besides every bit mappings for servlets, filters and listeners.

Annotation. If you lot specified Coffee EE six or Java EE 7 as the Java version when you created the project, you demand to create the deployment descriptor file past choosing Web > Standard Deployment Descriptor in the New File magician.

Perform the post-obit steps to reference the data source in the awarding's deployment descriptor.

1. In the Projects window, aggrandize the Configuration Files folder and double-click web.xml to open the file in the editor.

2. Click the References tab located along the acme of the editor.

3. Expand the Resource References heading and click Add to open up the Add Resource Reference dialog.

4. For Resource Proper noun, enter the resources name that you gave when configuring the data source for the server above (jdbc/IFPWAFCAD).

5. Type javax.sql.ConnectionPoolDataSource in the Resource Type field. Click OK.

The Clarification field is optional, merely y'all can enter a homo-readable description of the resources, e.g., Database for IFPWAFCAD application.

Figure 15. Specify resources properties in the Add Resource Reference dialog

The new resource is at present listed under the Resource References heading.

1. To verify that the resource is now added to the web.xml file, click the Source tab located forth the superlative of the editor. Notice that the following <`resource-ref`> tags are at present included.

                  <resources-ref>     <description>Database for IFPWAFCAD application</clarification>     <res-ref-proper noun>jdbc/IFPWAFCAD</res-ref-name>     <res-type>javax.sql.ConnectionPoolDataSource</res-type>     <res-auth>Container</res-auth>     <res-sharing-scope>Shareable</res-sharing-scope> </resources-ref>                

Calculation the database driver'south JAR file to the server

Adding the database driver's JAR file is some other stride that is vital to enabling the server to communicate with your database. Usually, you would need to locate your database driver'due south installation directory and copy the mysql-connector-java-v.1.6-bin.jar file from the driver's root directory into the library folder of the server y'all are using. Fortunately, the IDE's server management is able to find at deployment whether the JAR file has been added - and if not, it does so automatically.

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 bank check to make up one's mind whether any drivers are required for the server's deployed applications. In the case of MySQL, if the commuter is required and it is missing, the IDE'south bundled driver is deployed to the advisable location on the server.

1. Choose Tools > Servers to open the Servers managing director. Select the GlassFish server in the left pane.

2. In the main pane, select the Enable JDBC Driver Deployment option.

Effigy xvi. JDBC Commuter Deployment selection enables automatic driver deployment

1. 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 example 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. Every bit shown in the image above, the driver JAR file should be located within domain1, which is the default domain created upon installing the GlassFish server.

2. Click Close to go out the Servers director.

3. On your estimator, navigate to the GlassFish server installation directory and drill into the domains > domain1 > lib subfolder. Because yous should have already deployed the IFPWAFCAD project to the server, you lot should see the mysql-connector-java-5.1.6-bin.jar file. If you do non see the driver JAR file, perform the following step.

4. 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-iv; ⌘-four on Mac). The output indicates that the MySQL driver is deployed to a location in the GlassFish server.

Figure 17. Output window indicates that the MySQL commuter has been deployed

Now, if you return to the domain1/lib subfolder on your estimator, you tin meet that the mysql-connector-java-5.i.six-bin.jar file has been automatically added.

Adding Dynamic Logic

Returning to the index.jsp and response.jsp placeholders that you created earlier in the tutorial, you lot can now implement the JSTL lawmaking that enables pages to generate content dynamically, i.east., based on user input. To do so, perform the following three tasks.

1. Add the JSTL library to the projection's classpath

2. Implement JSTL code

Adding the JSTL library to the project'southward classpath

You lot can apply the JavaServer Pages Standard Tag Library (JSTL) to access and display data taken from the database. The GlassFish server includes the JSTL library past default. You can verify this by expanding the GlassFish Server node under the Libraries node in the Projects window, and searching for the javax.servlet.jsp.jstl.jar library. (Older versions of the GlassFish server use the jstl-impl.jar library.) Considering the GlassFish server libraries are by default added to your project'due south classpath, yous do non have to perform any steps for this task.

JSTL provides the following four basic areas of functionality.

• core: mutual, structural tasks such every bit iterators and conditionals for handling flow control

• fmt: internationalization and localization message formatting

• sql: unproblematic database admission

• xml: treatment of XML content

This tutorial focuses on usage of the cadre and sql tag libraries.

Implementing JSTL code

Now you can implement the lawmaking that dynamically retrieves and displays data for each folio. Both pages require that you implement an SQL query that utilizes the information source created earlier in the tutorial.

The IDE provides several database-specific JSTL snippets which you can select from the Palette (Ctrl-Shift-viii; ⌘-Shift-8 on Mac).

Effigy eighteen. Choose database-specific JSTL snippets from the Palette

index.jsp

In order to dynamically display the contents of the form in index.jsp, you need to access all name`s from the `Subject database table.

1. Hover your mouse over the DB Study detail in the Palette.

Figure 19. Type 'db' and press Ctrl-Space to access database-specific JSTL snippets

The DB Report particular uses the <sql:query> tag to create an SQL query, so it uses the <c:forEach> tag to loop through the query'due south resultset and output the retrieved data.

1. Place your cursor to a higher place the <%@page …​ %> declaration (line 7), so double-click the DB Written report particular in the Palette. In the dialog that displays, enter the following details:

   • Variable Name: subjects

   • Scope: folio

   • Data Source: jdbc/IFPWAFCAD

   • Query Statement: SELECT subject_id, name FROM Subject

Figure xx. Use the Insert DB Report dialog to specify query-specific details

1. Click OK. The following content is generated in the index.jsp file. (New content shown in assuming.)

                    *<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%> <%@taglib prefix="sql" uri="http://coffee.lord's day.com/jsp/jstl/sql"%>* <%--     Certificate   : index     Author     : nbuser --%>  *<sql:query var="subjects" dataSource="jdbc/IFPWAFCAD">     SELECT subject_id, name FROM Subject </sql:query>  <table border="1">     <!-- cavalcade 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> </tabular array>*  <%@page contentType="text/html" pageEncoding="UTF-8"%> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"     "http://world wide web.w3.org/TR/html4/loose.dtd">                  

Note that the IDE automatically added taglib directives needed for the JSTL tags used in the generated content (<sql:query> and <c:forEach>). A taglib directive declares that the JSP folio uses custom (i.e., JSTL) tags, names the tag library that defines them, and specifies their tag prefix.

1. Run the project to meet how it displays in a browser. Right-click the project node in the Projects window and choose Run.

When you lot choose Run, the IDE deploys the project to the GlassFish server, the alphabetize page is compiled into a servlet, and the welcome page opens in your default browser. The lawmaking generated from the DB Report particular creates the following table in the welcome page.

Figure 21. Use DB Written report for quick prototyping of database table data

As you tin see, the DB Report detail enables you to rapidly test your database connection, and enables you to view tabular array data from the database in your browser. This tin be specially useful when prototyping.

The post-obit steps demonstrate how to integrate the generated code into the HTML drop-downwardly list you lot created earlier in the tutorial.

1. Examine the column data in the generated code. Two <c:forEach> 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, information for the entire table is displayed.

2. Integrate the <c:forEach> tags into the HTML course every bit follows. The value of each item becomes the subject_id, and the output text becomes the name, as recorded in the database. (Changes are displayed in bold).

                    <form action="response.jsp">     <strong>Select a subject:</strong>     <select name="subject_id">         *<c:forEach var="row" items="${subjects.rowsByIndex}">             <c:forEach var="column" items="${row}">*                 <pick *value="<c:out value="${column}"/>"*>*<c:out value="${column}"/>*</option>             *</c:forEach>         </c:forEach>*     </select>     <input type="submit" value="submit" name="submit" /> </form>                  

An culling, simpler way to integrate the <c:forEach> tags into the HTML form would be equally follows.

                    <form activity="response.jsp">     <stiff>Select a field of study:</strong>     <select proper noun="subject_id">         *<c:forEach var="row" items="${subjects.rows}">*             <choice *value="${row.subject_id}"*>*${row.name}*</selection>         *</c:forEach>*     </select>     <input type="submit" value="submit" name="submit" /> </course>                  

In either case, the <c:forEach> tags loop through all subject_id and name values from the SQL query, and insert each pair into the HTML <selection> tags. In this style, the form'south drib-downwardly listing is populated with data.

1. Delete the tabular array that was generated from the DB Report particular. (Deletion shown beneath as strike-through text .)

                    <%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%> <%@taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql"%> <%--     Document   : alphabetize     Created on : December 22, 2009, 7:39:49 PM     Author     : nbuser --%>  <sql:query var="subjects" dataSource="jdbc/IFPWAFCAD">     SELECT subject_id, name FROM Subject </sql:query>  *[.line-through]#<table border="i">     <!-- cavalcade headers -->     <tr>     <c:forEach var="columnName" items="${subjects.columnNames}">         <th><c:out value="${columnName}"/></thursday>     </c:forEach> </tr> <!-- column information --> <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> </tabular array>#*  <%@folio contentType="text/html" pageEncoding="UTF-8"%> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML four.01 Transitional//EN"     "http://www.w3.org/TR/html4/loose.dtd">                  

1. Save your changes (Ctrl-Southward; ⌘-S on Mac).

2. Refresh the welcome page of the project in your browser.

Note that the drop-downwardly listing in the browser now contains subject field names that were retrieved from the database.

You lot practice not need to redeploy your project because compile-on-salve is enabled for your project by default. This means that when you modify and salvage a file, the file is automatically compiled and deployed and you do non need to recompile the entire project. You tin can enable and disable compile-on-save for your project in the Compiling category of the Properties window of the project.

response.jsp

The response folio provides details for the advisor who corresponds to the subject called in the welcome folio. The query you create must select the counselor record whose counselor_id matches the counselor_idfk from the selected subject record.

1. Place your cursor above the <%@page …​ %> proclamation (line 7), and double-click DB Query in the Palette to open the Insert DB Query dialog box.

2. Enter the following details in the Insert DB Query dialog box.

   • Variable Name: counselorQuery

   • Scope: page

   • Data Source: jdbc/IFPWAFCAD

   • Query Statement: SELECT * FROM Subject, Counselor WHERE Counselor.counselor_id = Subject.counselor_idfk AND Field of study.subject_id = ? <sql:param value="${param.subject_id}"/>

Effigy 22. Use the Insert DB Query dialog to specify query-specific details

1. Click OK. The following content is generated in the response.jsp file. (New content shown in assuming.)

                    *<%@taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql"%>* <%--     Document   : response     Created on : December 22, 2009, 8:52:57 PM     Author     : nbuser --%>  *<sql:query var="counselorQuery" dataSource="jdbc/IFPWAFCAD">     SELECT * FROM Field of study, Counselor     WHERE Counselor.counselor_id = Discipline.counselor_idfk     AND Subject field.subject_id = ? <sql:param value="${param.subject_id}"/> </sql:query>*  <%@page contentType="text/html" pageEncoding="UTF-eight"%> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"     "http://www.w3.org/TR/html4/loose.dtd">                  

Note that the IDE automatically added the taglib directive needed for the <sql:query> tag. Besides, notation that y'all used an <sql:param> tag directly within the query. Because this query relies on the subject_id value that was submitted from index.jsp, yous tin can extract the value using an EL (Expression Language) statement in the form of ${param.subject_id}, and so pass it to the <sql:param> tag so that it tin can be used in place of the SQL question marking (?) during runtime.

1. Use a <c:set up> tag to set up a variable that corresponds to the start tape (i.e., row) of the resultset returned from the query. (New content shown in bold.)

                    <sql:query var="counselorQuery" dataSource="jdbc/IFPWAFCAD">     SELECT * FROM Subject, Counselor     WHERE Counselor.counselor_id = Subject.counselor_idfk     AND Bailiwick.subject_id = ? <sql:param value="${param.subject_id}"/> </sql:query>  *<c:set var="counselorDetails" value="${counselorQuery.rows[0]}"/>*                  

Although the resultset returned from the query should but incorporate a single record, this is a necessary pace because the page needs to access values from the record using EL (Expression Language) statements. Think that in index.jsp, you lot were able to admission values from the resultset merely by using a <c:forEach> tag. However, the <c:forEach> tag operates by setting a variable for the rows contained in the query, thus enabling you lot to extract values past including the row variable in EL statements.

1. Add the taglib directive for the JSTL core library to the summit of the file, so that the <c:ready> tag is understood. (New content shown in bold.)

                    *<%@taglib prefix="c" uri="http://java.lord's day.com/jsp/jstl/core"%>* <%@taglib prefix="sql" uri="http://coffee.sun.com/jsp/jstl/sql"%>                  

1. In the HTML markup, replace all placeholders with EL statements code that display the information held in the counselorDetails variable. (Changes below shown in assuming):

                    <html>     <head>         <meta http-equiv="Content-Blazon" content="text/html; charset=UTF-viii"/>         <link rel="stylesheet" type="text/css" href="style.css">         <championship>*${counselorDetails.name}*</championship>     </caput>      <trunk>         <table>             <tr>                 <th colspan="2">*${counselorDetails.name}*</th>             </tr>             <tr>                 <td><strong>Description: </stiff></td>                 <td><span way="font-size:smaller; font-mode:italic;">*${counselorDetails.description}*</span></td>             </tr>             <tr>                 <td><strong>Counselor: </strong></td>                 <td><stiff>*${counselorDetails.first_name} ${counselorDetails.nick_name} ${counselorDetails.last_name}*</strong>                     <br><span way="font-size:smaller; font-style:italic;">                     <em>fellow member since: *${counselorDetails.member_since}*</em></span></td>             </tr>             <tr>                 <td><potent>Contact Details: </strong></td>                 <td><strong>email: </strong>                     <a href="mailto:*${counselorDetails.email}*">*${counselorDetails.e-mail}*</a>                     <br><stiff>phone: </strong>*${counselorDetails.telephone}*</td>             </tr>         </table>     </body> </html>                  

Running the Completed Application

You've at present completed the application. Attempt running information technology again to run across how it displays in a browser. Note that because of NetBeans' Compile on Salvage feature, you do non need to worry about compiling or redeploying the application. When you run a project, you can be sure the deployment contains your latest changes.

Click the Run Projection ( ) button in the main toolbar. The index.jsp page opens in the IDE's default browser.

When alphabetize.jsp displays in the browser, select a discipline from the drop-downwards list and click submit. You should now be forwarded to the response.jsp page, showing details corresponding to your selection.

Effigy 23. response.jsp displayed in a browser, showing information retrieved from database

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. Information technology 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 ways of accessing and displaying information dynamically.

Troubleshooting

Near of the problems that occur with the tutorial awarding are due to advice difficulties between the GlassFish Server Open Source Edition and the MySQL database server. If your awarding does not brandish correctly, or if you are receiving a server error, the following examinations may exist useful.

• Do database resource exist?

• Do the connection pool and information source exist on the server?

• Is the MySQL Connector/J driver accessible to the GlassFish server?

• Is the database password-protected?

• Are the connection pool backdrop correctly fix?

Do database resources be?

Use the IDE'south Services window (Ctrl-5; ⌘-5 on Mac) to ensure that the MySQL server is running, and that MyNewDatabase is accessible and contains appropriate table data.

• To connect to the MySQL database server, right-click the MySQL Server node and choose Connect.

• If a connection node ( ) for MyNewDatabase does non brandish in the Services window, yous can create a connexion past right-clicking the MySQL driver node ( ) and choosing Connect Using. Enter the required details in the dialog that displays.

Figure 24. Establish a database connection in the IDE using the New Database Connexion dialog

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 (east.yard., jdbc:mysql://localhost:3306/MyNewDatabase) you can paste it into the Show JDBC URL field, and the remaining dialog fields become automatically populated. * To ensure that the Subject field and Counselor tables be and that they contain sample data, expand the MyNewDatabase connection node ( ) and locate the MyNewDatabase catalog node ( ). Expand the catalog node to view existing tables. You tin can view table data by right-clicking a table node and choosing View Data.

Figure 25. View table information by choosing View Information from the right-click menu of a database table node

Do the connexion pool and information source exist on the server?

Subsequently deploying the application to the GlassFish server, the glassfish-resources.xml contained in the projection should instruct the server to create a JDBC resources and connexion pool. You can determine whether these exist from the Servers node in the Services window.

• Aggrandize the Servers > the GlassFish Server > Resource node. Expand JDBC Resources to view the jdbc/IFPWAFCAD data source that was created from glassfish-resource.xml. Expand the Connection Pools node to view the IfpwafcadPool connection pool that was created from glassfish-resource.xml. (This is demonstrated above.)

Is the MySQL Connector/J driver accessible to the GlassFish server?

Make sure that the MySQL Connector/J commuter has been deployed to the GlassFish server. (This is discussed in Adding the database driver'southward JAR file to the server.)

• Locate the GlassFish server installation folder on your computer and drill down into the GlassFish domains/domain1/lib subfolder. Hither you should find the mysql-connector-coffee-five.one.vi-bin.jar file.

Is the database password-protected?

The database needs to be password-protected to enable the GlassFish server information source to piece of work properly in this tutorial. If you lot are using the default MySQL root business relationship with an empty countersign, you can fix the countersign from a command-line prompt.

• To set up your password to nbuser , navigate to your MySQL installation'due south bin directory in the command-line prompt and enter the following:

                  shell> mysql -u root mysql> UPDATE mysql.user SET Password = PASSWORD('_nbuser_')     ->     WHERE User = 'root'; mysql> Affluent PRIVILEGES;                

Are the connectedness pool properties correctly set?

Ensure that the connection pool is working correctly for the server.

1. Open up the Services window (Ctrl-five; ⌘-five on Mac) and expand the Servers node.

2. Right-click the GlassFish server node and choose View Admin Console.

3. Enter the username and password if you are prompted. You can view the username and password in the Servers director.

4. In the tree on the left side of the console, expand the Resources > JDBC > JDBC Connexion Pools > IfpwafcadPool node. Details for the IfpwafcadPool connexion puddle display in the main window.

5. Click the Ping button. If the connection puddle is gear up correctly, y'all volition see a 'Ping Succeeded' message.

Figure 26. Examination your connection pool past clicking Ping in the GlassFish server Admin Console

1. If the ping fails, click the Boosted Backdrop tab and ensure that the listed property values are correctly set.

How To Make A Simple Web Page,

Source: https://netbeans.apache.org/kb/docs/web/mysql-webapp.html

Posted by: farrquir1968.blogspot.com

Share this post