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

List: Attributes of Multiple Objects Still selects public interface Proj { all columns … getA1(); DATAJPA-1218 Proj2 getB(); <P> Collection<P> } findAllProjectedBy(Class<P> p); Collection< Proj > c = Your Code r.findAll ProjectedBy ( Proj.class ); Spring Data JPA Collection<DTO> c = em.createQuery("SELECT " JPA + "new DTO( a. a1, b.a1, …)" + " FROM Entity a JOIN a.child b ") JDBC .getResultList(); Database SELECT a. a1, b.a1, … FROM tbl a JOIN tbl2 b

List: Attributes of Multiple Objects Still selects public interface Proj { all columns … getA1(); DATAJPA-1218 @Query("SELECT " Proj2 getB(); + "new DTO(a.a1, b.a1, …)" } + " FROM Entity a JOIN a.child b") Collection<DTO> findAllDtoProj(); Your Code Collection< DTO > c = r.findAll DtoProj (); Spring Data JPA Collection<DTO> c = em.createQuery("SELECT " JPA + "new DTO( a. a1, b.a1, …)" + " FROM Entity a JOIN a.child b ") JDBC .getResultList(); Database SELECT a. a1, b.a1, … FROM tbl a JOIN tbl2 b

List: Derived Data Your Code Spring Data JPA JPA JDBC Database

List: Derived Data Your Code Spring Data JPA JPA JDBC Database SELECT a.a1, SUM(b.a1) FROM tbl a JOIN tbl2 b GROUP BY a.a1

List: Derived Data Your Code Collection<DTO> c = Spring Data JPA em.createQuery("SELECT " + "new DTO(a.a1, SUM(b.a1) )" JPA + " FROM Entity a JOIN a.child b" + " GROUP BY a" ) JDBC .getResultList(); Database 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(); Your Code Collection<DTO> c = Spring Data JPA em.createQuery("SELECT " + "new DTO(a.a1, SUM(b.a1) )" JPA + " FROM Entity a JOIN a.child b" + " GROUP BY a" ) JDBC .getResultList(); Database SELECT a.a1, SUM(b.a1) FROM tbl a JOIN tbl2 b GROUP BY a.a1

List: Derived Data Your Code Spring Data JPA JPA SELECT a.a1, SUM(b.a1) JDBC , SUM(CASE WHEN b.a2=1 THEN b.a1 ELSE 0 Database 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) Your Code , SUM(b.a1) FILTER (WHERE b.a2=1) FROM tbl a JOIN tbl2 b Spring Data JPA GROUP BY a.a1 JPA SELECT a.a1, SUM(b.a1) JDBC , SUM(CASE WHEN b.a2=1 THEN b.a1 ELSE 0 Database END ) FROM tbl a JOIN tbl2 b GROUP BY a.a1

List: Derived Data 1999 2001 2003 2005 2007 2009 2011 2013 2015 2017 2019 More beautifully, but rarely supported: MariaDB 5.1 SELECT a.a1, SUM(b.a1) MySQL Your Code , SUM(b.a1) FILTER (WHERE b.a2=1) PostgreSQL 9.4 FROM tbl a JOIN tbl2 b 3.30 SQLite 1.0 Spring Data JPA GROUP BY a.a1 DB2 LUW JPA Oracle SELECT a.a1, SUM(b.a1) SQL Server JDBC , SUM(CASE WHEN b.a2=1 THEN b.a1 ELSE 0 Database END ) FROM tbl a JOIN tbl2 b GROUP BY a.a1

List: Derived Data 1999 2001 2003 2005 2007 2009 2011 2013 2015 2017 2019 More beautifully, but rarely supported: MariaDB 5.1 SELECT a.a1, SUM(b.a1) MySQL Your Code , SUM(b.a1) FILTER (WHERE b.a2=1) PostgreSQL 9.4 FROM tbl a JOIN tbl2 b 3.30 SQLite 1.0 Spring Data JPA GROUP BY a.a1 DB2 LUW JPA Oracle SELECT a.a1, SUM(b.a1) SQL Server JDBC , SUM(CASE WHEN b.a2=1 THEN b.a1 ELSE 0 Database END ) JPQL doesn't allow FROM tbl a JOIN tbl2 b CASE in aggregates GROUP BY a.a1

