[JDBC] JdbcTemplate

1. JdbcTemplate 설정

sql을 직접 사용 하는 경우에는 스프링이 제공하는 jdbcTemplate를 사용하면 JDBC를 매우 편리하게 사용할 수 있다.

 

장점

• spring-jdbc 라이브러리에 포함되어 있어 별도의 복잡한 설정 없이 바로 사용 가능하다.
• 콜백 패턴을 사용하기 때문에 JDBC를 직접 사용할 때 발생하는 대부분의 반복 작업을 대신 처리해준다.
  • SQL작성하고, 전달 파라미터 정의하고, 응답 값 매핑만 하면 된다. 

단점

• 동적 SQL 해결하기가 어렵다.

먼저 JdbcTemplate를 사용하려면 build.grdle에 추가를 해야하는데 H2 데이터베이스에 접속해야하기 때문에 H2 클라이언트 라이브러리도 추가해준다.

 

진행 전에 H2 데이터베이스에 item 테이블을 생성해야 한다.

drop table if exists item CASCADE;
create table item
(
 id bigint generated by default as identity,
 item_name varchar(10),
 price integer,
 quantity integer,
 primary key (id)
);

 generated by default as identity 

- identity 전략이고 하는데, 기본 키 생성을 데이터베이스에 위임하는 방법이다. (MySQL의 AutoIncrement와 같은 방법)

- PK로 사용되는 id는 개발자가 직접 정하는 것이 아닌 비워두고 저장하면 된다. 그러면 db가 순서대로 증가하는 값을 사용하여 넣어준다.

 

권장하는 식별자 선택 전략

데이터베이스의 기본 키는 3가지 조건을 모두 만족해야 한다.

1. null 값은 허용하지 않는다.
2. 유일해야 한다.
3. 변해서는 안 된다.

테이블의 기본 키를 선택하는 전략은 크게 자연키와 대리키 2가지로 나뉘는데 자연 키(natural key)는 비즈니스에 의미 있는 키로 주민번호나 이메일, 전화 번호 등이 해당되고 

대리 키(surrogate key)는 비즈니스와 관련 없는 임의로 만들어진 키로 대체 키라고도 한다. 예를 들어 오라클 시퀀스, auto_increment, identity, 키생성 테이블 사용 등이 있다.

 

보통은 자연 키<<<<대리 키이다. 예를 들어 자연 키인 전화번호를 기본 키로 택한다면 번호가 유일할 수는 있지만 전화 번호가 없을 수도 있고 변경될 수도 있기 때문에 기본 키로 적절하지 않다. 문제는 주민번호 같이 그럴듯하게 보이는 값인데 null도 아니고 유일하고 변하지도 않는다. 하지만 주민번호도 정부 정책 등 여러 이유로 변경될 수 있다. 기본 키의 조건을 현재는 물론 미래까지 충족하는 자연 키를 찾기는 쉽지 않다.

 

2. JdbcTemplate 적용1 - 기본

ItemRepository인터페이스를 기반으로 JdbcTemplate을 사용하는 새로운 구현체를 개발해보려 한다.

 

[JdbcTemplateItemRepositoryV1.java]

/*
* JdbcTemplate
* */
@Slf4j
@Repository
public class JdbcTemplateItemRepositoryV1 implements ItemRepository {
//ItemRepository 인터페이스 구현
    private final JdbcTemplate template;

    public JdbcTemplateItemRepositoryV1(DataSource dataSource) {
        this.template = new JdbcTemplate(dataSource); //커넥션도 만들고 하기 때문에 dataSource 필요
      	/*
        DataSource 의존 관계 주입 받고 생성자 내부에서 JdbcTemplate 생성
        JdbcTemplate 사용할 땐 관례상 이 방법 많이 사용
        JdbcTemplate 스프링 빈으로 직접 등록하고 주입받아도 상관은 없다.
        */
    }
    @Override
    public Item save(Item item) {
        String sql = "insert into item(item_name, price, quantity) values (?,?,?)";
        KeyHolder keyHolder = new GeneratedKeyHolder(); //db에서 생성해준 ID값 사용하기 위해서 필요
        template.update(connection-> {
        /*
        template.update의 반환 값은 int인데, 영향 받은 로우 수를 반환한다.
        */
            //자동 증가 키
            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);
            //queryForObject는 결과가 없으면 예외가 터진다.
            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;
        };
    }
}

