Data Geekery Trainings
Get Back in Control of Your SQL with jOOQ (6h)
This training is perfect for companies looking into a jOOQ integration, or migrating from another ORM to jOOQ. We'll help you get the most the quickest out of jOOQ.
Java does not provide an API to directly support the expressiveness and power of SQL. jOOQ is a dual-licensed Open Source product filling this gap. It implements SQL itself as a type safe domain-specific language in Java.
jOOQ is a good choice in a Java application where SQL and its proprietary features of the database in use are important. It shows, how a modern domain-specific language can greatly increase developer productivity, internalising SQL into Java.
- Module 1: The idea behind jooq
- Module 2: Introduction to Sakila, the Open Source Demo Database
- Module 3: The jOOQ code generator
- Module 4: The jOOQ DSL
- Module 5: Running simple, type safe queries using the jOOQ DSL
- Module 6: Running complex, type safe queries using the jOOQ DSL
- Module 7: Executing stored procedures via jOOQ
- Module 8: How jOOQ standardises SQL across various databases (e.g. H2, PostgreSQL, Oracle)
- Finding pleasure in writing SQL
- Understanding how to use SQL effectively
- Saving time and money in your project by replacing JDBC with jOOQ
Software developers and architects that work with Java and SQL
This workshop enables developers to use SQL as a first class language in their applications. Due to the historic divide between database and programming skills the majority of SQL written by developers suffers from at least one of the following problems:
- It is overly complex and could be greatly simplified via refactoring
- It has poor performance, due to insufficient knowledge of DB internals
- It does not leverage modern and/or vendor specific SQL functions
All of these issues can be fixed by encouraging developers to treat SQL with as much respect as their normal code, educating them about performance implications and informing them about new features available in commercial and open source databases. This masterclass achieves these three goals.
Prerequisites and Logistics
This is a 2-day day workshop delivered using the DB2 LuW, MySQL 8.0+, Oracle, PostgreSQL, or SQL Server databases. Delegates should be experienced at writing SQL queries. Delegates can bring their own laptop, in which case setup instructions will be provided.
DAY 1 - SQL Functionality
- Introduction to the course databases and revision of core SQL constructs
- Advanced constructs, such as row value expressions and hierarchical SQL
- Applying window functions for complex calculations within the database
- Modern use of DML (e.g. using MERGE instead of INSERT and UPDATE)
- Using esoteric data types (json, hstore, arrays, composite types, ranges etc…)
DAY 2 - SQL Performance
- The cost based optimiser (CBO)
- The inner workings of an index
- Writing performant predicates
- Constraints, or why metadata matters
- Faster writes / data manipulation (DML)
- Antipatterns, myths, and best practices