Skip to content

Solving real problems

Jump to bottom
Konstantin Triger edited this page Oct 1, 2019 · 4 revisions

Last year and 2 years ago Lukas Eder has been laughing at the Devoxx stage on a poor Java developer. The developer was given a task of implementing a query and enhancing it over time. Then he compared efforts of a SQL developer, that received the same task and a Java developer. Below is what the SQL developer had to do:

-- 1st query
SELECT title, payment_date, SUM(amount)
FROM film
JOIN inventory USING (film_id)
JOIN rental USING (inventory_id)
JOIN payment USING (rental_id)
GROUP BY film_id, payment_date
ORDER BY title, payment_date;

-- 2nd query
SELECT title, store_id, payment_date, SUM(amount)
FROM film
JOIN inventory USING (film_id)
JOIN rental USING (inventory_id)
JOIN payment USING (rental_id)
GROUP BY film_id, store_id, payment_date
ORDER BY title, store_id, payment_date;

-- 3rd query
SELECT title, store_id, payment_date,
    SUM(SUM(amount)) OVER (
        PARTITION BY title, store_id
        ORDER BY payment_date
    )
FROM film
JOIN inventory USING (film_id)
JOIN rental USING (inventory_id)
JOIN payment USING (rental_id)
GROUP BY film_id, store_id, payment_date
ORDER BY title, store_id, payment_date

And he showed that a Java developer has to work very hard to implement the same in Java. With FluentJPA, using the existing objects, Java developer already had in his project, without any additional step or changing a single line of existing code, Java developer would write the following code (producing same results SQL developer has):

// 1st query
SELECT(film.getTitle(), payment.getPaymentDate(), SUM(payment.getAmount()));
FROM(film).JOIN(inventory)
        .ON(film == inventory.getFilm())
        .JOIN(rental)
        .ON(rental.getInventory() == inventory)
        .JOIN(payment)
        .ON(rental == payment.getRental());
GROUP(BY(film.getId()), BY(payment.getPaymentDate()));
ORDER(BY(film.getTitle()), BY(payment.getPaymentDate()));

// 2nd query
SELECT(film.getTitle(), inventory.getStore().getId(), payment.getPaymentDate(),
                                                      SUM(payment.getAmount()));
FROM(film).JOIN(inventory)
        .ON(film == inventory.getFilm())
        .JOIN(rental)
        .ON(rental.getInventory() == inventory)
        .JOIN(payment)
        .ON(rental == payment.getRental());
GROUP(BY(film.getId()), BY(inventory.getStore().getId()), BY(payment.getPaymentDate()));
ORDER(BY(film.getTitle()), BY(inventory.getStore().getId()), BY(payment.getPaymentDate()));


// 3rd query
Float sum = aggregateBy(SUM(SUM(payment.getAmount())))
        .OVER(PARTITION(BY(film.getTitle()), BY(inventory.getStore().getId()))
        .ORDER(BY(payment.getPaymentDate())));

SELECT(film.getTitle(), inventory.getStore().getId(), payment.getPaymentDate(), sum);
FROM(film).JOIN(inventory)
        .ON(film == inventory.getFilm())
        .JOIN(rental)
        .ON(rental.getInventory() == inventory)
        .JOIN(payment)
        .ON(rental == payment.getRental());
GROUP(BY(film.getId()), BY(inventory.getStore().getId()), BY(payment.getPaymentDate()));
ORDER(BY(film.getTitle()), BY(inventory.getStore().getId()), BY(payment.getPaymentDate()));

Same effort the SQL developer has! I added this code as unit tests for FluentJPA, so you may examine the actual working code.

Getting Started

Examples

Basic SQL DML Statements

Advanced SQL DML Statements

Advanced Topics

Clone this wiki locally