18 JDBC 템플릿 활용

p355 - src/resources/application.properties

# Application Name
spring.application.name=template

# MySQL Database Configuration
spring.datasource.url=jdbc:mysql://localhost:3306/test
spring.datasource.username=root
spring.datasource.password=1234

# HikariCP Configuration
spring.datasource.hikari.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.hikari.connection-timeout=30000
spring.datasource.hikari.maximum-pool-size=10
spring.datasource.hikari.pool-name=HikariPool

# MyBatis
mybatis.type-aliases-package=spring.jdbc.template.domain
mybatis.configuration.map-underscore-to-camel-case=true
logging.level.spring.jdbc.template.mybatis=trace
logging.level.spring.jdbc.template.repository=trace
logging.level.org.springframework.transaction.interceptor=TRACE

# JPA
logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE

p356 - build.gradle

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

group = 'spring.jdbc'
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'
	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'

	implementation 'org.mybatis.spring.boot:mybatis-spring-boot-starter:3.0.3' // MyBatis 의존성 추가
	implementation 'org.springframework.boot:spring-boot-starter-data-jpa' // JPA 추가

}

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

p357 - src/main/java/spring/jdbc/template/domain/User

@Getter @Setter
public class User {

    private Long userIdx;
    private String userId;
    private int age;

    public User(String userId, int age) {
        this.userId = userId;
        this.age = age;
    }
}

p358 - src/main/java/spring/jdbc/template/repository/UserRepository

public interface UserRepository {

    // 유저 저장 메서드
    User save(User user);

    // 유저 정보 업데이트 메서드
    void update(Long userIdx, UserUpdateDto userUpdateDto);

    // 유저 ID로 찾기 메서드
    Optional<User> findByIdx(Long userIdx);

    // 모든 유저 찾기 메서드 (검색 조건을 이용할 수 있음)
    List<User> findAll(UserListDto dto);
}

p359 ~ p363 - src/main/java/spring/jdbc/template/repository/UserRepositoryImpl

@Repository
public class UserRepositoryImpl implements UserRepository {

    private final JdbcTemplate jdbcTemplate;

    // JdbcTemplate을 생성자 주입으로 받음
    public UserRepositoryImpl(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    // RowMapper를 사용하여 ResultSet을 User 객체로 변환
    private final RowMapper<User> userRowMapper = (rs, rowNum) -> {
        User user = new User(rs.getString("userId"), rs.getInt("age"));
        user.setUserIdx(rs.getLong("userIdx"));
        return user;
    };

    @Override
    public User save(User user) {
        String sql = "INSERT INTO users (userId, age) VALUES (?, ?)";
        KeyHolder keyHolder = new GeneratedKeyHolder();

        jdbcTemplate.update(connection -> {
            PreparedStatement pstmt = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
            pstmt.setString(1, user.getUserId());
            pstmt.setInt(2, user.getAge());
            return pstmt;
        }, keyHolder);

        // 생성된 userIdx 값을 User 객체에 설정
        user.setUserIdx(keyHolder.getKey().longValue());
        return user;
    }

    @Override
    public void update(Long userIdx, UserUpdateDto updateParam) {
        String sql = "UPDATE users SET userId = ?, age = ? WHERE userIdx = ?";
        jdbcTemplate.update(sql, updateParam.getUserId(), updateParam.getAge(), userIdx);
    }

    @Override
    public Optional<User> findByIdx(Long userIdx) {
        String sql = "SELECT * FROM users WHERE userIdx = ?";
        List<User> result = jdbcTemplate.query(sql, userRowMapper, userIdx);
        return result.stream().findAny(); // Optional로 반환
    }

    @Override
    public List<User> findAll(UserListDto dto) {
        String sql = "SELECT * FROM users WHERE 1=1";

        // 동적 쿼리 생성
        StringBuilder query = new StringBuilder(sql);
        if (dto.getUserId() != null && !dto.getUserId().isEmpty()) {
            query.append(" AND userId LIKE '%").append(dto.getUserId()).append("%'");
        }
        if (dto.getMinAge() != null) {
            query.append(" AND age >= ").append(dto.getMinAge());
        }
        if (dto.getMaxAge() != null) {
            query.append(" AND age <= ").append(dto.getMaxAge());
        }

        return jdbcTemplate.query(query.toString(), userRowMapper);
    }
}

p364 - src/main/java/spring/jdbc/template/service/UserService

public interface UserService {

