[스프링 DB 2편] #2. 데이터 접근 기술 - 스프링 JdbcTemplate

[YONGHYNG]

Jdbc Template

• 장점 : 설정의 편리함, 반복 문제 해결(커넥션 획득, statement 준비하고 실행, 결과를 반복하도록 루프를 실행, 커넥션 종료,
  statement, resultset 종료, 트랜잭션 다루기 위한 커넥션 동기화, 예외 발생시 스프링 예외 변환기 실행)
• 단점 : 동적 SQL을 해결하기 어려움

build.gradle에 추가해야 함

** JdbcTemplateItemRepositoryV1 **

package hello.itemservice.repository.jdbctemplate;

import hello.itemservice.domain.Item;
import hello.itemservice.repository.ItemRepository;
import hello.itemservice.repository.ItemSearchCond;
import hello.itemservice.repository.ItemUpdateDto;
import lombok.extern.slf4j.Slf4j;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import javax.sql.DataSource;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;

/**
 * JdbcTemplate
 * */
@Slf4j
@Repository
public class JdbcTemplateItemRepositoryV1 implements ItemRepository {

    private final JdbcTemplate template;

    //의존성 주입
    public JdbcTemplateItemRepositoryV1(DataSource dataSource){
        this.template = new JdbcTemplate(dataSource); //JdbcTemplate 은 SQL 실행, 데이터 매핑, 예외를 간단히 처리해줌
    }

    @Override
    public Item save(Item item){ //데이터를 테이블에 삽입
        String sql = "insert into item(item_name, price, quantity) values(?, ?, ?)";
        KeyHolder keyHolder = new GeneratedKeyHolder();
        template.update(connection -> {
            //자동 증가 키, PreparedStatement 로 sql 파라미터 안전하게 전달
            PreparedStatement ps = connection.prepareStatement(sql, new String[] {"id"});
            ps.setString(1, item.getItemName());
            ps.setInt(2, item.getPrice());
            ps.setInt(3, item.getQuantity());
            return ps;
        }, keyHolder);

        long key = keyHolder.getKey().longValue();
        item.setId(key);
        return item;
    }

    @Override
    public void update(Long itemId, ItemUpdateDto updateParam){ //기존 item 데이터를 업데이트
        String sql = "update item set item_name =?, price=?, quantity=? where id=?";
        template.update(sql,
                updateParam.getItemName(),
                updateParam.getPrice(),
                updateParam.getQuantity(),
                itemId);
    }

    @Override
    public Optional<Item> findById(Long id){//특정 id 값을 가진 item을 조회
        String sql = "select id, item_name, price, quantity from item where id = ?";
        try{//JdbcTemplate.queryForObject : 쿼리 결과가 단일 행일 때 사용, RowMapper 를 사용 원하는 형태의 결과 값 반환
            Item item = template.queryForObject(sql, itemRowMapper(), id);
            return Optional.of(item);
        } catch (EmptyResultDataAccessException e){ //데이터가 없는 경우 EmptyResultDataAccessException 예외 처리
            return Optional.empty();
        }
    }

    @Override
    public List<Item> findAll(ItemSearchCond cond){ //조건에 따라 데이터 목록 조회
        String itemName = cond.getItemName();
        Integer maxPrice = cond.getMaxPrice();

        String sql = "select id, item_name, price, quantity from item";
        //동적 쿼리
        if(StringUtils.hasText(itemName) || maxPrice != null){
            sql += "where";
        }

        boolean andFlag = false;
        List<Object> param = new ArrayList<>();
        if(StringUtils.hasText(itemName)){
            sql += " item_name like concat('%', ? , '%')";
            param.add(itemName);
            andFlag = true;
        }

        if(maxPrice != null){
            if(andFlag){
                sql += " and";
            }
            sql += " price <= ?";
            param.add(maxPrice);
        }

        log.info("sql={}", sql);
        return template.query(sql, itemRowMapper(), param.toArray());
    }

    private RowMapper<Item> itemRowMapper(){ // itemRowMapper : 데이터 베이스 결과를 Item 객체로 매핑
        return (rs, rowNum) -> {
            Item item = new Item(); //디비 결과를 Item 객체로 반환
            item.setId(rs.getLong("id"));
            item.setItemName(rs.getString("item_name"));
            item.setPrice(rs.getInt("price"));
            item.setQuantity(rs.getInt("quantity"));
            return item;
        };
    }
}

this.template = new JdbcTemplate(dataSource)

