| = Highly Efficient Enterprise Data Access |
| Matthias Andreas Benkard |
| // Meta |
| :experimental: |
| :data-uri: |
| :toc: |
| :stem: |
| :toclevels: 2 |
| :description: Slides for my Hibernate Patterns and Antipatterns talk |
| :keywords: mulk |
| // Settings |
| :icons: font |
| :revealjs_theme: simple |
| :revealjs_width: 1280 |
| :revealjs_height: 800 |
| :customcss: SLIDES.css |
| :source-highlighter: highlightjs |
| :highlightjs-theme: grayscale.css |
| :stem: |
| |
| |
| [%notitle] |
| == Setting the Stage |
| |
| image:UML.png[] |
| |
| [%notitle] |
| [.columns] |
| === Posts |
| |
| [source,java,data-lines=] |
| [.column] |
| [.is-two-thirds] |
| ---- |
| @Entity |
| public class Post extends PanacheEntity { |
| |
| public String title; |
| public Instant publicationDate; |
| public String body; |
| |
| @ManyToOne(fetch = FetchType.LAZY) //<1> |
| public Author author; |
| |
| @ManyToMany(fetch = FetchType.LAZY) //<2> |
| public Collection<Category> categories; |
| |
| @OneToMany(fetch = FetchType.LAZY, |
| mappedBy = "post") //<3> |
| public Collection<Comment> comments; |
| } |
| ---- |
| |
| [.column] |
| <1> lazy is good (can always `JOIN FETCH` later) |
| <2> `@ManyToMany` always implies a join table |
| <3> mapping without a join table, non-owning side |
| |
| |
| [%notitle] |
| === Comments and Categories |
| |
| [source,java] |
| ---- |
| @Entity |
| public class Comment extends PanacheEntity { |
| |
| public String authorName; |
| public Instant publicationDate; |
| public String text; |
| |
| @Enumerated(EnumType.STRING) |
| public SpamStatus spamStatus; |
| |
| @ManyToOne(fetch = FetchType.LAZY) |
| public Post post; |
| } |
| ---- |
| |
| [source,java] |
| ---- |
| @Entity |
| public class Category extends PanacheEntity { |
| |
| public String name; |
| } |
| ---- |
| |
| |
| [%notitle] |
| === Authors, Login Credentials |
| |
| [source,java] |
| ---- |
| @Entity |
| public class Author extends PanacheEntity { |
| |
| public String name; |
| |
| @OneToOne(fetch = FetchType.LAZY, //<1> |
| mappedBy = "author") |
| public BasicCredentials basicCredentials; |
| } |
| ---- |
| |
| [source,java] |
| ---- |
| @Entity |
| public class BasicCredentials extends PanacheEntity { |
| |
| @OneToOne(fetch = FetchType.LAZY) |
| @MapsId |
| public Author author; |
| |
| public String username; |
| public String password; |
| } |
| ---- |
| |
| |
| [%notitle] |
| //[.columns] |
| == Cartesian Blowup |
| |
| [source,java] |
| ---- |
| @Transactional |
| public List<Post> getAllWithCommentsAndCategories() { |
| |
| return Post.find( |
| """ |
| SELECT p FROM Post p |
| LEFT JOIN FETCH p.comments |
| LEFT JOIN FETCH p.categories |
| """) |
| .list(); |
| } |
| ---- |
| |
| [%notitle] |
| === Bad SQL |
| |
| [source,sql] |
| //[.column] |
| //[.is-two-thirds] |
| ---- |
| select |
| post0_.id, |
| comments1_.id, |
| category3_.id, |
| post0_.author_id, |
| post0_.body, |
| post0_.publication_date, |
| post0_.title, |
| comments1_.author_name, |
| comments1_.post_id, |
| comments1_.publication_date, |
| comments1_.spam_status, |
| comments1_.text, |
| comments1_.post_id, |
| comments1_.id, |
| category3_.name, |
| categories2_.post_id, |
| categories2_.categories_id |
| from |
| post post0_ |
| left outer join |
| comment comments1_ |
| on post0_.id=comments1_.post_id |
| left outer join |
| post_category categories2_ |
| on post0_.id=categories2_.post_id |
| left outer join |
| category category3_ |
| on categories2_.categories_id=category3_.id; |
| ---- |
| |
| [%notitle] |
| === Bad Results (1) |
| |
| -- |
| [source] |
| ---- |
| [2021-02-27 10:32:58] 60 rows retrieved |
| ---- |
| |
| stem:[60 = ubrace|"posts"|_10 * |
| ubrace|("comments")/("post")|_3 * ubrace|"categories"|_2] |
| |
| Cartesian explosion! 🙀 |
| |
| What gives? |
| -- |
| |
| [%notitle] |
| === Bad Results (2) |
| |
| [%header,format=tsv] |
| [.supersmall] |
| |=== |
| post_id com_id cat_id aut_id body pubdate title author_name postid2 com_pubdate spamp com_text postid3 com_id cat_name postid4 cat_id |
| 4 16 44 1 "" 2021-02-27 10:32:16.129627 Post #0 Anonymous Coward 4 2021-02-27 10:32:16.133969 UNKNOWN First post 4 16 Category #1 4 44 |
| 4 15 44 1 "" 2021-02-27 10:32:16.129627 Post #0 Anonymous Coward 4 2021-02-27 10:32:16.133963 UNKNOWN First post 4 15 Category #1 4 44 |
| 4 14 44 1 "" 2021-02-27 10:32:16.129627 Post #0 Anonymous Coward 4 2021-02-27 10:32:16.133870 UNKNOWN First post 4 14 Category #1 4 44 |
| 4 16 45 1 "" 2021-02-27 10:32:16.129627 Post #0 Anonymous Coward 4 2021-02-27 10:32:16.133969 UNKNOWN First post 4 16 Category #0 4 45 |
| 4 15 45 1 "" 2021-02-27 10:32:16.129627 Post #0 Anonymous Coward 4 2021-02-27 10:32:16.133963 UNKNOWN First post 4 15 Category #0 4 45 |
| 4 14 45 1 "" 2021-02-27 10:32:16.129627 Post #0 Anonymous Coward 4 2021-02-27 10:32:16.133870 UNKNOWN First post 4 14 Category #0 4 45 |
| 5 19 44 2 "" 2021-02-27 10:32:16.129674 Post #1 Anonymous Coward 5 2021-02-27 10:32:16.135200 UNKNOWN First post 5 19 Category #1 5 44 |
| 5 18 44 2 "" 2021-02-27 10:32:16.129674 Post #1 Anonymous Coward 5 2021-02-27 10:32:16.135192 UNKNOWN First post 5 18 Category #1 5 44 |
| 5 17 44 2 "" 2021-02-27 10:32:16.129674 Post #1 Anonymous Coward 5 2021-02-27 10:32:16.135205 UNKNOWN First post 5 17 Category #1 5 44 |
| 5 19 45 2 "" 2021-02-27 10:32:16.129674 Post #1 Anonymous Coward 5 2021-02-27 10:32:16.135200 UNKNOWN First post 5 19 Category #0 5 45 |
| 5 18 45 2 "" 2021-02-27 10:32:16.129674 Post #1 Anonymous Coward 5 2021-02-27 10:32:16.135192 UNKNOWN First post 5 18 Category #0 5 45 |
| 5 17 45 2 "" 2021-02-27 10:32:16.129674 Post #1 Anonymous Coward 5 2021-02-27 10:32:16.135205 UNKNOWN First post 5 17 Category #0 5 45 |
| 6 22 44 3 "" 2021-02-27 10:32:16.129700 Post #2 Anonymous Coward 6 2021-02-27 10:32:16.136043 UNKNOWN First post 6 22 Category #1 6 44 |
| 6 21 44 3 "" 2021-02-27 10:32:16.129700 Post #2 Anonymous Coward 6 2021-02-27 10:32:16.136038 UNKNOWN First post 6 21 Category #1 6 44 |
| 6 20 44 3 "" 2021-02-27 10:32:16.129700 Post #2 Anonymous Coward 6 2021-02-27 10:32:16.136031 UNKNOWN First post 6 20 Category #1 6 44 |
| 6 22 45 3 "" 2021-02-27 10:32:16.129700 Post #2 Anonymous Coward 6 2021-02-27 10:32:16.136043 UNKNOWN First post 6 22 Category #0 6 45 |
| 6 21 45 3 "" 2021-02-27 10:32:16.129700 Post #2 Anonymous Coward 6 2021-02-27 10:32:16.136038 UNKNOWN First post 6 21 Category #0 6 45 |
| 6 20 45 3 "" 2021-02-27 10:32:16.129700 Post #2 Anonymous Coward 6 2021-02-27 10:32:16.136031 UNKNOWN First post 6 20 Category #0 6 45 |
| 7 25 44 1 "" 2021-02-27 10:32:16.129724 Post #3 Anonymous Coward 7 2021-02-27 10:32:16.136904 UNKNOWN First post 7 25 Category #1 7 44 |
| 7 24 44 1 "" 2021-02-27 10:32:16.129724 Post #3 Anonymous Coward 7 2021-02-27 10:32:16.136897 UNKNOWN First post 7 24 Category #1 7 44 |
| 7 23 44 1 "" 2021-02-27 10:32:16.129724 Post #3 Anonymous Coward 7 2021-02-27 10:32:16.136909 UNKNOWN First post 7 23 Category #1 7 44 |
| 7 25 45 1 "" 2021-02-27 10:32:16.129724 Post #3 Anonymous Coward 7 2021-02-27 10:32:16.136904 UNKNOWN First post 7 25 Category #0 7 45 |
| 7 24 45 1 "" 2021-02-27 10:32:16.129724 Post #3 Anonymous Coward 7 2021-02-27 10:32:16.136897 UNKNOWN First post 7 24 Category #0 7 45 |
| 7 23 45 1 "" 2021-02-27 10:32:16.129724 Post #3 Anonymous Coward 7 2021-02-27 10:32:16.136909 UNKNOWN First post 7 23 Category #0 7 45 |
| 8 28 44 2 "" 2021-02-27 10:32:16.129746 Post #4 Anonymous Coward 8 2021-02-27 10:32:16.137743 UNKNOWN First post 8 28 Category #1 8 44 |
| 8 27 44 2 "" 2021-02-27 10:32:16.129746 Post #4 Anonymous Coward 8 2021-02-27 10:32:16.137739 UNKNOWN First post 8 27 Category #1 8 44 |
| 8 26 44 2 "" 2021-02-27 10:32:16.129746 Post #4 Anonymous Coward 8 2021-02-27 10:32:16.137731 UNKNOWN First post 8 26 Category #1 8 44 |
| 8 28 45 2 "" 2021-02-27 10:32:16.129746 Post #4 Anonymous Coward 8 2021-02-27 10:32:16.137743 UNKNOWN First post 8 28 Category #0 8 45 |
| 8 27 45 2 "" 2021-02-27 10:32:16.129746 Post #4 Anonymous Coward 8 2021-02-27 10:32:16.137739 UNKNOWN First post 8 27 Category #0 8 45 |
| 8 26 45 2 "" 2021-02-27 10:32:16.129746 Post #4 Anonymous Coward 8 2021-02-27 10:32:16.137731 UNKNOWN First post 8 26 Category #0 8 45 |
| 9 31 44 3 "" 2021-02-27 10:32:16.129767 Post #5 Anonymous Coward 9 2021-02-27 10:32:16.138536 UNKNOWN First post 9 31 Category #1 9 44 |
| 9 30 44 3 "" 2021-02-27 10:32:16.129767 Post #5 Anonymous Coward 9 2021-02-27 10:32:16.138548 UNKNOWN First post 9 30 Category #1 9 44 |
| 9 29 44 3 "" 2021-02-27 10:32:16.129767 Post #5 Anonymous Coward 9 2021-02-27 10:32:16.138543 UNKNOWN First post 9 29 Category #1 9 44 |
| 9 31 45 3 "" 2021-02-27 10:32:16.129767 Post #5 Anonymous Coward 9 2021-02-27 10:32:16.138536 UNKNOWN First post 9 31 Category #0 9 45 |
| 9 30 45 3 "" 2021-02-27 10:32:16.129767 Post #5 Anonymous Coward 9 2021-02-27 10:32:16.138548 UNKNOWN First post 9 30 Category #0 9 45 |
| 9 29 45 3 "" 2021-02-27 10:32:16.129767 Post #5 Anonymous Coward 9 2021-02-27 10:32:16.138543 UNKNOWN First post 9 29 Category #0 9 45 |
| 10 34 44 1 "" 2021-02-27 10:32:16.129789 Post #6 Anonymous Coward 10 2021-02-27 10:32:16.139349 UNKNOWN First post 10 34 Category #1 10 44 |
| 10 33 44 1 "" 2021-02-27 10:32:16.129789 Post #6 Anonymous Coward 10 2021-02-27 10:32:16.139354 UNKNOWN First post 10 33 Category #1 10 44 |
| 10 32 44 1 "" 2021-02-27 10:32:16.129789 Post #6 Anonymous Coward 10 2021-02-27 10:32:16.139337 UNKNOWN First post 10 32 Category #1 10 44 |
| 10 34 45 1 "" 2021-02-27 10:32:16.129789 Post #6 Anonymous Coward 10 2021-02-27 10:32:16.139349 UNKNOWN First post 10 34 Category #0 10 45 |
| 10 33 45 1 "" 2021-02-27 10:32:16.129789 Post #6 Anonymous Coward 10 2021-02-27 10:32:16.139354 UNKNOWN First post 10 33 Category #0 10 45 |
| 10 32 45 1 "" 2021-02-27 10:32:16.129789 Post #6 Anonymous Coward 10 2021-02-27 10:32:16.139337 UNKNOWN First post 10 32 Category #0 10 45 |
| 11 37 44 2 "" 2021-02-27 10:32:16.129809 Post #7 Anonymous Coward 11 2021-02-27 10:32:16.140032 UNKNOWN First post 11 37 Category #1 11 44 |
| 11 36 44 2 "" 2021-02-27 10:32:16.129809 Post #7 Anonymous Coward 11 2021-02-27 10:32:16.140025 UNKNOWN First post 11 36 Category #1 11 44 |
| 11 35 44 2 "" 2021-02-27 10:32:16.129809 Post #7 Anonymous Coward 11 2021-02-27 10:32:16.140037 UNKNOWN First post 11 35 Category #1 11 44 |
| 11 37 45 2 "" 2021-02-27 10:32:16.129809 Post #7 Anonymous Coward 11 2021-02-27 10:32:16.140032 UNKNOWN First post 11 37 Category #0 11 45 |
| 11 36 45 2 "" 2021-02-27 10:32:16.129809 Post #7 Anonymous Coward 11 2021-02-27 10:32:16.140025 UNKNOWN First post 11 36 Category #0 11 45 |
| 11 35 45 2 "" 2021-02-27 10:32:16.129809 Post #7 Anonymous Coward 11 2021-02-27 10:32:16.140037 UNKNOWN First post 11 35 Category #0 11 45 |
| 12 40 44 3 "" 2021-02-27 10:32:16.129839 Post #8 Anonymous Coward 12 2021-02-27 10:32:16.140766 UNKNOWN First post 12 40 Category #1 12 44 |
| 12 39 44 3 "" 2021-02-27 10:32:16.129839 Post #8 Anonymous Coward 12 2021-02-27 10:32:16.140786 UNKNOWN First post 12 39 Category #1 12 44 |
| 12 38 44 3 "" 2021-02-27 10:32:16.129839 Post #8 Anonymous Coward 12 2021-02-27 10:32:16.140779 UNKNOWN First post 12 38 Category #1 12 44 |
| 12 40 45 3 "" 2021-02-27 10:32:16.129839 Post #8 Anonymous Coward 12 2021-02-27 10:32:16.140766 UNKNOWN First post 12 40 Category #0 12 45 |
| 12 39 45 3 "" 2021-02-27 10:32:16.129839 Post #8 Anonymous Coward 12 2021-02-27 10:32:16.140786 UNKNOWN First post 12 39 Category #0 12 45 |
| 12 38 45 3 "" 2021-02-27 10:32:16.129839 Post #8 Anonymous Coward 12 2021-02-27 10:32:16.140779 UNKNOWN First post 12 38 Category #0 12 45 |
| 13 43 44 1 "" 2021-02-27 10:32:16.129860 Post #9 Anonymous Coward 13 2021-02-27 10:32:16.141651 UNKNOWN First post 13 43 Category #1 13 44 |
| 13 42 44 1 "" 2021-02-27 10:32:16.129860 Post #9 Anonymous Coward 13 2021-02-27 10:32:16.141655 UNKNOWN First post 13 42 Category #1 13 44 |
| 13 41 44 1 "" 2021-02-27 10:32:16.129860 Post #9 Anonymous Coward 13 2021-02-27 10:32:16.141642 UNKNOWN First post 13 41 Category #1 13 44 |
| 13 43 45 1 "" 2021-02-27 10:32:16.129860 Post #9 Anonymous Coward 13 2021-02-27 10:32:16.141651 UNKNOWN First post 13 43 Category #0 13 45 |
| 13 42 45 1 "" 2021-02-27 10:32:16.129860 Post #9 Anonymous Coward 13 2021-02-27 10:32:16.141655 UNKNOWN First post 13 42 Category #0 13 45 |
| 13 41 45 1 "" 2021-02-27 10:32:16.129860 Post #9 Anonymous Coward 13 2021-02-27 10:32:16.141642 UNKNOWN First post 13 41 Category #0 13 45 |
| |=== |
| |
| |
| [%notitle] |
| === Fix: Sequential Fetch Queries |
| |
| [source,java] |
| ---- |
| @Transactional |
| public List<Post> getAllWithCommentsAndCategories2() { |
| |
| List<Post> posts = Post.find( |
| """ |
| SELECT p FROM Post p |
| LEFT JOIN FETCH p.comments |
| """) |
| .list(); |
| |
| posts = Post.find( |
| """ |
| SELECT DISTINCT p FROM Post p |
| LEFT JOIN FETCH p.categories |
| WHERE p IN (?1) |
| """, |
| posts) |
| .list(); |
| |
| return posts; |
| } |
| ---- |
| |
| |
| [%notitle] |
| [.columns] |
| === Good SQL |
| |
| [source,sql] |
| [.column] |
| ---- |
| select |
| post0_.id, |
| comments1_.id, |
| post0_.author_id, |
| post0_.body, |
| post0_.publication_date, |
| post0_.title, |
| comments1_.author_name, |
| comments1_.post_id, |
| comments1_.publication_date, |
| comments1_.spam_status, |
| comments1_.text, |
| comments1_.post_id, |
| comments1_.id |
| from |
| post post0_ |
| left outer join |
| comment comments1_ |
| on post0_.id=comments1_.post_id |
| ---- |
| |
| [source,sql] |
| [.column] |
| ---- |
| select |
| distinct post0_.id, |
| cat2_.id, |
| post0_.author_id, |
| post0_.body, |
| post0_.publication_date, |
| post0_.title, |
| cat2_.name, |
| cats1_.post_id, |
| cats1_.categories_id |
| from |
| post post0_ |
| left outer join |
| post_category cats1_ |
| on post0_.id=cats1_.post_id |
| left outer join |
| category cat2_ |
| on cats1_.categories_id=cat2_.id |
| where |
| post0_.id in ( |
| ? , ? , ? , ? , ? , ? , ? , ? , |
| ? , ? , ? , ? , ? , ? , ? , ? , |
| ? , ? , ? , ? , ? , ? , ? , ? , |
| ? , ? , ? , ? , ? , ? |
| ) |
| ---- |
| |
| |
| //[%notitle] |
| //== Update without `#persist` |
| // |
| //[source,java] |
| //---- |
| //@Transactional |
| //public void resetCommentStatus() { |
| // |
| // List<Comment> comments = Comment.find( |
| // """ |
| // SELECT c FROM Comment c |
| // WHERE c.spamStatus <> 'UNKNOWN' |
| // """) |
| // .list(); |
| // |
| // comments.forEach(c -> c.spamStatus = SpamStatus.UNKNOWN); |
| //} |
| //---- |
| // |
| // |
| //[%notitle] |
| //=== Good SQL |
| // |
| //[source,sql] |
| //---- |
| //select |
| // comment0_.id, |
| // comment0_.author_name, |
| // comment0_.post_id, |
| // comment0_.publication_date, |
| // comment0_.spam_status, |
| // comment0_.text |
| //from |
| // comment comment0_ |
| //where |
| // comment0_.spam_status<>'UNKNOWN' |
| //---- |
| // |
| //[source,sql] |
| //---- |
| //update |
| // comment |
| //set |
| // author_name=?, |
| // post_id=?, |
| // publication_date=?, |
| // spam_status=?, |
| // text=? |
| //where |
| // id=? |
| // |
| //update |
| //... |
| //---- |
| |
| |
| [%notitle] |
| == First-Level Caching for the Win |
| |
| [source,java] |
| ---- |
| @Transactional |
| public void updateCommentStatus() { |
| |
| List<Comment> comments = Comment.find( //<1> |
| """ |
| SELECT c FROM Comment c |
| WHERE c.spamStatus = 'UNKNOWN' |
| """) |
| .list(); |
| |
| var assessments = spamAssessmentService.assess(comments); //<2> |
| |
| for (var assessment : assessments.entrySet()) { |
| Comment comment = Comment.findById(assessment.getKey()); //<3> |
| comment.spamStatus = assessment.getValue(); |
| } |
| } |
| ---- |
| |
| |
| [%notitle] |
| === Good SQL |
| |
| [source,sql] |
| ---- |
| select |
| comment0_.id, |
| comment0_.author_name, |
| comment0_.post_id, |
| comment0_.publication_date, |
| comment0_.spam_status, |
| comment0_.text |
| from |
| comment comment0_ |
| where |
| comment0_.spam_status='UNKNOWN' |
| ---- |
| |
| [source,sql] |
| ---- |
| update |
| comment |
| set |
| author_name=?, |
| post_id=?, |
| publication_date=?, |
| spam_status=?, |
| text=? |
| where |
| id=? |
| |
| update |
| ... |
| ---- |
| |
| [%notitle] |
| == `@OneToOne` Surprises |
| |
| [source,java] |
| ---- |
| @Transactional |
| public List<Post> getAllWithAuthors() { |
| |
| return Post.find( |
| """ |
| SELECT p FROM Post p |
| LEFT JOIN FETCH p.author |
| """) |
| .list(); |
| } |
| ---- |
| |
| |
| [%notitle] |
| [.columns] |
| === Bad SQL |
| |
| [source,sql] |
| [.column] |
| ---- |
| select |
| post0_.id, |
| author1_.id, |
| post0_.author_id, |
| post0_.body, |
| post0_.publication_date, |
| post0_.title, |
| author1_.name |
| from |
| post post0_ |
| left outer join |
| author author1_ |
| on post0_.author_id=author1_.id |
| |
| select |
| basiccrede0_.author_id, |
| basiccrede0_.password, |
| basiccrede0_.username |
| from |
| basic_credentials basiccrede0_ |
| where |
| basiccrede0_.author_id=? |
| ---- |
| |
| [source,sql] |
| [.column] |
| ---- |
| select |
| basiccrede0_.author_id, |
| basiccrede0_.password, |
| basiccrede0_.username |
| from |
| basic_credentials basiccrede0_ |
| where |
| basiccrede0_.author_id=? |
| |
| select |
| basiccrede0_.author_id, |
| basiccrede0_.password, |
| basiccrede0_.username |
| from |
| basic_credentials basiccrede0_ |
| where |
| basiccrede0_.author_id=? |
| ---- |
| |
| |
| [%notitle] |
| === Another Look at the Author |
| |
| [source,java] |
| ---- |
| @Entity |
| public class Author extends PanacheEntity { |
| |
| public String name; |
| |
| @OneToOne(fetch = FetchType.LAZY, |
| mappedBy = "author") |
| public BasicCredentials basicCredentials; //<1> |
| } |
| ---- |
| |
| [source,java] |
| ---- |
| @Entity |
| public class BasicCredentials extends PanacheEntity { |
| |
| @OneToOne(fetch = FetchType.LAZY) |
| @MapsId |
| public Author author; //<2> |
| |
| public String username; |
| public String password; |
| } |
| ---- |
| |
| |
| === Why? |
| |
| Hibernate has no way of knowing whether `Author#basicCredentials` is `null`! |
| |
| |
| [%notitle] |
| [.columns] |
| === Fix: `@LazyToOne(NO_PROXY)` |
| |
| [source,java,data-line-numbers=8] |
| [.column] |
| [.is-two-thirds] |
| ---- |
| @Entity |
| public class Author extends PanacheEntity { |
| |
| public String name; |
| |
| @OneToOne(fetch = FetchType.LAZY, |
| mappedBy = "author") |
| @LazyToOne(LazyToOneOption.NO_PROXY) //<1> |
| public BasicCredentials basicCredentials; |
| } |
| ---- |
| |
| [.column] |
| <1> `NO_PROXY` + bytecode enhancement = profit |
| |
| |
| [%notitle] |
| === Good SQL |
| |
| [source,sql] |
| ---- |
| select |
| post0_.id, |
| author1_.id, |
| post0_.author_id, |
| post0_.body, |
| post0_.publication_date, |
| post0_.title, |
| author1_.name |
| from |
| post post0_ |
| left outer join |
| author author1_ |
| on post0_.author_id=author1_.id |
| ---- |
| |
| [%notitle] |
| == Projection Queries |
| |
| [source,java] |
| ---- |
| @Transactional |
| public List<PostSummary> overview() { |
| |
| return entityManager.createQuery( |
| """ |
| SELECT NEW eu.mulk.demos.blog.posts.PostSummary( |
| p.author.name, p.title, p.publicationDate, size(p.comments)) |
| FROM Post p |
| """, |
| PostSummary.class) |
| .getResultList(); |
| } |
| ---- |
| |
| |
| [%notitle] |
| === Projection DTOs |
| |
| [source,java] |
| ---- |
| public final class PostSummary { |
| |
| public final String authorName; |
| public final String title; |
| public final Instant publicationDate; |
| public final int commentCount; |
| |
| public PostSummary( //<1> |
| String authorName, |
| String title, |
| Instant publicationDate, |
| int commentCount) { |
| this.authorName = authorName; |
| this.title = title; |
| this.publicationDate = publicationDate; |
| this.commentCount = commentCount; |
| } |
| } |
| ---- |
| |
| |
| [%notitle] |
| === Good SQL |
| |
| [source,sql] |
| ---- |
| select |
| author1_.name, |
| post0_.title, |
| post0_.publication_date, |
| (select |
| count(comments2_.post_id) |
| from |
| comment comments2_ |
| where |
| post0_.id = comments2_.post_id) |
| from |
| post post0_ |
| cross join |
| author author1_ |
| where |
| post0_.author_id=author1_.id |
| ---- |
| |
| |
| [%notitle] |
| == Conclusion |
| |
| - Lazy is good |
| - Lazier is better |
| - Lazy with bytecode enhancement is best |
| - Sequential queries avoid cartesian blowup |
| - The 1st-level cache is your friend |
| - Use DTO projections everywhere all the time |