15 JDBC(Java Database Connectivity)

p255 - build.gradle

plugins {
	id 'java'
	id 'org.springframework.boot' version '3.3.4'
	id 'io.spring.dependency-management' version '1.1.6'
}

group = 'spring'
version = '0.0.1-SNAPSHOT'

java {
	toolchain {
		languageVersion = JavaLanguageVersion.of(21)
	}
}

configurations {
	compileOnly {
		extendsFrom annotationProcessor
	}
}

repositories {
	mavenCentral()
}

dependencies {
	implementation 'org.springframework.boot:spring-boot-starter-jdbc'
	implementation 'org.springframework.boot:spring-boot-starter-web'
	testImplementation 'org.projectlombok:lombok'
	compileOnly 'org.projectlombok:lombok'
	runtimeOnly 'com.mysql:mysql-connector-j'
	annotationProcessor 'org.projectlombok:lombok'
	testImplementation 'org.springframework.boot:spring-boot-starter-test'
	testRuntimeOnly 'org.junit.platform:junit-platform-launcher'

	testCompileOnly 'org.projectlombok:lombok'
	testAnnotationProcessor 'org.projectlombok:lombok'
}

tasks.named('test') {
	useJUnitPlatform()
}

p258 - src/main/java/spring/jdbc/connection/DatabaseConnectionManager

public interface DatabaseConnectionManager {
    Connection getConnection() throws SQLException;

    void closeConnection(Connection connection);
}

p259 - src/main/java/spring/jdbc/connection/JdbcDatabaseConnectionManager

@Slf4j
public class JdbcDatabaseConnectionManager implements DatabaseConnectionManager{

    private static final String URL = "jdbc:mysql://localhost:3306/test";
    private static final String USERNAME = "root";
    private static final String PASSWORD = "1234";
    @Override
    public Connection getConnection() throws SQLException {
        try {
            Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);
            log.info("connection = {}", connection);
            return connection;
        } catch (SQLException e) {
            e.printStackTrace();
            throw e;
        }
    }

    @Override
    public void closeConnection(Connection connection) {
        if (connection != null) {
            try {
                connection.close();
                log.info("Connection closed.");
            } catch (SQLException e) {
                log.error("Failed to close connection", e);
            }
        }
    }
}

p260 - src/test/java/spring/jdbc/connection/JdbcDatabaseConnectionManagerTest

@Slf4j
class JdbcDatabaseConnectionManagerTest {
    private JdbcDatabaseConnectionManager connectionManager;

    @BeforeEach
    void setUp() {
        connectionManager = new JdbcDatabaseConnectionManager();
    }
    @Test
    void testGetConnection() {
        Connection connection = null;
        try {
            // 실제 데이터베이스에 연결 시도
            connection = connectionManager.getConnection();
            log.info("connection = {}", connection);
            assertNotNull(connection);
            assertFalse(connection.isClosed());
            log.info("Connection established successfully!");

        } catch (SQLException e) {
            fail("Connection failed: " + e.getMessage());
        } finally {
            // 연결 닫기
            if (connection != null) {
                connectionManager.closeConnection(connection);
            }
        }
    }
}

p262 - src/main/java/spring/jdbc/domain/Users

@Data
public class Users {

    private String userId;
    private String userName;
    private int age;
}

p263, 264, 266, 273 - src/main/java/spring/jdbc/repository/UsersRepository

@Slf4j
public class UsersRepository {

    private final JdbcDatabaseConnectionManager connectionManager;

