Slide 1
Slide 1 text
BOOTIFUL DATABASE-CENTRIC
APPLICATIONS WITH JOOQ
JOIN 2017 AT ORDINA JWORKS
Slide 2
Slide 2 text
JOIN 2017 AT ORDINA JWORKS
LIVE WITH YOUR
SQL-FETISH
Slide 3
Slide 3 text
JOIN 2017 AT ORDINA JWORKS
CHOOSING THE RIGHT
TOOL FOR THE JOB
Slide 4
Slide 4 text
ABOUT ME
MICHAEL SIMONS
▸ Lives and works in Aachen
▸ Leader of the Euregio JUG
▸ Blogs about Java, Spring and
software architecture on
info.michael-simons.eu, working
on @springbootbuch
▸ Co-Autor arc(42) by example
▸ @rotnroll666 on Twitter
Slide 5
Slide 5 text
DATABASE CENTRIC APPLICATIONS FOR THE ENERGY MARKET
Slide 6
Slide 6 text
DESKTOP GIS USING ORACLE SPATIAL
Slide 7
Slide 7 text
JAVA AND
DATABASES
Plain SQL, ORM or
something in between?
Slide 8
Slide 8 text
EVERYONE HAS THEIR OWN PERSISTENCE FRAMEWORK
ACTUALLY, A LOT…
▸ JDBC
▸ Springs JDBCTemplate
▸ JPA
▸ JPQL
▸ Criteria query
▸ jOOQ
▸ any many more…
Y U NOT GIVE ME THE RIGHT TOOL?
Slide 9
Slide 9 text
WELL…
Slide 10
Slide 10 text
Source: https://www.flickr.com/photos/133021668@N04/31403416393
Slide 11
Slide 11 text
Source: https://www.flickr.com/photos/potentialpast/5582667252
Slide 12
Slide 12 text
No content
Slide 13
Slide 13 text
TRACKING MUSICAL HABITS*
EXAMPLE
* Which are also timeseries
Slide 14
Slide 14 text
THE SCHEMA
Slide 15
Slide 15 text
HOW DO YOU WANT IT?
PLAIN SQL
Select *
from tracks
where album = 'True Survivor';
Slide 16
Slide 16 text
HOW DO YOU WANT IT?
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 17
Slide 17 text
HOW DO YOU WANT IT?
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 18
Slide 18 text
AND THEN SOMEONE*
DEMANDED A REPORT…
* „Business“
Slide 19
Slide 19 text
No content
Slide 20
Slide 20 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 21
Slide 21 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];
});
}
}
REALLY? SQL MEETS 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 22
Slide 22 text
JUST BECAUSE YOU'RE USING
HIBERNATE, DOESN'T MEAN YOU HAVE
TO USE IT FOR EVERYTHING.
Gavin King
CAN JPA / HIBERNATE DO THIS? SURE, BUT…
Slide 23
Slide 23 text
ENTER JOOQ
Slide 24
Slide 24 text
JAVA OBJECT ORIENTED QUERYING
WHAT IS JOOQ?
▸ „Query builder framework“
▸ Java DSL for generating database and schema specific
statements
▸ The schema is the leading factor
▸ jOOQ generates a corresponding Java-schema
▸ Typesafe
▸ OpenSource for OpenSource Datenbanken, $ ~ $$ for
Enterprise databases
Slide 25
Slide 25 text
No content
Slide 26
Slide 26 text
JOOQ
JAVA-BASED SCHEMA
BUILD
PROCESS
runs
GENERATOR
DATABASE
reverse
engineers
JAVA-BASED
SCHEMA
creates
APPLICATION DSL CONTEXT
creates
uses
SQL
generates
Slide 27
Slide 27 text
SCHEMA
DATABASE MIGRATIONS ARE ESSENTIAL
▸ Liquibase
▸ Flyway
Slide 28
Slide 28 text
DATABASEMIGRATIONS
WORKFLOW
▸ Build against development database
▸ starts migration
▸ starts jOOQ generator
▸ Application runs against production database
▸ starts migration again
➡ Generated Java schema is in sync with the production
database
Slide 29
Slide 29 text
HOW TO USE THIS WITH SPRING BOOT?
org.springframework.boot
spring-boot-starter-jooq
org.flywaydb
flyway-core
Slide 30
Slide 30 text
HOW TO USE THIS WITH SPRING BOOT?
org.springframework.boot
spring-boot-starter-jooq
org.jooq
jooq
org.jooq.pro
jooq
${jooq.version}
org.flywaydb
flyway-core
Slide 31
Slide 31 text
THE BUILD PROCESS IS A LITTLE BIT „HARDER“
USE MAVEN PROPERTIES IN DEFAULT CONFIGURATION
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 INSIDE POM
Slide 32
Slide 32 text
RUN FLYWAY DURING BUILD- AND RUNTIME
BUILDTIME MIGRATION WITH FLYWAY
org.flywaydb
flyway-maven-plugin
${flyway.version}
generate-sources
migrate
${db.url}
${db.username}
${db.password}
filesystem:src/main/resources/db/migration
Slide 33
Slide 33 text
JOOQ AFTER MIGRATIONS
JOOQ GENERATOR ALWAYS AFTER MIGRATIONS…
org.jooq.pro
jooq-codegen-maven
${jooq.version}
generate-sources
generate
oracle.jdbc.OracleDriver
${db.url}
${db.username}
${db.password}
Slide 34
Slide 34 text
DEMO
Slide 35
Slide 35 text
SUMMARY
▸ Map queries to URLs
▸ Simple as well as complex queries
▸ Logging of generated queries is quite useful
▸ Spring MVC parameters can be used without fear of SQL injection as
bind parameters
▸ Reuse query fragments
▸ Mapping records to POJOs and other
▸ DAOs possible, too
▸ Use jOOQ as SQL generator only
Slide 36
Slide 36 text
IS THIS
PORTABLE?
Slide 37
Slide 37 text
ORIGINAL DEMO WAS CREATED ON ORACLE 12C… NOW ON POSTGRESQL
MOSTLY…
▸ jOOQ tries to be as close as possible to SQL-standards
▸ knows how to emulate clauses for various databases
▸ Example
▸ LIMIT vs. OFFSET x ROWS / FETCH
▸ Vendor specific functions (trunc vs. date_trunc etc.)
Slide 38
Slide 38 text
WHEN TO USE
THIS?
Slide 39
Slide 39 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 40
Slide 40 text
USE CASES
▸ Analytic functions (Use the database features you most
likely paid for already)
▸ „Upsert“ (Merge-Statements)
▸ Partial selects (only the columns you need without going
through Entity Graphs)
▸ Calling stored procedures
Slide 41
Slide 41 text
BACK TO SILVER
BULLETS AND…
Slide 42
Slide 42 text
FETISH-ORIENTED
PROGRAMMING
Slide 43
Slide 43 text
FETISH-ORIENTED
PROGRAMMING
Slide 44
Slide 44 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 45
Slide 45 text
WHERE ARE THE SILVER BULLETS NOW?
JPA / HIBERNATE *AND* JOOQ
▸ Automatic database migrations
▸ JPA / Hibernate with Spring Data JPA
▸ JPQL Queries if necessary (Criteria queries if masochistic)
▸ Don’t hide native queries in annotations!
▸ Create complex queries and projections with jOOQ
▸ Pass to EntityManager to retrive Entities or
▸ use DSL Context directly
Slide 46
Slide 46 text
„GIVE ME THE GENRE THAT HAS
BEEN PLAYED THE MOST…“
CLASSIC SQL-INTERVIEW QUESTION
Slide 47
Slide 47 text
„AS A JPA-ENTITY!“
Slide 48
Slide 48 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;
ALL ENTRIES THAT HAVE „THE MOST“
FIRST: PLAIN SQL
Slide 49
Slide 49 text
PERFECTLY FINE TO USE INSIDE SPRING DATA JPA REPOSITORY
CUSTOM INTERFACE DECLARING „OUR“ METHOD
interface GenreRepositoryExt {
List findWithHighestPlaycount();
}
Slide 50
Slide 50 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();
}
}
PERFECTLY FINE TO USE INSIDE SPRING DATA JPA REPOSITORY
A CUSTOM CLASS IMPLEMENTING THAT INTERFACE ALONE
Slide 51
Slide 51 text
PERFECTLY FINE TO USE INSIDE SPRING DATA JPA REPOSITORY
DECLARE SPRING DATA JPA REPOSITORY, EXTENDING OURS, TOO
public interface GenreRepository extends
CrudRepository,
GenreRepositoryExt {
}
Slide 52
Slide 52 text
DEMO
Slide 53
Slide 53 text
RESOURCES
THANK YOU FOR YOUR TIME
▸ Demo project:
github.com/michael-simons/bootiful-databases
▸ Slides:
speakerdeck.com/michaelsimons
▸ Kontakt: michael-simons.eu
▸ Twitter: @rotnroll666
Slide 54
Slide 54 text
RESOURCES
SPRING BOOT BUCH
▸ Q4 2017 at Dpunkt.verlag
▸ springbootbuch.de
▸ @springbootbuch
▸ Example for chapter „Persistenz“:
github.com/springbootbuch/database_examples
Contains JDBCTemplate, JPA and JOOQ with Pagila-
Demo Database (PostgreSQL)