6. 스프링 DB 접근 기술

백엔드/인프런_스프링

6. 스프링 DB 접근 기술

smallsilver_west 2024. 11. 23. 22:58

H2 데이터베이스 설치

: 개발이나 테스트 용도로 가볍고 편리한 DB, 웹 화면 제공

데이터베이스 파일 생성

- jdbc:h2:~/test : 최초 1회 실행

- ~/test.mv.db : 파일 생성 확인

* 이후 접속 시에는 jdbc:h2:tcp://localhost/~/test 사용

테이블 생성

https://github.com/smallsilverwest/EWHA.W.UNIV_ECC_Spring/blob/main/sql/ddl.sql

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

순수 Jdbc

환경 설정

https://github.com/smallsilverwest/EWHA.W.UNIV_ECC_Spring/blob/main/build.gradle

jdbc, h2 데이터베이스 관련 라이브 추가

https://github.com/smallsilverwest/EWHA.W.UNIV_ECC_Spring/blob/main/src/main/resources/application.properties

스프링 부트 DB 연결 설정 추가

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

DataSource

데이터베이스 커넥션을 획득할 때 사용하는 객체

스프링 부트는 데이터베이스 커넥션 정보를 바탕으로 DataSource를 생성, 스프링빈으로 만들어 DI를 받음

구현 클래스 추가 이미지

스프링 설정 이미지

개방-폐쇄 원칙 (OCP, Open-Closed Principle)

: 확장에는 열려있고, 수정 및 변경 전에는 단힌 상태

이때, 스프링의 DI (Dependncies Injection)을 사용하는 경우 기존 코드 수정 없이 설정만으로 구현 클래스 변경 가능

스프링 통합 테스트

package hello.hellospring.service;

import hello.hellospring.domain.Member;
import hello.hellospring.repository.MemberRepository;
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 MemberServiceIntegrationTest {

	@Autowired MemberService memberService;
    @Autowired MemberRepository memberRepository;
    
    @Test
 	public void 회원가입() throws Exception {
    
    	//Given
    	Member member = new Member();
    	member.setName("hello");
    
    	//When
    	Long saveId = memberService.join(member);
    
    	//Then
    	Member findMember = memberRepository.findById(saveId).get();
    	assertEquals(member.getName(), findMember.getName());
	}
    
    @Test
 	public void 중복_회원_예외() throws Exception {
    
    	//Given
        Member member1 = new Member();
 		member1.setName("spring");
        
        Member member2 = new Member();
        member2.setName("spring");
        
        //When
        memberService.join(member1);
 		IllegalStateException e = assertThrows(IllegalStateException.class,
 			() -> memberService.join(member2));//예외가 발생해야 한다.
 		
        assertThat(e.getMessage()).isEqualTo("이미 존재하는 회원입니다.");
	}
}

* @SpringBottTest: 스프링 컨테이너와 테스트를 함께 실행

* @Transactional: 테스트 케이스에 해당 애노테이션이 있는 경우 테스트 시작 전 트랜잭션 시작, 테스트 완료 후 롤백

이때, DB에 데이터가 남지 않아 다음 테스트에 영향을 주지 않는다는 장점

스프링 JdbcTemplate

순수 Jdbc와 동일한 환경설정

스프링 JdbcTemplate과 MyBatis와 같은 라이브러리의 경우 JDBC API에서 반복 코드를 제거하지만 SQL은 직접 작성 필요

스프링 JdbsTemplate 회원 리포지토리

package hello.hellospring.repository;

import hello.hellospring.domain.Member;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;

import javax.sql.DataSource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Optional;

public class JdbcTemplateMemberRepository implements MemberRepository {
	private final JdbcTemplate jdbcTemplate;
	
    public JdbcTemplateMemberRepository(DataSource dataSource) {
		jdbcTemplate = new JdbcTemplate(dataSource);
	}

	@Override
	public Member save(Member member) {
		SimpleJdbcInsert jdbcInsert = new SimpleJdbcInsert(jdbcTemplate);
		jdbcInsert.withTableName("member").usingGeneratedKeyColumns("id");

		Map<String, Object> parameters = new HashMap<>();
		parameters.put("name", member.getName());

		Number key = jdbcInsert.executeAndReturnKey(new
MapSqlParameterSource(parameters));
		member.setId(key.longValue());
		return member;
	}
    
    @Override
 	public Optional<Member> findById(Long id) {
 		List<Member> result = jdbcTemplate.query("select * from member where id = ?", memberRowMapper(), id);
 		return result.stream().findAny();
	}
    
    @Override
 	public List<Member> findAll() {
 		return jdbcTemplate.query("select * from member", memberRowMapper());
 	}
    
    @Override
 	public Optional<Member> findByName(String name) {
 		List<Member> result = jdbcTemplate.query("select * from member where name = ?", memberRowMapper(), name);
 		return result.stream().findAny();
 	}
    
    private RowMapper<Member> memberRowMapper() {
 		return (rs, rowNum) -> {
			Member member = new Member();
 			member.setId(rs.getLong("id"));
 			member.setName(rs.getString("name"));
 			return member;
 		};
 	}
}

