Pertemuan 08 - Database dan Koneksi Database

Tujuan Praktikum

Setelah praktikum ini, mahasiswa mampu:

Membuat database db_presensi_pegawai.
Membuat tabel utama aplikasi presensi pegawai.
Mengisi data awal langsung melalui query SQL.
Menjalankan query dasar untuk mengecek data.
Menambahkan library MySQL Connector/J ke project.
Membuat class koneksi database Java ke MySQL.
Mengetes koneksi aplikasi ke database, baik dari console maupun dari browser.
Menampilkan status koneksi database di browser.

Persiapan

Siapkan kebutuhan berikut:

- MySQL / MariaDB
- phpMyAdmin, MySQLFront, atau tools database lain
- Project Java Web Starter
- File mysql-connector-j-9.3.0.jar
- Folder web/WEB-INF/lib/ pada project

Catatan penting:

Fokus praktikum ini:
1. Database
2. Tabel
3. Data awal
4. Query pengecekan
5. Library MySQL Connector/J
6. Koneksi Java ke MySQL
7. Test koneksi dari console
8. Status koneksi di browser

Konsep Singkat

Database digunakan untuk menyimpan data aplikasi secara permanen.

Koneksi database dicek dengan dua cara:

TestConnection.java
   -> DBConnection.java
   -> DBKoneksi.java
   -> MySQL Connector/J
   -> Database db_presensi_pegawai

footer.jsp
   -> db-status.js
   -> /db-status
   -> DBStatusServlet.java
   -> DBKoneksi.java
   -> MySQL Connector/J
   -> Database db_presensi_pegawai

DBKoneksi.java menyimpan detail koneksi ke MySQL.
DBConnection.java dipakai untuk mengambil koneksi dari class lain.
TestConnection.java dipakai untuk test koneksi dari console.
DBStatusServlet.java dipakai untuk test koneksi dari browser.
footer.jsp dan db-status.js menampilkan status koneksi di halaman web.

MySQL Connector/J adalah library .jar agar aplikasi Java dapat terhubung ke MySQL.

Langkah 1 - Membuat Database

Buka phpMyAdmin, MySQLFront, atau tools database yang digunakan.

Jalankan query berikut:

CREATE DATABASE IF NOT EXISTS db_presensi_pegawai
    CHARACTER SET utf8mb4
    COLLATE utf8mb4_unicode_ci;

USE db_presensi_pegawai;

Cek apakah database berhasil dibuat.

SHOW DATABASES;

Langkah 2 - Membuat Tabel Bagian

Tabel bagian digunakan untuk menyimpan data bagian atau divisi pegawai.

CREATE TABLE IF NOT EXISTS bagian (
    id_bagian INT AUTO_INCREMENT PRIMARY KEY,
    nama_bagian VARCHAR(100) NOT NULL UNIQUE
) ENGINE=InnoDB;

Isi data awal bagian:

INSERT IGNORE INTO bagian (nama_bagian) VALUES
('Administrasi'),
('Keuangan'),
('Kepegawaian');

Cek data bagian:

SELECT * FROM bagian;

Langkah 3 - Membuat Tabel Pegawai

Tabel pegawai digunakan untuk menyimpan data pegawai.

CREATE TABLE IF NOT EXISTS pegawai (
    id_pegawai INT AUTO_INCREMENT PRIMARY KEY,
    nip VARCHAR(30) NOT NULL UNIQUE,
    nama_pegawai VARCHAR(100) NOT NULL,
    jenis_kelamin ENUM('L', 'P') NOT NULL,
    no_hp VARCHAR(20) DEFAULT NULL,
    alamat TEXT DEFAULT NULL,
    id_bagian INT NOT NULL,
    status_pegawai ENUM('Aktif', 'Nonaktif') DEFAULT 'Aktif',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_pegawai_bagian
        FOREIGN KEY (id_bagian) REFERENCES bagian(id_bagian)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
) ENGINE=InnoDB;

Simpan id bagian ke variabel SQL agar insert pegawai tidak bergantung pada angka id manual.

SET @bagian_administrasi = (
    SELECT id_bagian FROM bagian WHERE nama_bagian = 'Administrasi' LIMIT 1
);

SET @bagian_keuangan = (
    SELECT id_bagian FROM bagian WHERE nama_bagian = 'Keuangan' LIMIT 1
);

SET @bagian_kepegawaian = (
    SELECT id_bagian FROM bagian WHERE nama_bagian = 'Kepegawaian' LIMIT 1
);

Isi data awal pegawai:

INSERT IGNORE INTO pegawai
(nip, nama_pegawai, jenis_kelamin, no_hp, alamat, id_bagian, status_pegawai)
VALUES
('PGW001', 'Andi Saputra', 'L', '081234567890', 'Bandung', @bagian_administrasi, 'Aktif'),
('PGW002', 'Siti Aminah', 'P', '081234567891', 'Jakarta', @bagian_keuangan, 'Aktif'),
('PGW003', 'Budi Santoso', 'L', '081234567892', 'Surabaya', @bagian_kepegawaian, 'Aktif');

