java & sql stronger together · database jdbc jpa spring data jpa your code provides unique api...
TRANSCRIPT
![Page 1: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/1.jpg)
Java & SQL Stronger Together
@MarkusWinand @ModernSQL
Picture: Africa Studio@Shutterstock
![Page 2: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/2.jpg)
![Page 3: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/3.jpg)
“We can solve any problem by introducing an extra
level of abstraction” (Based on the “fundamental theorem of software engineering”)
![Page 4: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/4.jpg)
![Page 5: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/5.jpg)
Database
![Page 6: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/6.jpg)
DatabaseJDBC
Provides unique API for different SQL-
Databases
![Page 7: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/7.jpg)
DatabaseJDBCJPA
Provides unique API for different SQL-
Databases
Maps objects from/to tables
![Page 8: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/8.jpg)
DatabaseJDBCJPA
Spring Data JPA
Provides unique API for different SQL-
Databases
Maps objects from/to tables
Makes common tasks very simple
![Page 9: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/9.jpg)
DatabaseJDBCJPA
Spring Data JPAYour Code
Provides unique API for different SQL-
Databases
Maps objects from/to tables
Makes common tasks very simple
![Page 10: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/10.jpg)
DatabaseJDBCJPA
Spring Data JPAYour 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
![Page 11: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/11.jpg)
DatabaseJDBCJPA
Spring Data JPAYour Code
Provides unique API for different SQL-
Databases
Maps Objects from/to tables
Makes common tasks very simple
90%?Proprietary data types? —> (VendorCls)jdbcCls
![Page 12: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/12.jpg)
DatabaseJDBCJPA
Spring Data JPAYour 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
or native queries
![Page 13: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/13.jpg)
DatabaseJDBCJPA
Spring Data JPAYour 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
or 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 SQLfrom you[…]”
— Hibernate ORM User Guide (second paragraph)
![Page 14: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/14.jpg)
DatabaseJDBCJPA
Spring Data JPAYour 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
or native queries
![Page 15: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/15.jpg)
DatabaseJDBCJPA
Spring Data JPAYour 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
or native queries
findBy… not powerful enough? —> QueryBuilder, JPQL
or native queries
![Page 16: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/16.jpg)
What is theright level of abstraction
for any given problem?
![Page 17: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/17.jpg)
Pattern #1: Lists
1.2.3.
Go for a walk in the woods
Picnic
Improve SQL-foo
BUCKET LISTSpring
![Page 18: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/18.jpg)
DatabaseJDBCJPA
Spring Data JPAYour Code
List: All Attributes
![Page 19: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/19.jpg)
DatabaseJDBCJPA
Spring Data JPAYour Code
SELECT*FROMtbl
List: All Attributes
![Page 20: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/20.jpg)
DatabaseJDBCJPA
Spring Data JPAYour Code
ResultSetrs=c.prepareStatement("SELECT*"+"FROMtbl").executeQuery();
SELECT*FROMtbl
List: All Attributes
![Page 21: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/21.jpg)
DatabaseJDBCJPA
Spring Data JPAYour Code
Collection<Entity>c=em.createQuery("FROMEntity").getResultList();
ResultSetrs=c.prepareStatement("SELECT*"+"FROMtbl").executeQuery();
SELECT*FROMtbl
List: All Attributes
![Page 22: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/22.jpg)
DatabaseJDBCJPA
Spring Data JPAYour Code
Collection<Entity>c=em.createQuery("FROMEntity").getResultList();
Collection<Entity>c=r.findAll();
ResultSetrs=c.prepareStatement("SELECT*"+"FROMtbl").executeQuery();
SELECT*FROMtbl
List: All Attributes
![Page 23: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/23.jpg)
DatabaseJDBCJPA
Spring Data JPAYour Code
List: Some Attributes
![Page 24: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/24.jpg)
DatabaseJDBCJPA
Spring Data JPAYour Code
SELECTa1,…FROMtbl
List: Some Attributes
![Page 25: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/25.jpg)
DatabaseJDBCJPA
Spring Data JPAYour Code
SELECTa1,…FROMtbl
List: Some Attributes
Runtime improvement: ~0% - 10 000%
![Page 26: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/26.jpg)
DatabaseJDBCJPA
Spring Data JPAYour Code
SELECTa1,…FROMtbl
List: Some Attributes
Runtime improvement: ~0% - 10 000%
Affects:
![Page 27: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/27.jpg)
DatabaseJDBCJPA
Spring Data JPAYour Code
SELECTa1,…FROMtbl
List: Some Attributes
Runtime improvement: ~0% - 10 000%
Affects:- Java: GC
![Page 28: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/28.jpg)
DatabaseJDBCJPA
Spring Data JPAYour Code
SELECTa1,…FROMtbl
List: Some Attributes
Runtime improvement: ~0% - 10 000%
Affects:- Java: GC- Network
![Page 29: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/29.jpg)
DatabaseJDBCJPA
Spring Data JPAYour Code
SELECTa1,…FROMtbl
List: Some Attributes
Runtime improvement: ~0% - 10 000%
Affects:- Java: GC- Network- Database:
![Page 30: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/30.jpg)
DatabaseJDBCJPA
Spring Data JPAYour Code
SELECTa1,…FROMtbl
List: Some Attributes
Runtime improvement: ~0% - 10 000%
Affects:- Java: GC- Network- Database:
- Index Only Scan
![Page 31: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/31.jpg)
DatabaseJDBCJPA
Spring Data JPAYour Code
SELECTa1,…FROMtbl
List: Some Attributes
Runtime improvement: ~0% - 10 000%
Affects: - Java: GC - Network - Database:
- Index Only Scan - Mem + CPU (e.g. sorting)
![Page 32: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/32.jpg)
DatabaseJDBCJPA
Spring Data JPAYour Code
ResultSetrs=c.prepareStatement("SELECTa1,…"+"FROMtbl").executeQuery();
SELECTa1,…FROMtbl
List: Some Attributes
![Page 33: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/33.jpg)
DatabaseJDBCJPA
Spring Data JPAYour Code
Collection<DTO>c=em.createQuery("SELECTnewDTO(a1,…)"+"FROMEnt").getResultList();
ResultSetrs=c.prepareStatement("SELECTa1,…"+"FROMtbl").executeQuery();
SELECTa1,…FROMtbl
List: Some Attributes
![Page 34: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/34.jpg)
DatabaseJDBCJPA
Spring Data JPAYour Code
Collection<DTO>c=em.createQuery("SELECTnewDTO(a1,…)"+"FROMEnt").getResultList();
ResultSetrs=c.prepareStatement("SELECTa1,…"+"FROMtbl").executeQuery();
SELECTa1,…FROMtbl
List: Some Attributes
Or: LAZY attributes
![Page 35: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/35.jpg)
DatabaseJDBCJPA
Spring Data JPAYour Code
Collection<DTO>c=em.createQuery("SELECTnewDTO(a1,…)"+"FROMEnt").getResultList();
ResultSetrs=c.prepareStatement("SELECTa1,…"+"FROMtbl").executeQuery();
SELECTa1,…FROMtbl
List: Some Attributes
Or: LAZY attributes
<P>Collection<P>findAllProjectedBy(Class<P>p);
![Page 36: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/36.jpg)
DatabaseJDBCJPA
Spring Data JPAYour Code
Collection<DTO>c=em.createQuery("SELECTnewDTO(a1,…)"+"FROMEnt").getResultList();
ResultSetrs=c.prepareStatement("SELECTa1,…"+"FROMtbl").executeQuery();
SELECTa1,…FROMtbl
List: Some Attributes
Or: LAZY attributes
<P>Collection<P>findAllProjectedBy(Class<P>p);
publicinterfaceProj{…getA1();…}
![Page 37: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/37.jpg)
DatabaseJDBCJPA
Spring Data JPAYour Code
Collection<DTO>c=em.createQuery("SELECTnewDTO(a1,…)"+"FROMEnt").getResultList();
ResultSetrs=c.prepareStatement("SELECTa1,…"+"FROMtbl").executeQuery();
SELECTa1,…FROMtbl
List: Some Attributes
Or: LAZY attributes
<P>Collection<P>findAllProjectedBy(Class<P>p);Collection<Proj>c=r.findAllProjectedBy(Proj.class);
publicinterfaceProj{…getA1();…}
![Page 38: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/38.jpg)
DatabaseJDBCJPA
Spring Data JPAYour Code
List: Attributes of Multiple Objects
![Page 39: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/39.jpg)
DatabaseJDBCJPA
Spring Data JPAYour Code
SELECTa.a1,b.a1,…FROMtblaJOINtbl2bON…
List: Attributes of Multiple Objects
![Page 40: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/40.jpg)
DatabaseJDBCJPA
Spring Data JPAYour Code
Collection<DTO>c=em.createQuery("SELECT"+"newDTO(a.a1,b.a1,…)"+"FROMEntityaJOINa.childb").getResultList();
SELECTa.a1,b.a1,…FROMtblaJOINtbl2bON…
List: Attributes of Multiple Objects
![Page 41: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/41.jpg)
DatabaseJDBCJPA
Spring Data JPAYour Code
Collection<DTO>c=em.createQuery("SELECT"+"newDTO(a.a1,b.a1,…)"+"FROMEntityaJOINa.childb").getResultList();
SELECTa.a1,b.a1,…FROMtblaJOINtbl2bON…
List: Attributes of Multiple Objects
<P>Collection<P>findAllProjectedBy(Class<P>p);Collection<Proj>c=r.findAllProjectedBy(Proj.class);
![Page 42: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/42.jpg)
DatabaseJDBCJPA
Spring Data JPAYour Code
Collection<DTO>c=em.createQuery("SELECT"+"newDTO(a.a1,b.a1,…)"+"FROMEntityaJOINa.childb").getResultList();
SELECTa.a1,b.a1,…FROMtblaJOINtbl2bON…
List: Attributes of Multiple Objects
<P>Collection<P>findAllProjectedBy(Class<P>p);Collection<Proj>c=r.findAllProjectedBy(Proj.class);
publicinterfaceProj{…getA1();Proj2getB();}
![Page 43: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/43.jpg)
DatabaseJDBCJPA
Spring Data JPAYour Code
Collection<DTO>c=em.createQuery("SELECT"+"newDTO(a.a1,b.a1,…)"+"FROMEntityaJOINa.childb").getResultList();
SELECTa.a1,b.a1,…FROMtblaJOINtbl2bON…
List: Attributes of Multiple Objects
<P>Collection<P>findAllProjectedBy(Class<P>p);Collection<Proj>c=r.findAllProjectedBy(Proj.class);
publicinterfaceProj{…getA1();Proj2getB();}
Still selects all columns
DATAJPA-1218
![Page 44: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/44.jpg)
DatabaseJDBCJPA
Spring Data JPAYour Code
Collection<DTO>c=em.createQuery("SELECT"+"newDTO(a.a1,b.a1,…)"+"FROMEntityaJOINa.childb").getResultList();
SELECTa.a1,b.a1,…FROMtblaJOINtbl2bON…
List: Attributes of Multiple Objects
@Query("SELECT"+"newDTO(a.a1,b.a1,…)"+"FROMEntityaJOINa.childb")Collection<DTO>findAllDtoProj();Collection<DTO>c=r.findAllDtoProj();
publicinterfaceProj{…getA1();Proj2getB();}
Still selects all columns
DATAJPA-1218
![Page 45: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/45.jpg)
DatabaseJDBCJPA
Spring Data JPAYour Code
Collection<DTO>c=em.createQuery("SELECT"+"newDTO(a.a1,b.a1,…)"+"FROMEntityaJOINa.childb").getResultList();
SELECTa.a1,b.a1,…FROMtblaJOINtbl2bON…
List: Attributes of Multiple Objects
@Query("SELECT"+"newDTO(a.a1,b.a1,…)"+"FROMEntityaJOINa.childb")Collection<DTO>findAllDtoProj();Collection<DTO>c=r.findAllDtoProj();
publicinterfaceProj{…getA1();Proj2getB();}
Still selects all columns
DATAJPA-1218
Consider Blaze Persistence
Entity Views
![Page 46: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/46.jpg)
DatabaseJDBCJPA
Spring Data JPAYour Code
List: Derived Data
![Page 47: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/47.jpg)
DatabaseJDBCJPA
Spring Data JPAYour Code
SELECTa.a1,SUM(b.a1)FROMtblaJOINtbl2bGROUPBYa.a1
List: Derived Data
![Page 48: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/48.jpg)
DatabaseJDBCJPA
Spring Data JPAYour Code Collection<DTO>c=
em.createQuery("SELECT"+"newDTO(a.a1,SUM(b.a1))"+"FROMEntityaJOINa.childb"+"GROUPBYa").getResultList();
SELECTa.a1,SUM(b.a1)FROMtblaJOINtbl2bGROUPBYa.a1
List: Derived Data
![Page 49: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/49.jpg)
DatabaseJDBCJPA
Spring Data JPAYour Code Collection<DTO>c=
em.createQuery("SELECT"+"newDTO(a.a1,SUM(b.a1))"+"FROMEntityaJOINa.childb"+"GROUPBYa").getResultList();
SELECTa.a1,SUM(b.a1)FROMtblaJOINtbl2bGROUPBYa.a1
List: Derived Data@Query("SELECT"+"newDTO(a.a1,SUM(b.a1))"+"FROMEntityaJOINa.childb"+"GROUPBYa")Collection<DTO>findAllDtoProj();Collection<DTO>c=r.findAllDtoProj();
![Page 50: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/50.jpg)
DatabaseJDBCJPA
Spring Data JPAYour Code
SELECTa.a1,SUM(b.a1),SUM(CASEWHENb.a2=1THENb.a1ELSE0END)FROMtblaJOINtbl2bGROUPBYa.a1
List: Derived Data
![Page 51: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/51.jpg)
DatabaseJDBCJPA
Spring Data JPAYour Code
SELECTa.a1,SUM(b.a1),SUM(CASEWHENb.a2=1THENb.a1ELSE0END)FROMtblaJOINtbl2bGROUPBYa.a1
List: Derived Data
More beautifully, but rarely supported: SELECTa.a1,SUM(b.a1),SUM(b.a1)FILTER(WHEREb.a2=1)FROMtblaJOINtbl2bGROUPBYa.a1
![Page 52: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/52.jpg)
DatabaseJDBCJPA
Spring Data JPAYour Code
SELECTa.a1,SUM(b.a1),SUM(CASEWHENb.a2=1THENb.a1ELSE0END)FROMtblaJOINtbl2bGROUPBYa.a1
List: Derived Data
More beautifully, but rarely supported: SELECTa.a1,SUM(b.a1),SUM(b.a1)FILTER(WHEREb.a2=1)FROMtblaJOINtbl2bGROUPBYa.a1
1999
2001
2003
2005
2007
2009
2011
2013
2015
2017
2019
5.1 MariaDBMySQL
9.4 PostgreSQL1.0 3.30 SQLite
DB2 LUWOracleSQL Server
![Page 53: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/53.jpg)
DatabaseJDBCJPA
Spring Data JPAYour Code
SELECTa.a1,SUM(b.a1),SUM(CASEWHENb.a2=1THENb.a1ELSE0END)FROMtblaJOINtbl2bGROUPBYa.a1
List: Derived Data
JPQL doesn't allow CASE in aggregates
More beautifully, but rarely supported: SELECTa.a1,SUM(b.a1),SUM(b.a1)FILTER(WHEREb.a2=1)FROMtblaJOINtbl2bGROUPBYa.a1
1999
2001
2003
2005
2007
2009
2011
2013
2015
2017
2019
5.1 MariaDBMySQL
9.4 PostgreSQL1.0 3.30 SQLite
DB2 LUWOracleSQL Server
![Page 54: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/54.jpg)
DatabaseJDBCJPA
Spring Data JPAYour Code
SELECTa.a1,SUM(b.a1),SUM(CASEWHENb.a2=1THENb.a1ELSE0END)FROMtblaJOINtbl2bGROUPBYa.a1
List: Derived Data
JPQL doesn't allow CASE in aggregates
![Page 55: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/55.jpg)
DatabaseJDBCJPA
Spring Data JPAYour Code
SELECTa.a1,SUM(b.a1),SUM(CASEWHENb.a2=1THENb.a1ELSE0END)FROMtblaJOINtbl2bGROUPBYa.a1
List: Derived Data@NamedNativeQuery(name="Entity.findAllXy",query="<<SQL>>",resultSetMapping="…")@SqlResultSetMapping(…)publicclassEntity{…}
JPQL doesn't allow CASE in aggregates
![Page 56: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/56.jpg)
DatabaseJDBCJPA
Spring Data JPAYour Code
SELECTa.a1,SUM(b.a1),SUM(CASEWHENb.a2=1THENb.a1ELSE0END)FROMtblaJOINtbl2bGROUPBYa.a1
List: Derived Data@NamedNativeQuery(name="Entity.findAllXy",query="<<SQL>>",resultSetMapping="…")@SqlResultSetMapping(…)publicclassEntity{…}
Collection<DTO>c=em.createNamedQuery("Entity.findAllXy").getResultList();
JPQL doesn't allow CASE in aggregates
![Page 57: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/57.jpg)
DatabaseJDBCJPA
Spring Data JPAYour Code
SELECTa.a1,SUM(b.a1),SUM(CASEWHENb.a2=1THENb.a1ELSE0END)FROMtblaJOINtbl2bGROUPBYa.a1
List: Derived Data@NamedNativeQuery(name="Entity.findAllXy",query="<<SQL>>",resultSetMapping="…")@SqlResultSetMapping(…)publicclassEntity{…}
Collection<DTO>c=em.createNamedQuery("Entity.findAllXy").getResultList();
@Query(native=true)Collection<DTO>findAllXy();
JPQL doesn't allow CASE in aggregates
![Page 58: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/58.jpg)
DatabaseJDBCJPA
Spring Data JPAYour Code
SELECTa.a1,SUM(b.a1),SUM(CASEWHENb.a2=1THENb.a1ELSE0END)FROMtblaJOINtbl2bGROUPBYa.a1
List: Derived Data
JPQL doesn't allow CASE in aggregates
![Page 59: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/59.jpg)
DatabaseJDBCJPA
Spring Data JPAYour Code
SELECTa.a1,SUM(b.a1),SUM(CASEWHENb.a2=1THENb.a1ELSE0END)FROMtblaJOINtbl2bGROUPBYa.a1
List: Derived Data
JPQL doesn't allow CASE in aggregates
Blaze Persistence Entity Views &
JPQL.next
@EntityView(Entity.class)interfaceProj{IntegergetA1();
@Mapping("SUM(b.a1)")IntegergetSum1();
@Mapping("SUM(b.a1)FILTER(WHEREa2=1)")IntegergetSum2();}
![Page 60: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/60.jpg)
![Page 61: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/61.jpg)
![Page 62: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/62.jpg)
![Page 63: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/63.jpg)
Application
![Page 64: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/64.jpg)
Application
![Page 65: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/65.jpg)
Application
![Page 66: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/66.jpg)
Application ORMs are super useful for the Load-Change-Store
cycle on complexentity graphs.
![Page 67: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/67.jpg)
Application ORMs are super useful for the Load-Change-Store
cycle on complexentity graphs.
If the cycle is broken,entities might not be the right level of abstraction.
![Page 68: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/68.jpg)
Pattern #2: Search
![Page 69: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/69.jpg)
Pattern #2: Search
![Page 70: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/70.jpg)
DatabaseJDBCJPA
Spring Data JPAYour Code
SELECT*FROMtbla<<supercomplexquery>>
Search: Load Entity from Native Query
![Page 71: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/71.jpg)
DatabaseJDBCJPA
Spring Data JPAYour Code
SELECT*FROMtbla<<supercomplexquery>>
Search: Load Entity from Native Query
@NamedNativeQuery(name="Entity.findSpecialOnes",query="<<SQL>>",resultSetMapping="…")@SqlResultSetMapping(@entities=@EntityResult(entityClass=Entity.class))publicclassEntity{…}
![Page 72: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/72.jpg)
DatabaseJDBCJPA
Spring Data JPAYour Code
SELECT*FROMtbla<<supercomplexquery>>
Search: Load Entity from Native Query
@NamedNativeQuery(name="Entity.findSpecialOnes",query="<<SQL>>",resultSetMapping="…")@SqlResultSetMapping(@entities=@EntityResult(entityClass=Entity.class))publicclassEntity{…}
@Query(native=true)Collection<Entity>findSpecialOnes();
![Page 73: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/73.jpg)
CREATETABLEt(idINTEGER,parentINTEGER,)
![Page 74: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/74.jpg)
CREATETABLEt(idINTEGER,parentINTEGER,)
![Page 75: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/75.jpg)
CREATETABLEt(idINTEGER,parentINTEGER,)
![Page 76: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/76.jpg)
CREATETABLEt(idINTEGER,parentINTEGER,)
![Page 77: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/77.jpg)
![Page 78: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/78.jpg)
publicvoiddescendTree(Entitystart){for(Entitye:start.getChildren()){descendTree(e);}}
![Page 79: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/79.jpg)
publicvoiddescendTree(Entitystart){for(Entitye:start.getChildren()){descendTree(e);}}
![Page 80: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/80.jpg)
publicvoiddescendTree(Entitystart){for(Entitye:start.getChildren()){descendTree(e);}}
SELECT...FROMEntityWHEREparent=?
![Page 81: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/81.jpg)
publicvoiddescendTree(Entitystart){for(Entitye:start.getChildren()){descendTree(e);}}
SELECT...FROMEntityWHEREparent=?
![Page 82: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/82.jpg)
publicvoiddescendTree(Entitystart){for(Entitye:start.getChildren()){descendTree(e);}}
SELECT...FROMEntityWHEREparent=?
![Page 83: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/83.jpg)
publicvoiddescendTree(Entitystart){for(Entitye:start.getChildren()){descendTree(e);}}
SELECT...FROMEntityWHEREparent=?
publicvoidascendTree(Entitye){while(e!=null){e=e.getParent();}}
![Page 84: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/84.jpg)
publicvoiddescendTree(Entitystart){for(Entitye:start.getChildren()){descendTree(e);}}
SELECT...FROMEntityWHEREparent=?
publicvoidascendTree(Entitye){while(e!=null){e=e.getParent();}}
SELECT...FROMEntityWHEREid=?
![Page 85: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/85.jpg)
![Page 86: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/86.jpg)
SELECTt.id,t.parentFROMtWHEREt.id=?
![Page 87: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/87.jpg)
SELECTt.id,t.parentFROMtWHEREt.id=?UNIONALLSELECTt.id,t.parentFROMtWHEREt.parent=?
![Page 88: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/88.jpg)
SELECTt.id,t.parentFROMtWHEREt.id=?UNIONALLSELECTt.id,t.parentFROMtWHEREt.parent=?
![Page 89: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/89.jpg)
SELECTt.id,t.parentFROMtWHEREt.id=?UNIONALLSELECTt.id,t.parentFROMtWHEREt.parent=?
![Page 90: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/90.jpg)
WITHRECURSIVEprev(id,parent)AS(
)
SELECTt.id,t.parentFROMtWHEREt.id=?UNIONALLSELECTt.id,t.parentFROMtJOINprevONt.parent=prev.id
SELECT*FROMprev
![Page 91: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/91.jpg)
WITHRECURSIVEprev(id,parent)AS(
)
SELECTt.id,t.parentFROMtWHEREt.id=?UNIONALLSELECTt.id,t.parentFROMtJOINprevONt.parent=prev.id
SELECT*FROMprev
![Page 92: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/92.jpg)
WITHRECURSIVEprev(id,parent)AS(
)
SELECTt.id,t.parentFROMtWHEREt.id=?UNIONALLSELECTt.id,t.parentFROMtJOINprevONprev.parent=t.id
SELECT*FROMprev
![Page 93: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/93.jpg)
![Page 94: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/94.jpg)
Vendor Dialect
![Page 95: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/95.jpg)
Vendor DialectStandard SQL (ISO 9075)
![Page 96: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/96.jpg)
Vendor DialectStandard SQL (ISO 9075)
HQL
![Page 97: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/97.jpg)
Vendor DialectStandard SQL (ISO 9075)
HQLJPQL
![Page 98: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/98.jpg)
Vendor DialectStandard SQL (ISO 9075)
HQLJPQL
Spring .findBy…
![Page 99: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/99.jpg)
Vendor DialectStandard SQL (ISO 9075)
HQLJPQL
Spring .findBy…
1999
2001
2003
2005
2007
2009
2011
2013
2015
2017
2019
5.1 10.2 MariaDB8.0 MySQL
8.4 PostgreSQL1.0 3.8.3 SQLite
7.0 DB2 LUW11gR2 Oracle
2005 SQL Server
* without keyword recursive
withrecursive
**
*
![Page 100: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/100.jpg)
JVM
![Page 101: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/101.jpg)
JVM
![Page 102: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/102.jpg)
JVM
publicvoidascendTree(Entitye){while(e!=null){e=e.getParent();}}
![Page 103: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/103.jpg)
JVM
publicvoidascendTree(Entitye){while(e!=null){e=e.getParent();}}
![Page 104: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/104.jpg)
JVM
publicvoidascendTree(Entitye){while(e!=null){e=e.getParent();}}
![Page 105: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/105.jpg)
JVM
publicvoidascendTree(Entitye){while(e!=null){e=e.getParent();}}
PersistenceContext
![Page 106: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/106.jpg)
JVM
publicvoidascendTree(Entitye){while(e!=null){e=e.getParent();}}
PersistenceContext
![Page 107: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/107.jpg)
JVM
publicvoidascendTree(Entitye){while(e!=null){e=e.getParent();}}
PersistenceContext
![Page 108: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/108.jpg)
JVM
publicvoidascendTree(Entitye){while(e!=null){e=e.getParent();}}
PersistenceContext
![Page 109: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/109.jpg)
![Page 110: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/110.jpg)
What if I run the recursive query
![Page 111: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/111.jpg)
What if I run the recursive query
![Page 112: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/112.jpg)
What if I run the recursive query
before my JPA logic?
![Page 113: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/113.jpg)
JVM
PersistenceContext
![Page 114: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/114.jpg)
JVM
PersistenceContext
@NamedNativeQuery(name="Entity.findSpecialOnes",query="<<SQL>>",resultSetMapping="…")@SqlResultSetMapping(@entities=@EntityResult(entityClass=Entity.class))publicclassEntity{…}
![Page 115: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/115.jpg)
JVM
PersistenceContext
findSpecialOnes();//WarmUp
![Page 116: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/116.jpg)
JVM
PersistenceContext
findSpecialOnes();//WarmUp
![Page 117: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/117.jpg)
JVM
PersistenceContext
findSpecialOnes();//WarmUp
![Page 118: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/118.jpg)
JVM
publicvoidascendTree(Entitye){while(e!=null){e=e.getParent();}}
PersistenceContext
findSpecialOnes();//WarmUp
![Page 119: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/119.jpg)
![Page 120: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/120.jpg)
JVM
PersistenceContext
![Page 121: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/121.jpg)
JVM
PersistenceContext
@NamedNativeQuery(name="Entity.findSpecialOnes",query="<<SQL>>",resultSetMapping="…")@SqlResultSetMapping(@entities=@EntityResult(entityClass=Entity.class))publicclassEntity{…}
![Page 122: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/122.jpg)
JVM
PersistenceContext
findSpecialOnes();//WarmUp
![Page 123: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/123.jpg)
JVM
PersistenceContext
findSpecialOnes();//WarmUp
![Page 124: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/124.jpg)
JVM
PersistenceContext
findSpecialOnes();//WarmUp
![Page 125: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/125.jpg)
JVM
PersistenceContext
findSpecialOnes();//WarmUp
publicvoiddescendTree(Entitys){for(Entitye:s.getChildren()){descendTree(e);}}
![Page 126: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/126.jpg)
JVM
PersistenceContext
findSpecialOnes();//WarmUp
publicvoiddescendTree(Entitys){for(Entitye:s.getChildren()){descendTree(e);}}
SELECT...FROMEntityWHEREparent=?
![Page 127: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/127.jpg)
JVM
PersistenceContext
findSpecialOnes();//WarmUp
publicvoiddescendTree(Entitys){for(Entitye:s.getChildren()){descendTree(e);}}
SELECT...FROMEntityWHEREparent=?
PersistenceContext cannot be searched
by PARENT *
* Possible Solution: EclipseLink @CacheIndex
![Page 128: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/128.jpg)
JVM
PersistenceContext
findSpecialOnes();//WarmUp
publicvoiddescendTree(Entitys){for(Entitye:s.getChildren()){descendTree(e);}}
SELECT...FROMEntityWHEREparent=?
PersistenceContext cannot be searched
by PARENT *
* Possible Solution: EclipseLink @CacheIndex
![Page 129: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/129.jpg)
Pre-Load via Modern SQL
1.
2.
Ineffective if Java code doesn’t access via @Id. (EclipseLink @CacheIndex?)
@NativeQueries flush the PersistenceContext
Limitations
1.2.3.
BlazePersistence
jOOQ.org
(myBatis, QueryDSL)
Helpful Tools
![Page 130: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/130.jpg)
![Page 131: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/131.jpg)
A lot has happened
since SQL-92
![Page 132: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/132.jpg)
SQL has evolved
beyond the relational idea
A lot has happened
since SQL-92
![Page 133: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/133.jpg)
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
![Page 134: Java & SQL Stronger Together · 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](https://reader031.vdocuments.us/reader031/viewer/2022013003/5f7feeb01b08f74a673d0ffe/html5/thumbnails/134.jpg)
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