[LIKELION] 221025

고관운·2022년 10월 25일

멋쟁이사자처럼

회고

😄 느낀점

Hash에 대한 개념을 잡을 수 있었다. (그 전까지는 Hash문제도 다른 방식으로 풀었음)
Toby String을 처음부터 다시 코딩했는데 중간중간 막히는 부분이 있었다. (다시 한번 정리하자)

😁 목표

스터디 DP 유형 공부

알고리즘(Hash)

Hash란

개념

문자열을 key로 문제를 풀어야 하는 경우

Hash VS HashTable

Hash : 자체는 특정 값을 리턴하는 함수(메소드)
HashTable : Hash를 써서 값에 접근하는 자료구조

속도

Hash 속도 : O(1)
함수에서 리턴된 값을 가지고 배열에 접근하기 때문에
Hash를 사용하지 않을 때 속도 : O(N)

구현

사이즈를 정하는 기준

🔴 공식은 없음
🔴 Hash충돌이 나지 않으면서도 메모리를 너무 많이 쓰지 않는 크기로 정함

예시

10000개를 설정한 경우 73 / 80 → 메모리를 더 많이 씀
200개를 설정한 경우 61 / 80 → 충돌하는 경우가 더 많아짐

Hash 구현

해당 문자열의 아스키 값들을 모두 더함
이 합의 나머지를 구함
🟢 나머지를 구한 이유
지정된 크기의 배열에 값을 저장하기 위해
size로 정한 배열의 어딘가로 가도록 하기 위함

    public int hash(String key) {
        int asciiSum = 0;
        for (int i = 0; i < key.length(); i++) {
            asciiSum += key.charAt(i);
        }
        return asciiSum % 1000;    // 1000개
    }

HashTable 구현

🟢 insert(key, value) : key에 value 넣기
🟢 search(key) : key에 해당하는 value 가져오기
🔴 Hash 충돌은 어떻게 구할 것인가?
👉 내일 배울 예정


public class HashTableFunction {
    private int size = 10000;
    private int[] table = new int[size];

    public HashTableFunction() {
    }

    public HashTableFunction(int size) {
        this.size = size;
        this.table = new int[size];
    }

    public int hash(String key) {
        int asciiSum = 0;
        for (int i = 0; i < key.length(); i++) {
            asciiSum += key.charAt(i);
        }
        return asciiSum % size;
    }

    public void insert(String key, Integer value) {
        int hashCode = hash(key);
        this.table[hashCode] = value;
        System.out.println(key + " " + hashCode + "방에 저장이 완료되었습니다.");
    }

    public int search(String key) {
        return this.table[hash(key)];
    }
}

Spring 입문(DAO 복습)

과정

User, UserDao까지 작성된 참고사이트 참고하여 입력
ConnectionMaker Class로 분리 (UserDao내에 getConnection)
Mysql Jdbc Driver dependencies에 추가하기
addAndGet 테스트 만들기
Interface적용해서 분리 (ConnectionMaker)
Factory적용
Test Code를 ApplicationContext사용하게 수정
UserDao에 deleteAll(), getCount() 메소드 및 테스트 추가
Test Code에 @Autowired, @BeforeEach 적용
findById()에서 user==null인 경우 Exception처리
try / catch / final 처리 (deleteAll, getCount)
StatementStrategy 인터페이스, deleteAllStatement 클래스 추가 및 deleteAll에 적용
jdbcContextWithStatementStrategy로 분리
AddStatement에도 적용
DataSource 적용
deleteAll(), add()에 익명 클래스 적용
JdbcContext로 분리
executeQuery() 분리 - deleteAll
JdbcTemplate적용 deleteAll(), add(), getCount(), findById()
getAll(), getAllTest() 추가
romMapper 뺀 후 최종 코드

과정별 실습

1. User, UserDao까지 작성된 참고사이트 참고하여 입력

User

public class User {
    private String id;
    private String name;
    private String password;

    public User(String id, String name, String password) {
        this.id = id;
        this.name = name;
        this.password = password;
    }

    public String getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public String getPassword() {
        return password;
    }
}

UserDao

import com.likelion.domain.User;

import java.sql.*;
import java.util.Map;