• JdbcTemplate은 데이터소스(dataSource)가 필요
• JdbcTemplateItemRepositoryV1() 생성자를 보면 dataSource 의존 관계 주입 받고 생성자 내부에서 JdbcTemplate 생성
• JdbcTemplate을 스프링 빈으로 직접 등록하고 주입받아도 됨

이름 지정 바인딩

• JdbcTemplate는 기본으로 사용하면 파라미터를 순서대로 바인딩 함, 실수로 코드의 순서가 바뀌면 큰 오류 발생, 이런 문제 해결책
• NamedParameterJdbcTemplate 라는 이름 지정해서 파라미터 바인딩 하는 기능 제공

package hello.itemservice.repository.jdbctemplate;

import hello.itemservice.domain.Item;
import hello.itemservice.repository.ItemRepository;
import hello.itemservice.repository.ItemSearchCond;
import hello.itemservice.repository.ItemUpdateDto;
import lombok.extern.slf4j.Slf4j;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;
import javax.sql.DataSource;
import java.util.List;
import java.util.Map;
import java.util.Optional;

/**
 * NamedParameterJdbcTemplate
 * SqlParameterSource
 * - BeanPropertySqlParameterSource //sql 파라미터를 전달하는 사용 객체
 * - MapSqlParameterSource //sql 파라미터를 전달하는 사용 객체
 * Map
 *
 * BeanPropertyRowMapper
 *
 */
@Slf4j
@Repository
public class JdbcTemplateItemRepositoryV2 implements ItemRepository {
    private final NamedParameterJdbcTemplate template;
    public JdbcTemplateItemRepositoryV2(DataSource dataSource) {
        this.template = new NamedParameterJdbcTemplate(dataSource);
    }

    @Override //저장
    public Item save(Item item) { //item 객체의 필드 이름을 sql 파라미터로 매핑
        String sql = "insert into item (item_name, price, quantity) " +
                "values (:itemName, :price, :quantity)"; // ? 대신  파라미터로 값을 받음
        SqlParameterSource param = new BeanPropertySqlParameterSource(item);
        KeyHolder keyHolder = new GeneratedKeyHolder(); //자동생성된 키 저장
        template.update(sql, param, keyHolder); //sql 실행 및 키 반환
        Long key = keyHolder.getKey().longValue(); 
        item.setId(key);
        return item;
    }

    @Override //업데이트
    public void update(Long itemId, ItemUpdateDto updateParam) {
        String sql = "update item " +
                "set item_name=:itemName, price=:price, quantity=:quantity " +
                "where id=:id";

        SqlParameterSource param = new MapSqlParameterSource() //개별 파라미터를 SQL에 매핑
                .addValue("itemName", updateParam.getItemName())
                .addValue("price", updateParam.getPrice())
                .addValue("quantity", updateParam.getQuantity())
                .addValue("id", itemId); //이 부분이 별도로 필요하다.
        template.update(sql, param); //SQL 실행
    }

    @Override //특정 ID로 아이템 조회
    public Optional<Item> findById(Long id) {
        String sql = "select id, item_name, price, quantity from item where id = :id";
        try {
            Map<String, Object> param = Map.of("id", id);
            Item item = template.queryForObject(sql, param, itemRowMapper()); //결과 데이터를 item 객체로 매핑
            return Optional.of(item);
        } catch (EmptyResultDataAccessException e) {
            return Optional.empty();
        }
    }

    @Override //조건에 맞는 모든 아이템 조회
    public List<Item> findAll(ItemSearchCond cond) {
        Integer maxPrice = cond.getMaxPrice();
        String itemName = cond.getItemName();
      /**
       * BeanPropertySqlParameterSource 항상 사용할 수 있는 것은 아님, 예를 들어서 update() 에서 SQL:id를 바인딩 해야 하는데
       * update()에서 사용하는 ItemUpdateDto에 itemId가 없음, 따라서 BeanPropertySqlParameterSource 사용 안 하고
       * 대신 MapSqlParameterSource 사용
       */
        SqlParameterSource param = new BeanPropertySqlParameterSource(cond); 
        String sql = "select id, item_name, price, quantity from item";
        //동적 쿼리
        if (StringUtils.hasText(itemName) || maxPrice != null) {
            sql += " where";
        }
        boolean andFlag = false;
        if (StringUtils.hasText(itemName)) { //itemName이 null이거나 빈 문자열인지 확인
            sql += " item_name like concat('%',:itemName,'%')";
            andFlag = true;
        }
        if (maxPrice != null) {
            if (andFlag) {
                sql += " and";
            }
            sql += " price <= :maxPrice";
        }
        log.info("sql={}", sql);
        return template.query(sql, param, itemRowMapper());
    }

