스프링 JdbcTemplate 소개

SQL을 직접 사용하는 경우에 JdbcTemplate은 JDBC를 매우 편리하게 사용할 수 있게 도와준다.

---

장점

• 설정의 편리함
  • spring-jdbc 라이브러리에 포함
• 반복 문제 해결
  • 템플릿 콜백 패턴 사용

---

단점

• 동적 SQL 해결이 어려움

---

save

• INSERT, UPDATE, DELETE SQL에는 template.update() 메서드를 사용한다.
• 데이터 저장시 PK 생성시에 identity ( auto increment ) 방식을 사용한다. 따라서 데이터베이스가 PK인 ID를 대신 생성해준다.
• 데이터베이스에 INSERT가 완료되어야 생성된 PK ID 값을 확인할 수 있다.
• KeyHolder 와 connection.preparedStatement 을 사용해서 id를 지정해주면 INSERT 쿼리 실행 이후에 데이터베이스에서 생성된 ID 값을 조회할 수 있다.
• JdbcTemplate 이 제공하는 SimpleJdbcInsert라는 훨씬 편리한 기능도 제공한다.

---

findById

• template.queryForObject : 결과 로우가 하나일 때 사용한다.
• RowMapper 는 데이터베이스의 반환 결과인 ResultSet 을 객체로 변환한다.
• EmptyResultDataAccessException : 결과가 없을때 예외
• IncorrectResultSizeDataAccessException : 결과가 둘 이상일때 예외

---

findAll

• template.query : 결과가 하나 이상일 때 사용한다.
• 결과가 없으면 빈 컬렉션 반환

---

itemRowMapper

• 데이터베이스 조회 결과를 객체로 변환할 때 사용한다.
• JdbcTemplate 이 루프를 돌려주고, 개발자는 RowMapper 를 구현해서 내부 코드를 채운다.

while(resultSet이 끝날 때까지){
	rowMapper(rs, rowNum)
}

---

동적 쿼리

사용자가 검색하는 값에 따라서 실행하는 SQL이 동적으로 달라져야 한다.

검색 조건이 없음
select * from item

itemName으로 검색
select * from item where item_name like concat('%',?,'%')

maxPrice로 검색
select * from item where price <= ?

itemName, maxPrice 둘 다 검색
select * from item where item_name like concat('%',?,'%') and price <= ?

• 결과적으로 4가지 상황에 따른 SQL을 동적으로 생성해야 한다.
• 어떤 경우에 where를 앞에 넣고, 어떤 경우에 and를 넣어야 하는지 등 모두 계산해야 한다.
• 각 상황에 맞추어 파라미터도 생성해야 한다.
• MyBatis의 가장 큰 장점이 SQL을 직접 사용할 때 동적 쿼리를 쉽게 작성할 수 있다는 점이다.

---

이름 지정 파라미터 - 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
 */
@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) {
        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);

        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()
                .addValue("itemName", updateParam.getItemName())
                .addValue("price", updateParam.getPrice())
                .addValue("quantity", updateParam.getQuantity())
                .addValue("id", itemId);
        template.update(sql, param);
    }

    @Override
    public Optional<Item> findById(Long id) {
        String sql = "select * 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) {
        String itemName = cond.getItemName();
        Integer maxPrice = cond.getMaxPrice();

        SqlParameterSource param = new BeanPropertySqlParameterSource(cond);

        String sql = "select * 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());
    }

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

• NamedParameterJdbcTemplate 도 내부에 dataSource 가 필요하다.
• SQL에서 ':파라미터이름' 을 받는 것을 확인할 수 있다.
• 데이터베이스가 생성해주는 키를 매우 쉽게 조회하는 기능도 제공해준다.
• 파라미터를 전달하려면 Map 처럼 key, value 데이터 구조를 만들어서 전달해야 한다.
  • key -> ':파라미터이름' 으로 지정한 파라미터의 이름
  • value -> 해당 파라미터의 값
• 이렇게 만든 파라미터를 전달한다.
  • template.update(sql, param, keyHolder);
• 이름 지정 바인딩에서 자주 사용하는 파라미터 종류 (3가지)
  • Map
  • SqlParameterSource
    • MapSqlParameterSource
    • BeanPropertyParameterSource

---

Map

Map<String, Object> param = Map.of("id", id);
Item item = template.queryForObject(sql, param, itemRowMapper());

---

MapSqlParameterSource

• Map과 유사하지만, SQL 타입을 지정할 수 있는 등 SQL 에 조금 더 특화된 기능을 제공한다.
• SqlParameterSource Interface 의 구현체이다.
• 메서드 체인을 통한 편리한 사용법을 제공한다.

