Java & SQL Stronger Together @MarkusWinand @ModernSQL - - PowerPoint PPT Presentation

▶
java sql stronger together
SMART_READER_LITE
LIVE PREVIEW

Java & SQL Stronger Together @MarkusWinand @ModernSQL - - PowerPoint PPT Presentation

Apr 12, 2023 150 likes •1.38k views

Java & SQL Stronger Together @MarkusWinand @ModernSQL Picture: Africa Studio@Shutterstock We can solve any problem by introducing an extra level of abstraction (Based on the fundamental theorem of software engineering)

slide-1
SLIDE 1

Java & SQL
 Stronger Together

@MarkusWinand @ModernSQL

Picture: Africa Studio@Shutterstock

slide-2
SLIDE 2
slide-3
SLIDE 3

“We can solve any problem by introducing an extra level of abstraction”

(Based on the “fundamental theorem of software engineering”)

slide-4
SLIDE 4
slide-5
SLIDE 5

Database

slide-6
SLIDE 6

Database JDBC

Provides unique API for different SQL- Databases

slide-7
SLIDE 7

Database JDBC JPA

Provides unique API for different SQL- Databases Maps objects from/to tables

slide-8
SLIDE 8

Database JDBC JPA Spring Data JPA

Provides unique API for different SQL- Databases Maps objects from/to tables Makes common tasks very simple

slide-9
SLIDE 9

Database JDBC JPA Spring Data JPA Your Code

Provides unique API for different SQL- Databases Maps objects from/to tables Makes common tasks very simple

slide-10
SLIDE 10

Database JDBC JPA Spring Data JPA Your Code

Provides unique API for different SQL- Databases Maps Objects from/to tables Makes common tasks very simple 90%? Doesn’t provide 100% of underlying functionality

slide-11
SLIDE 11

Database JDBC JPA Spring Data JPA Your Code

Provides unique API for different SQL- Databases Maps Objects from/to tables Makes common tasks very simple 90%? Proprietary data types?

—> (VendorCls)jdbcCls

slide-12
SLIDE 12

Database JDBC JPA Spring Data JPA Your Code

Provides unique API for different SQL- Databases Maps Objects from/to tables Makes common tasks very simple 90%? Proprietary data types?

—> (VendorCls)jdbcCls

The processing is not about objects? —> QueryBuilder, JPQL

  • r native queries
slide-13
SLIDE 13

Database JDBC JPA Spring Data JPA Your Code

Provides unique API for different SQL- Databases Maps Objects from/to tables Makes common tasks very simple 90%? Proprietary data types?

—> (VendorCls)jdbcCls

The processing is not about objects? —> QueryBuilder, JPQL

  • r native queries

“Hibernate’s design goal is to relieve the developer from 95% of common data persistence-related programming tasks[…] Hibernate does not hide the power of SQL
 from you[…]”

— Hibernate ORM User Guide (second paragraph)

slide-14
SLIDE 14

Database JDBC JPA Spring Data JPA Your Code

Provides unique API for different SQL- Databases Maps Objects from/to tables Makes common tasks very simple 90%? Proprietary data types?

—> (VendorCls)jdbcCls

The processing is not about objects? —> QueryBuilder, JPQL

  • r native queries
slide-15
SLIDE 15

Database JDBC JPA Spring Data JPA Your Code

Provides unique API for different SQL- Databases Maps Objects from/to tables Makes common tasks very simple 90%? Proprietary data types?

—> (VendorCls)jdbcCls

The processing is not about objects? —> QueryBuilder, JPQL

  • r native queries

findBy… not powerful enough? —> QueryBuilder, JPQL

  • r native queries
slide-16
SLIDE 16

What is the


right level of abstraction


for any given problem?

slide-17
SLIDE 17

Pattern #1: Lists

1. 2. 3.

Go for a walk in the woods Picnic Improve SQL-foo

BUCKET LIST

Spring

slide-18
SLIDE 18

Database JDBC JPA Spring Data JPA Your Code

