<Java Web Application & MySQL 1 |Main |Java Web Application & Derby Database >


 

 

 

A Simple Web Application Using a MySQL Database 2

 

 

 

 

The style.css File

 

Create a simple stylesheet that enhances the interface display:

 

1.     Right-click the IFPWAFCAD project node in the Projects window and choose New > Other. In the New File wizard, make sure Web is selected under Categories, then choose Cascading Style Sheet and click Next.

 

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

 

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

 

2.     Type style for CSS File Name and click Finish. The IDE creates an empty CSS file and places it in the same project location as index.jsp and response.jsp. Note that style.css now displays within the project in the Projects window, and the file opens in the Source Editor.

 

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

 

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

 

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

 

body {

    font-family: Verdana, Arial, sans-serif;

    font-size: smaller;

    padding: 50px;

    color: #555;

}

 

h2 {

    text-align: left;

    letter-spacing: 6px;

    font-size: 1.4em;

    color: #be7429; 

    font-weight: normal;

    width: 450px;

}

 

table {

    width: 550px;

    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;

}

 

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

 

4.     Link the stylesheet to index.jsp and response.jsp:

 

In both pages, add the following line between the <head> tags:

 

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

 

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

 

 

This document assumes that you understand how the stylesheet functions, and how it affects corresponding HTML elements found in index.jsp and response.jsp. When working with CSS in the IDE, you can take advantage of the CSS Style Builder and CSS Preview. Together, these tools provide extensive support for creating style rules and viewing elements when coupled with style attributes. For example, place your cursor within the h2 rule in style.css, then open CSS Preview (Window > Other):

 

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

 

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

 

CSS Preview demonstrates how an element renders in a browser. Also note that the preview automatically refreshes as you make changes to a rule, providing a real-time textual representation of style elements from the IDE.

 

Setting up a Connection Pool

 

The most efficient way to implement communication between the server and database is to set up a database connection pool. 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.

  1. Setting up a JNDI Datasource.

  2. Referencing the Datasource from the Application.

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

Setting up a JNDI Datasource

 

Both Tomcat and GlassFish contain Database Connection Pooling (DBCP) libraries that provide connection pooling functionality in a way that is transparent to you as a developer. In either case, you need to configure a JNDI Datasource for the server that creates an interface which your application can use for connection pooling. Depending on whether you're using Tomcat or GlassFish, do the following (either one). In this case we are going to use glassfish web server.

 

Tomcat

 

Configure a JNDI Datasource in Tomcat by adding a declaration for your resource to the application's context.xml file. This is the application's context container, which enables you to specify application meta-data necessary for the server in order to deploy and run the application. There are various locations where you can specify context elements, such as your server's global$CATALINA_HOME/conf/context.xml file. By adding resource declarations to your application's context.xml file however, you limit the resource to that application, and do not need to configure anything within the server itself. For more information, see theApache Tomcat Configuration Reference.

 

1.     From the Projects window, open the application's context container in the Source Editor by double-clicking the Web Pages > META-INF > context.xml file.

2.     Add the following <Resource> tags (changes below in bold) as a declaration for the JNDI resource. Your context.xml file should now look as follows:

 

<?xml version="1.0" encoding="UTF-8"?>

 

<Context path="/IFPWAFCAD">

    <Resource name="jdbc/IFPWAFCAD" auth="Container" type="javax.sql.DataSource"

        maxActive="100" maxIdle="30" maxWait="10000"

        username="root" password="nbuser" driverClassName="com.mysql.jdbc.Driver"

        url="jdbc:mysql://localhost:3306/MyNewDatabase"/>

</Context>

 

For a more thorough explanation of JNDI DataSource configuration, including a description of the parameters used in the above resource declaration, refer to the Apache Tomcat 6.0JNDI Datasource HOW-TO.

 

GlassFish

 

The IDE provides enhanced support for GlassFish, enabling you to specify resources using a wizard:

 

1.     In the Projects window, right-click the Server Resources node and choose New > Other. The New File wizard opens. Under Categories, select GlassFish. Under File Types, select JDBC Resource. Click Next.

 

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

 

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

 

2.     Under General Attributes, choose the Create New JDBC Connection Pool option, then in the JNDI Name text field, type in jdbc/IFPWAFCAD. Click Next.

 

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

 

3.     Click Next again to skip Additional Properties, then in Step 4, type in IfpwafcadPoolfor JDBC Connection Pool Name. Make sure the Extract from Existing Connection option is selected, and choose jdbc:mysql://localhost:3306/MyNewDatabase from the drop-down list. Click Next.

 

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

 

4.     Accept all default settings for Add Connection Pool Properties, then click Finish to exit the wizard.

 

By completing the wizard, you declared a new datasource and connection pool for the application. In the Projects window, open the newly created Server Resources > sun-resources.xml file and note that <resources> tags have been added which include the data you specified in the wizard.

 

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

 

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

 

1.     In the Projects window, right-click the IFPWAFCAD project node and choose Undeploy and Deploy.

 

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

 

