[Spring][ํ˜ผ๊ณต] 6. ์Šคํ”„๋ง DB ์ ‘๊ทผ ๊ธฐ์ˆ  - (2) ์ˆœ์ˆ˜ JDBC

2021. 9. 18. 17:00ยท๐Ÿ“ Language/โœ JAVA

์Šคํ”„๋ง DB ์ ‘๊ทผ ๊ธฐ์ˆ 

2. ์ˆœ์ˆ˜ JDBC

์ด๋ฒˆ ๊ธ€์€ `์•„์ฃผ ์˜ค๋ž˜์ „์—๋Š” ์ด๋ ‡๊ฒŒ JDBC API๋ฅผ ์ง์ ‘ ์ฝ”๋”ฉํ–ˆ๊ตฌ๋‚˜` ์ •๋„๋กœ๋งŒ ์ฐธ๊ณ ๋กœ ์•Œ์•„๋‘์ž

 

1) ํ™˜๊ฒฝ ์„ค์ •

  • build.gradle ํŒŒ์ผ์— jdbc, h2 ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ๊ด€๋ จ ๋ผ์ด๋ธŒ๋Ÿฌ๋ฆฌ ์ถ”๊ฐ€

build.gradle

  • ์Šคํ”„๋ง ๋ถ€ํŠธ์— ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ์—ฐ๊ฒฐ ์„ค์ • ์ถ”๊ฐ€
    • url์€ ์ด์ „ ๊ธ€์— ์„ค์น˜ํ•œ h2์˜ url์„ ์ž…๋ ฅํ•˜๋ฉด ๋œ๋‹ค.
    • ๋งŒ์•ฝ driver class name ์ž…๋ ฅ ์‹œ ๋นจ๊ฐ„ ๊ธ€์”จ๋กœ ๋…ธ์ถœ๋œ๋‹ค๋ฉด, build.gradle๋ฅผ ๋‹ค์‹œ ๋กœ๋“œํ•˜๋ฉด ๋œ๋‹ค.

resources/application.properties

 

2) Jdbc ๋ฆฌํฌ์ง€ํ† ๋ฆฌ ๊ตฌํ˜„

package hello.hello.spring.repository;

import hello.hello.spring.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 {

    // DB์— ์ ‘๊ทผํ•˜๊ธฐ ์œ„ํ•ด ํ•„์š”ํ•˜๋‹ค.
    private final DataSource dataSource;

    // application.properties์—์„œ 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 {
            // getConnection(): DB์˜ Connection์„ ๊ฐ€์ ธ์˜จ๋‹ค.
            conn = getConnection();
            // RETURN_GENERATED_KEYS: DB์˜ id ๊ฐ’์„ ์–ป์–ด์˜ฌ ๋–„ ์‚ฌ์šฉํ•œ๋‹ค.
            pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

            // ์ธ๋ฑ์Šค 1์ด (?)์™€ ๋งค์นญ์ด ๋˜๋ฉด์„œ member.getName()์„ (?)์— ๋„ฃ๋Š”๋‹ค.
            pstmt.setString(1, member.getName());

            // executeUpdate(): DB์— ์ฟผ๋ฆฌ๋ฅผ ๋„˜๊ธด๋‹ค.
            pstmt.executeUpdate();
            // getGeneratedKeys(): DB์˜ id ๊ฐ’์„ ๋ฐ˜ํ™˜ํ•œ๋‹ค.
            rs = pstmt.getGeneratedKeys();

            if (rs.next()) { // DB์˜ id ๊ฐ’์ด ์žˆ๋‹ค๋ฉด
                member.setId(rs.getLong(1));
            } else {
                throw new SQLException("id ์กฐํšŒ ์‹คํŒจ");
            }
            return member;
        } catch (Exception e) {
            throw new IllegalStateException(e);
        } finally {
            // ์‚ฌ์šฉํ•œ ์ž์›๋“ค ๋ฐ”๋กœ release ํ•ด์•ผ ํ•œ๋‹ค.
            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);

            // executeQuery(): DB์— ์ฟผ๋ฆฌ๋ฅผ ๋„˜๊ธด๋‹ค. (์กฐํšŒ)
            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() {
        // dataSource๋ฅผ ํ†ตํ•ด ์ง์ ‘ getConnection()๋ฅผ ํ• ์ˆ˜๋„ ์žˆ์ง€๋งŒ,
        // ๊ทธ๋ ‡๊ฒŒ ์‚ฌ์šฉํ•œ๋‹ค๋ฉด ์ƒˆ๋กœ์šด Connection์„ ๊ณ„์†ํ•ด์„œ ์ƒ์„ฑํ•˜๊ฒŒ ๋œ๋‹ค.
        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);
    }
}

 

