Slide 1
Slide 1 text
Datenbankzentrische
Anwendungen mit Spring Boot
und jOOQ
Java User Group Münster
15. November 2017
Michael Simons, @rotnroll666
Slide 2
Slide 2 text
Über mich
> Senior Consultant bei innoQ
> Mag relationale Datenbanken und
SQL
> Bloggt zu Java, Spring und
Softwarearchitektur unter
info.michael-simons.eu
> Schreibt gerne ->
> Regt sich auf Twitter als @rotnroll666
über alles mögliche auf
Slide 3
Slide 3 text
von @iamjoyclark
Slide 4
Slide 4 text
Hintergrund
> Zeitreihenmanagement im Energiemarkt
(Auswertungen Ist-Daten, Prognosen)
> GIS-Systeme auf Basis der Oracle Spatial
Option
Slide 5
Slide 5 text
JAVA UND
DATENBANKEN
Plain SQL, ORM oder etwas
dazwischen?
Slide 6
Slide 6 text
ETWAS DAZWISCHEN?
JEDE MENGE!
▸ JDBC
▸ Springs JDBCTemplate
▸ JPA
▸ JPQL
▸ Criteria query
▸ MyBatis
▸ jOOQ
▸ noch einige mehr…
Y U NOT GIVE ME THE RIGHT TOOL?
Slide 7
Slide 7 text
NUN…
Slide 8
Slide 8 text
Quelle: https://www.flickr.com/photos/133021668@N04/31403416393
Slide 9
Slide 9 text
Quelle: https://www.flickr.com/photos/potentialpast/5582667252
Slide 10
Slide 10 text
No content
Slide 11
Slide 11 text
DIE HITPARADE*
ZUERST EIN BEISPIEL
* Ebenfalls Zeitreihen
Slide 12
Slide 12 text
DAS ZUGEHÖRIGE SCHEMA
Slide 13
Slide 13 text
WIE HÄTTET IHR ES GERNE?
PLAIN SQL
Select *
from tracks
where album = 'True Survivor';
Slide 14
Slide 14 text
WIE HÄTTET IHR ES GERNE?
PLAIN JPA
@Entity
@Table(name = "tracks")
public class TrackEntity implements Serializable {
@Id @GeneratedValue(strategy = GenerationType.AUTO)
private Integer id;
@Column
private String album;
public static void main(String...a) {
final EntityManagerFactory factory =
Persistence.createEntityManagerFactory("whatever");
final EntityManager entityManager =
factory.createEntityManager();
List tracks = entityManager
.createQuery("Select t from tracks where album = :album")
.setParameter("album", "True Survivor")
.getResultList();
}
}
Slide 15
Slide 15 text
WIE HÄTTET IHR ES GERNE?
JPA + SPRING DATA
public interface TrackRepository extends
JpaRepository {
public List findAllByAlbum(final String name);
public static void main(String...a) {
TrackRepository trackRepository;
final List tracks = trackRepository
.findAllByAlbum("True Survivor");
}
}
Slide 16
Slide 16 text
UND DANN WOLLTE
JEMAND* EINE
AUSWERTUNG HABEN…
* „Business“
Slide 17
Slide 17 text
No content
Slide 18
Slide 18 text
THE WINDOW FUNCTION… IT MOVES THROUGH EVERY LIVING THING
SELECT ALL THE STUFF…
WITH
previous_month AS
(SELECT p.track_id, count(*) as cnt,
dense_rank() over(order by count(*) desc) as position
FROM plays p
WHERE trunc(p.played_on, 'DD') BETWEEN
date'2016-04-01' and date'2016-04-30' GROUP BY p.track_id),
current_month AS
(SELECT p.track_id, count(*) as cnt,
dense_rank() over(order by count(*) desc) as position
FROM plays p
WHERE trunc(p.played_on, 'DD') BETWEEN
date'2016-05-01' and date'2016-05-31' GROUP BY p.track_id)
SELECT a.artist || ' - ' || t.name || ' (' || t.album || ')' as label,
current_month.cnt,
previous_month.position - current_month.position as change
FROM tracks t
JOIN artists a on a.id = t.artist_id
JOIN current_month current_month on current_month.track_id = t.id
LEFT OUTER join previous_month on previous_month.track_id = t.id
ORDER BY current_month.cnt desc, label asc
FETCH FIRST 20 ROWS ONLY;
Slide 19
Slide 19 text
@Entity
@SqlResultSetMapping(
name = "ChartMapping",
columns = {
@ColumnResult(name = "label", type = String.class),
@ColumnResult(name = "cnt", type = Integer.class),
@ColumnResult(name = "chage", type = Integer.class)
})
@NamedNativeQueries(
@NamedNativeQuery(
name = "ChartQuery",
resultSetMapping = "ChartMapping",
query = ""
+ "WITH \n"
+ " previous_month AS\n"
+ " (SELECT p.track_id, count(*) as cnt, \n"
+ " dense_rank() over(order by count(*) desc) as
position \n"
+ " FROM plays p \n"
+ " WHERE trunc(p.played_on, 'DD') between date'2016-04-01'
and date'2016-04-30' GROUP BY p.track_id),\n"
+ " current_month AS\n"
+ " (SELECT p.track_id, count(*) as cnt, \n"
+ " dense_rank() over(order by count(*) desc) as
position \n"
+ " FROM plays p \n"
+ " WHERE trunc(p.played_on, 'DD') between date'2016-05-01'
and date'2016-05-31' GROUP BY p.track_id)\n"
+ "SELECT a.artist || ' - ' || t.name || ' (' || t.album || ')'
as label,\n"
+ " current_month.cnt, \n"
+ " previous_month.position - current_month.position as
change\n"
+ " FROM tracks t\n"
+ " JOIN artists a on a.id = t.artist_id\n"
+ " JOIN current_month current_month on current_month.track_id
= t.id\n"
+ " LEFT OUTER join previous_month on previous_month.track_id
= t.id\n"
+ " ORDER BY current_month.cnt desc, label asc"
)
)
public class PlayEntity {
public static void main(String... a) {
// Don't do this at home
EntityManager entityManager;
List results =
entityManager.createNamedQuery("ChartQuery").setMaxResults(20).getResultList();
results.stream().forEach((record) -> {
String label = (String) record[0];
Integer cnt = (Integer) record[1];
Integer change = (Integer) record[2];
});
}
}
ERNSTHAFT? SQL TRIFFT JAVA
this.create
.with(currentMonth)
.with(previousMonth)
.select(label,
currentMonth.field("cnt"),
previousMonth.field("position").minus(
currentMonth.field("position")
).as("change")
)
.from(TRACKS)
.join(ARTISTS).onKey()
.join(currentMonth)
.on(currentMonth.field("track_id", BigDecimal.class)
.eq(TRACKS.ID))
.leftOuterJoin(previousMonth)
.on(previousMonth.field("track_id", BigDecimal.class)
.eq(TRACKS.ID))
.orderBy(currentMonth.field("cnt").desc(), label.asc())
.limit(n)
.fetch()
.formatJSON(response.getOutputStream());
Slide 20
Slide 20 text
JOOQ
Slide 21
Slide 21 text
JAVA OBJECT ORIENTED QUERYING
WAS IST JOOQ?
▸ „Query builder framework“
▸ Java DSL zur Generierung datenbankspezifischer Statements
▸ Das Schema ist die „treibende Kraft“
▸ Generierung eines Java-Schemas (Optional, aber empfohlen)
▸ Typsicher
▸ OpenSource für OpenSource Datenbanken, $ bis $$ für
Enterprise Datenbanken
Slide 22
Slide 22 text
No content
Slide 23
Slide 23 text
JOOQ
JAVA BASIERTES SCHEMA
BUILD
PROCESS
runs
GENERATOR
DATABASE
reverse
engineers
JAVA-BASED
SCHEMA
creates
APPLICATION DSL CONTEXT
creates
uses
SQL
generates
Slide 24
Slide 24 text
SCHEMA
DATENBANKMIGRATIONEN SIND ESSENTIELL
▸ Liquibase
▸ Flyway
Slide 25
Slide 25 text
DATENBANKMIGRATIONEN
WORKFLOW
▸ Build gegen Entwicklungsdatenbank
▸ startet Migration
▸ startet jOOQ Generator
▸ Anwendung gegen Produktionsdatenbank
▸ startet ebenfalls Migration
➡ Java Schema „passt“ immer zur Datenbank
Slide 26
Slide 26 text
WIE FUNKTIONIERT DAS MIT SPRING BOOT?
org.springframework.boot
spring-boot-starter-jooq
org.flywaydb
flyway-core
Slide 27
Slide 27 text
WIE FUNKTIONIERT DAS MIT SPRING BOOT?
org.springframework.boot
spring-boot-starter-jooq
org.jooq
jooq
org.jooq.pro
jooq
${jooq.version}
org.flywaydb
flyway-core
Slide 28
Slide 28 text
DER BUILD PROCESS IST NUR WENIG AUFWÄNDIGER
MAVEN PROPERTIES IN DEFAULT-KONFIGURATION NUTZEN
spring.datasource.url = @db.url@
spring.datasource.username = @db.username@
spring.datasource.password = @db.password@
jdbc:oracle:thin:@//localhost:1521/ORCLPDB1
doag2016
doag2016
DOAG2016
DEVELOPMENT CONNECTION IM POM
Slide 29
Slide 29 text
FLYWAY WÄHREND DES BUILDS UND(!) ZUR LAUFZEIT NUTZEN
BUILDTIME MIGRATION MIT FLYWAY
org.flywaydb
flyway-maven-plugin
${flyway.version}
generate-sources
migrate
${db.url}
${db.username}
${db.password}
filesystem:src/main/resources/db/migration
Slide 30
Slide 30 text
JOOQ NACH DEN MIGRATIONEN
JOOQ GENERATOR IMMER NACH DER MIGRATION STARTEN…
org.jooq.pro
jooq-codegen-maven
${jooq.version}
generate-sources
generate
oracle.jdbc.OracleDriver
${db.url}
${db.username}
${db.password}
Slide 31
Slide 31 text
DEMO
Slide 32
Slide 32 text
ZUSAMMENFASSUNG
▸ Direkte Abbildung von Abfragen auf URLs
▸ Von einfach bis kompliziert alles möglich
▸ Logging der generierten Queries ist hilfreich
▸ Einfache Übergabe von Parametern an Queries
▸ Oft benutzte Fragmente können wiederverwendet werden
▸ Records können auf beliebige POJOs abgebildet werden
▸ Spezialisiertes Domainen-Modell
▸ DAOs sind auch möglich
Slide 33
Slide 33 text
IST DAS
PORTABLE?
Slide 34
Slide 34 text
DEMO FUNKTIONIERT MIT ORACLE 12C UND POSTGRESQL
ZUM GRÖßTEN TEIL…
▸ jOOQ hält sich soweit wie möglich an den SQL-Standard
▸ Kann verschiedene SQL-Clauses emulieren
▸ Beispiele:
▸ LIMIT vs. OFFSET x ROWS / FETCH
▸ Herstellerspezifische Funktionen (trunc vs. date_trunc
etc.)
Slide 35
Slide 35 text
WANN BENUTZT
MAN DAS?
Slide 36
Slide 36 text
THE SQL… IT'S ALWAYS BEEN
THERE, IT WILL GUIDE YOU
Lukas Skyeder
THE PROBLEM WITH INTERNET QUOTES IS THAT YOU CANT ALWAYS DEPEND
ON THEIR ACCURACY" - ABRAHAM LINCOLN, 1864
Slide 37
Slide 37 text
USE CASES
▸ Kein Interesse an Managed-Objects
▸ Analytic functions (Use what your paid for)
▸ „Upsert“ (Merge-Statements)
▸ Partial selects
▸ Aufruf von Stored Procedures
Slide 38
Slide 38 text
ZURÜCK ZU DEN
SILVER BULLETS…
Slide 39
Slide 39 text
FETISH-ORIENTED
PROGRAMMING
Slide 40
Slide 40 text
Source: https://www.flickr.com/photos/gruenewiese/15790420752
NOTHING IS MORE
DANGEROUS THAN AN IDEA,
WHEN IT'S THE ONLY ONE
WE HAVE.
Émile Auguste Chartier
Slide 41
Slide 41 text
WO SIND JETZT DIE SILBERNEN KUGELN?
JPA / HIBERNATE *UND* JOOQ
▸ Automatische Datenbankmigrationen
▸ JPA / Hibernate zusammen mit Spring Data JPA
▸ JPQL Queries falls nötig (Eventuell Criteria Queries)
▸ Native Queries nicht in Annotationen verstecken!
▸ SQL Code komplexer Abfragen und Projektionen mit jOOQ
generieren
▸ An den EntityManager übergeben und Entitäten selektieren
▸ oder den DSL Context direkt benutzen
Slide 42
Slide 42 text
„DAS GENRE, DAS AM HÄUFIGSTEN
GESPIELT WURDE…“
KLASSISCHE SQL-INTERVIEW FRAGE
Slide 43
Slide 43 text
„ALS JPA-ENTITY!“
Slide 44
Slide 44 text
select id, genre
from (
select g.id, g.genre,
rank() over (order by count(*) desc) rnk
from plays p
join tracks t on p.track_id = t.id
join genres g on t.genre_id = g.id
group by g.id, g.genre
) src
where src.rnk = 1;
ALLE DINGE, DIE „AM MEISTEN SIND…“
SQL:2003, WINDOW-FUNKTION UND RANK()
Slide 45
Slide 45 text
JOOQ INNERHALB VON SPRING DATA REPOSITORIES
EIGENES INTERFACE, DAS „UNSERE“ METHODE DEKLARIERT
interface GenreRepositoryExt {
List findWithHighestPlaycount();
}
Slide 46
Slide 46 text
class GenreRepositoryImpl implements GenreRepositoryExt {
private final EntityManager entityManager;
private final DSLContext create;
public List findWithHighestPlaycount() {
final SelectQuery sqlGenerator =
this.create.select()./* Query */getQuery();
final String sql = sqlGenerator
.getSQL(ParamType.NAMED);
final Query query = entityManager
.createNativeQuery(sql, GenreEntity.class);
return query.getResultList();
}
}
JOOQ INNERHALB VON SPRING DATA REPOSITORIES
IMPLEMENTIERUNG DIESES INTERFACES
Slide 47
Slide 47 text
JOOQ INNERHALB VON SPRING DATA REPOSITORIES
DEKLARATION DES SPRING DATA JPA REPOSITORY
public interface GenreRepository extends
CrudRepository,
GenreRepositoryExt {
}
Slide 48
Slide 48 text
DEMO
Slide 49
Slide 49 text
RESSOURCEN
DANKE FÜR EURE ZEIT!
▸ Demo project:
github.com/michael-simons/bootiful-databases
▸ Slides:
speakerdeck.com/michaelsimons
▸ Kontakt: michael-simons.eu
▸ Twitter: @rotnroll666
Slide 50
Slide 50 text
RESSOURCEN
SPRING BOOT BUCH
▸ Q1 2018 im Dpunkt.verlag
▸ springbootbuch.de
▸ @springbootbuch
▸ Beispiele des Kapitels „Persistenz“:
github.com/springbootbuch/
database_examples
Beinhaltet JDBCTemplate, JPA und
JOOQ mit Pagila-Demo
Datenbank (PostgreSQL)