Servlet Database Connectivity

JDBC with Servlet

Servlets commonly interact with databases using JDBC (Java Database Connectivity). The typical pattern is: Servlet receives request → calls DAO (Data Access Object) → DAO uses JDBC → returns data → Servlet forwards to JSP view.

DAO Pattern with JDBC

package com.example.dao;

import com.example.model.User;
import java.sql.*;
import java.util.*;

public class UserDAO {

    // CREATE
    public boolean createUser(User user) throws SQLException {
        String sql = "INSERT INTO users (username, email, password) VALUES (?, ?, ?)";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, user.getUsername());
            ps.setString(2, user.getEmail());
            ps.setString(3, user.getPassword()); // Hash in real app!
            return ps.executeUpdate() > 0;
        }
    }

    // READ - Get by ID
    public User getUserById(int id) throws SQLException {
        String sql = "SELECT * FROM users WHERE id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, id);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    return mapRow(rs);
                }
            }
        }
        return null;
    }

    // READ - Get all
    public List<User> getAllUsers() throws SQLException {
        List<User> users = new ArrayList<>();
        String sql = "SELECT * FROM users ORDER BY username";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                users.add(mapRow(rs));
            }
        }
        return users;
    }

    // UPDATE
    public boolean updateUser(User user) throws SQLException {
        String sql = "UPDATE users SET email = ? WHERE id = ?";
        try (Connection conn = DBUtil.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, user.getEmail());
            ps.setInt(2, user.getId());
            return ps.executeUpdate() > 0;
        }
    }

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

    private User mapRow(ResultSet rs) throws SQLException {
        User u = new User();
        u.setId(rs.getInt("id"));
        u.setUsername(rs.getString("username"));
        u.setEmail(rs.getString("email"));
        return u;
    }
}

package com.example.dao;

import java.sql.*;

public class DBUtil {
    private static final String URL      = "jdbc:mysql://localhost:3306/mydb?useSSL=false";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "password";

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

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

Servlet CRUD Controller

UserServlet - CRUD Controller

@WebServlet("/users")
public class UserServlet extends HttpServlet {

    private UserDAO userDAO = new UserDAO();

    // GET /users - List all users
    // GET /users?id=1 - Get specific user
    @Override
    protected void doGet(HttpServletRequest req, HttpServletResponse resp)
            throws ServletException, IOException {
        try {
            String idParam = req.getParameter("id");
            if (idParam != null) {
                User user = userDAO.getUserById(Integer.parseInt(idParam));
                req.setAttribute("user", user);
                req.getRequestDispatcher("/WEB-INF/views/user-detail.jsp")
                   .forward(req, resp);
            } else {
                List<User> users = userDAO.getAllUsers();
                req.setAttribute("users", users);
                req.getRequestDispatcher("/WEB-INF/views/user-list.jsp")
                   .forward(req, resp);
            }
        } catch (SQLException e) {
            throw new ServletException("Database error", e);
        }
    }

    // POST /users - Create new user
    @Override
    protected void doPost(HttpServletRequest req, HttpServletResponse resp)
            throws ServletException, IOException {
        User user = new User();
        user.setUsername(req.getParameter("username"));
        user.setEmail(req.getParameter("email"));
        user.setPassword(req.getParameter("password"));
        try {
            userDAO.createUser(user);
            resp.sendRedirect(req.getContextPath() + "/users");
        } catch (SQLException e) {
            throw new ServletException("Database error", e);
        }
    }

    // DELETE /users?id=1 - Delete user
    @Override
    protected void doDelete(HttpServletRequest req, HttpServletResponse resp)
            throws ServletException, IOException {
        int id = Integer.parseInt(req.getParameter("id"));
        try {
            userDAO.deleteUser(id);
            resp.setStatus(HttpServletResponse.SC_NO_CONTENT); // 204
        } catch (SQLException e) {
            throw new ServletException("Database error", e);
        }
    }
}

Connection Pooling with JNDI DataSource

For production applications, use a connection pool instead of creating a new connection per request. Configure a JNDI DataSource in Tomcat's context.xml:

JNDI DataSource Configuration

<!-- META-INF/context.xml -->
<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>

// Using JNDI DataSource in DAO
import javax.naming.*;
import javax.sql.DataSource;

public class DBUtil {
    private static DataSource dataSource;

    static {
        try {
            Context ctx = new InitialContext();
            dataSource = (DataSource) ctx.lookup("java:comp/env/jdbc/mydb");
        } catch (NamingException e) {
            throw new RuntimeException("JNDI lookup failed", e);
        }
    }

    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection(); // From pool
    }
}

Previous Next

Servlet Database Connectivity

JDBC with Servlet

Servlet CRUD Controller

Connection Pooling with JNDI DataSource

Ready to Level Up Your Skills?