2.     Open the Services window (Ctrl-5) and expand the Servers > GlassFish > Resources > JDBC > JDBC Resources and Connection Pools nodes. Note that the new datasource and connection pool are now displayed:

 

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

 

Referencing the Datasource from the Application

 

You need to reference the JNDI resource you just configured from the web application. To do so, you can create an entry in the application's deployment descriptor (web.xml). 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. To reference the JNDI Datasource in the application's deployment descriptor:

 

1.     In the Projects window, expand the Web Pages > WEB-INF subfolder and double-click web.xml. A graphical editor for the file displays in the Source Editor.

 

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

 

2.     Click the References tab located along the top of the Source Editor. Expand the Resource References heading, then click Add. The Add Resource Reference dialog opens.

 

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

 

3.     For Resource Name, enter the resource name that you gave when configuring the JNDI Datasource for the server above (jdbc/IFPWAFCAD). For Description, enter the resource URL (jdbc:mysql://localhost:3306/MyNewDatabase). Note that the default resource type is javax.sql.DataSource. Leave all fields that are provided by default and click OK. The new resource is added under the Resource References heading:

 

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

 

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

 

To verify that the resource is now added to the web.xml file, click the XML tab located along the top of the Source Editor you'll see that the following <resource-ref>tags are now included:

 

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

 

Adding the Database Driver's JAR File to the Server

 

Adding the database driver's JAR file is another step that is vital to enabling the server to communicate with your database. Ordinarily, you would need to locate your database driver's installation directory and copy the mysql-connector-java-5.x-bin.jar file from the driver's root directory into the library folder of the server you are using. Fortunately, the IDE is able to detect at deployment whether the JAR file has been added - and if not, it does so automatically.

In order to demonstrate this, deploy your application to the server you are using (in the Projects window, choose Undeploy and Deploy from the right-click menu of the project node) and, depending on the server you are using, do the following:

 

Tomcat

Open the Servers window (Tools > Servers from main menu). Make sure Apache Tomcat is selected in the left panel, then click the Classes tab. Note that a JAR file for a MySQL driver is added to the library:

 

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

 

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

 

Note: The files listed in the Classes tab correspond to the Tomcat installation directory's lib subfolder.

 

GlassFish

Locate the GlassFish installation directory and navigate to the domains > domain1 > lib > subfolder. Note that the mysql-connector-java-5.x-bin.jar file has been added here. When you connect to GlassFish in NetBeans IDE, you are actually connecting to an instance of the application server. Each instance runs applications in a unique domain, and so here the JAR file is located in domain1, which is the default domain created upon installing GlassFish.

 

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

 

Adding Dynamic Logic

 

If you return to the index.jsp and response.jsp placeholders created earlier in the tutorial, you can add JSP and JSTL code to enable pages to generate content dynamically, i.e. based on user input. To do so, you need to perform the following 3 steps:

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

  2. Adding taglib Directives to the JSP Pages

  3. Adding JSP and JSTL Code

Adding the JSTL Library to the Project's Classpath

 

In order to make better use of the JSP resources at your disposal, you can make use of the JavaServer Pages Standard Tag Library (JSTL) to access and display data taken from the Logic Layer. This library comes bundled with the IDE. You therefore need to make sure the JSTL library is added to the web project's compilation classpath, then add the relevant taglib directives to each of the JSP pages. This allows the server we are using to identify the tags when it reads them from the JSP pages. Depending on whether you are using Tomcat or GlassFish, do the following:

 

Tomcat

 

1.     In the Projects window, right-click the project's Libraries node and choose Add Library. Select the JSTL 1.1 library and click Add Library.

 

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

 

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

 

2.     Expand the Libraries node and note that two new JAR files have been added: the JSTL library's standard.jar, and the jstl.jar. You can further expand the standard.jar file to view JSTL's four basic areas of functionality:

 

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

 

The JSTL library includes four basic areas of functionality:

  1. core: common, structural tasks such as iteration and conditionals.

  2. fmt: internationalization and locale-sensitive formatting tags.

  3. sql: the SQL tags.

  4. xml: manipulation of the XML documents.

GlassFish

 

Do nothing! GlassFish includes the JSTL library in its own library. You can verify this by expanding the Libraries > GlassFish node. The appserv-jstl.jar file defines all standard tags in the JSTL library.

 

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

 

Adding taglib Directives to the JSP Pages

 

Regardless of what server you are using, you need to add the necessary taglib directives to JSP pages:

<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>

<%@taglib uri="http://java.sun.com/jsp/jstl/sql" prefix="sql"%>

 

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

 

 

Adding JSP and JSTL Code

 

Finally, add the code to each page. Both pages require that you implement an SQL query that utilizes the JSTL <sql:query> tags and the datasource created earlier in the tutorial.

 

The index.jsp File

 

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

 

1.     Add the following SQL query beneath the taglib directives you added in the previous step:

 

<sql:query var="subjects" dataSource="jdbc/IFPWAFCAD">

    SELECT subject_id as id, name FROM Subject

</sql:query>

 

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

 

The JSTL<sql:query> tags enable you to use SQL query language directly in a JSP page. A resultset is generated from the query, and the acquired data can then be inserted into the page using an iterator tag (<c:forEach>) from the JSTL core library.

 

2.     Replace the empty <option> tags in the HTML form with the following iterator (changes in bold):

 

<select name="subject_id">

    <c:forEach var="subject" items="${subjects.rows}">

        <option value="${subject.id}">${subject.name}</option>

    </c:forEach>

</select>

 

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

 

The forEach tag loops through all id and name values from the generated resultset, and inserts each pair into the HTML option tags. In this manner, the form's drop-down list is populated with data.

 

3.     Save changes (Ctrl+S), then right-click in the Source Editor and choose Run File (Shift-F6). The file is compiled and deployed to the server, and index.jsp renders in the browser. The drop-down list now contains subject names that were retrieved from the database:

 

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

 

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

 

The response.jsp File

 

For response.jsp, you need to access data from both the Subject and Counselor tables that correspond to the submitted id provided by the user. This is accomplished using an SQL query and the datasource created earlier in the tutorial:

 

1.     Add the following SQL query beneath the taglib directives you added in the previous step:

 

<sql:query var="counsSubjRs" maxRows="1" dataSource="jdbc/IFPWAFCAD">

    SELECT s.name, s.description,

    CONCAT(c.first_name," ",c.nick_name ," ",c.last_name) as counselor,

    c.member_since as memberSince, c.telephone, c.email

    FROM Subject as s, Counselor as c

    WHERE c.counselor_id = s.counselor_idfk

    AND s.subject_id = ? <sql:param value="${param.subject_id}"/>

</sql:query>

 

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

 

A resultset named counsSubjRs is generated from the above SQL query, which contains all data associated with the subject_id submitted by the user. Note that data from the Counselor table is acquired by mapping counselor_idfk from Subject to counselor_id from Counselor.

 

2.     Add the following declaration beneath the SQL query from the previous step:

 

<c:set var="counsSubj" scope="request" value="${counsSubjRs.rows[0]}"/>

 

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

 

The<c:set> tag enables you to set the resultset to a scoped variable, so that you can retrieve its contents at a later stage.

3.     In the HTML, replace all placeholders with JSP code that allows you to retrieve and display the data held in the counsSubj resultset (Changes below shown in bold):

<html>

    <head>

        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8"/>

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

        <title>${counsSubj.name}</title>

    </head>

 

    <body>

        <table>

            <tr>

                <th colspan="2">${counsSubj.name}</th>

            </tr>

            <tr>

                <td><strong>Description: </strong></td>

                <td><span style="font-size:smaller; font-style:italic;">${counsSubj.description}</span></td>

            </tr>

            <tr>

                <td><strong>Counselor: </strong></td>

                <td><strong>${counsSubj.counselor}</strong>

                    <br><span style="font-size:smaller; font-style:italic;">

                    member since: ${counsSubj.memberSince}</span></td>

            </tr>

            <tr>

                <td><strong>Contact Details: </strong></td>

                <td><strong>email: </strong>

                    <a href="mailto:${counsSubj.email}">${counsSubj.email}</a>

                    <br><strong>phone: </strong>${counsSubj.telephone}</td>

            </tr>

        </table>

    </body>

</html>

 

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

 

Deploying and Running the Project

 

Whether you are using the GlassFish application server or Tomcat, the process for deploying your project is the same. If you installed GlassFish or Tomcat through the IDE download, your server is already registered in the IDE. If you need to make any changes to server settings, or would like to register a different server with the IDE, choose Tools > Servers from the main menu to open the Server Manager. To deploy the IFPWAFCAD project to the server:

 

From the Projects window, right-click the project node and choose Undeploy and Deploy. The IDE automatically compiles the project, starts the server, then deploys the project to it. You can see any output generated in the Output window. The output should complete with a BUILD SUCCESSFUL message.

 

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

 

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

 

To check that the application has indeed been deployed to the server, open the Services window (Ctrl-5) and expand the Servers node. All servers that are registered in the IDE are listed here. For Tomcat, expand Web Applications to view the IPFWAPCAD application. For GlassFish, expand Applications > Web Applications to view the application.

 

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

 

To run the application:

In the Projects window, right-click the IFPWAFCAD project node and choose Run. The index.jsp page opens in the IDE's default browser.

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

 

If you had simply chosen Run to begin with, the IDE would have automatically compiled the project and deployed it to the server prior to running it. When index.jsp displays in the browser, select a subject from the drop-down list and click submit. You should now be forwarded to the response.jsp page, showing details corresponding to your selection:

 

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

 

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

 

 

 

 

Java Web Application With MySQL:Part 1 |Part 2


<Java Web Application & MySQL 1 |Main |Java Web Application & Derby Database >