< Java Web Application 2 | Main | Java Web Application & MySQL 2 >

 


 

 

A Simple Web Application Using a MySQL Database 1

 

 

Contents:

  1. Machine specification used in this tutorial

  2. Creating a Database Using MySQL Command Line Client

  3. Making a Connection Using NetBeans

  4. Running SQL Script: Creating Tables and Populate Sample Data

  5. Apache Tomcat and GlassFish V2 Web Server

  6. Planning the Structure

  7. Creating a New Project

  8. Preparing the Interface

  9. The index.jsp File

  10. The response.jsp File

  11. The style.css File

  12. Setting up a Connection Pool

  13. Setting up a JNDI Datasource

  14. Tomcat

  15. GlassFish

  16. Referencing the Datasource from the Application

  17. Adding the Database Driver's JAR File to the Server

  18. Tomcat

  19. GlassFish

  20. Adding Dynamic Logic

  21. Adding the JSTL Library to the Project's Classpath

  22. Tomcat

  23. GlassFish

  24. Adding taglib Directives to the JSP Pages

  25. Adding JSP and JSTL Code

  26. The index.jsp File

  27. The response.jsp File

  28. Deploying and Running the Project

 

 

This tutorial 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 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 apply their knowledge using a MySQL database.

MySQL is a popular Open Source database management system commonly used in web applications due to its speed, flexibility and reliability. MySQL already acquired by Sun Microsystems as their latest product offer. MySQL employs SQL, or Structured Query Language, for accessing and processing data contained in databases.

This tutorial continues from the Connecting to a MySQL Database tutorial and assumes that you already have a connection to a MySQL database created and registered in NetBeans IDE. The table data that is included in ifpwafcad.sql is also required for this tutorial. This SQL file creates two tables, Subject and Counselor, and then populates them with sample data. Save this file to a local directory, then open it in NetBeans IDE and run it on the MySQL database. The database used in this tutorial is named MyNewDatabase.

The original tutorial can be found at netbeans.org. All credits must go to the original authors. In this tutorial we try to refine all the steps to more details using screen shots.

 

Machine specification used in this tutorial are:

  • Intel Pentium Core 2 Duo, 2.2 GHz,

  • Windows XP Pro SP2 + periodical patches + periodical updates…

  • 2 GB DDR2 RAM

  • 160 GB SATA HDD

  • 17” SyncMaster 713N monitor.

Creating a Database Using MySQL Command Line Client

 

1.      Let create a MySQL database using MySQL Command Line Client.

 

Step-by-step Java web application using MySQL database development screeshots

 

2.      Provide the password for root user login.

 

Step-by-step Java web application using MySQL database development screeshots

 

3.      Create a database named MyNewDatabase and then exit.

 

CREATE DATABASE MyNewDatabase;

 

Step-by-step Java web application using MySQL database development screeshots

 

Making a Connection Using NetBeans

 

1.      Open NetBeans IDE. In the Services pane, expand Database node > expand the Drivers node > select MySQL (Connector/J driver) > right click mouse > select Connect Using.

 

Step-by-step Java web application using MySQL database development screeshots

 

2.      Fill in the database URL as shown below and the root password. Click OK.

 

Step-by-step Java web application using MySQL database development screeshots

 

3.      The following Figure shows that the connection to the MyNewDatabase MySQL database was established.

 

Step-by-step Java web application using MySQL database development screeshots

 

4.      You can re-confirm the connection from the following Figure

 

Step-by-step Java web application using MySQL database development screeshots

 

Running SQL Script: Creating Tables and Populate Sample Data

 

1.      Open the SQL Command editor and run the ifpwafcad.sql script.

 

Step-by-step Java web application using MySQL database development screeshots

 

Step-by-step Java web application using MySQL database development screeshots

 

2.      Verify through the Output window there should be no error.

 

Step-by-step Java web application using MySQL database development screeshots

 

3.      Verify through the Services window the created tables and data.

 

Step-by-step Java web application using MySQL database development screeshots

 

Step-by-step Java web application using MySQL database development screeshots

 

Step-by-step Java web application using MySQL database development screeshots

 

Step-by-step Java web application using MySQL database development screeshots

 

Step-by-step Java web application using MySQL database development screeshots

 

Step-by-step Java web application using MySQL database development screeshots

 

Apache Tomcat and GlassFish V2 Web Server

 

