Pertemuan 11 - CRUD Manajemen User

- Project Java Web Starter
- Folder `src/java/controller`
- Folder `src/java/dao`
- Folder `src/java/model`
- Folder `web/WEB-INF/views/users`
- File `src/java/dao/PegawaiDAO.java`
- File `web/WEB-INF/views/partials/sidebar.jsp`
- File `web/assets/css/style.css`
- Data pegawai di database

- admin
- pegawai

/users -> UserServlet.java -> UserDAO.java -> users/daftar.jsp atau users/form.jsp

src/java/model/User.java
src/java/dao/UserDAO.java
src/java/controller/UserServlet.java
web/WEB-INF/views/users/daftar.jsp
web/WEB-INF/views/users/form.jsp

package model;

public class User {

    private int id;
    private Integer pegawaiId;
    private String username;
    private String password;
    private String role;
    private String statusUser;
    private String nip;
    private String namaPegawai;

    public User() {
    }

    public User(int id, String username, String password, String role) {
        this.id = id;
        this.username = username;
        this.password = password;
        this.role = role;
    }

    public User(int id, Integer pegawaiId, String username, String password,
                String role, String statusUser, String nip, String namaPegawai) {
        this.id = id;
        this.pegawaiId = pegawaiId;
        this.username = username;
        this.password = password;
        this.role = role;
        this.statusUser = statusUser;
        this.nip = nip;
        this.namaPegawai = namaPegawai;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public Integer getPegawaiId() {
        return pegawaiId;
    }

    public void setPegawaiId(Integer pegawaiId) {
        this.pegawaiId = pegawaiId;
    }

    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 getRole() {
        return role;
    }

    public void setRole(String role) {
        this.role = role;
    }

    public String getStatusUser() {
        return statusUser;
    }

    public void setStatusUser(String statusUser) {
        this.statusUser = statusUser;
    }

    public String getNip() {
        return nip;
    }

    public void setNip(String nip) {
        this.nip = nip;
    }

    public String getNamaPegawai() {
        return namaPegawai;
    }

    public void setNamaPegawai(String namaPegawai) {
        this.namaPegawai = namaPegawai;
    }

    public boolean isAdmin() {
        return "admin".equals(role);
    }

    public boolean isPegawai() {
        return "pegawai".equals(role);
    }

    @Override
    public String toString() {
        return "User{id=" + id + ", username=" + username + ", role=" + role + "}";
    }
}

package dao;

import model.User;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.List;

public class UserDAO {

    public User authenticate(String loginId, String password) throws SQLException {
        String sql = "SELECT u.id_user AS id, u.id_pegawai, u.username, u.password, "
                   + "u.role, u.status_user, p.nip, p.nama_pegawai "
                   + "FROM users u "
                   + "LEFT JOIN pegawai p ON p.id_pegawai = u.id_pegawai "
                   + "WHERE (u.username = ? OR p.nip = ? OR CAST(p.id_pegawai AS CHAR) = ?) "
                   + "AND u.password = ? "
                   + "AND COALESCE(u.status_user, 'Aktif') = 'Aktif' "
                   + "LIMIT 1";

        try (Connection conn = DBKoneksi.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setString(1, loginId);
            ps.setString(2, loginId);
            ps.setString(3, loginId);
            ps.setString(4, password);

            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    return mapRow(rs);
                }
            }
        }

