[Spring] 스프링 DB 2편 02

김영한님의 스프링 DB 2편 을 공부하여 정리한 글입니다.

JdbcTemplate 소개와 설정

장점

• JdbcTemplate은 spring-jdbc 라이브러리에 포함되어 있는데, 이 라이브러리는 스프링으로 JDBC를 사용할 때 기본으로 사용되는 라이브러리이다. 그리고 별도의 복잡한 설정 없이 바로 사용할 수 있다.
• JdbcTemplate은 템플릿 콜백 패턴을 사용해서, JDBC를 직접 사용할 때 발생하는 대부분의 반복 작업을 대신 처리해준다.

단점

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

JdbcTemplate 적용1 - 기본

Repository

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

save()

• template.update()
  INSERT , UPDATE , DELETE SQL에 사용한다.
  template.update() 의 반환 값은 int 인데, 영향 받은 로우 수를 반환한다.
• 데이터를 저장할 때 PK 생성에 identity (auto increment) 방식을 사용하기 때문에,
  PK인 ID 값을 개발자가 직접 지정하는 것이 아니라 비워두고 저장해야 한다.
• KeyHolder 와 connection.prepareStatement(sql, new String[]{"id"}) 를 사용해서 id 를 지정해주면 INSERT 쿼리 실행 이후에 데이터베이스에서 생성된 ID 값을 조회할 수 있다.

findById()

• template.queryForObject()
  결과 로우가 하나일 때 사용한다.
  RowMapper 는 데이터베이스의 반환 결과인 ResultSet 을 객체로 변환한다.
  결과가 없으면 EmptyResultDataAccessException 예외가 발생한다.
  결과가 둘 이상이면 IncorrectResultSizeDataAccessException 예외가 발생한다.

findAll()

• template.query()
  결과가 하나 이상일 때 사용한다.
  RowMapper 는 데이터베이스의 반환 결과인 ResultSet 을 객체로 변환한다.

JdbcTemplate 적용3 - 구성과 실행

Config

@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 구현체로 JdbcTemplateItemRepositoryV1 이 사용되도록 했다.

Application

//@Import(MemoryConfig.class)
@Import(JdbcTemplateV1Config.class)
@SpringBootApplication(scanBasePackages = "hello.itemservice.web")
public class ItemServiceApplication {

	public static void main(String[] args) {
		SpringApplication.run(ItemServiceApplication.class, args);
	}

	@Bean
	@Profile("local")
	public TestDataInit testDataInit(ItemRepository itemRepository) {
		return new TestDataInit(itemRepository);
	}

}

JdbcTemplate - 이름 지정 파라미터 1

이름 지정 바인딩

NamedParameterJdbcTemplate 라는 이름을 지정해서 파라미터를 바인딩 하는 기능을 제공한다.

Repository

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

        MapSqlParameterSource 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, Long> 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();

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

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

JdbcTemplate - 이름 지정 파라미터 2

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

• Map
• SqlParameterSource
  • MapSqlParameterSource
  • BeanPropertySqlParameterSource

Map

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

MapSqlParameterSource

Map 과 유사한데, SQL 타입을 지정할 수 있는 등 SQL에 좀 더 특화된 기능을 제공한다.
MapSqlParameterSource 는 메서드 체인을 통해 편리한 사용법도 제공한다.

SqlParameterSource param = new MapSqlParameterSource()
 .addValue("itemName", updateParam.getItemName())
 .addValue("price", updateParam.getPrice())
 .addValue("quantity", updateParam.getQuantity())
 .addValue("id", itemId); //이 부분이 별도로 필요하다.
template.update(sql, param);

BeanPropertySqlParameterSource

자바빈 프로퍼티 규약을 통해서 자동으로 파라미터 객체를 생성한다.
예를 들어서 getItemName() , getPrice() 가 있으면 다음과 같은 데이터를 자동으로 만들어낸다.
key=itemName, value=상품명 값
key=price, value=가격 값

SqlParameterSource param = new BeanPropertySqlParameterSource(item);
KeyHolder keyHolder = new GeneratedKeyHolder();
template.update(sql, param, keyHolder);

BeanPropertySqlParameterSource 가 많은 것을 자동화 해주기 때문에 가장 좋아보이지만,
BeanPropertySqlParameterSource 를 항상 사용할 수 있는 것은 아니다.

update() 에서는 SQL에 :id 를 바인딩 해야 하는데, update() 에서 사용하는 ItemUpdateDto 에는 itemId 가 없다. 따라서 BeanPropertySqlParameterSource 를 사용할 수 없고,
대신에 MapSqlParameterSource 를 사용했다.

별칭

select item_name 의 경우 setItem_name() 이라는 메서드가 없기 때문에 골치가 아프다.
이런 경우 개발자가 조회 SQL을 다음과 같이 고치면 된다.

select item_name as itemName

별칭 as 를 사용해서 SQL 조회 결과의 이름을 변경하는 것이다.

관례의 불일치

자바 객체는 카멜( camelCase ) 표기법을 사용한다.
반면에 관계형 데이터베이스에서는 주로 언더스코어를 사용하는 snake_case 표기법을 사용한다.
BeanPropertyRowMapper 는 언더스코어 표기법을 카멜로 자동 변환해준다.
따라서 select item_name 으로 조회해도 setItemName() 에 문제 없이 값이 들어간다.
컬럼 이름과 객체 이름이 완전히 다른 경우에는 조회 SQL에서 별칭을 사용하면 된다.

JdbcTemplate - SimpleJdbcInsert

INSERT SQL를 직접 작성하지 않아도 되도록 SimpleJdbcInsert 라는 편리한 기능을 제공한다.

Repository

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

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

• jdbcInsert.executeAndReturnKey(param) 을 사용해서 INSERT SQL을 실행

JdbcTemplate 기능 정리

• JdbcTemplate
  순서 기반 파라미터 바인딩을 지원한다.
• NamedParameterJdbcTemplate
  이름 기반 파라미터 바인딩을 지원한다. (권장)
• SimpleJdbcInsert
  INSERT SQL을 편리하게 사용할 수 있다.
• SimpleJdbcCall
  스토어드 프로시저를 편리하게 호출할 수 있다.

JdbcTemplate 사용법 정리

조회

단건 조회 - 숫자 조회

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

객체 하나를 조회한다.
결과를 객체로 매핑해야 하므로 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() 를 사용하면 된다. 결과를 리스트로 반환한다.

변경(INSERT, UPDATE, DELETE)

데이터를 변경할 때는 jdbcTemplate.update() 를 사용하면 된다.
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을 실행할 때는 execute() 를 사용하면 된다. 테이블을 생성하는 DDL에 사용할 수 있다.

DDL

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

스토어드 프로시저 호출

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