How to connect html to database with mysql using java

• Planning the Structure
  • index.jsp
  • response.jsp
• Creating a New Project
• Preparing the Web Interface
  • Setting up the welcome page
  • Creating the response page
  • Creating a stylesheet
• Preparing Communication between the Application and Database
  • Setting up a JDBC data source and connection pool
  • Referencing the data source from the application
  • Adding the database driver’s JAR file to the server
• Adding Dynamic Logic
  • Adding the JSTL library to the project’s classpath
  • Implementing JSTL code
• Running the Completed Application
• Troubleshooting
  • Do database resources exist?
  • Do the connection pool and data 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 properties correctly set?
• See Also

Written by Troy Giunipero

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 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 employs SQL, or Structured Query Language, for accessing and processing data 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 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, 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 MyNewDatabase.

Figure 1. Content on this page applies to the NetBeans IDE 7.2, 7.3, 7.4 and 8.0

To follow this tutorial, you need the following software and resources.

Notes:

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

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

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

Planning the Structure

Simple web applications can be designed using a two-tier architecture, 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 MySQL Connector/J JDBC Driver 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).

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, 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[title="Sample structure of a two-tier web application"]

The welcome page (index.jsp) presents the user with a simple HTML form. When a browser requests index.jsp, the JSTL code within the page initiates a query on MyNewDatabase. It retrieves data from the Subject 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 (response.jsp). Again, the JSTL code within the page initiates a query on MyNewDatabase. This time, it retrieves data from both the Subject and Counselor 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.

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.

index.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-N; ⌘-Shift-N on Mac) from the main menu. Select the Java Web category, then select Web Application. Click Next.

Figure 4. Use the IDE’s New Project wizard to create a new project

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.

1. In Project Name, enter IFPWAFCAD. Also, specify the location for the project on your computer. (By default, the IDE places projects in a NetBeansProjects folder located in your home 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 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.

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

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

Java EE 6 and Java EE 7 web projects do not require the use of the web.xml deployment descriptor, and the NetBeans project template does not include the web.xml 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.

Note. You could equally set the project version to Java EE 6 or Java EE 7 and then create a web.xml deployment descriptor. (From the New File wizard, select the Web category, then Standard Deployment Descriptor.)

1. Click Finish. The IDE creates a project 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

Begin by preparing the welcome (index.jsp) and response (response.jsp) 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.

• Setting up the welcome page

• Creating the response page

• Creating a stylesheet

Setting up the welcome page

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

1. In the editor, change the text between the

   *${counselorDetails.name}*
   Description:	*${counselorDetails.description}*
   Counselor:	*${counselorDetails.first_name} ${counselorDetails.nick_name} ${counselorDetails.last_name}* member since: *${counselorDetails.member_since}*
   Contact Details:	email: *${counselorDetails.email}* phone: *${counselorDetails.telephone}*

   Running the Completed Application

   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.

   Click the Run Project (

   ) button in the main toolbar. The index.jsp page opens in the IDE’s default browser.

   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.

   Figure 23. response.jsp displayed in a browser, showing data 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. 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.

   Troubleshooting

   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.

   • Do database resources exist?

   • Do the connection pool and data 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 properties correctly set?

   Do database resources exist?

   Use the IDE’s 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 not display in the Services window, you can create a connection by 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 Connection 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 (e.g., 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 and Counselor tables exist 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 can view table data by right-clicking a table node and choosing View Data.

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

   Do the connection pool and data source exist on the server?

   After deploying the application to the GlassFish server, the glassfish-resources.xml 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.

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

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

   • Locate the GlassFish server installation folder on your computer and drill down into the GlassFish domains/domain1/lib subfolder. Here you should find the mysql-connector-java-5.1.6-bin.jar file.

   Is the database password-protected?

   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 root account with an empty password, you can set the password from a command-line prompt.

   • To set your password to nbuser, navigate to your MySQL installation’s 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> FLUSH PRIVILEGES;

   Are the connection pool properties correctly set?

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

   1. Open the Services window (Ctrl-5; ⌘-5 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 manager.

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

   5. Click the Ping button. If the connection pool is set up correctly, you will see a ‘Ping Succeeded’ message.

   Figure 26. Test your connection pool by clicking Ping in the GlassFish server Admin Console

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

   See Also

   For more information about Java web development, see the following resources.

   • NetBeans Articles and Tutorials

   • Connecting to a MySQL Database in NetBeans IDE. Covers the basics of working with a MySQL database in the IDE.

   • Introduction to JavaServer Faces 2.x. An introductory tutorial describing how to use the JSF framework in a Java web project.

   • Introduction to the Spring Framework. An introductory tutorial describing how to create an MVC web application using the Spring Framework.

   • Java Database Connectivity (JDBC)

   • JDBC Overview

   • Getting Started with the JDBC API

   • The Java Tutorials: JDBC Basics

   • JavaServer Pages Standard Tag Library (JSTL)

   • JavaServer Pages Standard Tag Library (official product page)

   • Java Naming and Directory Interface (JNDI)

   • Java SE Core Technologies - Java Naming and Directory Interface

   • The JNDI Tutorial

   • The Java Tutorials: Java Naming and Directory Interface

   How do I connect HTML form with MySQL database using JDBC?

   To start with the basic concept of interfacing:.

   Step 1: Creation of Database and Table in MySQL. ... .

   Step 2: Implementation of required Web-pages. ... .

   Step 3: Creation of Java Servlet program with JDBC Connection. ... .

   Step 4: To use this class method, create an object in Java Servlet program. ... .

   Step 5: Get the data from the HTML file..

   How do you connect to a MySQL database in Java?

   To connect to MySQL from Java, you have to use the JDBC driver from MySQL. The MySQL JDBC driver is called MySQL Connector/J. You find the latest MySQL JDBC driver under the following URL: http://dev.mysql.com/downloads/connector/j. The download contains a JAR file which we require later.

   Can we connect HTML with database?

   It happens on server, which is where the website is hosted. So, in order to connect to the database and perform various data related actions, you have to use server-side scripts, like php, jsp, asp.net etc. In order to insert new data into the database, you can use phpMyAdmin or write a INSERT query and execute them.

   Can we connect MySQL with Java?

   In Java, we can connect to our database(MySQL) with JDBC(Java Database Connectivity) through the Java code. JDBC is one of the standard APIs for database connectivity, using it we can easily run our query, statement, and also fetch data from the database.