        return null;
    }

    public List<User> findAll() throws SQLException {
        String sql = "SELECT u.id_user AS id, u.id_pegawai, u.username, u.password, "
                   + "u.role, u.status_user, p.nip, p.nama_pegawai "
                   + "FROM users u "
                   + "LEFT JOIN pegawai p ON p.id_pegawai = u.id_pegawai "
                   + "ORDER BY u.role ASC, u.username ASC";

        List<User> users = new ArrayList<>();

        try (Connection conn = DBKoneksi.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {

            while (rs.next()) {
                users.add(mapRow(rs));
            }
        }

        return users;
    }

    public User findById(int id) throws SQLException {
        String sql = "SELECT u.id_user AS id, u.id_pegawai, u.username, u.password, "
                   + "u.role, u.status_user, p.nip, p.nama_pegawai "
                   + "FROM users u "
                   + "LEFT JOIN pegawai p ON p.id_pegawai = u.id_pegawai "
                   + "WHERE u.id_user = ?";

        try (Connection conn = DBKoneksi.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setInt(1, id);

            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    return mapRow(rs);
                }
            }
        }

        return null;
    }

    public void insert(User user) throws SQLException {
        String sql = "INSERT INTO users "
                   + "(id_pegawai, username, password, role, status_user) "
                   + "VALUES (?, ?, ?, ?, ?)";

        try (Connection conn = DBKoneksi.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            setNullableInt(ps, 1, user.getPegawaiId());
            ps.setString(2, user.getUsername());
            ps.setString(3, user.getPassword());
            ps.setString(4, user.getRole());
            ps.setString(5, user.getStatusUser());
            ps.executeUpdate();
        }
    }

    public void update(User user, boolean updatePassword) throws SQLException {
        String sql = updatePassword
                ? "UPDATE users SET id_pegawai = ?, username = ?, password = ?, role = ?, status_user = ? WHERE id_user = ?"
                : "UPDATE users SET id_pegawai = ?, username = ?, role = ?, status_user = ? WHERE id_user = ?";

        try (Connection conn = DBKoneksi.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            setNullableInt(ps, 1, user.getPegawaiId());
            ps.setString(2, user.getUsername());

            if (updatePassword) {
                ps.setString(3, user.getPassword());
                ps.setString(4, user.getRole());
                ps.setString(5, user.getStatusUser());
                ps.setInt(6, user.getId());
            } else {
                ps.setString(3, user.getRole());
                ps.setString(4, user.getStatusUser());
                ps.setInt(5, user.getId());
            }

            ps.executeUpdate();
        }
    }

    public void delete(int id) throws SQLException {
        String sql = "DELETE FROM users WHERE id_user = ?";

        try (Connection conn = DBKoneksi.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setInt(1, id);
            ps.executeUpdate();
        }
    }

    public boolean existsUsername(String username, int excludeId) throws SQLException {
        String sql = "SELECT COUNT(*) FROM users WHERE LOWER(username) = LOWER(?) AND id_user <> ?";

        try (Connection conn = DBKoneksi.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setString(1, username);
            ps.setInt(2, excludeId);

            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    return rs.getInt(1) > 0;
                }
            }
        }

        return false;
    }

    public boolean existsPegawaiUser(int pegawaiId, int excludeId) throws SQLException {
        String sql = "SELECT COUNT(*) FROM users WHERE id_pegawai = ? AND id_user <> ?";

        try (Connection conn = DBKoneksi.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setInt(1, pegawaiId);
            ps.setInt(2, excludeId);

            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    return rs.getInt(1) > 0;
                }
            }
        }

        return false;
    }

    private void setNullableInt(PreparedStatement ps, int index, Integer value) throws SQLException {
        if (value == null || value <= 0) {
            ps.setNull(index, Types.INTEGER);
        } else {
            ps.setInt(index, value);
        }
    }

    private User mapRow(ResultSet rs) throws SQLException {
        int pegawaiId = rs.getInt("id_pegawai");
        Integer nullablePegawaiId = rs.wasNull() ? null : pegawaiId;

        return new User(
                rs.getInt("id"),
                nullablePegawaiId,
                rs.getString("username"),
                rs.getString("password"),
                rs.getString("role"),
                rs.getString("status_user"),
                rs.getString("nip"),
                rs.getString("nama_pegawai")
        );
    }
}

package controller;

import dao.PegawaiDAO;
import dao.UserDAO;
import model.Pegawai;
import model.User;

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 jakarta.servlet.http.HttpSession;

import java.io.IOException;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.sql.SQLException;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

@WebServlet(name = "UserServlet", urlPatterns = {"/users"})
public class UserServlet extends HttpServlet {

    private static final String LIST_PAGE = "/WEB-INF/views/users/daftar.jsp";
    private static final String FORM_PAGE = "/WEB-INF/views/users/form.jsp";

    private final UserDAO userDAO = new UserDAO();
    private final PegawaiDAO pegawaiDAO = new PegawaiDAO();

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

        if (!isAdmin(request)) {
            response.sendRedirect(request.getContextPath() + "/index.jsp");
            return;
        }

