JdbcTemplate OneToMany, 1:N Join - InSpring

 

JdbcTemplate의 사용법은 다음 링크에 자세하게 나와있다.

* https://blog.outsider.ne.kr/882

 

JdbcTemplate OneToMany, 1:N

JdbcTemplate을 이용해서 1:N 상황의 엔티티를 Join하는 방법을 알아보자.

먼저 다음과 같은 1:N 관계의 도메인이 있다.

• Order (1)
• OrderItem (N)

@Getter
@NoArgsConstructor(access = AccessLevel.PROTECTED)
@AllArgsConstructor
public class Order {

    private Long orderId;

    private String user;

    private List<OrderItem> items = new ArrayList();

}

@Getter
@NoArgsConstructor(access = AccessLevel.PROTECTED)
@AllArgsConstructor
public class OrderItem {

    private Long itemId;
    private Long orderId;
    private String name;
    private int quantity;

}

이 두 도메인을 조인해서 조회해보겠다.

 

1. orderId로 order 1건, orderItem 여러건 조인해서 조회

@Repository
@RequiredArgsConstructor
public class OrderJdbcRepository {

    private final NamedParameterJdbcTemplate jdbcTemplate;

    private final String query = "select * from orders o inner join order_items oi "
        + "on o.order_id = oi.order_id where o.id = :orderId";

    public Optional<Order> findOrderWithItemById(Long orderId) {

        Order findOrder = jdbcTemplate.query(query, new ResultSetExtractor<Order>() {
            @Override
            public Order extractData(ResultSet rs) throws SQLException, DataAccessException {
                Order order = null;
                int row = 0;
                while (rs.next()) {
                    if (order == null) {
                        order = orderMapper.mapRow(rs, row);
                    }

                    if (order != null) {
                        OrderItem orderItem = orderItemMapper.mapRow(rs, row);
                        order.getItems().add(orderItem);
                        row++;
                    }
                }

                return order;
            }
        });

        return Optional.ofNullable(findOrder);
    }

    private final RowMapper<Order> orderMapper = (rs, rowNum) -> {
        Long orderId = rs.getLong("order_id");
        Long userId = rs.getLong("user_id");

        return new Order(orderId, userId);
    };

    private final RowMapper<OrderItem> orderItemMapper = (rs, rowNum) -> {
        Long itemId = rs.getLong("item_id");
        Long orderId = rs.getLong("oi.order_id"); // 조인 시 별칭으로 나옴
        String name = rs.getString("name");
        int quantity = rs.getInt("quantity");

        return new OrderItem(itemId, orderId, name, quantity);
    };

}

• 두 테이블을 조인하면, 1:N 관계에서는 N만큼의 Row(행)가 나오게 된다.
• Order 1개, OrderItem이 5개 라면 Row는 5개가 나오게 된다
• Order정보는 중복일테니 1개만 만들어 주고, 나머지 Row 는 Count 해가면서 OrderItem을 만들어준다

Order

order_id	user_id
1	99

OrderITem

item_id	order_id	name	quantity
1	1	샴푸	1
2	1	개밥	2
3	1	개껌	3
4	1	책	4
5	1	금	5

두 테이블을 조인하면

order_id	user_id	item_id	name	quantity
1	99	1	샴푸	1
1	99	2	개밥	2
1	99	3	개껌	3
1	99	4	책	4
1	99	5	금	5

그러므로 Rs를 이용하여 Order가 null이면 한번만 생성하고, OrderItem을 계속 생성하면서 다음row 카운트를 증가 시킨다

 

2. 모든 Order와 OrderItem을 같이 조회

@Repository
@RequiredArgsConstructor
public class OrderJdbcRepository {

    private final NamedParameterJdbcTemplate jdbcTemplate;

    public List<Order> findAllOrderWithItems() {
        String query = "select * from orders o inner join order_items oi on o.order_id = oi.order_id";

        List<Order> orders =
            jdbcTemplate.query(query, new ResultSetExtractor<List<Order>>() {
                @Override
                public List<Order> extractData(ResultSet rs)
                    throws SQLException, DataAccessException {
                    List<Order> findOrders = new ArrayList<>();

                    Long orderId = null;
                    Order currentOrder = null;
                    int orderIdx = 0;
                    int itemIdx = 0;

                    while (rs.next()) {
                        if (currentOrder == null || !orderId.equals(rs.getLong("order_id"))) {
                            orderId = rs.getLong("order_id");

                            currentOrder = orderMapper.mapRow(rs, orderIdx++);

                            itemIdx = 0;
                            findOrders.add(currentOrder);
                        }
                        OrderItem orderItem = orderItemMapper.mapRow(rs, itemIdx++);
                        currentOrder.getItems().add(orderItem);
                    }

                    return findOrders;
                }
            });

        return orders.isEmpty() ? new ArrayList<>() : orders;
    }


