기본 CRUD 작업 구현

JDBC(Java Database Connectivity)는 자바 애플리케이션에서 관계형 데이터베이스와 상호작용할 수 있도록 해주는 표준 API입니다. 아래 코드는 MySQL을 대상으로 SELECT, INSERT, UPDATE, DELETE 작업을 수행하는 예제입니다.

import java.sql.*;

public class DatabaseHandler {
    private static final String DB_URL = "jdbc:mysql://localhost:3306/test?useSSL=false&characterEncoding=utf8&serverTimezone=UTC";
    private static final String DB_USER = "root";
    private static final String DB_PASS = "root";

    public ResultSet fetchData(String condition) throws SQLException {
        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS);
             PreparedStatement stmt = conn.prepareStatement("SELECT * FROM users WHERE name = ?")) {
            stmt.setString(1, condition);
            return stmt.executeQuery();
        }
    }

    public int modifyRecord(String newValue, String targetName) throws SQLException {
        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS);
             PreparedStatement stmt = conn.prepareStatement("UPDATE users SET email = ? WHERE name = ?")) {
            stmt.setString(1, newValue);
            stmt.setString(2, targetName);
            return stmt.executeUpdate();
        }
    }

    public long addRecord(String name, String email) throws SQLException {
        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS);
             PreparedStatement stmt = conn.prepareStatement(
                 "INSERT INTO users (name, email) VALUES (?, ?)", 
                 Statement.RETURN_GENERATED_KEYS)) {
            stmt.setString(1, name);
            stmt.setString(2, email);
            stmt.executeUpdate();
            try (ResultSet generatedKeys = stmt.getGeneratedKeys()) {
                if (generatedKeys.next()) {
                    return generatedKeys.getLong(1);
                }
            }
            return -1;
        }
    }

    public int removeRecord(String name) throws SQLException {
        try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS);
             PreparedStatement stmt = conn.prepareStatement("DELETE FROM users WHERE name = ?")) {
            stmt.setString(1, name);
            return stmt.executeUpdate();
        }
    }
}

트랜잭션 관리

데이터베이스 트랜잭션은 ACID 특성을 보장합니다:

• 원자성(Atomicity): 모든 작업이 성공하거나 모두 실패해야 함
• 일관성(Consistency): 트랜잭션 전후에 데이터 무결성 유지
• 격리성(Isolation): 동시 실행 시 서로 간섭하지 않음
• 지속성(Durability): 커밋된 데이터는 영구 저장

다음은 JDBC에서 트랜잭션을 명시적으로 제어하는 예제입니다:

public void executeTransaction() throws SQLException {
    try (Connection conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASS)) {
        conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
        conn.setAutoCommit(false);
        try {
            try (PreparedStatement stmt1 = conn.prepareStatement("DELETE FROM orders WHERE user_id = ?")) {
                stmt1.setInt(1, 101);
                stmt1.executeUpdate();
            }
            try (PreparedStatement stmt2 = conn.prepareStatement("DELETE FROM users WHERE id = ?")) {
                stmt2.setInt(1, 101);
                stmt2.executeUpdate();
            }
            conn.commit();
        } catch (Exception e) {
            conn.rollback();
            throw e;
        } finally {
            conn.setAutoCommit(true);
        }
    }
}

연결 풀 사용하기

매 요청마다 새로운 데이터베이스 연결을 생성하는 것은 비효율적이므로, HikariCP와 같은 연결 풀 라이브러리를 사용하는 것이 일반적입니다. Maven 의존성은 다음과 같습니다:

<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
    <version>5.0.1</version>
</dependency>

HikariCP를 통합한 예제:

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import javax.sql.DataSource;
import java.sql.*;

public class PooledDatabaseHandler {
    private static final String DB_URL = "jdbc:mysql://localhost:3306/test?useSSL=false&characterEncoding=utf8&serverTimezone=UTC";
    private static final String DB_USER = "root";
    private static final String DB_PASS = "root";

    private static DataSource dataSource;

    static {
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl(DB_URL);
        config.setUsername(DB_USER);
        config.setPassword(DB_PASS);
        config.setMaximumPoolSize(10);
        config.setConnectionTimeout(1000);
        config.setIdleTimeout(30000);
        dataSource = new HikariDataSource(config);
    }

    public ResultSet queryUsers(String name) throws SQLException {
        try (Connection conn = dataSource.getConnection();
             PreparedStatement stmt = conn.prepareStatement("SELECT * FROM users WHERE name = ?")) {
            stmt.setString(1, name);
            return stmt.executeQuery();
        }
    }
}