[study] 스프링 DB 2편 - 2. JdbcTemplate

김영한님의 스프링 DB 2편 - 데이터 접근 활용 기술을 듣고 정리한 내용입니다.

JdbcTemplate 소개

Sql을 직접 사용하는 경우 스프링이 제공하는 JdbcTemplet은 JDBC를 편리하게 사용할 수 있게 도와준다.

 

장점

설정의 편리
spring-jdbc 라이브러리에 포함되어 있는데,
이 라이브러리는 스프링으로 JDBC를 사용할 때 기본으로 사용되는 라이브러리이다.
별도의 설정 없이 바로 사용 가능

반복문제 해결

JdbcTemplate는 템플릿 콜백 패턴을 사용해서 JDBC를 직접 사용할 때 발생하는 대부분의 반복작업 대신 처리
개발자는 SQL을 작성하고, 전달할 파리미터를 정의하고, 응답 값을 매핑하기만 하면 된다.

 

단점

동적 SQL 해결하기 어려움

 

 

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

 

 

@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 -> {
            //자동 증가 키
            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;
        });
    }
}

JdbcTemplateItemRepositoryV1 은 ItemRepository 인터페이스를 구현

JdbcTemplate 은 데이터소스( dataSource )가 필요하다.
JdbcTemplateItemRepositoryV1() 생성자를 보면 dataSource 를 의존 관계 주입 받고
생성자 내부에서 JdbcTemplate 을 생성한다.

 

save()

데이터를 저장한다.
template.update() : 데이터를 변경할 때는 update() 를 사용하면 된다

데이터를 저장할 때 PK 생성에 identity (auto increment) 방식을 사용하기 때문에, 
PK인 ID 값을 개발자가 직접 지정하는 것이 아니라 비워두고 저장해야 한다. 
그러면 데이터베이스가 PK인 ID를 대신 생성

 

update()

데이터를 업데이트 한다.
template.update() : 데이터를 변경할 때는 update() 를 사용하면 된다.

 

findById()

데이터를 하나 조회한다.
template.queryForObject()
결과 로우가 하나일 때 사용한다.

 

queryForObject() 인터페이스 정의

	@Override
	@Nullable
	public <T> T queryForObject(String sql, RowMapper<T> rowMapper, @Nullable Object... args) throws DataAccessException {
		List<T> results = query(sql, args, new RowMapperResultSetExtractor<>(rowMapper, 1));
		return DataAccessUtils.nullableSingleResult(results);
	}

 

findAll()

데이터를 리스트로 조회한다. 그리고 검색 조건으로 적절한 데이터를 찾는다.

@Override
public <T> List<T> query(String sql, RowMapper<T> rowMapper, @Nullable Object... args) throws DataAccessException {
    return result(query(sql, args, new RowMapperResultSetExtractor<>(rowMapper)));
}

 

itemRowMapper()

데이터베이스의 조회 결과를 객체로 변환할 때 사용한다.
JDBC를 직접 사용할 때 ResultSet 를 사용했던 부분

 

 

동적 쿼리 문제

findAll() 에서 어려운 부분은 사용자가 검색하는 값에 따라서 실행하는 SQL이 동적으로 달려져야 한다는 점

검색 조건이 없음

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 <= ?

 

상품명( itemName ), 최대 가격( maxPrice ) 둘다 검색

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

 

4가지의 상황에따른 SQL 동적 생성 필요. 

 

 

이름 지정 바인딩

@Slf4j
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) {
        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); //camel 변환 지원
    }
}

save()

SQL에서 다음과 같이 ? 대신에 :파라미터이름 을 받는 것을 확인할 수 있다.

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

 

JdbcTemplate - 이름 지정 파라미터

주로 사용되는 파라미터 종류

• Map
• SqlParameterSource
• MapSqlParameterSource
• BeanPropertySqlParameterSource

 

1. Map

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

 

2. MapSqlParameterSource

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

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

 

BeanPropertyRowMapper

private RowMapper<Item> itemRowMapper() {
    return BeanPropertyRowMapper.newInstance(Item.class); //camel 변환 지원
}

ResultSet 의 결과를 받아서 자바빈 규약에 맞추어 데이터를 변환한다

 

자바 객체는 카멜( camelCase ) 표기법을 사용한다
반면에 관계형 데이터베이스에서는 주로 언더스코어를 사용하는 snake_case 표기법을 사용한다.
BeanPropertyRowMapper 는 언더스코어 표기법을 카멜로 자동 변환해준다.

 

@Slf4j
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"); //생략 가능
    }

    @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) {
        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); //camel 변환 지원
    }
}

 

SimpleJdbcInsert

    public JdbcTemplateItemRepositoryV3(DataSource dataSource) {
        this.template = new NamedParameterJdbcTemplate(dataSource);
        this.jdbcInsert = new SimpleJdbcInsert(dataSource)
                .withTableName("item")
                .usingGeneratedKeyColumns("id");
        //                .usingColumns("item_name", "price", "quantity"); //생략 가능
    }

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

 

save()

jdbcInsert.executeAndReturnKey(param) 을 사용해서 INSERT SQL을 실행하고, 생성된 키 값도 매우 편리하게 조회

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

 

 

정리

JdbcTemplate

순서 기반 파라미터 바인딩을 지원한다.

NamedParameterJdbcTemplate

이름 기반 파라미터 바인딩을 지원한다. (권장)

 

SimpleJdbcInsert

INSERT SQL을 편리하게 사용할 수 있다.

 

SimpleJdbcCall

스토어드 프로시저를 편리하게 호출할 수 있다.

 

JDBCTemplate 사용법

단건 - 숫자조회

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

 

단건 - 숫자, 파라미터 바인딩

int countOfActorsNamedJoe = jdbcTemplate.queryForObject(
"select count(*) from t_actor where first_name = ?", Integer.class,
"Joe");

 

단건 - 객체조회

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 사용

 

목록 조회 - 객체

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사용하여 결과 객체 매핑

 

목록 조회 - 객체

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

 

변경 ( INSERT,UPDATE,DELETE)

int값 반환, SQL 실행 결과에 영향받은 로우수 반환

등록

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 실행 시 excute() 사용하면 됨

DDL

jdbcTemplate.execute("create table mytable (id integer, name varchar(100))");

 

스토어드 프로시저 호출

jdbcTemplate.update(
    "call SUPPORT.REFRESH_ACTORS_SUMMARY(?)",
    Long.valueOf(unionId));

 

 

 

 

---

https://www.inflearn.com/course/%EC%8A%A4%ED%94%84%EB%A7%81-db-2/dashboard

스프링 DB 2편 - 데이터 접근 활용 기술 - 인프런 | 강의