    // 유저 생성
    User save(User user);

    // 유저 정보 업데이트
    void update(Long userIdx, UserUpdateDto userUpdateDto);

    // 유저 ID로 찾기
    Optional<User> findByIdx(Long userIdx);

    // 모든 유저 조회 (검색 조건 적용)
    List<User> findAll(UserListDto userListDto);
}

p365 - src/main/java/spring/jdbc/template/service/UserServiceImpl

@Service
public class UserServiceImpl implements UserService {

    private final UserRepository userRepository;

    // UserRepository를 생성자 주입
//    public UserServiceImpl(@Qualifier("userRepositoryImpl")UserRepository userRepository) {
//    public UserServiceImpl(@Qualifier("userRepositoryNameParameterImpl")UserRepository userRepository) {
    public UserServiceImpl(@Qualifier("userRepositoryMyBatisImpl")UserRepository userRepository) {
        this.userRepository = userRepository;
    }

    @Override
    public User save(User user) {
        // 유저를 저장하는 로직
        return userRepository.save(user);
    }

    @Override
    public void update(Long userIdx, UserUpdateDto updateParam) {
        // 유저 정보를 업데이트하는 로직
        userRepository.update(userIdx, updateParam);
    }

    @Override
    public Optional<User> findByIdx(Long userIdx) {
        // 유저 ID로 유저를 조회하는 로직
        return userRepository.findByIdx(userIdx);
    }

    @Override
    public List<User> findAll(UserListDto userListDto) {
        // 유저 목록을 검색 조건에 맞춰 조회하는 로직
        return userRepository.findAll(userListDto);
    }
}

p367 ~ 370 - src/main/java/spring/jdbc/template/repository/UserRepositoryNameParameterImpl

@Repository
public class UserRepositoryNameParameterImpl implements UserRepository {

    private final NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    // NamedParameterJdbcTemplate을 생성자 주입으로 받음
    public UserRepositoryNameParameterImpl(DataSource dataSource) {
        this.namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
    }

    // RowMapper를 사용하여 ResultSet을 User 객체로 변환
    private final RowMapper<User> userRowMapper = (rs, rowNum) -> {
        User user = new User(rs.getString("userId"), rs.getInt("age"));
        user.setUserIdx(rs.getLong("userIdx"));
        return user;
    };

    @Override
    public User save(User user) {
        String sql = "INSERT INTO users (userId, age) VALUES (:userId, :age)";
        KeyHolder keyHolder = new GeneratedKeyHolder();

        // 파라미터 설정
        MapSqlParameterSource params = new MapSqlParameterSource()
                .addValue("userId", user.getUserId())
                .addValue("age", user.getAge());

        // update 메서드를 통해 INSERT 쿼리 실행
        namedParameterJdbcTemplate.update(sql, params, keyHolder, new String[]{"userIdx"});

        // 생성된 userIdx 값을 User 객체에 설정
        user.setUserIdx(keyHolder.getKey().longValue());
        return user;
    }

    @Override
    public void update(Long userIdx, UserUpdateDto dto) {
        String sql = "UPDATE users SET userId = :userId, age = :age WHERE userIdx = :userIdx";

        // 파라미터 설정
        MapSqlParameterSource params = new MapSqlParameterSource()
                .addValue("userId", dto.getUserId())
                .addValue("age", dto.getAge())
                .addValue("userIdx", userIdx);

        // update 메서드를 통해 UPDATE 쿼리 실행
        namedParameterJdbcTemplate.update(sql, params);
    }

