[Spring DB2] 데이터 접근 기술 - 스프링 JdbcTemplate-2

JdbcTemplate - 이름 지정 파라미터

String sql = "update item set item_name=?,price=?, quantity=? where id=?

원래 이런 sql 코드였는데

String sql = "update item set item_name=?,quantity=?,price=? where id=?

이렇게 바뀌면 quantity와 price의 데이터 바인딩 순서가 바뀐다. 그러면 바인딩 코드도 바꿔야 하는 문제가 발생한다.

그래서 파라미터를 순서대로 바인딩하는 것이 아니라 이름을 지정해서 파라미터를 바인딩하는 'NamedParameterJdbcTemplate'라는 기능을 제공한다.

 

/**
 * NamedParameterJdbcTemplate
 * SqlParameterSource
 * - BeanPropertySqlParameterSource
 * - MapSqlParameterSource
 * - Map
 *
 * BeanPropertyRowMapper
 */
@Slf4j
public class JdbcTemplateItemRepositoryV2 implements ItemRepository {

//    private final JdbcTemplate template;
    private final NamedParameterJdbcTemplate template;
    public JdbcTemplateItemRepositoryV2(DataSource 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);

        //DB에서 생성해준 id값 가져오기
        KeyHolder keyHolder = new GeneratedKeyHolder();
        template.update(sql,param,keyHolder);

        //데이터베이스에 insert가 완료되어야 키 값을 확인할 수 있어서 insert후에 key값 가져오기.
        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) {
        String itemName = cond.getItemName();
        Integer maxPrice = cond.getMaxPrice();

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

SQL에서 '?' 대신 ':파라미터 이름'으로 받을 수 있다.

그리고 save()에서처럼 데이터베이스에서 생성해주는 키를 쉽게 조회할 수 있다.

 

이름 지정 파라미터

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

key는 ':파라미터 이름'으로 지정한 파라미터의 이름이고, value는 해당 파라미터의 값이다.

이렇게 만든 파라미터를 template.update(sql, param, keyHolder)처럼 전달한다.

 

이름 지정 바인딩에서 자주 사용하는 파라미터의 종류

• Map
  • findById()에서 확인할 수 있다.
  • Map을 만들어서 키는 "id", 값은 파라미터로 넘어온 id 값을 넣어줘서 파라미터를 넣어준다.
• SqlParameterSource: Map과 유사한데, SQL 타입을 지정해줄 수 있는 등의 SQL에 특화된 기능을 제공한다.
  • MapSqlParameterSource
    
    • update()에서 확인할 수 있다.
    • 메서드 체인을 통해 편리하게 사용할 수 있다.
  • BeanPropertySqlParameterSource
    • save(), findAll()에서 확인할 수 있다.
    • 자동으로 파라미터 객체를 생성해준다. 예) getXxx() -> xxx, getItemName() -> itemName처럼 key=itemName, value=상품명 값, key=price, value=가격 값 으로 만들어준다.
    • 하지만 이것은 update()에서는 사용할 수 없다. update()에서는 id 값도 바인딩해주어야 하는데, ItemUpdateDto에는 itemId가 없기 때문에 사용할 수 없다

BeanPropertyRowMapper

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

이런 코드를 알아서 작성해준다.

---

데이터베이스에는 item_name으로 저장되어 있고, 코드에서는 itemName으로 되어 있으면

select item_name as itemName ...

이렇게 별칭을 넣어 작성하면 별칭으로 조회를 한다.

 

자바의 관례는 카멜 표기법이고, 관계형 데이터베이스의 관례는 _를 사용하는 것이다. 그래서 BeanPropertyRowMapper는 _를 카멜로 자동 변환해준다. set item_name으로 조회해도, set itemName으로 자동 변환해준다.

 

그래서 _의 문제가 아니라 아예 컬럼명과 객체명이 다를 때 as를 사용하면 된다.

 

 

---

JdbcTemplate - SimpleJdbcInsert

직접 insert문을 작성하지 않아도 되도록 지원한다.

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

//    private final JdbcTemplate template;
    private final NamedParameterJdbcTemplate template;
    private final SimpleJdbcInsert jdbcInsert;
    public JdbcTemplateItemRepositoryV3(DataSource 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에 사용할 컬럼을 지정한다. 특정 값만 지정하고 싶을 때 사용한다. 생략 가능하다.
  • 생성 시점에 데이터베이스 테이블의 메타데이터를 조회해서 어떤 컬럼이 있는지 확인할 수 있어서 생략 가능한 것이다.

---

JdbcTemplate 기능 정리

• JdbcTemplate
  • 순서 기반 파라미터 바인딩 지원
• NamedParameterJdbcTemplate
  • 이름 기반 파라미터 바인딩 지원
• SimpleJdbcInsert
  • INSERT SQL을 편리하게 사용 가능
• SimpleJdbcCall
  • 스토어드 프로시저를 편리하게 호출 가능

조회

 

단건 조회 - 숫자 조회

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

하나의 로우를 조회할 때는 'queryForObject()'를 사용. 조회 대상이 객체가 아니라 단순 데이터 하나이면 Integer.class, String.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;
 });

목록 조회 시에는 quert()를 사용한다. 결과는 리스트로 반환된다. 결과를 객체로 매핑해야 해서 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);
}

이렇게 RowMapper를 분리하면 RowMapper를 재사용할 수 있다.

 

변경(INSERT, UPDATE, DELETE)

 

등록

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