Cek data pegawai:

SELECT * FROM pegawai;

Cek data pegawai beserta nama bagian:

SELECT
    p.id_pegawai,
    p.nip,
    p.nama_pegawai,
    p.jenis_kelamin,
    p.no_hp,
    p.alamat,
    b.nama_bagian,
    p.status_pegawai
FROM pegawai p
JOIN bagian b ON p.id_bagian = b.id_bagian;

Langkah 4 - Membuat Tabel Users

Tabel users digunakan untuk menyimpan akun login aplikasi.

Pada bagian ini, kita membuat tabel users dan mengisi data awal untuk akun admin dan akun pegawai.

CREATE TABLE IF NOT EXISTS users (
    id_user INT AUTO_INCREMENT PRIMARY KEY,
    id_pegawai INT DEFAULT NULL,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    role ENUM('admin', 'pegawai') NOT NULL,
    status_user ENUM('Aktif', 'Nonaktif') DEFAULT 'Aktif',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_users_pegawai
        FOREIGN KEY (id_pegawai) REFERENCES pegawai(id_pegawai)
        ON UPDATE CASCADE
        ON DELETE SET NULL
) ENGINE=InnoDB;

Isi akun admin:

INSERT IGNORE INTO users (id_pegawai, username, password, role, status_user)
VALUES
(NULL, 'admin', 'admin123', 'admin', 'Aktif');

Isi akun pegawai berdasarkan data pegawai:

INSERT IGNORE INTO users (id_pegawai, username, password, role, status_user)
SELECT id_pegawai, nip, 'pegawai123', 'pegawai', 'Aktif'
FROM pegawai
WHERE status_pegawai = 'Aktif';

Cek data user:

SELECT * FROM users;

Cek user beserta pegawai:

SELECT
    u.id_user,
    u.username,
    u.role,
    u.status_user,
    p.nip,
    p.nama_pegawai
FROM users u
LEFT JOIN pegawai p ON u.id_pegawai = p.id_pegawai;

Langkah 5 - Membuat Tabel Presensi

Tabel presensi digunakan untuk menyimpan data presensi pegawai per tanggal.

CREATE TABLE IF NOT EXISTS presensi (
    id_presensi INT AUTO_INCREMENT PRIMARY KEY,
    id_pegawai INT NOT NULL,
    tanggal DATE NOT NULL,
    jam_masuk TIME DEFAULT NULL,
    jam_pulang TIME DEFAULT NULL,
    status_presensi ENUM('Hadir', 'Izin', 'Sakit', 'Alpha') DEFAULT 'Hadir',
    keterangan TEXT DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    UNIQUE KEY uniq_presensi_pegawai_tanggal (id_pegawai, tanggal),

    CONSTRAINT fk_presensi_pegawai
        FOREIGN KEY (id_pegawai) REFERENCES pegawai(id_pegawai)
        ON UPDATE CASCADE
        ON DELETE CASCADE
) ENGINE=InnoDB;

Cek struktur tabel:

DESC presensi;

Langkah 6 - Simulasi Presensi Masuk

Simulasi presensi masuk dilakukan langsung melalui SQL.

INSERT INTO presensi
(id_pegawai, tanggal, jam_masuk, status_presensi)
VALUES
(1, CURDATE(), CURTIME(), 'Hadir');

Cek data presensi:

SELECT * FROM presensi;

Catatan:

Satu pegawai hanya boleh punya satu data presensi pada tanggal yang sama.
Aturan ini dibuat oleh UNIQUE KEY uniq_presensi_pegawai_tanggal.

Langkah 7 - Simulasi Presensi Pulang

Simulasi presensi pulang dilakukan dengan UPDATE data presensi hari ini.

UPDATE presensi
SET jam_pulang = CURTIME()
WHERE id_pegawai = 1
AND tanggal = CURDATE();

Cek hasil presensi:

SELECT
    pr.id_presensi,
    p.nip,
    p.nama_pegawai,
    pr.tanggal,
    pr.jam_masuk,
    pr.jam_pulang,
    pr.status_presensi
FROM presensi pr
JOIN pegawai p ON pr.id_pegawai = p.id_pegawai;

Langkah 8 - Query Laporan Presensi Sederhana

Query ini hanya untuk mengecek gabungan data presensi, pegawai, dan bagian.

SELECT
    pr.tanggal,
    p.nip,
    p.nama_pegawai,
    b.nama_bagian,
    pr.jam_masuk,
    pr.jam_pulang,
    pr.status_presensi,
    pr.keterangan
