Database centric applications with Spring Boot and jOOQ

Database centric applications with Spring Boot and jOOQ

jOOQ is one of several quite different technologies to access relational data from Java based applications.
jOOQ is short for "Java object oriented querying" and describes a query builder framework that takes a look at your database schema, independent wether you use an Open Source database like PostgreSQL or an commercial product like Oracle Database., and provides you with a domain specific language (DSL) for generating statements.
jOOQs goal is explicitly not to shield the user from SQL but providing a type safe way to use it.
Learn in this session who you can facilitate the "magic" of Spring Boot to provide jOOQ with needed resources and then use it to publish advanced analytic queries as HTTP apis.
Along the way you learn how automatic database migrations help you to do continuous delivery even with database centric applications.

Demos showed:
https://github.com/michael-simons/DOAG2016
https://github.com/michael-simons/tweetarchive

20492a196bb034ad3aa7e05e593fede9?s=128

Michael Simons

January 11, 2017
Tweet

Transcript

  1. SPRING BOOT AND JOOQ DATABASE CENTRIC APPLICATIONS WITH

  2. ABOUT ME MICHAEL SIMONS ▸ Lives and works in Aachen

    ▸ Developer at ENERKO INFORMATIK ▸ Leader of the Euregio JUG ▸ Blogs about Java, Spring and software architecture on
 info.michael-simons.eu ▸ Co-Autor arc(42) by example ▸ @rotnroll666 on Twitter
  3. DATABASE CENTRIC APPLICATIONS FOR THE ENERGY MARKET

  4. DESKTOP GIS USING ORACLE SPATIAL

  5. JAVA AND DATABASES Plain SQL, ORM or something in between?

  6. EVERYONE HAS THEIR OWN PERSISTENCE FRAMEWORK ACTUALLY, A LOT… ▸

    JDBC ▸ Springs JDBCTemplate ▸ JPA ▸ JPQL ▸ Criteria query ▸ Apache JDO ▸ many more ▸ and among them
  7. JOOQ

  8. TRACKING MUSICAL HABITS EXAMPLE

  9. None
  10. None
  11. None
  12. THE SCHEMA

  13. HOW DO YOU WANT IT?

  14. -- This way? Select * from tracks where album =

    'True Survivor'; HOW DO YOU WANT IT?
  15. -- This way? Select * from tracks where album =

    'True Survivor'; // 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<Track> tracks = entityManager.createQuery("Select t from tracks where album = :album") .setParameter("album", "True Survivor") .getResultList(); } } HOW DO YOU WANT IT?
  16. -- This way? Select * from tracks where album =

    'True Survivor'; // 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<Track> tracks = entityManager.createQuery("Select t from tracks where album = :album") .setParameter("album", "True Survivor") .getResultList(); } } // JPA + Spring Data: Much better! public interface TrackRepository extends JpaRepository<TrackEntity, Integer> { public List<Track> findAllByAlbum(final String name); public static void main(String...a) { TrackRepository trackRepository; final List<Track> tracks = trackRepository.findAllByAlbum("True Survivor"); } } HOW DO YOU WANT IT?
  17. @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<Object[]> 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?
  18. @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<Object[]> 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());
  19. 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
  20. None
  21. JOOQ JAVA-BASED SCHEMA

  22. JOOQ JAVA-BASED SCHEMA BUILD 
 PROCESS

  23. JOOQ JAVA-BASED SCHEMA BUILD 
 PROCESS runs GENERATOR

  24. JOOQ JAVA-BASED SCHEMA BUILD 
 PROCESS runs GENERATOR DATABASE reverse


    engineers
  25. JOOQ JAVA-BASED SCHEMA BUILD 
 PROCESS runs GENERATOR DATABASE reverse


    engineers JAVA-BASED
 SCHEMA creates
  26. JOOQ JAVA-BASED SCHEMA BUILD 
 PROCESS runs GENERATOR DATABASE reverse


    engineers JAVA-BASED
 SCHEMA creates APPLICATION
  27. JOOQ JAVA-BASED SCHEMA BUILD 
 PROCESS runs GENERATOR DATABASE reverse


    engineers JAVA-BASED
 SCHEMA creates APPLICATION DSL CONTEXT creates
  28. JOOQ JAVA-BASED SCHEMA BUILD 
 PROCESS runs GENERATOR DATABASE reverse


    engineers JAVA-BASED
 SCHEMA creates APPLICATION DSL CONTEXT creates uses SQL generates
  29. SCHEMA DATABASE MIGRATIONS ARE ESSENTIAL ▸ Liquibase ▸ Flyway

  30. DATABASEMIGRATIONS WORKFLOW

  31. DATABASEMIGRATIONS WORKFLOW ▸ Build against development database ▸ starts migration

    ▸ starts jOOQ generator
  32. DATABASEMIGRATIONS WORKFLOW ▸ Build against development database ▸ starts migration

    ▸ starts jOOQ generator ▸ Application runs against production database ▸ starts migration again
  33. 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
  34. HOW TO USE THIS WITH SPRING BOOT?

  35. HOW TO USE THIS WITH SPRING BOOT?

  36. HOW TO USE THIS WITH SPRING BOOT? <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jooq</artifactId>

    </dependency> <dependency> <groupId>org.flywaydb</groupId> <artifactId>flyway-core</artifactId> </dependency>
  37. <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-jooq</artifactId> <exclusions> <exclusion> <groupId>org.jooq</groupId> <artifactId>jooq</artifactId> </exclusion> </exclusions> </dependency>

    <dependency> <groupId>org.jooq.pro</groupId> <artifactId>jooq</artifactId> <version>${jooq.version}</version> </dependency> <dependency> <groupId>org.flywaydb</groupId> <artifactId>flyway-core</artifactId> </dependency> HOW TO USE THIS WITH SPRING BOOT?
  38. THE BUILD PROCESS IS A LITTLE BIT „HARDER“

  39. THE BUILD PROCESS IS A LITTLE BIT „HARDER“ DEVELOPMENT CONNECTION

    INSIDE BUILD FILE <properties> <db.url>jdbc:oracle:thin:@//localhost:1521/ORCLPDB1</db.url> <db.username>doag2016</db.username> <db.password>doag2016</db.password> <db.schema>DOAG2016</db.schema> </properties>
  40. THE BUILD PROCESS IS A LITTLE BIT „HARDER“ DEVELOPMENT CONNECTION

    INSIDE BUILD FILE <properties> <db.url>jdbc:oracle:thin:@//localhost:1521/ORCLPDB1</db.url> <db.username>doag2016</db.username> <db.password>doag2016</db.password> <db.schema>DOAG2016</db.schema> </properties> spring.datasource.url = @db.url@ spring.datasource.username = @db.username@ spring.datasource.password = @db.password@ USE THEM IN DEFAULT CONFIG, TOO
  41. FLYWAY RUN THE SAME FLYWAY MIGRATIONS AS BOOT <build> <plugins>

    <plugin> <groupId>org.flywaydb</groupId> <artifactId>flyway-maven-plugin</artifactId> <version>${flyway.version}</version> <executions> <execution> <phase>generate-sources</phase> <goals> <goal>migrate</goal> </goals> </execution> </executions> <configuration> <url>${db.url}</url> <user>${db.username}</user> <password>${db.password}</password> <locations> <location>filesystem:src/main/resources/db/migration</location> </locations> </configuration> </plugin> </plugins> </build>
  42. JOOQ JOOQ AFTER MIGRATIONS <build> <plugins> <plugin> <groupId>org.jooq.pro</groupId> <artifactId>jooq-codegen-maven</artifactId> <version>${jooq.version}</version>

    <executions> <execution> <phase>generate-sources</phase> <goals> <goal>generate</goal> </goals> </execution> </executions> <configuration> <jdbc> <driver>oracle.jdbc.OracleDriver</driver> <url>${db.url}</url> <user>${db.username}</user> <password>${db.password}</password> </jdbc> <generator> <!-- whatever --> </generator> </configuration> </plugin> </plugins> </build>
  43. DEMO

  44. 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
  45. 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
  46. THE WINDOW FUNCTION…IT MOVES THROUGH EVERY LIVING THING USE CASE

    ANALYTIC FUNCTIONS 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;
  47. USE THE INDEX, LUKE USE CASE „UPSERT“ / MERGE STATEMENT

    MERGE INTO tablename USING table_reference ON (condition) WHEN MATCHED THEN UPDATE SET column1 = value1 [, column2 = value2 ...] WHEN NOT MATCHED THEN INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...]);
  48. JPA / HIBERNATE AND JOOQ „JUST BECAUSE YOU'RE USING HIBERNATE,

    DOESN'T MEAN YOU HAVE TO USE IT FOR EVERYTHING.“ GAVIN KING
  49. JPA / HIBERNATE AND JOOQ „JUST BECAUSE YOU'RE USING HIBERNATE,

    DOESN'T MEAN YOU HAVE TO USE IT FOR EVERYTHING.“ GAVIN KING ▸ Automatic database migrations
  50. JPA / HIBERNATE AND JOOQ „JUST BECAUSE YOU'RE USING HIBERNATE,

    DOESN'T MEAN YOU HAVE TO USE IT FOR EVERYTHING.“ GAVIN KING ▸ Automatic database migrations ▸ JPA / Hibernate with Spring Data JPA
  51. JPA / HIBERNATE AND JOOQ „JUST BECAUSE YOU'RE USING HIBERNATE,

    DOESN'T MEAN YOU HAVE TO USE IT FOR EVERYTHING.“ GAVIN KING ▸ Automatic database migrations ▸ JPA / Hibernate with Spring Data JPA ▸ JPQL Queries if necessary (Criteria queries if masochistic) ▸ Don’t hide native queries in annotations!
  52. JPA / HIBERNATE AND JOOQ „JUST BECAUSE YOU'RE USING HIBERNATE,

    DOESN'T MEAN YOU HAVE TO USE IT FOR EVERYTHING.“ GAVIN KING ▸ 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
  53. None
  54. None
  55. USING SQL-1999 A HIERARCHICAL QUERY WITH RECURSIVE CTE WITH RECURSIVE

    cte AS ( SELECT id, content FROM tweets WHERE id = ? UNION ALL SELECT t.id, t.content FROM cte c JOIN tweets t on t.in_reply_to_status_id = c.id ) SELECT * FROM cte;
  56. PERFECTLY FINE TO USE INSIDE SPRING DATA JPA REPOSITORY 2

    INTERFACES, ONE CLASS
  57. PERFECTLY FINE TO USE INSIDE SPRING DATA JPA REPOSITORY 2

    INTERFACES, ONE CLASS ▸ Spring Data JPA Repository
 public interface TweetRepository extends Repository<TweetEntity, Long>, 
 TweetRepositoryExt
  58. PERFECTLY FINE TO USE INSIDE SPRING DATA JPA REPOSITORY 2

    INTERFACES, ONE CLASS ▸ Spring Data JPA Repository
 public interface TweetRepository extends Repository<TweetEntity, Long>, 
 TweetRepositoryExt ▸ Custom extension TweetRepositoryExt containing
 List<TweetEntity> getTweetHierarchy(final long id);
  59. PERFECTLY FINE TO USE INSIDE SPRING DATA JPA REPOSITORY 2

    INTERFACES, ONE CLASS ▸ Spring Data JPA Repository
 public interface TweetRepository extends Repository<TweetEntity, Long>, 
 TweetRepositoryExt ▸ Custom extension TweetRepositoryExt containing
 List<TweetEntity> getTweetHierarchy(final long id); ▸ Class implementing the our jOOQ code
 public class TweetRepositoryImpl implements TweetRepositoryExt
  60. DEMO

  61. RESOURCES KNOW YOUR TOOLS! ▸ https://modern-sql.com ▸ https://blog.jooq.org ▸ https://vladmihalcea.com/tutorials/

    hibernate/ ▸ http://www.thoughts-on-java.org/ persistence/ ▸ Most certainly the manuals!
  62. RESOURCES THANK YOU FOR YOUR TIME! ▸ Demo projects:
 github.com/michael-simons/DOAG2016


    github.com/michael-simons/tweetarchive ▸ Slides:
 speakerdeck.com/michaelsimons ▸ German article in DOAG RedStack magazine about Spring Boot & jOOQ in Q2/2017 ▸ Kontakt: michael-simons.eu ▸ Twitter: @rotnroll666