스프링 JdbcTemplate

1. JdbcTemplate 소개와 설정
2. JdbcTemplate 적용1 - 기본
3. JdbcTemplate 적용2 - 구성과 실행
4. JdbcTemplate - 이름 지정 파라미터
5. JdbcTemplate - SimpleJdbcInsert
6. JdbcTemplate 기능 정리

1. JdbcTemplate 소개와 설정

SQL을 직접 사용하는 경우에 스프링이 제공하는 JdbcTemplate은 아주 좋은 선택지다. JdbcTemplate
은 JDBC를 매우 편리하게 사용할 수 있게 도와준다.

장점

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

build.gradle 설정 추가

implementation 'org.springframework.boot:spring-boot-starter-jdbc'

2. JdbcTemplate 적용1 - 기본

JdbcTemplate을 사용해서 메모리에 저장하던 데이터를 데이터베이스에 저장해보자.

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;

@Slf4j
@Repository
public class JdbcTemplateItemRepositoryV1 implements ItemRepository {

    private final JdbcTemplate template;

    public JdbcTemplateItemRepositoryV1(DataSource dataSource) {
        this.template = new JdbcTemplate(dataSource);
    }

    @Override
    public Item save(Item item) {
        String sql = "insert into item(item_name, price, quantity) values (?,?,?)";

        KeyHolder keyHolder = new GeneratedKeyHolder();
        template.update(connection ->{
            // 자동 증가 키
            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) {
        String sql = "update item set item name=?, price=?, quantity=? from item where id = ?";
        template.update(sql,
                updateParam.getItemName(),
                updateParam.getPrice(),
                updateParam.getQuantity(),
                itemId);
    }

    @Override
    public Optional<Item> findById(Long id) {
        String sql = "select id, item_name, price, quantity from item where id = ?";

        try {
            Item item = template.queryForObject(sql, itemRowMapper(), id);
            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();

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

    private RowMapper<Item> itemRowMapper() {
        return ((rs, rowNum) -> {
            Item item = new Item();
            item.setId(rs.getLong("id"));
            item.setItemName(rs.getString("item_name"));
            item.setPrice(rs.getInt("price"));
            item.setQuantity(rs.getInt("quantity"));
            return item;
        });
    }
}

• JdbcTemplate은 데이터소스(dataSource)가 필요하다.
• JdbcTemplateItemRepositoryV1() 생성자를 보면 dataSource를 의존 관계 주입 받고 생성자내부에서 JdbcTemplate을 생성한다. 스프링에서는 JdbcTemplate을 사용할 때 관례상 이 방법을 많이 사용한다.

save()

• template.update(): 데이터를 변경할 때는 update()를 사용하면 된다.
  • 반환 값은 int인데, 영향 받은 로우 수를 반환한다.
• KeyHolder와 connection.prepareStatement(sql, new String[]{"id"})를 사용해서 id를 지정해주면 INSERT 쿼리 실행 이후에 데이터베이스에서 생성된 ID값을 조회할 수 있다.

update()

• template.update(): 데이터를 변경할 때는 update()를 사용하면 된다.
  • 반환 값은 해당 쿼리의 영향을 받은 로우 수 이다. 여기서는 where id=? 를 지정했기 때문에 영향 받은 로우수는 최대 1개이다.

findById()

• 데이터를 하나 조회
• template.queryForObject()
  • 결과 로우가 하나일 때 사용한다.
  • RowMapper는 데이터베이스의 반환 결과인 ResultSet을 객체로 변환한다.
  • 결과가 없으면 EmptyResultDataAccessException 예외가 발생한다.
  • 결과가 둘 이상이면 IncorrectResultSizeDataAccessException 예외가 발생한다.
• ItemRepository.findById() 인터페이스는 결과가 없을 때 Optional 을 반환해야 한다. 따라서 결과가 없으면 예외를 잡아서 Optional.empty를 대신 반환하면 된다.

findAll()

• 데이터를 리스트로 조회한다. 그리고 검색 조건으로 적절한 데이터를 찾는다.
• template.query()
  • 결과가 하나 이상일 때 사용한다.
  • RowMapper는 데이터베이스의 반환 결과인 ResultSet을 객체로 변환한다.

itemRowMapper()

• JDBC를 직접 사용할 때 ResultSet 를 사용했던 부분을 떠올리면 된다.
• 차이가 있다면 다음과 같이 JdbcTemplate이 다음과 같은 루프를 돌려주고, 개발자는 RowMapper를 구현해서 그 내부 코드만 채운다고 이해하면 된다.

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

동적 쿼리 부분

• 결과를 검색하는 findAll()에서 어려운 부분은 사용자가 검색하는 값에 따라서 실행하는 SQL이 동적으로 달려져야 한다는 점이다.
  

4가지 상황에 따른 조건이 있다.

1. 검색 조건이 없음

select id, item_name, price, quantity from item

2. 상품명(itemName)으로 검색

select id, item_name, price, quantity from item where item_name like concat('%',?,'%')

3. 최대 가격(maxPrice)으로 검색

select id, item_name, price, quantity from item where price <= ?

4. 상품명(itemName), 최대 가격(maxPrice) 둘다 검색

select id, item_name, price, quantity from item
where item_name like concat('%',?,'%') and price <= ?

이후에 설명할 MyBatis의 가장 큰 장점은 SQL을 직접 사용할 때 동적 쿼리를 쉽게 작성할 수 있다는 점이다.

3. JdbcTemplate 적용2 - 구성과 실행

JdbcTemplateV1Config

package hello.itemservice.config;

import hello.itemservice.repository.ItemRepository;
import hello.itemservice.repository.jdbctemplate.JdbcTemplateItemRepositoryV1;
import hello.itemservice.service.ItemService;
import hello.itemservice.service.ItemServiceV1;
import lombok.RequiredArgsConstructor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;

@Configuration
@RequiredArgsConstructor
public class JdbcTemplateV1Config {

    private final DataSource dataSource;

    @Bean
    public ItemService itemService() {
        return new ItemServiceV1(itemRepository());
    }
            
    @Bean
    public ItemRepository itemRepository() {
        return new JdbcTemplateItemRepositoryV1(dataSource);
    }
}

application.properties

spring.profiles.active=local
spring.datasource.url=jdbc:h2:tcp://localhost/~/test
spring.datasource.username=sa

ItemServiceApplication

// @Import(MemoryConfig.class)
@Import(JdbcTemplateV1Config.class)
@SpringBootApplication(scanBasePackages = "hello.itemservice.web")
public class ItemServiceApplication {

	public static void main(String[] args) {
		SpringApplication.run(ItemServiceApplication.class, args);
	}

실행 결과

• 등록 Kwon, Yong, ho

검색 과정에서 오류 발생

• 정확히는 모르겠지만 파라미터가 세팅이 안되고 있는 것 같아서 해당 소스를 점검했다.
• param.toArray() 소스를 안쓴 것을 확인했다. 리턴값이 없어서 오류가 난 것 같다.

JdbcTemplateItemRepositoryV1

        log.info("sql={}", sql);
        // 실수 한 코드
        // return template.query(sql, itemRowMapper());
        // 수정
        return template.query(sql, itemRowMapper(), param.toArray());
    }

검색 성공

4. JdbcTemplate - 이름 지정 파라미터

• JdbcTemplate을 기본으로 사용하면 파라미터를 순서대로 바인딩 한다.
• 누군가 다음과 같이 SQL 코드의 순서를 변경했다고 가정해보자. (price와 quantity의 순서를 변경했다.)
• 여기서 버그가 일어나면 상당히 찾기가 힘들다.
• 개발을 할 때는 코드를 몇줄 줄이는 편리함도 중요하지만, 모호함을 제거해서 코드를 명확하게 만드는 것이 유지보수 관점에서 매우 중요하다.

이름 지정 바인딩
JdbcTemplate은 이런 문제를 보완하기 위해 NamedParameterJdbcTemplate라는 이름을 지정해서 파라미터를 바인딩 하는 기능을 제공한다.

코드로 확인해보자.
JdbcTemplateItemRepositoryV2

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;

@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 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());
    }
    
    private RowMapper<Item> itemRowMapper() {
        return BeanPropertyRowMapper.newInstance(Item.class); //camel 변환 지원
    }
}

• NamedParameterJdbcTemplate도 내부에 dataSource가 필요하다.
• JdbcTemplateItemRepositoryV2 생성자를 보면 의존관계 주입은 dataSource를 받고 내부에서NamedParameterJdbcTemplate을 생성해서 가지고 있다. 스프링에서는 JdbcTemplate관련 기능을 사용할 때 관례상 이 방법을 많이 사용한다.

save()

• SQL에서 다음과 같이 ? 대신에 :파라미터이름 을 받는 것을 확인할 수 있다.

이름 지정 파라미터
파라미터를 전달하려면 Map처럼 key, value데이터 구조를 만들어서 전달해야 한다. 여기서 key는 :파리이터이름 으로 지정한, 파라미터의 이름이고, value 는 해당 파라미터의 값이 된다.

이름 지정 바인딩에서 자주 사용하는 파라미터의 종류는 크게 3가지가 있다.

• Map
• SqlParameterSource
  • MapSqlParameterSource
  • BeanPropertySqlParameterSource

1. Map
   findById()에서

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

2. MapSqlParameterSource

• Map과 유사한데, SQL 타입을 지정할 수 있는 등 SQL에 좀 더 특화된 기능을 제공한다.