• save() 
  • 데이터 저장할 때 PK 생성에 identity (auto increment)방식을 사용하기 때문에, PK인 ID 값을 개발자가 직접 지정하는 것이 아닌 비워두고 저장해야 한다. 그러면 데이터베이스가 PK인 ID를 대신 생성해준다.
  • 문제는 이렇게 생성된 PK ID 값은 데이터베이스에 INSERT가 완료 되어야 생성된 값을 확인할 수 있다. 
    
    • KeyHolder와 connection.preparedStatement(sql, new String[]{"id"})를 사용해서 id를 지정해주면 INSERT 쿼리 실행 이후 생성된 ID 값 조회가 가능하다.
• update() 
  • 데이터를 변경할 때에는 template.update()를 사용하면 된다.
    • ?에 바인딩할 파라미터를 순서대로 전달하면 된다.
    • 반환 값은 해당 쿼리의 영향을 받은 로우 수 이다. where id=?이므로 영향 받은 로우수는 최대 1개이다. 
• findById()
  • template.queryForObject()
    • 결과 로우가 하나일 때 사용한다.
    • RowMapper는 데이터베이스의 반환 결과인 Result을 객체로 변환한다.
    • 결과가 없으면 EmptyResultDataAccessException 예외가 발생한다.
    • 결과 둘 이상이면 IncorrectResultSizeDataAccessException 예외가 발생한다.
  • ItemRepository.findById() 인터페이스는 결과가 없을 때 Optional을 반환해야 한다. 따라서 결과가 없으면 예외를 잡아서 Optional.empty를 대신 반환하면 된다.
• findAll()
  • 데이터를 리스트로 조회하고 검색 조건으로 적절한 데이터를 찾는다.
  • template.query()
    • 결과가 하나 이상일 때 사용한다.
    • RowMapper는 데이터베이스의 반환 결과인 ResultSet을 객체로 반환한다.
    • 결과가 없으면 빈 컬렉션을 반환한다.
• itemRowMapper()
  • 데이터베이스의 조회 결과를 객체로 변환할 때 사용한다.
  • JDBC를 직접 사용할 때 ResultSet를 사용했던 부분을 떠올리면 된다.
  • 차이가 있다면 아래와 같이 JdbcTemplate가 루프를 돌려주고, 개발자는 RowMapper를 구현해서 내부코드만 채운다고 이해하면 된다. 

while(resultSet이 끝날 때 까지) {
	rowMapper(rs, rowNum)	
}

 

3. JdbcTemplate 적용 2 - 동적 쿼리 문제

결과를 검색하는 findAll()에서 사용자가 검색하는 값에 따라서 실행하는 SQL이 동적으로 달라져야 한다는 점에서 어렵다고 볼 수 있는데 다음과 같이 4가지 다양한 상황을 고민해야 한다.

 

검색 조건이 없는 경우

select id, item_name, price, quantity from item

상품명(itemName)으로 검색하는 경우

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

최대 가격(maxPrice)으로 검색하는 경우

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

상품명, 최대 가격 둘 다 검색하는 경우

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

 

4. JdbcTemplate 적용 3 - 구성과 실행

[JdbcTemplateV1Config.java]

@Configuration
@RequiredArgsConstructor
public class JdbcTemplateV1Config {

    private final DataSource dataSource;

    @Bean
    public ItemService itemService() {
        return new ItemServiceV1(itemRepository());
    }

    @Bean
    public ItemRepository itemRepository() {
        return new JdbcTemplateItemRepositoryV1(dataSource);
    }
}

ItemRepository 구현체로 JdbcTemplateItemRepository가 사용되도록 하고 메모리 저장소가 아닌 실제 DB에 연결하는 JdbcTemplate가 사용된다.

 

ItemServiceApplication에서 @Import를 MemoryConfig.class에서 JdbcTemplateV1Config.class로 변경해주어야 한다.

그리고 src/main에 위치한 application.properties파일에 데이터베이스 정보를 설정해준다.

spring.profiles.active=local
spring.datasource.url=jdbc:h2:tcp://localhost/~/test
spring.datasource.username=sa
spring.datasource.password=1234

 

이렇게 설정을 해두면 스프링 부트가 해당 설정을 사용해서 커넥션 풀과 DataSource, 트랜잭션 매니저를 스프링 빈으로 자동 등록한다.

 

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

순서대로 바인딩

JdbcTemplate을 기본으로 사용하면 파라미터를 순서대로 바인딩한다. SQL에 있는 ?의 순서대로 바인딩 된다는 소리이다. 따라서 순서만 잘 지키면 문제가 될 건 없다. 

하지만 수정을 하면서 순서를 바꾸게 된다면 버그가 발생하게 된다.

 

