1. H2 DataBase 구성
1-1. H2 DataBase 다운로드
https://www.h2database.com/html/main.html
저거 클릭 후 압축을 푼다.
1-2. H2 실행
윈도우 사용자라면 h2w.bat 클릭!
나처럼 h2.bat 클릭해서 30분동안 삽질하지 말고
1-3. H2 접속
저대로 연결 클릭
1-4. member Table 만들기
h2 콘솔에 입력
drop table if exists member CASCADE;
create table member
(
id bigint generated by default as identity,
name varchar(255),
primary key (id)
);
2. Spring boot 프로젝트 H2 연결
2-1. gradle dependency 추가
build.gradle
dependencies {
implementation 'org.springframework.boot:spring-boot-starter-thymeleaf'
implementation 'org.springframework.boot:spring-boot-starter-web'
//=====add=====
implementation 'org.springframework.boot:spring-boot-starter-jdbc'
runtimeOnly 'com.h2database:h2'
//=====add=====
testImplementation 'org.springframework.boot:spring-boot-starter-test'
}2-2. application.properties 추가
application.properties
spring.datasource.url=jdbc:h2:tcp://localhost/~/test
spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.username=sa
spring.datasource.password=
spring.datasource.url의 값은 아래의 h2 서버 로그인에 나와있다.
IDE에 빨간 줄이 뜰 때 저 코끼리 한번 클릭해주자.
3. member 정보를 DB에 입력
3-1. JdbcMemberRepository 생성
JdbcMemberRepository.java
package com.rptp.rptpSpringBoot.repository.impliment;
import com.rptp.rptpSpringBoot.model.MemberDTO;
import com.rptp.rptpSpringBoot.repository.MemberRepository;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.stereotype.Repository;
import javax.sql.DataSource;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;
@Repository
public class JdbcMemberRepository implements MemberRepository {
private final DataSource dataSource;
@Autowired
public JdbcMemberRepository(DataSource dataSource) {
this.dataSource = dataSource;
}
@Override
public MemberDTO save(MemberDTO member) {
String sql = "insert into member(name) values(?)";// sql문에 파라미터 값을 받음.
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
conn = getConnection(); //DB Conntection을 가지고 옴
pstmt = conn.prepareStatement(sql,
Statement.RETURN_GENERATED_KEYS);
//connection에 sql문 입력, 추가적으로 자동 생성되는 키도 얻어옴
pstmt.setString(1, member.getName());
//위 sql 물음표 중 첫번째(1) 파라미터에 값을 넣는다
pstmt.executeUpdate();
//DB에 완성된 쿼리를 전송
rs = pstmt.getGeneratedKeys();
//DB에서 pstmt에서 요청한 결과물을 가져옴
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<MemberDTO> 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()) {
MemberDTO member = new MemberDTO();
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<MemberDTO> 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<MemberDTO> members = new ArrayList<>();
while(rs.next()) {
MemberDTO member = new MemberDTO();
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<MemberDTO> 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()) {
MemberDTO member = new MemberDTO();
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);
}
}와씨 코드길이 토나오네 ㄹㅇ
3-2 실행
Repository가 bean에서 2개로 인식되어 충돌이 난다.
기존에 만든 MemoryMemberRepository에서 위 에너테이션을 지운다.
재실행 후 회원가입에서 정보를 넣으면 성공적으로 DB에 입력되는 것을 볼 수 있다.
참조
4. JdbcMemberRepositoryTest 추가
JdbcMemberRepositoryTest
package com.rptp.rptpSpringBoot.repository.impliment;
import com.rptp.rptpSpringBoot.model.MemberDTO;
import com.rptp.rptpSpringBoot.repository.MemberRepository;
import com.rptp.rptpSpringBoot.service.MemberService;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.transaction.annotation.Transactional;
import static org.assertj.core.api.Assertions.assertThat;
import static org.junit.jupiter.api.Assertions.assertEquals;
import static org.junit.jupiter.api.Assertions.assertThrows;
@SpringBootTest
@Transactional
class JdbcMemberRepositoryTest {
@Autowired
MemberService memberService;
@Autowired
MemberRepository memberRepository;
@Test
public void 회원가입() throws Exception {
//Given
MemberDTO member = new MemberDTO();
member.setName("루핑이2");
//When
Long saveId = memberService.join(member);
//Then
MemberDTO findMember = memberRepository.findById(saveId).get();
assertEquals(member.getName(), findMember.getName());
}
@Test
public void 중복_회원_예외() throws Exception {
//Given
MemberDTO member1 = new MemberDTO();
member1.setName("spring");
MemberDTO member2 = new MemberDTO();
member2.setName("spring");
//When
memberService.join(member1);
IllegalStateException e = assertThrows(IllegalStateException.class,
() -> memberService.join(member2));//예외가 발생해야 한다.
assertThat(e.getMessage()).isEqualTo("이미 존재하는 회원입니다");
}
}@SpringBootTest
스프링을 실행시켜 스프링 컨테이너를 생성해 테스트를 진행한다.(bean Autowired를 할 수 있다!!)
@Transactional
트랜젝션을 rollback시켜 실제 db에 저장이 되지 않게 한다. (테스트를 반복해서 진행할 수 있다!!)
romantic programmer