[springBoot] JDBC를 이용한 CRUD

suRan·2022년 7월 28일
스프링부트
0

🍃 SpringBoot

목록 보기
10/24
post-thumbnail

본 포스팅은 프로그래머스 미니 데브 코스를 공부하며
학습을 기록하기 위한 목적으로 작성된 글입니다.

JDBC

  • 자바 어플리케이션과 데이터베이스의 브릿지 역할
  • 영속성 레이어를 위해 존재한 최초의 컴포넌트
  • 1987년도부터 JDK에 포함되어있었다.



JDBC Architecture Model

JDBC 인터페이스는 크게 두 가지로 나뉜다.

  • JDBC API
  • JDBC DB Driver

JDBC DB Driver는 DBMS bender사(Oracle, MySql...) 에서 개발하고 배포한다.

JDBC API를 이용하면 DBMS의 종류에 관계없이 SQL문을 실행하고 처리할 수 있다.

백엔드 엔지니어는 JDBC API를 이용해서 JDBC DB Driver와 연결하고 쿼리에 대한 요청을 할 수 있다.



JDBC Flow

JDBC의 흐름은 다음과 같다.

  • DriverManager 를 통해서 커넥션 객체를 받아온다.
  • Connection을 통해서 Statement를 가져온다.
  • Statement를 통해서 쿼리를 실행해서 ResultSet을 가져오거나 update를 실행한다.
  • 데이터베이스 커넥션을 종료한다.

위와 같은 JDBC Flow를 따라
Api 요청이 오면 DB와 커넥션하고,
쿼리 실행 후 ResultSet으로 엔티티를 구성하고
화면에 전달하거나 서비스를 실행하고
커넥션을 종료하게 된다.


주의사항

  • ResultSet을 가져오지 않고 DML문(update)을 실행시킬 때는
    실행 결과의 ROW 개수만 받아올 수 있다.
  • 문제 상황 발생 시 커넥션은 무조건 닫아야 한다.



Table 준비

order_mgmt 테이블

intellij와 Mysql서버를 연동하고 쿼리 콘솔을 추가한다.


코드


결과


쿼리 콘솔에서 MySql과 동일하게 쿼리 입력이 가능하다.



코드 설명
customer_id가 BINARY로 되어있는 이유는 UUID를 입력하기 위해서이다.
last_login_at의 타입은 datetime이고 기본값은 Null이다.
created_at의 기본값은 CURRENT_TIMESTAMP()이다.
email은 제약조건으로 UNIQUE를 설정했기 때문에 중복값이 설정될 수 없다.



JDBC CRUD 처리하기

CRUD란
Create(생성), Read(읽기), Update(갱신), Delete(삭제)를 일컫는 말이다.

필요한 메소드들을 잘 학습하자.
connection 연결
statement 쿼리 저장
resultSet SELECT문 저장
ExecuteUpdate() SELECT 이외 구문에서 사용



전체 코드



의존성 추가

JDBC 커넥션에 필요한 드라이버 설치를 위해
pom.xml에 의존성을 추가해준다.

mysql jdbc driver maven repo 검색 후 복사 붙여넣기 하면 된다.
maven repository 링크 첨부


pom.xml 의존성 추가

컴파일 시에는 필요하지 않으므로 <scope>runtime<scope/> 코드를 추가한다.
메이븐에 추가한다는 버튼을 눌러주면 의존성 추가 끝!

더 뭔가를 할 필요 없이 DriverManager가 추가된 Jdbc 드라이버를 찾아서 사용할 수 있게 해준다.

Repository 생성

JdbcCustomerRepository클래스 를 새로 생성한다.

원래는 코드상에 패스워드가 절 대!!!!!!!!!!!!!!!!!! 들어가서는 안 된다.
하지만 아직 JDBC를 이용하기 전 설정으로 configuration을 다 빼줘야 하기 때문에
사용해준다..? (무슨 말인지 사실 잘 모르겠다.)

try/catch로 오류 처리를 해줘야한다.


코드

하지만 커넥션은 문제 상황(예외상황 등) 발생 시 무조건 닫아야 한다
finally문에서 접근해서 close해줄 수 있도록
코드의 위로 빼준다.

Statement

if (connection != null) connection.close()에서도 오류가 발생할 수 있으므로 throws 로 예외를 던져준다.

위 코드에서는 커넥션을 가지고 오면 createStatement()로 statement를 만들어준다.
그런데 사실은 statement도 닫아주어야 한다.

그러므로 finally에서 접근할 수 있도록 마찬가지로 statement도 위로 빼준다.

만약 finally 내부에서도 오류 처리를 해야한다면 다시 try-catch를 사용한다.
finally 문에서도 SQLException이 발생할 수 있다. 만약 오류가 발생한다면
logger를 이용해 오류 메세지를 출력할 수 있도록 한다.


