Praktik Mandiri 7: Java Database Connectivity (JDBC)

Langkah 1: Persiapan Lingkungan

1. Install Java JDK
2. Install database (MySQL/PostgreSQL/H2 Database)
3. Download JDBC driver sesuai database:
   • MySQL: MySQL Connector/J
   • PostgreSQL: JDBC Driver
   • H2: Otomatis tersedia di Maven

Langkah 2: Membuat Project & Setup

Buat project Java baru dan tambahkan JDBC driver ke:

• IDE: Tambahkan JAR ke classpath
• Maven/Gradle: Tambahkan dependency

Contoh Maven untuk MySQL:

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.33</version>
</dependency>

Latihan 1: Koneksi Database

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

public class JdbcExample {
    static final String DB_URL = "jdbc:mysql://localhost:3306/mydatabase";
    static final String USER = "root";
    static final String PASS = "password";

    public static void main(String[] args) {
        try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS)) {
            System.out.println("Connected to database!");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

Tugas:

1. Ubah kode untuk menangani ClassNotFoundException
2. Coba dengan database berbeda (misal H2 in-memory database)

Latihan 2: Membuat Tabel

import java.sql.Statement;

// Dalam blok try-with-resources
try(
    Connection conn = DriverManager.getConnection(DB_URL, USER, PASS);
    Statement stmt = conn.createStatement()
) {
    String sql = "CREATE TABLE employees (" +
                 "id INT PRIMARY KEY AUTO_INCREMENT," +
                 "name VARCHAR(255) NOT NULL," +
                 "position VARCHAR(255)," +
                 "salary DECIMAL(10,2))";
    
    stmt.executeUpdate(sql);
    System.out.println("Table created!");
}

Tugas:

1. Tambahkan kolom ’email’ dengan constraint UNIQUE
2. Buat tabel department terpisah dan relasikan dengan employees

Latihan 3: CRUD Operations

Create (Insert):

String insertSQL = "INSERT INTO employees(name, position, salary) VALUES ('John Doe', 'Developer', 75000.00)";
stmt.executeUpdate(insertSQL);

Read (Select):

ResultSet rs = stmt.executeQuery("SELECT * FROM employees");
while(rs.next()) {
    int id = rs.getInt("id");
    String name = rs.getString("name");
    String position = rs.getString("position");
    double salary = rs.getDouble("salary");
    System.out.printf("%d: %s - %s (%.2f)\n", id, name, position, salary);
}

Update:

String updateSQL = "UPDATE employees SET salary = 80000.00 WHERE name = 'John Doe'";
stmt.executeUpdate(updateSQL);

Delete:

String deleteSQL = "DELETE FROM employees WHERE id = 1";
stmt.executeUpdate(deleteSQL);

Tugas:

1. Buat method terpisah untuk setiap operasi CRUD
2. Implementasikan pencarian berdasarkan kriteria
3. Tambahkan validasi input

---

Latihan 4: PreparedStatement

String insertSQL = "INSERT INTO employees(name, position, salary) VALUES (?, ?, ?)";
try(PreparedStatement pstmt = conn.prepareStatement(insertSQL)) {
    pstmt.setString(1, "Jane Smith");
    pstmt.setString(2, "Manager");
    pstmt.setDouble(3, 95000.00);
    pstmt.executeUpdate();
}

Tugas:

1. Buat form input sederhana untuk memasukkan data employee
2. Implementasikan batch insert untuk multiple records

Latihan 5: Transaction Management

try(Connection conn = DriverManager.getConnection(DB_URL, USER, PASS)) {
    conn.setAutoCommit(false);  // Start transaction
    
    // Operasi 1
    // Operasi 2
    
    conn.commit();  // Commit jika semua berhasil
} catch (SQLException e) {
    conn.rollback();  // Rollback jika ada error
}

Tugas:

1. Implementasikan transfer gaji antar employee
2. Handle transaction isolation levels

---

Latihan 6: Connection Pooling (Advanced)

Gunakan library seperti HikariCP:

HikariConfig config = new HikariConfig();
config.setJdbcUrl(DB_URL);
config.setUsername(USER);
config.setPassword(PASS);

try(HikariDataSource ds = new HikariDataSource(config);
    Connection conn = ds.getConnection()) {
    // Gunakan koneksi
}

Tugas:

1. Bandingkan performa dengan dan tanpa connection pooling
2. Konfigurasi pool size dan parameter lainnya

Latihan 7: Exception Handling

try {
    // Kode JDBC
} catch (SQLException e) {
    System.err.println("SQL State: " + e.getSQLState());
    System.err.println("Error Code: " + e.getErrorCode());
    System.err.println("Message: " + e.getMessage());
}

Tugas:

1. Buat custom exception handler
2. Implementasikan retry mechanism untuk koneksi

---

Bonus: Stored Procedure

String callSQL = "{call increase_salaries(?)}";
try(CallableStatement cstmt = conn.prepareCall(callSQL)) {
    cstmt.setDouble(1, 0.10);  // 10% increase
    cstmt.execute();
}

Testing & Validasi

1. Jalankan setiap latihan dan verifikasi hasil di database
2. Gunakan unit testing dengan JUnit
3. Implementasikan logging untuk tracking operasi

Referensi Tambahan:

1. Coba implementasikan DAO Pattern
2. Eksplor ORM framework seperti Hibernate
3. Pelajari tentang JDBC Batch Processing
4. Coba implementasikan RowMapper

Latihan ini dirancang untuk dilakukan secara bertahap. Mulai dari dasar dan lanjutkan ke topik yang lebih kompleks setelah memahami konsep sebelumnya. Selamat belajar!