public class UserDao {
    public void add(User user) {
        Map<String, String> env = System.getenv();
        try {
            Connection c = DriverManager.getConnection(env.get("DB_HOST"),
                    env.get("DB_USER"), env.get("DB_PASSWORD"));

            PreparedStatement pstmt = c.prepareStatement("INSERT INTO users(id, name, password) VALUES(?,?,?);");
            pstmt.setString(1, user.getId());
            pstmt.setString(2, user.getName());
            pstmt.setString(3, user.getPassword());

            pstmt.executeUpdate();

            pstmt.close();
            c.close();

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public User findById(String id) {
        Map<String, String> env = System.getenv();
        Connection c;
        try {
            c = DriverManager.getConnection(env.get("DB_HOST"),
                    env.get("DB_USER"), env.get("DB_PASSWORD"));

            PreparedStatement pstmt = c.prepareStatement("SELECT * FROM users WHERE id = ?");
            pstmt.setString(1, id);

            ResultSet rs = pstmt.executeQuery();
            rs.next();
            User user = new User(rs.getString("id"), rs.getString("name"),
                    rs.getString("password"));

            rs.close();
            pstmt.close();
            c.close();

            return user;

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

build.gradle
dependencies에 추가

    implementation 'org.springframework.boot:spring-boot-starter-test:2.7.4'
    implementation 'org.springframework.boot:spring-boot-starter-jdbc:2.7.4'

2. ConnectionMaker Class로 분리 (UserDao내에 getConnection)

🟢 중복되는 Connection 부분을 getConnection 메소드로 따로 생성

import com.likelion.domain.User;

import java.sql.*;
import java.util.Map;

public class UserDao {
    public Connection getConnection() throws SQLException {
        Map<String, String> env = System.getenv();
        Connection c = DriverManager.getConnection(env.get("DB_HOST"),
            env.get("DB_USER"), env.get("DB_PASSWORD"));
        return c;
    }

    public void add(User user) {
        try {
            Connection c = getConnection();

            PreparedStatement pstmt = c.prepareStatement("INSERT INTO users(id, name, password) VALUES(?,?,?);");
            pstmt.setString(1, user.getId());
            pstmt.setString(2, user.getName());
            pstmt.setString(3, user.getPassword());

            pstmt.executeUpdate();

            pstmt.close();
            c.close();

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public User findById(String id) {
        try {
            Connection c = getConnection();

            PreparedStatement pstmt = c.prepareStatement("SELECT * FROM users WHERE id = ?");
            pstmt.setString(1, id);

            ResultSet rs = pstmt.executeQuery();
            rs.next();
            User user = new User(rs.getString("id"), rs.getString("name"),
                    rs.getString("password"));

            rs.close();
            pstmt.close();
            c.close();

            return user;

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

3. `Mysql Jdbc Driver` dependencies에 추가하기

mvnrepositroy 접속 ➡ MySQL Connector Java 검색 ➡ Central에 버전 선택 ➡ 아래 그림의 빨간 네모칸 복사 ➡ dependencies에 추가

4. addAndGet 테스트 만들기

🟢 add(), findById()를 테스트하는 테스트 클래스 생성(Gradle 이용)

import com.likelion.domain.User;
import org.junit.jupiter.api.Test;

import static org.junit.jupiter.api.Assertions.*;

class UserDaoTest {

    @Test
    void addandGet() {
        UserDao userDao = new UserDao();

        User user = new User("7", "김구글", "1234");
        userDao.add(user);

        User seletedUser = userDao.findById("7");
        assertEquals("김구글", seletedUser.getName());
    }
}

5. Interface적용해서 분리 (ConnectionMaker)

🟢 UserDao 내에 있는 getConnection을 인터페이스 적용

ConnectionMaker 인터페이스 생성
ConnectionMaker를 의존하는 AwsConnectionMaker 생성
(따로 인터페이스를 의존하게 만드는 의유 : Aws, Local 등 다양하게 접속할 수 있도록 하기 위해)
UserDao가 AwsConnectionMaker의 getConnection 메소드를 호출하도록 코드 변경

ConnectionMaker

import java.sql.Connection;
import java.sql.SQLException;

public interface ConnectionMaker {
    public Connection getConnection() throws SQLException;
}

AwsConnectionMaker

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Map;

public class AwsConnectionMaker implements ConnectionMaker{
    @Override
    public Connection getConnection() throws SQLException {
        Map<String, String> env = System.getenv();
        Connection c = DriverManager.getConnection(env.get("DB_HOST"),
                env.get("DB_USER"), env.get("DB_PASSWORD"));
        return c;
    }
}

UserDao

import com.likelion.domain.User;

import java.sql.*;

public class UserDao {
    private ConnectionMaker connectionMaker;

    public UserDao() {
        this.connectionMaker = new AwsConnectionMaker();
    }

    public UserDao(ConnectionMaker connectionMaker) {
        this.connectionMaker = connectionMaker;
    }

    public void add(User user) {
        try {
            Connection c = this.connectionMaker.getConnection();

            PreparedStatement pstmt = c.prepareStatement("INSERT INTO users(id, name, password) VALUES(?,?,?);");
            pstmt.setString(1, user.getId());
            pstmt.setString(2, user.getName());
            pstmt.setString(3, user.getPassword());

            pstmt.executeUpdate();

            pstmt.close();
            c.close();

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public User findById(String id) {
        try {
            Connection c = this.connectionMaker.getConnection();

            PreparedStatement pstmt = c.prepareStatement("SELECT * FROM users WHERE id = ?");
            pstmt.setString(1, id);

            ResultSet rs = pstmt.executeQuery();
            rs.next();
            User user = new User(rs.getString("id"), rs.getString("name"),
                    rs.getString("password"));

            rs.close();
            pstmt.close();
            c.close();

            return user;

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }
}

6. Factory적용

🟢 UserDao와 Connection의 관계를 지어주는 역할

UserDaoFactory

public class UserDaoFactory {
    public UserDao awsUserDao() {
        return new UserDao(new AwsConnectionMaker());
    }
}

UserDaoTest

import com.likelion.domain.User;
import org.junit.jupiter.api.Test;

import static org.junit.jupiter.api.Assertions.*;

class UserDaoTest {

    @Test
    void addandGet() {
        UserDao userDao = new UserDaoFactory().awsUserDao();

//        User user = new User("7", "김구글", "1234");
//        userDao.add(user);

        User seletedUser = userDao.findById("7");
        assertEquals("김구글", seletedUser.getName());
    }
}

7. Test Code를 ApplicationContext사용하게 수정

UserDaoFactory

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class UserDaoFactory {
    @Bean
    public UserDao awsUserDao() {
        return new UserDao(new AwsConnectionMaker());
    }
}

UserDaoTest

import com.likelion.domain.User;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.extension.ExtendWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit.jupiter.SpringExtension;

import static org.junit.jupiter.api.Assertions.*;

@ExtendWith(SpringExtension.class)
@ContextConfiguration(classes = UserDaoFactory.class)
class UserDaoTest {
    @Autowired
    private ApplicationContext context;

    @Test
    void addandGet() {
        UserDao userDao = context.getBean("awsUserDao", UserDao.class);

//        User user = new User("7", "김구글", "1234");
//        userDao.add(user);

        User seletedUser = userDao.findById("7");
        assertEquals("김구글", seletedUser.getName());
    }
}

8. UserDao에 deleteAll(), getCount() 메소드 및 테스트 추가

🟢 deleteAll() : 테이블 내용 삭제
🟢 getCount() : 테이블 데이터 개수 리턴

UserDaoFactory

import com.likelion.domain.User;

import java.sql.*;

public class UserDao {
    private ConnectionMaker connectionMaker;

    public UserDao() {
        this.connectionMaker = new AwsConnectionMaker();
    }

    public UserDao(ConnectionMaker connectionMaker) {
        this.connectionMaker = connectionMaker;
    }

    public void add(User user) {
        try {
            Connection c = this.connectionMaker.getConnection();

            PreparedStatement pstmt = c.prepareStatement("INSERT INTO users(id, name, password) VALUES(?,?,?);");
            pstmt.setString(1, user.getId());
            pstmt.setString(2, user.getName());
            pstmt.setString(3, user.getPassword());

            pstmt.executeUpdate();

            pstmt.close();
            c.close();

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public User findById(String id) {
        try {
            Connection c = this.connectionMaker.getConnection();

            PreparedStatement pstmt = c.prepareStatement("SELECT * FROM users WHERE id = ?");
            pstmt.setString(1, id);

            ResultSet rs = pstmt.executeQuery();
            rs.next();
            User user = new User(rs.getString("id"), rs.getString("name"),
                    rs.getString("password"));

            rs.close();
            pstmt.close();
            c.close();

            return user;

        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public void deleteAll() throws SQLException {
        Connection c = this.connectionMaker.getConnection();

        PreparedStatement pstmt = c.prepareStatement("delete from users");
        pstmt.executeUpdate();

        pstmt.close();
        c.close();
    }

    public int getCount() throws SQLException {
        Connection c = this.connectionMaker.getConnection();

        PreparedStatement pstmt = c.prepareStatement("select count(*) from users");

        ResultSet rs = pstmt.executeQuery();
        rs.next();
        int count = rs.getInt(1);

        rs.close();
        pstmt.close();
        c.close();

        return count;
    }
}

UserDaoTest

import com.likelion.domain.User;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.extension.ExtendWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit.jupiter.SpringExtension;

import java.sql.SQLException;

import static org.junit.jupiter.api.Assertions.*;

@ExtendWith(SpringExtension.class)
@ContextConfiguration(classes = UserDaoFactory.class)
class UserDaoTest {
    @Autowired
    private ApplicationContext context;

    @Test
    void addandGet() throws SQLException {
        UserDao userDao = context.getBean("awsUserDao", UserDao.class);
        userDao.deleteAll();

        User user = new User("7", "김구글", "1234");
        userDao.add(user);

        User seletedUser = userDao.findById("7");
        assertEquals("김구글", seletedUser.getName());
    }

    @Test
    void deleteAndGetCount() throws SQLException {
        UserDao userDao = context.getBean("awsUserDao", UserDao.class);
        userDao.deleteAll();
        assertEquals(0, userDao.getCount());

        User user1 = new User("1", "김구글", "1234");
        User user2 = new User("2", "강다음", "2345");
        User user3 = new User("3", "박토익", "3456");
        userDao.add(user1);
        userDao.add(user2);
        userDao.add(user3);
        assertEquals(3, userDao.getCount());
    }
}

9. Test Code에 @Autowired, @BeforeEach 적용

UserDaoTest

import com.likelion.domain.User;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.extension.ExtendWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit.jupiter.SpringExtension;

import java.sql.SQLException;

import static org.junit.jupiter.api.Assertions.*;

@ExtendWith(SpringExtension.class)
@ContextConfiguration(classes = UserDaoFactory.class)
class UserDaoTest {
    @Autowired
    private ApplicationContext context;
    UserDao userDao;
    User user1;
    User user2;
    User user3;

    @BeforeEach
    void setUp() {
        userDao = context.getBean("awsUserDao", UserDao.class);
        user1 = new User("1", "김구글", "1234");
        user2 = new User("2", "강다음", "2345");
        user3 = new User("3", "박토익", "3456");
    }

    @Test
    void addandGet() throws SQLException {
        userDao.deleteAll();

        userDao.add(user1);

        User seletedUser = userDao.findById("1");
        assertEquals(user1.getName(), seletedUser.getName());
    }

    @Test
    void deleteAndGetCount() throws SQLException {
        userDao.deleteAll();
        assertEquals(0, userDao.getCount());

        userDao.add(user1);
        userDao.add(user2);
        userDao.add(user3);
        assertEquals(3, userDao.getCount());
    }
}

10. findById()에서 user==null인 경우 Exception처리

UserDao의 findById()

    public User findById(String id) {
        try {
            Connection c = this.connectionMaker.getConnection();

            PreparedStatement pstmt = c.prepareStatement("SELECT * FROM users WHERE id = ?");
            pstmt.setString(1, id);

            ResultSet rs = pstmt.executeQuery();

            User user = null;
            if(rs.next()){
                user = new User(rs.getString("id"), rs.getString("name"),
                        rs.getString("password"));
            }

            rs.close();
            pstmt.close();
            c.close();

            if(user == null) throw new EmptyResultDataAccessException(1);

            return user;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

11. try / catch / final 처리 (deleteAll, getCount)

UserDao의 deleteAll()

    public void deleteAll() throws SQLException {
        Connection c = null;
        PreparedStatement pstmt = null;

        try {
            c = this.connectionMaker.getConnection();

            pstmt = c.prepareStatement("delete from users");
            pstmt.executeUpdate();
        } catch (SQLException e) {
            throw e;
        } finally {
            if(pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                }
            }
            if(c != null) {
                try {
                    c.close();
                } catch (SQLException e) {
                }
            }
        }
    }

UserDao의 getCount()

    public int getCount() throws SQLException {
        Connection c = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            c = this.connectionMaker.getConnection();

            pstmt = c.prepareStatement("select count(*) from users");
            rs = pstmt.executeQuery();
            rs.next();

            return rs.getInt(1);
        } catch (SQLException e) {
            throw e;
        } finally {
            if(rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                }
            }
            if(pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                }
            }
            if(c != null) {
                try {
                    c.close();
                } catch (SQLException e) {
                }
            }
        }
    }

12. StatementStrategy 인터페이스, deleteAllStatement 클래스 추가 및 deleteAll에 적용

🟢 deleteAll의 쿼리문을 따로 생성하는 deleteAllStatement 생성 (이것은 StatementStrategy을 의존)

StatementStrategy

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public interface StatementStrategy {
    PreparedStatement makePreparedStatement(Connection c) throws SQLException;
}

deleteAllStatement

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class deleteAllStatement implements StatementStrategy{
    @Override
    public PreparedStatement makePreparedStatement(Connection c) throws SQLException {
        return c.prepareStatement("delete from users");
    }
}

UserDao의 deleteAll()

    public void deleteAll() throws SQLException {
        Connection c = null;
        PreparedStatement pstmt = null;

        try {
            c = this.connectionMaker.getConnection();

            StatementStrategy strategy = new deleteAllStatement();
            pstmt = strategy.makePreparedStatement(c);

            pstmt.executeUpdate();
        } catch (SQLException e) {
            throw e;
        } finally {
            if(pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                }
            }
            if(c != null) {
                try {
                    c.close();
                } catch (SQLException e) {
                }
            }
        }
    }

13. jdbcContextWithStatementStrategy로 분리

🔴 deleteAll안의 코드를 jdbcContextWithStatementStrategy로 분리

UserDao의 deleteAll, jdbcContextWithStatementStrategy

    public void jdbcContextWithStatementStrategy(StatementStrategy stmt) throws SQLException {
        Connection c = null;
        PreparedStatement pstmt = null;

        try {
            c = this.connectionMaker.getConnection();

            pstmt = stmt.makePreparedStatement(c);

            pstmt.executeUpdate();
        } catch (SQLException e) {
            throw e;
        } finally {
            if(pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                }
            }
            if(c != null) {
                try {
                    c.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public void deleteAll() throws SQLException {
        jdbcContextWithStatementStrategy(new deleteAllStatement());
    }

14. AddStatement에도 적용

🔴 User를 리턴해야하기 때문에 AddStatement에도 User변수 선언

AddStatement

import com.likelion.domain.User;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class AddStatement implements StatementStrategy{
    User user;

    public AddStatement(User user) {
        this.user = user;
    }

    public PreparedStatement makePreparedStatement(Connection c) throws SQLException {
        PreparedStatement pstmt = c.prepareStatement("INSERT INTO users(id, name, password) VALUES(?,?,?);");
        pstmt.setString(1, user.getId());
        pstmt.setString(2, user.getName());
        pstmt.setString(3, user.getPassword());

        return pstmt;
    }
}

UserDao의 add()

    public void add(User user) throws SQLException {
        jdbcContextWithStatementStrategy(new AddStatement(user));
    }

15. DataSource 적용

🔴 Connection 방식을 내재되어 있는 DataSource 인터페이스 적용

UserDaoFactory

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.SimpleDriverDataSource;

import javax.sql.DataSource;
import java.util.Map;

@Configuration
public class UserDaoFactory {
    @Bean
    public UserDao awsUserDao() {
        return new UserDao(awsDateSource());
    }

    DataSource awsDateSource() {
        Map<String, String> env = System.getenv();
        SimpleDriverDataSource dataSource =new SimpleDriverDataSource();
        dataSource.setDriverClass(com.mysql.cj.jdbc.Driver.class);
        dataSource.setUrl(env.get("DB_HOST"));
        dataSource.setUsername(env.get("DB_USER"));
        dataSource.setPassword(env.get("DB_PASSWORD"));

        return dataSource;
    }
}

UserDao

import com.likelion.domain.User;
import org.springframework.dao.EmptyResultDataAccessException;

import javax.sql.DataSource;
import java.sql.*;

public class UserDao {
    private DataSource dataSource;

    public UserDao(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public void jdbcContextWithStatementStrategy(StatementStrategy stmt) throws SQLException {
        Connection c = null;
        PreparedStatement pstmt = null;

        try {
            c = this.dataSource.getConnection();

            pstmt = stmt.makePreparedStatement(c);

            pstmt.executeUpdate();
        } catch (SQLException e) {
            throw e;
        } finally {
            if(pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                }
            }
            if(c != null) {
                try {
                    c.close();
                } catch (SQLException e) {
                }
            }
        }
    }

    public void add(User user) throws SQLException {
        jdbcContextWithStatementStrategy(new AddStatement(user));
    }

    public User findById(String id) {
        try {
            Connection c = this.dataSource.getConnection();

            PreparedStatement pstmt = c.prepareStatement("SELECT * FROM users WHERE id = ?");
            pstmt.setString(1, id);

            ResultSet rs = pstmt.executeQuery();

            User user = null;
            if(rs.next()){
                user = new User(rs.getString("id"), rs.getString("name"),
                        rs.getString("password"));
            }

            rs.close();
            pstmt.close();
            c.close();

            if(user == null) throw new EmptyResultDataAccessException(1);

            return user;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

    public void deleteAll() throws SQLException {
        jdbcContextWithStatementStrategy(new deleteAllStatement());
    }

    public int getCount() throws SQLException {
        Connection c = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            c = this.dataSource.getConnection();

            pstmt = c.prepareStatement("select count(*) from users");
            rs = pstmt.executeQuery();
            rs.next();

            return rs.getInt(1);
        } catch (SQLException e) {
            throw e;
        } finally {
            if(rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                }
            }
            if(pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                }
            }
            if(c != null) {
                try {
                    c.close();
                } catch (SQLException e) {
                }
            }
        }
    }
}

16. deleteAll(), add()에 익명 클래스 적용

🟢 메소드 내부에서 StatementStrategy 메소드 생성
🟢 add()의 경우 따로 User를 선언해주지 않기 때문에 장점

UserDao의 add(), deleteAll()

    public void add(User user) throws SQLException {
        jdbcContextWithStatementStrategy(new StatementStrategy() {
            @Override
            public PreparedStatement makePreparedStatement(Connection c) throws SQLException {
                PreparedStatement pstmt = c.prepareStatement("INSERT INTO users(id, name, password) VALUES(?,?,?);");
                pstmt.setString(1, user.getId());
                pstmt.setString(2, user.getName());
                pstmt.setString(3, user.getPassword());

                return pstmt;
            }
        });
    }
    
    public void deleteAll() throws SQLException {
        jdbcContextWithStatementStrategy(new StatementStrategy() {
            @Override
            public PreparedStatement makePreparedStatement(Connection c) throws SQLException {
                return c.prepareStatement("delete from users");
            }
        });
    }

17. JdbcContext로 분리

🟢 따로 JdbcContext 클래스를 생성

JdbcContext

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

public class JdbcContext {
    private DataSource dataSource;

    public JdbcContext(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    public void workWithStatementStrategy(StatementStrategy stmt) throws SQLException {
        Connection c = null;
        PreparedStatement pstmt = null;

        try {
            c = this.dataSource.getConnection();

            pstmt = stmt.makePreparedStatement(c);

            pstmt.executeUpdate();
        } catch (SQLException e) {
            throw e;
        } finally {
            if(pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                }
            }
            if(c != null) {
                try {
                    c.close();
                } catch (SQLException e) {
                }
            }
        }
    }
}

UserDao 바뀐 부분

import com.likelion.domain.User;
import org.springframework.dao.EmptyResultDataAccessException;

import javax.sql.DataSource;
import java.sql.*;

public class UserDao {
    private DataSource dataSource;
    private JdbcContext jdbcContext;

    public UserDao(DataSource dataSource) {
        this.dataSource = dataSource;
        this.jdbcContext = new JdbcContext(dataSource);
    }

    public void add(User user) throws SQLException {
        this.jdbcContext.workWithStatementStrategy(new StatementStrategy() {
            @Override
            public PreparedStatement makePreparedStatement(Connection c) throws SQLException {
                PreparedStatement pstmt = c.prepareStatement("INSERT INTO users(id, name, password) VALUES(?,?,?);");
                pstmt.setString(1, user.getId());
                pstmt.setString(2, user.getName());
                pstmt.setString(3, user.getPassword());

                return pstmt;
            }
        });
    }
    
    public void deleteAll() throws SQLException {
        this.jdbcContext.workWithStatementStrategy(new StatementStrategy() {
            @Override
            public PreparedStatement makePreparedStatement(Connection c) throws SQLException {
                return c.prepareStatement("delete from users");
            }
        });
    }
}

18. executeQuery() 분리 - deleteAll

🟢 쿼리문만 따로 넣을 수 있도록 JdbcContext에 executeQuery() 추가

JdbcContext의 executeQuery()

    public void executeSql(final String query) throws SQLException {
        workWithStatementStrategy(new StatementStrategy() {
            @Override
            public PreparedStatement makePreparedStatement(Connection c) throws SQLException {
                return c.prepareStatement(query);
            }
        });
    }

UserDao의 deleteAll()

    public void deleteAll() throws SQLException {
        this.jdbcContext.executeSql("delete from users");
    }

19. JdbcTemplate적용 deleteAll(), add(), getCount(), findById()

import com.likelion.domain.User;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import javax.sql.DataSource;
import java.sql.*;

public class UserDao {
    private DataSource dataSource;
    private JdbcTemplate jdbcTemplate;

    public UserDao(DataSource dataSource) {
        this.dataSource = dataSource;
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    public void add(User user) {
        jdbcTemplate.update("INSERT INTO users(id, name, password) VALUES(?,?,?);",
                user.getId(), user.getName(), user.getPassword());
    }

    public User findById(String id) {
        String sql = "SELECT * FROM users WHERE id = ?";

        RowMapper<User> rowMapper = new RowMapper<User>() {
            @Override
            public User mapRow(ResultSet rs, int rowNum) throws SQLException {
                User user = new User(rs.getString("id"), rs.getString("name"),
                        rs.getString("password"));
                return user;
            }
        };
        return this.jdbcTemplate.queryForObject(sql, rowMapper, id);
    }

    public void deleteAll() {
        this.jdbcTemplate.update("delete from users");
    }

    public int getCount() {
        return this.jdbcTemplate.queryForObject("select count(*) from users", Integer.class);
    }
}

20. getAll(), getAllTest() 추가

UserDao의 getAll()

    public List<User> getAll() {
        String sql = "SELECT * FROM users order by id";

        RowMapper<User> rowMapper = new RowMapper<User>() {
            @Override
            public User mapRow(ResultSet rs, int rowNum) throws SQLException {
                User user = new User(rs.getString("id"), rs.getString("name"),
                        rs.getString("password"));
                return user;
            }
        };
        return this.jdbcTemplate.query(sql, rowMapper);
    }

UserDaoTest의 getAll()

    @Test
    void getAll() {
        userDao.deleteAll();
        List<User> userList = userDao.getAll();
        assertEquals(0, userList.size());

        userDao.add(user1);
        userDao.add(user2);
        userDao.add(user3);
        userList = userDao.getAll();
        assertEquals(3, userList.size());
    }

21. romMapper 뺀 후 최종 코드

UserDao

import com.likelion.domain.User;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;

import javax.sql.DataSource;
import java.sql.*;
import java.util.List;

public class UserDao {
    private DataSource dataSource;
    private JdbcTemplate jdbcTemplate;

    public UserDao(DataSource dataSource) {
        this.dataSource = dataSource;
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    RowMapper<User> rowMapper = new RowMapper<User>() {
        @Override
        public User mapRow(ResultSet rs, int rowNum) throws SQLException {
            User user = new User(rs.getString("id"), rs.getString("name"),
                    rs.getString("password"));
            return user;
        }
    };

    public void add(User user) {
        jdbcTemplate.update("INSERT INTO users(id, name, password) VALUES(?,?,?);",
                user.getId(), user.getName(), user.getPassword());
    }

    public User findById(String id) {
        String sql = "SELECT * FROM users WHERE id = ?";
        return this.jdbcTemplate.queryForObject(sql, rowMapper, id);
    }

    public List<User> getAll() {
        String sql = "SELECT * FROM users order by id";
        return this.jdbcTemplate.query(sql, rowMapper);
    }

    public void deleteAll() {
        this.jdbcTemplate.update("delete from users");
    }

    public int getCount() {
        return this.jdbcTemplate.queryForObject("select count(*) from users", Integer.class);
    }
}

UserDaoTest

import com.likelion.domain.User;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;
import org.junit.jupiter.api.extension.ExtendWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.ApplicationContext;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit.jupiter.SpringExtension;

import java.sql.SQLException;
import java.util.List;

import static org.junit.jupiter.api.Assertions.*;

@ExtendWith(SpringExtension.class)
@ContextConfiguration(classes = UserDaoFactory.class)
class UserDaoTest {
    @Autowired
    private ApplicationContext context;
    UserDao userDao;
    User user1;
    User user2;
    User user3;

    @BeforeEach
    void setUp() {
        userDao = context.getBean("awsUserDao", UserDao.class);
        user1 = new User("1", "김구글", "1234");
        user2 = new User("2", "강다음", "2345");
        user3 = new User("3", "박토익", "3456");
    }

    @Test
    void addandGet() throws SQLException {
        userDao.deleteAll();

        userDao.add(user1);

        User seletedUser = userDao.findById("1");
        assertEquals(user1.getName(), seletedUser.getName());
    }

    @Test
    void deleteAndGetCount() throws SQLException {
        userDao.deleteAll();
        assertEquals(0, userDao.getCount());

        userDao.add(user1);
        userDao.add(user2);
        userDao.add(user3);
        assertEquals(3, userDao.getCount());
    }

    @Test
    void getAll() {
        userDao.deleteAll();
        List<User> userList = userDao.getAll();
        assertEquals(0, userList.size());

        userDao.add(user1);
        userDao.add(user2);
        userDao.add(user3);
        userList = userDao.getAll();
        assertEquals(3, userList.size());
    }
}

고관운

이전 포스트

[LIKELION] 221024

다음 포스트

[LIKELION] 221025

회고

😄 느낀점

😁 목표

알고리즘(Hash)

Hash란

개념

Hash VS HashTable

속도

구현

사이즈를 정하는 기준

Hash 구현

HashTable 구현

Spring 입문(DAO 복습)

과정

과정별 실습

1. User, UserDao까지 작성된 참고사이트 참고하여 입력

2. ConnectionMaker Class로 분리 (UserDao내에 getConnection)

3. `Mysql Jdbc Driver` dependencies에 추가하기

4. addAndGet 테스트 만들기

5. Interface적용해서 분리 (ConnectionMaker)

6. Factory적용

7. Test Code를 ApplicationContext사용하게 수정

8. UserDao에 deleteAll(), getCount() 메소드 및 테스트 추가

9. Test Code에 @Autowired, @BeforeEach 적용

10. findById()에서 user==null인 경우 Exception처리

11. try / catch / final 처리 (deleteAll, getCount)

12. StatementStrategy 인터페이스, deleteAllStatement 클래스 추가 및 deleteAll에 적용

13. jdbcContextWithStatementStrategy로 분리

14. AddStatement에도 적용

15. DataSource 적용

16. deleteAll(), add()에 익명 클래스 적용

17. JdbcContext로 분리

18. executeQuery() 분리 - deleteAll

19. JdbcTemplate적용 deleteAll(), add(), getCount(), findById()

20. getAll(), getAllTest() 추가

21. romMapper 뺀 후 최종 코드

[LIKELION] 221024

[프로그래머스] Q43105(정수 삼각형), Q42898(등굣길)

0개의 댓글

[LIKELION] 221025

회고

😄 느낀점

😁 목표

알고리즘(Hash)

Hash란

개념

Hash VS HashTable

속도

구현

사이즈를 정하는 기준

Hash 구현

HashTable 구현

Spring 입문(DAO 복습)

과정

과정별 실습

1. User, UserDao까지 작성된 참고사이트 참고하여 입력

2. ConnectionMaker Class로 분리 (UserDao내에 getConnection)

3. Mysql Jdbc Driver dependencies에 추가하기

4. addAndGet 테스트 만들기

5. Interface적용해서 분리 (ConnectionMaker)

6. Factory적용

7. Test Code를 ApplicationContext사용하게 수정

8. UserDao에 deleteAll(), getCount() 메소드 및 테스트 추가

9. Test Code에 @Autowired, @BeforeEach 적용

10. findById()에서 user==null인 경우 Exception처리

11. try / catch / final 처리 (deleteAll, getCount)

12. StatementStrategy 인터페이스, deleteAllStatement 클래스 추가 및 deleteAll에 적용

13. jdbcContextWithStatementStrategy로 분리

14. AddStatement에도 적용

15. DataSource 적용

16. deleteAll(), add()에 익명 클래스 적용

17. JdbcContext로 분리

18. executeQuery() 분리 - deleteAll

19. JdbcTemplate적용 deleteAll(), add(), getCount(), findById()

20. getAll(), getAllTest() 추가

21. romMapper 뺀 후 최종 코드

[LIKELION] 221024

[프로그래머스] Q43105(정수 삼각형), Q42898(등굣길)

0개의 댓글

3. `Mysql Jdbc Driver` dependencies에 추가하기