Intro to Databases

Best practices

No Hibernate/JPA

• https://www.toptal.com/java/how-hibernate-ruined-my-career
• https://www.reddit.com/r/java/comments/w4abyg/is_there_a_reason_to_not_use_spring_data_jpa_and/

Persistence layer

• JOOQ queries
  • https://github.com/jOOQ/jOOQ
  • Please start to use jooq for queries instead of hibernate/jpa if is possible. Jooq respects much better the Principle of least surprise. No special sessions, no strange java abstractions on top of queries, dirty detection, special collections, etc. Is a much safer library than hibernate and jpa.
• spring JdbcTemplate queries
• deprecated
  • returning Object[] from queries instead of POJO
    • see https://stackoverflow.com/questions/13012584/jpa-how-to-convert-a-native-query-result-set-to-pojo-class-collection
  • hibernate mappings
  • hibernate queries
  • hibernate native queries
  • hibernate automagic fetch/lazy for one-to-many, many-to-one, many-to-many
  • jdbc low level api

Concepts & Notions

• Relational vs Non Relational Databases
  • Relational
    • standard sql
    • extensions
      • stored procedures
  • Non Relational - see https://martinfowler.com/tags/noSQL.html
    • document
    • key-value
    • graph
• ORM - Object Relational Mapping
• ORM vs non ORM
  • https://github.com/bwajtr/java-persistence-frameworks-comparison
• impedance mismatch
  • mapping object hierarchies to one or multiple tables - https://www.baeldung.com/hibernate-inheritance
• session
• transactions
  • database
  • JPA
    • JPA-2.2 - 2017 - https://download.oracle.com/otndocs/jcp/persistence-2_2-mrel-eval-spec/index.html
  • JTA - cross transactional
• isolation level
• see Intro to Resource Pooling
  • connection pooling
• Database Migrations
  • Liquibase
    • works also via spring
  • Flyway - https://flywaydb.org/
    • works also via spring
  • Carbon5 - old
  • Evolutions - from playframework
    • https://www.playframework.com/documentation/2.8.x/Evolutions
• Poliglot database
• Database refactoring techniques
• Profiling database
• Rapid prototyping
  • h2
    • in memory
    • file
• CRUD
• upsert operation - insert or update

Database clients

• Simple
  • jdbc - low level connection
  • spring templates - better error handling
    • simple - https://www.baeldung.com/spring-jdbc-jdbctemplate
    • named templates
• NORM - Not ORM
  • Exposed - Kotlin SQL Framework
    • https://github.com/JetBrains/Exposed
  • Slick - scala
    • https://scala-slick.org/doc/3.3.3/gettingstarted.html#hello-slick
  • Anorm - scala
    • https://playframework.github.io/anorm/
    • https://github.com/playframework/anorm
  • ebean
    • https://github.com/ebean-orm/ebean
    • https://ebean.io
    • a fork with avaje ebeanorm - https://mvnrepository.com/artifact/org.avaje.ebeanorm/avaje-ebeanorm/8.1.1
    • intro: https://www.baeldung.com/ebean-orm
    • is using JPA annotations but is not an JPA implementation
• ORM
  • JPA
    • CONS
      • Hibernate deletes by first fetching entire object - https://stackoverflow.com/questions/13210638/hibernate-delete-query
  • Hibernate - extension of JPA
• others - everyobdy invents one
  • https://github.com/dieselpoint/norm
  • https://www.findbestopensource.com/product/ebean-orm-ebean

Databases

• postgres
  • upsert
    • https://www.postgresql.org/docs/9.5/sql-insert.html#SQL-ON-CONFLICT

Resources

• http://www.h2database.com/html/cheatSheet.html