    public UsersRepository(JdbcDatabaseConnectionManager connectionManager) {
        this.connectionManager = connectionManager;
    }
    public Users save(Users user) {
        String sql = "INSERT INTO users (userId, userName, age) VALUES (?, ?, ?)";

        Connection con = null;
        PreparedStatement pstmt = null;

        try {
            // 데이터베이스 연결
            con = connectionManager.getConnection();

            // PreparedStatement 생성
            pstmt = con.prepareStatement(sql);

            // PreparedStatement 파라미터 설정
            pstmt.setString(1, user.getUserId());
            pstmt.setString(2, user.getUserName());
            pstmt.setInt(3, user.getAge());

            // 쿼리 실행
            int affectedRows = pstmt.executeUpdate();
            log.info("affectedRows = {}", affectedRows);
            return user;

        } catch (SQLException e) {
            log.error("Failed to save user: {}", e.getMessage());
            throw new RuntimeException("Failed to save user", e);
        } finally {
            // 자원 해제
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
                if (con != null) {
                    connectionManager.closeConnection(con);
                }
            } catch (SQLException e) {
                log.error("Failed to close resources", e);
            }
        }
    }

    // 사용자 조회 로직 (findById)
    public Users findById(String userId) {
        String sql = "SELECT * FROM users WHERE userId = ?";

        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            // 데이터베이스 연결
            con = connectionManager.getConnection();

            // PreparedStatement 생성
            pstmt = con.prepareStatement(sql);
            pstmt.setString(1, userId);

            // 쿼리 실행 및 결과 처리
            rs = pstmt.executeQuery();

            if (rs.next()) {
                Users user = new Users();
                user.setUserId(rs.getString("userId"));
                user.setUserName(rs.getString("userName"));
                user.setAge(rs.getInt("age"));
                log.info("Found user: {}", user);
                return user;
            } else {
                log.warn("User with ID {} not found", userId);
                return null;
            }
        } catch (SQLException e) {
            log.error("Failed to find user: {}", e.getMessage());
            throw new RuntimeException("Failed to find user", e);
        } finally {
            // 자원 해제
            try {
                if (rs != null) {
                    rs.close();
                }
                if (pstmt != null) {
                    pstmt.close();
                }
                if (con != null) {
                    connectionManager.closeConnection(con);
                }
            } catch (SQLException e) {
                log.error("Failed to close resources", e);
            }
        }
    }

    // 사용자 업데이트 로직 (update)
    public Users update(Users user) {
        String sql = "UPDATE users SET userName = ?, age = ? WHERE userId = ?";

        Connection con = null;
        PreparedStatement pstmt = null;

        try {
            // 데이터베이스 연결
            con = connectionManager.getConnection();

            // PreparedStatement 생성
            pstmt = con.prepareStatement(sql);

            // PreparedStatement 파라미터 설정
            pstmt.setString(1, user.getUserName());
            pstmt.setInt(2, user.getAge());
            pstmt.setString(3, user.getUserId());

            // 쿼리 실행
            int affectedRows = pstmt.executeUpdate();
            log.info("Updated affectedRows = {}", affectedRows);

            if (affectedRows > 0) {
                log.info("User updated successfully: {}", user.getUserId());
                return user;
            } else {
                log.warn("No user found with ID {}", user.getUserId());
                return null;
            }

        } catch (SQLException e) {
            log.error("Failed to update user: {}", e.getMessage());
            throw new RuntimeException("Failed to update user", e);
        } finally {
            // 자원 해제
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
                if (con != null) {
                    connectionManager.closeConnection(con);
                }
            } catch (SQLException e) {
                log.error("Failed to close resources", e);
            }
        }
    }

    // 사용자 삭제 로직 (deleteById)
    public void deleteById(String userId) {
        String sql = "DELETE FROM users WHERE userId = ?";

        Connection con = null;
        PreparedStatement pstmt = null;

        try {
            // 데이터베이스 연결
            con = connectionManager.getConnection();

            // PreparedStatement 생성
            pstmt = con.prepareStatement(sql);
            pstmt.setString(1, userId);

            // 쿼리 실행
            int affectedRows = pstmt.executeUpdate();
            log.info("Deleted affectedRows = {}", affectedRows);

            if (affectedRows > 0) {
                log.info("User with ID {} deleted successfully", userId);
            } else {
                log.warn("No user found with ID {}", userId);
            }

        } catch (SQLException e) {
            log.error("Failed to delete user: {}", e.getMessage());
            throw new RuntimeException("Failed to delete user", e);
        } finally {
            // 자원 해제
            try {
                if (pstmt != null) {
                    pstmt.close();
                }
                if (con != null) {
                    connectionManager.closeConnection(con);
                }
            } catch (SQLException e) {
                log.error("Failed to close resources", e);
            }
        }
    }
}