§         The Web and Java EE installation enables you to optionally install the Apache Tomcat servlet container 6.0.14, and the GlassFish V2 application server. You must install one of these to work through this tutorial.

 

Step-by-step Java web application using MySQL database development screeshots

 

You can add web server by right-clicking the Server node and select Add Server…

 

Step-by-step Java web application using MySQL database development screeshots

 

Step-by-step Java web application using MySQL database development screeshots

 

You can customize the web server through the Properties page.

 

Step-by-step Java web application using MySQL database development screeshots

 

Step-by-step Java web application using MySQL database development screeshots

 

§         The MySQL Connector/J JDBC Driver, necessary for communication between Java platforms and the MySQL database driver, is included in NetBeans IDE 6.0.

 

Planning the Structure

 

Simple web applications can be designed using a two-tier architecture, in which the application communicates directly with a data source using the Java Database Connectivity API. A user's requests are sent to a database, and the results are sent directly back to the user. Two-tier architectures can be easily mapped to a client-server configuration, where a user's browser serves as the client, and a remote database reachable over the Internet corresponds to the server.

The application you build in this tutorial involves the creation of two JavaServer Pages. In each of these pages, you add basic HTML to implement a simple interface, followed by SQL tags provided by JSTL technology in order to query the database. Consider the following client-server scenario:

 

Step-by-step Java web application using MySQL database development screeshots

 

The welcome page (index.jsp) presents the user with a simple HTML form. When a client requests the index.jsp page, the JSP code contained therein is parsed, and data from the Subject database table is gathered, added to the page, and sent to the client. The user makes a selection in the provided HTML form and submits, which causes the client to make a request for response.jsp. When response.jsp is parsed, data from both the Subject and Counselor tables is gathered and inserted into the page. Finally, the page is returned to the client and the user views data based upon his or her selection.

 

Creating a New Project

 

In order to implement the scenario described above, you develop a simple application for a fictitious organization named IFPWAFCAD, or The International Former Professional Wrestlers' Association for Counseling and Development. The application enables a user to choose a counseling subject from a drop-down list (index.jsp), then retrieves data from the MySQL database and returns the information to the user (response.jsp):

 

Step-by-step Java web application using MySQL database development screeshots

 

Step-by-step Java web application using MySQL database development screeshots

 

Create a new project in the IDE:

 

1.      Start NetBeans IDE and choose File > New Project (Ctrl-Shift-N) from the main menu. Under Categories select Web; under Projects select Web Application. Click Next.

 

Step-by-step Java web application using MySQL database development screeshots

 

Step-by-step Java web application using MySQL database development screeshots

 

2.      In Project Name, enter IFPWAFCAD. From the Server drop-down list, select the server you plan work with. Leave all other settings at their defaults and click Finish. The IDE creates a project template for the entire application, and opens an empty JSP page (index.jsp) in the Source Editor. index.jsp serves as the entry point for the application. The new project is structured according to Sun Java BluePrints guidelines.

 

Step-by-step Java web application using MySQL database development screeshots

 

Step-by-step Java web application using MySQL database development screeshots

 

Step-by-step Java web application using MySQL database development screeshots

 

Preparing the Interface

 

Begin by preparing a simple interface for the two pages. Both index.jsp and response.jsp implement an HTML table to display data in a structured fashion. index.jsp also requires an HTML form that includes a drop-down list.

 

The index.jsp File

 

Make sure index.jsp is opened in the Source Editor. If it is not already open, double-click index.jsp from IFPWAFCAD > Web Pages in the Projects window.

 

1.      In the Source Editor, change the text between the <title> tags to IFPWAFCAD Homepage. Also change the text between the <h1> tags to Welcome to IFPWAFCAD, the International Former Professional Wrestlers' Association for Counseling and Development!. Note: For further content, you can either add HTML elements to the page using the IDE's palette, as demonstrated below, or just copy and paste the code provided in step 8 directly into your index.jsp page.

 

Step-by-step Java web application using MySQL database development screeshots

 

 

2.      Open the IDE's Palette by choosing Window > Palette (Ctrl-Shift-8) from the main menu. Hover your pointer over the Table icon from the HTML category and note that the default code for the item displays:

 

Step-by-step Java web application using MySQL database development screeshots

 

Click the icon, and drag and drop a table into the page in the Source Editor, to a point just after the </h1> closing tags.

 

