JDBC Concept & Connection

| พฤศจิกายน 6, 2025

บทนำ: ทำไมต้อง Database?

เมื่อเขียนโปรแกรม ข้อมูลทั้งหมดจะเก็บใน memory (RAM) ของ program:

java// ❌ ปัญหา: Data หายเมื่อปิด program
List<User> users = new ArrayList<>();
users.add(new User(1, "John"));
users.add(new User(2, "Jane"));

// เมื่อ program จบ ข้อมูลหายไป!
// ครั้งหน้า run ได้ 0 users

ทำไมต้อง Database:

  • Persistent – ข้อมูลคงอยู่แม้ program จบ
  • Scalable – สามารถเก็บข้อมูลขนาดใหญ่
  • Multi-user – หลาย program/user เข้าถึงพร้อมกัน
  • Query – สามารถค้นหาข้อมูลแบบ complex

JDBC (Java Database Connectivity) = วิธี Java connect กับ database

JDBC คืออะไร?

JDBC = API ที่ให้ Java program communicate กับ database

text┌────────────┐
│ Java Code  │
│ (Your App) │
└─────┬──────┘
      │
      │ JDBC API
      ▼
┌─────────────────────────────────────┐
│ JDBC Driver (ขึ้นอยู่กับ DB vendor)  │
│ - MySQL Driver                      │
│ - PostgreSQL Driver                 │
│ - Oracle Driver                     │
│ - etc.                              │
└─────────────────────────────────────┘
      │
      │ Database Protocol
      ▼
┌─────────────────────────────────────┐
│ Database Server                     │
│ - MySQL                             │
│ - PostgreSQL                        │
│ - Oracle                            │
│ - SQL Server                        │
│ - etc.                              │
└─────────────────────────────────────┘

JDBC Connection Flow

ขั้นตอนการทำงาน

text1. Load Driver
   └─ Class.forName("driver class")

2. Get Connection
   └─ DriverManager.getConnection(url, user, password)

3. Create Statement
   └─ connection.createStatement()

4. Execute Query
   └─ statement.executeQuery() / executeUpdate()

5. Process Results
   └─ ResultSet

6. Close Resources
   └─ close connection, statement, resultset

ตัวอย่างที่ 1: JDBC Connection พื้นฐาน

Setup: MySQL Database

sql-- Create database
CREATE DATABASE store_db;

-- Create table
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    age INT
);

-- Insert sample data
INSERT INTO users (name, email, age) VALUES ('John Doe', '[email protected]', 25);
INSERT INTO users (name, email, age) VALUES ('Jane Smith', '[email protected]', 30);

Maven Dependency: pom.xml

xml<!-- MySQL Connector -->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.33</version>
</dependency>

Code: Connection Example

javaimport java.sql.*;

public class DatabaseConnection {
    
    // Database connection parameters
    private static final String DB_URL = 
        "jdbc:mysql://localhost:3306/store_db";
    private static final String DB_USER = "root";
    private static final String DB_PASSWORD = "password";
    private static final String DB_DRIVER = 
        "com.mysql.cj.jdbc.Driver";
    
