스프링 DB 접근 기술

테이블 생성

drop table if exists member CASCADE;
create table member
(
 id bigint generated by default as identity,
 name varchar(255),
 primary key (id)
);

순수 JDBC

build.gradle 파일에

implementation 'org.springframework.boot:spring-boot-starter-jdbc'
runtimeOnly 'com.h2database:h2'

스프링 부트 DB 연결 추가

spring.datasource.url=jdbc:h2:tcp://localhost/~/test
spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.username=sa

JDBC 회원 Repository

public class JdbcMemberRepository implements MemberRepository {
 private final DataSource dataSource;
 public JdbcMemberRepository(DataSource dataSource) {
 this.dataSource = dataSource;
 }
 
 @Override
 public Member save(Member member) {
 String sql = "insert into member(name) values(?)";
 Connection conn = null;
 PreparedStatement pstmt = null;
 ResultSet rs = null;
 try {
 conn = getConnection();
 pstmt = conn.prepareStatement(sql,
Statement.RETURN_GENERATED_KEYS);
 pstmt.setString(1, member.getName());
 pstmt.executeUpdate();
 rs = pstmt.getGeneratedKeys();
 if (rs.next()) {
 member.setId(rs.getLong(1));
 } else {
 throw new SQLException("id 조회 실패");
 }
 return member;
 } catch (Exception e) {
 throw new IllegalStateException(e);
 } finally {
 close(conn, pstmt, rs);
 }
 }
 
 @Override
 public Optional<Member> findById(Long id) {
 String sql = "select * from member where id = ?";
 Connection conn = null;
 PreparedStatement pstmt = null;
 ResultSet rs = null;
 try {
 conn = getConnection();
 pstmt = conn.prepareStatement(sql);
 pstmt.setLong(1, id);
 rs = pstmt.executeQuery();
 if(rs.next()) {
 Member member = new Member();
 member.setId(rs.getLong("id"));
 member.setName(rs.getString("name"));
 return Optional.of(member);
 } else {
 return Optional.empty();
 }
 } catch (Exception e) {
 throw new IllegalStateException(e);
 } finally {
 close(conn, pstmt, rs);
 }
 }
 
 @Override
 public List<Member> findAll() {
 String sql = "select * from member";
 Connection conn = null;
 PreparedStatement pstmt = null;
 ResultSet rs = null;
 try {
 conn = getConnection();
 pstmt = conn.prepareStatement(sql);
 rs = pstmt.executeQuery();
 List<Member> members = new ArrayList<>();
 while(rs.next()) {
 Member member = new Member();
 member.setId(rs.getLong("id"));
 member.setName(rs.getString("name"));
 members.add(member);
 }
 return members;
 } catch (Exception e) {
 throw new IllegalStateException(e);
 } finally {
 close(conn, pstmt, rs);
 }
 }
 
 @Override
 public Optional<Member> findByName(String name) {
 String sql = "select * from member where name = ?";
 Connection conn = null;
 PreparedStatement pstmt = null;
 ResultSet rs = null;
 try {
 conn = getConnection();
 pstmt = conn.prepareStatement(sql);
 pstmt.setString(1, name);
 rs = pstmt.executeQuery();
 if(rs.next()) {
 Member member = new Member();
 member.setId(rs.getLong("id"));
 member.setName(rs.getString("name"));
 return Optional.of(member);
 }
 return Optional.empty();
 } catch (Exception e) {
 throw new IllegalStateException(e);
 } finally {
 close(conn, pstmt, rs);
 }
 }
 private Connection getConnection() {
 return DataSourceUtils.getConnection(dataSource);
 }
 private void close(Connection conn, PreparedStatement pstmt, ResultSet rs)
{
 try {
 if (rs != null) {
 rs.close();
 }
 } catch (SQLException e) {
 e.printStackTrace();
 }
 try {
 if (pstmt != null) {
 pstmt.close();
 }
 } catch (SQLException e) {
 e.printStackTrace();
 }
 try {
 if (conn != null) {
 close(conn);
 }
 } catch (SQLException e) {
 e.printStackTrace();
 }
 }
 private void close(Connection conn) throws SQLException {
 DataSourceUtils.releaseConnection(conn, dataSource);
 }
}

스프링 설정 변경