List: All Attributes

slide-19
SLIDE 19

Database JDBC JPA Spring Data JPA Your Code

SELECT * FROM tbl

List: All Attributes

slide-20
SLIDE 20

Database JDBC JPA Spring Data JPA Your Code

ResultSet rs = c.prepareStatement("SELECT *" + " FROM tbl") .executeQuery(); SELECT * FROM tbl

List: All Attributes

slide-21
SLIDE 21

Database JDBC JPA Spring Data JPA Your Code

Collection<Entity> c = em.createQuery("FROM Entity") .getResultList(); ResultSet rs = c.prepareStatement("SELECT *" + " FROM tbl") .executeQuery(); SELECT * FROM tbl

List: All Attributes

slide-22
SLIDE 22

Database JDBC JPA Spring Data JPA Your Code

Collection<Entity> c = em.createQuery("FROM Entity") .getResultList(); Collection<Entity> c = r.findAll(); ResultSet rs = c.prepareStatement("SELECT *" + " FROM tbl") .executeQuery(); SELECT * FROM tbl

List: All Attributes

slide-23
SLIDE 23

Database JDBC JPA Spring Data JPA Your Code

List: Some Attributes

slide-24
SLIDE 24

Database JDBC JPA Spring Data JPA Your Code

SELECT a1, … FROM tbl

List: Some Attributes

slide-25
SLIDE 25

Database JDBC JPA Spring Data JPA Your Code

SELECT a1, … FROM tbl

List: Some Attributes

Runtime improvement: ~0% - 10

000%
slide-26
SLIDE 26

Database JDBC JPA Spring Data JPA Your Code

SELECT a1, … FROM tbl

List: Some Attributes

Runtime improvement: ~0% - 10

000%

Affects:

slide-27
SLIDE 27

Database JDBC JPA Spring Data JPA Your Code

SELECT a1, … FROM tbl

List: Some Attributes

Runtime improvement: ~0% - 10

000%

Affects:

  • Java: GC
slide-28
SLIDE 28

Database JDBC JPA Spring Data JPA Your Code

SELECT a1, … FROM tbl

List: Some Attributes

Runtime improvement: ~0% - 10

000%

Affects:

  • Java: GC
  • Network
slide-29
SLIDE 29

Database JDBC JPA Spring Data JPA Your Code

SELECT a1, … FROM tbl

List: Some Attributes

Runtime improvement: ~0% - 10

000%

Affects:

  • Java: GC
  • Network
  • Database:
slide-30
SLIDE 30

Database JDBC JPA Spring Data JPA Your Code

SELECT a1, … FROM tbl

List: Some Attributes

Runtime improvement: ~0% - 10

000%

Affects:

  • Java: GC
  • Network
  • Database:
  • Index Only Scan
slide-31
SLIDE 31

Database JDBC JPA Spring Data JPA Your Code

SELECT a1, … FROM tbl

List: Some Attributes

Runtime improvement: ~0% - 10

000%

Affects:

  • Java: GC
  • Network
  • Database:
  • Index Only Scan
  • Mem + CPU

(e.g. sorting)

slide-32
SLIDE 32

Database JDBC JPA Spring Data JPA Your Code

ResultSet rs = c.prepareStatement("SELECT a1, …" + " FROM tbl") .executeQuery(); SELECT a1, … FROM tbl

List: Some Attributes

slide-33
SLIDE 33

Database JDBC JPA Spring Data JPA Your Code

Collection<DTO> c = em.createQuery("SELECT new DTO(a1, …)" + " FROM Ent") .getResultList(); ResultSet rs = c.prepareStatement("SELECT a1, …" + " FROM tbl") .executeQuery(); SELECT a1, … FROM tbl

List: Some Attributes

slide-34
SLIDE 34

Database JDBC JPA Spring Data JPA Your Code

