Live with your SQL-fetish and choose the right tool for the job

This is a new iteration of my database talk. It has taken quite a different direction since first presented at DOAG 2016. Should we as developers focus on only one technology and make that "our fetish"? Or is better, more resilient to have a broader tool belt. Check the slides and the code:

https://github.com/michael-simons/bootiful-databases
There's a nice video of that iteration here as well:
https://www.youtube.com/watch?v=NJ9ZJstVL9E

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.

Transcript

1. BOOTIFUL DATABASE-CENTRIC

   APPLICATIONS WITH JOOQ
   JOIN 2017 AT ORDINA JWORKS
   

   View Slide

2. JOIN 2017 AT ORDINA JWORKS
   LIVE WITH YOUR 

   SQL-FETISH
   

   View Slide

3. JOIN 2017 AT ORDINA JWORKS
   CHOOSING THE RIGHT
   TOOL FOR THE JOB
   

   View Slide

4. 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
   

   View Slide

5. DATABASE CENTRIC APPLICATIONS FOR THE ENERGY MARKET
   

   View Slide

6. DESKTOP GIS USING ORACLE SPATIAL
   

   View Slide

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

   View Slide

8. 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?
   

   View Slide

9. WELL…
   

   View Slide

10. Source: https://www.flickr.com/photos/133021668@N04/31403416393
    

    View Slide

11. Source: https://www.flickr.com/photos/potentialpast/5582667252
    

    View Slide

12. View Slide

13. TRACKING MUSICAL HABITS*
    EXAMPLE
    * Which are also timeseries
    

    View Slide

14. THE SCHEMA
    

    View Slide

15. HOW DO YOU WANT IT?
    PLAIN SQL
    Select *
    from tracks
    where album = 'True Survivor';
    

    View Slide

16. 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();
    }
    }
    

    View Slide

17. 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");
    }
    }
    

    View Slide

18. AND THEN SOMEONE*
    DEMANDED A REPORT…
    * „Business“
    

    View Slide

19. View Slide

20. 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;
    

    View Slide

21. @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());
    

    View Slide

22. 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…
    

    View Slide

23. ENTER JOOQ
    

    View Slide

24. 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
    

    View Slide

25. View Slide

26. JOOQ
    JAVA-BASED SCHEMA
    BUILD 

    PROCESS
    runs
    GENERATOR
    DATABASE
    reverse

    engineers
    JAVA-BASED

    SCHEMA
    creates
    APPLICATION DSL CONTEXT
    creates
    uses
    SQL
    generates
    

    View Slide

27. SCHEMA
    DATABASE MIGRATIONS ARE ESSENTIAL
    ▸ Liquibase
    ▸ Flyway
    

    View Slide

28. 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
    

    View Slide

29. HOW TO USE THIS WITH SPRING BOOT?
    
    org.springframework.boot
    spring-boot-starter-jooq
    
    
    org.flywaydb
    flyway-core
    
    

    View Slide

30. 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
    
    

    View Slide

31. 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
    

    View Slide

32. 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
    
    
    
    
    
    

    View Slide

33. 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}
    
    
    
    
    
    

    View Slide

34. DEMO
    

    View Slide

35. 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
    

    View Slide

36. IS THIS
    PORTABLE?
    

    View Slide

37. 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.)
    

    View Slide

38. WHEN TO USE
    THIS?
    

    View Slide

39. 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
    

    View Slide

40. 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
    

    View Slide

41. BACK TO SILVER
    BULLETS AND…
    

    View Slide

42. FETISH-ORIENTED

    PROGRAMMING
    

    View Slide

43. FETISH-ORIENTED

    PROGRAMMING
    

    View Slide

44. 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
    

    View Slide

45. 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
    

    View Slide

46. „GIVE ME THE GENRE THAT HAS
    BEEN PLAYED THE MOST…“
    CLASSIC SQL-INTERVIEW QUESTION
    

    View Slide

47. „AS A JPA-ENTITY!“
    

    View Slide

48. 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
    

    View Slide

49. PERFECTLY FINE TO USE INSIDE SPRING DATA JPA REPOSITORY
    CUSTOM INTERFACE DECLARING „OUR“ METHOD
    interface GenreRepositoryExt {
    List findWithHighestPlaycount();
    }
    

    View Slide

50. 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
    

    View Slide

51. PERFECTLY FINE TO USE INSIDE SPRING DATA JPA REPOSITORY
    DECLARE SPRING DATA JPA REPOSITORY, EXTENDING OURS, TOO
    public interface GenreRepository extends
    CrudRepository,
    GenreRepositoryExt {
    }
    

    View Slide

52. DEMO
    

    View Slide

53. RESOURCES
    THANK YOU FOR YOUR TIME
    ▸ Demo project:

    github.com/michael-simons/bootiful-databases
    ▸ Slides:

    speakerdeck.com/michaelsimons
    ▸ Kontakt: michael-simons.eu
    ▸ Twitter: @rotnroll666
    

    View Slide

54. 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)
    

    View Slide