        SqlParameterSource param = new MapSqlParameterSource()
                .addValue("itemName", updateParam.getItemName())
                .addValue("price", updateParam.getPrice())
                .addValue("quantity", updateParam.getQuantity())
                .addValue("id", itemId); //이 부분이 별도로 필요
        template.update(sql, param);

3. BeanPropertySqlParameterSource

• 자바빈 프로퍼티 규약을 통해서 자동으로 파라미터 객체를 생성한다.
• 예를 들어서 getItemName(), getPrice()가 있으면 다음과 같은 데이터를 자동으로 만들어낸다.
  • key=itemName, value=상품명 값
  • key=price, value=가격 값

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

BeanPropertyRowMapper

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

• BeanPropertyRowMapper는 ResultSet의 결과를 받아서 자바빈 규약에 맞추어 데이터를 변환한다. 예를 들어서 데이터베이스에서 조회한 결과가 select id, price 라고 하면 다음과 같은 코드를 작성해준다.

Item item = new Item();
item.setId(rs.getLong("id"));
item.setPrice(rs.getInt("price"));

관례의 불일치

• 자바 객체는 카멜(camelCase) 표기법을 사용한다. itemName 처럼 중간에 낙타 봉이 올라와 있는 표기법이다.
• 반면에 관계형 데이터베이스에서는 주로 언더스코어를 사용하는 snake_case표기법을 사용한다. item_name처럼 중간에 언더스코어를 사용하는 표기법이다.
• 이 부분을 관례로 많이 사용하다 보니 BeanPropertyRowMapper는 언더스코어 표기법을 카멜로 자동변환해준다.

JdbcTemplateV2Config

package hello.itemservice.config;

import hello.itemservice.repository.ItemRepository;
import hello.itemservice.repository.jdbctemplate.JdbcTemplateItemRepositoryV2;
import hello.itemservice.service.ItemService;
import hello.itemservice.service.ItemServiceV1;
import lombok.RequiredArgsConstructor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;

@Configuration
@RequiredArgsConstructor
public class JdbcTemplateV2Config {