    @Override
    public Optional<User> findByIdx(Long userIdx) {
        String sql = "SELECT * FROM users WHERE userIdx = :userIdx";

        // 파라미터 설정
        Map<String, Object> params = new HashMap<>();
        params.put("userIdx", userIdx);

        List<User> result = namedParameterJdbcTemplate.query(sql, params, userRowMapper);
        return result.stream().findAny(); // Optional로 반환
    }

    @Override
    public List<User> findAll(UserListDto dto) {
        String sql = "SELECT * FROM users WHERE 1=1";

        // 동적 쿼리 생성 및 파라미터 설정
        StringBuilder query = new StringBuilder(sql);
        MapSqlParameterSource params = new MapSqlParameterSource();

        if (dto.getUserId() != null && !dto.getUserId().isEmpty()) {
            query.append(" AND userId LIKE :userId");
            params.addValue("userId", "%" + dto.getUserId() + "%");
        }
        if (dto.getMinAge() != null) {
            query.append(" AND age >= :minAge");
            params.addValue("minAge", dto.getMinAge());
        }
        if (dto.getMaxAge() != null) {
            query.append(" AND age <= :maxAge");
            params.addValue("maxAge", dto.getMaxAge());
        }

        return namedParameterJdbcTemplate.query(query.toString(), params, userRowMapper);
    }
}

p372 ~ p375 - src/test/java/spring/jdbc/template/repository/UserRepositryNameParameterImplTest

@Slf4j
@Transactional
@SpringBootTest
class UserRepositoryNameParameterImplTest {

    @Autowired
    @Qualifier("userRepositoryNameParameterImpl") // 특정 빈을 명시적으로 주입
    private UserRepository userRepository;

    @Autowired
    private JdbcTemplate jdbcTemplate;  // JdbcTemplate 주입

    private Long userIdx1;
    private Long userIdx2;

    @BeforeEach
    void setUp() {
        // 데이터를 저장하고 각각의 userIdx를 저장
        User user1 = userRepository.save(new User("testUser1", 25));
        User user2 = userRepository.save(new User("testUser2", 32));
        userIdx1 = user1.getUserIdx();
        userIdx2 = user2.getUserIdx();
    }

    @Test
    void testSave() {
        User user = new User("testUser3", 35);
        User savedUser = userRepository.save(user);

        assertNotNull(savedUser.getUserIdx()); // Auto-generated ID should not be null
        assertEquals("testUser3", savedUser.getUserId());
        assertEquals(35, savedUser.getAge());
    }

    @Test
    void testUpdate() {
        Optional<User> userOpt = userRepository.findByIdx(userIdx1);
        assertTrue(userOpt.isPresent());

        User user = userOpt.get();
        UserUpdateDto dto = new UserUpdateDto("updatedUser1",40);
        dto.setAge(40);
        dto.setUserId("updatedUser1");

        userRepository.update(user.getUserIdx(), dto);

        Optional<User> updatedUserOpt = userRepository.findByIdx(user.getUserIdx());
        assertTrue(updatedUserOpt.isPresent());

        User updatedUser = updatedUserOpt.get();
        assertEquals("updatedUser1", updatedUser.getUserId());
        assertEquals(40, updatedUser.getAge());
    }

    @Test
    void testFindByIdx() {
        // userIdx1을 기준으로 조회
        Optional<User> userOpt = userRepository.findByIdx(userIdx1);
        assertTrue(userOpt.isPresent());

        User user = userOpt.get();
        assertEquals("testUser1", user.getUserId());
        assertEquals(25, user.getAge());
    }

    @Test
    void testFindAll() {
        UserListDto dto = new UserListDto(null, 20, 35);
        List<User> users = userRepository.findAll(dto);
        assertEquals(2, users.size());
    }

    @Test
    void testFindAllWithUserIdFilter() {
        UserListDto dto = new UserListDto("testUser1", null, null);
        List<User> users = userRepository.findAll(dto);

        assertEquals(1, users.size());
        assertEquals("testUser1", users.get(0).getUserId());
    }
}