    public static void main(String[] args) {
        Connection connection = null;
        
        try {
            // STEP 1: Load the JDBC Driver
            System.out.println("Loading JDBC Driver...");
            Class.forName(DB_DRIVER);
            System.out.println("✓ Driver loaded successfully");
            
            // STEP 2: Establish Connection
            System.out.println("\nConnecting to database...");
            connection = DriverManager.getConnection(
                DB_URL, 
                DB_USER, 
                DB_PASSWORD
            );
            System.out.println("✓ Connected successfully!");
            
            // STEP 3: Test connection with simple query
            String query = "SELECT * FROM users";
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(query);
            
            // STEP 4: Process results
            System.out.println("\n=== Users in Database ===");
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                String email = resultSet.getString("email");
                int age = resultSet.getInt("age");
                
                System.out.printf(
                    "ID: %d | Name: %s | Email: %s | Age: %d%n",
                    id, name, email, age
                );
            }
            
            // STEP 5: Close resources
            resultSet.close();
            statement.close();
            
        } catch (ClassNotFoundException e) {
            System.err.println("❌ JDBC Driver not found");
            e.printStackTrace();
        } catch (SQLException e) {
            System.err.println("❌ Database error");
            e.printStackTrace();
        } finally {
            // Always close connection
            if (connection != null) {
                try {
                    connection.close();
                    System.out.println("\n✓ Connection closed");
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}

// Output:
// Loading JDBC Driver...
// ✓ Driver loaded successfully
// 
// Connecting to database...
// ✓ Connected successfully!
// 
// === Users in Database ===
// ID: 1 | Name: John Doe | Email: [email protected] | Age: 25
// ID: 2 | Name: Jane Smith | Email: [email protected] | Age: 30
// 
// ✓ Connection closed

ตัวอย่างที่ 2: Connection Helper Class

ปัญหา: Repeating Connection Code

java// ❌ ไม่ดี: Connection code ซ้ำกันทุก class
public class UserDAO {
    public void getUser() {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection conn = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/store_db", 
                "root", 
                "password"
            );
            // ... code ...
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

public class ProductDAO {
    public void getProduct() {
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection conn = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/store_db", 
                "root", 
                "password"
            );
            // ... code ...
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
// ← การ hardcode connection ไม่ดี

วิธีแก้: Database Connection Helper

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

public class DatabaseConfig {
    // Configuration constants
    private static final String DB_URL = 
        "jdbc:mysql://localhost:3306/store_db";
    private static final String DB_USER = "root";
    private static final String DB_PASSWORD = "password";
    private static final String DB_DRIVER = 
        "com.mysql.cj.jdbc.Driver";
    
    // Static block: Load driver once when class is loaded
    static {
        try {
            Class.forName(DB_DRIVER);
            System.out.println("✓ JDBC Driver loaded");
        } catch (ClassNotFoundException e) {
            System.err.println("❌ Failed to load JDBC Driver");
            e.printStackTrace();
        }
    }
    
    /**
     * Get a database connection
     * @return Connection object
     * @throws SQLException if connection fails
     */
    public static Connection getConnection() throws SQLException {
        return DriverManager.getConnection(
            DB_URL, 
            DB_USER, 
            DB_PASSWORD
        );
    }
    
    /**
     * Close connection safely
     * @param connection to close
     */
    public static void closeConnection(Connection connection) {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                System.err.println("❌ Error closing connection");
                e.printStackTrace();
            }
        }
    }
}

// ✓ ใช้งาน: ง่ายขึ้น
public class UserDAO {
    public void getUser(int userId) {
        Connection connection = null;
        try {
            connection = DatabaseConfig.getConnection();
            // ... code ...
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DatabaseConfig.closeConnection(connection);
        }
    }
}

public class ProductDAO {
    public void getProduct(int productId) {
        Connection connection = null;
        try {
            connection = DatabaseConfig.getConnection();
            // ... code ...
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DatabaseConfig.closeConnection(connection);
        }
    }
}

ตัวอย่างที่ 3: Try-With-Resources (Auto-Close)

ปัญหา: ลืม close resources

java// ❌ ไม่ดี: ต้อง manually close ทุกอย่าง
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;

try {
    conn = DatabaseConfig.getConnection();
    stmt = conn.createStatement();
    rs = stmt.executeQuery("SELECT * FROM users");
    
    while (rs.next()) {
        System.out.println(rs.getString("name"));
    }
} catch (SQLException e) {
    e.printStackTrace();
} finally {
    if (rs != null) try { rs.close(); } catch (SQLException e) {}
    if (stmt != null) try { stmt.close(); } catch (SQLException e) {}
    if (conn != null) try { conn.close(); } catch (SQLException e) {}
}
// ← ยาว ซ้ำซ้อน easy to forget

วิธีแก้: Try-With-Resources

java// ✓ ดี: Auto-close when done
try (Connection conn = DatabaseConfig.getConnection();
     Statement stmt = conn.createStatement();
     ResultSet rs = stmt.executeQuery("SELECT * FROM users")) {
    
    while (rs.next()) {
        System.out.println(rs.getString("name"));
    }
    
} catch (SQLException e) {
    System.err.println("❌ Database error: " + e.getMessage());
    e.printStackTrace();
}
// ← สั้นกว่า auto-close ทั้งหมด

ตัวอย่างที่ 4: Complete UserDAO with Connection

Entity Class

javapublic class User {
    private int id;
    private String name;
    private String email;
    private int age;
    
    // Constructor
    public User(int id, String name, String email, int age) {
        this.id = id;
        this.name = name;
        this.email = email;
        this.age = age;
    }
    
    // Getters
    public int getId() { return id; }
    public String getName() { return name; }
    public String getEmail() { return email; }
    public int getAge() { return age; }
    
    @Override
    public String toString() {
        return String.format(
            "User{id=%d, name='%s', email='%s', age=%d}",
            id, name, email, age
        );
    }
}

DAO Class with JDBC

javaimport java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class UserDAO {
    
    /**
     * Retrieve all users from database
     */
    public List<User> getAllUsers() {
        List<User> users = new ArrayList<>();
        String query = "SELECT * FROM users";
        
        try (Connection conn = DatabaseConfig.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(query)) {
            
            while (rs.next()) {
                User user = new User(
                    rs.getInt("id"),
                    rs.getString("name"),
                    rs.getString("email"),
                    rs.getInt("age")
                );
                users.add(user);
            }
            
            System.out.println("✓ Retrieved " + users.size() + " users");
            
        } catch (SQLException e) {
            System.err.println("❌ Error retrieving users: " + e.getMessage());
        }
        
        return users;
    }
    
    /**
     * Retrieve user by ID
     */
    public User getUserById(int userId) {
        String query = "SELECT * FROM users WHERE id = ?";
        
        try (Connection conn = DatabaseConfig.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(query)) {
            
            // Set parameter
            pstmt.setInt(1, userId);
            
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    User user = new User(
                        rs.getInt("id"),
                        rs.getString("name"),
                        rs.getString("email"),
                        rs.getInt("age")
                    );
                    System.out.println("✓ User found: " + user.getName());
                    return user;
                }
            }
            
            System.out.println("⚠ User with ID " + userId + " not found");
            
        } catch (SQLException e) {
            System.err.println("❌ Error retrieving user: " + e.getMessage());
        }
        
        return null;
    }
    
    /**
     * Retrieve users by age range
     */
    public List<User> getUsersByAgeRange(int minAge, int maxAge) {
        List<User> users = new ArrayList<>();
        String query = "SELECT * FROM users WHERE age >= ? AND age <= ?";
        
        try (Connection conn = DatabaseConfig.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(query)) {
            
            pstmt.setInt(1, minAge);
            pstmt.setInt(2, maxAge);
            
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    User user = new User(
                        rs.getInt("id"),
                        rs.getString("name"),
                        rs.getString("email"),
                        rs.getInt("age")
                    );
                    users.add(user);
                }
            }
            
            System.out.println("✓ Found " + users.size() + 
                " users between age " + minAge + "-" + maxAge);
            
        } catch (SQLException e) {
            System.err.println("❌ Error: " + e.getMessage());
        }
        
