[Spring] JdbcTemplate

JDBC

JDBC가 무엇인지 알기위해서는 JDBC가 어떤이유로 탄생했는지에 대해 먼저 알아보자
웹 서비스의 동작방식

사용자인 클라이언트가 웹 브라우저를 통해 데이터를 사용하기위해 어떤 요청을 보내면 애플리케이션 서버는 요청한 데이터를 데이터베이스로부터 꺼내어 클라이언트에게 응답하는 방식이 가장 기초적인 웹 서비스의 기본동작 방식이다.
이 때 애플리케이션 서버가 데이터베이스로부터 데이터를 받기위해 아래 세 가지 동작이 필요하다

1. Connection: 서버와 DB를 연결, 이 때 네트워크 통신은 TCP/IP를 사용한다
2. SQL전달: 원하는 데이터를 받기위해 DB가 이해할 수 있는 SQL을 전달한다.
3. 응답: DB에서 요청받은 SQL을 수행하여 결과로 나온 데이터를 애플리케이션 서버에 전달한다

여기서 문제가 생기는데,

1. Connection과정에서 연결하는 방법이 데이터베이스마다 다르다는 점
2. SQL문장을 전달하는 방법이 다르다는 점
3. 결과를 받는 방법이 다르다는 점
4. 1,2,3의 과정이 데이터베이스마다 다 다른데 이런 데이터베이스가 수십개 넘게 있다는 점
   이렇게 되면 만약 내가 MysqlDB를 사용하다가 OracleDB로 변경해야한다면 애플리케이션의 Connection과정, SQL전달 과정, 응답받는과정 등 모든 코드를 수정해야하는 불상사가 발생한다.

이 문제를 어떻게 해결할 수 있을까?
지금까지 Java를 열심히 공부했다면 얼추 떠오르는 개념이 하나 있는데 바로 인터페이스를 이용하여 다형성을 통해 사용방법을 통일화시키는 것이다.

실제로 이 방법으로 위의 문제들을 해결하였으며 그 인터페이스를 바로 JDBC라고 한다.

JDBC는 Java Database Connectivity의 약자로 자바에서 데이터베이스에 접속할 수 있도록 하는 자바 API이다.
위에서 다루었던 1,2,3번 각각의 문제에 대해 아래의 표준 인터페이스를 제공한다.

1. java.sql.Connection
2. java.sql.Statement
3. java.sql.ResultSet
   이제 이 JDBC(인터페이스)에 내가 사용하고자하는 데이터베이스(구현체)를 연결하여 사용하면된다.
   이 때 JDBC와 데이터베이스를 연결시켜주는 것을 JDBC드라이버라고 한다.
   예를 들어서 내가 Mysql을 사용한다면 Mysql JDBC드라이버를 연결해야하고 Oracl을 사용한다면 Orcla JDBC드라이버를 연결해야한다.
   각 데이터베이스의 JDBC드라이버는 각각의 회사에서 JDBC를 구현한 구현체이며,
   com.mysql.cj.jdbc.Driver와 같은 클래스로 제공한다.

JDBC인터페이스와 그에 맞는 각 벤더의 구현체 드라이버를 사용함으로써 애플리케이션은 JDBC표준에만 의존하기때문에 만약 Oracle을 사용하다가 Mysql로 넘어가더라도 애플리케이션 코드의 변경을 최소화 할 수 있으며, SQL문법만 변경해주면 편리하게 사용할 수 있게되었다.

---

## JDBC사용하기

JDBC를 사용하여 H2 데이터베이스와 연결
DriverManager.getConnection()메서드를 사용하면 URL에 담겨있는 정보를 읽어 프로젝트 라이브러리에서 해당 드라이버가 제공하는 커넥션을 반환한다.

@Slf4j  
public class DBConnectionUtil {  

    public static Connection getConnection() {  
        try {  
            Connection connection = DriverManager.getConnection(URL, USERNAME, PASSWORD);  
            log.info("get connection = {}, class = {}", connection, connection.getClass());  
            return connection;  
        } catch (SQLException e) {  
            throw new IllegalStateException(e);  
        }  
    }  
}

현재 connection변수에는 H2데이터베이스의 Connection객체가 담겨있는 것이다.
connection class org.h2.jdbc.JdbcConnection - connection변수 조회

JDBC는 Connection인터페이스를 정의해놓았고, H2, Mysql등등 데이터베이스들은 JDBC에서 정의한 이 Connection을 구현한 것이다.

커넥션을 연결하는과정에서 DriverManager를 사용해야하는데,
DriverManage는 라이브러리에 등록된 DB드라이버들을 관리하고, 커넥션을 흭득하는 기능을 제공한다.

