Each country’s GDP per capita in each year between 2009-2012
Each country’s debt as a percentage of its GDP in each year between 2009-2012
This will then create 8 series with four data points for each series in both charts. In addition to the above, we’d like to order the series among themselves by the average projected value between 2009-2012, such that the series – and thus the countries – can be compared easily.
This is obviously easier to explain visually via the resulting chart than in text, so stay tuned until the end of the article.
Collecting the data with jOOQ and JavaFX
The query that we would write to calculate the above data series would look as follows in plain SQL:
select
COUNTRIES.YEAR,
COUNTRIES.CODE,
COUNTRIES.GOVT_DEBT
from
COUNTRIES
join (
select
COUNTRIES.CODE,
avg(COUNTRIES.GOVT_DEBT) avg
from
COUNTRIES
group by
COUNTRIES.CODE
) c1
on COUNTRIES.CODE = c1.CODE
order by
avg asc,
COUNTRIES.CODE asc,
COUNTRIES.YEAR asc
In other words, we’ll simply select the relevant columns from the COUNTRIES table, and we’ll self-join the average projected value per country such that we can order the result by that average. The same query could be written using window functions. We’ll get to that later on.
The code that we’ll write to create such a bar chart with jOOQ and JavaFX is the following:
CategoryAxis xAxis = new CategoryAxis();
NumberAxis yAxis = new NumberAxis();
xAxis.setLabel("Country");
yAxis.setLabel("% of GDP");
BarChart<String, Number> bc =
new BarChart<>(xAxis, yAxis);
bc.setTitle("Government Debt");
bc.getData().addAll(
// SQL data transformation, executed in the DB
// -------------------------------------------
DSL.using(connection)
.select(
COUNTRIES.YEAR,
COUNTRIES.CODE,
COUNTRIES.GOVT_DEBT)
.from(COUNTRIES)
.join(
table(
select(
COUNTRIES.CODE,
avg(COUNTRIES.GOVT_DEBT).as("avg"))
.from(COUNTRIES)
.groupBy(COUNTRIES.CODE)
).as("c1")
)
.on(COUNTRIES.CODE.eq(
field(
name("c1", COUNTRIES.CODE.getName()),
String.class
)
))
// order countries by their average
// projected value
.orderBy(
field(name("avg")),
COUNTRIES.CODE,
COUNTRIES.YEAR)
// The result produced by the above statement
// looks like this:
// +----+----+---------+
// |year|code|govt_debt|
// +----+----+---------+
// |2009|RU | 8.70|
// |2010|RU | 9.10|
// |2011|RU | 9.30|
// |2012|RU | 9.40|
// |2009|CA | 51.30|
// +----+----+---------+
// Java data transformation, executed in app memory
// ------------------------------------------------
// Group results by year, keeping sort
// order in place
.fetchGroups(COUNTRIES.YEAR)
// The generic type of this is inferred...
// Stream<Entry<Integer, Result<
// Record3<BigDecimal, String, Integer>>
// >>
.entrySet()
.stream()
// Map entries into { Year -> Projected value }
.map(entry -> new XYChart.Series<>(
entry.getKey().toString(),
observableArrayList(
// Map records into a chart Data
entry.getValue().map(country ->
new XYChart.Data<String, Number>(
country.getValue(COUNTRIES.CODE),
country.getValue(COUNTRIES.GOVT_DEBT)
))
)
))
.collect(toList())
);
The interesting thing here is really that we can fetch data from the database, and later on, transform it into JavaFX data structures all in one go. The whole thing is almost a single Java statement.
SQL and Java is cleanly separated
As we’ve blogged on this blog before, there is a very important difference when comparing the above approach to LINQ or to JPQL’s DTO fetching capabilities. The SQL query is cleanly separated from the Java in-memory data transformation, even if we express the whole transformation in one single statement.
We want to be as precise as possible when expressing our SQL query for the database to be able to calculate the optimal execution plan. Only once we have materialised our data set, the Java 8 Stream transformation will kick in.
The importance of this is made clear when we change the above SQL-92 compatible query with a SQL-1999 compatible one that makes use of awesome window functions. The jOOQ part of the above statement could be replaced by the following query:
select
COUNTRIES.YEAR,
COUNTRIES.CODE,
COUNTRIES.GOVT_DEBT
from
COUNTRIES
order by
avg(COUNTRIES.GOVT_DEBT)
over (partition by COUNTRIES.CODE),
COUNTRIES.CODE,
COUNTRIES.YEAR
As you can see, staying in control of you actual SQL statement is of the essence when you run such reports. There is no way you could have refactored ordering via nested selects into a much more efficient ordering via window functions as easily as this. Let alone refactoring dozens of lines of Java sorting logic.
Yep. It’s hard to beat the beauty of window functions
If we add some additional JavaFX boilerplate to put the chart into a Pane, a Scene, and a Stage, we’ll get these nice-looking charts below:
SQL with JavaFX and jOOQ
Fixed… It should work now. We’ve recently downgraded the example dependencies back to jOOQ 3.5.x, instead of 3.6.0-SNAPSHOT to make it easier for people to run the examples. Looks like we’ve made it harder for you, this time :-(
Gosh I love reading your blog. This is awesome.
fail to run https://dl.dropboxusercontent.com/u/12228758/jooq_output.log
Thanks for letting us know. We’ll look into this right away. I’ve created an issue to track this problem:
https://github.com/jOOQ/jOOQ/issues/4204
Fixed… It should work now. We’ve recently downgraded the example dependencies back to jOOQ 3.5.x, instead of 3.6.0-SNAPSHOT to make it easier for people to run the examples. Looks like we’ve made it harder for you, this time :-(
works like a charm now, thanks!