에러를 throw하지 않고 logging 처리 후 끝낸다면
위와 같은 코드가 될 것이다.

statement를 추가했으니 statement를 통해 쿼리를 실행할 수 있다.

쿼리 작성 후 project SQL dialect를 MYSQL로 설정해준다.
connection이나 statement를 닫아주듯이, 마찬가지로 resultSet도 닫아주어야 한다.

예외가 발생하지 않고 잘 실행된다.

resultSet()

  • java.sql.ResultSet
    ResultSet은 Statement을 통해 실행한 쿼리 결과값을 ResultSet타입으로 반환해서 저장할 수 있는 객체이다. 값 저장은 executeQuery(String sql)메소드를 통해 할 수 있다.

    next()
    ResultSet의 행을 가리키는 커서 이동

    getString()
    컬럼 index, 컬럼명을 통해 ResultSet의 값을 가져올 수 있다.

    getByte()

resultSet은 개별 행에 대한 커서가 존재해서 그것을 next()로 옮겨가며 한 건씩 가져와야 한다.

현재 Customer 테이블 에는 3건의 데이터가 있다.

while문으로 반복을 돌리며 데이터를 꺼내보자.

next()메소드를 사용하면 커서가 1행을 가리키게 된다. getString()으로 값을 꺼내서 로그로 출력해본다.


주의

  • 커서는 초기에 아무 것도 가리키고 있지 않다. 아무 것도 가리키지 않은 상태에서
    get()메소드를 사용하면 오류가 발생한다.

콘솔창에 로그가 출력되지 않는다면 logback.xml파일의 log level을 수정해본다.

같은 방식으로 custom_id를 꺼내올 수도 있을 것이다. 그러나 custom_id는 바이너리타입 으로 저장되어있다.

그리고 custom_id는 자바코드에서 UUID로 정의됐었다.
그러므로 꺼내온 데이터를 사용하려면 getBytes로 불러온 데이터를
다시 UUID로 변환해주는 과정이 필요하다.

이 때 UUID.nameUUIDFromBytes()메소드가 쓰인다.


try-with-resources

Connection을 연결한 상태에서 어떤 문제가 발생했을 때 반드시 Connection을 닫아주어야 한다.

왜냐하면 DataBase Connection 자체가 많은 리소스를 차지하기 때문에,
Connection이 연결된 상태에서는 데이터베이스와 어플리케이션 모두에게 부담이 크기 때문이다.

그래서 쿼리를 진행한 뒤에 반드시 Connection을 닫아줘야
데이터베이스와 어플리케이션의 부담이 덜어진다.

위의 코드에서는 예외 발생 이후
finally 내부의 try-catch문에서 무조건
Connection, statement, resultSet을 닫아주고 있다.

if (connection != null) connection.close();
if (statement != null) statement.close();
if (resultSet != null) resultSet.close();

그래서 코드가 상당히 지저분해졌다.

다행히도 java 10에서 try-with-resources를 지원하면서
try-with-resources를 이용한 자원해제 처리가 가능해졌다.
try-with-resources를 사용하게 되면 try블록에서 Connection을 사용할 수 있고,
사용 후에 Connection이 자동으로 해제된다.

try-with-resources를 사용하려면 try에 autoCloseable의 구현체가 들어와야한다.
Connection, statement, resultSet은 모두 autoCloseable의 구현체이기 때문에
위 코드에서는 첫 번째 {}블록이 끝난 시점에 Connection의 연결이 자동으로 닫힌다.


AutoCloseable을 구현한 Connection 인터페이스

정리해보자면, try-with-resources를 이용하면
try블록이 끝나거나 모든 종류의 예외가 발생할 때 자동으로 close를 호출해준다.

try-with-resources는 AutoCloseable를 구현하는 구현체인
Connection, statement, resultSet 등의 멀티 리소스에 모두 적용된다.
사용이 끝나거나 예외가 발생한 모든 리소스의 close를 자동 호출해주는 편리한 기능이라고 볼 수 있다.




이제 while문에 Timestamp타입의 create_at필드를 가져올 코드를 작성한다.

그런데 현재 java에서 시간과 날짜를 대표하는 타입은 LocalDate타입이다.
따라서 .toLocalDate()메소드를 이용해 LocalDate타입으로 형변환한 뒤 이용하는 것이 좋다.

주의

  • name과 customerId는 not null 으로 생성되었기 때문에 null 여부를 체크해줄 필요가 없었지만
    created_at처럼 null이 될 수 있는 경우에는 null 여부를 체크해주고 형변환을 해주어야 한다.
    그렇지 않으면 NullPointException 이 발생할 수 있다.