FROM presensi pr
JOIN pegawai p ON pr.id_pegawai = p.id_pegawai
JOIN bagian b ON p.id_bagian = b.id_bagian
ORDER BY pr.tanggal DESC, p.nama_pegawai ASC;

Langkah 9 - Menambahkan Library MySQL Connector/J

Gunakan file library:

mysql-connector-j-9.3.0.jar

Letakkan file .jar ke folder berikut:

web/WEB-INF/lib/

Struktur folder yang benar:

JavaWebStarter/
    web/
        WEB-INF/
            lib/
                mysql-connector-j-9.3.0.jar

Setelah itu pastikan file .jar benar-benar ada di dalam folder lib.

web/WEB-INF/lib/mysql-connector-j-9.3.0.jar

Langkah 10 - Menyiapkan Class Koneksi Database

Koneksi database dipakai lewat dua file:

dao/DBKoneksi.java
config/DBConnection.java

DBKoneksi.java menyimpan detail koneksi ke MySQL. DBConnection.java dipakai oleh class lain untuk membuka koneksi.

Isi file DBKoneksi.java:

package dao;

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

/**
 * Kelas untuk mengelola koneksi ke database MySQL.
 *
 * CARA PAKAI:
 *   Connection conn = DBKoneksi.getConnection();
 *
 * KONFIGURASI:
 *   Sesuaikan DB_NAME, DB_USER, DB_PASS di bawah
 *   dengan database yang kamu buat di XAMPP.
 */
public class DBKoneksi {

    // ============================================================
    // KONFIGURASI — sesuaikan dengan setup XAMPP kamu
    // ============================================================
    private static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
    private static final String DB_HOST   = "localhost";
    private static final String DB_PORT   = "3306";
    private static final String DB_NAME   = "db_presensi_pegawai";
    private static final String DB_USER   = "root";
    private static final String DB_PASS   = "";                // default XAMPP kosong
    // ============================================================

    private static final String JDBC_URL =
            "jdbc:mysql://" + DB_HOST + ":" + DB_PORT + "/" + DB_NAME
            + "?useSSL=false&serverTimezone=Asia/Jakarta&allowPublicKeyRetrieval=true";

    /**
     * Mengembalikan objek Connection ke database.
     * Lempar SQLException jika gagal konek.
     */
    public static Connection getConnection() throws SQLException {
        try {
            Class.forName(JDBC_DRIVER);
            return DriverManager.getConnection(JDBC_URL, DB_USER, DB_PASS);
        } catch (ClassNotFoundException e) {
            throw new SQLException("MySQL JDBC Driver tidak ditemukan. "
                    + "Pastikan mysql-connector-j sudah ada di Libraries.", e);
        }
    }

    /**
     * Menutup koneksi dengan aman.
     * Gunakan di blok finally setelah selesai query.
     */
    public static void closeConnection(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                System.err.println("Gagal menutup koneksi: " + e.getMessage());
            }
        }
    }

    public static String getJdbcDriver() {
        return JDBC_DRIVER;
    }

    public static String getDbHost() {
        return DB_HOST;
    }

    public static String getDbPort() {
        return DB_PORT;
    }

    public static String getDbName() {
        return DB_NAME;
    }

    public static String getDbUser() {
        return DB_USER;
    }

    public static String getJdbcUrl() {
        return JDBC_URL;
    }
}

Isi file DBConnection.java:

package config;

import dao.DBKoneksi;
import java.sql.Connection;
import java.sql.SQLException;

public class DBConnection {

    public static Connection getConnection() throws SQLException {
        return DBKoneksi.getConnection();
    }

    public static void closeConnection(Connection conn) {
        DBKoneksi.closeConnection(conn);
    }
}

Catatan:

`DBConnection.java` dipakai untuk membuka koneksi dari class lain.
`DBKoneksi.java` menyimpan detail koneksi ke MySQL.

Langkah 11 - Membuat Test Koneksi

Di package config, buat file:

TestConnection.java

Isi file TestConnection.java:

package config;

import java.sql.Connection;

public class TestConnection {

    public static void main(String[] args) {
        try {
            Connection conn = DBConnection.getConnection();

            if (conn != null) {
                System.out.println("Koneksi database berhasil");
            } else {
                System.out.println("Koneksi database gagal");
            }

            conn.close();
        } catch (Exception e) {
            System.out.println("Koneksi database gagal");
            e.printStackTrace();
        }
    }
}

Urutan test dari console:

TestConnection.java -> DBConnection.java -> DBKoneksi.java -> MySQL

Jalankan TestConnection.java.

Jika berhasil, output yang muncul:

Koneksi database berhasil

Test Koneksi dari Browser

Di halaman web, status koneksi ditampilkan di footer.

Sebelum test ini dijalankan, buat file berikut:

web/WEB-INF/views/partials/footer.jsp
web/assets/js/db-status.js
src/java/controller/DBStatusServlet.java

