| = 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 |
| :table-stripes: hover |
| :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] |
| === 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%autofit.supersmall,format=tsv,stripes=hover] |
| |=== |
| post_id com_id cat_id body title comment_author com_pubdate spamp com_text cat_name |
| 4 16 44 "foo" Post #0 Anonymous Coward 2021-02-27 10:32:16.133969 HAM First post Category #1 |
| 4 15 44 "foo" Post #0 Anonymous Coward 2021-02-27 10:32:16.133963 SPAM First post! Category #1 |
| 4 14 44 "foo" Post #0 Anonymous Coward 2021-02-27 10:32:16.133870 UNKNOWN First post!! Category #1 |
| 4 16 45 "foo" Post #0 Anonymous Coward 2021-02-27 10:32:16.133969 HAM First post Category #0 |
| 4 15 45 "foo" Post #0 Anonymous Coward 2021-02-27 10:32:16.133963 SPAM First post! Category #0 |
| 4 14 45 "foo" Post #0 Anonymous Coward 2021-02-27 10:32:16.133870 UNKNOWN First post!! Category #0 |
| 5 19 44 "bar!" Post #1 Anonymous Coward 2021-02-27 10:32:16.135200 SPAM Bah Category #1 |
| 5 18 44 "bar!" Post #1 Anonymous Coward 2021-02-27 10:32:16.135192 SPAM OK Category #1 |
| 5 17 44 "bar!" Post #1 Anonymous Coward 2021-02-27 10:32:16.135205 UNKNOWN Meh. Category #1 |
| 5 19 45 "bar!" Post #1 Anonymous Coward 2021-02-27 10:32:16.135200 SPAM Bah Category #0 |
| 5 18 45 "bar!" Post #1 Anonymous Coward 2021-02-27 10:32:16.135192 SPAM OK Category #0 |
| 5 17 45 "bar!" Post #1 Anonymous Coward 2021-02-27 10:32:16.135205 UNKNOWN Meh. Category #0 |
| |=== |
| ... |
| |
| [%notitle] |
| === Fix: Sequential Fetch Queries |
| |
| [source,java,linenums,highlight="|4-9|11-18|15,17"] |
| ---- |
| @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] |
| == First-Level Caching for the Win |
| |
| [source,java,linenums,highlight="|4-9|11-12|14-17"] |
| ---- |
| @Transactional |
| public void updateCommentStatus() { |
| |
| List<Comment> comments = Comment.find( //<1> |
| """ |
| SELECT c FROM Comment c |
| WHERE c.spamStatus = 'UNKNOWN' |
| """) |
| .list(); |
| |
| Map<Long, SpamState> 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,linenums,highlight="|6,7"] |
| ---- |
| @Entity |
| public class Author extends PanacheEntity { |
| |
| public String name; |
| |
| @OneToOne(fetch = FetchType.LAZY, |
| mappedBy = "author") |
| public BasicCredentials basicCredentials; |
| } |
| ---- |
| |
| [source,java,linenums,highlight="0"] |
| ---- |
| @Entity |
| public class BasicCredentials extends PanacheEntity { |
| |
| @OneToOne(fetch = FetchType.LAZY) |
| @MapsId |
| public Author author; |
| |
| 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,linenums,highlight="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] |
| === Fix: Bytecode Enhancement (Gradle) |
| |
| [source,groovy,linenums,highlight="5|11"] |
| ---- |
| apply plugin: 'org.hibernate.orm' |
| |
| buildscript { |
| dependencies { |
| classpath "org.hibernate:hibernate-gradle-plugin:$hibernateVersion" |
| } |
| } |
| |
| hibernate { |
| enhance { |
| enableLazyInitialization = true |
| enableDirtyTracking = false |
| enableAssociationManagement = false |
| } |
| } |
| ---- |
| |
| [%notitle] |
| === Fix: Bytecode Enhancement (Maven) |
| |
| [source,xml,linenums,highlight="3|10"] |
| ---- |
| <plugin> |
| <groupId>org.hibernate.orm.tooling</groupId> |
| <artifactId>hibernate-enhance-maven-plugin</artifactId> |
| <version>${hibernate.version}</version> |
| |
| <executions> |
| <execution> |
| |
| <configuration> |
| <enableLazyInitialization>true</enableLazyInitialization> |
| <enableDirtyTracking>false</enableDirtyTracking> |
| <enableAssociationManagement>false</enableAssociationManagement> |
| <failOnError>true</failOnError> |
| </configuration> |
| |
| <goals> |
| <goal>enhance</goal> |
| </goals> |
| |
| </execution> |
| </executions> |
| </plugin> |
| ---- |
| |
| [%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.columns] |
| == Projections |
| |
| [source,java] |
| [.column.is-one-half] |
| ---- |
| public final class PostSummary { |
| |
| public final String authorName; |
| public final String title; |
| public final Instant publicationDate; |
| public final int commentCount; |
| |
| public PostSummary( |
| String authorName, |
| String title, |
| Instant pubDate, |
| int commentCount) { |
| this.authorName = authorName; |
| this.title = title; |
| this.publicationDate = pubDate; |
| this.commentCount = commentCount; |
| } |
| } |
| ---- |
| |
| [source,java] |
| [.column.is-one-half] |
| ---- |
| @Transactional |
| public List<PostSummary> overview() { |
| |
| return Post.<Post>find( |
| """ |
| SELECT p FROM Post p |
| LEFT JOIN FETCH p.author |
| LEFT JOIN FETCH p.comments |
| """) |
| .stream() |
| .map((Post p) -> |
| new PostSummary( |
| p.author.name, |
| p.title, |
| p.publicationDate, |
| p.comments.size())) |
| .collect(Collectors.toList()); |
| } |
| ---- |
| |
| [%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] |
| === 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 |
| ---- |
| |
| |
| == Conclusion |
| |
| [%step] |
| * Use a DTO projection if you can. |
| * If not: |
| [%step] |
| ** Check your ``@OneToOne``s. Use `@LazyToOne` and bytecode enhancement if necessary. |
| ** Don't `LEFT JOIN FETCH` more than one collection at once. Use sequential queries instead. |
| ** The 1st-level cache is your friend. Use it. |