이제 JDBC를 사용하여 데이터베이스에 실제 데이터를 저장해보자
String sql = "insert into member(member_id, money) values(?,?)";
먼저 데이터를 삽입하는 SQL문을 작성한다. ?는 후에 실제 저장될 데이터와 매핑되어 처리된다.
이 때 매핑 기준은 등록하는 컬럼의 순서와 일치시켜야된다.

• Connection con = null; - Connection을 생성해주는데 null로 생성해 둔 이유는 사용한 리소스를 후에 정리하기위해서 null로 선언만 해둔다.
• PreparedStatement pstmt = null; - PreparedStatement는 SQL을 데이터베이스에 전달하는 역할을 한다.
• ResultSet rs = null; - ResultSet은 전달한 SQL의 결과데이터를 응답으로 받아 저장해두는 객체이다.

데이터 저장 - save()

public Member save(Member member) throws SQLException {  
    String sql = "insert into member(member_id, money) values(?,?)";  

    Connection con = null;  
    PreparedStatement pstmt = null;  

    try {  
        con = getConnection();  
        pstmt = con.prepareStatement(sql);  
        pstmt.setString(1, member.getMemberId());  
        pstmt.setInt(2, member.getMoney());  
        pstmt.executeUpdate();  
        return member;  
    } catch (SQLException e) {  
        log.error("db error", e);  
        throw e;  
    } finally {  
        close(con, pstmt, null);  
    }  
}

데이터 저장 실행코드

MemberRepositoryV0 repository = new MemberRepositoryV0();  

@Test  
void save() throws SQLException {  
    Member member = new Member("memberV1", 10000);  
    repository.save(member);  
}

데이터 조회 findById()

public Member findById(String memberId) throws SQLException {  
    String sql = "select * from member where member_Id = ?";  

    Connection con = null;  
    PreparedStatement pstmt = null;  
    ResultSet rs = null;  

    try {  
        con = getConnection();  
        pstmt = con.prepareStatement(sql);  
        pstmt.setString(1, memberId);  
        rs = pstmt.executeQuery();  
        if (rs.next()) {  
            Member member = new Member();  
            member.setMemberId(rs.getString("member_id"));  
            member.setMoney(rs.getInt("money"));  
            return member;  
        } else{ //받은 데이터에서 찾지 못했을 때  
            throw new NoSuchElementException("member not found memberId= " + memberId);  
        }  
    } catch (SQLException e) {  
        log.error("db error", e);  
        throw e;  
    } finally {  
        close(con, pstmt, rs);  
    }  
}

데이터를 조회한다는 것은 조회할 데이터를 반환받는다는 의미이다.
때문에 데이터의 조회과정에서는 SQL문 실행결과의 데이터를 반환받아야하는데 이 때 ResultSet을 통해 객체를 반환받는다.

if (rs.next()) {  
    Member member = new Member();  
    member.setMemberId(rs.getString("member_id"));  
    member.setMoney(rs.getInt("money"));  
    return member;  
}

ResultSet객체를 순회하며 Member객체에 데이터를 바인딩하는 코드이다.
이 때 ResultSet에서 데이터를 꺼내는 기준은 테이블의 컬럼명을 기준으로하여 꺼낸다.

---

JdbcTemplate

SQL을 직접 작성하여 사용하는 경우에는 JdbcTemplate을 유용하게 사용할 수 있다.
JdbcTemplate은 JDBC를 매우 편리하게 사용할 수 있도록 도와준다.

장점

• 설정이 편리함
• 반복 문제해결
  • JdbcTemplate은 템플릿 콜백 패턴을 사용해서 JDBC를 직접 사용할 때 발생하는 반복작업을 대신처리해준다.
  • 개발자는 SQL작성과 전달할 파라미터를 정의하고, 결과로나온 응답값을 매핑해주기만 하면된다

단점

• 동적 SQL을 해결하기가 어렵다. 자바의 String으로 일일이 짜줘야한다.

---

#### JDBC 사용해보기

private final JdbcTemplate template;

public JdbcTemplateItemRepositoryV1(DataSource dataSource) {  
    this.template = new JdbcTemplate(dataSource);  
}

JdbcTemplate을 사용할 때는 DataSource를 직접 설정해주어야한다.
스프링을 사용한다면 관례상 생성자를 통해 DataSource를 주입해준다.
스프링 빈을 따로 생성하여 주입해줄수도 있다.

save()

@Override  
public Item save(Item item) {  
    String sql = "INSERT INTO item (item_name, price, quantity) VALUES (?, ?, ?)";  

    //PK ID값  
    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;  
}

• 데이터를 변경할 때는 update()를 사용한다
• 기존 JDBC를 사용했던것과 같이 SQL은 ?를 사용하여 파라미터를 바인딩 해준다.
• KeyHolder는 PK인 id컬럼을 가져오는데 사용한다.
• PreparedStatemnt를 통해 SQL에 동적으로 들어가는 변수(?)를 바인딩해준다.

update()

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

