2. 데이터 접근 기술 - 스프링 JdbcTemplate

1. JdbcTemplate 소개와 설정

1.1. 장점

1. 설정의 편리함
   • JdbcTemplate은 spring-jdbc library에 포함되어 있는데, 이 library는 spring으로 JDBC 사용시, 기본으로 사용되는 library여서, 복잡한 설정없이 바로 사용가능
2. 반복 문제 해결
   • JDBC 사용할 때 발생하는 대부분의 반복 작업을 대신 처리해준다.
   • SQL 작성하고, 전달할 params만 정의하고, 응답 값을 매핑만 하면된다.
   • 처리하는 반복 작업
     • Connection 획득, statement 준비 및 실행, 결과를 반복하도록 루프 실행, 컨넥션-statement-resultset 종료, transection, 스프링 예외 변환기 실행

 

1.2. JdbcTemplate 설정

• jdbc만 추가하면 JdbcTemplate이 들어있는 spring-jdbc가 library에 포함된다.

repositories {
   mavenCentral()
}

dependencies {
   ...
   // JdbcTemplate 추가
   implementation 'org.springframework.boot:spring-boot-starter-jdbc'
   // H2 데이터베이스 추가
   runtimeOnly 'com.h2database:h2'
   //테스트에서 lombok 사용
   testCompileOnly 'org.projectlombok:lombok'
   testAnnotationProcessor 'org.projectlombok:lombok'
}

 

2. JdbcTemplate 적용1 - 기본

• 완전 날것 방식 - 권장 안함

package hello.itemservice.repository.jdbctemplate;

@Slf4j
public class JdbcTemplateItemRepositoryV1 implements ItemRepository {
    private final JdbcTemplate template;

    // 관례적인 DI
    // jdbc template은 생성자에 datasource를 받는다.
    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 (?,?,?)";

        // db에서 생성한 Id 값을 받아오는 방법
        // 1. keyHolder 2. connection.prepareStatement(sql, new String[]{"id"}) 2가지 이용
        // Insert 쿼리 실행 이후에 db에서 생성된 ID 값을 조회할 수 있다.

        GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();

        // connection, stateMent
        template.update(
                // id 값 자동 생성때문에 connection하는 과정이 복잡해짐
                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 = Objects.requireNonNull(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 {
            // sql은 sql 쿼리문, itemRowMapper()는 rs로부터 받아온 값 객체로 저장, id는 ?에 들어가는 값
            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) {
            // 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);

        // 결과가 1개 이상인 경우 ResultSet을 객체로 변환한다.
        List<Item> itemList = template.query(sql, itemRowMapper(), param.toArray());
        return itemList;
    }

    // RowMapper : db 반환 결과인 ResultSet을 객체로 변환 -> 내부에선 resultSet이 끝날때까지 while문 loop를 돌린다.
    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;
        };
    }
}

 

• 핵심
  
  1. 생성자
     • 생성자를 보면 dataSource 를 의존 관계 주입 받고 생성자 내부에서 JdbcTemplate 을 생성 스프링에서는 JdbcTemplate 을 사용할 때 관례상 이 방법을 많이 사용
       
       
  2. template.update()
     • Insert, Update, Delete SQL에 사용한다.
     • 해당 method의 반환 값은 int 이지만, 영향 받은 row 수를 반환한다.
       
       
  3. KeyHolder & connection.prepareStatement(sql, new String[]{"id"})
     • id를 지정해서 Insert queary 실행 이후 db에서 생성된 ID 를 조회해서 Item 수행
       
       
  4. template.queryForObject()
     • select 하는 값이 1개 일 때 사용
       
       
  5. RowMapper
     • ResultSet -> Object로 변환
       
       
  6. template.query()
     • data를 list로 조회

 

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

 

• sql 을 직접 작성하는 부분인 동적 쿼리문이 어렵다고 하지만 실제 대기업 혹은 data가 많이 축적된 곳은 쿼리문을 통해서 data를 선별할 수 밖에 없다. 따라서 dbTool을 통해서 먼저 queary logic이 잘 수행되는지 확인하고 spring의 queary문을 작성하여 동작하는 것을 잘 연습해야한다.
  -> sql을 잘 할 줄 알아야한다. 백엔드 개발자의 기본 소양이다.

 

• 다행인 점은 MyBatis에서 SQL을 직접 사용할 때 동적 쿼리를 쉽게 작성할 수 있다는 점이다.

 

 

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

 

4.1. DataSource

• 이미 spring이 해당 datasource를 등록했기 때문에 설정파일에 등록할 부분만 작성하면 된다.

# 현재 local 환경으로 설정
spring.profiles.active=local

# db = dataSource 설정 등록 방법
# @Config으로 등록하지 않는 이유는 이미 spring에서 구현한 DataBase(hikari)가 존재하기 때문이다.

spring.datasource.url=jdbc:h2:tcp://localhost/~/test
spring.datasource.username=sa
# password가 없는 경우 생략가능하지만 그냥 작성
spring.datasource.password=

