[Spring] 스프링 DB 접근

Dodam·2023년 8월 4일

Database Spring

[Spring]

목록 보기

5/9

H2 데이터베이스 설치

https://www.h2database.com 에서 다운로드 및 설치
권한 주기: chmod 755 h2.sh (맥 사용자만 해당, 윈도우 사용자는 X)
실행: ./h2.sh (윈도우 사용자는 h2.bat)
데이터베이스 파일 생성 방법
- jdbc:h2:~/test (최초 한번)
- ~/test.mv.db 파일 생성 확인
- 이후부터는 jdbc:h2:tcp://localhost/~/test 이렇게 접속

테이블 생성하기

테이블 관리를 위해 프로젝트 루트에 sql 폴더 만들고 ddl.sql 파일을 생성

drop table if exists member CASCADE;
create table member
(
	id bigint generated by default as identity,
    name varchar(255),
    primary key (id)
);

H2 데이터베이스에 접근해서 member 테이블 생성

JDBC

환경설정

build.gradle 파일에 jdbc, h2 데이터베이스 관련 라이브러리 추가

implementation 'org.springframework.boot:spring-boot-starter-jdbc'
runtimeOnly 'com.h2database:h2'

스프링 부트 데이터베이스 연결 설정 추가

resources/application.properties

spring.datasource.url=jdbc:h2:tcp://localhost/~/test
spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.username=sa

Jdbc 리포지토리 구현

Jdbc 회원 리포지토리

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;

public class JdbcMemberRepository implements MemberRepository {

	private final 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 {
 		conn = getConnection();
 		pstmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
 
 		pstmt.setString(1, member.getName());
 
 		pstmt.executeUpdate();
 		rs = pstmt.getGeneratedKeys();
 
		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;

import hello.hellospring.repository.JdbcMemberRepository;
import hello.hellospring.repository.JdbcTemplateMemberRepository;
import hello.hellospring.repository.MemberRepository;
import hello.hellospring.repository.MemoryMemberRepository;
import hello.hellospring.service.MemberService;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;

@Configuration
public class SpringConfig {
 
 	private final DataSource dataSource;
 
 	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);
 	}
}