Collection<DTO> c = em.createQuery("SELECT new DTO(a1, …)" + " FROM Ent") .getResultList(); ResultSet rs = c.prepareStatement("SELECT a1, …" + " FROM tbl") .executeQuery(); SELECT a1, … FROM tbl

List: Some Attributes

Or: LAZY attributes

slide-35
SLIDE 35

Database JDBC JPA Spring Data JPA Your Code

Collection<DTO> c = em.createQuery("SELECT new DTO(a1, …)" + " FROM Ent") .getResultList(); ResultSet rs = c.prepareStatement("SELECT a1, …" + " FROM tbl") .executeQuery(); SELECT a1, … FROM tbl

List: Some Attributes

Or: LAZY attributes

<P> Collection<P> findAllProjectedBy(Class<P> p);

slide-36
SLIDE 36

Database JDBC JPA Spring Data JPA Your Code

Collection<DTO> c = em.createQuery("SELECT new DTO(a1, …)" + " FROM Ent") .getResultList(); ResultSet rs = c.prepareStatement("SELECT a1, …" + " FROM tbl") .executeQuery(); SELECT a1, … FROM tbl

List: Some Attributes

Or: LAZY attributes

<P> Collection<P> findAllProjectedBy(Class<P> p); public interface Proj { … getA1(); … }

slide-37
SLIDE 37

Database JDBC JPA Spring Data JPA Your Code

Collection<DTO> c = em.createQuery("SELECT new DTO(a1, …)" + " FROM Ent") .getResultList(); ResultSet rs = c.prepareStatement("SELECT a1, …" + " FROM tbl") .executeQuery(); SELECT a1, … FROM tbl

List: Some Attributes

Or: LAZY attributes

<P> Collection<P> findAllProjectedBy(Class<P> p); Collection<Proj> c = r.findAllProjectedBy(Proj.class); public interface Proj { … getA1(); … }

slide-38
SLIDE 38

Database JDBC JPA Spring Data JPA Your Code

List: Attributes of Multiple Objects

slide-39
SLIDE 39

Database JDBC JPA Spring Data JPA Your Code

SELECT a.a1, b.a1, … FROM tbl a JOIN tbl2 b

List: Attributes of Multiple Objects

slide-40
SLIDE 40

Database JDBC JPA Spring Data JPA Your Code

Collection<DTO> c = em.createQuery("SELECT " + "new DTO(a.a1, b.a1, …)" + " FROM Entity a JOIN a.child b") .getResultList(); SELECT a.a1, b.a1, … FROM tbl a JOIN tbl2 b

List: Attributes of Multiple Objects

slide-41
SLIDE 41

Database JDBC JPA Spring Data JPA Your Code

Collection<DTO> c = em.createQuery("SELECT " + "new DTO(a.a1, b.a1, …)" + " FROM Entity a JOIN a.child b") .getResultList(); SELECT a.a1, b.a1, … FROM tbl a JOIN tbl2 b

List: Attributes of Multiple Objects

<P> Collection<P> findAllProjectedBy(Class<P> p); Collection<Proj> c = r.findAllProjectedBy(Proj.class);

slide-42
SLIDE 42

Database JDBC JPA Spring Data JPA Your Code

Collection<DTO> c = em.createQuery("SELECT " + "new DTO(a.a1, b.a1, …)" + " FROM Entity a JOIN a.child b") .getResultList(); SELECT a.a1, b.a1, … FROM tbl a JOIN tbl2 b

List: Attributes of Multiple Objects

<P> Collection<P> findAllProjectedBy(Class<P> p); Collection<Proj> c = r.findAllProjectedBy(Proj.class); public interface Proj { … getA1(); Proj2 getB(); }

slide-43
SLIDE 43

Database JDBC JPA Spring Data JPA Your Code

Collection<DTO> c = em.createQuery("SELECT " + "new DTO(a.a1, b.a1, …)" + " FROM Entity a JOIN a.child b") .getResultList(); SELECT a.a1, b.a1, … FROM tbl a JOIN tbl2 b

List: Attributes of Multiple Objects