Step-by-step Java web application using MySQL database development screeshots

 

 In the Insert Table dialog that displays, specify the following criteria, and then click OK:

  1. Rows: 2

  2. Columns: 1

  3. Border Size: 0

Step-by-step Java web application using MySQL database development screeshots

 

Step-by-step Java web application using MySQL database development screeshots

 

Press Alt+Shift+F to reformat the code. The HTML table code is generated and added to your page.

 

Step-by-step Java web application using MySQL database development screeshots

 

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

 

  <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>

 

Step-by-step Java web application using MySQL database development screeshots

 

4.      For the bottom row of the table, drag and drop an HTML form from the Palette into the page, directly between the second pair of <td> tags. In the Action text box, type in response.jsp, then click OK.

 

Step-by-step Java web application using MySQL database development screeshots

 

Step-by-step Java web application using MySQL database development screeshots

 

Step-by-step Java web application using MySQL database development screeshots

 

Step-by-step Java web application using MySQL database development screeshots

 

5.      Type in the following text between the <form> tags of the form you just created:

 

<strong>Select a subject:</strong>

 

Step-by-step Java web application using MySQL database development screeshots

 

6.      Drag and drop a drop-down list from the Palette to a point just after the text you just added (still in the form tag. In the Insert Drop-down dialog that displays, type in subject_id for the Name text field, and click OK. The number of options for the drop-down is currently not important. Later in the tutorial you will add JSTL tags that dynamically generate options based on the data gathered from the Subject database table. Click OK, and note that the code for the drop-down list is added to the form.

 

Step-by-step Java web application using MySQL database development screeshots

 

Step-by-step Java web application using MySQL database development screeshots

 

Step-by-step Java web application using MySQL database development screeshots

 

Step-by-step Java web application using MySQL database development screeshots

 

7.      Add a submit button to the form by dragging a button from the Palette to a point just after the drop-down list you just added. Enter submit for both the Label and Name text fields, then click OK.

 

Step-by-step Java web application using MySQL database development screeshots

 

Step-by-step Java web application using MySQL database development screeshots

 

Step-by-step Java web application using MySQL database development screeshots

 

8.      To format your code, right-click in the Source Editor, and choose Format (Alt-Shift-F). Your code is automatically formatted, and should now look similar to the following:

 

  <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>

 

 

Step-by-step Java web application using MySQL database development screeshots

 

To view this page in a browser, right-click in the Source Editor and choose Run File (Shift-F6). When you do this, the JSP page is automatically compiled and deployed to your server. The IDE opens your default browser to display the page from its deployed location:

 

Step-by-step Java web application using MySQL database development screeshots

 

The response.jsp File

 

In order to prepare the interface for response.jsp you must first create the file in your application. Note that most of the content that displays in this page is generated dynamically using JSP technology. Therefore, in the following steps you add placeholders which you will later substitute for the JSP code.

 

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

 

Step-by-step Java web application using MySQL database development screeshots

 

2.      In the JSP File Name field, enter response. Note that Web Pages is currently selected for the Location field, meaning that the file will be created in the same directory as the index.jsp welcome page.

3.      Accept all other defaults and click Finish. A template for the new response.jsp page is generated and opens in the Source Editor. A new JSP node also displays under Web Pages in the Projects window:

 

Step-by-step Java web application using MySQL database development screeshots

 

Step-by-step Java web application using MySQL database development screeshots

 

4.      In the Source Editor, change the title to IFPWAFCAD - {placeholder}.

 

Step-by-step Java web application using MySQL database development screeshots

 

5.      Change the page header to IFPWAFCAD Information.

 

Step-by-step Java web application using MySQL database development screeshots

 

6.      Copy and paste the following HTML table into the body of the page:

 

   <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>

 

Step-by-step Java web application using MySQL database development screeshots

 

To view this page in a browser, right-click in the Source Editor and choose Run File (Shift-F6). The page is compiled, deployed to your server, and opened in your default browser. Your response.jsp page should display similar to the following:

 

Step-by-step Java web application using MySQL database development screeshots

 

Step-by-step Java web application using MySQL database development screeshots

 

Continue on next tutorial, part 2

 

 

 

 

 

 

 

 

Java Web Application With MySQL: Part 1 | Part 2


 

< Java Web Application 2 | Main | Java Web Application & MySQL 2 >