SqlParameterSource param = new MapSqlParameterSource()
 .addValue("itemName", updateParam.getItemName())
    .addValue("price", updateParam.getPrice())
    .addValue("quantity", updateParam.getQuantity())
    .addValue("id", itemId);
template.update(sql, param);

---

BeanPropertySqlParameterSource

• Java Bean Property 규약을 통해서 자동으로 파라미터 객체를 생성한다.
  • getItemName()
    • key -> itemName
    • value -> 상품명 값
  • getPrice()
    • key -> price
    • value -> 가격 값
• SqlParameterSource Interface 의 구현체이다.
• 많은 것 자동화해주만, 항상 사용할 수 있는 것은 아니다.
• update() 메소드에서는 SQL에 ':id' 를 바인딩 해야하는데, ItemUpdateDto에는 itemId가 없다. 따라서 MapSqlParameterSource를 사용했다.

SqlParameterSource param = new BeanPropertySqlParameterSource(item);
KeyHolder keyHolder = new GeneratedKeyHolder();
template.update(sql, param, keyHolder);

BeanPropertySqlParameterSource param = BeanPropertySqlParameterSource {class=class hello.itemservice.domain.Item, id=6 (type:BIGINT), itemName=te (type:VARCHAR), price=7890 (type:INTEGER), quantity=11 (type:INTEGER)}

---

BeanPropertyRowMapper

ResultSet의 결과를 받아서 Java Bean 규약에 맞추어 데이터를 변환한다. ( 실제로는 reflection 같은 기능을 사용 )

• 데이터베이스에서 조회한 결과 이름을 기반으로 자바빈 프로퍼티 규약에 맞춘 메서드를 호출한다.
• 별칭 ( 관례의 불일치 )
  • select member_name as username
  • snake_case는 자동으로 해결된다.
  • 컬럼 이름과 객체 이름이 완전히 다른 경우에 조회 SQL에서 별칭을 사용한다.

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

---

SimpleJdbcInsert

INSERT SQL 을 직접 작성하지 않아도 되도록 하는 기능

• jdbcInsert.executeAndReturnKey(param) 을 사용해서 INSERT SQL 을 실행하고, 생성된 키 값도 편리하게 조회할 수 있다.

SqlParameterSource param = new BeanPropertySqlParameterSource(item);

Number key = jdbcInsert.executeAndReturnKey(param);
item.setId(key.longValue());
log.info("BeanPropertySqlParameterSource param = {}", param);
return item;

---

JdbcTemplate 기능 정리

• JdbcTemplate
  • 순서 기반 파라미터 바인딩을 지원한다. ( ? )
• NamedParameterJdbcTemplate
  • 이름 기반 파라미터 바인딩을 지원한다. ( 권장 , :id )
• SimpleJdbcInsert
  • INSERT SQL 을 편리하게 사용할 수 있다. ( jdbcInsert.executeAndReturnKey(param) )
• SimpleJdbcCall
  • Stored Procedure 를 편리하게 호출할 수 있다.

---

JdbcTemplate 사용법 정리

• 조회
  • 단건 조회 - 숫자 조회 (조회 대상이 단순 데이터 하나)
    • 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);
  • 단건 조회 - 객체 조회

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

• 목록 조회 - 객체 ( 결과를 리스트로 반환 )

List<Actor> actors = jdbcTemplate.query(
      "select first_name, last_name from t_actor",
      (resultSet, rowNum) -> {
          Actor actor = new Actor();
          actor.setFirstName(resultSet.getString("first_name"));
          actor.setLastName(resultSet.getString("last_name"));
          return actor;
});

 public List<Actor> findAllActors() {
     return this.jdbcTemplate.query("select first_name, last_name from t_actor", actorRowMapper);
 }
 private final RowMapper<Actor> actorRowMapper = (resultSet, rowNum) -> {
     Actor actor = new Actor();
     actor.setFirstName(resultSet.getString("first_name"));
     actor.setLastName(resultSet.getString("last_name"));
     return actor;
 };

• 변경 ( INSERT, UPDATE, DELETE )
  • jdbcTemplate.update(sql, param)
• 기타 기능 ( 테이블을 생성하는 DDL 등 )
  • jdbcTemplate.execute("create table mytable (id integer, name varchar(100))");
• Stored Procedure 호출
  • jdbcTemplate.update("call SUPPORT.REFRESH_ACTORS_SUMMARY(?)", Long.valueOf(unionId));