<P> Collection<P> findAllProjectedBy(Class<P> p); Collection<Proj> c = r.findAllProjectedBy(Proj.class); public interface Proj { … getA1(); Proj2 getB(); }

Still selects all columns DATAJPA-1218

slide-44
SLIDE 44

Database JDBC JPA Spring Data JPA Your Code

Collection<DTO> c = em.createQuery("SELECT " + "new DTO(a.a1, b.a1, …)" + " FROM Entity a JOIN a.child b") .getResultList(); SELECT a.a1, b.a1, … FROM tbl a JOIN tbl2 b

List: Attributes of Multiple Objects

@Query("SELECT " + "new DTO(a.a1, b.a1, …)" + " FROM Entity a JOIN a.child b") Collection<DTO> findAllDtoProj(); Collection<DTO> c = r.findAllDtoProj(); public interface Proj { … getA1(); Proj2 getB(); }

Still selects all columns DATAJPA-1218

slide-45
SLIDE 45

Database JDBC JPA Spring Data JPA Your Code

List: Derived Data

slide-46
SLIDE 46

Database JDBC JPA Spring Data JPA Your Code

SELECT a.a1, SUM(b.a1) FROM tbl a JOIN tbl2 b GROUP BY a.a1

List: Derived Data

slide-47
SLIDE 47

Database JDBC JPA Spring Data JPA Your Code

Collection<DTO> c = em.createQuery("SELECT " + "new DTO(a.a1, SUM(b.a1))" + " FROM Entity a JOIN a.child b" + " GROUP BY a" ) .getResultList(); SELECT a.a1, SUM(b.a1) FROM tbl a JOIN tbl2 b GROUP BY a.a1

List: Derived Data

slide-48
SLIDE 48

Database JDBC JPA Spring Data JPA Your Code

Collection<DTO> c = em.createQuery("SELECT " + "new DTO(a.a1, SUM(b.a1))" + " FROM Entity a JOIN a.child b" + " GROUP BY a" ) .getResultList(); SELECT a.a1, SUM(b.a1) FROM tbl a JOIN tbl2 b GROUP BY a.a1

List: Derived Data

@Query("SELECT " + "new DTO(a.a1, SUM(b.a1))" + " FROM Entity a JOIN a.child b" + " GROUP BY a") Collection<DTO> findAllDtoProj(); Collection<DTO> c = r.findAllDtoProj();

slide-49
SLIDE 49

Database JDBC JPA Spring Data JPA Your Code

SELECT a.a1, SUM(b.a1) , SUM(CASE WHEN b.a2=1 THEN b.a1 ELSE 0 END ) FROM tbl a JOIN tbl2 b GROUP BY a.a1

List: Derived Data

slide-50
SLIDE 50

Database JDBC JPA Spring Data JPA Your Code

SELECT a.a1, SUM(b.a1) , SUM(CASE WHEN b.a2=1 THEN b.a1 ELSE 0 END ) FROM tbl a JOIN tbl2 b GROUP BY a.a1

List: Derived Data

More beautifully, but rarely supported: SELECT a.a1, SUM(b.a1) , SUM(b.a1) FILTER (WHERE b.a2=1) FROM tbl a JOIN tbl2 b GROUP BY a.a1

slide-51
SLIDE 51

Database JDBC JPA Spring Data JPA Your Code

SELECT a.a1, SUM(b.a1) , SUM(CASE WHEN b.a2=1 THEN b.a1 ELSE 0 END ) FROM tbl a JOIN tbl2 b GROUP BY a.a1

List: Derived Data

More beautifully, but rarely supported: SELECT a.a1, SUM(b.a1) , SUM(b.a1) FILTER (WHERE b.a2=1) FROM tbl a JOIN tbl2 b GROUP BY a.a1

1999 2001 2003 2005 2007 2009 2011 2013 2015 2017 2019

5.1

MariaDB MySQL

9.4

PostgreSQL

1.0 3.30 SQLite

DB2 LUW Oracle SQL Server