    private RowMapper<Item> itemRowMapper() {
        return BeanPropertyRowMapper.newInstance(Item.class); //camel 변환 지원
    }
}

• NamedParameterJdbcTemplate : 이름 기반 파라미터로 코드 가독성 및 유지 보수성 향상
• BeanPropertySqlParameterSource : 객체와 SQL 파라미터 자동 매핑, 항상 사용할 수 있는 것은 아님
• 동적 쿼리 생성 : 조건에 따라 SQL 유연하게 생성
• BeanPropertyRowMapper : 간편하게 결과 데이터 객체 매핑
• Exception Handling : 결과가 없는 경우 예외 처리 Optional 반환

JdbcTemplate - SimpleJdbcInsert

• Jdbctemplate은 INSERT SQL을 직접 작성하지 않아도 되도록 SimpleJdbcInsert 라는 편리한 기능 제공

package hello.itemservice.repository.jdbctemplate;

import hello.itemservice.domain.Item;
import hello.itemservice.repository.ItemRepository;
import hello.itemservice.repository.ItemSearchCond;
import hello.itemservice.repository.ItemUpdateDto;
import lombok.extern.slf4j.Slf4j;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import javax.sql.DataSource;
import java.util.List;
import java.util.Map;
import java.util.Optional;

/**
 * SimpleJdbcInsert
 * */

@Slf4j
@Repository
public class JdbcTemplateItemRepositoryV3 implements ItemRepository {

    private final NamedParameterJdbcTemplate template;
    private final SimpleJdbcInsert jdbcInsert; //자동화된 API를 사용 INSERT 작업 수행

    public JdbcTemplateItemRepositoryV3(DataSource dataSource){
        this.template = new NamedParameterJdbcTemplate(dataSource);
        this.jdbcInsert = new SimpleJdbcInsert(dataSource)
                .withTableName("Item") //테이블 이름 설정
                .usingGeneratedKeyColumns("id"); //자동 생성 키 컬럼 지정
    }

    @Override //저장
    public Item save(Item item){ 
        SqlParameterSource param = new BeanPropertySqlParameterSource(item); //객체 데이터를 SQL 파라미터 변환
        Number key = jdbcInsert.executeAndReturnKey(param);
        item.setId(key.longValue());
        return item;
    }

    @Override //수정
    public void update(Long itemId, ItemUpdateDto updateParam){
        String sql = "update item" +
                "set item_name=:itemName, price=:price, quantity=:quantity" +
                "where id=:id";

        SqlParameterSource param = new MapSqlParameterSource() //SQL 파라미터를 key-value 형태 매핑
                .addValue("itemName", updateParam.getItemName())
                .addValue("price", updateParam.getPrice())
                .addValue("quantity", updateParam.getQuantity())
                .addValue("id", itemId);
        template.update(sql, param);
    }

    @Override //특정 ID 가진 아이템 데이터 조회
    public Optional<Item> findById(Long id){
        String sql = "select id, item_name, price, quantity from item where id = :id";
        try{
            Map<String, Object> param = Map.of("id", id);
            Item item = template.queryForObject(sql, param, itemRowMapper());
            return Optional.of(item);
        } catch(EmptyResultDataAccessException e){
            return Optional.empty();
        }
    }

    @Override //조건에 맞는 아이템 조회
    public List<Item> findAll(ItemSearchCond cond){

        Integer maxPrice = cond.getMaxPrice();
        String itemName = cond.getItemName();

        SqlParameterSource param = new BeanPropertySqlParameterSource(cond);

        String sql = "select id, item_name, price quantity from item";

        //동적 쿼리
        if(StringUtils.hasText(itemName) || maxPrice != null){
            sql += "where";
        }

        boolean andFlag = false;
        if(StringUtils.hasText(itemName)){
            sql += "item_name like concat('%', :itemName, '%')";
            andFlag = true;
        }

        if(maxPrice != null){
            if(andFlag){
                sql += "and";
            }
            sql += " price <= :maxPrice";
        }

        log.info("sql={}", sql);
        return template.query(sql, param, itemRowMapper()); //SQL 실행 수 결과를 itemRowMapper 매핑 리스트 반환
    }

    private RowMapper<Item> itemRowMapper(){
        return BeanPropertyRowMapper.newInstance(Item.class);
    }
}

