-
Notifications
You must be signed in to change notification settings - Fork 10
Solving real problems
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
- Introduction
- Setup
- Data Types
- Entities & Tuples
- Sub Queries
- JPA Integration
- Java Language Support
- Directives
- Library
- Returning Results
- JPA Repositories
Examples
Basic SQL DML Statements
Advanced SQL DML Statements
- Common Table Expressions (WITH Clause)
- Window Functions (OVER Clause)
- Aggregate Expressions
- MERGE
- Temporal Tables
Advanced Topics