slide-52
SLIDE 52

Database JDBC JPA Spring Data JPA Your Code

SELECT a.a1, SUM(b.a1) , SUM(CASE WHEN b.a2=1 THEN b.a1 ELSE 0 END ) FROM tbl a JOIN tbl2 b GROUP BY a.a1

List: Derived Data

JPQL doesn't allow CASE in aggregates

More beautifully, but rarely supported: SELECT a.a1, SUM(b.a1) , SUM(b.a1) FILTER (WHERE b.a2=1) FROM tbl a JOIN tbl2 b GROUP BY a.a1

1999 2001 2003 2005 2007 2009 2011 2013 2015 2017 2019

5.1

MariaDB MySQL

9.4

PostgreSQL

1.0 3.30 SQLite

DB2 LUW Oracle SQL Server

slide-53
SLIDE 53

Database JDBC JPA Spring Data JPA Your Code

SELECT a.a1, SUM(b.a1) , SUM(CASE WHEN b.a2=1 THEN b.a1 ELSE 0 END ) FROM tbl a JOIN tbl2 b GROUP BY a.a1

List: Derived Data

JPQL doesn't allow CASE in aggregates

slide-54
SLIDE 54

Database JDBC JPA Spring Data JPA Your Code

SELECT a.a1, SUM(b.a1) , SUM(CASE WHEN b.a2=1 THEN b.a1 ELSE 0 END ) FROM tbl a JOIN tbl2 b GROUP BY a.a1

List: Derived Data

@NamedNativeQuery( name = "Entity.findAllXy", query = "<<SQL>>", resultSetMapping = "…") @SqlResultSetMapping(…) public class Entity {…}

JPQL doesn't allow CASE in aggregates

slide-55
SLIDE 55

Database JDBC JPA Spring Data JPA Your Code

SELECT a.a1, SUM(b.a1) , SUM(CASE WHEN b.a2=1 THEN b.a1 ELSE 0 END ) FROM tbl a JOIN tbl2 b GROUP BY a.a1

List: Derived Data

@NamedNativeQuery( name = "Entity.findAllXy", query = "<<SQL>>", resultSetMapping = "…") @SqlResultSetMapping(…) public class Entity {…} Collection<DTO> c = em.createNamedQuery("Entity.findAllXy") .getResultList();

JPQL doesn't allow CASE in aggregates

slide-56
SLIDE 56

Database JDBC JPA Spring Data JPA Your Code

SELECT a.a1, SUM(b.a1) , SUM(CASE WHEN b.a2=1 THEN b.a1 ELSE 0 END ) FROM tbl a JOIN tbl2 b GROUP BY a.a1

List: Derived Data

@NamedNativeQuery( name = "Entity.findAllXy", query = "<<SQL>>", resultSetMapping = "…") @SqlResultSetMapping(…) public class Entity {…} Collection<DTO> c = em.createNamedQuery("Entity.findAllXy") .getResultList(); @Query(native=true) Collection<DTO> findAllXy();

JPQL doesn't allow CASE in aggregates

slide-57
SLIDE 57
slide-58
SLIDE 58
slide-59
SLIDE 59
slide-60
SLIDE 60
slide-61
SLIDE 61

Application

slide-62
SLIDE 62

Application

slide-63
SLIDE 63

Application

slide-64
SLIDE 64

Application

ORMs are super useful for the Load-Change-Store cycle on complex
 entity graphs.

slide-65
SLIDE 65

Application

ORMs are super useful for the Load-Change-Store cycle on complex
 entity graphs. If the cycle is broken,
 entities might not be the right level of abstraction.

slide-66
SLIDE 66

Pattern #2: Search

slide-67
SLIDE 67

Pattern #2: Search

slide-68
SLIDE 68

Database JDBC JPA Spring Data JPA Your Code

SELECT * FROM tbl a <<super complex query>>

Search: Load Entity from Native Query

slide-69
SLIDE 69

Database JDBC JPA Spring Data JPA Your Code

