[Spring Database] JdbcTemplate

 

 

 

SQL을 자바 문자열로 직접 사용할 때 JdbcTemplate을 사용하면 편하다.

 

JdbcTemplate은 spring-jdbc 라이브러리에 기본적으로 포함돼있어 별도의 설정 없이 바로 사용할 수 있고, 템플릿 콜백 패턴을 사용해 JDBC를 직접 사용할 때 발생하는 여러 가지 귀찮은 작업을 대신 처리해준다.

(커넥션 획득, 스프링 예외 변환기 실행 등..)

 

 

/**
 * jdbc template 구현
 */

@Slf4j
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 -> {
            // auto increasing key
            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=? 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(), param.toArray());
    }

    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가 필요하다.

생성자를 통해 DataSource를 주입받고 JdbcTemplate을 생성하자.

 

데이터베이스에 저장 할 때 id는 데이터베이스가 직접 생성해서 넣어준다.

즉, insert가 완료된 상황에서 id값을 알 수 있기 때문에 이 부분을 KeyHolder를 사용해서 처리했다.

 

업데이트는 간단하니 코드를 참고하도록 하고..

 

id로 데이터를 하나 찾을 때는 queryForObject() 메서드를 사용한다.

메서드의 파라미터로 (SQL, 반환받는 타입, SQL에 전달해야하는 파라미터들) 을 넣어준다.

반환되는 결과인 ResultSet을 item으로 변환하기 위해 RowMapper를 사용했고 (루프를 대신 돌려준다), 값이 존재하지 않거나 두 가지 이상인 경우를 처리하기 위해 try - catch로 감쌌다.

 

가져오는 데이터가 하나 이상일 경우 query() 메서드를 사용한다.

역시 메서드의 파라미터로 (SQL, 반환받는 타입, SQL에 전달해야하는 파라미터들) 을 넣어준다.

검색 쿼리의 조건에 따라 가져오는 데이터가 달라지는 경우 검색 조건이 증가할수록 작성해야 하는 SQL이 기하급수적으로 많아진다.

 

즉, SQL을 각각의 경우마다 모두 작성해 놓는게 불가능하다.

그러니 이 부분에서 동적 쿼리를 사용해야 하는데.. JdbcTemplate을 사용해 동적 쿼리를 구현하는건 상당히 힘들다..

물론 불가능하지는 않지만, 자칫하면 오류가 발생하기 쉽고, 작성하는 과정도 복잡하다.

 

 

 

---

 

 

 

위의 코드에서 update부분을 살펴보면 파라미터를 바인딩 할 때 작성한 순서대로 바인딩되는걸 확인할 수 있다.

 

순서만 잘 지키면 파라미터 바인딩 관련 문제가 발생하지 않을텐데.. 파라미터가 10 ~ 20개가 넘어가게 되면 필드를 추가하거나 수정할 때 파라미터 바인딩 순서를 지키지 못하는 경우가 발생하기 쉽다.

 

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

 

 

@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을 사용한다.

 

SQL에서 (?) 대신  (:파라미터이름) 을 사용해 파라미터를 받는다.

 

이제 파라미터를 전달해보자.

 key값으로 파라미터의 이름 / value값으로 해당 파라미터의 값을 가지는 Map 자료구조를 사용해 전달하는데, 이 부분을 구현하는 방법으로는 여러 가지가 있다.

 

 

1. Map 그대로 사용하기

 

그냥 형식에 맞게 Map을 만들어서 사용하는 방법이다.

findById() 메서드에서 확인하자.

 

 

2. MapSqlParameterSource

 

Map을 그냥 사용하는 방법과 유사한데, SQL에 특화된 기능 몇 가지를 제공한다.

메서드 체인을 통해 편하게 사용할 수 있다.

update() 메서드에서 확인하자.

 

3. BeanPropertySqlParameterSource

 

가장 편한 방법이다.

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

save(), findAll() 메서드에서 확인하자.

 

가장 편하지만 항상 쓸 수는 없다.

update() 메서드에서는 id가 필요한데, 해당 메서드에서 사용하는 ItemUpdateDto에는 id가 포함돼있지 않다.

따라서 BeanPropertySqlParameterSource 대신 MapSqlParameterSource 를 사용했다.

 

세 가지 방법 중 편한 방법을 사용하자.

 

추가로 RowMapper도 BeanPropertyRowMapper를 사용해 간단하게 해당 객체로 변환해줬다.

이 때도 자바빈 프로퍼티 규약에 맞춰 getter와 setter를 사용하는데, 객체 이름과 데이터베이스 칼럼 이름을 맞춰서 사용하기 위해 SQL문을 select item_name as itemName 처럼 작성해 별칭을 사용한다.

 

 

자바 객체는 camelCase 표기법을 자주 사용하고, 관계형 데이터베이스에서는 snake_case 표기법을 자주 사용한다.

이렇게 관례로 많이 사용하는 표현들에 대해서 쉽게 변환이 가능하도록 스프링은 BeanPropertyRowMapper을 제공한다.

 

 

 

---

 

 

 

JdbcTemplate은 insert SQL을 대신 작성해주는 SimpleJdbcInsert 를 제공한다.

 

 

@Slf4j
@Repository
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");
                // usingcolumn은 생략 가능 데이터소스에서 읽어온다.
    }

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

}

 

 

dataSource를 통해 SimpleJdbcInsert 를 생성한다.

 

withTableName() : 데이터를 저장할 테이블 명을 지정하고,

usingGeneratedKeyColumns() : key를 생성하는 PrimaryKey 칼럼 명을 지정한다.

usingColumns() : insert 를 적용할 칼럼을 지정한다. (생략 가능)

 

생성 시점에 데이터베이스 테이블의 메타 데이터를 조회하기 때문에, SimpleJdbcInsert 가 어떤 칼럼이 있는지 알 수 있다.

생략 시 모든 칼럼을 사용하고, 특정 칼럼만 지정해서 사용하고 싶으면 해당하는 칼럼을 지정해주자.

 

 

JdbcTemplate은 간단하게 사용할 수 있지만, SQL을 자바로 작성해야 하고 동적 쿼리 문제를 해결하지 못한다는 단점이 있다.