Update juga file CSS yang sudah ada:

web/assets/css/style.css

File ini sudah ada sebelumnya. Pada update program ini, file style.css perlu ditambah atau diubah agar widget status koneksi tampil rapi di halaman.

Tambahkan kode berikut ke bagian bawah web/assets/css/style.css:

/* ============================================================
   JavaWebStarter — style.css
   CSS minimal, cukup untuk praktikum. Tidak perlu framework.
   ============================================================ */

/* Reset dasar */
*, *::before, *::after {
    box-sizing: border-box;
    margin: 0;
    padding: 0;
}

body {
    font-family: 'Segoe UI', Tahoma, Geneva, Verdana, sans-serif;
    font-size: 14px;
    background-color: #f4f6f8;
    color: #333;
}

body:not(.login-page) {
    padding-bottom: 160px;
}

a {
    color: #2563eb;
    text-decoration: none;
}
a:hover {
    text-decoration: underline;
}

/* ============================================================
   LAYOUT UTAMA
   ============================================================ */
#wrapper {
    display: flex;
    flex-direction: column;
    min-height: 100vh;
}

#main {
    display: flex;
    flex: 1;
}

#content {
    flex: 1;
    padding: 24px;
    overflow-y: auto;
}

/* ============================================================
   HEADER
   ============================================================ */
#header {
    background-color: #1e40af;
    color: white;
    padding: 12px 24px;
    display: flex;
    justify-content: space-between;
    align-items: center;
}

#header h1 a {
    color: white;
    font-size: 20px;
}

#header-user {
    display: flex;
    align-items: center;
    gap: 16px;
    font-size: 13px;
}

.role-label,
.badge {
    display: inline-flex;
    align-items: center;
    padding: 3px 8px;
    border-radius: 999px;
    background: #dbeafe;
    color: #1d4ed8;
    font-size: 11px;
    font-weight: 700;
    text-transform: uppercase;
}

.btn-logout {
    background-color: #ef4444;
    color: white !important;
    padding: 6px 14px;
    border-radius: 4px;
    font-size: 12px;
}
.btn-logout:hover {
    background-color: #dc2626;
    text-decoration: none;
}

/* ============================================================
   SIDEBAR
   ============================================================ */
#sidebar {
    width: 200px;
    background-color: #1e3a5f;
    padding-top: 16px;
    flex-shrink: 0;
}

#sidebar ul {
    list-style: none;
}

#sidebar ul li a {
    display: block;
    padding: 12px 20px;
    color: #cbd5e1;
    font-size: 13px;
    transition: background 0.15s;
}

#sidebar ul li a:hover,
#sidebar ul li.active a {
    background-color: #2563eb;
    color: white;
    text-decoration: none;
}

/* ============================================================
   FOOTER
   ============================================================ */
#footer {
    background-color: #e2e8f0;
    text-align: center;
    padding: 10px;
    font-size: 12px;
    color: #64748b;
}

#db-status-widget {
    position: fixed;
    right: 18px;
    bottom: 18px;
    width: 300px;
    padding: 14px 16px;
    border-radius: 16px;
    background: #0f172a;
    color: #e2e8f0;
    border: 1px solid rgba(148, 163, 184, 0.18);
    box-shadow: 0 18px 40px rgba(15, 23, 42, 0.28);
    z-index: 1000;
}

#db-status-widget .db-status-widget__head {
    display: flex;
    justify-content: space-between;
    align-items: center;
    gap: 12px;
    margin-bottom: 10px;
}

#db-status-widget .db-status-widget__title {
    font-size: 12px;
    font-weight: 700;
    letter-spacing: 0.06em;
    text-transform: uppercase;
    color: #cbd5e1;
}

#db-status-widget .db-status-widget__badge {
    display: inline-flex;
    align-items: center;
    gap: 6px;
    padding: 4px 10px;
    border-radius: 999px;
    font-size: 12px;
    font-weight: 700;
    white-space: nowrap;
    background: #334155;
    color: #e2e8f0;
}

#db-status-widget .db-status-widget__badge::before {
    content: '';
    width: 8px;
    height: 8px;
    border-radius: 999px;
    background: #94a3b8;
}

#db-status-widget.is-unknown .db-status-widget__badge {
    background: #334155;
    color: #e2e8f0;
}

#db-status-widget.is-checking .db-status-widget__badge {
    background: #dbeafe;
    color: #1d4ed8;
}

#db-status-widget.is-checking .db-status-widget__badge::before {
    background: #2563eb;
    animation: dbPulse 1.1s ease-in-out infinite;
}

#db-status-widget.is-online .db-status-widget__badge {
    background: #dcfce7;
    color: #166534;
}

#db-status-widget.is-online .db-status-widget__badge::before {
    background: #16a34a;
}

