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

[YuDeokRin]

5️⃣ JdbcTemplate - 이름 지정 파라미터 1

✅순서대로 바인딩

• JdbcTemplate를 기본으로 사용하면 파리머터를 순서대로 바인딩 된다.

String sql = "update item set item_name = ? , price = ?, quantity = ?, where id = ?";
template.update(sql, itemName, price, quantity, itemId);

• 여기서는 itemName, price, quantity가 SQL에 있는 ?에 순서대로 바인딩 된다. 따라서 순서만 잘 지키면 된다.

하지만…. 문제점이 있다. → 변경시점에서 발생한다.

• 예를들어) SQL코드의 순서를 변경했다고 가정했을 때
  • price와 quantity의 순서를 변경

String sql = "update item set item_name = ? , quantity = ?, price = ?, where id = ?";
template.update(sql, itemName, price, quantity, itemId);

• 위와 같이 변경이 된다면 순서로 데이터가 바인딩 된다.

item_name = itemName, quantity=price, price=quantity

▶️ 결과적으로..

• price와 quantity가 바뀌는 매우 심각한 문제가 발생한다.
• 실무에서는 파라미터가 매우 많은 경우가 많다. 필드를 추가하거나 수정하면서 이런 문제가 충분히 발생할 수 있다.

개발을 할 때는 코드를 몇줄 줄이는 편리함도 중요하지만, 모호함을 제거해서 코드를 명확하게 마드는 것이 유지보수 관점에서 매우 중요하다.

✅ 이름 지정 바인딩

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

/**
 * NamedParameterJdbcTemplate
 * SqlParamterSource
 * - BeanPropertySqlParameterSource
 * - MapSqlParameterSource
 * Map
 *
 * BeanPropertyRowMapper
 */
@Slf4j
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 변환 지원
    }
}

✅ 위 코드 설명

• JdbcTemplateItemRepositoryV2는 ItemRepository 인터페이스를 구현했다.
• this.template = new NamedParameterJdbcTemplate(dataSource)
  • NamedParameterJdbcTemplate도 내부에 dataSource 가 필요하다.
  • JdbcTemplateItemRepositoryV2 생성자를 보면 의존관계 주입은 dataSource 를 받고 내부에서
  • NamedParameterJdbcTemplate 을 생성해서 가지고 있다. 스프링에서는 JdbcTemplate 관련 기능을 사용할 때 관례상 이 방법을 많이 사용한다.
  • 물론 NamedParameterJdbcTemplate 을 스프링 빈으로 직접 등록하고 주입받아도 된다.

✅ sava()

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

 insert into item (item_name, price, quantity) "+" values (:itemName, :price, :quantity) "

6️⃣ JdbcTemplate - 이름 지정 파라미터 2

✅ 이름 지정 파라미터

• 파라미터를 전달하려면 Map처럼 key, value 데이터 구조를 만들어서 전달해야 한다.

• 여기서 key는 :파라미터이름 으로 지정한, 파라미터의 이름이고, value는 해당 파라미터의 값이 된다.

• 다음 코드를 보면 이렇게 만든 파라미터(param)를 전달하는 것을 확인할 수 있다.

template.update(sql, param, keyHolder);

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

1️⃣ Map

• 단순히 Map을 사용한다.

• findById() 코드에서 확인

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

2️⃣MapSqlParameterSource

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

• SqlParameterSource 인터페이스의 구현체이다.

• MapSqlParameterSource는 메서드 체인을 통해 편리한 사용법도 제공한다.

• update() 코드에서 확인 가능

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

• 자바빈 프로퍼티 규약을 통해서 자동으로 파라미터 객체를 생성한다.

• 예)

  • getXxx() → xxx
  • getItemName() → itemName

• 예) getItemName(), getPrice()가 있으면 다음과 같은 데이터를 자동으로 만들어낸다.

  • key=itemName, value = 상품명 값
  • key=price, value=가격 값

• SqlParameterSource 인터페이스의 구현체이다.

• save(), findAll() 코드에서 확인할 수 있다.

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

• BeanPropertySqlParameterSource가 많은 것을 자동화 해주기 때문에 가장 좋아보이지만, BeanPropertySqlParameterSource를 항상 사용할 수 있는 것은 아니다.
• 예를 들어서 update() 에서는 SQL에 :id를 바인딩 해야 하는데, update()에서 사용하는 ItemUpdateDto에는 ItemId가 없다. 따라서 BeanPropertySqlParameterSource를 사용할 수 없고, 대신에 MapSqlParameterSource를 사용했다.

✅ BeanPropertyRowMapper