List: Derived Data Your Code Spring Data JPA JPA SELECT a.a1, SUM(b.a1) JDBC , SUM(CASE WHEN b.a2=1 THEN b.a1 ELSE 0 Database END ) JPQL doesn't allow FROM tbl a JOIN tbl2 b CASE in aggregates GROUP BY a.a1

List: Derived Data @NamedNativeQuery( name = "Entity.findAllXy", query = "<<SQL>>", resultSetMapping = "…") @SqlResultSetMapping(…) public class Entity {…} Your Code Spring Data JPA JPA SELECT a.a1, SUM(b.a1) JDBC , SUM(CASE WHEN b.a2=1 THEN b.a1 ELSE 0 Database END ) JPQL doesn't allow FROM tbl a JOIN tbl2 b CASE in aggregates GROUP BY a.a1

List: Derived Data @NamedNativeQuery( name = "Entity.findAllXy", query = "<<SQL>>", resultSetMapping = "…") @SqlResultSetMapping(…) public class Entity {…} Your Code Collection<DTO> c = em.create Named Query( "Entity.findAllXy" ) Spring Data JPA .getResultList(); JPA SELECT a.a1, SUM(b.a1) JDBC , SUM(CASE WHEN b.a2=1 THEN b.a1 ELSE 0 Database END ) JPQL doesn't allow FROM tbl a JOIN tbl2 b CASE in aggregates GROUP BY a.a1

List: Derived Data @Query( native=true ) @NamedNativeQuery( Collection<DTO> findAll Xy (); name = "Entity.findAllXy", query = "<<SQL>>", resultSetMapping = "…") @SqlResultSetMapping(…) public class Entity {…} Your Code Collection<DTO> c = em.create Named Query( "Entity.findAllXy" ) Spring Data JPA .getResultList(); JPA SELECT a.a1, SUM(b.a1) JDBC , SUM(CASE WHEN b.a2=1 THEN b.a1 ELSE 0 Database END ) JPQL doesn't allow FROM tbl a JOIN tbl2 b CASE in aggregates GROUP BY a.a1

Application

Application

Application

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

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

Pattern #2: Search

Pattern #2: Search

Search: Load Entity from Native Query Your Code Spring Data JPA JPA JDBC Database SELECT * FROM tbl a <<super complex query>>

Search: Load Entity from Native Query @NamedNativeQuery( Your Code name = "Entity.find SpecialOnes ", query = "<<SQL>>", Spring Data JPA resultSetMapping = "…") @SqlResultSetMapping( JPA @entities = @EntityResult( JDBC entityClass = Entity.class) ) public class Entity {…} Database SELECT * FROM tbl a <<super complex query>>

Search: Load Entity from Native Query @Query( native=true ) Collection< Entity > find SpecialOnes (); @NamedNativeQuery( Your Code name = "Entity.find SpecialOnes ", query = "<<SQL>>", Spring Data JPA resultSetMapping = "…") @SqlResultSetMapping( JPA @entities = @EntityResult( JDBC entityClass = Entity.class) ) public class Entity {…} Database SELECT * FROM tbl a <<super complex query>>

CREATE TABLE t ( id INTEGER, parent INTEGER, )

CREATE TABLE t ( id INTEGER, parent INTEGER, )

CREATE TABLE t ( id INTEGER, parent INTEGER, )

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

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

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

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

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

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

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 = ?

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

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

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

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

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

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

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

Vendor Dialect

Standard SQL (ISO 9075) Vendor Dialect

HQL Standard SQL (ISO 9075) Vendor Dialect

JPQL HQL Standard SQL (ISO 9075) Vendor Dialect

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

with recursive 9 1 3 5 7 9 1 3 5 7 9 9 0 0 0 0 0 1 1 1 1 1 9 0 0 0 0 0 0 0 0 0 0 1 2 2 2 2 2 2 2 2 2 2 MariaDB 5.1 10.2 MySQL 8.0 PostgreSQL 8.4 SQLite 1.0 3.8.3 DB2 LUW * 7.0 Spring .findBy… Oracle * 11gR2 SQL Server * 2005 JPQL HQL Standard SQL (ISO 9075) Vendor Dialect * without keyword recursive

JVM

JVM

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