В недавнем прошлом мы показали, как Java 8 и функциональное программирование дадут новую перспективу для разработчиков Java, когда речь заходит о функциональном преобразовании данных SQL-данных с использованием jOOQ и Java 8 lambdas and Streams . Сегодня мы делаем еще один шаг вперед и преобразуем данные в JavaFX XYChart.Series чтобы получить из наших данных красивые гистограммы.
Настройка базы данных
Мы снова будем использовать небольшое подмножество открытых данных Всемирного банка в базе данных PostgreSQL. Данные, которые мы используем, это здесь:
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
|
DROP SCHEMA IF EXISTS world;CREATE SCHEMA world;CREATE TABLE world.countries ( code CHAR(2) NOT NULL, year INT NOT NULL, gdp_per_capita DECIMAL(10, 2) NOT NULL, govt_debt DECIMAL(10, 2) NOT NULL); INSERT INTO world.countriesVALUES ('CA', 2009, 40764, 51.3), ('CA', 2010, 47465, 51.4), ('CA', 2011, 51791, 52.5), ('CA', 2012, 52409, 53.5), ('DE', 2009, 40270, 47.6), ('DE', 2010, 40408, 55.5), ('DE', 2011, 44355, 55.1), ('DE', 2012, 42598, 56.9), ('FR', 2009, 40488, 85.0), ('FR', 2010, 39448, 89.2), ('FR', 2011, 42578, 93.2), ('FR', 2012, 39759,103.8), ('GB', 2009, 35455,121.3), ('GB', 2010, 36573, 85.2), ('GB', 2011, 38927, 99.6), ('GB', 2012, 38649,103.2), ('IT', 2009, 35724,121.3), ('IT', 2010, 34673,119.9), ('IT', 2011, 36988,113.0), ('IT', 2012, 33814,131.1), ('JP', 2009, 39473,166.8), ('JP', 2010, 43118,174.8), ('JP', 2011, 46204,189.5), ('JP', 2012, 46548,196.5), ('RU', 2009, 8616, 8.7), ('RU', 2010, 10710, 9.1), ('RU', 2011, 13324, 9.3), ('RU', 2012, 14091, 9.4), ('US', 2009, 46999, 76.3), ('US', 2010, 48358, 85.6), ('US', 2011, 49855, 90.1), ('US', 2012, 51755, 93.8); |
( см. также эту статью здесь о другом удивительном наборе SQL-запросов к приведенным выше данным )
Теперь мы хотим построить два набора значений на двух разных гистограммах:
- ВВП каждой страны на душу населения в каждом году между 2009-2012
- Долг каждой страны в процентах от ее ВВП в каждом году между 2009-2012 гг.
Затем будут созданы 8 серий с четырьмя точками данных для каждой серии в обеих диаграммах. В дополнение к вышесказанному, мы хотели бы упорядочить ряды между собой по среднему прогнозируемому значению за период 2009-2012 годов, чтобы можно было легко сравнивать ряды и, следовательно, страны. Это, очевидно, легче объяснить визуально через полученный график, чем в тексте, поэтому следите за обновлениями до конца статьи.
Сбор данных с помощью jOOQ и JavaFX
Запрос, который мы напишем для вычисления вышеуказанного ряда данных, будет выглядеть следующим образом в простом SQL:
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
|
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 |
Другими словами, мы просто выберем соответствующие столбцы из таблицы СТРАН и самостоятельно объединим среднее прогнозируемое значение для каждой страны, чтобы можно было упорядочить результат по этому среднему значению. Тот же запрос может быть написан с использованием оконных функций. Мы вернемся к этому позже.
Код, который мы напишем для создания такой гистограммы с jOOQ и JavaFX, выглядит следующим образом:
|
01
02
03
04
05
06
07
08
09
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
|
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())); |
Интересно, что на самом деле мы можем получать данные из базы данных, а затем преобразовывать их в структуры данных JavaFX за один раз. Все это почти одно утверждение Java.
SQL и Java четко разделены
Как мы уже писали в этом блоге, есть очень важное различие при сравнении вышеуказанного подхода с LINQ или с возможностями извлечения DTO в JPQL . Запрос SQL четко отделен от преобразования данных в памяти Java, даже если мы выражаем все преобразование в одном выражении.
Мы хотим быть максимально точными при выражении нашего SQL-запроса к базе данных, чтобы иметь возможность рассчитать оптимальный план выполнения. Только после того, как мы материализовали наш набор данных, преобразование Java 8 Stream вступит в силу.
Важность этого становится ясной, когда мы заменяем вышеупомянутый SQL-92-совместимый запрос на SQL-1999-совместимый, который использует удивительные оконные функции . Часть jOOQ вышеприведенного оператора может быть заменена следующим запросом:
|
01
02
03
04
05
06
07
08
09
10
11
12
|
DSL.using(connection) .select( COUNTRIES.YEAR, COUNTRIES.CODE, COUNTRIES.GOVT_DEBT) .from(COUNTRIES) .orderBy( avg(COUNTRIES.GOVT_DEBT) .over(partitionBy(COUNTRIES.CODE)), COUNTRIES.CODE, COUNTRIES.YEAR) ; |
… или в SQL:
|
01
02
03
04
05
06
07
08
09
10
11
|
select COUNTRIES.YEAR, COUNTRIES.CODE, COUNTRIES.GOVT_DEBTfrom COUNTRIESorder by avg(COUNTRIES.GOVT_DEBT) over (partition by COUNTRIES.CODE), COUNTRIES.CODE, COUNTRIES.YEAR |
Как вы можете видеть, когда вы запускаете такие отчеты, важно контролировать фактический оператор SQL. Нет никакого способа, которым вы могли бы реорганизовать упорядочение с помощью вложенных выборок в гораздо более эффективный порядок упорядочения с помощью оконных функций. Не говоря уже о рефакторинге десятков строк логики сортировки Java.
Ага. Трудно превзойти красоту оконных функций
Если мы добавим несколько дополнительных шаблонов JavaFX, чтобы поместить диаграмму в панель, сцену и сцену, мы получим эти симпатичные диаграммы ниже:
SQL с JavaFX и jOOQ
Играй с этим сам
Вы можете скачать и запустить приведенный выше пример самостоятельно. Просто скачайте следующий пример и запустите mvn clean install : https://github.com/jOOQ/jOOQ/tree/master/jOOQ-examples/jOOQ-javafx-example
| Ссылка: | Преобразуйте свои данные SQL в графики, используя jOOQ и JavaFX от нашего партнера по JCG Лукаса Эдера из блога JAVA, SQL и AND JOOQ . |