# jdbcTemplate sql log 설정
logging.level.org.springframework.jdbc=debug

 

4.2. Config 파일

package hello.itemservice.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);
    }
}

 

4.3. ItemServiceApplication

 

package hello.itemservice;

// 현재 componentScan의 영역이 web 이하이므로 @Import로 수동 bean 등록내용을 받아와야한다.
@Import(JdbcTemplateV1Config.class)
@SpringBootApplication(scanBasePackages = "hello.itemservice.web")
public class ItemServiceApplication {

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

   // application.properties의 설정 중 local 환경일 경우 @Bean으로 등록한다.
   @Bean
   @Profile("local")
   public TestDataInit testDataInit(ItemRepository itemRepository) {
      return new TestDataInit(itemRepository);
   }
}

 

 

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

 

• NamedParameterJdbcTemplte : 권장하는 사용 방식

package hello.itemservice.repository.jdbctemplate;

/**
 * NamedParameterJdbcTemplate
 *
 * 아래 3가지 방식 중 원하는 방식으로 사용 가능
 * 1. SqlParameterSource
 * - 1.1. BeanPropertySqlParameterSource
 * - 1.2. MapSqlParameterSource
 * 2. Map
 *
 * BeanPropertyRowMapper
 *
 */

@Slf4j
public class JdbcTemplateItemRepositoryV2 implements ItemRepository {
    private final NamedParameterJdbcTemplate template;

    // 관례적인 DI
    // jdbc template은 생성자에 datasource를 받는다.
    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)";

// V1
//        template.update(
//                // id 값 자동 생성때문에 connection하는 과정이 복잡해짐
//                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);

        // save method의 parms로 넘오은 item을 매개변수로 받아서 db의 statement 수행
        BeanPropertySqlParameterSource param = new BeanPropertySqlParameterSource(item);
        GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();
        template.update(sql, param, keyHolder);

        // 내가 변경함 - null 가능성을 막아버림
        long key = Objects.requireNonNull(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";
//V1
//        template.update(sql,
//                updateParam.getItemName(),
//                updateParam.getPrice(),
//                updateParam.getQuantity(),
//                itemId);

        SqlParameterSource param = new MapSqlParameterSource()
                .addValue("itemName", updateParam.getItemName())
                .addValue("price", updateParam.getPrice())
                .addValue("quantity", updateParam.getQuantity())
                // id는 updateParams로 부터 받아와야한다.
                .addValue("id", itemId);
        // update를 해줘야함
        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 {
            // 변경된 부분은 try 내부 logic만 변경
            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();

        String sql = "select id, item_name, price, quantity from item";
        BeanPropertySqlParameterSource param = new BeanPropertySqlParameterSource(cond);
        // 동적 쿼리
        if (StringUtils.hasText(itemName) || maxPrice != null) {
            // null로 될 수 있음, 정렬 조건은 필수가 아니기 때문
            sql += " where";
        }
        boolean andFlag = false;

// V1
//        List<Object> param = new ArrayList<>();
        if (StringUtils.hasText(itemName)) {
            sql += " item_name like concat('%',:itemName,'%')";
// V1
//            param.add(itemName);
            andFlag = true;
        }

        if (maxPrice != null) {
            if (andFlag) {
                sql += " and";
            }
            sql += " price <= :maxPrice";
// V1
//            param.add(maxPrice);
        }
        log.info("sql={}", sql);

// V1
//        return template.query(sql, itemRowMapper(), param.toArray());

        // params를 list로 따로 담지 않아도 된다.
        return template.query(sql, param, itemRowMapper());
    }

    // RowMapper : db 반환 결과인 ResultSet을 객체로 변환 -> 내부에선 resultSet이 끝날때까지 while문 loop를 돌린다.
    private RowMapper<Item> itemRowMapper() {
// V1
//        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;
//        };
        return BeanPropertyRowMapper.newInstance(Item.class);
    }
}

 

5.1. 주요 method, class

• 이름 지정 파라미터 - Map 과 같은 데이터 구조를 만들어서 전달
  
  

자주 사용하는 Params 종류

 

1. Map
2. SqlParameterSource
   1. MapSqlParameterSource
   2. BeanPropertySqlParameterSource
      
      

1. Map
   • Map.of("id", id)
   • 단순 map 사용
2. MapSqlParameterSource
   • Map과 유사한데, SQL 타입을 지정할 수 있는 SQL에 좀 더 특화된 기능 제공
     
     
3. BeanPropertySqlParameterSource
   • 자바빈 프로퍼티 규약을 통해서 자동으로 파라미터 객체를 생성한다.
   • 가장 많이 사용되지만 항상 사용할 수 있는 것은 아니다.

 

 

 

BeanPropertyRowMapper

 

• resultSet -> 객체
• 문제점 
  • db 계열은 관례적으로 item_Name 과 같이 snake 형식을 주로 사용한다.
  • java 진영은 대소문자 구분을 할 수 있어 camel 형식을 사용한다.