#db-status-widget.is-offline .db-status-widget__badge {
    background: #fee2e2;
    color: #991b1b;
}

#db-status-widget.is-offline .db-status-widget__badge::before {
    background: #ef4444;
}

#db-status-widget .db-status-widget__meta {
    margin-bottom: 12px;
    font-size: 12px;
    line-height: 1.5;
    color: #cbd5e1;
    min-height: 36px;
}

#db-status-widget .db-status-widget__actions {
    display: flex;
    justify-content: flex-end;
}

#db-status-widget .db-status-widget__button {
    width: auto;
    padding: 8px 12px;
    border-radius: 8px;
    background-color: #2563eb;
    color: white;
    font-size: 13px;
}

#db-status-widget .db-status-widget__button:hover {
    background-color: #1d4ed8;
}

#db-status-widget .db-status-widget__button:disabled {
    cursor: wait;
    opacity: 0.75;
}

@keyframes dbPulse {
    0%, 100% {
        transform: scale(1);
        opacity: 0.85;
    }
    50% {
        transform: scale(1.25);
        opacity: 0.45;
    }
}

/* ============================================================
   HALAMAN LOGIN
   ============================================================ */
body.login-page {
    display: flex;
    justify-content: center;
    align-items: center;
    min-height: 100vh;
    background-color: #1e40af;
}

#login-box {
    background: white;
    padding: 40px;
    border-radius: 8px;
    width: 360px;
    box-shadow: 0 4px 20px rgba(0,0,0,0.2);
}

#login-box h1 {
    font-size: 22px;
    color: #1e40af;
    margin-bottom: 4px;
}

#login-box h2 {
    font-size: 14px;
    color: #64748b;
    font-weight: normal;
    margin-bottom: 24px;
}

/* ============================================================
   FORM
   ============================================================ */
.form-group {
    margin-bottom: 16px;
}

.form-group label {
    display: block;
    font-size: 13px;
    font-weight: 600;
    margin-bottom: 6px;
    color: #374151;
}

.form-group input,
.form-group select,
.form-group textarea {
    width: 100%;
    padding: 8px 12px;
    border: 1px solid #d1d5db;
    border-radius: 4px;
    font-size: 14px;
    transition: border-color 0.15s;
}

.form-group input:focus,
.form-group select:focus,
.form-group textarea:focus {
    outline: none;
    border-color: #2563eb;
}

.form-help,
.muted {
    margin-top: 6px;
    color: #64748b;
    font-size: 12px;
}

/* ============================================================
   TOMBOL
   ============================================================ */
button, .btn {
    padding: 8px 20px;
    border: none;
    border-radius: 4px;
    cursor: pointer;
    font-size: 14px;
    font-weight: 600;
}

button[type="submit"], .btn-primary {
    background-color: #2563eb;
    color: white;
    width: 100%;
    padding: 10px;
}
button[type="submit"]:hover, .btn-primary:hover {
    background-color: #1d4ed8;
}

.btn-warning {
    background-color: #f59e0b;
    color: white;
    padding: 5px 12px;
    font-size: 12px;
}

.btn-danger {
    background-color: #ef4444;
    color: white;
    padding: 5px 12px;
    font-size: 12px;
}

.table-actions {
    display: flex;
    gap: 8px;
    flex-wrap: wrap;
    align-items: center;
}

.inline-form {
    display: inline;
}

/* ============================================================
   TABEL
   ============================================================ */
table {
    width: 100%;
    border-collapse: collapse;
    background: white;
    border-radius: 6px;
    overflow: hidden;
    box-shadow: 0 1px 4px rgba(0,0,0,0.08);
}

th {
    background-color: #1e40af;
    color: white;
    padding: 10px 14px;
    text-align: left;
    font-size: 13px;
}

td {
    padding: 10px 14px;
    border-bottom: 1px solid #f1f5f9;
    font-size: 13px;
}

tr:last-child td {
    border-bottom: none;
}

