[SpringBoot][Gradle]DB접근기술-순수JDBC

 

강의에 사용된 H2 Database 설치와 사용법은 포스트를 참고해주세요.

2025.01.08 - [💾 Data Base] - H2 Databas 설치부터 사용까지

 

---

 

환경설정

 

1. build.gradle 파일에 jdbc, h2 Database 관련 라이브러리 추가.

   

   
   
   1. implementation 'org.springframework.boot:spring-boot-starter-jdbc'
      → java는 DB랑 연결하려면 기본적으로  jdbc가 필요하다.
      
      
   2. runtimeOnly 'com.h2database:h2'
      → 데이터베이스가 제공하는 클라이언트.
      
      
   3. testImplementation('org.springframework.boot:spring-boot-starter-test') {
          exclude group: 'org.junit.vintage', module: 'junit-vintage-engine'
      }
      
      
2. src-main-resources-application.properties파일에 아래 코드를 적어준다.
   

   

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

 

---

 

사용법

 

1. Repository
   
   • src-main-java-hello-hellospring-repository패키지 안에 JdbcMemberRepository클래스를 만든다.

     

   • 여기서는 스프링 순수JDBC를 사용하는 법이고 제가 JDBC에 대해 간단히 정리해준 게 있습니다.
     2025.01.01 - [☕ JAVA/☕ 개발환경] - [JAVA]JDBC란? 사용법까지 간략 정리
     
     
   • public class JdbcMemberRepository implements MemberRepository
     

     

     위 제가 정리한 JDBC포스트 외에 추가 내용만 정리하겠습니다.

   • pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
     → sql을 넣습니다. 뒷 부분은 id를 자동으로 넣는 코드입니다.
     
     
   • pstmt.setString(1, member.getName());
     → insert쿼리의 (?) 에 넣을 데이터입니다.
     
     
   • rs = pstmt.getGeneratedKeys();
     → 위 코드에서 Statement.RETURN_GENERATED_KEYS랑 매칭되는 코드입니다. 생성된 코드를 반환합니다.
     
     
   • DataSourceUtils를 통해서 Data connection을 획득해야 똑같은 걸 유지할 수 있다.

     

     

   • 닫을 때도 DataSourceUtils를 통해 닫는다.
     

     

   • 전체코드

     package hello.hellospring.repository;
     
     import hello.hellospring.domain.Member;
     import org.springframework.jdbc.datasource.DataSourceUtils;
     import javax.sql.DataSource;
     import java.sql.*;
     import java.util.ArrayList;
     import java.util.List;
     import java.util.Optional;
     
     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);
         }
     }

2. SpringConfig 수정
   

   

   
   
   • DataSource를 DI, 의존성주입으로 적어줍니다.
     
     
   • return new JdbcMemberRepository(dataSource);
     → MemberRepository를 상속받은 리포지토리들 중 Jdbc로 바꿔줍니다.
   • 전체코드

     package hello.hellospring;
     
     import javax.sql.DataSource;
     
     import org.springframework.beans.factory.annotation.Autowired;
     import org.springframework.context.annotation.Bean;
     import org.springframework.context.annotation.Configuration;
     
     import hello.hellospring.repository.JdbcMemberRepository;
     import hello.hellospring.repository.MemberRepository;
     // import hello.hellospring.repository.MemoryMemberRepository;
     import hello.hellospring.service.MemberService;
     
     @Configuration
     public class SpringConfig {
     
         private DataSource dataSource;
     
         @Autowired
         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);
         }
     }

3. cmd로 h2.bat을 실행해서 h2에 연결한다.

   

4. 서버를 실행해보니 잘된다.

   

 

 

---

※ 참조 : https://www.inflearn.com/roadmaps/373