[스프링 DB 2편] #2. 데이터 접근 기술 - Spring JdbcTemplate(2)

[Pracrobo]

JdbcTemplate - 이름 지정 파라미터1

순서대로 바인딩

String sql = "update item set item_name =?, price =?  quantity= ?  where id =?";
template.update(sql, itemName, price, quantity, itemId);

여기서는 itemName, price, quantity가 SQL에 있는 ?d에 순서대로 바인딩 된다. 순서만 잘 지키면 문제될것은 없다. 문제는 변경시점에 발생한다.

만약 순서를 다르게 한다면?

실무에서 파라미터가 10~20개가 넘어가는 일도 잦은데 필드를 추가하거나 수정하면서 이런 문제가 발생하게 되고 가장 고치기 힘든 버그인 데이터베이스에 데이터가 잘못들어가는 버그가 생긴다.

이름 지정 바인딩

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

JdbcTemplateItemRepositoryV2

package hello.itemservice.repository.jdbctemplate;

import hello.itemservice.domain.Item;
import hello.itemservice.repository.ItemRepository;
import hello.itemservice.repository.ItemSearchCond;
import hello.itemservice.repository.ItemUpdateDto;
import lombok.extern.slf4j.Slf4j;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.util.StringUtils;

import javax.sql.DataSource;
import java.sql.PreparedStatement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Optional;

/**
 * NamedParameterJdbcTemplate
 * SqlParameterSource
 * - BeanPropertySqlParameterSource
 * - MapSqlParameterSource
 * Map
 *
 * BeanPropertyRowMapper
 *
 */
@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 변환 지원
    }
}

기본

• JdbcTemplateItemRepositoryV2는 ItemRepository 인터페이스를 구현했다.
• this.template = new NamedParameterJdbcTempate(dataSource)
  • NamedParametrJdbcTemplate도 내부에 dataSource가 필요하다
  • JdbcTemplateItemRepositoryV2생성자를 보면 의존관계 주입은 dataSource를 받고 내부에서 NamedParameterJdbcTemplate 을 생성해서 가지고 있다. 스프링에서는 JdbcTemplate 관련 기능을 사용할때 관례상 이 방법을 많이 사용한다.
  • 물론 NameParameterJdbcTemplate을 스프링 빈으로 직접 등록하고 주입받아도 된다.

save()

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

insert into item(item_name, price, quantity) " + " value(:itemName, :price, :quantity)"

추가로 NamedParameterJdbcTempate은 데이터베이스가 생성해주는 키를 매우 쉽게 조회하는 기능도 제공해준다.

JdbcTemplate - 이름 지정 파라미터2

이름 지정 파라미터

‘Map’ 처럼 key, value 데이터 구조를 만들어서 전달해야 한다.

여기서 key는 :파라미터 이름 으로 지정한 파라미터의 이름이고 value는 해당 파라미터의 값이 된다.

다음 코드를 보면 이렇게 만든 파라미터’param’을 전달하는 것을 확인할 수 있다.

• Map
• SqlParameterSource
  • MapSqlParameterSource
  • BeanPropertySqlParameterSource

1. Map

단순히 Map을 사용한다.

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

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

1. MapSqlParameterSource

Map과 유사한데 SQL타입을 지정할 수 있는 등 SQL에 좀 더 특화된 기능을 제공한다.

SqlParamterSource 인터페이스의 구현체이다.

MapSqlParameterSource는 메서드 체인을 통해 편리한 사용법도 제공한다.

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

SqlParameterSource param = new MapSqlParamterSource()
	.addValue("itemName", updateParam.getItemName())
	.addValue("price", updateaParam.getPrice())
	.addValue("quantity", updateParam.getQuantity())

1. BeanProPertySqlSource

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

예 ) getXxx() → xxx, getItemName() → itemName

예를 들어 getItemName(), getPrice() 가 있으면 다음과 같은 데이터를 자동으로 만들어내낟.

• key = itemName, value=상품명 값
• key = price, value = 가격 값

SqlParameterSource 인터페이스의 구현체이다.

save(), findAll() 코드에서 확인 할 수 있다.

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