SELECT * FROM tbl a <<super complex query>>

Search: Load Entity from Native Query

@NamedNativeQuery( name = "Entity.findSpecialOnes", query = "<<SQL>>", resultSetMapping = "…") @SqlResultSetMapping( @entities = @EntityResult( entityClass = Entity.class)) public class Entity {…}

slide-70
SLIDE 70

Database JDBC JPA Spring Data JPA Your Code

SELECT * FROM tbl a <<super complex query>>

Search: Load Entity from Native Query

@NamedNativeQuery( name = "Entity.findSpecialOnes", query = "<<SQL>>", resultSetMapping = "…") @SqlResultSetMapping( @entities = @EntityResult( entityClass = Entity.class)) public class Entity {…} @Query(native=true) Collection<Entity> findSpecialOnes();

slide-71
SLIDE 71

CREATE TABLE t ( id INTEGER, parent INTEGER, )

slide-72
SLIDE 72

CREATE TABLE t ( id INTEGER, parent INTEGER, )

slide-73
SLIDE 73

CREATE TABLE t ( id INTEGER, parent INTEGER, )

slide-74
SLIDE 74
slide-75
SLIDE 75
slide-76
SLIDE 76

public void descendTree(Entity start) { for (Entity e : start.getChildren()) { descendTree(e); } }

slide-77
SLIDE 77

public void descendTree(Entity start) { for (Entity e : start.getChildren()) { descendTree(e); } }

slide-78
SLIDE 78

public void descendTree(Entity start) { for (Entity e : start.getChildren()) { descendTree(e); } } SELECT ... FROM Entity WHERE parent = ?

slide-79
SLIDE 79

public void descendTree(Entity start) { for (Entity e : start.getChildren()) { descendTree(e); } } SELECT ... FROM Entity WHERE parent = ?

slide-80
SLIDE 80

public void descendTree(Entity start) { for (Entity e : start.getChildren()) { descendTree(e); } } SELECT ... FROM Entity WHERE parent = ?

slide-81
SLIDE 81

public void descendTree(Entity start) { for (Entity e : start.getChildren()) { descendTree(e); } } SELECT ... FROM Entity WHERE parent = ? public void ascendTree(Entity e) { while (e != null) { e = e.getParent(); } }

slide-82
SLIDE 82

public void descendTree(Entity start) { for (Entity e : start.getChildren()) { descendTree(e); } } SELECT ... FROM Entity WHERE parent = ? public void ascendTree(Entity e) { while (e != null) { e = e.getParent(); } } SELECT ... FROM Entity WHERE id = ?

slide-83
SLIDE 83
slide-84
SLIDE 84

SELECT t.id, t.parent FROM t WHERE t.id = ?

slide-85
SLIDE 85

SELECT t.id, t.parent FROM t WHERE t.id = ? UNION ALL SELECT t.id, t.parent FROM t WHERE t.parent = ?

slide-86
SLIDE 86

SELECT t.id, t.parent FROM t WHERE t.id = ? UNION ALL SELECT t.id, t.parent FROM t WHERE t.parent = ?

slide-87
SLIDE 87

SELECT t.id, t.parent FROM t WHERE t.id = ? UNION ALL SELECT t.id, t.parent FROM t WHERE t.parent = ?

slide-88
SLIDE 88

WITH RECURSIVE prev (id, parent) AS ( ) SELECT t.id, t.parent FROM t WHERE t.id = ? UNION ALL SELECT t.id, t.parent FROM t JOIN prev ON t.parent = prev.id SELECT * FROM prev

slide-89
SLIDE 89

WITH RECURSIVE prev (id, parent) AS ( ) SELECT t.id, t.parent FROM t WHERE t.id = ? UNION ALL SELECT t.id, t.parent FROM t JOIN prev ON t.parent = prev.id SELECT * FROM prev

slide-90
SLIDE 90