3) ์ƒˆ๋กœ์šด ๋ฆฌํฌ์ง€ํ† ๋ฆฌ ์—ฐ๊ฒฐ

  • MemberRepository ์ธํ„ฐํŽ˜์ด์Šค์˜ ์ƒˆ๋กœ์šด ๊ตฌํ˜„์ฒด๋ฅผ ์ƒ์„ฑํ•œ๋‹ค.
  • ๋ฐ์ดํ„ฐ๋ฅผ DB์— ์ €์žฅํ•˜๋ฏ€๋กœ ์Šคํ”„๋ง ์„œ๋ฒ„๋ฅผ ๋‹ค์‹œ ์‹คํ–‰ํ•ด๋„ ๋ฐ์ดํ„ฐ๊ฐ€ ์•ˆ์ „ํ•˜๊ฒŒ ์ €์žฅ๋œ๋‹ค.

๋ฆฌํฌ์ง€ํ† ๋ฆฌ ํด๋ž˜์Šค

  • ๊ฐ์ฒด ์ง€ํ–ฅ์˜ ๋‹คํ˜•์„ฑ ํŠน์„ฑ
  • ๊ฐœ๋ฐฉ-ํ์‡„ ์›์น™(OCP, Open-Closed Principle): ํ™•์žฅ์—๋Š” ์—ด๋ ค์žˆ๊ณ  ์ˆ˜์ •, ๋ณ€๊ฒฝ์—๋Š” ๋‹ซํ˜€์žˆ๋‹ค.
  • ์Šคํ”„๋ง DI์„ ์‚ฌ์šฉํ•˜๋ฉด ๊ธฐ์กด ์ฝ”๋“œ๋ฅผ ์ „ํ˜€ ์†๋Œ€์ง€ ์•Š๊ณ  ์„ค์ •๋งŒ์œผ๋กœ ๊ตฌํ˜„ ํด๋ž˜์Šค๋ฅผ ๋ณ€๊ฒฝํ•  ์ˆ˜ ์žˆ๋‹ค.

์Šคํ”„๋ง ์ปจํ…Œ์ด๋„ˆ

  • DataSource๋Š” ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ์ปค๋„ฅ์…˜์„ ํš๋“ํ•  ๋•Œ ์‚ฌ์šฉํ•˜๋Š” ๊ฐ์ฒด๋‹ค.
  • ์Šคํ”„๋ง ๋ถ€ํŠธ๋Š” ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ์ปค๋„ฅ์…˜ ์ •๋ณด๋ฅผ ๋ฐ”ํƒ•์œผ๋กœ DataSource๋ฅผ ์ƒ์„ฑํ•˜๊ณ  ์Šคํ”„๋ง ๋นˆ์œผ๋กœ ๋งŒ๋“ค์–ด๋‘”๋‹ค.
  • ๊ทธ๋ž˜์„œ DataSource๋„ DI๋ฅผ ๋ฐ›์„ ์ˆ˜ ์žˆ๋‹ค.
package hello.hello.spring;