• 여기서 보면 ‘BeanPropertySqlParameterSource’가 많은 것을 자동화해주기 때문에 가장 좋아보이지만 ‘BeanPropertySqlParameterSource`를 항상 사용할 수 있는 것은 아니다.
• 예를 들어서 update() 에서는 SQL에 :id 를 바인딩해야 하는데 update() 에서 사용하는 ItemUpdataeDto에는 itemId 가 없다. 따라서 BeanPropertySqlParameterSource를 사용할 수 없고 대신에 MapSqlParameterSource를 사용했다.

BeanPropertyRowMapper

이번 코드에서 V1과 비교해서 변화된 부분이 하나 더 있다. 바로 BeanPropertyRowMapper를 사용한 것이다.

JdbcTemplateItemRepositoryV1 - itemRowMapper()

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

JdbcTemplateItemRepositoryV2-ItemRowMapper()

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

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

예를 들어서 데이터 베이스에서 조회한 결과가 select id, price 라고 하면 다음과 같은 코드를 작성해준다.(실제로는 리플렉션 같은 기능을 사용한다.

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

데이터 베이스에서 조회한 결과 이름을 기반으로 setId(), setPrice()처럼 자바빈 프로퍼티 규약에 맞춘 메서드를 호출하는 것이다.

별칭

그런데 select item_name의 경우 setItme_name()이라는 메서드가 없기 때문에 골치가 아프다.

이런 경우 개발자가 조회 SQL을 다음과 같이 고치면 된다.

select item_name as itemName

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

특히 데이터 베이스 컬럼 이름과 객체 이름이 완전히 다를때 문제를 해결할 수 있다

예를 들어 데이터 베이스에는 ‘member_name’이라고 되어있는데 객체에 username이라고 되었다면

select member_name as username

이렇게 데이터베이스 컬럼 이름과 객체의 이름이 다를대 별칭을 사용해서 문제를 많이 해결한다.

JdbcTemplate 은 물론이고 MyBatis같은 기술에서도 자주 사용되낟.

관례의 불일치

자바 객체는 카멜표기법을 사용한다.

반면에 관계형데이터베이스에서는 주로 언더스코어를 사용하는 snake_case표기법을 사용한다.

item_name처럼 중간에 언더스코어를 사용하는 표기법이다.

이부분을 관례로 많이 사용하다보니 BeanPropertyRowMapper는 언더스코어 표기법을 카멜로 자동 변환해준다.

따라서 select item_name으로 조회해도 setItemName()에 문제없이 값이 들어간다.

정리하면 snake_case는 자동으로 해결되니 그냥 두면 되고 컬럼 이름과 객체 이름이 완전히 다른 경우에는 조회 SQL에서 별칭을 사용하면 된다.

JdbcTemplate - 이름 지정 파라미터3

package hello.itemservice.config;

import hello.itemservice.repository.ItemRepository;
import hello.itemservice.repository.jdbctemplate.JdbcTemplateItemRepositoryV2;
import hello.itemservice.service.ItemService;
import hello.itemservice.service.ItemServiceV1;
import lombok.RequiredArgsConstructor;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;

import javax.sql.DataSource;

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

}

@Slf4j
@Import(JdbcTemplateV2Config.class)
@Import(V2Config.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 - SimpleJdbcInsert

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

package hello.itemservice.repository.jdbctemplate;

import hello.itemservice.domain.Item;
import hello.itemservice.repository.ItemRepository;
import hello.itemservice.repository.ItemSearchCond;
import hello.itemservice.repository.ItemUpdateDto;
import lombok.extern.slf4j.Slf4j;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.util.StringUtils;

import javax.sql.DataSource;
import java.util.List;
import java.util.Map;
import java.util.Optional;

/**
 * SimpleJdbcInsert
 */
@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"); //PK
//                .usingColumns("item_name", "price", "quantity"); //생략 가능 meta 데이터를 읽고 자동으로 인지할 수있다
    }

    @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 변환 지원
    }
}

기본

• JdbcTemplateItemRepositorV3 은 ItemRepository인터페이스를 구현했다.
• this.jdbcInsert = new SimpleJdbcInsert(dateSource) : 생성자를 보면 의존관계 주입은 dataSource를 받고 내부에서 SimpleJdbcInsert 을 생성해서 가지고 있다. 스프링에서는 JdbcTemplate관련 기능을 사용할때 관례상 이 방법을 많이 사용한다.
  • 물론 SimpleJdbcInsert를 스프링 빈으로 직접 등록하고 주입 받아도 된다.

SimpleJdbcInsert

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

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

“SimpleJdbcInsert”는 생성 시점에 데이터베이스 테이블의 메타 데이터를 조회한다. 따라서 어떤 컬럼이 있는지 확인할 수 있으므로 usingColumns을 생략할 수 있다.

만약 특정 컬럼만 지정해서 저장하고 싶다면 usingColumns를 사용하면 된다.

애플리케이션을 실행하보면 SimpleJdbcInsert가 어떤 INSERT SQL을 만들어서 사용하는지 로그로 확인할 수 잇다.

save()

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

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

나머지는 코드가 같다.

@Slf4j
@Import(JdbcTemplateV3Config.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 기능 정리

주요기능

JdbcTemplate

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

NamedParameterJdbcTemplate

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

SimpleJdbcInsert

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

SimpleJdbcCall

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

스토어드 프로시저를 사용하기 위한 SimpleJdbcCall에 대한 자세한 내용은 스프링 공식 메뉴얼을 참고하자

JdbdcTemplate 사용법정리

spring 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 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"));
	acotr.setLastName(resultSet..getString("last_name"));
	return actor;
};

public List<Actor> findAllActors() {
	return this.jdbcTemplate.queryt("select first_name, last_name from t_actor", actorRowMapper);
	}

여러 로우를 조회할땐 query()를 사용하면 된다. 결과를 리스트로 반환한다.

여기서는 ‘RowMapper`를 분리했다. 이렇게 하면 여러곳에서 재사용할 수 있다.

변경

데이터를 변경할때는 jdbcTemplate.update()를 사용하면 된다. 참고로 int 반환값을 반환하는데 SQL실행 결과에 영향받은 로우수를 반환한다.

등록

jdbcTemplate.update("insert into t_actor (first_name, last_name) vlaues (?, ?)" , "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));

기타 기능

execute()를 사용해 실행하면 테이블을 생성하는 DDL에 사용할 수 있다

DDL

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

스토어드 프로시저 호출

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

정리

실무에서 가장 간단하고 실용적인 방법으로 SQL을 사용하려면 JdbcTemplate 을 사용하면 된다.

JPA와 같은 ORM기술을 사용하면서 동시에 SQL을 직접 작성해야 할때가 있는데 그때도 JdbcTemplate를 함께 사용하면 된다. 그런데 JdbcTemplate 의 단점은 동적쿼리를 작성하는것이 엄청 복잡하다는 것이다.

동적 쿼리 문제를 해결하면서 SQL을 작성할수 있도록 하는 것이 MyBatis다.