• JdbcTemplateItemRepositoryV1 - 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;
		}; 
}

• JdbcTemplateItemRepositoryV2 - itemRowMapper()

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

✅ 별칭

• DB와 같은 경우 → 스네이크 케이스 표기법
• 자바 → 카멜 케이스 표기법

7️⃣ JdbcTemplate - 이름 지정 파라미터 3

• JdbcTemplateV2Config

@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

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

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

	@Bean
	@Profile("local")
	public TestDataInit testDataInit(ItemRepository itemRepository) {
		return new TestDataInit(itemRepository);
	}

}

8️⃣ JdbcTemplate - SimpleJdbcInsert

/**
 * SimpleJdbcInsert
 */
@Slf4j
public class JdbcTemplateItemRepositoryV3 implements ItemRepository {

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

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

✅ 코드 설명

• JdbcTemplateItemRepositoryV3은 ItemRepository인터페이스를 구현했다.

• this.jdbcInsert = new SimpleJdbcInsert(dataSource): 생성자를 보면 의존관계 주입은 dataSource를 받고 내부에서 SimpleJdbcInsert을 생성해서 가지고 있다. 스프링에서는JdbcTemplate 관련 기능을 사용할 때 관례상 이 방법을 많이 사용한다.

• 물론 SimpleJdbcInsert 을 스프링 빈으로 직접 등록하고 주입받아도 된다.

• SimpleJdbcInsert

this.jdbcInsert = new SimpleJdbcInsert(dataSource)
                 .withTableName("item")
.usingGeneratedKeyColumns("id");
// .usingColumns("item_name", "price", "quantity"); //생략 가능

• withTableName : 데이터를 저장할 테이블 명을 지정한다.

• usingGeneratedKeyColumns : key 를 생성하는 PK 컬럼 명을 지정한다.

• usingColumns : INSERT SQL에 사용할 컬럼을 지정한다. 특정 값만 저장하고 싶을 때 사용한다. 생략할 수 있다.

• SimpleJdbcInsert는 생성 시점에 데이터베이스 테이블의 메타 데이터를 조회한다. 따라서 어떤 컬럼이 있는지 확인할 수 있으므로 usingColumns을 생략할 수 있다.

• 애플리케이션을 실행해보면 SimpleJdbcInsert이 어떤 INSERT SQL을 만들어서 사용하는지 로그로 확인할 수 있다.

DEBUG 39424 --- [           main] o.s.jdbc.core.simple.SimpleJdbcInsert    :
Compiled insert object: insert string is [INSERT INTO item (ITEM_NAME, PRICE,
QUANTITY) VALUES(?, ?, ?)]

✅ save()

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

public Item save(Item item) {
     SqlParameterSource param = new BeanPropertySqlParameterSource(item);
     Number key = jdbcInsert.executeAndReturnKey(param);
     item.setId(key.longValue());
     return item;
}

• JdbcTemplateV3Config

package hello.itemservice.config;

import hello.itemservice.repository.ItemRepository;
import hello.itemservice.repository.jdbctemplate.JdbcTemplateItemRepositoryV3;
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 JdbcTemplateV3Config {

    private final DataSource dataSource;
    
    @Bean
    public ItemService itemService() {
        return new ItemServiceV1(itemRepository());
    }
    
    @Bean
    public ItemRepository itemRepository() {
        return new JdbcTemplateItemRepositoryV3(dataSource);
    }
}

• ItemServiceApplication - 변경

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

• JdbcTemplateV3Config.class를 설정으로 사용하도록 변경되었다.
  • @import(JdbcTemplateV2Config.class) ``@Import(JdbcTemplateV3Config.class)`

9️⃣ JdbcTemplate 기능 정리

✅ 주요 기능

• JdbcTemplate이 제공하는 주요 기능은 다음과 같다.

• JdbcTemplate

  • 순서 기반 파라미터 바인딩을 지원한다.

• NamedParameterJdbcTemplate

• 이름 기반 파라미터 바인딩을 지원한다. (권장)

• SimpleJdbcInsert

  • INSERT SQL을 편리하게 사용할 수 있다.

• SimpleJdbcCall

  • 스토어드 프로시저를 편리하게 호출할 수 있다.

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

• 목록 조회 - 객체

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;
 };
 
 
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) values (?, ?)",
         "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에 사용할 수 있다.

• DDL

jdbcTemplate.execute("create table mytable (id integer, name varchar(100))");

• 스토어드 프로시저 호출

jdbcTemplate.update(
         "call SUPPORT.REFRESH_ACTORS_SUMMARY(?)",
         Long.valueOf(unionId));