        String action = normalize(request.getParameter("action"));
        switch (action) {
            case "create" -> showForm(request, response, new User(), new LinkedHashMap<>());
            case "edit" -> handleEdit(request, response);
            default -> showList(request, response);
        }
    }

    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

        request.setCharacterEncoding("UTF-8");

        if (!isAdmin(request)) {
            response.sendRedirect(request.getContextPath() + "/index.jsp");
            return;
        }

        String action = normalize(request.getParameter("action"));
        if ("delete".equals(action)) {
            handleDelete(request, response);
            return;
        }

        handleSave(request, response);
    }

    private void showList(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

        try {
            request.setAttribute("activeMenu", "users");
            request.setAttribute("userList", userDAO.findAll());
            forwardToLayout(request, response, LIST_PAGE);
        } catch (SQLException e) {
            throw new ServletException("Gagal mengambil data user.", e);
        }
    }

    private void handleEdit(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

        int id = parseInt(request.getParameter("id"));
        if (id <= 0) {
            redirectWithMessage(response, request, "/users", "Data user tidak valid.", "error");
            return;
        }

        try {
            User user = userDAO.findById(id);
            if (user == null) {
                redirectWithMessage(response, request, "/users", "Data user tidak ditemukan.", "error");
                return;
            }

            showForm(request, response, user, new LinkedHashMap<>());
        } catch (SQLException e) {
            throw new ServletException("Gagal membuka form user.", e);
        }
    }

    private void handleSave(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {

        User user = bind(request);
        String passwordBaru = trimToNull(request.getParameter("password"));
        boolean updatePassword = passwordBaru != null;

        if (updatePassword) {
            user.setPassword(passwordBaru);
        }

        Map<String, String> errors = validate(user, updatePassword);
        if (!errors.isEmpty()) {
            showForm(request, response, user, errors);
            return;
        }

        try {
            if (user.getId() > 0) {
                userDAO.update(user, updatePassword);
            } else {
                userDAO.insert(user);
            }

            redirectWithMessage(response, request, "/users", "Data user berhasil disimpan.", "success");
        } catch (SQLException e) {
            errors.put("general", "Gagal menyimpan data user.");
            showForm(request, response, user, errors);
        }
    }

    private void handleDelete(HttpServletRequest request, HttpServletResponse response)
            throws IOException {

        int id = parseInt(request.getParameter("id"));
        User userLogin = getUserLogin(request);

        if (id <= 0) {
            redirectWithMessage(response, request, "/users", "Data user tidak valid.", "error");
            return;
        }

        if (userLogin != null && userLogin.getId() == id) {
            redirectWithMessage(response, request, "/users", "User yang sedang login tidak boleh dihapus.", "error");
            return;
        }

        try {
            userDAO.delete(id);
            redirectWithMessage(response, request, "/users", "Data user berhasil dihapus.", "success");
        } catch (SQLException e) {
            redirectWithMessage(response, request, "/users", "Gagal menghapus data user.", "error");
        }
    }

    private void showForm(HttpServletRequest request, HttpServletResponse response,
                          User user, Map<String, String> errors)
            throws ServletException, IOException {

        try {
            if (user.getRole() == null) {
                user.setRole("pegawai");
            }

            if (user.getStatusUser() == null) {
                user.setStatusUser("Aktif");
            }

            request.setAttribute("activeMenu", "users");
            request.setAttribute("userData", user);
            request.setAttribute("pegawaiList", pegawaiDAO.findAll());
            request.setAttribute("errors", errors);
            request.setAttribute("formTitle", user.getId() > 0 ? "Edit User" : "Tambah User");
            forwardToLayout(request, response, FORM_PAGE);
        } catch (SQLException e) {
            throw new ServletException("Gagal membuka form user.", e);
        }
    }

    private User bind(HttpServletRequest request) throws ServletException {
        User user = new User();
        user.setId(parseInt(request.getParameter("id")));
        user.setRole(defaultIfBlank(request.getParameter("role"), "pegawai"));
        user.setStatusUser(defaultIfBlank(request.getParameter("statusUser"), "Aktif"));

        int pegawaiId = parseInt(request.getParameter("pegawaiId"));
        if (pegawaiId > 0) {
            user.setPegawaiId(pegawaiId);
            try {
                Pegawai pegawai = pegawaiDAO.findById(pegawaiId);
                if (pegawai != null) {
                    user.setUsername(pegawai.getNip());
                    user.setNip(pegawai.getNip());
                    user.setNamaPegawai(pegawai.getNamaPegawai());
                }
            } catch (SQLException e) {
                throw new ServletException("Gagal membaca data pegawai.", e);
            }
        } else {
            user.setUsername(trimToNull(request.getParameter("username")));
        }

        return user;
    }

    private Map<String, String> validate(User user, boolean updatePassword) {
        Map<String, String> errors = new LinkedHashMap<>();

        if (!"admin".equals(user.getRole()) && !"pegawai".equals(user.getRole())) {
            errors.put("role", "Role tidak valid.");
        }

        if (!"Aktif".equals(user.getStatusUser()) && !"Nonaktif".equals(user.getStatusUser())) {
            errors.put("statusUser", "Status user tidak valid.");
        }

        if ("pegawai".equals(user.getRole()) && (user.getPegawaiId() == null || user.getPegawaiId() <= 0)) {
            errors.put("pegawaiId", "Pegawai wajib dipilih untuk role pegawai.");
        }

        if (user.getUsername() == null || user.getUsername().isBlank()) {
            errors.put("username", "Username wajib diisi.");
        }

        if (user.getId() <= 0 && !updatePassword) {
            errors.put("password", "Password wajib diisi untuk user baru.");
        }

        try {
            if (!errors.containsKey("username")
                    && userDAO.existsUsername(user.getUsername(), user.getId())) {
                errors.put("username", "Username sudah digunakan.");
            }

            if (user.getPegawaiId() != null && user.getPegawaiId() > 0
                    && userDAO.existsPegawaiUser(user.getPegawaiId(), user.getId())) {
                errors.put("pegawaiId", "Pegawai ini sudah punya akun login.");
            }
        } catch (SQLException e) {
            errors.put("general", "Gagal memeriksa data user.");
        }

        return errors;
    }

    private boolean isAdmin(HttpServletRequest request) {
        User user = getUserLogin(request);
        return user != null && user.isAdmin();
    }

    private User getUserLogin(HttpServletRequest request) {
        HttpSession session = request.getSession(false);
        if (session == null) {
            return null;
        }

        Object user = session.getAttribute("userLogin");
        return user instanceof User ? (User) user : null;
    }

    private void forwardToLayout(HttpServletRequest request, HttpServletResponse response,
                                 String contentPage) throws ServletException, IOException {
        request.setAttribute("contentPage", contentPage);
        request.getRequestDispatcher("/index.jsp").forward(request, response);
    }

    private void redirectWithMessage(HttpServletResponse response, HttpServletRequest request,
                                     String path, String message, String type) throws IOException {
        String target = request.getContextPath() + path
                + "?message=" + URLEncoder.encode(message, StandardCharsets.UTF_8)
                + "&type=" + URLEncoder.encode(type, StandardCharsets.UTF_8);
        response.sendRedirect(target);
    }

    private String normalize(String value) {
        return value == null ? "" : value.trim().toLowerCase();
    }

    private int parseInt(String value) {
        try {
            return Integer.parseInt(value);
        } catch (Exception e) {
            return 0;
        }
    }

    private String defaultIfBlank(String value, String defaultValue) {
        String trimmed = trimToNull(value);
        return trimmed == null ? defaultValue : trimmed;
    }

    private String trimToNull(String value) {
        if (value == null) {
            return null;
        }

        String trimmed = value.trim();
        return trimmed.isEmpty() ? null : trimmed;
    }
}

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>

