JSP + JDBC + MySQL Example Tutorial

In this tutorial, we will learn how to use JDBC with JSP with an example.
All JSP Examples at JSP Source Code Examples.

In this example, we will design below  Employee Registration  JSP page and store Form parameters into the MySQL database using JDBC API:

MySQL Database Setup

Let's create a database named "mysql_database" in MySQL. Now, we create an employee table using below DDL script:
CREATE TABLE `employee` (
   `id` int(3) NOT NULL,
   `first_name` varchar(20) DEFAULT NULL,
   `last_name` varchar(20) DEFAULT NULL,
   `username` varchar(250) DEFAULT NULL,
   `password` varchar(20) DEFAULT NULL,
   `address` varchar(45) DEFAULT NULL,
   `contact` varchar(45) DEFAULT NULL,
   PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
SELECT * FROM mysql_database.employee; 

Create a JavaBean - Employee.java

Let's create an Employee JavaBean class which we will use in JSP action tags.
package net.javaguides.jsp.jdbc.bean;

import java.io.Serializable;

/**
 * JavaBean class used in jsp action tags.
 * @author Ramesh Fadatare
 */
public class Employee implements Serializable {
    /**
     * 
     */
    private static final long serialVersionUID = 1 L;
    private String firstName;
    private String lastName;
    private String username;
    private String password;
    private String address;
    private String contact;
    public String getFirstName() {
        return firstName;
    }
    public void setFirstName(String firstName) {
        this.firstName = firstName;
    }
    public String getLastName() {
        return lastName;
    }
    public void setLastName(String lastName) {
        this.lastName = lastName;
    }
    public String getUsername() {
        return username;
    }
    public void setUsername(String username) {
        this.username = username;
    }
    public String getPassword() {
        return password;
    }
    public void setPassword(String password) {
        this.password = password;
    }
    public String getAddress() {
        return address;
    }
    public void setAddress(String address) {
        this.address = address;
    }
    public String getContact() {
        return contact;
    }
    public void setContact(String contact) {
        this.contact = contact;
    }
}

Create an EmployeeDao.java

We will separate JDBC accessing code separate from JSP. So let's create an EmployeeDao class under net.javaguides.jsp.jdbc.database package and add the following code to it:
package net.javaguides.jsp.jdbc.database;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import net.javaguides.jsp.jdbc.bean.Employee;

public class EmployeeDao {

    public int registerEmployee(Employee employee) throws ClassNotFoundException {
        String INSERT_USERS_SQL = "INSERT INTO employee" +
            "  (id, first_name, last_name, username, password, address, contact) VALUES " +
            " (?, ?, ?, ?, ?,?,?);";

        int result = 0;

        Class.forName("com.mysql.jdbc.Driver");

        try (Connection connection = DriverManager
            .getConnection("jdbc:mysql://localhost:3306/mysql_database?useSSL=false", "root", "root");

            // Step 2:Create a statement using connection object
            PreparedStatement preparedStatement = connection.prepareStatement(INSERT_USERS_SQL)) {
            preparedStatement.setInt(1, 1);
            preparedStatement.setString(2, employee.getFirstName());
            preparedStatement.setString(3, employee.getLastName());
            preparedStatement.setString(4, employee.getUsername());
            preparedStatement.setString(5, employee.getPassword());
            preparedStatement.setString(6, employee.getAddress());
            preparedStatement.setString(7, employee.getContact());

            System.out.println(preparedStatement);
            // Step 3: Execute the query or update query
            result = preparedStatement.executeUpdate();

        } catch (SQLException e) {
            // process sql exception
            
        }
        return result;
    }
}

Create a employeeregister.jsp

Let's design employee registration HTML form with following fields:
  • firstName
  • lastName
  • username
  • password
  • address
  • contact
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
    <!DOCTYPE html>
    <html>

    <head>
        <meta charset="ISO-8859-1">
        <title>Insert title here</title>
    </head>

    <body>
        <h1>Employee Register Form</h1>
        <form action="employeedetails.jsp" method="post">
            <table style="with: 50%">
                <tr>
                    <td>First Name</td>
                    <td><input type="text" name="firstName" /></td>
                </tr>
                <tr>
                    <td>Last Name</td>
                    <td><input type="text" name="lastName" /></td>
                </tr>
                <tr>
                    <td>UserName</td>
                    <td><input type="text" name="username" /></td>
                </tr>
                <tr>
                    <td>Password</td>
                    <td><input type="password" name="password" /></td>
                </tr>
                <tr>
                    <td>Address</td>
                    <td><input type="text" name="address" /></td>
                </tr>
                <tr>
                    <td>Contact No</td>
                    <td><input type="text" name="contact" /></td>
                </tr>
            </table>
            <input type="submit" value="Submit" /></form>
    </body>

    </html>

Create a employeedetail.jsp

After an employee successfully registered then this page show a succesful message on screen:
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
 pageEncoding="ISO-8859-1"%>
<%@page import="net.javaguides.jsp.jdbc.database.*"%>
<!DOCTYPE html>
<html>
<head>
<meta charset="ISO-8859-1">
<title>Insert title here</title>
</head>
<body>

 <jsp:useBean id="employee"
  class="net.javaguides.jsp.jdbc.bean.Employee" />

 <jsp:setProperty property="*" name="employee" />

 <%
  EmployeeDao employeeDao = new EmployeeDao();
  int status = employeeDao.registerEmployee(employee);
  if (status > 0) {
   out.print("You are successfully registered");
  }
 %>
</body>
</html>

Demo

It's time to see a demo of the above development. Deploy this web application in tomcat server.

Employee Registration

Once you deploy this application successfully then hit this link into a browser - http://localhost:8080/jsp-jdbc-mysql-example/employeeregister.jsp

Registration Success Page


Comments