Select문 : 문자열 이용

  • connection 연결
  • statement 쿼리 저장
  • resultSet SELECT문 저장
  • ExecuteUpdate() SELECT 이외 구문에서 사용

이번에는 Where절을 추가해보자.
가장 간단한 방법은 sql 문장을 문자열 조합으로 바꾸는 것이다.

SELECT_SQL라는 var변수를 만들고 executeQuery()에 인자로 넣었던 sql문을 대입한다.

그리고 String 타입의 매개변수를 입력받고
String타입의 List를 반환하는 findNames 메소드를 새로 생성해서
Main의 코드를 모두 findNames 메소드로 옮긴다.

그리고 return해줄 List를 담을 그릇인 참조변수도 선언해준다.

findNames를 호출하면 주어진 name에 해당되는 결과를 찾는 것이다.

name이 "name"인 사람을 찾고싶다면 이런 코드 작성이 가능한데,

다음과 같이 좀 더 포맷팅을 해줄 수 있다.

  • formatted()
    입력값을 string으로 변환해서 return한다.

% 이 코드는 진행하다가 에러가 발생했는데 나중에 그 이유를 찾아보고싶다. %

이렇게 코드를 작성해주면 name이 치환되어 실제 WHERE절에 들어가게 된다.

결과



SQL injection

하지만 이 코드에는 큰 문제가 있다.

where절 쿼리를 다음과 같이 준다면? 어떤 결과가 나올까?

이것이 그 유명한 SQL injection이다.
tester01의 뒤에 ' OR 'a'='a 라는 or문을 주입시킨 코드이다.

이로 인해 유저의 권한에 맞는 tester01의 정보만 열람할 수 있어야하는데
SQL injection이 발생해 모든 고객의 정보가 모두 열람된 것이다.

만약 SELECT문을 formInput 으로 받았다면?
어떤 문제가 발생했을까?

이처럼 SQL문을 문자열 조합으로 사용한다면
원치않는 쿼리가 실행되는 문제인
SQL injection 에 매우 취약해진다.

이런 문제점 때문에 SQL문의 문자열 조합은 매우 신중하게 사용해야 하고
만약 사용한다면 SQL injection이 발생하지 않도록 잘 제어해야 한다.



Select문 : prepared Statement (권장)

SQL문의 문자열 조합에서 생길 수 있는 문제를 해결하기 위해서
prepared Statement를 사용할 수 있다.

보통 Statement를 사용하면
statement.executeQuery()메소드의 사용 등 매번 쿼리를 수행할 때마다

  1. 쿼리를 분석하고
  2. 컴파일하고
  3. 실행하는

3단계를 거친다.


그런데 prepared Statement는 처음 한 번만 위의 3단계를 거치고
이후에는 case에 담아 재사용 된다. 이것이 컴파일 되기 때문에 쿼리문은
처음에 만들어진 쿼리문으로 고정된다.

따라서 SQL injection이 발생할 우려가 사라진다.

prepared Statement는 말그대로 Statement를 미리 만들어두는 것이므로
쿼리를 중간에 바꿀 수 없고, 매번 3단계를 거치지 않아도 되기 때문에 성능상의 이점이 있다.

그런 이유로 가급적이면 prepared Statement를 사용하는 것이 좋다.
(심지어 동적인 SQL문일지라도 prepared Statement으로 사용할 수 있는 방법이 있다.)


prepared Statement으로 SQL문을 사용할 때는
치환될 데이터에 대해 따로 포맷팅을 할 필요 없이 물음표 '?'를 사용한다.

그리고 prepared Statement에 ? 에 치환될 값을 전달해준다.

그런데 try문에 auto closerable을 할 수 있는 걸 못넣기 떄문ㅇ?? 무슨 소리야 이건

statement를 {} 블록에서 호출해주어야 한다.

  • statement.setString()
    statement.setString()는 지정된 인덱스 매개변수를 지정된 문자열 값으로 적어주는 것이다.
    prepared Statement를 사용하려면 이렇게 파라미터를 설정해주어야 한다.
    파라미터 설정 시, 인덱스 1부터 시작한다는 점을 숙지해야 한다.

resultSet을 선언하는 코드의 위치를 {}블록 안으로 바꿔줬기 때문에
close를 위한 try-catch문을 따로 만들어주어야 한다.

그래서 코드는 다음과 같아진다.

중간에

check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1라는 오류가 발생했는데
(왜 강의를 보고 따라치는데도 오류가 발생하는가)

스택오버플로우에서 이 글을 보고 해결할 수 있었다.
https://stackoverflow.com/questions/24917917/right-syntax-to-use-near