<div class="page-header">
    <h2>Manajemen User</h2>
</div>

<c:if test="${not empty param.message}">
    <div class="${param.type == 'error' ? 'error' : param.type == 'info' ? 'info' : 'success'}">
        <c:out value="${param.message}"/>
    </div>
</c:if>

<div class="table-toolbar">
    <a href="${pageContext.request.contextPath}/users?action=create" class="btn btn-add">+ Tambah User</a>
</div>

<div class="card">
    <c:choose>
        <c:when test="${empty userList}">
            <div class="info">Belum ada data user.</div>
        </c:when>
        <c:otherwise>
            <table>
                <thead>
                    <tr>
                        <th>No</th>
                        <th>Username</th>
                        <th>Pegawai</th>
                        <th>Role</th>
                        <th>Status</th>
                        <th>Aksi</th>
                    </tr>
                </thead>
                <tbody>
                    <c:forEach var="akun" items="${userList}" varStatus="loop">
                        <tr class="${loop.index % 2 == 1 ? 'genap' : ''}">
                            <td>${loop.index + 1}</td>
                            <td><c:out value="${akun.username}"/></td>
                            <td>
                                <c:choose>
                                    <c:when test="${not empty akun.namaPegawai}">
                                        <c:out value="${akun.nip}"/> - <c:out value="${akun.namaPegawai}"/>
                                    </c:when>
                                    <c:otherwise>-</c:otherwise>
                                </c:choose>
                            </td>
                            <td><span class="badge"><c:out value="${akun.role}"/></span></td>
                            <td><c:out value="${akun.statusUser}"/></td>
                            <td>
                                <div class="table-actions">
                                    <a class="btn btn-warning"
                                       href="${pageContext.request.contextPath}/users?action=edit&id=${akun.id}">
                                        Edit
                                    </a>
                                    <form class="inline-form"
                                          method="post"
                                          action="${pageContext.request.contextPath}/users"
                                          onsubmit="return confirm('Hapus user ini?');">
                                        <input type="hidden" name="action" value="delete">
                                        <input type="hidden" name="id" value="${akun.id}">
                                        <button type="submit" class="btn btn-danger">Hapus</button>
                                    </form>
                                </div>
                            </td>
                        </tr>
                    </c:forEach>
                </tbody>
            </table>
        </c:otherwise>
    </c:choose>
