[JdbcTemplate] JdbcTemplate 적용

SQL을 직접 사용하는 경우에 스프링이 제공하는 JdbcTemplate을 사용할 수 있습니다. 

 

JdbcTemplate 장점

• 설정의 편리함
  • JdbcTemplate은 spring-jdbc 라이브러리에 포함되어, 별도의 복잡한 설정 없이 바로 사용할 수 있습니다.
  • build.gradle 에 추가만 하면 사용 가능 

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

• 반복 문제 해결
  • JDBC를 직접 사용할 때 발생하는 반복적인 작업을 대신 처리해 주는 덕분에, 개발자는 SQL 작성, 전달 파라미터 정의, 응답 값 매핑만 하면 됨.
  • JdbcTemplate은 커넥션 획득, statement 준비 실행, 결과를 반복하도록 루프를 실행, 커넥션-statement-resultset 종료, 트랜잭션을 다루기 위한 커넥션 동기화, 예외 발생시 스프링 예외 변환기 실행 의 반복작업을 대신 처리해줍니다!

JdbcTemplate 단점

• 동적 SQL을 처리하기 어렵습니다.

JdbcTemplate 

Repository에 JdbcTemplate을 적용해보면서 어떻게 사용하는지 알아보겠습니다.

Database :  H2 
Table : Item ( id(big int by default as identity, PK)  , item_name(varchar10) , price(int), quantity(int) ) 

 

우선 JdbcTemplate을 사용하기 위해서는 dataSource를 주입받아야 합니다. 

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

jdbctemplate.update() For insert, update, delete

jdbctemplate.update() arguments

 jdbctemplate.update(PreparedStatementCreator, KeyHolder)

• DB에 데이터를 삽입(insert) 하기 위해서는 update() 메소드에 PreparedStatementCreator , KeyHolder를 인자로 받습니다. 
• update() 메소드의 리턴 값은 영향 받은 row 수 (int)
• PreparedStatmentCreator의 createPreparedStatement는 Connection을 인자로 받아 PreparedStatment를 생성해주는 메소드를 구현합니다.

@FunctionalInterface
public interface PreparedStatementCreator {

   /**
    * Create a statement in this connection. Allows implementations to use
    * PreparedStatements. The JdbcTemplate will close the created statement.
    * @param con the connection used to create statement
    * @return a prepared statement
    * @throws SQLException there is no need to catch SQLExceptions
    * that may be thrown in the implementation of this method.
    * The JdbcTemplate class will handle them.
    */
   PreparedStatement createPreparedStatement(Connection con) throws SQLException;

}

• 데이터를 저장(insert)할 때 Primary Key (PK) 생성에 identity 방식을 사용하므로 Id값은 직접 지정하지 않고 비워두고 저장합니다.
• PK인 ID 값은 데이터베이스가 생성하므로 데이터베이스에 insert가 완료된 이후에 생성된 PK ID 값을 확인할 수 있습니다.

• KeyHolder 와 connection.prepareStatement(sql, new String[]{"id"}) 를 사용해서 id 를 지정해주면 INSERT 쿼리 실행 이후에 데이터베이스에서 생성된 ID 값을 조회할 수 있습니다.
• 여기서는 PreparedStatementCreator의 createPreparedStatement를 람다식으로 표현
  • 인자로 받은 connection을 통해 pstmt 세팅 후 리턴 

public Item save(Item item) {
    String sql = "insert into item(item_name,price,quantity) values(?,?,?)";
    KeyHolder keyHolder = new GeneratedKeyHolder();

    template.update(connection -> {
        // 자동 증가 키
        PreparedStatement pstmt = connection.prepareStatement(sql, new String[]{"id"});
        pstmt.setString(1,item.getItemName());
        pstmt.setInt(2,item.getPrice());
        pstmt.setInt(3, item.getQuantity());
        return pstmt;
    }, keyHolder);

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

    return item;
}

jdbctemplate.update(sql, args..)

• DB에 데이터를 수정,삭제(update,delete) 하기 위해서는 update() 메소드에 sql, args..를 인자로 받습니다. 
• args에는 sql의 인덱스 파라미터를 순서대로 입력

@Override
public void update(Long itemId, ItemUpdateDto updateParam) {
    String sql = "update item set item_name=?, price=?, quantity=? where id = ?";
    template.update(sql,
            updateParam.getItemName(),
            updateParam.getPrice(),
            updateParam.getQuantity(),
            itemId);
}

 

jdbctemplate.queryForObject(),   jdbctemplate.query() For select

queryForObject() arguments

query() arguments

• queryForObject() : 결과 로우가 하나인 경우 사용합니다.
• query() : 결과 로우가 여러개인 경우 사용합니다.

queryForObject(sql, RowMapper, args) 

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

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

• 여기서 RowMapper는 데이터베이스의 반환 결과인 ResultSet을 객체로 변환합니다.

• ItemRepository.findById() 인터페이스는 결과가 없을 때 Optional 을 반환해야 하기 때문에, 결과가 없으면 예외를 잡아서 Optional.empty 를 대신 반환하도록 설정.

[JdbcTemplate] RowMapper에 대해

• JDBC를 직접 사용할 때는 rs.next()로 루프를 직접 돌려주었지만, jdbctemplate을 사용하면 루프를 직접 구현할 필요 없이 루프를 돌릴 내부 코드만 구현해서 내부 코드만 채우면 됩니다. 루프는 RowMapper가 돌려줍니다. 
• query()은 queryForObject()와 비교해 결과 로우가 한 개 이상이기 때문에 리턴값이 List<T>라는 점을 제외하고는 차이점이 없으므로 따로 설명은 생략하겠습니다.