이름 지정 바인딩

JdbcTemplate는 위와 같은 문제를 보완하기 위해 NmaedParameterJdbcTemplate라는 이름을 지정하여 파라미터를 바인딩하는 기능을 제공한다.

 

[JdbcTemplateItemRepositoryV2.java]

/*
* NamedParamterJdbcTemplate
* SqlParameterSource
*  - BeanPropertySqlParameterSource
*  - MapSqlParameterSource
* Map
*
* BeanPropertyRowMapper
* */

@Slf4j
@Repository
public class JdbcTemplateItemRepositoryV2 implements ItemRepository {

//    private final JdbcTemplate template;

    private final NamedParameterJdbcTemplate template;

    public JdbcTemplateItemRepositoryV2(DataSource dataSource) {
        this.template = new NamedParameterJdbcTemplate(dataSource); //커넥션도 만들고 하기 때문에 dataSource 필요
    }

    @Override
    public Item save(Item item) {
        String sql = "insert into item(item_name, price, quantity) " +
                "values (:itemName, :price, :quantity)";
                //?대신에 :파라미터이름 을 받는다. 

        BeanPropertySqlParameterSource param = new BeanPropertySqlParameterSource(item);

        KeyHolder keyHolder = new GeneratedKeyHolder(); //db에서 생성해준 ID값 사용하기 위해서 필요
        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=?";

        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); //key:id, value: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();

        BeanPropertySqlParameterSource 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가 필요하다.
  • V2의 생성자를 보면 의존관계 주입은 dataSource를 받고 내부에서 NamedParameterJdbcTemplate을 생성해서 가지고 있다. (스프링 빈으로 직접 등록하고 주입 받아도 상관 없다.)
• SQL에서 ?대신에 :파라미터이름을 받는 것을 확인할 수 있다.

 

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

파라미터를 전달하기 위해선 Map처럼 key, value 구조를 만들어 전달해야한다. 

 

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

1. Map

단순하게 Map을 사용하는 것인데 findById()코드에서 확인할 수 있다.

 

2. MapSqlParameterSource

Map과 유사하지만 SQL타입을 지정할 수 있는 등 SQL에 좀 더 특화되어 있고 SqlParameterSource 인터페이스의 구현체이다.

update()코드에서 확인할 수 있다.

 

3. BeanPropertySqlParameterSource

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

예를 들어 getItemName(), getPrice()가 있으면 key=itemName, value = 상품명 값 / key=price, value=가격 값 같은 데이터를 자동으로 만들어 낸다.

이또한 SqlparmeterSource 인터페이스의 구현체이다. save(), findAll() 코드에서 확인할 수 있다.

BeanPropertySqlParameterSource param = new BeanPropertySqlParameterSource(item);

KeyHolder keyHolder = new GeneratedKeyHolder(); //db에서 생성해준 ID값 사용하기 위해서 필요
template.update(sql, param, keyHolder);

BeanPropertySqlParameterSource를 항상 사용할 수 있는 것은 아니다. 예를 들어 update()에서는 SQL에 :id를 바인딩 해야 하는데 update()에서 사용하는 ItemUpdateDto에는 itemId가 없어서 사용할 수 없다.

 

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

[JdbcTemplateV2Config.java]

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

JdbcTemplateItemRepositoryV2를 사용하도록 스프링 빈에 등록한다. 

 

ItemServiceApplication에서 설정을 변경해준다. 

@Import(JdbcTemplateV1Config.class) -> @Import(JdbcTemplateV2Config.class)

 

8. JdbcTemplate - SimpleJdbcInsert

JdbcTempalte는 INSERT SQL을 직접 작성하지 않아도 되도록 simpleJdbcInsert라는 기능을 제공한다.

[JdbcTempalteItemRepositoryV3.java]

/*
* 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); //커넥션도 만들고 하기 때문에 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); //INSERT SQL 실행, 생성된 키 값 편리하게 조회 가능
        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); //key:id, value: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();

        BeanPropertySqlParameterSource 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표기법 변환 지원
    }
}

 

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

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

 

[JdbcTemplateV3Config.java]

@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에서 @Import(JdbcTemplateV2Config.class) -> @Import(JdbcTemplateV3Config.class)로 변경해준다.

 

이외에도 JdbcTempalte에 대한 사용법은 아래 링크를 통해 자세히 소개되어있다.

https://docs.spring.io/spring-framework/reference/data-access/jdbc/core.html#jdbc-JdbcTemplate

Using the JDBC Core Classes to Control Basic JDBC Processing and Error Handling :: Spring Framework