@Configuration
public class SpringConfig {
 private final DataSource dataSource;
 public SpringConfig(DataSource dataSource) {
 this.dataSource = dataSource;
 }
 @Bean
 public MemberService memberService() {
 return new MemberService(memberRepository());
 }
 @Bean
 public MemberRepository memberRepository() {
// return new MemoryMemberRepository();
return new JdbcMemberRepository(dataSource);
 }
}

DataSource는 DB connection을 획득할때 사용하는 객체

개방 - 폐쇠 원칙
확장에는 열려있고, 수정 변경에는 닫혀있다.
Spring DI를 사용하면 기존코드를 손대지 않고 설정만으로 구현 클래스 변경 가능
회원을 등록 DB에 결과각 잘 입력되는지 확인

@SpringBootTest
@Transactional
class MemberServiceIntegrationTest {
 @Autowired MemberService memberService;
 @Autowired MemberRepository memberRepository;
 @Test
 public void 회원가입() throws Exception {
 //Given
 Member member = new Member();
 member.setName("hello");
 //When
 Long saveId = memberService.join(member);
 //Then
 Member findMember = memberRepository.findById(saveId).get();
 assertEquals(member.getName(), findMember.getName());
 }
 @Test
 public void 중복_회원_예외() throws Exception {
 //Given
 Member member1 = new Member();
 member1.setName("spring");
 Member member2 = new Member();
 member2.setName("spring");
 //When
 memberService.join(member1);
 IllegalStateException e = assertThrows(IllegalStateException.class,
 () -> memberService.join(member2));//예외가 발생해야 한다.
 assertThat(e.getMessage()).isEqualTo("이미 존재하는 회원입니다.");
 }
}

@SpringBootTest : 스프링 컨테이너와 테스트를 함께 실행
@Transactional : 테스트 케이스에 이 에노테이션 있다면, 태스트 시작 전에 트랜잭션을 시작, 테스트 완료후에 항상 rollback, -> 이렇게 하면 DB에 데이터 남아 있지 않는다.

Spring JDBC Template
-> 스프링 JDBC Template과 MyBatis 같은 라이브러리는 JDBC API에서 본 반복 코드를 대부분 제거, 단 SQL은 직접 제거

public class JdbcTemplateMemberRepository implements MemberRepository {
 private final JdbcTemplate jdbcTemplate;
 public JdbcTemplateMemberRepository(DataSource dataSource) {
 jdbcTemplate = new JdbcTemplate(dataSource);
 }
 @Override
 public Member save(Member member) {
 SimpleJdbcInsert jdbcInsert = new SimpleJdbcInsert(jdbcTemplate);
 jdbcInsert.withTableName("member").usingGeneratedKeyColumns("id");
 Map<String, Object> parameters = new HashMap<>();
 parameters.put("name", member.getName());
 Number key = jdbcInsert.executeAndReturnKey(new
MapSqlParameterSource(parameters));
 member.setId(key.longValue());
 return member;
 }
 @Override
 public Optional<Member> findById(Long id) {
 List<Member> result = jdbcTemplate.query("select * from member where id 
= ?", memberRowMapper(), id);
 return result.stream().findAny();
 }
 @Override
 public List<Member> findAll() {
 return jdbcTemplate.query("select * from member", memberRowMapper());
 }
 @Override
 public Optional<Member> findByName(String name) {
 List<Member> result = jdbcTemplate.query("select * from member where 
name = ?", memberRowMapper(), name);
 return result.stream().findAny();
 }
 private RowMapper<Member> memberRowMapper() {
 return (rs, rowNum) -> {
 Member member = new Member();
 member.setId(rs.getLong("id"));
 member.setName(rs.getString("name"));
 return member;
 };
 }
}

JDBC Template 사용하도록 스프링 설정 변경

@Configuration
public class SpringConfig {
 private final DataSource dataSource;
 public SpringConfig(DataSource dataSource) {
 this.dataSource = dataSource;
 }
 @Bean
 public MemberService memberService() {
 return new MemberService(memberRepository());
 }
 @Bean
 public MemberRepository memberRepository() {
// return new MemoryMemberRepository();
// return new JdbcMemberRepository(dataSource);
 return new JdbcTemplateMemberRepository(dataSource);
 }
}