Spring JDBC

강정우·2024년 1월 22일

Spring

Spring-boot

목록 보기

61/73

데이터 접근 기술 소개

데이터 접근 기술

1. SQLMapper

SQL Mapper 주요기능

개발자는 SQL만 작성하면 해당 SQL의 결과를 객체로 편리하게 매핑해준다.
JDBC를 직접 사용할 때 발생하는 여러가지 중복을 제거해주고, 기타 개발자에게 여러가지 편리한 기능을 제공한다.

SQLMapper 관련기술

JdbcTemplate
MyBatis

2. ORM

ORM 주요 기능

JdbcTemplate이나 MyBatis 같은 SQL 매퍼 기술은 SQL을 개발자가 직접 작성해야 하지만, JPA를 사용하면 기본적인 SQL은 JPA가 대신 작성하고 처리해준다. 개발자는 저장하고 싶은 객체를 마치 자바 컬렉션에 저장하고 조회하듯이 사용하면 ORM 기술이 데이터베이스에 해당 객체를 저장하고 조회해준다.

JPA는 자바 진영의 ORM 표준이고, Hibernate(하이버네이트)는 JPA에서 가장 많이 사용하는 구현체이다. 자바에서 ORM을 사용할 때는 JPA 인터페이스를 사용하고, 그 구현체로 하이버네이트를 사용한다고 생각하면 된다.

스프링 데이터 JPA, Querydsl은 JPA를 더 편리하게 사용할 수 있게 도와주는 프로젝트이다. 실무에서는 JPA를 사용하면 이 프로젝트도 꼭! 함께 사용하는 것이 좋다.

ORM 관련 기술

JPA, Hibernate
스프링 데이터 JPA
Querydsl

JDBC

장점

1. 설정의 편리함

JdbcTemplate은 spring-jdbc 라이브러리에 포함되어 있는데, 이 라이브러리는 스프링으로 JDBC를 사용할 때 기본으로 사용되는 라이브러리이다. 그리고 별도의 복잡한 설정 없이 바로 사용할 수 있다.

2. 반복 문제 해결

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

커넥션 획득
statement 를 준비하고 실행
결과를 반복하도록 루프를 실행
커넥션 종료, statement , resultset 종료
트랜잭션 다루기 위한 커넥션 동기화
예외 발생시 스프링 예외 변환기 실행

단점

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

구현

v1 기본 with keyHolder

JdbcTemplateItemRepositoryV1 은 ItemRepository 인터페이스를 구현했다.

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

의존관계 주입

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

스프링에서는 JdbcTemplate 을 사용할 때 관례상 이 방법을 많이 사용한다. 물론, JdbcTemplate 을 스프링 빈으로 직접 등록하고 주입받아도 된다.

update query

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

INSERT , UPDATE , DELETE SQL에 사용한다.

template.update() 의 반환 값은 int 인데, 영향 받은 로우 수를 반환한다.

데이터를 저장할 때 PK 생성에 identity (auto increment) 방식을 사용하기 때문에, PK인 ID 값을 개발자가 직접 지정하는 것이 아니라 비워두고 저장해야 한다. 그러면 데이터베이스가 PK인 ID를 대신 생성해준다.
문제는 이렇게 데이터베이스가 대신 생성해주는 PK ID 값은 데이터베이스가 생성하기 때문에, 데이터베이스에 INSERT가 완료 되어야 생성된 PK ID 값을 확인할 수 있다.
KeyHolder 와 connection.prepareStatement(sql, new String[]{"id"}) 를 사용해서 id 를 지정해주면 INSERT 쿼리 실행 이후에 데이터베이스에서 생성된 ID 값을 조회할 수 있다.

물론 데이터베이스에서 생성된 ID 값을 조회하는 것은 순수 JDBC로도 가능하지만, 코드가 훨씬 더 복잡하다.
참고로 뒤에서 설명하겠지만 JdbcTemplate이 제공하는 SimpleJdbcInsert 라는 훨씬 편리한 기능이 있으므로 대략 이렇게 사용한다 정도로만 알아두면 된다.

