# 15 JDBC(Java Database Connectivity)

p255 - build.gradle

```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 

```java
public interface DatabaseConnectionManager {
    Connection getConnection() throws SQLException;

    void closeConnection(Connection connection);
}

```

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

```java
@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

```java
@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

```java
@Data
public class Users {

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

```

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

```java
@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

```java
@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());
    }
}
```


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://team-everywhere.gitbook.io/backend/spring/spring-boot/15-jdbc-java-database-connectivity.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.