        return users;
    }
}

// ==== Usage Example ====
public class Main {
    public static void main(String[] args) {
        UserDAO userDAO = new UserDAO();
        
        // Get all users
        System.out.println("=== All Users ===");
        List<User> allUsers = userDAO.getAllUsers();
        allUsers.forEach(System.out::println);
        
        // Get user by ID
        System.out.println("\n=== Get User by ID ===");
        User user = userDAO.getUserById(1);
        System.out.println(user);
        
        // Get users by age range
        System.out.println("\n=== Users aged 25-30 ===");
        List<User> filtered = userDAO.getUsersByAgeRange(25, 30);
        filtered.forEach(System.out::println);
    }
}

// Output:
// === All Users ===
// ✓ Retrieved 2 users
// User{id=1, name='John Doe', email='[email protected]', age=25}
// User{id=2, name='Jane Smith', email='[email protected]', age=30}
// 
// === Get User by ID ===
// ✓ User found: John Doe
// User{id=1, name='John Doe', email='[email protected]', age=25}
// 
// === Users aged 25-30 ===
// ✓ Found 2 users between age 25-30
// User{id=1, name='John Doe', email='[email protected]', age=25}
// User{id=2, name='Jane Smith', email='[email protected]', age=30}

ตัวอย่างที่ 5: Connection Pool (แบบ Mock)

ปัญหา: Creating Connection Expensive

text❌ ปัญหา:
- สร้าง connection ใหม่ทุกครั้งช้า
- TCP handshake กับ database
- Database overhead สูง
- Performance ลดลง

วิธีแก้: Connection Pool

javaimport java.sql.Connection;
import java.util.Queue;
import java.util.LinkedList;

/**
 * Simple Connection Pool (Mock implementation for demonstration)
 * In production, use HikariCP, Tomcat Connection Pool, etc.
 */
public class SimpleConnectionPool {
    
    private static final int POOL_SIZE = 5;
    private Queue<Connection> availableConnections;
    private int totalConnections;
    
    public SimpleConnectionPool() {
        availableConnections = new LinkedList<>();
        totalConnections = 0;
    }
    
    /**
     * Get connection from pool
     * If pool empty, create new one (up to POOL_SIZE)
     */
    public synchronized Connection getConnection() throws Exception {
        if (availableConnections.isEmpty()) {
            if (totalConnections < POOL_SIZE) {
                // Create new connection
                Connection conn = DatabaseConfig.getConnection();
                totalConnections++;
                System.out.println("✓ Created new connection. Total: " + totalConnections);
                return conn;
            } else {
                // Pool full, wait for available
                System.out.println("⏳ Pool full, waiting...");
                wait();
                return availableConnections.poll();
            }
        } else {
            Connection conn = availableConnections.poll();
            System.out.println("✓ Got connection from pool. Available: " + 
                availableConnections.size());
            return conn;
        }
    }
    
