java sql stronger together
play

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

Mar 04, 2024 •199 likes •1.54k 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)

  1. 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 ON …

  2. 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 ON …

  3. 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") Consider Collection<DTO> findAllDtoProj(); Blaze Persistence Your Code Collection< DTO > c = r.findAll DtoProj (); Entity Views 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 ON …

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

  5. 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

  6. 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

  7. 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

  8. 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

  9. 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

  10. 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

  11. 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

  12. 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

  13. 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

  14. 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

  15. 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

  16. 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

  17. List: Derived Data @EntityView(Entity.class) interface Proj { Blaze Persistence Integer getA1(); Entity Views & JPQL.next @Mapping("SUM(b.a1)") Integer getSum1(); @Mapping("SUM(b.a1) FILTER (WHERE a2=1)") Your Code Integer getSum2(); 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

  21. Application

  22. Application

  23. Application

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

  25. 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.

  26. Pattern #2: Search

  27. Pattern #2: Search

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

  29. 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>>

  30. 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>>

  31. CREATE TABLE t ( id INTEGER, parent INTEGER, )

  32. CREATE TABLE t ( id INTEGER, parent INTEGER, )

  33. CREATE TABLE t ( id INTEGER, parent INTEGER, )

  34. CREATE TABLE t ( id INTEGER, parent INTEGER, )

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

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

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

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

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

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

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

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

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

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

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

  48. 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

  49. 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

  50. 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

  52. Vendor Dialect

  53. Standard SQL (ISO 9075) Vendor Dialect

  54. HQL Standard SQL (ISO 9075) Vendor Dialect

  55. JPQL HQL Standard SQL (ISO 9075) Vendor Dialect

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

  57. 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

  58. JVM

Recommend

Java &amp; SQL Stronger Together @MarkusWinand @ModernSQL Picture: Africa

Java &amp; SQL Stronger Together @MarkusWinand @ModernSQL Picture: Africa

Java &amp; 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)

1.37k views • 122 slides
JAVA Java vs. Java Java Language Specification

JAVA Java vs. Java Java Language Specification

BR 10/05 JAVA Java vs. Java Java Language Specification http://java.sun.com/docs/books/jls/second_edition/html/j.title.doc.html Java programming language is compiled into java byte code Java Virtual Machine Specification

1.07k views • 44 slides
Multi-core in JVM/Java Concurrent programming in java Prior Java 5 Java 5 (2006)

Multi-core in JVM/Java Concurrent programming in java Prior Java 5 Java 5 (2006)

Multi-core in JVM/Java Concurrent programming in java Prior Java 5 Java 5 (2006) Java 7 (2010) Other topics Basic concurrency in Java Java Memory Model describes how threads interact through memory Single thread

812 views • 34 slides
Java Java Basics Java Program Statements Java Review Conditional statements

Java Java Basics Java Program Statements Java Review Conditional statements

Java Java Basics Java Program Statements Java Review Conditional statements Repetition statements (loops) Writing Classes in Java Selim Aksoy Class definitions Bilkent University Encapsulation and Java modifiers

346 views • 11 slides
Growing Stronger Together Webinar Series We Are Not Alone: Growing Stronger Together The

Growing Stronger Together Webinar Series We Are Not Alone: Growing Stronger Together The

Webinar Series We Are Not Alone: Growing Stronger Together Webinar Series We Are Not Alone: Growing Stronger Together The Virtual Work-Ordered Day: Making It Work For Your Clubhouse! Presented by Genesis Club Worcester, Massachusetts Ruth

