Java Servlet CRUD Operations – Complete Example with MySQL

Java Servlet CRUD Operations – Complete Example with MySQL

Java Servlets are the foundation of Java web development. Before Spring MVC and frameworks, servlets handled every HTTP request directly. Even today, understanding servlets is essential because frameworks like Spring are built on top of them. This tutorial shows you how to implement full CRUD (Create, Read, Update, Delete) operations using Servlets, JSP, and MySQL.

Technology Stack

• Java 8+
• Jakarta EE (Servlet 5.0) or Java EE (Servlet 4.0)
• Apache Tomcat 10.x
• MySQL 8.x
• JSP + JSTL for views
• Maven for build

Database Setup

-- Create database and table
CREATE DATABASE IF NOT EXISTS java9rdb;
USE java9rdb;

CREATE TABLE IF NOT EXISTS products (
    id       INT AUTO_INCREMENT PRIMARY KEY,
    name     VARCHAR(100) NOT NULL,
    price    DOUBLE NOT NULL,
    quantity INT DEFAULT 0,
    created  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Insert sample data
INSERT INTO products (name, price, quantity) VALUES
('Laptop',     75000.00, 10),
('Smartphone', 25000.00, 50),
('Tablet',     35000.00, 30);

Maven pom.xml

<dependencies>
    <dependency>
        <groupId>jakarta.servlet</groupId>
        <artifactId>jakarta.servlet-api</artifactId>
        <version>5.0.0</version>
        <scope>provided</scope>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.33</version>
    </dependency>
    <dependency>
        <groupId>jakarta.servlet.jsp.jstl</groupId>
        <artifactId>jakarta.servlet.jsp.jstl-api</artifactId>
        <version>2.0.0</version>
    </dependency>
</dependencies>

DBConnection.java – Database Helper

package com.java9r.util;

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

public class DBConnection {

    private static final String URL  = "jdbc:mysql://localhost:3306/java9rdb?useSSL=false";
    private static final String USER = "root";
    private static final String PASS = "yourpassword";

    static {
        try { Class.forName("com.mysql.cj.jdbc.Driver"); }
        catch (ClassNotFoundException e) { throw new RuntimeException(e); }
    }

    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(URL, USER, PASS);
    }
}

Product.java – Model

package com.java9r.model;

public class Product {
    private int    id;
    private String name;
    private double price;
    private int    quantity;

    public Product() {}

    public Product(int id, String name, double price, int quantity) {
        this.id       = id;
        this.name     = name;
        this.price    = price;
        this.quantity = quantity;
    }

    // Getters and setters
    public int    getId()          { return id; }
    public void   setId(int id)    { this.id = id; }
    public String getName()        { return name; }
    public void   setName(String n){ this.name = n; }
    public double getPrice()       { return price; }
    public void   setPrice(double p){ this.price = p; }
    public int    getQuantity()    { return quantity; }
    public void   setQuantity(int q){ this.quantity = q; }
}

ProductDAO.java – Data Access Layer

package com.java9r.dao;

import com.java9r.model.Product;
import com.java9r.util.DBConnection;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class ProductDAO {

    // CREATE
    public boolean insert(Product p) throws SQLException {
        String sql = "INSERT INTO products (name, price, quantity) VALUES (?, ?, ?)";
        try (Connection con = DBConnection.getConnection();
             PreparedStatement ps = con.prepareStatement(sql)) {
            ps.setString(1, p.getName());
            ps.setDouble(2, p.getPrice());
            ps.setInt(3,    p.getQuantity());
            return ps.executeUpdate() > 0;
        }
    }

    // READ ALL
    public List<Product> findAll() throws SQLException {
        List<Product> list = new ArrayList<>();
        String sql = "SELECT * FROM products ORDER BY id";
        try (Connection con = DBConnection.getConnection();
             Statement   st  = con.createStatement();
             ResultSet   rs  = st.executeQuery(sql)) {
            while (rs.next()) {
                list.add(mapRow(rs));
            }
        }
        return list;
    }

    // READ ONE
    public Product findById(int id) throws SQLException {
        String sql = "SELECT * FROM products WHERE id = ?";
        try (Connection con = DBConnection.getConnection();
             PreparedStatement ps = con.prepareStatement(sql)) {
            ps.setInt(1, id);
            try (ResultSet rs = ps.executeQuery()) {
                return rs.next() ? mapRow(rs) : null;
            }
        }
    }

    // UPDATE
    public boolean update(Product p) throws SQLException {
        String sql = "UPDATE products SET name=?, price=?, quantity=? WHERE id=?";
        try (Connection con = DBConnection.getConnection();
             PreparedStatement ps = con.prepareStatement(sql)) {
            ps.setString(1, p.getName());
            ps.setDouble(2, p.getPrice());
            ps.setInt(3,    p.getQuantity());
            ps.setInt(4,    p.getId());
            return ps.executeUpdate() > 0;
        }
    }

    // DELETE
    public boolean delete(int id) throws SQLException {
        String sql = "DELETE FROM products WHERE id = ?";
        try (Connection con = DBConnection.getConnection();
             PreparedStatement ps = con.prepareStatement(sql)) {
            ps.setInt(1, id);
            return ps.executeUpdate() > 0;
        }
    }

    private Product mapRow(ResultSet rs) throws SQLException {
        return new Product(
            rs.getInt("id"),
            rs.getString("name"),
            rs.getDouble("price"),
            rs.getInt("quantity")
        );
    }
}