</div>

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core"%>

<div class="page-header">
    <h2>Manajemen User</h2>
</div>

<div class="card">
    <h3><c:out value="${formTitle}" default="Form User"/></h3>

    <c:if test="${not empty errors.general}">
        <div class="error"><c:out value="${errors.general}"/></div>
    </c:if>

    <form method="post" action="${pageContext.request.contextPath}/users">
        <input type="hidden" name="id" value="${userData.id}">

        <div class="form-group">
            <label for="role">Role</label>
            <select id="role" name="role" required>
                <option value="pegawai" ${userData.role == 'pegawai' ? 'selected' : ''}>Pegawai</option>
                <option value="admin" ${userData.role == 'admin' ? 'selected' : ''}>Admin</option>
            </select>
            <c:if test="${not empty errors.role}">
                <div class="error"><c:out value="${errors.role}"/></div>
            </c:if>
        </div>

        <div class="form-group">
            <label for="pegawaiId">Data Pegawai</label>
            <select id="pegawaiId" name="pegawaiId">
                <option value="">-- Tidak terhubung ke pegawai --</option>
                <c:forEach var="pegawai" items="${pegawaiList}">
                    <option value="${pegawai.id}" ${userData.pegawaiId == pegawai.id ? 'selected' : ''}>
                        <c:out value="${pegawai.nip}"/> - <c:out value="${pegawai.namaPegawai}"/>
                    </option>
                </c:forEach>
            </select>
            <p class="form-help">Untuk role pegawai, username login otomatis memakai NIP/ID pegawai yang dipilih.</p>
            <c:if test="${not empty errors.pegawaiId}">
                <div class="error"><c:out value="${errors.pegawaiId}"/></div>
            </c:if>
        </div>

        <div class="form-group">
            <label for="username">Username Admin</label>
            <input type="text"
                   id="username"
                   name="username"
                   value="${userData.username}"
                   placeholder="Diisi manual jika tidak memilih pegawai">
            <c:if test="${not empty errors.username}">
                <div class="error"><c:out value="${errors.username}"/></div>
            </c:if>
        </div>

        <div class="form-group">
            <label for="password">Password</label>
            <input type="password"
                   id="password"
                   name="password"
                   placeholder="${userData.id > 0 ? 'Kosongkan jika tidak diganti' : 'Password user'}">
            <c:if test="${not empty errors.password}">
                <div class="error"><c:out value="${errors.password}"/></div>
            </c:if>
        </div>

        <div class="form-group">
            <label for="statusUser">Status</label>
            <select id="statusUser" name="statusUser" required>
                <option value="Aktif" ${userData.statusUser == 'Aktif' ? 'selected' : ''}>Aktif</option>
                <option value="Nonaktif" ${userData.statusUser == 'Nonaktif' ? 'selected' : ''}>Nonaktif</option>
            </select>
            <c:if test="${not empty errors.statusUser}">
                <div class="error"><c:out value="${errors.statusUser}"/></div>
            </c:if>
        </div>

        <div class="form-actions">
            <button type="submit" class="btn-primary">
                <c:choose>
                    <c:when test="${userData.id > 0}">Update</c:when>
                    <c:otherwise>Simpan</c:otherwise>
                </c:choose>
            </button>
            <a href="${pageContext.request.contextPath}/users" class="btn btn-secondary">Kembali</a>
        </div>
    </form>
</div>

- `UserServlet.java` menolak akses jika user bukan admin
- menu `Manajemen User` ada di `sidebar.jsp`
- route `/users` bisa dibuka dari menu admin
- tabel dan form tetap rapi dengan bantuan `style.css`