import hello.hello.spring.repository.JdbcMemberRepository;
import hello.hello.spring.repository.MemberRepository;
import hello.hello.spring.repository.MemoryMemberRepository;
import hello.hello.spring.service.MemberService;
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 {

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

 

4) ๊ฒฐ๊ณผ

  • ์›น ํŽ˜์ด์ง€๋ฅผ ํ†ตํ•ด ๊ฐ€์ž…ํ•œ๋‹ค.

http://localhost:8080/members/new

  • DB ๋ฐ์ดํ„ฐ๋ฅผ ํ™•์ธํ•˜๋ฉด ๊ฐ™์€ ์ด๋ฆ„์„ ๊ฐ€์ง„ member๊ฐ€ ์กฐํšŒ๋œ๋‹ค.

H2 ์ฝ˜์†”

์ €์ž‘์žํ‘œ์‹œ (์ƒˆ์ฐฝ์—ด๋ฆผ)
'๐Ÿ“ Language/โœ JAVA' ์นดํ…Œ๊ณ ๋ฆฌ์˜ ๋‹ค๋ฅธ ๊ธ€
  • [Spring][ํ˜ผ๊ณต] 6. ์Šคํ”„๋ง DB ์ ‘๊ทผ ๊ธฐ์ˆ  - (4) ์Šคํ”„๋ง JDBC Template
  • [Spring][ํ˜ผ๊ณต] 6. ์Šคํ”„๋ง DB ์ ‘๊ทผ ๊ธฐ์ˆ  - (3) ์Šคํ”„๋ง ํ†ตํ•ฉ ํ…Œ์ŠคํŠธ
  • [Spring][ํ˜ผ๊ณต] 6. ์Šคํ”„๋ง DB ์ ‘๊ทผ ๊ธฐ์ˆ  - (1) H2 DB ์„ค์น˜
  • [Spring][ํ˜ผ๊ณต] 5. ์›น MVC ๊ฐœ๋ฐœ - ํšŒ์› ์›น ๊ธฐ๋Šฅ
Blxxming
Blxxming
CS ์ง€์‹๊ณผ ๊ณต๋ถ€ํ•˜๋‹ค ๋ฐฐ์šด ๊ฒƒ, ๊ฒฝํ—˜ํ•œ ๊ฒƒ ๋“ฑ์„ ๊ธฐ๋กํ•˜๋Š” ๋ธ”๋กœ๊ทธ์ž…๋‹ˆ๋‹ค.
  • Blxxming
    ๐Ÿ’ก๋ฒˆ๋œฉ๐Ÿ’ก
    Blxxming
  • ์ „์ฒด
    ์˜ค๋Š˜
    ์–ด์ œ
  • ๊ณต์ง€์‚ฌํ•ญ

    • Tech Interview
    • ๐Ÿ“š Tech (246)
      • ๐Ÿ“ Computer Science (96)
        • โœ OS (12)
        • โœ Network & Web (10)
        • โœ Database (11)
        • โœ Data Structure (6)
        • โœ Algorithm (40)
        • โœ Design Pattern (9)
        • โœ Cloud Computing (3)
        • โœ (5)
      • ๐Ÿ“ Language (73)
        • โœ Language (6)
        • โœ C & C++ (11)
        • โœ C# (19)
        • โœ JAVA (37)
      • ๐Ÿ“ Game (43)
        • โœ Computer Graphics (2)
        • โœ Unity (14)
        • โœ Unreal (26)
        • โœ (1)
      • ๐Ÿ“ Book (34)
        • โœ Effective (3)
        • โœ Game Server (16)
        • โœ Clean Code (14)
        • โœ (1)
  • hELLOยท Designed By์ •์ƒ์šฐ.v4.10.0
Blxxming
[Spring][ํ˜ผ๊ณต] 6. ์Šคํ”„๋ง DB ์ ‘๊ทผ ๊ธฐ์ˆ  - (2) ์ˆœ์ˆ˜ JDBC
์ƒ๋‹จ์œผ๋กœ

ํ‹ฐ์Šคํ† ๋ฆฌํˆด๋ฐ”