jdbctemplate에 동적 SQL 적용 

item 테이블에 있는 특정 rows를 다음 네 가지 조건으로 검색하기 위해서는 동적 쿼리를 생성해야 합니다. 

 

1. 검색 조건이 없는 경우 

select id, item_name, price, quantity from item

2. 상품명만으로 검색

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

3. 최대 가격만으로 검색

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

4. 상품명, 최대 가격으로 검색

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

하지만 jdbctemplate은 따로 동적 쿼리를 구현하는 기능을 제공하지 않기 때문에 개발자가 직접 아래와 같이 구현해 주어야 합니다.

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

    // jdbc 동적 쿼리
    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());
}

상황이 커질수록 동적 쿼리를 직접 생성하는 일은 꽤 복잡한 문제입니다.

동적 쿼리는 MyBatis나 querydsl을 사용하면 쉽게 작성할 수 있습니다.

NamedParameterJdbcTemplate

jdbctemplate을 사용하면 파라미터를 순서대로 바인딩해주어야 합니다.

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

 

하지만 NamedParameterJdbcTemplate을 사용하면 이름을 지정해서 파라미터를 바인딩 할 수 있습니다.

 

NamedParameterJdbcTemplate도 마찬가지로 당연히 dataSource를 주입받습니다.

public JdbcTemplateItemRepositoryV2(DataSource dataSource)
{
    this.template = new NamedParameterJdbcTemplate(dataSource);
}

 

 

NameParamterJdbcTemplate은 update() 메소드에서 아래 인자를 사용합니다.

NamedParameterJdbcTemplate update() arguments

파라미터를 전달하기 위해서는 Map처럼 key, value 데이터 구조를 만들어 전달해야 합니다.

 

이 Map을 직접 만들어 전달하는 방법도 있지만, SqlParameterSource라는 인터페이스를 사용해서 파라미터 객체를 전달하는 두 가지 방법에 대해 알아보겠습니다.

1. BeanPropertySqlParameterSource ( SqlParameterSource Interface ) 

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

BeanPropertySqlParameterSource(T)

• 해당 Type에 자바빈 프로퍼티 규약(getXxx() -> xxx)을 통해 자동으로 파라미터 객체를 생성합니다.
• ex) T의 프로퍼티를 통해 (key=itemName, value=값), (key=price, value = 값), (key=quantity, value = 값) 생성

2. MapSqlParameterSource( SqlParameterSource Interface ) 

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("itemName",updateParam.getItemName());

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

    template.update(sql, param);
}

 

MapSqlParameterSource()

• 특정 T로부터 바인딩해야 할 뿐만 아니라, 그 외의 타입도 바인딩 해야 할 경우 사용합니다.
• 추가로 MapSqlParameterSource는 여러 개의 파라미터를 등록해야 할 경우 위처럼 메소드 체인을 통해 편리하게 등록할 수 있습니다.

3. Map을 직접 사용 

    @Override
    public Optional<Item> findById(Long id) {
        String sql = "select id, item_name, price, quantity from item where id=:id";
        try
        {
//            MapSqlParameterSource param = new MapSqlParameterSource("id", id);
            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();
        }
    }

    private RowMapper<Item> itemRowMapper() {

        return BeanPropertyRowMapper.newInstance(Item.class);

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

• Map을 직접 사용해서 param 객체를 만들어도 됩니다.

* itemRowMapper()를 BeanPropertyRowMapper.newInstance(Item.class)로 생성해주었습니다.

Q. 그렇다면 이렇게 사용하면 itemRowMapper()  setItemName()이 아닌 setItem_Name()으로 적용되지 않을까 ?
* 자바 객체는 카멜 표기법 사용하지만, 데이터베이스에서는 주로 언더스코어를 사용하는 스네이크 표기법을 사용합니다.
* 이 부분을 관례로 많이 사용하다보니 BeanPropertyRowMapper는 언더스코어 표기법을 카멜로 자동으로 변환해 줍니다!

SimpleJdbcInsert

insert에 한해서 jdbcTemplate은 insert SQL을 직접 작성하지 않아도 되도록 SimpleJdbcInsert라는 기능을 제공합니다.

 

SimpleJdbcInsert는 dataSource를 주입받고, Table 이름을 지정하고 key를 생성하는 PK 컬럼명을 지정합니다.

추가적으로 INSERT SQL에 사용할 특정 column을 지정할 수 있는데, 특정 값만 저장하고 싶을 때 지정할 수 있는 옵션입니다. 생략 가능.

    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");
//                .usingGeneratedKeyColumns("item_name","price","quantity"); // 생략 가능
    }

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

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

jdbcTemplate 기능 정리

기능 

• jdbctemplate : 순서 기반 파라미터 바인딩 지원
• NamedParameterJdbcTemplate : 이름 기반 파라미터 바인딩 지원
  • BeanPropertySqlParameterSource : 자바빈 프로퍼티 규약을 통해 자동으로 Type에 대한 파라미터 객체 생성
  • MapSqlParameterSource : Map과 유사. 메소드 체인을 통해 여러 파라미터 등록 가능 
• SimpleJdbcInsert : insert 쿼리 편리하게 사용 가능

메소드

• 변경 ( insert, update, delete ) : update() 
• 조회 ( select ) : query(), queryForObejct()
• 기타 ( DDL ) : execute() 

 

 

<참고자료>

Data Access