JdbcTemplate을 사용하도록 스프링 설정 변경

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);
        return new JdbcTemplateMemberRepository(dataSource);
     }
}

JPA

https://github.com/smallsilverwest/EWHA.W.UNIV_ECC_Spring/blob/main/build.gradle

* spring-boot-starter-data-jpa는 내부에 jdbc 관련 라이브러리를 포함하므로 제거 가능

스프링 부트에 JPA 설정 추가

https://github.com/smallsilverwest/EWHA.W.UNIV_ECC_Spring/blob/main/src/main/resources/application.properties

- show-sql: JPA가 생성하는 SQL을 출력

- ddl-auto: JPA는 테이블을 자동으로 생성하는 기능을 제공하므로 none을 사용해 해당 기능 종료

- create: 엔티티 정보를 바탕으로 테이블 직접 생헝

JPA 엔티티 매핑

package hello.hellospring.domain;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;

@Entity
public class Member {
	
    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
 	private Long id;
	private String name;
    
    public Long getId() {
 		return id;
 	}
    
    public void setId(Long id) {
    	this.id = id;
    }
    
    public String getName() {
		return name;
	}
    
    public void setName(String name) {
		this.name = name;
	}
}

JPA 회원 리포지토리

package hello.hellospring.repository;

import hello.hellospring.domain.Member;

import javax.persistence.EntityManager;
import java.util.List;
import java.util.Optional;

public class JpaMemberRepository implements MemberRepository {

	private final EntityManager em;
 	
    public JpaMemberRepository(EntityManager em) {
		this.em = em;
 	}
 	
    public Member save(Member member) {
 		em.persist(member);
 		return member;
 	}
 
 	public Optional<Member> findById(Long id) {
 		Member member = em.find(Member.class, id);
 		return Optional.ofNullable(member);
 	}
 
 	public List<Member> findAll() {
		return em.createQuery("select m from Member m", Member.class)
 			.getResultList();
 	}
    
	public Optional<Member> findByName(String name) {
		List<Member> result = em.createQuery("select m from Member m where m.name = :name", Member.class)
			.setParameter("name", name)
 			.getResultList();
 		return result.stream().findAny();
 	}
}

서비스 계층에 트랜잭션 추가

import org.springframework.transaction.annotation.Transactional

@Transactional
public class MemberService {}

* org.springframework.transaction.annotation.Transactional

: 스프링은 해당 클래스의 메서드를 실행할 때 트랜잭션을 시작하고 메서드 정상 종료 후 커밋, 이때 런타임에 예외가 발생하면 롤백

단, JPA를 통한 모든 데이터 변경은 트랜잭션 내에서 실행

JPA 사용 스프링 설정 변경

package hello.hellospring;


import hello.hellospring.repository.*;
import hello.hellospring.service.MemberService;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.persistence.EntityManager;
import javax.sql.DataSource;

@Configuration
public class SpringConfig {

	private final DataSource dataSource;
	private final EntityManager em;
    
    public SpringConfig(DataSource dataSource, EntityManager em) {
 		this.dataSource = dataSource;
 		this.em = em;
 	}
    
    @Bean
 	public MemberService memberService() {
 		return new MemberService(memberRepository());
	}

	@Bean
 	public MemberRepository memberRepository() {
		// return new MemoryMemberRepository();
		// return new JdbcMemberRepository(dataSource);
		// return new JdbcTemplateMemberRepository(dataSource);
	return new JpaMemberRepository(em);
 	}
}

스프링 데이터 JPA

스프링 데이터 JPA 회원 리포지토리

package hello.hellospring.repository;

import hello.hellospring.domain.Member;
import org.springframework.data.jpa.repository.JpaRepository;

import java.util.Optional;

public interface SpringDataJpaMemberRepository extends JpaRepository<Member, Long>, MemberRepository {

	Optional<Member> findByName(String name);
}

스프링 설정 변경

package hello.hellospring;

import hello.hellospring.repository.*;
import hello.hellospring.service.MemberService;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

@Configuration
public class SpringConfig {

	private final MemberRepository memberRepository;
	
    public SpringConfig(MemberRepository memberRepository) {
		this.memberRepository = memberRepository;
}

@Bean
	public MemberService memberService() {
	return new MemberService(memberRepository);
	}
}

* JPA가 SprigDataJpaMemberRepository를 스프링 빈으로 자동 등록

스프링 데이터 JPA 제공 기능

- 인터페이스를 통한 기본적인 CRUD

- findByName( ), findByEmail( )과 같이 메서드의 이름으로 조회 기능 제공

- 페이징 기능 자동 제공

'백엔드 > 인프런_스프링' 카테고리의 다른 글

7. AOP (0)	2024.11.23
5. 회원 관리 예제 - 웹 MVC 개발 (1)	2024.11.16
4. 스프링 빈과 의존관계 (0)	2024.11.16
3. 회원 관리 예제 - 백엔드 개발 (0)	2024.11.16
2. 스프링 웹 개발 기초 (2)	2024.11.09

현재글6. 스프링 DB 접근 기술

smallsilver_west

오블완, 티스토리챌린지,

Today :
Yesterday :

일	월	화	수	목	금	토
			1	2	3	4
5	6	7	8	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28	29	30	31

smallsilver_west