• JdbcTeamplte에서 PreparedStatement로 파라미터 변수에 매핑하는 과정을 내부적으로 처리해준다
  • save()메서드에서는 KeyHolder를 사용하기위해서 명시적으로 사용해주어야함

findById()

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

• 결과 로우가 하나일 때는 queryForObject()를 사용한다.
  • 결과가 없으면 EmptyResultDataAccessException예외를 반환
  • 결과가 둘 이상이면 InccrectResultSizeDataAccessException예외를 반환
• 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;  
    };  
  }

• 조회하고자하는 데이터를 DB로부터 가져와서 Item객체에 값을 주입한 뒤 반환해준다.

findAll()

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

동적 쿼리를 작성하는 부분으로 여기서 JdbcTemplate의 단점이 나온다.
매우 복잡한 자바문법으로 동적쿼리를 복잡하게 작성하는 것을 확인할 수 있다.
->JdbcTemplate은 어쩔수 없이 위와같이 복잡하게 동적쿼리를 제공해야한다. MyBatis를 사용하면 이런 복잡한 동적쿼리 문제를 비교적 간단하게 사용할 수 있다.

---

JdbcTemplate의 주의할점과 해결방법
위의 코드에서 SQL쿼리를 보면 ?를 사용하여 파라미터를 바인딩하는 것을 확인할 수 있다.
이 쿼리는 데이터베이스에 직접적으로 연결되며 입력한 쿼리대로 데이터베이스에 저장되고 삭제되기때문에 주의가 필요한데, 만약 파라미터가 10개이상을 넘어가게된다면 순서에 기반하여 ?바인딩을 통해 데이터에 접근할 때 만약 실수로 파라미터 순서를 바꾸어서 데이터를 입력할 경우 심각한 버그가 발생할 수 있다.
이러한 문제를 개선하기위해 JdbcTemplate은 이름 지정 파라미터를 제공한다

NamedParameterJdbcTemplate

NamedParameterJdbcTemplate은 바인딩과정에서 컬럼과 파라미터의 순서에 대한 보장을 해준다.

NamedParameterJdbcTemplate template;

• NamedParameterJdbcTemplate 을 사용하기위한 변수 선언

save()

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

sql을 작성할 때 기존에 ?로 바인딩하던 것을 :itemName과 같이 이름으로 명확하게 바인딩해주는 것을 확인할 수 있다.

뿐만아니라 NamedParameterJdbcTemplate은 데이터베이스에서 생성되는 키를 매우 쉽게 조회하는 기능도 제공해준다.

기존에는 ?를 통해 바인딩을 하여 자바코드에서 직접적으로 SQL에 들어가는 파라미터에 대하여 직접적으로 바인딩을 해주었지만,
NamedParameterJdbcTemplate을 사용할 때는 SQL에 파라미터를 전달하기위해서 Map과 같은 key,value데이터 구조를 직접 만들어서 전달해야한다.
여기서 key에 들어가는 값은 SQL에 대해 데이터베이스의 컬럼을 의미하고
value는 사용자로부터 입력되는 실제 객체의 값이라고 할 수 있다.

이렇게 만든 Map구조(이하 param)을
template.update(sql, param, keyHolder)와 같이 파라미터와 SQL을 전달하고
NamedParameterJdbcTemplate내부에서 SQL에 대해 동적으로 바인딩을 처리해주고 쿼리를 실행하여 데이터베이스에 접근하는 것이다.

이렇게 이름지정 바인딩에서 자주사용하는 파라미터(Map구조)에는 크게 3가지가 있다.

• Map
• SqlParameterSource(interface)
  • MapSqlParameterSource
  • BeanPropertySqlParameterSource

Map
Map은 단순히 컬렉션 프레임워크의 Map을 사용하여 전달하는 것이다.

@Override  
public Optional<Item> findById(Long id) {  
    String sql = "select id, item_name, price, quantity from item where id = :id";  
        Map<String, Object> param = Map.of("id", id);  
        Item item = template.queryForObject(sql, param, itemRowMapper()); 
}

Map.of를 통해 생성된 Map의 key에는 컬럼을 의미하는 id가 들어가게되고,
value에는 findById(Long id)에서 받은 실제 객체의 id값이 들어가게 된다.
이를 통해 where id = : id에 메서드 파라미터로 받은 id가 조건절에 들어가게 된다.

MapSqlParameterSource
Map과 유사하지만 SQL에 특화된 기능을 좀 더 제공한다.

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

• 메서드 체인을 제공하여 가독성 좋고 편리한 사용을 제공한다
  쿼리가 적용되는 과정을 살펴보자

1. item_name이라는 DB컬럼에 대해서 itemName으로 치환
2. addValue("itemName", updateParam.getItemName())치환된 itemName에 실제 객체 값을 대입
3. 대입된 값이 DB의 item_name컬럼에 들어가게 됨