    private final DataSource dataSource;

    @Bean
    public ItemService itemService() {
        return new ItemServiceV1(itemRepository());
    }

    @Bean
    public ItemRepository itemRepository() {
        return new JdbcTemplateItemRepositoryV2(dataSource);
    }
}

ItemServiceApplication - 변경

//@Import(MemoryConfig.class)
//@Import(JdbcTemplateV1Config.class)
@Import(JdbcTemplateV2Config.class)
@SpringBootApplication(scanBasePackages = "hello.itemservice.web")

실행 확인

잘 실행 되는 것을 확인

5. JdbcTemplate - SimpleJdbcInsert

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

JdbcTemplateItemRepositoryV3 일부

    private final NamedParameterJdbcTemplate template;
    private final SimpleJdbcInsert jdbcInsert;
    
    public JdbcTemplateItemRepositoryV3(DataSource dataSource) {
        this.template = new NamedParameterJdbcTemplate(dataSource);
        this.jdbcInsert = new SimpleJdbcInsert(dataSource)
                .withTableName("item")
                .usingGeneratedKeyColumns("id");
            //  .usingColumns("item_name", "price", "quantity"); //생략 가능
    }
    
    @Override
    public Item save(Item item) {
        SqlParameterSource param = new BeanPropertySqlParameterSource(item);
        Number key = jdbcInsert.executeAndReturnKey(param);
        item.setId(key.longValue());
        return item;
    }

• withTableName: 데이터를 저장할 테이블 명을 지정한다.
• usingGeneratedKeyColumns: key를 생성하는 PK 컬럼 명을 지정한다.
• usingColumns: INSERT SQL에 사용할 컬럼을 지정한다. 특정 값만 저장하고 싶을 때 사용한다. 생략할 수 있다.
• SimpleJdbcInsert는 생성 시점에 데이터베이스 테이블의 메타 데이터를 조회한다. 따라서 어떤 컬럼이 있는지 확인 할 수 있으므로 usingColumns을 생략할 수 있다. 만약 특정 컬럼만 지정해서 저장하고 싶다면 usingColumns을 사용하면 된다.

실행 결과

잘 실행되는 것을 확인 했다.

6. JdbcTemplate 기능 정리

주요기능

• JdbcTemplate
  • 순서 기반 파라미터 바인딩을 지원한다.
• NamedParameterJdbcTemplate
  • 이름 기반 파라미터 바인딩을 지원한다. (권장)
• SimpleJdbcInsert
  • INSERT SQL을 편리하게 사용할 수 있다.
• SimpleJdbcCall
  • 스토어드 프로시저를 편리하게 호출할 수 있다.

JdbcTemplate에 대한 사용법은 스프링 공식 메뉴얼에 자세히 소개되어 있다.

스프링 JdbcTemplate 사용 방법 공식 메뉴얼
https://docs.spring.io/spring-framework/docs/current/reference/html/dataaccess.html#jdbc-JdbcTemplate

조회, 변경(INSERT, UPDATE, DELETE) 등 위에 JdbcTemplateItemRepository들 에서 사용한 방법들이 정리 되어있다.

• 실무에서 가장 간단하고 실용적인 방법으로 SQL을 사용하려면 JdbcTemplate을 사용하면 된다.
• JPA와 같은 ORM 기술을 사용하면서 동시에 SQL을 직접 작성해야 할 때가 있는데, 그때도 JdbcTemplate을 함께 사용하면 된다.
• JdbcTemplate의 최대 단점이 있는데, 바로 동적 쿼리 문제를 해결하지 못한다는 점이다. 그리고 SQL을 자바 코드로 작성하기 때문에 SQL 라인이 코드를 넘어갈 때 마다 문자 더하기를 해주어야 하는단점도 있다.

동적 쿼리 문제를 해결하면서 동시에 SQL도 편리하게 작성할 수 있게 도와주는 기술이 바로 MyBatis이다.

참고
김영한: 스프링 DB 2편 - 데이터 접근 활용 기술(인프런)
Github - https://github.com/b2b2004/Spring_DB