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

[newh08]

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

이러면 데이터 바인딩 될 때 price 에 quantity 가 바인딩 됨.

실무에서 파라미터가 10개, 20개 넘어가고 또 필드 추가되거나 수정하면서 이런 문제 충분히 발생할 수 있음.
버그 중 가장 고치기 힘든 버그는 데이터베이스에 데이터가 잘못 들어가는 버그. 코드만 고치는게 아니라 데이터를 복구해야 하기 때문에 리소스가 많이 필요.

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

JdbcTemplate 는 이를 보완하기 위해 NamedParameterJdbcTemplate 라는 이름을 지정해서 파라미터를 바인딩 하는 기능을 제공한다.
(파라미터 바인딩을 순서 기반이 아닌 이름 기반으로 함)
이를 사용한 리포지토리V2

/**
 * NamedParameterJdbcTemplate
 */
@Repository
public class JdbcTemplateItemRepositoryV2 implements ItemRepository {

    private final NamedParameterJdbcTemplate template;

    public JdbcTemplateItemRepositoryV2(DataSource dataSource) {
        this.template = new NamedParameterJdbcTemplate(dataSource); // NamedParameterJdbcTemplate 로 변경
    }

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

        SqlParameterSource param = new BeanPropertySqlParameterSource(item); // Item 객체 가지고 파라미터를 만듦. (객체 기반)

        KeyHolder keyHolder = new GeneratedKeyHolder(); // 아이디값 가져오기. JDBC 에선 이렇게 씀.
        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, Long> param = Map.of("id", id);
            Item item = template.queryForObject(sql, param, itemRowMapper());
            return Optional.of(item);
        } catch (EmptyResultDataAccessException e) {
            return Optional.empty();
        }
    }

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

    @Override
    public List<Item> findAll(ItemSearchCond cond) {
        String itemName = cond.getItemName();
        Integer maxPrice = cond.getMaxPrice();

        SqlParameterSource param = new BeanPropertySqlParameterSource(cond);

        String sql = "select id, item_name, price, quantity from item";
        // 동적 쿼리, param 에 이미 파라미터가 있고, param 에 있는 파라미터를 sql 에 집어넣음.
        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 <= :masPrice";
        }

        template.query(sql, param, itemRowMapper());
        return null;
    }
}

NamedParamterJdbcTemplate 에서 파라미터 맵핑에 사용 가능한 것

• SqlParameterSource
  • BeanPropertySqlParameterSource
  • MapSqlParameterSource
• Map

1. Repository 인터페이스 구현
2. template = new NamedParameterJdbcTemplate(dataSource) 로 변경.

save() 에서 sql 의 ? 대신에 :파라미터이름 을 받았다.

    String sql = "insert into item(item_name, price, quantity) "
            + "values (:itemName,:price,:quantity)";

추가로 NamedParameterJdbcTemplate 은 데이터베이스가 생성해주는 키를 매우 쉽게 조회하는 기능도 제공해준다.

JdbcTemplate - 이름 지정 파라미터 2

이름 지정 파라미터

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

이름 지정 파라미터에서 자주 사용하는 파라미터의 종류는 크게 3가지

• Map
• SqlParameterSource (인터페이스임)
  • BeanPropertySqlParameterSource (구현체임)
  • MapSqlParameterSource (구현체임)

1. Map
   단순히 Map 사용하면 된다.
   findById() 에서 확인 가능.

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

2. MapSqlParameterSource
   Map 과 유사한데, SQL 타입을 지정할 수 있는 등 SQL특화 기능 제공
   SqlParameterSource의 구현체
   메서드 체인으로 편리한 기능 제공
   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); // Item 객체 가지고 파라미터를 만듦. (객체 기반)
    KeyHolder keyHolder = new GeneratedKeyHolder(); // 아이디값 가져오기. JDBC 에선 이렇게 씀.
    template.update(sql, param, keyHolder);

가장 많은 자동화를 해줘서 제일 좋아보이지만, 항상 사용할 순 없음
update() 에서 id 가 필요한데 itemUpdateDto 엔 id 가 없음. 그래서 MapSqlParameterSource 사용함.

이전 코드 대비 하나 더 변경된 부분 itemRowMapper()

JdbcTemplateItemRepositoryV1

 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

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

데이터베이스 조회한 결과 이름 기반으로 자바빈 프로퍼티 규약에 맞춘 메서드를 호출함.

그런데 데이터베이스의 네이밍 규약은 자바와 다름
select item_name 의 경우 setItem_name() 이라는 메서드가 없음.
이런경우 조회sql 을 다음과 같이 고치면 됨
select item_name as itemName

