[Spring] JPA 심화 JDBC 끝내기기 - 실습편

#JDBC

📘 JDBC FINAL: Spring JDBC

📘 개념 정리

🎯  Spring JDBC

• Spring JDBC는 기존 JDBC를 더 편하게 쓰기 위한 API 레벨의 추상화다
• 장점
  
  • 코드 간결화
  • 자원 자동 정리
  • 예외 추상화 (DataAccessException)
  • 객체 매핑 지원 (RowMapper)

항목	JDBC	Spring JDBC
코드양	반복 코드 많음 (try-catch, close 등)	반복 제거, 코드 간결
예외 처리	SQLException 직접 처리	DataAccessException 계열로 추상화
트랜잭션 처리	직접 제어	선언적 트랜잭션 처리 가능
PreparedStatement 처리	수동 바인딩	JdbcTemplate에서 자동 처리
결과 매핑(ResultSet)	수동으로 하나하나 꺼냄	RowMapper 등으로 자동 매핑 가능
유지보수성	낮음	높음
학습 난이도	낮음 (단순)	중간 (Spring 개념 필요)

• •  

Spring MVC 3계층과 DB에서 데이터 교환 과정.

 

Spring 환경에서의 JDBC 사용

• Example API Code
   PostMan에서 Spring으로 Member라는 객체의 정보를 INSERT, SELECT, UPDATE ,DELETE 하는 코드이다.
  https://github.com/Faragon02/JDBCTestCode 

GitHub - Faragon02/JDBCTestCode

clone을 받아 실행 할거면 위의 git링크를 이용 할것.

 

 

• Contorller

@RequestMapping("/testMembers")
@RestController
@RequiredArgsConstructor
public class Controller {
	private final MemberServiceImpl memberService;

	@PostMapping
	public ResponseEntity<MemberResponseDto> createMember(@RequestBody MemberRequestDto dto){

		return new ResponseEntity<>(memberService.setMember(dto), HttpStatus.OK);
	}
	@GetMapping("/{id}")
	public ResponseEntity<MemberResponseDto>getMember(@PathVariable Long id){
		MemberResponseDto memberResponseDto = memberService.getMember(id);
		if(memberResponseDto == null){
			new ResponseEntity<>(HttpStatus.NOT_FOUND);
		}
		return  new ResponseEntity<>(memberResponseDto, HttpStatus.OK);
	}

	@GetMapping
	public ResponseEntity<List<MemberResponseDto>>getAllMember(){
		return  new ResponseEntity<>(memberService.geAllMembers(), HttpStatus.OK);
	}

	@PutMapping("/{id}")
	public ResponseEntity<MemberResponseDto> updateAllMember(@PathVariable Long id, @RequestBody MemberRequestDto dto){
		MemberResponseDto memberResponseDto = memberService.updateAllMember(id, dto);
		if(memberResponseDto == null){
			new ResponseEntity<>(HttpStatus.NOT_FOUND);
		}
		return  new ResponseEntity<>(memberResponseDto, HttpStatus.OK);
	}
	@PatchMapping("/{id}")
	public ResponseEntity<MemberResponseDto> updateMember(@PathVariable Long id, @RequestBody MemberRequestDto dto){
		MemberResponseDto memberResponseDto = memberService.updateMemberName(id, dto);
		if(memberResponseDto == null){
			new ResponseEntity<>(HttpStatus.NOT_FOUND);
		}
		return  new ResponseEntity<>(memberResponseDto, HttpStatus.OK);
	}

	@DeleteMapping("/{id}")
	public ResponseEntity<Void> withdrawMember(@PathVariable Long id){
		memberService.withdrawMember(id);
		return new ResponseEntity<>(HttpStatus.OK);
	}

}

• Service

@Service
@RequiredArgsConstructor
public class MemberServiceImpl {
	private final JdbcTemplate jdbcTemplate;
    private final MemberRepository memberRepasitory;


    //member 데이터 추가
    public MemberResponseDto setMember(MemberRequestDto dto){
		Member member = new Member(dto);
		return  memberRepasitory.saveMember(member);
	}

    //단건 조회
	public MemberResponseDto getMember(Long id){
		Member member = memberRepasitory.findById(id)
			.orElseThrow(()->new RuntimeException("데이터 조회 실패"));

		return new MemberResponseDto(member);
	}

    //전체 조회
	public List<MemberResponseDto> geAllMembers(){
		return memberRepasitory.findByAllId();
	}