select query findById()

데이터를 하나 조회한다.

template.queryForObject()
- 결과 로우가 하나일 때 사용한다.
- RowMapper 는 데이터베이스의 반환 결과인 ResultSet 을 객체로 변환한다.
- 결과가 없으면 EmptyResultDataAccessException 예외가 발생한다.
- 결과가 둘 이상이면 IncorrectResultSizeDataAccessException 예외가 발생한다.
ItemRepository.findById() 인터페이스는 결과가 없을 때 Optional 을 반환해야 한다. 따라서 결과가 없으면 예외를 잡아서 Optional.empty 를 대신 반환하면 된다.

queryForObject() 인터페이스 정의

<T> T queryForObject(String sql, RowMapper<T> rowMapper, Object... args) throws DataAccessException;

itemRowMapper()

데이터베이스의 조회 결과를 객체로 변환할 때 사용

while(resultSet 이 끝날 때 까지) {
	rowMapper(rs, rowNum)
}

문제점

동적 쿼리 문제 - 사용자 검색 조건에 따라 query가 바뀌어야한다. 위에서 억지로 짜놓은 코드를 개몬생겼다.
이를 mybatis로 해결할 수 있는데 다음 포스팅에서 알아보자.
또한 파라미터 순서가 바뀌면 ㄹㅇ 개망한다. 가격이 수량이 돼버리고 수량이 가격이 돼버리면 바겐세일이다.
이를 이름 지정 파라미터로 해결할 수 있다.

v2 이름 지정 파라미터

JdbcTemplate을 기본으로 사용하면 파라미터를 순서대로 바인딩 한다.

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

여기서는 itemName , price , quantity 가 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);
    }
}

NamedParameterJdbcTemplate 도 내부에 dataSource 가 필요하다.
JdbcTemplateItemRepositoryV2 생성자를 보면 의존관계 주입은 dataSource 를 받고 내부에서NamedParameterJdbcTemplate 을 생성해서 가지고 있다.
스프링에서는 JdbcTemplate 관련 기능 을 사용할 때 관례상 이 방법을 많이 사용한다.
물론 NamedParameterJdbcTemplate 을 스프링 빈으로 직접 등록하고 주입받아도 된다.

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

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

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

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

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

1. Map

Map<String, Object> param = Map.of("id", id); 의 기본 JAVA Map 객체를 사용하여 넣을 수 있고

2. MapSqlParameterSource

Map 과 유사한데, SQL 타입을 지정할 수 있는 등 SQL에 좀 더 특화된 기능을 제공한다.
SqlParameterSource 인터페이스의 구현체이다.
MapSqlParameterSource 는 메서드 체인( .addValue )을 통해 편리한 사용법도 제공한다.

3. BeanPropertySqlParameterSource

자바빈 프로퍼티 규약을 통해서 자동으로 파라미터 객체를 생성한다.
예) ( getXxx() -> xxx, getItemName() -> itemName )
예를 들어서 getItemName() , getPrice() 가 있으면 다음과 같은 데이터를 자동으로 만들어낸다.
SqlParameterSource 인터페이스의 구현체이다

즉, psmt로 일일이 순서대로 "?" 를 채워넣어주는 것이 아닌, BeanPropertySqlParameterSource를 사용하여 Bean으로 등록된 class에 대하여 속성값들을 가져와서 만들어준다.

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

BeanPropertyRowMapper

itemRowMapper 역시 컬럼들을 보면 class의 property과 같아서 그냥 BeanPropertyRowMapper 로 처리하였다.
어떻게 이게 가능할까? ->
BeanPropertyRowMapper 는 ResultSet 의 결과를 받아서 자바빈 규약에 맞추어 데이터를 변환한다.
예를 들어서 데이터베이스에서 조회한 결과가 select id, price 라고 하면 다음과 같은 코드를 작성해준다. (실제로는 리플렉션 같은 기능을 사용한다.)

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

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