별칭 as 를 사용해서 SQL 조회 결과의 이름을 변경하는 것이다. 실제로 이 방법은 자주 사용된다. 특히 데이터베이스 컬럼 이름과 객체 이름이 완전히 다를 때 문제를 해결할 수 있다. 예를 들어서 데이터베이스에는 member_name 이라 고 되어 있는데 객체에 username 이라고 되어 있다면 다음과 같이 해결할 수 있다.

관례의 불일치.
자바 객체는 카멜 표기법을 사용.
관계형 데이터베이스는 언더스코어를 사용하는 snake_case 표기법 사용함. (예전엔 디비에서 대소문자를 구분 안했음)
이 부분을 관례로 많이 사용하다 보니 BeanPropertyRowMapper 는 언더스코어 표기법을 카멜로 자동 변환해준다. 따라서 select item_name 으로 조회해도 setItemName() 에 문제 없이 값이 들어간다.

정리하면 snake_case 는 자동으로 해결되니 그냥 두면 되고, 컬럼 이름과 객체 이름이 완전히 다른 경우에는 조회 SQL에서 별칭을 사용하면 된다.

JdbcTemplate - 이름 지정 파라미터 3

지금까지 만든걸 구성하고 실행해보자.

컨피그 구성 변경

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

이거로 어플리케이션에서 사용하도록 변경

@Import(JdbcTemplateV2Config.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);
	}
}

V1 에선 파라미터 순서가 바뀌면 큰일나지만, V2 에선 상관 없어짐

JdbcTemplate - SimpleJdbcInsert

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

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

    private final NamedParameterJdbcTemplate template;
    private final SimpleJdbcInsert jdbcInsert;

    public JdbcTemplateItemRepositoryV3(DataSource dataSource) {
        this.template = new NamedParameterJdbcTemplate(dataSource); // NamedParameterJdbcTemplate 로 변경
        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;
    }

new SimpleJdbcInsert(dataSource) : 의존관계 주입은 dataSource를 받고, 내부에서 생성함. 관례상 이렇게 많이 사용한다.
테이블 명을 다 정해줘야 하기 때문에 직접 주입은 거의 안씀.

        this.jdbcInsert = new SimpleJdbcInsert(dataSource)
                .withTableName("item")
                .usingGeneratedKeyColumns("id")
                .usingColumns("item_name", "price", "quantity"); // 생략 가능 (디비의 메타데이터 읽어서 자동 생성 가능)
    }

• withTableName : 데이터를 저장할 테이블 명을 지정한다.
• usingGeneratedKeyColumns : key 를 생성하는 PK 컬럼 명을 지정한다.
• usingColumns : INSERT SQL에 사용할 컬럼을 지정한다. 특정 값만 저장하고 싶을 때 사용한다. 생략할 수 있다.

SimpleJdbcInsert 생성 시점에 데이터베이스 메타데이터 읽어 어떤 컬럼이 있는지 확인함.
save 에서만 적용 가능하고, 나머지 코드는 같음.

컨피그 변경해서 돌려보기

JdbcTemplate 기능 정리

JdbcTemplate : 순서 기반 파라미터 바인딩
NamedParameterJdbcTemplate : 이름 기반 파리미터 바인딩 (권장)
SimpleJdbcInsert : Insert SQL 을 간단히 사용 가능
SimpleJdbcCall : 스토어드 프로시저 편리하게 호출 가능

참고
스토어드 프로시저를 사용하기 위한 SimpleJdbcCall 에 대한 자세한 내용은 다음 스프링 공식 메뉴얼을 참고 하자.
https://docs.spring.io/spring-framework/docs/current/reference/html/data-access.html#jdbc- simple-jdbc-call-1

JdbcTemplate 사용법

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

조회

단건 조회 - 숫자 조회

 int rowCount = jdbcTemplate.queryForObject("select count(*) from t_actor", Integer.class);

하나의 로우 조회할 땐 queryForObject 사용. 조회 대상이 단순 데이터라면 타입을 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);

조회 결과를 객체로 맵핑해야 하므로 RowMapper 를 사용해야 한다.

public interface RowMapper<T> {
	@Nullable
	T mapRow(ResultSet rs, int rowNum) throws SQLException;
}

목록 조회 - 객체

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

여러 로우를 조회할땐 query() 사용.
마찬가지로 RowMapper 사용

RowMapper 분리

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

변경

변경할땐 jdbcTemplate.update() 사용하면 된다. int 를 반환하는데 영향받은 로우 수 이다.
등록

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