    private final RowMapper<Order> orderMapper = (rs, rowNum) -> {
        Long orderId = rs.getLong("order_id");
        Long userId = rs.getLong("user_id");

        return new Order(orderId, userId);
    };

    private final RowMapper<OrderItem> orderItemMapper = (rs, rowNum) -> {
        Long itemId = rs.getLong("item_id");
        Long orderId = rs.getLong("oi.order_id"); // 조인 시 별칭으로 나옴
        String name = rs.getString("name");
        int quantity = rs.getInt("quantity");

        return new OrderItem(itemId, orderId, name, quantity);
    };

}

• currentOrder == null || !orderId.equals(rs.getLong("order_id")) 이부분이 핵심이다.
  • 1:N 처럼 결국에 같은 로우가 계속 나오기 때문에, orderId를 비교하여 새 current Order를 만든다.

 

전체 코드

도메인 엔티티

@Getter
@NoArgsConstructor(access = AccessLevel.PROTECTED)
@AllArgsConstructor
public class Order {

    private Long orderId;

    private String user;

    private List<OrderItem> items = new ArrayList();

}

@Getter
@NoArgsConstructor(access = AccessLevel.PROTECTED)
@AllArgsConstructor
public class OrderItem {

    private Long itemId;
    private Long orderId;
    private String name;
    private int quantity;

}

JdbcTemplate

@Repository
@RequiredArgsConstructor
public class OrderJdbcRepository {

    private final NamedParameterJdbcTemplate jdbcTemplate;

    private final String query = "select * from orders o inner join order_items oi "
        + "on o.order_id = oi.order_id where o.id = :orderId";

    public Optional<Order> findOrderWithItemById(Long orderId) {

        Order findOrder = jdbcTemplate.query(query, new ResultSetExtractor<Order>() {
            @Override
            public Order extractData(ResultSet rs) throws SQLException, DataAccessException {

                Order order = null;
                int row = 0;
                while (rs.next()) {
                    if (order == null) {
                        order = orderMapper.mapRow(rs, row);
                    }

                    if (order != null) {
                        OrderItem orderItem = orderItemMapper.mapRow(rs, row);
                        order.getItems().add(orderItem);
                        row++;
                    }
                }

                return order;
            }
        });

        return Optional.ofNullable(findOrder);
    }


    public List<Order> findAllOrderWithItems() {
        String query = "select * from orders o inner join order_items oi on o.order_id = oi.order_id";

        List<Order> orders =
            jdbcTemplate.query(query, new ResultSetExtractor<List<Order>>() {
                @Override
                public List<Order> extractData(ResultSet rs)
                    throws SQLException, DataAccessException {
                    List<Order> findOrders = new ArrayList<>();

                    Long orderId = null;
                    Order currentOrder = null;
                    int orderIdx = 0;
                    int itemIdx = 0;

                    while (rs.next()) {
                        if (currentOrder == null || !orderId.equals(rs.getLong("order_id"))) {
                            orderId = rs.getLong("order_id");

                            currentOrder = orderMapper.mapRow(rs, orderIdx++);

                            itemIdx = 0;
                            findOrders.add(currentOrder);
                        }
                        OrderItem orderItem = orderItemMapper.mapRow(rs, itemIdx++);
                        currentOrder.getItems().add(orderItem);
                    }

                    return findOrders;
                }
            });

        return orders.isEmpty() ? new ArrayList<>() : orders;
    }


    private final RowMapper<Order> orderMapper = (rs, rowNum) -> {
        Long orderId = rs.getLong("order_id");
        Long userId = rs.getLong("user_id");

        return new Order(orderId, userId);
    };

    private final RowMapper<OrderItem> orderItemMapper = (rs, rowNum) -> {
        Long itemId = rs.getLong("item_id");
        Long orderId = rs.getLong("oi.order_id"); // 조인 시 별칭으로 나옴
        String name = rs.getString("name");
        int quantity = rs.getInt("quantity");

        return new OrderItem(itemId, orderId, name, quantity);
    };

}