WITH RECURSIVE prev (id, parent) AS ( ) SELECT t.id, t.parent FROM t WHERE t.id = ? UNION ALL SELECT t.id, t.parent FROM t JOIN prev ON prev.parent = t.id SELECT * FROM prev

slide-91
SLIDE 91
slide-92
SLIDE 92

Vendor Dialect

slide-93
SLIDE 93

Vendor Dialect Standard SQL (ISO 9075)

slide-94
SLIDE 94

Vendor Dialect Standard SQL (ISO 9075) HQL

slide-95
SLIDE 95

Vendor Dialect Standard SQL (ISO 9075) HQL JPQL

slide-96
SLIDE 96

Vendor Dialect Standard SQL (ISO 9075) HQL JPQL Spring .findBy…

slide-97
SLIDE 97

Vendor Dialect Standard SQL (ISO 9075) HQL JPQL Spring .findBy…

1 9 9 9 2 1 2 3 2 5 2 7 2 9 2 1 1 2 1 3 2 1 5 2 1 7 2 1 9

5.1 10.2

MariaDB

8.0

MySQL

8.4

PostgreSQL

1.0 3.8.3

SQLite

7.0

DB2 LUW

11gR2

Oracle

2005

SQL Server

* without keyword recursive

with recursive

* * *

slide-98
SLIDE 98

JVM

slide-99
SLIDE 99

JVM

slide-100
SLIDE 100

JVM

public void ascendTree(Entity e) { while (e != null) { e = e.getParent(); } }

slide-101
SLIDE 101

JVM

public void ascendTree(Entity e) { while (e != null) { e = e.getParent(); } }

slide-102
SLIDE 102

JVM

public void ascendTree(Entity e) { while (e != null) { e = e.getParent(); } }

slide-103
SLIDE 103

JVM

public void ascendTree(Entity e) { while (e != null) { e = e.getParent(); } }

PersistenceContext

slide-104
SLIDE 104

JVM

public void ascendTree(Entity e) { while (e != null) { e = e.getParent(); } }

PersistenceContext

slide-105
SLIDE 105

JVM

public void ascendTree(Entity e) { while (e != null) { e = e.getParent(); } }

PersistenceContext

slide-106
SLIDE 106

JVM

public void ascendTree(Entity e) { while (e != null) { e = e.getParent(); } }

PersistenceContext

slide-107
SLIDE 107
slide-108
SLIDE 108

What if I run the recursive query

slide-109
SLIDE 109

What if I run the recursive query

slide-110
SLIDE 110

What if I run the recursive query before my JPA logic?

slide-111
SLIDE 111

JVM PersistenceContext

slide-112
SLIDE 112

JVM PersistenceContext

@NamedNativeQuery( name = "Entity.findSpecialOnes", query = "<<SQL>>", resultSetMapping = "…") @SqlResultSetMapping( @entities = @EntityResult( entityClass = Entity.class)) public class Entity {…}

slide-113
SLIDE 113

JVM PersistenceContext

findSpecialOnes(); // Warm Up

slide-114
SLIDE 114

JVM PersistenceContext

findSpecialOnes(); // Warm Up

slide-115
SLIDE 115

JVM PersistenceContext

findSpecialOnes(); // Warm Up

slide-116
SLIDE 116

JVM

public void ascendTree(Entity e) { while (e != null) { e = e.getParent(); } }

PersistenceContext

findSpecialOnes(); // Warm Up

slide-117
SLIDE 117
slide-118
SLIDE 118
slide-119
SLIDE 119

A lot has happened since SQL-92

slide-120
SLIDE 120

SQL has evolved beyond the relational idea A lot has happened since SQL-92

slide-121
SLIDE 121

SQL has evolved beyond the relational idea If you use SQL for CRUD operations only, you are doing it wrong A lot has happened since SQL-92

slide-122
SLIDE 122

SQL has evolved beyond the relational idea If you use SQL for CRUD operations only, you are doing it wrong A lot has happened since SQL-92

https://modern-sql.com

@ModernSQL by @MarkusWinand