따라서 상황에 맞게 3개중 하나를 선택하여 사용하면 된다.

sql alias 사용하기

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

select item_name as itemName

별칭 as 를 사용해서 SQL 조회 결과의 이름을 변경하는 것이다.
실제로 이 방법은 자주 사용된다. 특히 데이터베이스 컬럼 이름과 객체 이름이 완전히 다를 때 문제를 해결할 수 있다.
예를 들어서 데이터베이스에는 member_name 이라고 되어 있는데 객체에 username 이라고 되어 있다면 다음과 같이 해결할 수 있다.

select member_name as username

이렇게 데이터베이스 컬럼 이름과 객체의 이름이 다를 때 별칭( as )을 사용해서 문제를 많이 해결한다.
즉, 이렇게 as를 갖다 붙여놓으면 result set을 BeanPropertyRowMapper가 돌릴 때 alias값을 갖다 쓴다는 것이다.

관례의 불일치

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

이 부분을 관례로 많이 사용하다 보니 BeanPropertyRowMapper 는 언더스코어 표기법을 카멜로 자동 변환해준다.
따라서 select item_name 으로 조회해도 setItemName() 에 문제 없이 값이 들어간다.
정리하면 snake_case 는 자동으로 해결되니 그냥 두면 되고, 컬럼 이름과 객체 이름이 완전히 다른 경우에는 조회 SQL에서 별칭을 사용하면 된다.

v3 SimpleJdbcInsert

JdbcTemplate은 INSERT SQL를 직접 작성하지 않아도 되도록 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")
                .usingColumns("item_name", "price", "quantity");  // 생략가능 -> SimpleJdbcInsert 가 데이터 소스를 통하여 테이블명, 키 컬럼등을 메타 데이터를 DB 에서 읽는다. 그래서 생략 가능

    }

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

    ...
    
}

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

SimpleJdbcInsert

public JdbcTemplateItemRepositoryV3(DataSource dataSource) {
    this.template = new NamedParameterJdbcTemplate(dataSource);
    this.jdbcInsert = new SimpleJdbcInsert(dataSource)
            .withTableName("item")
            .usingGeneratedKeyColumns("id")
            .usingColumns("item_name", "price", "quantity");  // 생략가능 -> SimpleJdbcInsert 가 데이터 소스를 통하여 테이블명, 키 컬럼등을 메타 데이터를 DB 에서 읽는다. 그래서 생략 가능
}

SimpleJdbcInsert 는 생성 시점에 데이터베이스 테이블의 메타 데이터를 조회한다. 따라서 어떤 컬럼이 있는지 확인 할 수 있으므로 usingColumns 을 생략할 수 있다. 만약 특정 컬럼만 지정해서 저장하고 싶다면 usingColumns 를 사용하면 된다.

save

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

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

JdbcTemplate 기능 정리

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

JDBC Template 사용법

공식문서

조회

1. 단건 조회 - 숫자 조회

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

하나의 로우를 조회할 때는 queryForObject() 를 사용하면 된다. 지금처럼 조회 대상이 객체가 아니라 단순 데이터 하나라면 타입을 Integer.class , String.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. 단건 조회 - 객체 조회

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 를 사용해야 한다. 여기서는 람다를 사용했다.

5. 목록 조회 - 객체

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 를 사용해야 한다. 여기서는 람다를 사용했다.

6. 목록 조회 - 객체

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

여러 로우를 조회할 때는 query() 를 사용하면 된다. 결과를 리스트로 반환한다.
여기서는 RowMapper 를 분리했다. 이렇게 하면 여러 곳에서 재사용 할 수 있다.

변경(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));

강정우

智(지)! 德(덕)! 體(체)!

이전 포스트

Spring 프로젝트 기본 전략

다음 포스트