BeanPropertySqlParameterSource

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

• 코드에서 알 수 있듯이BeanPropertySqlParameterSource은 많은 것을 자동화 해주기 때문에 가장 유용하게 사용할 순 있지만 항상 사용할 수는 없다.
• 예를 들어 update()의 경우에 updateDto를 따로 받아주고 있고 객체 내부에 id값을 가지고 있지 않다. save()의 파라미터로 Item타입의 객체 자체를 받고있기 때문에 이럴 경우에는 BeanPropertySqlParameterSource을 사용할 수 없다.

BeanPropertyRowMapper
추가적으로 BeanPropertyRowMapper는 SQL을 수행하고 DB로부터 받은 결과를 실제 Java에서 사용하는 객체로 치환해주는 역할을 수행하며 결과타입으로 RowMapper타입을 반환한다.

private RowMapper<Item> itemRowMapper() {  
    return BeanPropertyRowMapper.newInstance(Item.class);  
}

이전에는 아래와 같이 직접 Item객체에 필드값들을 매핑해주었지만 BeanPropertyRowMapper를 사용하여 자동화 해주었다.
이 때 데이터베이스에서 조회한 결과 이름을 기반으로 자바빈 프로퍼티 규약에 맞춘 메서드를 호출하게 된다.

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

이 때 직접 객체를 매핑해주던 코드에서는 데이터베이스 컬럼으로 등록된 "item_name"을 직접 작성해주었기때문에 상관없지만 BeanPropertyRowMapper.newInstance를 사용할 경우에 주의할 점이한가지 있는데,
Item객체의 상품명 필드에 대해 itemName으로 선언되어있지만 데이터베이스에는 item_name으로 선언되어 있다. 이 때 자동화하는 과정에서 setItem_name()이라는 메서드를 찾기 때문에 문제가 발생한다. 이 경우에는 SQL문에 개입해서 as문법을 사용해 컬럼에 별칭을 주어야한다.
Ex)item_name as itemName
이 방법은 카멜케이스와 스네이크 케이스의 차이점을 보완해줄 뿐만아니라, 객체의 필드명과 DB컬렴명의 이름이 아예 다른 경우도 as를 통해 이름을 맞춰줄 수 있다.

참고로 BeanPropertyRowMapper에서는 스네이크 케이스를 카멜케이스로 자동 변환해주기때문에 개발자는 username과 memberName과 같이 아예 다른 이름의 불일치에 대해서만 as 처리해주면 된다.

---

SimpleJdbcInsert

JdbcTemplate은 INSERT문에 대해서 SQL을 직접 작성하지 않아도 되는 SimpleJdbcInsert라는 편리한 기능을 제공한다.

SimpleJdbcInsert jdbcInsert;

public JdbcTemplateItemRepositoryV3(DataSource dataSource) {  
    this.jdbcInsert = new SimpleJdbcInsert(dataSource)  
            .withTableName("item")  
            .usingGeneratedKeyColumns("id");  
}

SimpleJdbcInsert도 NamedParameterJdbcTemplate과 마찬가지로 DataSource를 필요로한다.

• INSERT하고자하는 테이블의 이름을 withTableName()으로 명시한다.
• 테이블의 자동생성 키컬럼을 usingGeneratedKeyColumns()메서드로 명시한다.

이제 아래와 같이 INSERT SQL문을 작성하지 않아도되며 KeyHolder를 통해 받던 키값도 간단하게 받을 수 있다.

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

추가로 usingColujmns()를 사용하면 INSERT SQL에 사용할 컬럼을 지정할 수 있다. 특정 값만 저장하고 싶을 때 사용한다.
Ex)usingColumns("item_name", "price", "quantity")

---

정리

• JdbcTemplate
  • 순서 기반 파라미터 바인딩을 지원
• NamedParameterJdbcTemplate
  • 이름 기반 파라미터 바인딩을 지원(권장)
• SimpleJdbcInsert
  • INSERT SQL을 편리하게 사용
• SimpleJdbcCall
  • 스토어드 프로시저를 편리하게 호출(공부)
  • https://docs.spring.io/spring-framework/docs/current/reference/html/data-access.html#jdbc- simple-jdbc-call-1

스프링 JdbcTemplate 사용방법 공식 메뉴얼
https://docs.spring.io/spring-framework/docs/current/reference/html/data-access.html#jdbc- JdbcTemplate

실무에서 가장 간단하고 실용적인 방법으로 SQL을 사용하려면 JdbcTemplate을 사용하면 된다.
JPA와 같은 ORM기술을 사용하면서 동시에 SQL을 직접 작성해야 할 때가 있는데 그 때도 JdbcTemplate을 함께 사용한다.

참고로 JOOQ라는 기술로 JDBC의 단점인 동적쿼리를 해결해줄수는 있다.