statement.executeQuery()의 인자에 String을 넣으면 바인딩하는 파라미터를 사용하지 않고 쿼리를 실행한다는 것 같다. 자세히 보니 정말 executeQuery(SELECT_SQL) 이라고
코드를 적어두었다.



수정한 코드

문제없이 실행된다.

tester01' OR 'a'='a를 SELECT문으로 질의해도 SQL injection이 발생하지 않는 것이다.

제대로 된 질의를 한 번 넣어보자.


잘 동작한다.



특정 이름 조회

전체 이름 조회



SQL injection이 발생하지 않는 원리

왜 ' OR 'a'='a은 들어가지 않았을까?

' OR 'a'='a를 인자로 주고 statement의 로그를 찍어보면 이런 결과가 나온다.

로그에서
statement의 실질적 구현체는 mysql.cj.jdbc.ClientPreparedStatement라는 것을 확인 가능.
select문이 어떻게 만들어졌는지도 확인 가능.

쿼리가 문자열이 아니기 때문에 OR절이 들어갈 수 없다는 사실을 확인할 수 있다.



Insert문

새로운 유저를 추가할 때 사용하는 메소드를 정의해본다.

ExecuteUpdate()

  • ExecuteUpdate()
    SELECT가 아닌 구문에서 사용할 수 있는 메소드. Int값을 반환한다.
    INSERT / DELETE / UPDATE : 반영된 레코드의 건수 반환.
    CREATE / DROP : -1 반환.

참고자료 : 📌 꽁담 님의 [JAVA] Execute, ExecuteQuery, ExecuteUpdate 차이점 알아보기



코드

private final String INSERT_SQL = "INSERT INTO customers(customer_id, name, email) VALUES (UUID_TO_BIN(?), ?, ?)";

  • UUID_TO_BIN()
    MySQL 8.0 이상에서 사용할 수 있는 함수이다.
    STRING타입의 매개변수를 바이너리 타입으로 바꿔준다.

결과



Delete문

코드



Update문

코드



UUID의 버전 문제

userId, 즉 UUID를 사용할 때
입력된 UUID와 조회된 UUID가 일치하지 않는 문제가 발생했다.


이유

  • UUID.ramdomUUID()으로 만든 UUID의 버전은 VER.4이다.
  • 검색해서 가져온 UUID의 버전은 VER.3이다.

즉, UUID.nameUUIDFromBytes 메소드에 사용되는 UUID가 TYPE 3이라서 발생한 문제이다.


해결
UUID를 만들 수 있는 세가지 방법이 있다.
1. UUID.randomUUID() <- TYPE 4
2. UUID.nameUUIDFromBytes <- TYPE 3
3. UUID.fromString()
4. UUID의 생성자 ✔

UUID의 생성자 인자에는 long타입 mostSigBits, long타입 leastSigBits 가 들어간다.
테이블 customers의 customer_id는 16byte -> 128bit.
long타입은 64bit. long타입 인자가 두 개 들어가서 128bit가 된다.

생성자로 인자를 넣어주기 위해서는 이렇게 64bit씩 쪼개서 넣어줘야한다.


생성(Main)
var customerId = UUID.randomUUID();

--

호출
var customerId = toUUID(resultSet.getBytes("customer_id"));

static UUID toUUID(byte[] bytes){
	var byteBuffer = ByteBuffer.wrap(bytes);
	return new UUID(byteBuffer.getLong(), byteBuffer.getLong());
}

결론
UUID.ramdomUUID()으로 만든 UUID의 버전은 TYPE 4고, MySQL에서는 TYPE 4를 지원한다.
util성 메소드를 따로 생성해서 메소드에 인자를 넣어주면 UUID의 생성자 두 개로 분리되어 들어가도록 한다.

해당 메소드에서는
인자로 들어온 매개변수(byte로 형변환 된 테이블의 ID)를 Buffer로 감싸 Long 두 개로 쪼갠 뒤 UUID의 생성자를 호출한 결과를 return한다.

그럼 호출한 쪽에서는 TYPE 4 UUID를 반환받아 저장하게 된다.



rf
https://docs.microsoft.com/ko-kr/sql/connect/jdbc/reference/setstring-method-sqlserverpreparedstatement?view=sql-server-ver16

개념정리

  • try-catch
    오류 발생 예상 부분을 try블록으로 감싼다.
    발생할 오류와 관련된 Exception을 catch블록에서 처리한다.
    finally블록은 오류가 발생하든 하지않든 무조건 실행된다.

  • throws
    throws는 예외가 발생했을 때, 예외를 호출한 쪽에서 처리하도록 던져주는 것이다.
profile
suRan
개발 공부를 해라
이전 포스트

[SpringBoot] test framework를 이용한 통합테스트

0개의 댓글