554 views • 12 slides
1 Java compilation model Java bytecode format void spin () { int i; for (i = 0; i &lt; 100;

1 Java compilation model Java bytecode format void spin () { int i; for (i = 0; i &lt; 100;

The Java programming environment Introduction to JVM Based on material produced by Bill Venners 1 2 The Java platform The role of the virtual machime byte code generated by the Java front-end is an intermediate representation (IR)

834 views • 3 slides
TM Technology for Blu-ray and TV: Java Creating your own Blu-ray Java Discs The Blu-ray Java

TM Technology for Blu-ray and TV: Java Creating your own Blu-ray Java Discs The Blu-ray Java

TM Technology for Blu-ray and TV: Java Creating your own Blu-ray Java Discs The Blu-ray Java Authoring Team: Bill Foote, Chihiro Saito, A. Sundararajan, Jaya Hangal TS-5449 Talk Outline HD Cookbook Community Overview Target: Blu-ray

592 views • 44 slides
DTrace Topics: -&gt; java/lang/System.arraycopy &lt;- java/lang/System.arraycopy Java &lt;-

DTrace Topics: -&gt; java/lang/System.arraycopy &lt;- java/lang/System.arraycopy Java &lt;-

# ./jflow.d &lt;- java/lang/Thread.sleep -&gt; Greeting.greet -&gt; java/io/PrintStream.println -&gt; java/io/PrintStream.print -&gt; java/io/PrintStream.write -&gt; java/io/PrintStream.ensureOpen &lt;- java/io/PrintStream.ensureOpen -&gt;

632 views • 34 slides
Migrating to Java 9 Modules @Sander_Mak By Sander Mak Migrating to Java 9 Java 8 java -cp ..

Migrating to Java 9 Modules @Sander_Mak By Sander Mak Migrating to Java 9 Java 8 java -cp ..

Migrating to Java 9 Modules @Sander_Mak By Sander Mak Migrating to Java 9 Java 8 java -cp .. -jar myapp.jar Java 9 java -cp .. -jar myapp.jar Today's journey Running on Java 9 Java 9 modules Migrating to modules Modularising code

851 views • 45 slides
C vs Java Dalhousie University Winter 2019 Comparing C to Java Assumption: You know Java well.

C vs Java Dalhousie University Winter 2019 Comparing C to Java Assumption: You know Java well.

CSCI 2132: Software Development Norbert Zeh Faculty of Computer Science C vs Java Dalhousie University Winter 2019 Comparing C to Java Assumption: You know Java well. Focus on differences between C and Java. Arithmetic Operators Most

152 views • 14 slides
Actually, C++ and Java are much the same as C. C was designed in the 1970s. 1 Java 2 C 3

Actually, C++ and Java are much the same as C. C was designed in the 1970s. 1 Java 2 C 3

C is a high level language (HLL) Its syntax is much the same as Java and C++, but no objects. Actually, C++ and Java are much the same as C. C was designed in the 1970s. 1 Java 2 C 3 Why C ? 1. Millions of lines of code have been

377 views • 23 slides
A stronger competitive position Q2 2004 Oslo London 25 - 26 August 2004 A stronger

A stronger competitive position Q2 2004 Oslo London 25 - 26 August 2004 A stronger

Norw egian Air Shuttle ASA (NAS) A stronger competitive position Q2 2004 Oslo London 25 - 26 August 2004 A stronger competitive position Q2 results in line with expectations Strong financial position maintained Low-cost model

370 views • 20 slides
JAVA Language Mapping Java IDL (CORBA) introduced in Version 1.2 of the Java 2 platform,

JAVA Language Mapping Java IDL (CORBA) introduced in Version 1.2 of the Java 2 platform,

BR 11/05 JAVA Language Mapping Java IDL (CORBA) introduced in Version 1.2 of the Java 2 platform, provides an interface between Java programs and distributed objects and services built using the Common Object Request Broker

355 views • 31 slides
JAVA Basics &amp; OOP 2020/3/14 Write Once, Run Anywhere Java Virtual Machine (JVM)

JAVA Basics &amp; OOP 2020/3/14 Write Once, Run Anywhere Java Virtual Machine (JVM)

Poly- mor- phism Abstra ction Class OOP Inheri -tance En- capsu- lation Kuan-Ting Lai JAVA Basics &amp; OOP 2020/3/14 Write Once, Run Anywhere Java Virtual Machine (JVM) http://net-informations.com/java/intro/jvm.htm Java Overview

660 views • 44 slides
RIDING THE JET STREAMS FUAD MALIKOV | HAZELCAST JAVA 8 STREAM API WHAT IS IT? JAVA 8 PRE JAVA

RIDING THE JET STREAMS FUAD MALIKOV | HAZELCAST JAVA 8 STREAM API WHAT IS IT? JAVA 8 PRE JAVA

RIDING THE JET STREAMS FUAD MALIKOV | HAZELCAST JAVA 8 STREAM API WHAT IS IT? JAVA 8 PRE JAVA 8 HISTORIC TIMES Collections Iterators For loops If-else statements WORD COUNT CODE Pre Java 8 Stream examples Get the unique

489 views • 19 slides
2017 NACo Board 101 A Brief Overview of NACo 1 STRONGER COUNTIES. STRONGER AMERICA. NAC O The

2017 NACo Board 101 A Brief Overview of NACo 1 STRONGER COUNTIES. STRONGER AMERICA. NAC O The

2017 NACo Board 101 A Brief Overview of NACo 1 STRONGER COUNTIES. STRONGER AMERICA. NAC O The National Association of Counties (NACo) unites Americas 3,069 county governments. Founded in 1935, NACo brings county officials together to advocate

492 views • 37 slides
The testing pyramid Maurcio F. Aniche M.F.Aniche@tudelft.nl A.java ATest.java Thats what

The testing pyramid Maurcio F. Aniche M.F.Aniche@tudelft.nl A.java ATest.java Thats what

The testing pyramid Maurcio F. Aniche M.F.Aniche@tudelft.nl A.java ATest.java Thats what we have been calling B.java BTest.java Unit Testing . C.java CTest.java Some definitions ISQTB: Searches for defects in, and verifies the

604 views • 17 slides
C++ -&gt; Java Moving from C++ to Java Execu7ve

C++ -&gt; Java Moving from C++ to Java Execu7ve

C++ -&gt; Java Moving from C++ to Java Execu7ve Summary Introduces Java to a C++ programmer. Provides a roadmap for understanding advanced

798 views • 50 slides
Upgrading Past Java 9 Sounds Scary and I dont want to pay for Java Super happy with Java 8,

Upgrading Past Java 9 Sounds Scary and I dont want to pay for Java Super happy with Java 8,

Upgrading Past Java 9 Sounds Scary and I dont want to pay for Java Super happy with Java 8, Super happy with Java 8, thanks thanks Starting with Java 11, Oracle will provide JDK releases under the open source GNU General Public License

1.01k views • 71 slides
Healthy Places for Kids to Eat (HPKE) Presented by: Growing Stronger Together Todays

Healthy Places for Kids to Eat (HPKE) Presented by: Growing Stronger Together Todays

Growing Stronger Together Farm to School/Preschool Creating.. Healthy Places for Kids to Eat (HPKE) Presented by: Growing Stronger Together Todays Presenter: Mary Stein, MS Deputy Director, National Farm to School Network Growing Stronger

772 views • 30 slides
Todays topics Java Syntax and Grammars Sample Programs Upcoming More

Todays topics Java Syntax and Grammars Sample Programs Upcoming More

Todays topics Java Syntax and Grammars Sample Programs Upcoming More Java Reading Great Ideas , Chapter 2 Java! Java is a buzzword-enabled language From Sun (the developers of Java), Java is a simple,

582 views • 28 slides
Stronger Together: Honoring the Past and Supporting the Future Welcome! Stronger Together:

Stronger Together: Honoring the Past and Supporting the Future Welcome! Stronger Together:

Stronger Together: Honoring the Past and Supporting the Future Welcome! Stronger Together: Honoring the Past and Supporting the Future Held in conjunction with the 9/11 National Day of Service 9/11 Day of Remembrance and

530 views • 28 slides
Introduction to Java Brief history of Java Sample Java Program Compiling &amp;

Introduction to Java Brief history of Java Sample Java Program Compiling &amp;

Introduction to Java Brief history of Java Sample Java Program Compiling &amp; Executing Reading: =&gt; Section 1.1 1 Introduction to Java History Invented in 1991 - Sun Microsystems, Inc (James Gosling). Sun

593 views • 12 slides
Java SE 9 and the Application Server Kevin Sutter MicroProfile and Java EE Architect

Java SE 9 and the Application Server Kevin Sutter MicroProfile and Java EE Architect

InterConnect EclipseCon Europe 2017 2017 Java SE 9 and the Application Server Kevin Sutter MicroProfile and Java EE Architect @kwsutter 1 Java SE 9 Standalone 2 10/30/17 Java 9 Standard Features JSR 379: Java SE 9 Release

468 views • 32 slides

More recommend