    /**
     * Return connection to pool
     */
    public synchronized void releaseConnection(Connection connection) {
        if (connection != null) {
            availableConnections.offer(connection);
            System.out.println("✓ Released connection to pool");
            notifyAll();
        }
    }
}

// ==== Usage ====
public class PoolDemo {
    public static void main(String[] args) throws Exception {
        SimpleConnectionPool pool = new SimpleConnectionPool();
        
        // Get 3 connections
        Connection conn1 = pool.getConnection();
        Connection conn2 = pool.getConnection();
        Connection conn3 = pool.getConnection();
        
        System.out.println("\n--- Returning connections ---");
        
        // Return connections
        pool.releaseConnection(conn1);
        pool.releaseConnection(conn2);
        pool.releaseConnection(conn3);
        
        System.out.println("\n--- Reusing connections ---");
        
        // Reuse connections
        Connection conn4 = pool.getConnection();
        Connection conn5 = pool.getConnection();
    }
}

// Output:
// ✓ Created new connection. Total: 1
// ✓ Created new connection. Total: 2
// ✓ Created new connection. Total: 3
// 
// --- Returning connections ---
// ✓ Released connection to pool
// ✓ Released connection to pool
// ✓ Released connection to pool
// 
// --- Reusing connections ---
// ✓ Got connection from pool. Available: 2
// ✓ Got connection from pool. Available: 1

Key JDBC Concepts

Statement vs PreparedStatement

java// ❌ Statement (Vulnerable to SQL Injection)
String query = "SELECT * FROM users WHERE id = " + userId;
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery(query);
// Problem: If userId = "1 OR 1=1", returns ALL users!

// ✓ PreparedStatement (Safe)
String query = "SELECT * FROM users WHERE id = ?";
PreparedStatement pstmt = conn.prepareStatement(query);
pstmt.setInt(1, userId);  // ← Parameter binding
ResultSet rs = pstmt.executeQuery();
// Safe: Parameters treated as data, not code

Exception Handling Strategies

java// Strategy 1: Log and throw
try {
    // database operation
} catch (SQLException e) {
    System.err.println("Database error: " + e.getMessage());
    throw new RuntimeException("Failed to access database", e);
}

// Strategy 2: Log and return default
try {
    // database operation
} catch (SQLException e) {
    System.err.println("Database error: " + e.getMessage());
    return new ArrayList<>();  // Empty list
}

// Strategy 3: Create custom exception
public class DataAccessException extends RuntimeException {
    public DataAccessException(String message, SQLException cause) {
        super(message, cause);
    }
}

try {
    // database operation
} catch (SQLException e) {
    throw new DataAccessException("Failed to access database", e);
}

Best Practices: JDBC Checklist

text✓ DO:
  ☑ Use try-with-resources for auto-close
  ☑ Use PreparedStatement for parameterized queries
  ☑ Handle SQLException appropriately
  ☑ Use connection pool in production
  ☑ Centralize database configuration
  ☑ Log database operations
  ☑ Validate input before querying
  ☑ Close resources in finally block
  ☑ Use specific exception messages
  ☑ Test connections before use

✗ DON'T:
  ☐ String concatenation for SQL (SQL Injection!)
  ☐ Load driver multiple times
  ☐ Leave connections open
  ☐ Ignore SQLExceptions
  ☐ Hardcode database credentials
  ☐ Use Statement for dynamic queries
  ☐ Create new connection per query
  ☐ Mix business logic with database code
  ☐ Forget to handle null ResultSet

สรุป

JDBC Concept & Connection ไม่ใช่แค่ “connect ไปที่ database” แต่เป็นการออกแบบ reliable database communication:

ความเข้าใจที่สำคัญ:

  • JDBC Driver → Bridge ระหว่าง Java กับ Database
  • Connection → Channel เพื่อ communicate
  • Statement/PreparedStatement → Execute queries safely
  • ResultSet → Represent query results
  • Resource Management → Always close connections

Best Practices:

  • Try-with-resources → Auto-close resources
  • PreparedStatement → Prevent SQL Injection
  • Connection Pool → Reuse connections, improve performance
  • Error Handling → Handle SQLException gracefully
  • Configuration → Centralize database settings

เมื่อใช้ JDBC อย่างถูกต้อง:

  • ปลอดภัย – SQL Injection ไม่ได้
  • Efficient – Connection pool ใช้ทรัพยากรน้อย
  • Maintainable – Code clean และ organized
  • Scalable – Ready สำหรับ production use

JDBC คือ “สะพาน” ระหว่าง Java application กับ database – มันต้องมั่นคง, ปลอดภัย, และ efficient เพื่อให้ data flow ได้อย่างนิ่มนวล

เรื่องนี้คุณอาจชอบ