	//필드 전체 수정
	public MemberResponseDto updateAllMember(Long id, MemberRequestDto dto){

		memberRepasitory.updateAllFieldMember(id, dto);

		Member member = memberRepasitory.findById(id)
			.orElseThrow(()->new RuntimeException("데이터 조회 실패"));
		return new MemberResponseDto(member);
	}

	//필드 부분 수정(이름)
	public MemberResponseDto updateMemberName(Long id, MemberRequestDto dto){

		memberRepasitory.updateMemberName(id, dto);

		Member member = memberRepasitory.findById(id)
			.orElseThrow(()->new RuntimeException("데이터 조회 실패"));
		return new MemberResponseDto(member);
	}

    //memeber 정보 삭제
	public void withdrawMember(Long id){
		Member member = memberRepasitory.findById(id)
			.orElseThrow(()->new RuntimeException("데이터 조회 실패"));

		memberRepasitory.withdrawMember(id);
	}
}

       

 

• Repository

@Repository
public class MemberRepository {

	private final JdbcTemplate jdbcTemplate;

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

	//데이터 추가
	public MemberResponseDto saveMember(Member member){

		KeyHolder keyHolder = new GeneratedKeyHolder();
		String sql = "INSERT INTO member(name, age) VALUES (?, ?)";

		jdbcTemplate.update(connection -> {
			PreparedStatement ps = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			ps.setString(1, member.getName());
			ps.setInt(2, member.getAge());
			return ps;
		}, keyHolder);
		Number key = keyHolder.getKey(); // 여기서 id 값을 바로 얻을 수 있어!
		Member getMember = new Member(key.longValue(), member.getName(), member.getAge());

		return new MemberResponseDto(getMember);
	}

	//단건 조회
	public Optional<Member> findById(Long id){
		String sql = "SELECT * FROM member WHERE id = ?";
		List<Member> members =jdbcTemplate.query(sql, memberRepasitoryRowMapperV1(), id);
		return members.stream().findFirst();
	}
	private RowMapper<Member> memberRepasitoryRowMapperV1(){
		return new RowMapper<Member>() {
			@Override
			public Member mapRow(ResultSet rs, int rowNum) throws SQLException {
				return new Member(
					rs.getLong("id"),
					rs.getString("name"),
					rs.getInt("age")
				);
			}
		};
	}
	//다건 조회
	public List<MemberResponseDto> findByAllId(){
		String sql = "SELECT * FROM member";
		return jdbcTemplate.query(sql, memberRepasitoryRowMapperV2());
	}
	private RowMapper<MemberResponseDto> memberRepasitoryRowMapperV2(){
		return new RowMapper<MemberResponseDto>() {
			@Override
			public MemberResponseDto mapRow(ResultSet rs, int rowNum) throws SQLException {
				return new MemberResponseDto(
					rs.getLong("id"),
					rs.getString("name"),
					rs.getInt("age")
				);
			}
		};
	}
	//필드 전체 수정
    public void updateAllFieldMember(Long id, MemberRequestDto member ){
		String sql = "UPDATE Member SET name =?, age=? WHERE id =?";
		jdbcTemplate.update(sql,member.getName(), member.getAge(), id);
	}
	//필드 전체 수정
	public void updateMemberName(Long id, MemberRequestDto member ){
		String sql = "UPDATE Member SET name =? WHERE id =?";
		jdbcTemplate.update(sql,member.getName(), id);

	}
	public void withdrawMember(Long id){
		String sql = "DELETE FROM Member WHERE id=?";
		jdbcTemplate.update(sql, id);
	}

}

       

⚠️ 실수 및 주의사항

JDBC Template (RowMapper)

• SQL Mapper 첫번째 주자로 JDBCTemplate 에 RowMapper 탄생
  • 쿼리 수행 결과와 객채 필드 매핑
  • RowMapper 로 응답필드 매핑코드 재사용
  • Connection, Statement, ResultSet 반복적 처리 대신 해줌
  • 😵‍💫 But, 결과값을 객체 인스턴스에 매핑하는데 여전히 많은 코드가 필요함

 

 

 

정리: JdbcTemplate은 Spring에서 JDBC를 안전하고 간편하게 다루기 위한 기본 도구이다. ORM을 쓰더라도, JDBC를 대체하는 것이 아니라 기반 위에 구축되는 것이기 때문에 반드시 익혀두자.