2022 01 09 일요일 공부 (김영한 스프링 입문 day3 : 스프링 DB 접근 기술)

1. 스프링 DB 접근 기술

 

- jdbc 기술 : db application server 연결 (20년 전 방식)
- 순수 jdbc 개발이 힘듦 --> Spring 이 jdbc templete 기술 제공 : 애플리케이션에서 데이터베이스로 sql 을 편리하게 나를 수 있다
- jpa 기술 : sql 조차도 개발자들이 직접 짜는 게 아니라 sql 을 아예 등록, 수정, 조회 등등 쿼리를 날려줌 (객체를 바로 디비에 쿼리 없이 저장 및 관리할 수 있다)
- 스프링 데이터 jpa 기술 : jpa를 편리하게 사용할 수 있도록 한번 감싼 기능
- 나열된 기술들을 적용해보면서 회원관리 소스코드를 한단계씩 바꿔본다

H2 데이터베이스 설치

- 실습 완료

https://www.h2database.com/html/download-archive.html

cd C:\Program Files (x86)\H2\bin 접근 후 h2.bat 실행

C:\Users\multicampus 에서 dir 후 test.mv.db 생성 여부 확인

파일 직접 접근(콘솔 충돌 위험 피함 ??? )x 소켓을 통해서 접근o

순수 JDBC

- There was an unexpected error (type=Internal Server Error, status=500) ... 실습 실패

- JdbcMemoryRepository.jva
- SpringConfig.java
- bulid.gradle
- application.properties

// application.properties 추가
spring.datasource.url=jdbc:h2:tcp://localhost/~/test
spring.datasource.driver-class-name=org.h2.Driver

// build.gradle 파일에 jdbc, h2 데이터베이스 관련 라이브러리 추가 : jdbc driver, client
implementation 'org.springframework.boot:spring-boot-starter-jdbc'
runtimeOnly 'com.h2database:h2'

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;

// 구현을 메모리에 할거야 or DB랑 연동해서 JDBC 로 할거야 의 차이
public class JdbcMemberRepository implements MemberRepository {

    // DB 를 붙이기 위해서 DataSource 필요
    private final DataSource dataSource;

    // Spring 에서 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()); // 1 이 쿼리문의 ? 과 매칭
            pstmt.executeUpdate();
            rs = pstmt.getGeneratedKeys(); // key 에 1,2,3 ... 을 꺼내줌

            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);
    }

}

package hello.hellospring.service;

import hello.hellospring.repository.JdbcMemberRepository;
import hello.hellospring.repository.MemberRepository;
import hello.hellospring.repository.MemoryMemberRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;

@Configuration
public class SpringConfig {

    // memberService, memberRepository 를 Spring Bean 등록
    // Controller --> Service --> Repository 를 각각 Spring Bean 에 올리고 엮어주어야 함
    // 그러나 Controller 는 어쩔수 없이 어차피 Spring 이 관리하는 거기 때문에 Component Scan 으로 Container 에 올라 감

    private DataSource dataSource;

    @Autowired
    public SpringConfig(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    @Bean
    public MemberService memberService(){
        // Service 랑 Repository 를 엮어줌
        return new MemberService(memberRepository());
    }

    @Bean
    public MemberRepository memberRepository(){
        // 인터페이스는 new 안되니까 MemoryMemberRepository
        //return new MemoryMemberRepository();
        return new JdbcMemberRepository(dataSource);
    }

}