p265, 269, 270, 271, 272, 274 - src/test/java/spring/jdbc/repository/UsersRepositoryTest

@Slf4j
class UsersRepositoryTest {

    private UsersRepository usersRepository;
    private JdbcDatabaseConnectionManager connectionManager;

    @BeforeEach
    void setUp() throws SQLException {
        // 테스트용 DB에 연결
        connectionManager = new JdbcDatabaseConnectionManager();
        usersRepository = new UsersRepository(connectionManager);
        try (Connection connection = connectionManager.getConnection();
             Statement stmt = connection.createStatement()) {
            stmt.executeUpdate("DELETE FROM users");
            stmt.executeUpdate("ALTER TABLE users AUTO_INCREMENT = 1"); // AUTO_INCREMENT 초기화
        }
    }

    @Test
    void testSaveUser() {
        // 테스트용 유저 생성
        Users user = new Users();
        user.setUserId("AT001");
        user.setUserName("JohnDoe");
        user.setAge(30);

        // 유저 저장 테스트
        Users savedUser = usersRepository.save(user);

        // 저장된 유저의 정보 확인
        assertNotNull(savedUser);
        assertEquals("AT001", savedUser.getUserId());
        assertEquals("JohnDoe", savedUser.getUserName());
        assertEquals(30, savedUser.getAge());

        log.info("User saved successfully: " + savedUser.getUserName());
    }

    @Test
    void testFindUserById() {
        // 먼저 사용자 저장
        Users user = new Users();
        user.setUserId("AT001");
        user.setUserName("JaneDoe");
        user.setAge(25);
        Users savedUser = usersRepository.save(user);

        // 저장된 사용자의 ID로 조회 테스트
        Users foundUser = usersRepository.findById(String.valueOf(savedUser.getUserId()));

        log.info("savedUser = {}", savedUser);
        log.info("foundUser = {}", foundUser);

        // 조회된 사용자 정보 확인
        assertNotNull(foundUser);
        assertEquals("AT001", foundUser.getUserId());
        assertEquals("JaneDoe", foundUser.getUserName());
        assertEquals(25, foundUser.getAge());

        log.info("User found successfully: " + foundUser.getUserName());
    }

    @Test
    void testFindUserByIdNotFound() {
        // 존재하지 않는 ID로 조회 테스트
        Users foundUser = usersRepository.findById("999");

        // 조회 결과가 null인지 확인
        assertNull(foundUser);

        log.info("User with ID 999 not found");
    }

    @Test
    void testUpdateUser() {
        // 먼저 사용자 저장
        Users user = new Users();
        user.setUserId("AT002");
        user.setUserName("JaneDoe");
        user.setAge(25);
        Users savedUser = usersRepository.save(user);

        // 수정할 사용자 정보 설정
        savedUser.setUserName("JaneUpdated");
        savedUser.setAge(28);

        // 사용자 정보 수정
        Users updatedUser = usersRepository.update(savedUser);

        // 수정된 사용자 정보 확인
        assertNotNull(updatedUser);
        assertEquals("JaneUpdated", updatedUser.getUserName());
        assertEquals(28, updatedUser.getAge());

        log.info("User updated successfully: " + updatedUser.getUserName());
    }

    @Test
    void testDeleteUser() {
        // 먼저 사용자 저장
        Users user = new Users();
        user.setUserId("AT003");
        user.setUserName("JohnToDelete");
        user.setAge(40);
        Users savedUser = usersRepository.save(user);

        // 사용자 삭제
        usersRepository.deleteById(savedUser.getUserId());

        // 삭제된 사용자가 없는지 확인
        Users deletedUser = usersRepository.findById(savedUser.getUserId());
        assertNull(deletedUser);

        log.info("User deleted successfully: " + savedUser.getUserName());
    }
}