ProductServlet.java – Controller

package com.java9r.servlet;

import com.java9r.dao.ProductDAO;
import com.java9r.model.Product;
import jakarta.servlet.ServletException;
import jakarta.servlet.annotation.WebServlet;
import jakarta.servlet.http.HttpServlet;
import jakarta.servlet.http.HttpServletRequest;
import jakarta.servlet.http.HttpServletResponse;

import java.io.IOException;

@WebServlet("/products")
public class ProductServlet extends HttpServlet {

    private final ProductDAO dao = new ProductDAO();

    // List all products
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse res)
            throws ServletException, IOException {

        String action = req.getParameter("action");

        try {
            if ("edit".equals(action)) {
                int id = Integer.parseInt(req.getParameter("id"));
                req.setAttribute("product", dao.findById(id));
                req.getRequestDispatcher("editProduct.jsp").forward(req, res);
            } else if ("delete".equals(action)) {
                int id = Integer.parseInt(req.getParameter("id"));
                dao.delete(id);
                res.sendRedirect("products");
            } else {
                req.setAttribute("products", dao.findAll());
                req.getRequestDispatcher("listProducts.jsp").forward(req, res);
            }
        } catch (Exception e) {
            throw new ServletException(e);
        }
    }

    // Add or update a product
    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse res)
            throws ServletException, IOException {

        req.setCharacterEncoding("UTF-8");
        String idParam = req.getParameter("id");
        String name    = req.getParameter("name");
        double price   = Double.parseDouble(req.getParameter("price"));
        int    qty     = Integer.parseInt(req.getParameter("quantity"));

        try {
            if (idParam != null && !idParam.isEmpty()) {
                // Update existing product
                dao.update(new Product(Integer.parseInt(idParam), name, price, qty));
            } else {
                // Insert new product
                dao.insert(new Product(0, name, price, qty));
            }
            res.sendRedirect("products");
        } catch (Exception e) {
            throw new ServletException(e);
        }
    }
}

listProducts.jsp – View

<%@ page contentType="text/html;charset=UTF-8" %>
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<!DOCTYPE html>
<html>
<head><title>Product List</title></head>
<body>
<h2>Products</h2>
<a href="addProduct.jsp">Add New Product</a>
<table border="1">
  <tr><th>ID</th><th>Name</th><th>Price</th><th>Qty</th><th>Actions</th></tr>
  <c:forEach var="p" items="${products}">
  <tr>
    <td>${p.id}</td>
    <td>${p.name}</td>
    <td>Rs. ${p.price}</td>
    <td>${p.quantity}</td>
    <td>
      <a href="products?action=edit&id=${p.id}">Edit</a> |
      <a href="products?action=delete&id=${p.id}"
         onclick="return confirm('Delete ${p.name}?')">Delete</a>
    </td>
  </tr>
  </c:forEach>
</table>
</body>
</html>

Summary

This Servlet CRUD example follows the MVC pattern: the Servlet acts as the controller, JSP handles the view, and the DAO layer manages database operations. Always use PreparedStatement (never string concatenation) to prevent SQL injection. The single servlet handles all operations through request parameters, with doGet() for reads/deletes and doPost() for inserts/updates. For production code, add input validation, connection pooling (via JNDI or HikariCP), and proper error pages.