JSP with Database

JSP with JDBC

JSP can interact with databases using JDBC (Java Database Connectivity). The recommended approach is to keep database logic in a separate DAO (Data Access Object) class and use JSP only for display. However, understanding direct JDBC in JSP is important for learning.

Best Practice: Never put JDBC code directly in JSP. Use the MVC pattern — Servlet handles DB logic, JSP renders results.

MySQL Connection and Displaying Data in Table

package com.example.dao;

import java.sql.*;
import java.util.*;

public class UserDAO {

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

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

    // Get all users from database
    public List<Map<String, Object>> getAllUsers() throws SQLException {
        List<Map<String, Object>> users = new ArrayList<>();
        String sql = "SELECT id, name, email, created_at FROM users ORDER BY id";

        try (Connection conn = DriverManager.getConnection(URL, USER, PASS);
             PreparedStatement ps = conn.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {

            while (rs.next()) {
                Map<String, Object> row = new LinkedHashMap<>();
                row.put("id",         rs.getInt("id"));
                row.put("name",       rs.getString("name"));
                row.put("email",      rs.getString("email"));
                row.put("created_at", rs.getTimestamp("created_at"));
                users.add(row);
            }
        }
        return users;
    }

    // Insert a new user
    public int insertUser(String name, String email) throws SQLException {
        String sql = "INSERT INTO users (name, email) VALUES (?, ?)";
        try (Connection conn = DriverManager.getConnection(URL, USER, PASS);
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, name);
            ps.setString(2, email);
            return ps.executeUpdate(); // returns rows affected
        }
    }
}

<%-- users.jsp: display users from database --%>
<%@ page language="java" contentType="text/html; charset=UTF-8" %>
<%@ page import="com.example.dao.UserDAO, java.util.*" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<!DOCTYPE html>
<html>
<head><title>Users</title></head>
<body>
    <h2>User List</h2>
    <%
        UserDAO dao = new UserDAO();
        List<Map<String, Object>> users = dao.getAllUsers();
        request.setAttribute("users", users);
    %>
    <table border="1" cellpadding="8">
        <tr><th>ID</th><th>Name</th><th>Email</th><th>Created</th></tr>
        <c:forEach var="user" items="${users}">
            <tr>
                <td>${user.id}</td>
                <td>${user.name}</td>
                <td>${user.email}</td>
                <td>${user.created_at}</td>
            </tr>
        </c:forEach>
    </table>
</body>
</html>

Form Insert with Servlet Controller

Form Insert via Servlet (MVC Pattern)

package com.example.controller;

import com.example.dao.UserDAO;
import javax.servlet.*;
import javax.servlet.http.*;
import javax.servlet.annotation.WebServlet;
import java.io.IOException;

@WebServlet("/add-user")
public class AddUserServlet extends HttpServlet {

    // Show the add-user form
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp)
            throws ServletException, IOException {
        req.getRequestDispatcher("/WEB-INF/views/add-user.jsp").forward(req, resp);
    }

    // Process form submission
    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp)
            throws ServletException, IOException {
        String name  = req.getParameter("name");
        String email = req.getParameter("email");

        // Basic validation
        if (name == null || name.trim().isEmpty() ||
            email == null || email.trim().isEmpty()) {
            req.setAttribute("error", "Name and email are required.");
            req.getRequestDispatcher("/WEB-INF/views/add-user.jsp").forward(req, resp);
            return;
        }

        try {
            UserDAO dao = new UserDAO();
            dao.insertUser(name.trim(), email.trim());
            // Redirect to user list after successful insert (PRG pattern)
            resp.sendRedirect(req.getContextPath() + "/users");
        } catch (Exception e) {
            req.setAttribute("error", "Database error: " + e.getMessage());
            req.getRequestDispatcher("/WEB-INF/views/add-user.jsp").forward(req, resp);
        }
    }
}

<%-- WEB-INF/views/add-user.jsp --%>
<%@ page language="java" contentType="text/html; charset=UTF-8" %>
<%@ taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c" %>
<!DOCTYPE html>
<html>
<head><title>Add User</title></head>
<body>
    <h2>Add New User</h2>
    <c:if test="${not empty error}">
        <p style="color:red;">${error}</p>
    </c:if>
    <form action="${pageContext.request.contextPath}/add-user" method="post">
        <label>Name: <input type="text" name="name" required /></label><br/><br/>
        <label>Email: <input type="email" name="email" required /></label><br/><br/>
        <button type="submit">Add User</button>
    </form>
    <p><a href="${pageContext.request.contextPath}/users">View All Users</a></p>
</body>
</html>

JNDI Connection Pooling

For production applications, use JNDI (Java Naming and Directory Interface) to obtain a pooled connection from the application server instead of creating connections directly in code.

JNDI Connection Pool Configuration

<!-- META-INF/context.xml (Tomcat) -->
<Context>
    <Resource name="jdbc/mydb"
              auth="Container"
              type="javax.sql.DataSource"
              maxTotal="20"
              maxIdle="10"
              maxWaitMillis="10000"
              username="root"
              password="password"
              driverClassName="com.mysql.cj.jdbc.Driver"
              url="jdbc:mysql://localhost:3306/mydb?useSSL=false"/>
</Context>

<!-- web.xml: declare the resource reference -->
<resource-ref>
    <description>DB Connection Pool</description>
    <res-ref-name>jdbc/mydb</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
</resource-ref>

package com.example.dao;

import javax.naming.*;
import javax.sql.DataSource;
import java.sql.*;

public class JNDIUserDAO {

    // Lookup the DataSource from JNDI
    private DataSource getDataSource() throws NamingException {
        Context initCtx = new InitialContext();
        Context envCtx  = (Context) initCtx.lookup("java:comp/env");
        return (DataSource) envCtx.lookup("jdbc/mydb");
    }

    public List<String> getUserNames() throws Exception {
        List<String> names = new java.util.ArrayList<>();
        // Connection is obtained from the pool — no need to manage driver loading
        try (Connection conn = getDataSource().getConnection();
             PreparedStatement ps = conn.prepareStatement("SELECT name FROM users");
             ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                names.add(rs.getString("name"));
            }
        }
        // Connection is automatically returned to the pool
        return names;
    }
}

Previous Next

JSP with Database

JSP with JDBC

Form Insert with Servlet Controller

JNDI Connection Pooling

Ready to Level Up Your Skills?