• 해결
  1. db에서는 as 와 같은 별칭을 사용할 수 있는데 이때, 별칭을 java 객체의 iv로 변형
  2. 관례적인 부분은 BeanPropertyRowMapper 가 snake 방식을 자동으로 camel 방식으로 변환한다.

 

5.2. Config.java , Import

• 이전과 동일하지만 version만 변경

 

6. JdbcTemplate - SimpleJdbcInsert

• Insert sql를 직접 작성하지 않아도 되도록 SimpleJdbcInsert라는 편리한 기능 제공
• SimpleJdbcInsert를 스프링 빈으로 직접 등록하고 주입받아도 되지만 권장하지 않는다.

public class JdbcTemplateItemRepositoryV3 implements ItemRepository {
    private final NamedParameterJdbcTemplate template;
    private final SimpleJdbcInsert jdbcInsert;

    // 관례적인 DI
    // jdbc template은 생성자에 datasource를 받는다.
    public JdbcTemplateItemRepositoryV3(DataSource dataSource) {
        this.template = new NamedParameterJdbcTemplate(dataSource);
        this.jdbcInsert = new SimpleJdbcInsert(dataSource)
                .withTableName("item")
                .usingGeneratedKeyColumns("id"); // pk 값으로부터 id를 받아오기 때문
                    // 생략 가능 - tableName으로부터 값을 받아오기 때문에 이미 알고 있다. - 넣기 싫은 data 존재시 사용
//                .usingColumns("item_name", "price", "quantity");
    }

    @Override
    public Item save(Item item) {
// V2
//        String sql = "insert into item (item_name, price, quantity) " + "values (:itemName, :price, :quantity)";
        // save method의 parms로 넘오은 item을 매개변수로 받아서 db의 statement 수행
//        BeanPropertySqlParameterSource param = new BeanPropertySqlParameterSource(item);
//        GeneratedKeyHolder keyHolder = new GeneratedKeyHolder();
//        template.update(sql, param, keyHolder);
//
//        // 내가 변경함 - null 가능성을 막아버림
//        long key = Objects.requireNonNull(keyHolder.getKey()).longValue();
//        item.setId(key);
//        return item;

// V1
//        template.update(
//                // id 값 자동 생성때문에 connection하는 과정이 복잡해짐
//                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);

        BeanPropertySqlParameterSource param = new BeanPropertySqlParameterSource(item);
        Number key = jdbcInsert.executeAndReturnKey(param); // id(=key)를 반환해야 item에 id를 넣을 수 있게 된다.
        item.setId(key.longValue());
        return item;
    }

 

7. JdbcTemplate 기능 정리

 

7.1. 주요 기능

1. JdbcTemplate
   • 순서 기반 파라미터 바인딩을 지원
2. NamedParameterJdbcTemplate
   • 이름 기반 파라미터 바인딩을 지원 (권장)
3. SimpleJdbcInsert
   • INSERT SQL을 편리하게 사용할 수 있다.
4. SimpleJdbcCall
   • 스토어드 프로시저를 편리하게 호출 가능
   • 사용 공식 메뉴얼 : https://docs.spring.io/spring-framework/docs/current/reference/html/dataaccess.html#jdbc-simple-jdbc-call-1

 

7.2. JdbcTemplate 사용법 정리

 

1. 조회
   1. 단건 조회 - 숫자 조회
      int rowCount = jdbcTemplate.queryForObject("select count(*) from t_actor", Integer.class);
      
      
   2. 단건 조회 - 숫자 조회, 파라미터 바인딩
      int countOfActorsNamedJoe = jdbcTemplate.queryForObject( "select count(*) from t_actor where first_name = ?", Integer.class, "Joe");
      
      
   3. 단건 조회 - 문자 조회
      String lastName = jdbcTemplate.queryForObject( "select last_name from t_actor where id = ?", String.class, 1212L);
      
      
   4. 단건 조회 - 객체 조회
      1개를 조회하여도 결과로 객체를 매핑해야 하므로 RowMapper를 사용
      queryForObject()
      
      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);
      
   5. 목록 조회 - 객체
      query() -> list 반환
      
      List 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;
      });
      
      
2. 변경
   
   1. 등록
      jdbcTemplate.update( "insert into t_actor (first_name, last_name) values (?, ?)", "Leonor", "Watling");
      
   2. 수정
      jdbcTemplate.update( "update t_actor set last_name = ? where id = ?", "Banjo", 5276L);
      
   3. 삭제
      jdbcTemplate.update( "delete from t_actor where id = ?", Long.valueOf(actorId));
      
3. 기타기능
   1. DDL
      jdbcTemplate.execute("create table mytable (id integer, name varchar(100))");
      
4. 스토어드 프로시저 호출
   jdbcTemplate.update( "call SUPPORT.REFRESH_ACTORS_SUMMARY(?)", Long.valueOf(unionId));