tr.genap { background-color: #f8fafc; }

/* ============================================================
   CARD & KOMPONEN UMUM
   ============================================================ */
.card {
    background: white;
    padding: 24px;
    border-radius: 6px;
    box-shadow: 0 1px 4px rgba(0,0,0,0.08);
    margin-bottom: 20px;
}

.page-header {
    margin-bottom: 20px;
    padding-bottom: 12px;
    border-bottom: 2px solid #e2e8f0;
}

.page-header h2 {
    font-size: 20px;
    color: #1e3a5f;
}

.table-toolbar {
    display: flex;
    justify-content: flex-end;
    margin-bottom: 12px;
}

.btn-add {
    background-color: #16a34a;
    color: white;
    display: inline-block;
}

.btn-add:hover {
    background-color: #15803d;
    text-decoration: none;
}

.btn-secondary {
    background-color: #64748b;
    color: white;
    display: inline-block;
    text-align: center;
}

.btn-secondary:hover {
    background-color: #475569;
    text-decoration: none;
}

.form-actions {
    display: flex;
    gap: 10px;
    margin-top: 8px;
}

.form-actions .btn-primary,
.form-actions .btn-secondary {
    width: auto;
}

.quick-actions,
.presensi-actions {
    display: flex;
    gap: 10px;
    flex-wrap: wrap;
    margin-top: 16px;
}

.stats-grid {
    display: grid;
    grid-template-columns: repeat(auto-fit, minmax(180px, 1fr));
    gap: 14px;
    margin-bottom: 20px;
}

.stat-card {
    background: white;
    border-left: 4px solid #2563eb;
    border-radius: 6px;
    padding: 16px;
    box-shadow: 0 1px 4px rgba(0,0,0,0.08);
}

.stat-card span {
    display: block;
    color: #64748b;
    font-size: 12px;
    margin-bottom: 8px;
}

.stat-card strong {
    display: block;
    color: #1e3a5f;
    font-size: 24px;
    line-height: 1.2;
}

.dashboard-grid {
    display: grid;
    grid-template-columns: repeat(auto-fit, minmax(280px, 1fr));
    gap: 20px;
}

.chart-list {
    display: grid;
    gap: 14px;
    margin-top: 16px;
}

.chart-row__meta {
    display: flex;
    align-items: center;
    justify-content: space-between;
    gap: 12px;
    margin-bottom: 6px;
    color: #334155;
    font-size: 13px;
}

.chart-row__meta strong {
    color: #1e3a5f;
}

.chart-track {
    height: 12px;
    border-radius: 999px;
    background: #e2e8f0;
    overflow: hidden;
}

.chart-fill {
    height: 100%;
    min-width: 0;
    border-radius: 999px;
    background: #2563eb;
}

.chart-fill--green {
    background: #16a34a;
}

.bar-chart {
    height: 220px;
    display: flex;
    align-items: end;
    gap: 14px;
    padding-top: 20px;
    border-bottom: 1px solid #e2e8f0;
    overflow-x: auto;
}

.bar-chart__item {
    min-width: 58px;
    height: 100%;
    display: flex;
    flex-direction: column;
    align-items: center;
    justify-content: flex-end;
    gap: 8px;
}

.bar-chart__bar {
    width: 36px;
    min-height: 8px;
    border-radius: 6px 6px 0 0;
    background: #2563eb;
    color: white;
    display: flex;
    align-items: flex-start;
    justify-content: center;
    padding-top: 6px;
    font-size: 11px;
    font-weight: 700;
}

.bar-chart__item small {
    color: #64748b;
    font-size: 11px;
}

.detail-grid,
.presensi-status {
    display: grid;
    grid-template-columns: repeat(auto-fit, minmax(180px, 1fr));
    gap: 14px;
    margin-top: 16px;
}

.detail-grid > div,
.presensi-status > div {
    border: 1px solid #e2e8f0;
    border-radius: 6px;
    padding: 12px;
    background: #f8fafc;
}

.detail-grid span,
.presensi-status span {
    display: block;
    margin-bottom: 6px;
    color: #64748b;
    font-size: 12px;
}

.detail-grid strong,
.presensi-status strong {
    color: #1e3a5f;
}

.btn-presensi {
    width: 160px;
    padding: 14px 18px;
    color: white;
    font-size: 15px;
}

.btn-datang {
    background-color: #16a34a;
}

.btn-datang:hover {
    background-color: #15803d;
}

.btn-pulang {
    background-color: #ef4444;
}

.btn-pulang:hover {
    background-color: #dc2626;
}

/* Pesan status */
.error   { color: #dc2626; background: #fef2f2; padding: 10px 14px; border-radius: 4px; margin-bottom: 16px; border-left: 4px solid #dc2626; }
.success { color: #16a34a; background: #f0fdf4; padding: 10px 14px; border-radius: 4px; margin-bottom: 16px; border-left: 4px solid #16a34a; }
.info    { color: #2563eb; background: #eff6ff; padding: 10px 14px; border-radius: 4px; margin-bottom: 16px; border-left: 4px solid #2563eb; }

@media (max-width: 640px) {
    body:not(.login-page) {
        padding-bottom: 220px;
    }

    #db-status-widget {
        left: 12px;
        right: 12px;
        bottom: 12px;
        width: auto;
    }
}

Kalau folder belum ada, buat dulu foldernya.

Isi file footer.jsp:

<%@page contentType="text/html" pageEncoding="UTF-8"%>

<footer id="footer">
    <p>JavaWebStarter &copy; 2025 - Praktikum Java Web Programming</p>
</footer>

<aside id="db-status-widget"
       class="db-status-widget"
       data-db-status-widget
       data-endpoint="${pageContext.request.contextPath}/db-status"
       role="status"
       aria-live="polite">
    <div class="db-status-widget__head">
        <span class="db-status-widget__title">Status Koneksi DB</span>
        <span class="db-status-widget__badge is-unknown"
              data-db-status-badge>Belum dicek</span>
    </div>
    <p class="db-status-widget__meta" data-db-status-meta>
        Tekan tombol cek koneksi untuk melihat status terakhir.
    </p>
    <div class="db-status-widget__actions">
        <button type="button" class="db-status-widget__button" data-db-status-button>
            Cek koneksi
        </button>
    </div>
</aside>

<script src="${pageContext.request.contextPath}/assets/js/db-status.js"></script>

Isi file db-status.js:

(function () {
    function initDbStatusWidget() {
        const widget = document.querySelector('[data-db-status-widget]');
        if (!widget) {
            return;
        }

        const endpoint = widget.dataset.endpoint;
        const badge = widget.querySelector('[data-db-status-badge]');
        const meta = widget.querySelector('[data-db-status-meta]');
        const button = widget.querySelector('[data-db-status-button]');

        if (!endpoint || !badge || !meta || !button) {
            return;
        }

        function setState(state, badgeText, metaText) {
            widget.classList.remove('is-unknown', 'is-checking', 'is-online', 'is-offline');
            widget.classList.add(state);
            badge.textContent = badgeText;
            meta.textContent = metaText;
        }

        async function checkConnection() {
            setState('is-checking', 'Mengecek...', 'Sedang menghubungi MySQL...');
            button.disabled = true;
            button.textContent = 'Mengecek...';

            try {
                const response = await fetch(endpoint, {
                    headers: {
                        'Accept': 'application/json'
                    }
                });

                const data = await response.json();

                if (data.ok) {
                    setState(
                        'is-online',
                        'Online',
                        `${data.message}. DB: ${data.database}. Waktu: ${data.responseTimeMs} ms. Terakhir: ${data.checkedAt}.`
                    );
                } else {
                    setState(
                        'is-offline',
                        'Offline',
                        `${data.message}${data.errorMessage ? ` - ${data.errorMessage}` : ''}`
                    );
                }
            } catch (error) {
                setState(
                    'is-offline',
                    'Offline',
                    error && error.message
                        ? error.message
                        : 'Gagal mengambil status koneksi.'
                );
            } finally {
                button.disabled = false;
                button.textContent = 'Cek koneksi';
            }
        }

        button.addEventListener('click', checkConnection);
        checkConnection();
    }

    document.addEventListener('DOMContentLoaded', initDbStatusWidget);
})();

Isi file DBStatusServlet.java:

package controller;

import dao.DBKoneksi;
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;
import java.sql.Connection;
import java.sql.SQLException;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;

@WebServlet(name = "DBStatusServlet", urlPatterns = {"/db-status"})
public class DBStatusServlet extends HttpServlet {

    private static final DateTimeFormatter TIME_FORMATTER =
            DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");

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

        response.setCharacterEncoding("UTF-8");
        response.setContentType("application/json;charset=UTF-8");

        long startedAt = System.currentTimeMillis();
        boolean ok = false;
        String message = "Koneksi database gagal";
        String errorMessage = null;
        String database = DBKoneksi.getDbName();
        String checkedAt = LocalDateTime.now().format(TIME_FORMATTER);

        try (Connection conn = DBKoneksi.getConnection()) {
            ok = true;
            message = "Koneksi database berhasil";
            String catalog = conn.getCatalog();
            if (catalog != null && !catalog.isBlank()) {
                database = catalog;
            }
        } catch (SQLException e) {
            errorMessage = e.getMessage();
        }

        long responseTimeMs = System.currentTimeMillis() - startedAt;

        StringBuilder json = new StringBuilder();
        json.append('{');
        appendJsonField(json, "ok", ok);
        json.append(',');
        appendJsonField(json, "message", message);
        json.append(',');
        appendJsonField(json, "database", database);
        json.append(',');
        appendJsonField(json, "checkedAt", checkedAt);
        json.append(',');
        appendJsonField(json, "responseTimeMs", responseTimeMs);

        if (errorMessage != null) {
            json.append(',');
            appendJsonField(json, "errorMessage", errorMessage);
        }

        json.append('}');
        response.getWriter().write(json.toString());
    }

    private static void appendJsonField(StringBuilder json, String key, boolean value) {
        json.append('"').append(escapeJson(key)).append('"')
            .append(':')
            .append(value);
    }

    private static void appendJsonField(StringBuilder json, String key, long value) {
        json.append('"').append(escapeJson(key)).append('"')
            .append(':')
            .append(value);
    }

    private static void appendJsonField(StringBuilder json, String key, String value) {
        json.append('"').append(escapeJson(key)).append('"')
            .append(':')
            .append('"').append(escapeJson(value)).append('"');
    }

    private static String escapeJson(String value) {
        if (value == null) {
            return "";
        }

        StringBuilder escaped = new StringBuilder(value.length() + 16);
        for (int i = 0; i < value.length(); i++) {
            char ch = value.charAt(i);
            switch (ch) {
                case '"' -> escaped.append("\\\"");
                case '\\' -> escaped.append("\\\\");
                case '\b' -> escaped.append("\\b");
                case '\f' -> escaped.append("\\f");
                case '\n' -> escaped.append("\\n");
                case '\r' -> escaped.append("\\r");
                case '\t' -> escaped.append("\\t");
                default -> {
                    if (ch < 0x20) {
                        escaped.append(String.format("\\u%04x", (int) ch));
                    } else {
                        escaped.append(ch);
                    }
                }
            }
        }
        return escaped.toString();
    }
}

Alurnya:

footer.jsp -> db-status.js -> /db-status -> DBStatusServlet.java -> DBKoneksi.java -> MySQL

DBStatusServlet mengembalikan data status koneksi, lalu db-status.js membaca data itu dan menampilkan hasilnya di footer.

Pengujian

Lakukan pengujian berikut:

Database db_presensi_pegawai berhasil dibuat.
Tabel bagian, pegawai, users, dan presensi berhasil dibuat.
Data awal bagian berhasil tampil.
Data awal pegawai berhasil tampil.
Data awal user berhasil tampil.
Simulasi presensi masuk berhasil masuk ke tabel presensi.
Simulasi presensi pulang berhasil mengisi jam_pulang.
Query laporan sederhana berhasil menampilkan gabungan data presensi, pegawai, dan bagian.
File mysql-connector-j-9.3.0.jar sudah ada di web/WEB-INF/lib/.
Class dao/DBKoneksi.java berhasil membuka koneksi.
Class config/DBConnection.java berhasil memanggil koneksi dari DBKoneksi.java.
TestConnection.java menampilkan output Koneksi database berhasil.
Widget status koneksi di footer menampilkan status database.

Troubleshooting

Masalah	Penyebab	Solusi
`Unknown database 'db_presensi_pegawai'`	Database belum dibuat	Jalankan query `CREATE DATABASE` lalu `USE db_presensi_pegawai`
`Table doesn't exist`	Tabel belum dibuat	Jalankan ulang query `CREATE TABLE` sesuai urutan
Data pegawai gagal insert	`id_bagian` tidak ditemukan	Pastikan tabel `bagian` sudah diisi lebih dulu
Duplicate entry pada presensi	Pegawai yang sama sudah presensi di tanggal yang sama	Gunakan data pegawai lain atau hapus data presensi untuk tanggal tersebut
`No suitable driver found`	MySQL Connector/J belum terbaca	Pastikan `mysql-connector-j-9.3.0.jar` ada di `web/WEB-INF/lib/`
`Communications link failure`	MySQL belum berjalan atau port salah	Jalankan MySQL dan cek port `3306`
`Access denied for user 'root'`	Username atau password MySQL salah	Sesuaikan `DB_USER` dan `DB_PASS` pada `dao/DBKoneksi.java`
Koneksi gagal tetapi database sudah ada	Nama database, host, port, user, atau password tidak sesuai	Cek ulang konfigurasi koneksi di `dao/DBKoneksi.java`
Widget status DB di footer tidak tampil	`footer.jsp` belum memuat `db-status.js`, `style.css` belum di-update, atau endpoint `/db-status` gagal	Pastikan `footer.jsp` memuat script, `style.css` sudah ditambah style widget, `DBStatusServlet.java` ada, lalu deploy ulang aplikasi

Tugas Praktikum

Tambahkan minimal 2 data bagian baru langsung melalui SQL.
Tambahkan minimal 3 data pegawai baru langsung melalui SQL.
Buat akun user pegawai berdasarkan NIP pegawai yang baru ditambahkan.
Lakukan simulasi presensi masuk untuk salah satu pegawai baru.
Lakukan simulasi presensi pulang untuk pegawai yang sama.
Jalankan query laporan presensi sederhana dan screenshot hasilnya.
Jalankan TestConnection.java dan screenshot output koneksi berhasil.

Kesimpulan

Pada pertemuan ini, mahasiswa sudah membuat database dasar aplikasi presensi pegawai.

Output akhir Pertemuan 08:

Database db_presensi_pegawai
Tabel bagian
Tabel pegawai
Tabel users
Tabel presensi
Data awal bagian
Data awal pegawai
Data awal user
Simulasi presensi masuk
Simulasi presensi pulang
Query laporan presensi sederhana
Library MySQL Connector/J terpasang
dao/DBKoneksi.java
config/DBConnection.java
config/TestConnection.java
controller/DBStatusServlet.java
footer.jsp + db-status.js + style.css
Koneksi Java ke MySQL berhasil dari console dan browser