• SimpleJdbcInsert : 직접 SQL 쿼리를 작성할 필요 없이 데이터 삽입 간소화, 자동 생성된 키를 간단히 반환받아 처리 가능
• NamedParameterJdbcTemplate : 이름 기반 SQL 파라미터 사용 가독성 및 유지보수성 향상, 매핑 오류 줄이고 코드 명확성 높임
• this.jdbcInsert = new SimpleJdbcInsert(dataSource) : 생성자를 보면 의존관계 주입은 dataSource 받고 내부에서 SimpleJdbcInsert
  생성해 가짐, 스프링에서는 JdbcTemplate 관련 기능 사용할 때 관례상 많이 씀

JdbcTemplate 기능 정리

• JdbcTemplate : 순서 기반 파라미터 바인딩 지원
• NamedParameterJdbcTemplate : 이름 기반 파라미터 바인딩 지원
• SimpleJdbcInsert : INSERT SQL을 편리하게 사용
• SimpleJdbcCall : 스토어드 프로시저를 편리하게 호출

/**

/**
* 단건 조회-숫자 조회
* 하나의 로우를 조회할 때는 queryForObject() 사용, 단순 데이터 하나라면 타입을 Integer.class, String class 지정
* /
int rowCount = jdbcTemplate.queryForObject("select count() from t_actor",
Integer.class);

 /**
  * 단건 조회-숫자 조회, 숫자 하나와 파라미터 바인딩
  * */
 int countOfActorsNamedJoe = jdbcTemplate.queryForObject(
    "select count(*) from t_actor where first_name = ?", Integer.class, "Joe");

 /**
  * 단건 조회-문자 조회, 문자 하나와 파라미터 바인딩
  * */
 String lastName = jdbcTemplate.queryForObject(
     "select last_name from t_actor where id = ?",
     String.class, 1212L);

 /**
  * 단건 조회-객체 조회, 결과 객체 매핑 위해 RowMapper 사용, 람다 사용
  * */
 Actor actor = jdbcTemplate.queryForObject(
         "select first_name, last_name from t_actor where id = ?",
         (resultSet, rowNum) -> {
             Actor newActor = new Actor();
             newActor.setFirstName(resultSet.getString("first_name"));
             newActor.setLastName(resultSet.getString("last_name"));
             return newActor;
         }, 1212L);

 /**
  * 목록 조회 - 객체
  * 여러 로우를 조회할 때 query()사용하면 됨, 결과 리스트 반환
  * 결과를 객체로 매핑해야 하므로 RowMapper 사용해야 함, 여기서 람다 사용
  * */
 List<Actor> actors = jdbcTemplate.query(
         "select first_name, last_name from t_actor",
         (resultSet, rowNum) -> {
             Actor actor = new Actor();
             actor.setFirstName(resultSet.getStrgin("first_name"));
             actor.setLastName(resultSet.getStrgin("last_name"));
             return actor;
         }
 );

     /**
      * 목록 조회 - 객체
      * 여러 로우를 조회할 때 query() 사용하면 됨, 결과 리스트 반환
      * 여기서 RowMapper 분리, 이렇게 하면 여러 곳에서 재사용 가능
      * */
     public List<Actor> findAllActors(){
         return this.jdbcTemplate.query("select first_name, last_name from t_actor", actorRowMapper);
     }
     
     /**
      * 변경 (INSERT, UPDATE, DELETE)
      * 데이터를 변경할 때는 jdbcTemplate.update() 사용하면 됨
      * 참고로 int 반환 값을 반환하는데, SQL 실행 결과에 영향받은 로우 수 반환
      * */
     
     //등록
     jdbcTemplate.update(
             "insert into t_actor (first_name, last_name) vlaues (?, ?)",
             "Leonor", "Watling");
     
     //수정
     jdbcTemplate.update(
             "update t_actor set last_name = ? where id = ?",
             "Banjo", 5276L);
     
     //삭제
    jdbcTemplate.update(
            "delete from t_actor where id =?",
            Long.valueOf(actorId));
}

• 임의의 SQL을 실행할 때는 execute()를 사용, 테이블을 생성하는 DDL에 사용할 수도 있음
• ex) jdbcTemplate.execute("create table mytable (id integer, name varchar(100))");
• 실무에서 가장 간단하고 실용적인 방법으로 SQL 사용하려면 JdbcTemplate 사용하면 됨
• JPA와 같은 ORM 기술을 사용하면 동시에 SQL을 직접 작성해야 할 때가 있는데, 그때도 JdbcTemplate 함께 사용
• 최대 단점은 동적 쿼리 문제 해결 못 함 + 자바 코드로 작성해 SQL 라인이 코드를 넘어갈 때 마다 문자 더하기 해줘야 함

번외

• select item_name 의 경우 setItem_name()이라는 메서드가 없어 힘듬, 이런 경우 select item_name as itemName 카멜 표기법 쓰면 됨