JdbcOrderDao extends JdbcDaoSupport implements OrderDao { @Override public List<Order> findExpensiveOrders( final long customerId, final LocalDate boughtAt, final BigDecimal minimumAmount) { final DateTime dateLower = boughtAt.toDateMidnight().toDateTime(); final DateTime dateUpper = dateLower.plusDays(1); return getJdbcTemplate().query( "select o.* from CUSTOMER_ORDER o " + "LEFT JOIN (" + " SELECT o.ID as ORDER_ID,SUM(PRICE) as TOTAL" + " FROM CUSTOMER_ORDER o " + " LEFT JOIN CUSTOMER_ORDER_ITEM oi ON o.ID = oi.ORDER_ID " + " LEFT JOIN PRODUCT p ON oi.PRODUCT_ID = p.ID " + " GROUP BY o.ID" + ") prices ON o.ID = prices.ORDER_ID " + "WHERE o.CUSTOMER_ID = ? " + " AND o.CREATION >= ? " + " AND o.CREATION < ? " + " AND prices.TOTAL >= ? ", ROW_MAPPER, customerId, dateLower.toDate(), dateUpper.toDate(), minimumAmount ); }  @Override List<Order> findExpensiveOrders(
            final long customerId,
            final LocalDate boughtAt,
            final BigDecimal minimumAmount); static final RowMapper<Order> ROW_MAPPER; }
@Test public void findExpensiveOrders_should_selectCorrectlySummedUpItems() throws IOException { final Table orders = aTable(OrderTable.class) .withRowsBetween(1, 10) .allHaving(CUSTOMER_ID, CUSTOMER_1) .allHaving(CREATION, duringCreationDay); final Table orderItems = aTable(OrderItemTable.class) .withRowsBetween(101, 107) .having(ORDER_ID, 1, 2, 3, 4, 5, 6, 7) .allHaving(PRODUCT_ID, PRODUCT_1) .with( aRow(108).f(ORDER_ID, 8).f(PRODUCT_ID, PRODUCT_10), aRow(109).f(ORDER_ID, 8).f(PRODUCT_ID, PRODUCT_10), aRow(110).f(ORDER_ID, 8).f(PRODUCT_ID, PRODUCT_10), aRow(111).f(ORDER_ID, 8).f(PRODUCT_ID, PRODUCT_9_99), aRow(112).f(ORDER_ID, 9).f(PRODUCT_ID, PRODUCT_10), aRow(113).f(ORDER_ID, 9).f(PRODUCT_ID, PRODUCT_10), aRow(114).f(ORDER_ID, 9).f(PRODUCT_ID, PRODUCT_10), aRow(115).f(ORDER_ID, 9).f(PRODUCT_ID, PRODUCT_10), aRow(116).f(ORDER_ID, 10).f(PRODUCT_ID, PRODUCT_50) ); daleq.insertIntoDatabase(orders, orderItems); final List<Order> actual = orderDao.findExpensiveOrders(CUSTOMER_1, creationDay, fortyEuro); assertContainsOrders(actual, order(8), order(9), order(10)); }
@Test public void findExpensiveOrders_should_selectOrdersOfThatUser() { final Table orders = aTable(OrderTable.class) .withRowsBetween(1, 10) .having(CUSTOMER_ID, CUSTOMER_1, CUSTOMER_1, CUSTOMER_1, CUSTOMER_2, CUSTOMER_2, CUSTOMER_2, CUSTOMER_3, CUSTOMER_3, CUSTOMER_3, CUSTOMER_3 ) .allHaving(CREATION, duringCreationDay); final Table orderItems = aTable(OrderItemTable.class) .withRowsBetween(1, 10) .having(ORDER_ID, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10) .allHaving(PRODUCT_ID, PRODUCT_50); daleq.insertIntoDatabase(orders, orderItems); final List<Order> actual = orderDao.findExpensiveOrders(CUSTOMER_1, creationDay, fortyEuro); assertContainsOrders(actual, order(1), order(2), order(3)); }
@Test public void findExpensiveOrders_should_selectOrdersOnCreationDay() { final DateTime beginOfCreationDay = creationDay.toDateTimeAtStartOfDay(); final Table orders = aTable(OrderTable.class).with( aRow(1).f(CREATION, beginOfCreationDay.minusDays(10)), aRow(2).f(CREATION, beginOfCreationDay.minusDays(1)), aRow(3).f(CREATION, beginOfCreationDay.minusSeconds(1)), aRow(4).f(CREATION, beginOfCreationDay), aRow(5).f(CREATION, beginOfCreationDay.plusSeconds(1)), aRow(6).f(CREATION, beginOfCreationDay.plusHours(5)), aRow(7).f(CREATION, creationDay.toDateTime(new LocalTime(23, 59, 59))), aRow(8).f(CREATION, beginOfCreationDay.plusDays(1)), aRow(9).f(CREATION, beginOfCreationDay.plusDays(10)) ).allHaving(CUSTOMER_ID, CUSTOMER_1); final Table orderItems = aTable(OrderItemTable.class) .withRowsBetween(1, 9) .having(ORDER_ID, 1, 2, 3, 4, 5, 6, 7, 8, 9) .allHaving(PRODUCT_ID, PRODUCT_50); daleq.insertIntoDatabase(orders, orderItems); final List<Order> actual = orderDao.findExpensiveOrders(CUSTOMER_1, creationDay, fortyEuro); assertContainsOrders(actual, order(4), order(5), order(6), order(7)); }