Repository files navigation

Hibernate & Spring Boot Samples

1. How To Store UTC Timezone In MySQL

Description: This application is a sample of how to store date, time, and timestamps in UTC time zone. The second setting,useLegacyDatetimeCode is needed only for MySQL. Otherwise, set onlyhibernate.jdbc.time_zone.

Key points:

• spring.jpa.properties.hibernate.jdbc.time_zone=UTC
• spring.datasource.url=jdbc:mysql://localhost:3306/screenshotdb?useLegacyDatetimeCode=false

2. View Binding/Extracted Params Via Log4J 2

Description: View the prepared statement binding/extracted parameters via Log4J 2 logger setting.

Key points:

• for Maven, inpom.xml, exclude Spring Boot's Default Logging
• for Maven, inpom.xml, Add Log4j 2 Dependency
• inlog4j2.xml add,<Logger name="org.hibernate.type.descriptor.sql" level="trace"/>

Output example:

3. How To View Query Details Via DataSource-Proxy Library

Description: View the query details (query type, binding parameters, batch size, execution time, etc) viaDataSource-Proxy

Key points:

• for Maven, add inpom.xml thedatasource-proxy dependency
• create an bean post processor to intercept theDataSource bean
• wrap theDataSource bean viaProxyFactory and an implementation ofMethodInterceptor

Output example:

4. Batch Inserts viasaveAll(Iterable<S> entities) in MySQL

Description: Batch inserts viaSimpleJpaRepository#saveAll(Iterable<S> entities) method in MySQL

Key points:

• inapplication.properties setspring.jpa.properties.hibernate.jdbc.batch_size
• inapplication.properties setspring.jpa.properties.hibernate.generate_statistics (just to check that batching is working)
• inapplication.properties set JDBC URL withrewriteBatchedStatements=true (optimization for MySQL)
• inapplication.properties set JDBC URL withcachePrepStmts=true (enable caching and is useful if you decide to setprepStmtCacheSize,prepStmtCacheSqlLimit, etc as well; without this setting the cache is disabled)
• inapplication.properties set JDBC URL withuseServerPrepStmts=true (this way you switch to server-side prepared statements (may lead to signnificant performance boost))
• in case of using a parent-child relationship with cascade persist (e.g. one-to-many, many-to-many) then consider to set upspring.jpa.properties.hibernate.order_inserts=true to optimize the batching by ordering inserts
• in entity, use theassigned generator since MySQLIDENTITY will cause insert batching to be disabled
• in entity, add@Version property to avoid extra-SELECT statements fired before batching (also prevent lost updates in multi-request transactions). Extra-SELECT statements are the effect of usingmerge() instead ofpersist(); behind the scene,saveAll() usessave(), which in case of non-new entities (entities that have IDs) will callmerge(), which instruct Hibernate to fire aSELECT statement to make sure that there is no record in the database having the same identifier
• pay attention on the amount of inserts passed tosaveAll() to not "overwhelm" the Persistence Context; normally theEntityManager should be flushed and cleared from time to time, but during thesaveAll() execution you simply cannot do that, so if insaveAll() there is a list with a high amount of data, all that data will hit the Persistence Context (1st Level Cache) and will remain in memory until the flush time; using relatively small amount of data should be ok (in this example, each batch of 30 entities run in a separate transaction and Persistent Context)
• thesaveAll() method return aList<S> containing the persisted entities; each persisted entity is added into this list; if you just don't need thisList then it is created for nothing
• if is not needed, then ensure that Second Level Cache is disabled viaspring.jpa.properties.hibernate.cache.use_second_level_cache=false

5. Batch Inserts Via EntityManager (MySQL)

Description: This application is a sample of batching inserts viaEntityManager in MySQL. This way you can easily control theflush() andclear() cycles of the Persistence Context (1st Level Cache) inside the current transaction. This is not possible via Spring Boot,saveAll(Iterable<S> entities), since this method executes a single flush per transaction. Another advantage is that you can callpersist() instead ofmerge() - this is used behind the scene by the SpringBootsaveAll(Iterable<S> entities) andsave(S entity).

If you want to execute a batch per transaction (recommended) then check thisexample.

Key points:

• inapplication.properties setspring.jpa.properties.hibernate.jdbc.batch_size
• inapplication.properties setspring.jpa.properties.hibernate.generate_statistics (just to check that batching is working)
• inapplication.properties set JDBC URL withrewriteBatchedStatements=true (optimization for MySQL)
• inapplication.properties set JDBC URL withcachePrepStmts=true (enable caching and is useful if you decide to setprepStmtCacheSize,prepStmtCacheSqlLimit, etc as well; without this setting the cache is disabled)
• inapplication.properties set JDBC URL withuseServerPrepStmts=true (this way you switch to server-side prepared statements (may lead to signnificant performance boost))
• in case of using a parent-child relationship with cascade persist (e.g. one-to-many, many-to-many) then consider to set upspring.jpa.properties.hibernate.order_inserts=true to optimize the batching by ordering inserts
• in entity, use theassigned generator since MySQLIDENTITY will cause insert batching to be disabled
• in your DAO layer, flush and clear the Persistence Context from time to time (e.g. for each batch); this way you avoid to "overwhelm" the Persistence Context
• if is not needed, then ensure that Second Level Cache is disabled viaspring.jpa.properties.hibernate.cache.use_second_level_cache=false

Output example:

6. How To Batch Inserts Via JpaContext/EntityManager In MySQL

Description: Batch inserts viaJpaContext/EntityManager in MySQL.

Key points:

• inapplication.properties setspring.jpa.properties.hibernate.jdbc.batch_size
• inapplication.properties setspring.jpa.properties.hibernate.generate_statistics (just to check that batching is working)
• inapplication.properties set JDBC URL withrewriteBatchedStatements=true (optimization for MySQL)
• inapplication.properties set JDBC URL withcachePrepStmts=true (enable caching and is useful if you decide to setprepStmtCacheSize,prepStmtCacheSqlLimit, etc as well; without this setting the cache is disabled)
• inapplication.properties set JDBC URL withuseServerPrepStmts=true (this way you switch to server-side prepared statements (may lead to signnificant performance boost))
• in case of using a parent-child relationship with cascade persist (e.g. one-to-many, many-to-many) then consider to set upspring.jpa.properties.hibernate.order_inserts=true to optimize the batching by ordering inserts
• in entity, use theassigned generator since MySQLIDENTITY will cause insert batching to be disabled
• theEntityManager is obtain per entity type via,JpaContext#getEntityManagerByManagedType(Class<?> entity)
• in DAO, flush and clear the Persistence Context from time to time; this way you avoid to "overwhelm" the Persistence Context
• if is not needed, then ensure that Second Level Cache is disabled viaspring.jpa.properties.hibernate.cache.use_second_level_cache=false

Output example:

7. How To Exploit Session-Level Batching (Hibernate 5.2 Or Higher) In MySQL

Description: Batch inserts via Hibernate session-level batching (Hibernate 5.2 or higher) in MySQL.

Key points:

• inapplication.properties setspring.jpa.properties.hibernate.generate_statistics (just to check that batching is working)
• inapplication.properties set JDBC URL withrewriteBatchedStatements=true (optimization for MySQL)
• inapplication.properties set JDBC URL withcachePrepStmts=true (enable caching and is useful if you decide to setprepStmtCacheSize,prepStmtCacheSqlLimit, etc as well; without this setting the cache is disabled)
• inapplication.properties set JDBC URL withuseServerPrepStmts=true (this way you switch to server-side prepared statements (may lead to signnificant performance boost))
• in case of using a parent-child relationship with cascade persist (e.g. one-to-many, many-to-many) then consider to set upspring.jpa.properties.hibernate.order_inserts=true to optimize the batching by ordering inserts
• in entity, use theassigned generator since MySQLIDENTITY will cause insert batching to be disabled
• the HibernateSession is obtained by un-wrapping it viaEntityManager#unwrap(Session.class)
• the batching size is set viaSession#setJdbcBatchSize(Integer size) and get viaSession#getJdbcBatchSize()
• in DAO, flush and clear the Persistence Context from time to time; this way you avoid to "overwhelm" the Persistence Context
• if is not needed, then ensure that Second Level Cache is disabled viaspring.jpa.properties.hibernate.cache.use_second_level_cache=false

Output example:

8. Direct Fetching Via Spring DatafindById(), JPAEntityManager And HibernateSession

Description: Direct fetching via Spring Data,EntityManager and HibernateSession examples.

Key points:

• direct fetching via Spring Data usesfindById()
• direct fetching via JPAEntityManager usesfind()
• direct fetching via HibernateSession usesget()

9. DTO Via Spring Data Projections

Note: You may also like to read the recipe,"How To Enrich DTOs With Virtual Properties Via Spring Projections"

Description: Fetch only the needed data from the database via Spring Data Projections (DTO).

Key points:

• write an interface (projection) containing getters only for the columns that should be fetched from the database
• write the proper query returning aList<projection>
• if it is applicable, limit the number of returned rows (e.g., viaLIMIT)
• in this example, we can use query builder mechanism built into Spring Data repository infrastructure

Note: Using projections is not limited to use query builder mechanism built into Spring Data repository infrastructure. We can fetch projections via JPQL or native queries as well. For example, in thisapplication we use a JPQL.

Output example (select first 2 rows; select only "name" and "age"):

10. How To Use Hibernate Attribute Lazy Loading

Description: By default, the attributes of an entity are loaded eagerly (all at once). But, we can load themlazy as well. This is useful for column types that store large amounts of data:CLOB,BLOB,VARBINARY, etc ordetails that should be loaded on demand. In this application, we have an entity namedAuthor. Its properties are:id,name,genre,avatar andage. And, we want to load theavatar lazy. So, theavatar should be loaded on demand.

Key points:

• inpom.xml, activate Hibernatebytecode enhancement (e.g. use Mavenbytecode enhancement plugin)
• in entity, annotate the attributes that should be loaded lazy with@Basic(fetch = FetchType.LAZY)
• inapplication.properties, disable Open Session in View

Check as well:
-Default Values For Lazy Loaded Attributes
-Attribute Lazy Loading And Jackson Serialization

11. How To Populate a Child-Side Parent Association via Proxy

Description: A Hibernate proxy can be useful when a child entity can be persisted with a reference to its parent (@ManyToOne or@OneToOne association). In such cases, fetching the parent entity from the database (execute theSELECT statement) is a performance penalty and a pointless action, because Hibernate can set the underlying foreign key value for an uninitialized proxy.

Key points:

• rely onEntityManager#getReference()
• in Spring, useJpaRepository#getOne() -> used in this example
• in Hibernate, useload()
• assume two entities,Author andBook, involved in a unidirectional@ManyToOne association (Author is the parent-side)
• we fetch the author via a proxy (this will not trigger aSELECT), we create a new book, we set the proxy as the author for this book and we save the book (this will trigger anINSERT in thebook table)

Output example:

• the console output will reveal that only anINSERT is triggered, and noSELECT

12. How To Quickly Reproduce The N+1 Performance Issue

Description: The N+1 is an issue of lazy fetching (but, eager is not exempt). This application reproduce the N+1 behavior.

Key points:

• define two entities,Author andBook in a lazy bidirectional@OneToMany association
• fetch allBook lazy, so withoutAuthor (results in 1 query)
• loop the fetchedBook collection and for each entry fetch the correspondingAuthor (results N queries)
• or, fetch allAuthor lazy, so withoutBook (results in 1 query)
• loop the fetchedAuthor collection and for each entry fetch the correspondingBook (results N queries)

Output example:

13. OptimizeSELECT DISTINCT Via HibernateHINT_PASS_DISTINCT_THROUGH Hint

Description: Starting with Hibernate 5.2.2, we can optimize JPQL (HQL) query entites of typeSELECT DISTINCT viaHINT_PASS_DISTINCT_THROUGH hint. Keep in mind that this hint is useful only for JPQL (HQL) JOIN FETCH-ing queries. Is not useful for scalar queries (e.g.,List<Integer>), DTO orHHH-13280. In such cases, theDISTINCT JPQL keyword is needed to be passed to the underlying SQL query. This will instruct the database to remove duplicates from the result set.

Key points:

• use@QueryHints(value = @QueryHint(name = HINT_PASS_DISTINCT_THROUGH, value = "false"))

Output example:

14. How To Enable Dirty Tracking In A Spring Boot Application

Note: The HibernateDirty Checking mechanism is responsible to identify the entitites modifications at flush-time and to trigger the correspondingUPDATE statements in our behalf.

Description: Prior to Hibernate version 5, theDirty Checking mechanism relies on Java Reflection API for checking every property of every managed entity. Starting with Hibernate version 5, theDirty Checking mechanism can rely on theDirty Tracking mechanism (which is the capability of an entity to track its own attributes changes) which requires HibernateBytecode Enhancement to be present in the application. TheDirty Tracking mechanism sustain a better performance, especially when you have a relatively large number of entitites.

ForDirty Tracking, duringBytecode Enhancement process, the entity classes bytecode is instrumented by Hibernate by adding atracker,$$_hibernate_tracker. At flush time, Hibernate will use thistracker to discover the entities changes (each entitytracker will report the changes). This is better than checking every property of every managed entity.

Commonly (by default), the instrumentation takes place at build-time, but it can be configured to take place at runtime or deploy-time as well. It is preferable to take place at build-time for avoiding an overhead in the runtime.

AddingBytecode Enhancement and enablingDirty Tracking can be done via a plugin added via Maven or Gradle (Ant can be used as well). We use Maven, therefore we add it inpom.xml.

Key points:

• Hibernate come withBytecode Enhancement plugins for Maven, Gradle (Ant can be used as well)
• for Maven, add theBytecode Enhancement plugin in thepom.xml file

Output example:

TheBytecode Enhancement effect can be seen onAuthor.classhere. Notice how the bytecode was instrumented with$$_hibernate_tracker.

15. Use Java 8Optional In Entities And Queries

Description: This application is an example of how is correct to use the Java 8Optional in entities and queries.

Key points:

• use the Spring Data built-in query-methods that returnOptional (e.g.,findById())
• write your own queries that returnOptional
• useOptional in entities getters
• in order to run different scenarios check the file,data-mysql.sql

16. The Best Way To Map The@OneToMany Bidirectional Association

Description: This application is a proof of concept of how is correct to implement the bidirectional@OneToMany association from the performance perspective.

Key points:

• always cascade from parent to child
• usemappedBy on the parent
• useorphanRemoval on parent in order to remove children without references
• use helper methods on parent to keep both sides of the association in sync
• use lazy fetching on both side of the association
• as entities identifiers, use assigned identifiers (business key, natural key (@NaturalId)) and/or database-generated identifiers and override (on child-side) properly theequals() andhashCode() methods ashere
• iftoString() need to be overridden, then pay attention to involve only the basic attributes fetched when the entity is loaded from the database

Note: Pay attention to remove operations, especially to removing child entities. TheCascadeType.REMOVE andorphanRemoval=true may produce too many queries. In such scenarios, relying onbulk operations is most of the time the best way to go for deletions.

17. Query Fetching

Description: This application is an example of how to write a query viaJpaRepository,EntityManager andSession.

Key points:

• forJpaRepository use@Query or Spring Data Query Creation
• forEntityManager andSession use thecreateQuery() method

18. Why And How To Avoid TheAUTO Generator Type In Hibernate 5 And MySQL

Description: In MySQL & Hibernate 5, theGenerationType.AUTO generator type will result in using theTABLE generator. This adds a significant performance penalty. Turning this behavior toIDENTITY generator can be obtained by usingGenerationType.IDENTITY or thenative generator.

Key points:

• useGenerationType.IDENTITY instead ofGenerationType.AUTO
• use thenative generator - exemplified in this application

Output example:

19. How To Avoid The Redundant save() Call

Description: This application is an example when callingsave() for an entity is redundant (not necessary).

Key points:

• at flush time, Hibernate relies ondirty checking mechanism to determine the potential modifications in entities
• for each modification, Hibernate automatically triggers the correspondingUPDATE statement without the need to explicitly call thesave() method
• behind the scene, this redundancy (callingsave() when is not necessarily) doesn't affect the number of triggered queries, but it implies a performance penalty in the underlying Hibernate processes

20. Why To Avoid PostgreSQL (BIG)SERIAL In Batching Inserts Via Hibernate

Description: In PostgreSQL, usingGenerationType.IDENTITY will disable insert batching. The(BIG)SERIAL is acting "almost" like MySQL,AUTO_INCREMENT. In this application, we use theGenerationType.SEQUENCE which permits insert batching, and we optimize it via thehi/lo optimization algorithm.

Key points:

• useGenerationType.SEQUENCE instead ofGenerationType.IDENTITY
• rely on thehi/lo algorithm to fetch ahi value in a database roundtrip (thehi value is useful for generating a certain/given number of identifiers in-memory; until you haven't exhausted all in-memory identifiers there is no need to fetch anotherhi)
• you can go even further and use the Hibernatepooled andpooled-lo identifier generators (these are optimizations ofhi/lo that allows external services to use the database without causing duplication keys errors)
• optimize batching viaspring.datasource.hikari.data-source-properties.reWriteBatchedInserts=true

Output example:

21. JPA Inheritance -SINGLE_TABLE

Description: This application is a sample of using JPA Single Table inheritance strategy (SINGLE_TABLE).

Key points:

• this is the default inheritance strategy (@Inheritance(strategy=InheritanceType.SINGLE_TABLE))
• all the classes in an inheritance hierarchy are represented via a single table in the database
• subclasses attributes non-nullability is ensured via@NotNull and MySQL triggers
• the default discriminator column memory footprint was optimized by declaring it of typeTINYINT

Output example (below is a single table obtained from 3 entities):

22. Count and Assert SQL Statements

Description: This application is a sample of counting and asserting SQL statements triggered "behind the scene". Is very useful to count the SQL statements in order to ensure that your code is not generating more SQL statements that you may think (e.g., N+1 can be easily detected by asserting the number of expected statements).

Key points:

• for Maven, inpom.xml, add dependencies for DataSource-Proxy library and Vlad Mihalcea's db-util library
• create theProxyDataSourceBuilder withcountQuery()
• reset the counter viaSQLStatementCountValidator.reset()
• assertINSERT,UPDATE,DELETE andSELECT viaassertInsert/Update/Delete/Select/Count(long expectedNumberOfSql)

Output example (when the number of expected SQLs is not equal with the reality an exception is thrown):

23. How To Setup JPA Callbacks

Description: This application is a sample of setting the JPA callbacks (Pre/PostPersist,Pre/PostUpdate,Pre/PostRemove andPostLoad).

Key points:

• in entity, write callback methods and use the proper annotations
• callback methods annotated on the bean class must returnvoid and take no arguments

Output example:

24. How To Use@MapsId For Sharing Identifier In@OneToOne Relationship

Description: Instead ofregular unidirectional/bidirectional@OneToOne better rely on an unidirectional@OneToOne and@MapsId. This application is a proof of concept.

Key points:

• use@MapsId on child side
• use@JoinColumn to customize the name of the primary key column
• mainly, for@OneToOne associations,@MapsId will share the primary key with the parent table (id property acts as both primary key and foreign key)

Note:

• @MapsId can be used for@ManyToOne as well

25. How To Fetch DTO ViaSqlResultSetMapping AndEntityManager

Description: Fetching more data than needed is prone to performance penalities. Using DTO allows us to extract only the needed data. In this application we rely onSqlResultSetMapping andEntityManager.

Key points:

• useSqlResultSetMapping andEntityManager
• for using Spring Data Projections check thisitem

26. How To Fetch DTO ViaSqlResultSetMapping AndNamedNativeQuery

Note: If you want to rely on the{EntityName}.{RepositoryMethodName} naming convention for simply creating in the repository interface methods with the same name as of native named query then skip this application andcheck this one.

Description: Fetching more data than needed is prone to performance penalities. Using DTO allows us to extract only the needed data. In this application we rely onSqlResultSetMapping,NamedNativeQuery.

Key points:

• useSqlResultSetMapping,NamedNativeQuery
• for using Spring Data Projections check thisitem

27. How To Fetch DTO Viajavax.persistence.Tuple And Native SQL

Description: Fetching more data than needed is prone to performance penalities. Using DTO allows us to extract only the needed data. In this application we rely onjavax.persistence.Tuple and native SQL.

Key points:

• usejava.persistence.Tuple in a Spring repository and mark the query asnativeQuery = true
• for using Spring Data Projections check thisitem

28. How To Fetch DTO viajavax.persistence.Tuple and JPQL

Description: Fetching more data than needed is prone to performance penalities. Using DTO allows us to extract only the needed data. In this application we rely onjavax.persistence.Tuple and JPQL.

Key points:

• usejava.persistence.Tuple in a Spring repository
• for using Spring Data Projections check thisitem

29. How To Fetch DTO Via Constructor Expression and JPQL

Description: Fetching more data than needed is prone to performance penalities. Using DTO allows us to extract only the needed data. In this application we rely on Constructor Expression and JPQL.

Key points:

• write a proper constructor in the DTO class
• use a query asSELECT new com.bookstore.dto.AuthorDto(a.name, a.age) FROM Author a
• for using Spring Data Projections check thisitem

See also:
How To Fetch DTO Via Constructor And Spring Data Query Builder Mechanism

30. How To Fetch DTO ViaResultTransformer And Native SQL

Description: Fetching more data than needed is prone to performance penalties. Using DTO allows us to extract only the needed data. In this application we rely on Hibernate,ResultTransformer and native SQL.

Key points:

• useAliasToBeanConstructorResultTransformer for DTO without setters, but with constructor
• useTransformers.aliasToBean() for DTO with setters
• useEntityManager.createNativeQuery() andunwrap(org.hibernate.query.NativeQuery.class)
• starting with Hibernate 5.2,ResultTransformer is deprecated, but until a replacement will be available (probably in Hibernate 6.0) it can be used (read further)
• for using Spring Data Projections check thisrecipe

31. How To Fetch DTO ViaResultTransformer and JPQL

Description: Fetching more data than needed is prone to performance penalties. Using DTO allows us to extract only the needed data. In this application we rely on Hibernate,ResultTransformer and JPQL.

Key points:

• useAliasToBeanConstructorResultTransformer for DTO without setters, with constructor
• useTransformers.aliasToBean() for DTO with setters
• useEntityManager.createQuery() andunwrap(org.hibernate.query.Query.class)
• starting with Hibernate 5.2,ResultTransformer is deprecated, but until a replacement will be available (in Hibernate 6.0) it can be used (read further)
• for using Spring Data Projections check thisitem

32. How To Fetch DTO Via Blaze-Persistence Entity Views

Description: Fetching more data than needed is prone to performance penalities. Using DTO allows us to extract only the needed data. In this application we rely onBlaze-Persistence entity views.

Key points:

• for Maven, add inpom.xml the dependencies specific to Blaze-Persistence
• configure Blaze-Persistence viaCriteriaBuilderFactory andEntityViewManager
• write anentity view via an interface in Blaze-Persistence fashion
• write a Spring-centric repository by extendingEntityViewRepository
• call method of this repository such as,findAll(),findOne(), etc
• for using Spring Data Projections check thisitem

33. How Regular@ElementCollection (Without@OrderColumn) Works

Description: This application reveals the possible performance penalties of using@ElementCollection. In this case, without@OrderColumn. As you can see in the next item (34) adding@OrderColumn can mitigate some performance penalties.

Key points:

• an@ElementCollection doesn't have a primary key
• an@ElementCollection is mapped in a separate table
• avoid@ElementCollection when you have a lot of inserts/deletes on this collection; inserts/deletes will cause Hibernate to delete all the existing table rows, process the collection in-memory, and re-insert the remaining table rows to mirror the collection from memory
• the more entries we have in this collection the greater the performance penalty will be

Output example:

34. How@ElementCollection With@OrderColumn Works

Description: This application reveals the performance penalties of using@ElementCollection. In this case, with@OrderColumn. But, as you can see in this application (in comparison with item 33), by adding@OrderColumn can mitigate some performance penalties when operations takes place near the collection tail (e.g., add/remove at/from the end of the collection). Mainly, all elements situated before the adding/removing entry are left untouched, so the performance penalty can be ignored if we affect rows close to the collection tail.

Key points:

• an@ElementCollection doesn't have a primary key
• an@ElementCollection is mapped in a separate table
• prefer@ElementCollection with@OrderColumn when you have a lot of inserts and deletes near the collection tail
• the more elements are inserted/removed from the beginning of the collection the greater the performance penalty will be

Output example:

35. How To Avoid Lazy Initialization Issues Caused By Disabling Open Session In View Via Explicit (Default) Values

Note: Before reading this item try to see ifHibernate5Module is not what you are looking for.

Description: The Open-Session in View anti-pattern is activated by default in SpringBoot. Now, imagine a lazy association (e.g.,@OneToMany) between two entities,Author andBook (an author has associated more books). Next, a REST controller endpoint fetches anAuthor without the associatedBook. But, the View (more precisely, Jackson), forces the lazy loading of the associatedBook as well. Since OSIV will supply the already openedSession, the proxies initializations take place successfully. The solution to avoid this performance penalty starts by disabling the OSIV. Further, explicitly initialize the un-fetched lazy associations. This way, the View will not force lazy loading.

Key points:

• disable OSIV by adding inapplication.properties this setting:spring.jpa.open-in-view=false
• fetch anAuthor entity and initialize its associatedBook explicitly with (default) values (e.g.,null)
• set@JsonInclude(Include.NON_EMPTY) on this entity-level to avoid renderingnull or what is considered empty in the resulted JSON

NOTE: If OSIV is enabled, the developer can still initialize the un-fetched lazy associations manually as long as he does this outside of a transaction to avoid flushing. But, why is this working? Since theSession is open, why the manually initialization of the associations of a managed entity doesn't trigger the flush? The answer can be found in the documentation ofOpenSessionInViewFilter which specifies that:This filter will by default not flush the HibernateSession, with the flush mode set toFlushMode.NEVER. It assumes to be used in combination with service layer transactions that care for the flushing: The active transaction manager will temporarily change the flush mode toFlushMode.AUTO during a read-write transaction, with the flush mode reset toFlushMode.NEVER at the end of each transaction. If you intend to use this filter without transactions, consider changing the default flush mode (through the "flushMode" property).

36. How To Use Spring Projections(DTO) And Inner Joins

Description: This application is a proof of concept for using Spring Projections(DTO) and inner joins written via JPQL and native SQL (for MySQL).

Key points:

• define two entities (e.g.,Author andBook in a (lazy) bidirectional@OneToMany association)
• populate the database with some test data (e.g., check the fileresources/data-mysql.sql)
• write interfaces (Spring projections) that contains getters for the columns that should be fetched from the database (e.g., checkAuthorNameBookTitle.java)
• write inner joins queries using JPQL/SQL

37. How To Use Spring Projections(DTO) And Left Joins

Description: This application is a proof of concept for using Spring Projections(DTO) and left joins written via JPQL and native SQL (for MySQL).

Key points:

• define two entities (e.g.,Author andBook in a (lazy) bidirectional@OneToMany association)
• populate the database with some test data (e.g., check the fileresources/data-mysql.sql)
• write interfaces (Spring projections) that contains getters for the columns that should be fetched from the database (e.g., checkAuthorNameBookTitle.java)
• write left joins queries using JPQL/SQL

38. How To Use Spring Projections(DTO) And Right Joins

Description: This application is a proof of concept for using Spring Projections(DTO) and right joins written via JPQL and native SQL (for MySQL).

Key points:

• define two entities (e.g.,Author andBook in a (lazy) bidirectional@OneToMany association)
• populate the database with some test data (e.g., check the fileresources/data-mysql.sql)
• write interfaces (Spring projections) that contains getters for the columns that should be fetched from the database (e.g., checkAuthorNameBookTitle.java)
• write right joins queries using JPQL/SQL

39. How To Use Spring Projections(DTO) And Inclusive Full Joins (PostgreSQL)

Description: This application is a proof of concept for using Spring Projections(DTO) and inclusive full joins written via JPQL and native SQL (for PostgreSQL).

Key points:

• define two entities (e.g.,Author andBook in a (lazy) bidirectional@OneToMany association)
• populate the database with some test data (e.g., check the fileresources/data-mysql.sql)
• write interfaces (Spring projections) that contains getters for the columns that should be fetched from the database (e.g., checkAuthorNameBookTitle.java)
• write inclusive full joins queries using JPQL/SQL

40. How To Use Spring Projections(DTO) And Exclusive Left Joins

Description: This application is a proof of concept for using Spring Projections(DTO) and exclusive left joins written via JPQL and native SQL (for MySQL).

Key points:

• define two entities (e.g.,Author andBook in a (lazy) bidirectional@OneToMany association)
• populate the database with some test data (e.g., check the fileresources/data-mysql.sql)
• write interfaces (projections) that contains getters for the columns that should be fetched from the database (e.g., checkAuthorNameBookTitle.java)
• write exclusive left joins queries using JPQL/SQL

41. How To Use Spring Projections(DTO) And Exclusive Right Joins

Description: This application is a proof of concept for using Spring Projections(DTO) and exclusive right joins written via JPQL and native SQL (for MySQL).

Key points:

• define two entities (e.g.,Author andBook in a (lazy) bidirectional@OneToMany association)
• populate the database with some test data (e.g., check the fileresources/data-mysql.sql)
• write interfaces (Spring projections) that contains getters for the columns that should be fetched from the database (e.g., checkAuthorNameBookTitle.java)
• write exclusive right joins queries using JPQL/SQL

42. How To Use Spring Projections(DTO) And Exclusive Full Joins (PostgreSQL)

Description: This application is a proof of concept for using Spring Projections(DTO) and exclusive full joins written via JPQL and native SQL (for PostgreSQL).

Key points:

• define two entities (e.g.,Author andBook in a (lazy) bidirectional@OneToMany association)
• populate the database with some test data (e.g., check the fileresources/data-mysql.sql)
• write interfaces (Spring projections) that contains getters for the columns that should be fetched from the database (e.g., checkAuthorNameBookTitle.java)
• write exclusive full joins queries using JPQL/SQL

43. Why You Should Avoid Time-Consuming Tasks In Spring Boot Post-Commit Hooks

Description: This application is a proof of concept for using Spring post-commit hooks and how they may affect the persistence layer performance.

Key points:

• avoid time-consuming tasks in Spring post-commit hooks since the database connection will remain open until this code finshes

44. How To Exploit Spring Projections(DTO) And Join Unrelated Entities In Hibernate 5.1+

Description: This application is a proof of concept for using Spring Projections (DTO) and join unrelated entities. Hibernate 5.1 introduced explicit joins on unrelated entities and the syntax and behaviour are similar to SQLJOIN statements.

Key points:

• define serveral entities (e.g.,Author andBook unrelated entities)
• populate the database with some test data (e.g., check the fileresources/data-mysql.sql)
• write interfaces (Spring projections) that contains getters for the columns that should be fetched from the database (e.g.,BookstoreDto)
• write joins queries using JPQL/SQL (e.g., queries all authors names and book titles of the given price)

45. Why To Avoid Lombok@EqualsAndHashCode And@Data In Entities And How To Overrideequals() AndhashCode()

Description: Entities should implementequals() andhashCode() ashere. The main idea is that Hibernate requires that an entity is equal to itself across all its state transitions (transient,attached,detached andremoved). Using Lombok@EqualsAndHashCode (or@Data) will not respect this requirment.

Key points:
AVOID THESE APPROACHES

• Using Lombok default behavior of@EqualsAndHashCode(entity:LombokDefaultBook, test:LombokDefaultEqualsAndHashCodeTest)
• Using Lombok@EqualsAndHashCode with primary key only(entity:LombokIdBook, test:LombokEqualsAndHashCodeWithIdOnlyTest)
• Rely on defaultequals() andhashCode()(entity:DefaultBook, test:DefaultEqualsAndHashCodeTest)
• Rely on defaultequals() andhashCode() containing only the database-generated identifier(entity:IdBook, test:IdEqualsAndHashCodeTest)

PREFER THESE APPROACHES

• Rely on business key (entity:BusinessKeyBook, test:BusinessKeyEqualsAndHashCodeTest)
• Rely on@NaturalId (entity:NaturalIdBook, test:NaturalIdEqualsAndHashCodeTest)
• Rely on manually assigned identifiers (entity:IdManBook, test:IdManEqualsAndHashCodeTest)
• Rely on database-generated identifiers (entity:IdGenBook, test:IdGenEqualsAndHashCodeTest)

46. How To AvoidLazyInitializationException ViaJOIN FETCH

See also:

• LEFT JOIN FETCH
• JOIN VS. JOIN FETCH

Description: Typically, when we get aLazyInitializationException we tend to modify the association fetching type fromLAZY toEAGER. That is very bad! This is acode smell. Best way to avoid this exception is to rely onJOIN FETCH (if you plan to modify the fetched entities) orJOIN + DTO (if the fetched data is only read).JOIN FETCH allows associations to be initialized along with their parent objects using a singleSELECT. This is particularly useful for fetching associated collections.

This application is aJOIN FETCH example for avoidingLazyInitializationException.

Key points:

• define two related entities (e.g.,Author andBook in a@OneToMany lazy-bidirectional association)
• write a JPQLJOIN FETCH to fetch an author including his books
• write a JPQLJOIN FETCH (orJOIN) to fetch a book including its author

Output example:

47. How To Merge Entity Collections

Description: This is a Spring Boot example based on the followingarticle. Is a functional implementation of the Vlad's example. It is highly recommended to read that article.

Key points:

• remove the existing database rows that are no longer found in the incoming collection
• update the existing database rows which can be found in the incoming collection
• add the rows found in the incoming collection, which cannot be found in the current database snapshot

48. How To Delay Connection Acquisition As Needed (Hibernate 5.2.10)

Description: This is a Spring Boot example that exploits Hibernate 5.2.10 capability of delaying the connection acquisition as needed. By default, inresource-local mode, a database connection is aquried immediately after calling a method annotated with@Transactional. If this method contains some time-consuming tasks before the first SQL statement then the connection is hold open for nothing. But, Hibernate 5.2.10 allows us to delay the connection acquisition as needed. This example rely on HikariCP as the default connection pool for Spring Boot.

Key points:

• setspring.datasource.hikari.auto-commit=false in application.properties
• setspring.jpa.properties.hibernate.connection.provider_disables_autocommit=true inapplication.properties

Output example:

49. How To Generate Sequences Of Identifiers Via Hibernatehi/lo Algorithm

Note: If systems external to your application need to insert rows in your tables then don't rely onhi/lo algorithm since, in such cases, it may cause errors resulted from generating duplicated identifiers. Rely onpooled orpooled-lo algorithms (optimizations ofhi/lo).

Description: This is a Spring Boot example of using thehi/lo algorithm for generating 1000 identifiers in 10 database roundtrips for batching 1000 inserts in batches of 30.

Key points:

• use theSEQUENCE generator type (e.g., in PostgreSQL)
• configure thehi/lo algorithm as inAuthor.java entity

Output example:

50. The Best Way To Implement A Bidirectional@ManyToMany Association

Description: This application is a proof of concept of how it is correct to implement the bidirectional@ManyToMany association from the performance perspective.

Key points:

• choose an owning and amappedBy side
• materialize the relationships collections viaSet notList
• use helper methods on the owner of the relationship to keep both sides of the association in sync
• on the owner of the relationship useCascadeType.PERSIST andCascadeType.MERGE, but avoidCascadeType.REMOVE/ALL
• on the owner of the relationship set up join table
• @ManyToMany is lazy by default; keep it this way!
• as entities identifiers, use assigned identifiers (business key, natural key (@NaturalId)) and/or database-generated identifiers and override (on both sides) properly theequals() andhashCode() methods ashere
• iftoString() need to be overridden, then pay attention to involve only for the basic attributes fetched when the entity is loaded from the database

51. PreferSet Instead ofList in@ManyToMany Associations

Description: This is a Spring Boot example of removing rows in case of a bidirectional@ManyToMany usingList, respectivelySet. The conclusion is thatSet is much better! This applies to unidirectional as well!

Key points:

• usingSet is much more efficent thanList

Output example:

52. How To View Query Details Vialog4jdbc

Description: View the query details vialog4jdbc.

Key points:

• for Maven, inpom.xml, addlog4jdbc dependency

Output sample:

53. How To View Binding Params Via TRACE

Description: View the prepared statement binding/extracted parameters viaTRACE.

Key points:

• inapplication.properties add:logging.level.org.hibernate.type.descriptor.sql=TRACE
• or, even better (for filtering SQLs capabilities), in a Logback specific configuration file add the proper logger

Output sample:

54. How To Storejava.time.YearMonth AsInteger OrDate Via Hibernate Types Library

Description:Hibernate Types is a set of extra types not supported by default in Hibernate Core. One of these types isjava.time.YearMonth. This is a Spring Boot application that uses Hibernate Type to store thisYearMonth in a MySQL database as integer or date.

Key points:

• for Maven, add Hibernate Types as a dependency inpom.xml
• in entity use@TypeDef to maptypeClass todefaultForType

Output example:

55. How To Execute SQL Functions In JPQL Query

Note: Using SQL functions in theWHERE part (not in theSELECT part) of query in JPA 2.1 can be done viafunction() ashere.

Description: Trying to use SQL functions (standard or defined) in JPQL queries may result in exceptions if Hibernate will not recognize them and cannot parse the JPQL query. For example, the MySQL,concat_ws function is not recognized by Hibernate. This application is a Spring Boot application based on Hibernate 5.3, that registers theconcat_ws function viaMetadataBuilderContributor and inform Hibernate about it via,metadata_builder_contributor property. This example uses@Query andEntityManager as well, so you can see two use cases.

Key points:

• use Hibernate 5.3 (or, to be precisely, 5.2.18) (e.g., use Spring Boot 2.1.0.RELEASE)
• implementMetadataBuilderContributor and register theconcat_ws MySQL function
• inapplication.properties, setspring.jpa.properties.hibernate.metadata_builder_contributor to point out Hibernate toMetadataBuilderContributor implementation

Output example:

56. Log Slow Queries Via DataSource-Proxy

Description: This application is a sample of logging only slow queries viaDataSource-Proxy. A slow query is a query that has an execution time bigger than a specificed threshold in milliseconds.

Key points:

• for Maven, add inpom.xml the DataSource-Proxy dependency
• create an bean post processor to intercept theDataSource bean
• wrap theDataSource bean viaProxyFactory and an implementation ofMethodInterceptor
• choose a threshold in milliseconds
• define a listener and overrideafterQuery()

Output example:

57. Offset Pagination - TriggerSELECT COUNT Subquery And ReturnPage<dto>

Description: This application fetches data asPage<dto> via Spring Boot offset pagination. Most of the time, the data that should be paginated isread-only data. Fetching the data into entities should be done only if we plan to modify that data, therefore, fetchingread only data asPage<entity> is not preferable since it may end up in a significant performance penalty. TheSELECT COUNT triggered for counting the total number of records is a subquery of the mainSELECT. Therefore, there will be a single database roundtrip instead of two (typically, there is one query needed for fetching the data and one for counting the total number of records).

Key points:

• create a Spring projection (DTO) to contains getters only for the columns that should be fetched
• write a repository that extendsPagingAndSortingRepository
• fetch data via a JPQL or native query (that includes counting) into aList<dto>
• use the fetchedList<dto> and the properPageable to create aPage<dto>

58. Offset Pagination - TriggerSELECT COUNT Subquery And ReturnList<dto>

Description: This application fetches data asList<dto> via Spring Boot offset pagination. Most of the time, the data that should be paginated isread-only data. Fetching the data into entities should be done only if we plan to modify that data, therefore, fetchingread only data asList<entity> is not preferable since it may end up in a significant performance penalty. TheSELECT COUNT triggered for counting the total number of records is a subquery of the mainSELECT. Therefore, there will be a single database roundtrip instead of two (typically, there is one query needed for fetching the data and one for counting the total number of records).

Key points:

• create a Spring projection (DTO) to contains getters only for the columns that should be fetched
• write a repository that extendsPagingAndSortingRepository
• fetch data via a JPQL or native query (that includes counting) into aList<dto>

59. How To Customize HikariCP Settings Via Properties

If you use thespring-boot-starter-jdbc orspring-boot-starter-data-jpa "starters", you automatically get a dependency to HikariCP

Note: The best way to tune the connection pool parameters consist in usingFlexy Pool by Vlad Mihalcea. ViaFlexy Pool you can find the optim settings that sustain high-performance of your connection pool.

Description: This is a kickoff application that set up HikariCP viaapplication.properties only. ThejdbcUrl is set up for a MySQL database. For testing purposes, the application uses anExecutorServicefor simulating concurrent users. Check the HickariCP report revealing the connection pool status.

Key points:

• inapplication.properties, rely onspring.datasource.hikari.* to configure HikariCP

Output sample:

60. How To Customize HikariCP Settings Via Properties AndDataSourceBuilder

If you use thespring-boot-starter-jdbc orspring-boot-starter-data-jpa "starters", you automatically get a dependency to HikariCP

Note: The best way to tune the connection pool parameters consist in usingFlexy Pool by Vlad Mihalcea. ViaFlexy Pool you can find the optim settings that sustain high-performance of your connection pool.

Description: This is a kickoff application that set up HikariCP viaDataSourceBuilder. ThejdbcUrl is set up for a MySQL database. For testing purposes, the application uses anExecutorService for simulating concurrent users. Check the HickariCP report revealing the connection pool status.

Key points:

• inapplication.properties, configure HikariCP via a custom prefix, e.g.,app.datasource.*
• write a@Bean that returns theDataSource

Output sample:

61. Running a SpringBoot Application Under Payara Server Using a Payara Data Source (JDBC Resource and Connection Pool)

This application is detailed in thisDZone article.

62. How To Customize BoneCP Settings Via Properties AndDataSourceBuilder

Note: The best way to tune the connection pool parameters consist in usingFlexy Pool by Vlad Mihalcea. ViaFlexy Pool you can find the optim settings that sustain high-performance of your connection pool.

Description: This is a kickoff application that set up BoneCP viaDataSourceBuilder. ThejdbcUrl is set up for a MySQL database. For testing purposes, the application uses anExecutorService for simulating concurrent users.

Key points:

• inpom.xml add the BoneCP dependency
• inapplication.properties, configure BoneCP via a custom prefix, e.g.,app.datasource.*
• write a@Bean that returns theDataSource

Output sample:

63. How To Customize ViburDBCP Settings Via Properties AndDataSourceBuilder

Note: The best way to tune the connection pool parameters consist in usingFlexy Pool by Vlad Mihalcea. ViaFlexy Pool you can find the optim settings that sustain high-performance of your connection pool.

Description: This is a kickoff application that set up ViburDBCP viaDataSourceBuilder. ThejdbcUrl is set up for a MySQL database. For testing purposes, the application uses anExecutorService for simulating concurrent users.

Key points:

• inpom.xml add the ViburDBCP dependency
• inapplication.properties, configure ViburDBCP via a custom prefix, e.g.,app.datasource.*
• write a@Bean that returns theDataSource

Output sample:

64. How To Customize C3P0 Settings Via Properties AndDataSourceBuilder

Note: The best way to tune the connection pool parameters consist in usingFlexy Pool by Vlad Mihalcea. ViaFlexy Pool you can find the optim settings that sustain high-performance of your connection pool.

Description: This is a kickoff application that set up C3P0 viaDataSourceBuilder. ThejdbcUrl is set up for a MySQL database. For testing purposes, the application uses anExecutorService for simulating concurrent users.

Key points:

• inpom.xml add the C3P0 dependency
• inapplication.properties, configure C3P0 via a custom prefix, e.g.,app.datasource.*
• write a@Bean that returns theDataSource

Output sample:

65. How To Customize DBCP2 Settings Via Properties AndDataSourceBuilder

Note: The best way to tune the connection pool parameters consist in usingFlexy Pool by Vlad Mihalcea. ViaFlexy Pool you can find the optim settings that sustain high-performance of your connection pool.

Description: This is a kickoff application that set up DBCP2 viaDataSourceBuilder. ThejdbcUrl is set up for a MySQL database. For testing purposes, the application uses anExecutorService for simulating concurrent users.

Key points:

• inpom.xml add the DBCP2 dependency
• inapplication.properties, configure DBCP2 via a custom prefix, e.g.,app.datasource.*
• write a@Bean that returns theDataSource

66. How To Customize Tomcat Settings Via Properties AndDataSourceBuilder

Note: The best way to tune the connection pool parameters consist in usingFlexy Pool by Vlad Mihalcea. ViaFlexy Pool you can find the optim settings that sustain high-performance of your connection pool.

Description: This is a kickoff application that set up Tomcat viaDataSourceBuilder. ThejdbcUrl is set up for a MySQL database. For testing purposes, the application uses anExecutorService for simulating concurrent users.

Key points:

• inpom.xml add the Tomcat dependency
• inapplication.properties, configure Tomcat via a custom prefix, e.g.,app.datasource.*
• write a@Bean that returns theDataSource

Output sample:

67. How To Configure Two Data Sources With Two Connection Pools

Note: The best way to tune the connection pool parameters consist in usingFlexy Pool by Vlad Mihalcea. ViaFlexy Pool you can find the optim settings that sustain high-performance of your connection pool.

Description: This is a kickoff application that uses two data sources (two MySQL databases, one namedauthorsdb and one namedbooksdb) with two connection pools (each database uses its own HikariCP connection pool with different settings). Based on the above items is pretty easy to configure two connection pools from two different providers as well.

Key points:

• inapplication.properties, configure two HikariCP connection pools via a two custom prefixes, e.g.,app.datasource.ds1 andapp.datasource.ds2
• write a@Bean that returns the firstDataSource and mark it as@Primary
• write another@Bean that returns the secondDataSource
• configure twoEntityManagerFactory and point out the packages to scan for each of them
• put the domains and repositories for eachEntityManager in the right packages

Output sample:

68. How To Provide a Fluent API Via Setters For Building Entities

Note: If you want yo provide a Fluent API without altering setters then considerthis item.

Description: This is a sample application that alter the entities setters methods in order to empower a Fluent API.

Key points:

• in entitites, returnthis instead ofvoid in setters

Fluent API example:

69. How To Provide a Fluent API Via Additional Methods For Building Entities

Note: If you want yo provide a Fluent API by altering setters then considerthis item.

Description: This is a sample application that add in entities additional methods (e.g., forsetName, we addname) methods in order to empower a Fluent API.

Key points:

• in entities, add for each setter an additional method that returnthis instead ofvoid

Fluent API example:

70. How To ImplementSlice<T> findAll()

Most probably this is all you want:How To FetchSlice<entity>/Slice<dto> ViafetchAll/fetchAllDto

Some implementations ofSlice<T> findAll():

• This is a thin implementation based on a hard-coded SQL:"SELECT e FROM " + entityClass.getSimpleName() + " e;"
• This is just another minimalist implementation based onCriteriaBuilder instead of hard-coded SQL
• This is an implementation that allows us to provide aSort, so sorting results is possible
• This is an implementation that allows us to provide aSort and a Spring DataSpecification
• This is an implementation that allows us to provide aSort, aLockModeType, aQueryHints and a Spring DataSpecification
• This is an implementation that allows us to provide a Spring DataPageable and/orSpecification by extending theSimpleJpaRepository from Spring Data. Bascially, this implementation is the only one that returnsPage<T> instead ofSlice<T>, but it doesn't trigger the extraSELECT COUNT since it was eliminated by overriding thePage<T> readPage(...) method fromSimpleJpaRepository. The main drawback is that by returing aPage<T> you don't know if there is a next page or the current one is the last. Nevertheless, there are workarounds to have this as well. In this implementation you cannot setLockModeType or query hints.

Story: Spring Boot provides anoffset based built-in paging mechanism that returns aPage orSlice. Each of these APIs represents a page of data and some metadata. The main difference is thatPage contains the total number of records, whileSlice can only tell if there is another page available. ForPage, Spring Boot provides afindAll() method capable to take as arguments aPageable and/or aSpecification orExample. In order to create aPage that contains the total number of records, this method triggers anSELECT COUNT extra-query next to the query used to fetch the data of the current page. This can be a performance penalty since theSELECT COUNT query is triggered every time we request a page. In order to avoid this extra-query, Spring Boot provides a more relaxed API, theSlice API. UsingSlice instead ofPage removes the need of this extraSELECT COUNT query and returns the page (records) and some metadata without the total number of records. So, whileSlice doesn't know the total number of records, it still can tell if there is another page available after the current one or this is the last page. The problem is thatSlice work fine for queries containing the SQL,WHERE clause (including those that uses the query builder mechanism built into Spring Data), but itdoesn't work forfindAll(). This method will still return aPage instead ofSlice therefore theSELECT COUNT query is triggered forSlice<T> findAll(...);.

Description: This is a suite of samples applications that provides different versions of aSlice<T> findAll(...) method. We have from a minimalist implementation that relies on a hardcoded query as:"SELECT e FROM " + entityClass.getSimpleName() + " e"; (this recipe), to a custom implementation that supports sorting, specification, lock mode and query hints to an implementation that relies on extendingSimpleJpaRepository.

Key points:

• write anabstract class that expose theSlice<T> findAll(...) methods (SlicePagingRepositoryImplementation)
• implement thefindAll() methods to returnSlice<T> (orPage<T>, but without the total number of elements)
• return aSliceImpl (Slice<T>) or aPageImpl (Page<T>) without the total number of elements
• implement a newreadSlice() method or override theSimpleJpaRepository#readPage() page to avoidSELECT COUNT
• pass the entity class (e.g.,Author.class) to thisabstract class via a class repository (AuthorRepository)

71. Offset Pagination - TriggerCOUNT(*) OVER And ReturnList<dto>

Description: Typically, in offset pagination, there is one query needed for fetching the data and one for counting the total number of records. But, we can fetch this information in a single database rountrip via aSELECT COUNT subquery nested in the mainSELECT. Even better, for databases vendors that supportWindow Functions there is a solution relying onCOUNT(*) OVER() as in this application that uses this window function in a native query against MySQL 8. So, prefer this one instead ofSELECT COUNT subquery.

Key points:

• create a DTO projection that contains getters for the columns that should be fetched and an extra-column for mapping the return of theCOUNT(*) OVER() window function
• write a native query relying on this window function

Example:

72. How To Implement Keyset Pagination in Spring Boot

Description: When we rely on anoffset paging we have the performance penalty induced by throwing awayn records before reached the desiredoffset. Largern leads to a significant performance penalty. When we have a largen is better to rely onkeyset pagination which maintain a "constant" time for large datasets. In order to understand how badoffset can perform please check thisarticle:

Screenshot from that article (offset pagination):

Need to know if there are more records?
By its nature,keyset doesn't use aSELECT COUNT to fetch the number of total records. But, with a little tweak, we can easily say if there are more records, therefore to show a button of typeNext Page. Mainly, if you need such a thing then considerthis application whose climax is listed below:

public AuthorView fetchNextPage(long id, int limit) {
     List<Author> authors = authorRepository.fetchAll(id, limit + 1);

     if (authors.size() == (limit + 1)) {
          authors.remove(authors.size() - 1);
          return new AuthorView(authors, true);
     }

     return new AuthorView(authors, false);
}

Or, like this (rely onAuthor.toString() method):

public Map<List<Author>, Boolean> fetchNextPage(long id, int limit) {
     List<Author> authors = authorRepository.fetchAll(id, limit + 1);

     if(authors.size() == (limit + 1)) {
          authors.remove(authors.size() -1);
          return Collections.singletonMap(authors, true);
     }

     return Collections.singletonMap(authors, false);
}

APrevious Page button can be implemented easily based on the first record.

Key points:

• choose the column(s) to act as the latest visited record (e.g.,id)
• use the column(s) in theWHERE andORDER BY clauses of your SQL

73. How To Implement Offset Pagination in Spring Boot

Description: This is a classical Spring Bootoffset pagination example. However, is not advisable to use this approach in production because of its performance penalties explained further.

When we rely on anoffset pagination, we have the performance penalty induced by throwing awayn records before reaching the desiredoffset. Largern leads to a significant performance penalty. Another penalty is the extra-SELECT needed to count the total number of records. In order to understand how badoffset pagination can perform please checkthis article. A screenshot from that article is below:Nevertheless, maybe this example is a little bit extreme. For relatively small datasets,offset pagination is not so bad (it is close in performance tokeyset pagination), and, since Spring Boot provides built-in support foroffset pagination via thePage API, it is very easy to use it. However, depending on the case, we can optimize a little bit theoffset pagination as in the following examples:

Fetch a page as aPage:

• TriggerCOUNT(*) OVER And ReturnPage<dto>
• TriggerCOUNT(*) OVER And ReturnPage<entity> Via Extra Column
• TriggerSELECT COUNT Subquery And ReturnPage<dto>
• TriggerSELECT COUNT Subquery And ReturnPage<entity> Via Extra Column
• TriggerSELECT COUNT Subquery And ReturnPage<projection> That Maps Entities And The Total Number Of Records Via Projection

Fetch a page as aList:

• TriggerCOUNT(*) OVER And ReturnList<dto>
• TriggerCOUNT(*) OVER And ReturnList<entity> Via Extra Column
• TriggerSELECT COUNT Subquery And ReturnList<dto>
• TriggerSELECT COUNT Subquery And ReturnList<entity> Via Extra Column
• TriggerSELECT COUNT Subquery And ReturnList<projection> That Maps Entities And The Total Number Of Records Via Projection

But: Ifoffset pagination is causing you performance issues and you decide to go withkeyset pagination then please checkhere (keyset pagination).

Key points of classicaloffset pagination:

• write a repository that extendsPagingAndSortingRepository
• call or write methods that returnsPage<entity>

Examples of classicaloffset pagination:

• call the built-infindAll(Pageable) without sorting:
  repository.findAll(PageRequest.of(page, size));
• call the built-infindAll(Pageable) with sorting:
  repository.findAll(PageRequest.of(page, size, new Sort(Sort.Direction.ASC, "name")));
• use Spring Data query creation to define new methods in your repository:
  Page<Author> findByName(String name, Pageable pageable);
Page<Author> queryFirst10ByName(String name, Pageable pageable);

74. How To Optimize Batch Inserts of Parent-Child Relationships In MySQL

Description: Let's suppose that we have a one-to-many relationship betweenAuthor andBook entities. When we save an author, we save his books as well thanks to cascading all/persist. We want to create a bunch of authors with books and save them in the database (e.g., a MySQL database) using the batch technique. By default, this will result in batching each author and the books per author (one batch for the author and one batch for the books, another batch for the author and another batch for the books, and so on). In order to batch authors and books, we need toorder inserts as in this application.

Key points:Beside all setting specific to batching inserts in MySQL, we need to set up inapplication.properties the following property:spring.jpa.properties.hibernate.order_inserts=true

Example without ordered inserts:

Example with ordered inserts:

75. How To Batch Updates In MySQL

Implementations:

• Single entity update
• Parent-child relationship update

Description: Batch updates in MySQL.

Key points:

• inapplication.properties setspring.jpa.properties.hibernate.jdbc.batch_size
• inapplication.properties set JDBC URL withrewriteBatchedStatements=true (optimization for MySQL, statements get rewritten into a single string buffer and sent in a single request)
• inapplication.properties set JDBC URL withcachePrepStmts=true (enable caching and is useful if you decide to setprepStmtCacheSize,prepStmtCacheSqlLimit, etc as well; without this setting the cache is disabled)
• inapplication.properties set JDBC URL withuseServerPrepStmts=true (this way you switch to server-side prepared statements (may lead to signnificant performance boost))
• in case of using a parent-child relationship with cascade all/persist (e.g. one-to-many, many-to-many) then consider to set upspring.jpa.properties.hibernate.order_updates=true to optimize the batching by ordering updates
• before Hibernate 5, we need to set inapplication.properties a setting for enabling batching for versioned entities during update and delete operations (entities that contains@Version for implicit optimistic locking); this setting is:spring.jpa.properties.hibernate.jdbc.batch_versioned_data=true; starting with Hibernate 5, this setting should betrue by default

Output example for single entity:

Output example for parent-child relationship:

76. How To Batch Deletes That Don't Involve Associations In MySQL

Description: Batch deletes that don't involve associations in MySQL.

Note: SpringdeleteAllInBatch() anddeleteInBatch() don't use delete batching and don't take advantage of automatic optimstic locking mechanism to preventlost updates (e.g.,@Version is ignored). They rely onQuery.executeUpdate() to triggerbulk operations. These operations are fast, but Hibernate doesn’t know which entities are removed, therefore, the Persistence Context is not updated accordingly (it's up to you to flush (before delete) and close/clear (after delete) the Persistence Context accordingly to avoid issues created by unflushed (if any) or outdated (if any) entities). The first one (deleteAllInBatch()) simply triggers adelete from entity_name statement and is very useful for deleting all records. The second one (deleteInBatch()) triggers adelete from entity_name where id=? or id=? or id=? ... statement, therefore, is prone to cause issues if the generatedDELETE statement exceedes the maximum accepted size. This issue can be controlled by deleting the data in chunks, relying onIN operator, and so on.Bulk operations are faster than batching which can be achieved via thedeleteAll(),deleteAll(Iterable<? extends T> entities) ordelete() method. Behind the scene, the two flavors ofdeleteAll() relies ondelete(). Thedelete()/deleteAll() methods rely onEntityManager.remove() therefore the Persistence Context is synchronized accordingly. Moreover, if automatic optimstic locking mechanism (to preventlost updates) is enabled then it will be used.

Key points forregular delete batching:

• for deleting in batches rely ondeleteAll(),deleteAll(Iterable<? extends T> entities) ordelete() method
• inapplication.properties setspring.jpa.properties.hibernate.jdbc.batch_size
• inapplication.properties set JDBC URL withrewriteBatchedStatements=true (optimization for MySQL, statements get rewritten into a single string buffer and sent in a single request)
• inapplication.properties set JDBC URL withcachePrepStmts=true (enable caching and is useful if you decide to setprepStmtCacheSize,prepStmtCacheSqlLimit, etc as well; without this setting the cache is disabled)
• inapplication.properties set JDBC URL withuseServerPrepStmts=true (this way you switch to server-side prepared statements (may lead to signnificant performance boost))
• before Hibernate 5, we need to set inapplication.properties a setting for enabling batching for versioned entities during update and delete operations (entities that contains@Version for implicit optimistic locking); this setting is:spring.jpa.properties.hibernate.jdbc.batch_versioned_data=true; starting with Hibernate 5, this setting should betrue by default

Output example:

77. How To Batch Deletes In MySQL Via orphanRemoval=true

Description: Batch deletes in MySQL viaorphanRemoval=true.

Note: SpringdeleteAllInBatch() anddeleteInBatch() don't use delete batching and don't take advantage of cascading removal,orphanRemoval and automatic optimstic locking mechanism to preventlost updates (e.g.,@Version is ignored). They rely onQuery.executeUpdate() to triggerbulk operations. These operations are fast, but Hibernate doesn’t know which entities are removed, therefore, the Persistence Context is not updated accordingly (it's up to you to flush (before delete) and close/clear (after delete) the Persistence Context accordingly to avoid issues created by unflushed (if any) or outdated (if any) entities). The first one (deleteAllInBatch()) simply triggers adelete from entity_name statement and is very useful for deleting all records. The second one (deleteInBatch()) triggers adelete from entity_name where id=? or id=? or id=? ... statement, therefore, is prone to cause issues if the generatedDELETE statement exceedes the maximum accepted size. This issue can be controlled by deleting the data in chunks, relying onIN operator, and so on.Bulk operations are faster than batching which can be achieved via thedeleteAll(),deleteAll(Iterable<? extends T> entities) ordelete() method. Behind the scene, the two flavors ofdeleteAll() relies ondelete(). Thedelete()/deleteAll() methods rely onEntityManager.remove() therefore the Persistence Context is synchronized accordingly. If automatic optimstic locking mechanism (to preventlost updates) is enabled then it will be used. Moreover, cascading removals andorphanRemoval works as well.

Key points for usingdeleteAll()/delete():

• in this example, we have aAuthor entity and each author can have severalBook (one-to-many)
• first, we useorphanRemoval=true andCascadeType.ALL
• second, we dissociate allBook from the correspondingAuthor
• third, we explicitly (manually) flush the Persistent Context; is time fororphanRemoval=true to enter into the scene; thanks to this setting, all disassociated books will be deleted; the generatedDELETE statements are batched (iforphanRemoval is set tofalse, a bunch of updates will be executed instead of deletes)
• forth, we delete allAuthor via thedeleteAll() ordelete() method (since we have dissaciated allBook, theAuthor deletion will take advantage of batching as well)

78. How To Batch Deletes In MySQL Via SQLON DELETE CASCADE

Description: Batch deletes in MySQL viaON DELETE CASCADE. Auto-generated database schema will contain theON DELETE CASCADE directive.

Note: SpringdeleteAllInBatch() anddeleteInBatch() don't use delete batching and don't take advantage of cascading removal,orphanRemoval and automatic optimistic locking mechanism to preventlost updates (e.g.,@Version is ignored), but both of them take advantage onON DELETE CASCADE and are very efficient. They triggerbulk operations viaQuery.executeUpdate(), therefore, the Persistence Context is not synchronized accordingly (it's up to you to flush (before delete) and close/clear (after delete) the Persistence Context accordingly to avoid issues created by unflushed (if any) or outdated (if any) entities). The first one simply triggers adelete from entity_name statement, while the second one triggers adelete from entity_name where id=? or id=? or id=? ... statement. For delete in batches rely ondeleteAll(),deleteAll(Iterable<? extends T> entities) ordelete() method. Behind the scene, the two flavors ofdeleteAll() relies ondelete(). Mixing batching with database automatic actions (ON DELETE CASCADE) will result in a partially synchronized Persistent Context.

Key points:

• in this application, we have aAuthor entity and each author can have severalBook (one-to-many)
• first, we removeorphanRemoval or set it tofalse
• second, we use onlyCascadeType.PERSIST andCascadeType.MERGE
• third, we set@OnDelete(action = OnDeleteAction.CASCADE) next to@OneToMany
• fourth, we setspring.jpa.properties.hibernate.dialect toorg.hibernate.dialect.MySQL5InnoDBDialect (or,MySQL8Dialect)
• fifth, we run through a set ofdeleteFoo() methods that usesbulk and batching deletes as well

Output example:

79. How To Use Hibernate@NaturalId In Spring Boot Style

Alternative implementation: In case that you want to avoid extendingSimpleJpaRepository check thisimplementation.

Description: This is a SpringBoot application that maps a natural business key using Hibernate@NaturalId. This implementation allows us to use@NaturalId as it was provided by Spring.

Key points:

• in the entity (e.g.,Book), mark the properties (business keys) that should act as natural IDs with@NaturalId; commonly, there is a single such property, but multiple are suppored as well ashere
• for non-mutable ids, mark the columns as@NaturalId(mutable = false) and@Column(nullable = false, updatable = false, unique = true, ...)
• for mutable ids, mark the columns as@NaturalId(mutable = true) and@Column(nullable = false, updatable = true, unique = true, ...)
• override theequals() andhashCode() using the natural id(s)
• define a@NoRepositoryBean interface (NaturalRepository) to define two methods, namedfindBySimpleNaturalId() andfindByNaturalId()
• provide an implementation for this interface (NaturalRepositoryImpl) relying on Hibernate,Session,bySimpleNaturalId() andbyNaturalId() methods
• use@EnableJpaRepositories(repositoryBaseClass = NaturalRepositoryImpl.class) to register this implementation as the base class
• for the entity, write a classic repository
• inject this class in your services and callfindBySimpleNaturalId() orfindByNaturalId()

80. How To Set Up P6Spy in Spring Boot

Description: This is a Spring Boot application that usesP6Spy.P6Spy is a framework that enables database data to be seamlessly intercepted and logged with no code changes to the application.

Key points:

• inpom.xml, add the P6Spy Maven dependency
• inapplication.properties, set up JDBC URL as,jdbc:p6spy:mysql://localhost:3306/db_users
• inapplication.properties, set up driver class name as,com.p6spy.engine.spy.P6SpyDriver
• in the application root folder add the filespy.properties (this file contains P6Spy configurations); in this application, the logs will be outputed to console, but you can easy switch to a file; more details about P6Spy configurations can be found in documentation

Output sample:

81. How To Retry Transactions AfterOptimisticLockException Exception (@Version)

Note: Optimistic locking mechanism via@Version works for detached entities as well.

Description: This is a Spring Boot application that simulates a scenario that leads to an optimistic locking exception. When such exception occur, the application retry the corresponding transaction viadb-util library developed by Vlad Mihalcea.

Key points:

• for Maven, inpom.xml, add thedb-util dependency
• configure theOptimisticConcurrencyControlAspect bean
• mark the method (not annotated with@Transactional) that is prone to throw (or that calls a method that is prone to throw (this method can be annotated with@Transactional)) an optimistic locking exception with@Retry(times = 10, on = OptimisticLockingFailureException.class)

Output sample:

82. How To Retry Transaction AfterOptimisticLockException Exception (Hibernate Version-less Optimistic Locking Mechanism)

Note: Optimistic locking mechanism via Hibernate version-less doesn't work for detached entities (don't close the Persistent Context).

Description: This is a Spring Boot application that simulates a scenario that leads to an optimistic locking exception (e.g., in Spring Boot,OptimisticLockingFailureException) via Hibernate version-less optimistic locking. When such exception occur, the application retry the corresponding transaction viadb-util library developed by Vlad Mihalcea.

Key points:

• for Maven, inpom.xml, add thedb-util library dependency
• configure theOptimisticConcurrencyControlAspect bean
• annotate the corresponding entity (e.g.,Inventory) with@DynamicUpdate and@OptimisticLocking(type = OptimisticLockType.DIRTY)
• mark the method (not annotated with@Transactional) that is prone to throw (or that calls a method that is prone to throw (this method can be annotated with@Transactional)) an optimistic locking exception with@Retry(times = 10, on = OptimisticLockingFailureException.class)

83. How To Enrich DTO With Virtual Properties Via Spring Projections

Note: You may also like to read the recipe,"How To Create DTO Via Spring Data Projections"

Description: This is an application sample that fetches only the needed columns from the database via Spring Data Projections (DTO) and enrich the result via virtual properties.

Key points:

• we fetch from the database only the authorname andage
• in the projection interface,AuthorNameAge, use the@Value and Spring SpEL to point to a backing property from the domain model (in this case, the domain model propertyage is exposed via the virtual propertyyears)
• in the projection interface,AuthorNameAge, use the@Value and Spring SpEL to enrich the result with two virtual properties that don't have a match in the domain model (in this case,rank andbooks)

Output example:

84. How To Use Query Creation Mechanism For JPA To Limit Result Size

Description: Spring Data comes with the query creation mechanism for JPA that is capable to interpret a query method name and convert it into a SQL query in the proper dialect. This is possible as long as we respect the naming conventions of this mechanism. This is an application that exploit this mechanism to write queries that limit the result size. Basically, the name of the query method instructs Spring Data how to add theLIMIT (or similar clauses depending on the RDBMS) clause to the generated SQL queries.

Key points:

• define a Spring Data regular repository (e.g.,AuthorRepository)
• write query methods respecting the query creation mechanism for JPA naming conventions

Examples:
-List<Author> findFirst5ByAge(int age);
-List<Author> findFirst5ByAgeGreaterThanEqual(int age);
-List<Author> findFirst5ByAgeLessThan(int age);
-List<Author> findFirst5ByAgeOrderByNameDesc(int age);
-List<Author> findFirst5ByGenreOrderByAgeAsc(String genre);
-List<Author> findFirst5ByAgeGreaterThanEqualOrderByNameAsc(int age);
-List<Author> findFirst5ByGenreAndAgeLessThanOrderByNameDesc(String genre, int age);
-List<AuthorDto> findFirst5ByOrderByAgeAsc();
-Page<Author> queryFirst10ByName(String name, Pageable p);
-Slice<Author> findFirst10ByName(String name, Pageable p);

The list of supported keywords is listed below:

85. How To Generate A Schema Viaschema-*.sql In MySQL

Note: As a rule, in real applications avoid generating schema viahibernate.ddl-auto or set it tovalidate. Useschema-*.sql file or betterFlyway orLiquibase migration tools.

Description: This application is an example of usingschema-*.sql to generate a schema(database) in MySQL.

Key points:

• inapplication.properties, set the JDBC URL (e.g.,spring.datasource.url=jdbc:mysql://localhost:3306/bookstoredb?createDatabaseIfNotExist=true)
• inapplication.properties, disable DDL auto (just don't add explicitly thehibernate.ddl-auto setting)
• inapplication.properties, instruct Spring Boot to initialize the schema fromschema-mysql.sql file

86. How To Generate Two Databases Viaschema-*.sql And Match Entities To Them Via@Table In MySQL

Note: As a rule, in real applications avoid generating schema viahibernate.ddl-auto or set it tovalidate. Useschema-*.sql file or betterFlyway orLiquibase.

Description: This application is an example of usingschema-*.sql to generate two databases in MySQL. The databases are matched at entity mapping via@Table.

Key points:

• inapplication.properties, set the JDBC URL without the database, e.g.,spring.datasource.url=jdbc:mysql://localhost:3306
• inapplication.properties, disable DDL auto (just don't specifyhibernate.ddl-auto)
• inaaplication.properties, instruct Spring Boot to initialize the schema fromschema-mysql.sql file
• inAuthor entity, specify that the corresponding table (author) is in the databaseauthorsdb via@Table(schema="authorsdb")
• inBook entity, specify that the corresponding table (book) is in the databasebooksdb via@Table(schema="booksdb")

Output example:

• Persisting aAuthor results in the following SQL:insert into authorsdb.author (age, genre, name) values (?, ?, ?)
• Persisting aBook results the following SQL:insert into booksdb.book (isbn, title) values (?, ?)

87. How To Stream Result Set Via Spring Data In MySQL

Note: For web-applications, pagination should be the way to go, not streaming. But, if you choose streaming then keep in mind the golden rule: keep th result set as small as posible. Also, keep in mind that the Execution Plan might not be as efficient as when using SQL-level pagination.

Description: This application is an example of streaming the result set via Spring Data and MySQL. This example can be adopted for databases that fetches the entire result set in a single roundtrip causing performance penalties.

Key points:

• rely onforward-only result set (default in Spring Data)
• rely onread-only statement (add@Transactional(readOnly=true))
• set the fetch-size set (e.g. 30, or row-by-row;Integer.MIN_VALUE (recommended in MySQL))
• for MySQL, setStatement fetch-size toInteger.MIN_VALUE, or adduseCursorFetch=true to the JDBC URL and setStatement fetch-size to a positive integer (e.g., 30)

88. How To Migrate MySQL Database Using Flyway - MySQL Database Created ViacreateDatabaseIfNotExist

Note: For production, don't rely onhibernate.ddl-auto (or counterparts) to export schema DDL to the database. Simply remove (disable)hibernate.ddl-auto or set it tovalidate. Rely on Flyway or Liquibase.

Description: This application is an example of migrating a MySQL database via Flyway when the database exists (it is created before migration via MySQL specific parameter,createDatabaseIfNotExist=true).

Key points:

• for Maven, inpom.xml, add the Flyway dependency
• remove (disable)spring.jpa.hibernate.ddl-auto
• inapplication.properties, set the JDBC URL as follows:jdbc:mysql://localhost:3306/bookstoredb?createDatabaseIfNotExist=true
• each SQL file containing the schema update add it inclasspath:db/migration
• each SQL file name it asV1.1__Description.sql,V1.2__Description.sql, ...

89. How To Migrate MySQL Database Using Flyway - Database Created Viaspring.flyway.schemas

Note: For production, don't rely onhibernate.ddl-auto (or counterparts) to export schema DDL to the database. Simply remove (disable)hibernate.ddl-auto or set it tovalidate. Rely on Flyway or Liquibase.

Description: This application is an example of migrating a MySQL database when the database is created by Flyway viaspring.flyway.schemas. In this case, the entities should be annotated with@Table(schema = "bookstoredb") or@Table(catalog = "bookstoredb"). Here, the database name isbookstoredb.

Key points:

• for Maven, inpom.xml, add the Flyway dependency
• remove (disable)spring.jpa.hibernate.ddl-auto
• inapplication.properties, set the JDBC URL as follows:jdbc:mysql://localhost:3306/
• inapplication.properties, addspring.flyway.schemas=bookstoredb, wherebookstoredb is the database that should be created by Flyway (feel free to add your own database name)
• each entity that should be stored in this database should be annotated with,@Table(schema/catalog = "bookstoredb")
• each SQL file containing the schema update add it inclasspath:db/migration
• each SQL file name it asV1.1__Description.sql,V1.2__Description.sql, ...

Output of migration history example:

90. How To Auto-Create And Migrate Schemas For Two Data Sources (MySQL and PostgreSQL) Using Flyway

Note: For production don't rely onhibernate.ddl-auto to create your schema. Remove (disable)hibernate.ddl-auto or set it tovalidate. Rely on Flyway or Liquibase.

Description: This application is an example of auto-creating and migrating schemas for MySQL and PostgreSQL. In addition, each data source uses its own HikariCP connection pool. In case of MySQL, whereschema=database, we auto-create the schema (authorsdb) based oncreateDatabaseIfNotExist=true. In case of PostgreSQL, where a database can have multiple schemas, we use the defaultpostgres database and auto-create in it the schema,booksdb. For this we rely on Flyway, which is capable to create a missing schema.

Key points:

• for Maven, inpom.xml, add the Flyway dependency
• remove (disable)spring.jpa.hibernate.ddl-auto or set it tovalidate
• inapplication.properties, configure the JDBC URL for MySQL as,jdbc:mysql://localhost:3306/authorsdb?createDatabaseIfNotExist=true and for PostgreSQL as,jdbc:postgresql://localhost:5432/postgres?currentSchema=booksdb
• inapplication.properties, setspring.flyway.enabled=false to disable default behavior
• programmatically create aDataSource for MySQL and one for PostgreSQL
• programmatically create aFlywayDataSource for MySQL and one for PostgreSQL
• programmatically create anEntityManagerFactory for MySQL and one for PostgreSQL
• for MySQL, place the migration SQLs files indb\migration\mysql
• for PostgreSQL, place the migration SQLs files indb\migration\postgresql

91. How To Auto-Create And Migrate Two Schemas In PostgreSQL Using Flyway

Note: For production, don't rely onhibernate.ddl-auto (or counterparts) to export schema DDL to the database. Simply remove (disable)hibernate.ddl-auto or set it tovalidate. Rely on Flyway or Liquibase.

Description: This application is an example of auto-creating and migrating two schemas in PostgreSQL using Flyway. In addition, each data source uses its own HikariCP connection pool. In case of PostgreSQL, where a database can have multiple schemas, we use the defaultpostgres database and auto-create two schemas,authors andbooks. For this we rely on Flyway, which is capable to create the missing schemas.

Key points:

• for Maven, inpom.xml, add the Flyway dependency
• remove (disable)spring.jpa.hibernate.ddl-auto or set it tovalidate
• inapplication.properties, configure the JDBC URL forbooks asjdbc:postgresql://localhost:5432/postgres?currentSchema=books and forauthors asjdbc:postgresql://localhost:5432/postgres?currentSchema=authors
• inapplication.properties, setspring.flyway.enabled=false to disable default behavior
• programmatically create twoDataSource, one forbooks and one forauthors
• programmatically create twoFlywayDataSource, one forbooks and one forauthors
• programmatically create twoEntityManagerFactory, one forbooks and one forauthors
• forbooks, place the migration SQLs files indb\migration\books
• forauthors, place the migration SQLs files indb\migration\authors

92. How ToJOIN FETCH an@ElementCollection

Description: This application is an example applyingJOIN FETCH to fetch an@ElementCollection.

Key points:

• by default,@ElementCollection is loaded lazy, keep it lazy
• useJOIN FETCH in the repository

93. How To Map An Entity To a Query (@Subselect) in a Spring Boot Application

Note: Consider using@Subselect only if using DTO, DTO and extra queries, or map a database view to an entity is not a solution.

Description: This application is an example of mapping an entity to a query via Hibernate,@Subselect. Mainly, we have two entities in a bidirectionalone-to-many association. AnAuthor has wrote severalBook. The idea is to write aread-only query to fetch fromAuthor only some fields (e.g., DTO), but to have the posibility to callgetBooks() and fetch theBook in a lazy manner as well. As you know, a classic DTO cannot be used, since such DTO is not managed and we cannot navigate the associations (don’t support any managed associations to other entities). Via Hibernate@Subselect we can map aread-only andimmutable entity to a query. This time, we can lazy navigate the associations.

Key points:

• define a new entity that contains only the needed fields from theAuthor (including association toBook)
• for these fields, define only getters
• mark the entity as@Immutable since no write operations are allowed
• flush pending state transitions for the used entities by@Synchronize
• use@Subselect to write the needed query, map an entity to an SQL query

94. How To Use Hibernate Soft Deletes In A Spring Boot Application

Description: This application is an example of using Hibernate soft deletes in a Spring Boot application.

Key points:

• define anabstract classBaseEntity with a field nameddeleted
• the entities (e.g.,Author andBook entities) that should take advantage of soft deletes should extendBaseEntity
• these entities should be marked with Hibernate,@Where annotation like this:@Where(clause = "deleted = false")
• these entities should be marked with Hibernate,@SQLDelete annotation to triggerUPDATE SQLs in place ofDELETE SQLs, as follows:@SQLDelete(sql = "UPDATE author SET deleted = true WHERE id = ?")
• for fetching all entities including those marked as deleted or for fetching only the entities marked as deleted we need to rely on SQL native queries

Output example:

95. How To Programmatically Customize HikariCP Settings ViaDataSourceBuilder

If you use thespring-boot-starter-jdbc orspring-boot-starter-data-jpa "starters", you automatically get a dependency to HikariCP

Note: The best way to tune the connection pool parameters consist in usingFlexy Pool by Vlad Mihalcea. ViaFlexy Pool you can find the optim settings that sustain high-performance of your connection pool.

Description: This is a kickoff application that set up HikariCP viaDataSourceBuilder. ThejdbcUrl is set up for a MySQL database. For testing purposes, the application uses anExecutorService for simulating concurrent users. Check the HickariCP report revealing the connection pool status.

Key points:

• write a@Bean that returns theDataSource programmatically

96. How To Setup Spring Data JPA Auditing

Description: Auditing is useful for maintaining history records. This can later help us in tracking user activities.

Key points:

• create anabstract base entity (e.g.,BaseEntity) and annotate it with@MappedSuperclass and@EntityListeners({AuditingEntityListener.class})
• in this base entity, add the following fields that will be automatically persisted:
        -@CreatedDate protected LocalDateTime created;
        -@LastModifiedDate protected LocalDateTime lastModified;
        -@CreatedBy protected U createdBy;
        -@LastModifiedBy protected U lastModifiedBy;
• enable auditing via@EnableJpaAuditing(auditorAwareRef = "auditorAware")
• provide an implementation forAuditorAware (this is needed for persisting the user that performed the modification; use Spring Security to return the currently logged-in user)
• expose this implementation via@Bean
• entites that should be audited should extend the base entity
• store the date-time in database in UTC

97. Hibernate Envers Auditing (spring.jpa.hibernate.ddl-auto=create)

Description: Auditing is useful for maintaining history records. This can later help us in tracking user activities.

Key points:

• each entity that should be audited should be annotated with@Audited
• optionally, annotate entities with@AuditTable to rename the table used for auditing
• rely onValidityAuditStrategy for fast database reads, but slower writes (slower than the defaultDefaultAuditStrategy)

98. Attributes Lazy Loading Via Subentities

Description: By default, the attributes of an entity are loaded eager (all at once). This application is an alternative toHow To Use Hibernate Attribute Lazy Loading fromhere. This application uses a base class to isolate the attributes that should be loaded eagerly and subentities (entities that extends the base class) for isolating the attributes that should be loaded on demand.

Key points:

• create the base class (this is not an entity),BaseAuthor, and annotate it with@MappedSuperclass
• createAuthorShallow subentity ofBaseAuthor and don't add any attribute in it (this will inherit the attributes from the superclass)
• createAuthorDeep subentity ofBaseAuthor and add to it the attributes that should be loaded on demand (e.g.,avatar)
• map both subentities to the same table via@Table(name = "author")
• provide the typical repositories,AuthorShallowRepository andAuthorDeepRepository

Run the following requests (via BookstoreController):

• fetch all authors shallow (without avatars):localhost:8080/authors/shallow
• fetch all authors deep (with avatars):localhost:8080/authors/deep

Check as well:

• Attribute Lazy Loading (basic)
• Default Values For Lazy Loaded Attributes
• Attribute Lazy Loading And Jackson Serialization

99. DTO Via Constructor And Spring Data Query Builder Mechanism

Description: Fetching more data than needed is prone to performance penalities. Using DTO allows us to extract only the needed data. In this application we rely on constructor and Spring Data Query Builder Mechanism.

Key points:

• write a proper constructor in the DTO class
• rely on Spring Data Query Builder Mechanism to write the SQL
• for using Spring Data Projections check thisitem

See also:
Dto Via Constructor Expression and JPQL

100. How To Page The Result Set of aJOIN

Description: UsingJOIN is very useful for fetching DTOs (data that is never modified, not in the current or subsequent requests). For example, consider two entities,Author andBook in a lazy-bidirectional@OneToMany association. And, we want to fetch a subset of columns from the parent table (author) and a subset of columns from the child table (book). This job is a perfect fit forJOIN which can pick up columns from different tables and build araw result set. This way we fetch only the needed data. Moreover, we may want to serve the result set in pages (e.g., viaLIMIT). This application contains several approaches for accomplishing this task with offset pagination.

Key points:

• pagination viaPage (withSELECT COUNT andCOUNT(*) OVER() window function)
• pagination viaSlice andList
• pagination viaDENSE_RANK() for avoiding the truncation of the result set (an author can be fetched with only a subset of his books)

101. LEFT JOIN FETCH

See also:

• How To Avoid LazyInitializationException Via JOIN FETCH
• JOIN VS. JOIN FETCH

Description: Let's assume that we have two entities engaged in a one-to-many (or many-to-many) lazy bidirectional (or unidirectional) relationship (e.g.,Author has moreBook). And, we want to trigger a singleSELECT that fetches allAuthor and the correspondingBook. This is a job forJOIN FETCH which is converted behind the scene into aINNER JOIN. Being anINNER JOIN, the SQL will return onlyAuthor that haveBook. If we want to return allAuthor, including those that doesn't haveBook, then we can rely onLEFT JOIN FETCH. Similar, we can fetch allBook, including those with no registeredAuthor. This can be done viaLEFT JOIN FETCH orLEFT JOIN.

Key points:

• define two related entities (e.g.,Author andBook in a one-to-many lazy bidirectional relationship)
• write a JPQLLEFT JOIN FETCH to fetch all authors and books (fetch authors even if they don't have registered books)
• write a JPQLLEFT JOIN FETCH to fetch all books and authors (fetch books even if they don't have registered authors)

102. JOIN VS.JOIN FETCH

See also:

• How To Avoid LazyInitializationException Via JOIN FETCH
• LEFT JOIN FETCH

Description: This is an application meant to reveal the differences betweenJOIN andJOIN FETCH. The important thing to keep in mind is that, in case ofLAZY fetching,JOIN will not be capable to initialize the associated collections along with their parent objects using a single SQLSELECT. On the other hand,JOIN FETCH is capable to accomplish this kind of task. But, don't underestimateJOIN, becauseJOIN is the proper choice when we need to combine/join the columns of two (or more) tables in the same query, but we don't need to initialize the associated collections on the returned entity (e.g., very useful for fetching DTO).

Key points:

• define two related entities (e.g.,Author andBook in a one-to-many lazy-bidirectional relationship)
• write a JPQLJOIN andJOIN FETCH to fetch an author including his books
• write a JPQLJOIN to fetch a book (1)
• write a JPQLJOIN to fetch a book including its author (2)
• write aJOIN FETCH to fetch a book including its author

Notice that:

• viaJOIN, fetchingBook ofAuthor requires additionalSELECT statements being prone to N+1 performance penalty
• viaJOIN (1), fetchingAuthor ofBook requires additionalSELECT statements being prone to N+1 performance penalty
• viaJOIN (2), fetchingAuthor ofBook works exactly asJOIN FETCH (requires a singleSELECT)
• viaJOIN FETCH, fetching eachAuthor of aBook requires a singleSELECT

103. Entity Inside Spring Projection

Description: If, for some reason, you need an entity in your Spring projection (DTO), then this application shows you how to do it via an example. In this case, there are two entities,Author andBook, involved in a lazy bidirectional one-to-many association (it can be other association as well, or even no materialized association). And, we want to fetch in a Spring projection the authors as entities,Author, and thetitle of the books.

Key points:

• define two related entities (e.g.,Author andBook in a one-to-many lazy bidirectional relationship)
• define the proper Spring projection havingpublic Author getAuthor() andpublic String getTitle()
• write a JPQL to fetch data

104. Entity Inside Spring Projection (no association)

Description: If, for some reason, you need an entity in your Spring projection (DTO), then this application shows you how to do it via an example. In this case, there are two entities,Author andBook, that have no materialized association between them, but, they share thegenre attribute. We use this attribute to join authors with books via JPQL. And, we want to fetch in a Spring projection the authors as entities,Author, and thetitle of the books.

Key points:

• define two unrelated entities (e.g.,Author andBook)
• define the proper Spring projection havingpublic Author getAuthor() andpublic String getTitle()
• write a JPQL to fetch data

105. Avoid Entity In DTO Via Constructor Expression (no association)

Description: Let's assume that we have two entities,Author andBook. There is no materialized association between them, but, both entities shares an attribute named,genre. We want to use this attribute to join the tables corresponding toAuthor andBook, and fetch the result in a DTO. The result should contain theAuthor entity and only thetitle attribute fromBook. Well, when you are in a scenario as here, it is strongly advisable to avoid fetching the DTO viaconstructor expression. This approach cannot fetch the data in a singleSELECT, and is prone to N+1. Way better than this consists of using Spring projections, JPATuple or even HibernateResultTransformer. These approaches will fetch the data in a singleSELECT. This application is aDON'T DO THIS example. Check the number of queries needed for fetching the data. In place, do it as here:Entity Inside Spring Projection (no association).

106. How To DTO an@ElementCollection

Description: This application is an example of fetching a DTO that includes attributes from an@ElementCollection.

Key points:

• by default,@ElementCollection is loaded lazy, keep it lazy
• use a Spring projection andJOIN in the repository

107. Ordering TheSet Of Associated Entities In@ManyToMany Association Via@OrderBy

Description: In case of@ManyToMany association, we always should rely onSet (not onList) for mapping the collection of associated entities (entities of the other parent-side). Why? Well, please seePrefer Set Instead of List in @ManyToMany Relationships. But, is well-known thatHashSet doesn't have a predefined entry order of elements. If this is an issue then this application relies on@OrderBy which adds anORDER BY clause in the SQL statement. The database will handle the ordering. Further, Hibernate will preserve the order via aLinkedHashSet.

This application uses two entities,Author andBook, involved in a lazy bidirectional many-to-many relationship. First, we fetch aBook by title. Further, we callgetAuthors() to fetch the authors of this book. The fetched authors are ordered descending by name. The ordering is done by the database as a result of adding@OrderBy("name DESC"), and is preserved by Hibernate.

Key points:

• ask the database to handle ordering and Hibernate to preserve this order via@OrderBy
• this works withHashSet, but doesn't provide consistency across all transition states (e.g.,transient state)
• for consistency across thetransient state as well, consider using explicitlyLinkedHashSet instead ofHashSet

Note: Alternatively, we can use@OrderColumn. This gets materialized in an additional column in the junction table. This is needed for maintaining a permanent ordering of the related data.

108. Versioned Optimistic Locking And Detached Entities Sample

Description: This is a sample application that shows how versioned (@Version) optimistic locking and detached entity works. Running the application will result in an optimistic locking specific exception (e.g., the Spring Boot specific,OptimisticLockingFailureException).

Key points:

• in a transaction, fetch an entity viafindById(1L); commit transaction and close the Persistence Context
• in a second transaction, fetch another entity viafindById(1L) and update it; commit the transaction and close the Persistence Context
• outside transactional context, update the detached entity (fetched in the first transaction)
• in a third transaction, callsave() and pass to it the detached entity; trying to merge (EntityManager.merge()) the entity will end up in an optimistic locking exception since the version of the detached and just loaded entity don't match

109. How To SimulateOptimisticLockException Shaped Via@Version

Note: Optimistic locking via@Version works for detached entities as well.

Description: This is a Spring Boot application that simulates a scenario that leads to an optimistic locking exception. So, running the application should end up with a Spring specificObjectOptimisticLockingFailureException exception.

Key points:

• set up versioned optimistic locking mechanism
• rely on two concurrent threads that call the same@Transactional method used for updating data

110. How To Retry Transaction ViaTransactionTemplate AfterOptimisticLockException Exception (@Version)

Note: Optimistic locking via@Version works for detached entities as well.

Description: This is a Spring Boot application that simulates a scenario that leads to an optimistic locking exception. When such exception occurs, the application retry the corresponding transaction viadb-util library developed by Vlad Mihalcea.

Key points:

• inpom.xml, add thedb-util dependency
• configure theOptimisticConcurrencyControlAspect bean
• rely onTransactionTemplate

111. How To SimulateOptimisticLockException In Version-less Optimistic Locking

Note: Version-less optimistic locking doesn't work for detached entities (do not close the Persistence Context).

Description: This is a Spring Boot application that simulates a scenario that leads to an optimistic locking exception. So, running the application should end up with a Spring specificObjectOptimisticLockingFailureException exception.

Key points:

• set up the version-less optimistic locking mechanism
• rely on two concurrent threads that call the same a@Transactional method used for updating data

112. How To Retry Transaction ViaTransactionTemplate AfterOptimisticLockException Shaped Via Hibernate Version-less Optimistic Locking Mechanism

Note: Version-less optimistic locking doesn't work for detached entities (do not close the Persistence Context).

Description: This is a Spring Boot application that simulates a scenario that leads to an optimistic locking exception. When such exception occur, the application retry the corresponding transaction viadb-util library developed by Vlad Mihalcea.

Key points:

• for Maven, inpom.xml, add thedb-util dependency
• configure theOptimisticConcurrencyControlAspect bean
• rely onTransactionTemplate

113. HTTP Long Conversation Via Versioned Optimistic Locking And Detached Entities In The HTTP Session

Description: This is a sample application that shows how to take advantage of versioned optimistic locking and detached entities in HTTP long conversations. The climax consists of storing the detached entities across multiple HTTP requests. Commonly, this can be accomplished via HTTP session.

Key points:

• prepare the entity via@Version
• rely on@SessionAttributes for storing the detached entities

Sample output (check the message caused by optimistic locking exception):

114. Filter Association Via Hibernate@Where

Note: Rely on this approach only if you simply cannot useJOIN FETCH WHERE or@NamedEntityGraph.

Description: This application is a sample of using Hibernate@Where for filtering associations.

Key points:

• use@Where(clause = "condition to be met") in entity (check theAuthor entity)

115. Batch Inserts In Spring Boot Style

Description: Batch inserts (in MySQL) in Spring Boot style.

Key points:

• inapplication.properties setspring.jpa.properties.hibernate.jdbc.batch_size
• inapplication.properties setspring.jpa.properties.hibernate.generate_statistics (just to check that batching is working)
• inapplication.properties set JDBC URL withrewriteBatchedStatements=true (optimization for MySQL)
• inapplication.properties set JDBC URL withcachePrepStmts=true (enable caching and is useful if you decide to setprepStmtCacheSize,prepStmtCacheSqlLimit, etc as well; without this setting the cache is disabled)
• inapplication.properties set JDBC URL withuseServerPrepStmts=true (this way you switch to server-side prepared statements (may lead to signnificant performance boost))
• in case of using a parent-child relationship with cascade persist (e.g. one-to-many, many-to-many) then consider to set upspring.jpa.properties.hibernate.order_inserts=true to optimize the batching by ordering inserts
• in entity, use theassigned generator since the HibernateIDENTITY will cause insert batching to be disabled
• if is not needed then ensure that Second Level Cache is disabled viaspring.jpa.properties.hibernate.cache.use_second_level_cache=false

Output example:

116. Offset Pagination - TriggerCOUNT(*) OVER And ReturnPage<entity> Via Extra Column

Description: Typically, in offset pagination, there is one query needed for fetching the data and one for counting the total number of records. But, we can fetch this information in a single database rountrip via aSELECT COUNT subquery nested in the mainSELECT. Even better, for databases vendors that supportWindow Functions there is a solution relying onCOUNT(*) OVER() as in this application that uses this window function in a native query against MySQL 8. So, prefer this one instead ofSELECT COUNT subquery.This application fetches data asPage<entity> via Spring Boot offset pagination, but, if the fetched data isread-only, then rely onPage<dto> ashere.

Key points:

• write a repository that extendsPagingAndSortingRepository
• in the entity, add an extra column for representing the total number of records and annotate it as@Column(insertable = false, updatable = false)
• fetch data via a native query (that includes counting) into aList<entity>
• use the fetchedList<entity> andPageable to create aPage<entity>

117. Offset Pagination - TriggerSELECT COUNT Subquery And ReturnList<entity> Via Extra Column

Description: This application fetches data asList<entity> via Spring Boot offset pagination. TheSELECT COUNT triggered for counting the total number of records is a subquery of the mainSELECT. Therefore, there will be a single database roundtrip instead of two (typically, one query is needed for fetching the data and one for counting the total number of records).

Key points:

• write a repository that extendsPagingAndSortingRepository
• in theentity, add an extra column for representing the total number of records and annotate it as@Column(insertable = false, updatable = false)
• fetch data via a native query (that includesSELECT COUNT subquery) into aList<entity>

118. Offset Pagination - TriggerSELECT COUNT Subquery And ReturnList<projection> That Maps Entities And The Total Number Of Records Via Projection

Description: This application fetches data asList<projection> via Spring Boot offset pagination. The projection maps the entity and the total number of records. This information is fetched in a single database rountrip because theSELECT COUNT triggered for counting the total number of records is a subquery of the mainSELECT. Therefore, there will be a single database roundtrip instead of two (typically, there is one query needed for fetching the data and one for counting the total number of records). Use this approch only if the fetched data is notread-only. Otherwise, preferList<dto> ashere.

Key points:

• write a Spring projection that maps the entity and the total number of records
• write a repository that extendsPagingAndSortingRepository
• fetch data via a JPQL query (that includesSELECT COUNT subquery) into aList<projection>

119. Offset Pagination - TriggerCOUNT(*) OVER And ReturnList<entity> Via Extra Column

Description: Typically, in offset pagination, there is one query needed for fetching the data and one for counting the total number of records. But, we can fetch this information in a single database rountrip via aSELECT COUNT subquery nested in the mainSELECT. Even better, for databases vendors that supportWindow Functions there is a solution relying onCOUNT(*) OVER() as in this application that uses this window function in a native query against MySQL 8. So, prefer this one instead ofSELECT COUNT subquery.This application fetches data asList<entity> via Spring Boot offset pagination, but, if the fetched data isread-only, then rely onList<dto> ashere.

Key points:

• write a repository that extendsPagingAndSortingRepository
• in theentity, add an extra column for representing the total number of records and annotate it as@Column(insertable = false, updatable = false)
• fetch data via a native query (that includesCOUNT(*) OVER subquery) into aList<entity>

120. Offset Pagination - TriggerSELECT COUNT Subquery And ReturnPage<entity> Via Extra Column

Description: This application fetches data asPage<entity> via Spring Boot offset pagination. Use this only if the fetched data will be modified. Otherwise, fetchPage<dto> ashere. TheSELECT COUNT triggered for counting the total number of records is a subquery of the mainSELECT. Therefore, there will be a single database roundtrip instead of two (typically, there is one query needed for fetching the data and one for counting the total number of records).

Key points:

• write a repository that extendsPagingAndSortingRepository
• in the entity, add an extra column for representing the total number of records and annotate it as@Column(insertable = false, updatable = false)
• fetch data via a native query (that includes counting) into aList<entity>
• use the fetchedList<entity> andPageable to create aPage<entity>

121. Offset Pagination - TriggerSELECT COUNT Subquery And ReturnPage<projection> That Maps Entities And The Total Number Of Records Via Projection

Description: This application fetches data asPage<projection> via Spring Boot offset pagination. The projection maps the entity and the total number of records. This information is fetched in a single database rountrip because theSELECT COUNT triggered for counting the total number of records is a subquery of the mainSELECT.

Key points:

• define a Spring projection that maps the entity and the total number of records
• write a repository that extendsPagingAndSortingRepository
• fetch data via a JPQL query into aList<projection>
• use the fetchedList<projection> andPageable to create aPage<projection>

122. Offset Pagination - TriggerCOUNT(*) OVER And ReturnPage<dto>

Description: Typically, in offset pagination, there is one query needed for fetching the data and one for counting the total number of records. But, we can fetch this information in a single database rountrip via aSELECT COUNT subquery nested in the mainSELECT. Even better, for databases vendors that supportWindow Functions there is a solution relying onCOUNT(*) OVER() as in this application that uses this window function in a native query against MySQL 8. So, prefer this one instead ofSELECT COUNT subquery. This application return aPage<dto>.

Key points:

• create a Spring projection (DTO) to contains getters only for the columns that should be fetched
• write a repository that extendsPagingAndSortingRepository
• fetch data via a native query (that includes counting) into aList<dto>
• use the fetchedList<dto> andPageable to create aPage<dto>

Example:

123. How To FetchSlice<entity>/Slice<dto> ViafetchAll/fetchAllDto

Story: Spring Boot provides anoffset based built-in paging mechanism that returns aPage orSlice. Each of these APIs represents a page of data and some metadata. The main difference is thatPage contains the total number of records, whileSlice can only tell if there is another page available. ForPage, Spring Boot provides afindAll() method capable to take as arguments aPageable and/or aSpecification orExample. In order to create aPage that contains the total number of records, this method triggers anSELECT COUNT extra-query next to the query used to fetch the data of the current page . This can be a performance penalty since theSELECT COUNT query is triggered every time we request a page. In order to avoid this extra-query, Spring Boot provides a more relaxed API, theSlice API. UsingSlice instead ofPage removes the need of this extraSELECT COUNT query and returns the page (records) and some metadata without the total number of records. So, whileSlice doesn't know the total number of records, it still can tell if there is another page available after the current one or this is the last page. The problem is thatSlice work fine for queries containing the SQL,WHERE clause (including those that uses the query builder mechanism built into Spring Data), but itdoesn't work forfindAll(). This method will still return aPage instead ofSlice therefore theSELECT COUNT query is triggered forSlice<T> findAll(...);.

Workaround:The trick is to simply define a method namedfetchAll() that uses JPQL andPageable to returnSlice<entity>, and a method namedfetchAllDto() that uses JPQL andPageable as well to returnSlice<dto>. So, avoid naming the methodfindAll().

Usage example:
public Slice<Author> fetchNextSlice(int page, int size) {
    return authorRepository.fetchAll(PageRequest.of(page, size, new Sort(Sort.Direction.ASC, "age")));
}

public Slice<AuthorDto> fetchNextSliceDto(int page, int size) {
    return authorRepository.fetchAllDto(PageRequest.of(page, size, new Sort(Sort.Direction.ASC, "age")));
}

124. How To Use Spring Projections(DTOs) And Inclusive Full Joins (MySQL)

Description: This application is a proof of concept for using Spring Projections(DTO) and inclusive full joins written in native SQL (for MySQL).

Key points:

• define two entities (e.g.,Author andBook in a lazy bidirectional@OneToMany relationship)
• populate the database with some test data (e.g., check the fileresources/data-mysql.sql)
• write interfaces (projections) that contains getters for the columns that should be fetched from the database (e.g., checkAuthorNameBookTitle.java)
• write inclusive full joins queries using native SQL

125. How To Declare Immutable Entities And Store Them In Second Level Cache (e.g.,EhCache)

Description: This application is a sample of declaring an immutable entity. Moreover, the immutable entity will be stored in Second Level Cache viaEhCache implementation.

Key points of declaring an immutable entity:

• annotate the entity with@Immutable (org.hibernate.annotations.Immutable)
• avoid any kind of associations
• sethibernate.cache.use_reference_entries configuration totrue

126. How To Programmatically Customize HikariCP Settings ViaDataSourceBuilder

If you use thespring-boot-starter-jdbc orspring-boot-starter-data-jpa "starters", you automatically get a dependency to HikariCP

Note: The best way to tune the connection pool parameters consist in usingFlexy Pool by Vlad Mihalcea. ViaFlexy Pool you can find the optim settings that sustain high-performance of your connection pool.

Description: This is a kickoff application that set up HikariCP viaDataSourceBuilder. ThejdbcUrl is set up for a MySQL database. For testing purposes, the application uses anExecutorService for simulating concurrent users. Check the HickariCP report revealing the connection pool status.

Key points:

• write a@Bean that returns theDataSource programmatically

Output sample:

127. How To Use Hibernate@NaturalIdCache For Skipping The Entity Identifier Retrieval

Description: This is a SpringBoot - MySQL application that maps a natural business key using Hibernate@NaturalId. This implementation allows us to use@NaturalId as it was provided by Spring. Moreover, this application uses Second Level Cache (EhCache) and@NaturalIdCache for skipping the entity identifier retrieval from the database.

Key points:

• enable Second Level Cache (EhCache)
• annotate entity with@NaturalIdCache for caching natural ids
• optionally, annotate entity with@Cache(usage = CacheConcurrencyStrategy.READ_WRITE, region = "Book") for caching entites as well

Output sample (for MySQL withIDENTITY generator,@NaturalIdCache and@Cache):

128. How To Calculate Non-Persistent Property via JPA@PostLoad

Description: This application is an example of calculating a non-persistent property of an entity based on the persistent entity attributes. In this case, we will use JPA,@PostLoad.

Key points:

• annotate the non-persistent field and property with@Transient
• define a method annotated with@PostLoad that calculates this non-persistent property based on the persistent entity attributes

129. How To Calculate Entity Persistent Property Via Hibernate@Generated

Description: This application is an example of calculating an entity persistent property atINSERT and/orUPDATE time via Hibernate,@Generated.

Key points:

Calculate atINSERT time:

• annotate the corresponding persistent field with@Generated(value = GenerationTime.INSERT)
• annotate the corresponding persistent field with@Column(insertable = false)

Calculate atINSERT andUPDATE time:

• annotate the corresponding persistent field with@Generated(value = GenerationTime.ALWAYS)
• annotate the corresponding persistent field with@Column(insertable = false, updatable = false)

Further, apply:

Method 1:

• if the database schema is generated via JPA annotations (not recommended) then usecolumnDefinition element of@Column to specify as an SQL query expression the formula for calculating the persistent property

Method 2:

• if the database schema is not generated via JPA annotations (recommended way) then add the formula as part of schema inCREATE TABLE

Note: In production, you should not rely oncolumnDefinition. You should disablehibernate.ddl-auto (by omitting it) or set it tovalidate, and add the SQL query expression inCREATE TABLE (in this application, check thediscount column inCREATE TABLE, fileschema-sql.sql). Nevertheless, not evenschema-sql.sql is ok in production. The best way is to rely on Flyway or Liquibase.

130. How To Calculate Non-Persistent Property via Hibernate@Formula

Description: This application is an example of calculating a non-persistent property of an entity based on the persistent entity attributes. In this case, we will use Hibernate,@Formula.

Key points:

• annotate the non-persistent property with@Transient
• annotate the non-persistent field with@Formula
• as the value of@Formula add the SQL query expression that calculates this non-persistent property based on the persistent entity attributes

131. How To Addcreated,createdBy,lastModified AndlastModifiedBy In Entities Via Hibernate

Note: The same thing can be obtained via Spring Data JPA auditing ashere.

Description: This application is an example of adding in an entity the fields,created,createdBy,lastModified andlastModifiedBy via Hibernate support. These fields will be automatically generated/populated.

Key points:

• write anabstract class (e.g.,BaseEntity) annotated with@MappedSuperclass
• in thisabstract class, define a field namedcreated and annotate it with the built-in@CreationTimestamp annotation
• in thisabstract class, define a field namedlastModified and annotate it with the built-in@UpdateTimestamp annotation
• in thisabstract class, define a field namedcreatedBy and annotate it with the@CreatedBy annotation
• in thisabstract class, define a field namedlastModifiedBy and annotate it with the@ModifiedBy annotation
• implement the@CreatedBy annotation viaAnnotationValueGeneration
• implement the@ModifiedBy annotation viaAnnotationValueGeneration
• every entity that want to take advantage ofcreated,createdBy,lastModified andlastModifiedBy will extend theBaseEntity
• store the date-time in UTC

132. Hibernate Envers Auditing (schema-mysql.sql)

Description: Auditing is useful for maintaining history records. This can later help us in tracking user activities.

Key points:

• each entity that should be audited should be annotated with@Audited
• optionally, annotate entities with@AuditTable to rename the table used for auditing
• rely onValidityAuditStrategy for fast database reads, but slower writes (slower than the defaultDefaultAuditStrategy)
• remove (disable)spring.jpa.hibernate.ddl-auto or set it tovalidate for avoiding schema generated from JPA annotations
• createschema-mysql.sql and provide the SQL statements needed by Hibernate Envers
• if the schema is not automatically found, then point it viaspring.jpa.properties.org.hibernate.envers.default_catalog for MySQL orspring.jpa.properties.org.hibernate.envers.default_schema for the rest

133. How To Programmatically Setup Flyway And MySQLDataSource

Note: For production, don't rely onhibernate.ddl-auto (or counterparts) to export schema DDL to the database. Simply remove (disable)hibernate.ddl-auto or set it tovalidate. Rely on Flyway or Liquibase.

Description: This application is a kickoff for setting Flyway and MySQLDataSource programmatically.

Key points:

• for Maven, inpom.xml, add the Flyway dependency
• remove (disable)spring.jpa.hibernate.ddl-auto or set it tovalidate
• configureDataSource and Flyway programmatically

134. How To Migrate PostgreSQL Database Using Flyway - Use The Default Databasepostgres And Schemapublic

Note: For production, don't rely onhibernate.ddl-auto (or counterparts) to export schema DDL to the database. Simply remove (disable)hibernate.ddl-auto or set it tovalidate. Rely on Flyway or Liquibase.

Description: This application is an example of migrating a PostgreSQL database via Flyway for the default databasepostgres and schemapublic.

Key points:

• for Maven, inpom.xml, add the Flyway dependency
• remove (disable)spring.jpa.hibernate.ddl-auto or set it tovalidate
• inapplication.properties, set the JDBC URL as follows:jdbc:postgresql://localhost:5432/postgres
• each SQL file containing the schema update add it inclasspath:db/migration
• each SQL file name it asV1.1__Description.sql,V1.2__Description.sql, ...

135. How To Migrate Schema Using Flyway In PostgreSQL - Use The Default Databasepostgres And Schema Created Viaspring.flyway.schemas

Note: For production, don't rely onhibernate.ddl-auto (or counterparts) to export schema DDL to the database. Simply remove (disable)hibernate.ddl-auto or set it tovalidate. Rely on Flyway or Liquibase.

Description: This application is an example of migrating a schema (bookstore) created by Flyway viaspring.flyway.schemas in the defaultpostgres database. In this case, the entities should be annotated with@Table(schema = "bookstore").

Key points:

• for Maven, inpom.xml, add the Flyway dependency
• remove (disable)spring.jpa.hibernate.ddl-auto or set it tovalidate
• inapplication.properties, set the JDBC URL as follows:jdbc:postgresql://localhost:5432/postgres
• inapplication.properties, addspring.flyway.schemas=bookstore, wherebookstore is the schema that should be created by Flyway in thepostgres database (feel free to add your own database name)
• each entity that should be stored in this database should be annotated with,@Table(schema = "bookstore")
• each SQL file containing the schema update add it inclasspath:db/migration
• each SQL file name it asV1.1__Description.sql,V1.2__Description.sql, ...

136. How To Programmatically Setup Flyway And PostgreSQLDataSource

Note: For production, don't rely onhibernate.ddl-auto (or counterparts) to export schema DDL to the database. Simply remove (disable)hibernate.ddl-auto or set it tovalidate. Rely on Flyway or Liquibase.

Description: This application is a kickoff for setting Flyway and PostgreSQLDataSource programmatically.

Key points:

• for Maven, inpom.xml, add the Flyway dependency
• remove (disable)spring.jpa.hibernate.ddl-auto or set it tovalidate
• configureDataSource and Flyway programmatically

137. How To Auto-Create And Migrate Two Databases In MySQL Using Flyway

Note: For production, don't rely onhibernate.ddl-auto (or counterparts) to export schema DDL to the database. Simply remove (disable)hibernate.ddl-auto or set it tovalidate. Rely on Flyway or Liquibase.

Description: This application is an example of auto-creating and migrating two databases in MySQL using Flyway. In addition, each data source uses its own HikariCP connection pool. In case of MySQL, where a database is the same thing with schema, we create two databases,authorsdb andbooksdb.

Key points:

• for Maven, inpom.xml, add the Flyway dependency
• remove (disable)spring.jpa.hibernate.ddl-auto or set it tovalidate
• inapplication.properties, configure the JDBC URL forbooksdb asjdbc:mysql://localhost:3306/booksdb?createDatabaseIfNotExist=true and forauthorsdb asjdbc:mysql://localhost:3306/authorsdb?createDatabaseIfNotExist=true
• inapplication.properties, setspring.flyway.enabled=false to disable default behavior
• programmatically create twoDataSource, one forbooksdb and one forauthorsdb
• programmatically create twoFlywayDataSource, one forbooksdb and one forauthorsdb
• programmatically create twoEntityManagerFactory, one forbooksdb and one forauthorsdb
• forbooksdb, place the migration SQLs files indb\migration\booksdb
• forauthorsdb, place the migration SQLs files indb\migration\authorsdb

138. Hibernatehi/lo Algorithm And External Systems Issue

Description: This is a Spring Boot sample that exemplifies how thehi/lo algorithm may cause issues when the database is used by external systems as well. Such systems can safely generate non-duplicated identifiers (e.g., for inserting new records) only if they know about thehi/lo presence and its internal work. So, better rely onpooled orpooled-lo algorithm which doesn't cause such issues.

Key points:

• use theSEQUENCE generator type (e.g., in PostgreSQL)
• configure thehi/lo algorithm as inAuthor.java entity
• insert a few records viahi/lo
• insert a few records natively (this acts as an external system that relies onNEXTVAL('hilo_sequence') and is not aware ofhi/lo presence and/or behavior)

Output sample: Running this application should result in the following error:
ERROR: duplicate key value violates unique constraint "author_pkey"
Detail: Key (id)=(2) already exists.

139. How To Generate Sequences Of Identifiers Via Hibernatepooled Algorithm

Note: Rely onpooled-lo orpooled especially if, beside your application, external systems needs to insert rows in your tables. Don't rely onhi/lo since, in such cases, it may cause errors resulted from generating duplicated identifiers.

Description: This is a Spring Boot example of using thepooled algorithm. Thepooled is an optimization ofhi/lo. This algorithm fetched from the database the current sequence value as the top boundary identifier (the current sequence value is computed as the previous sequence value +increment_size). This way, the application will use in-memory identifiers generated between the previous top boundary exclusive (aka, lowest boundary) and the current top boundary inclusive.

Key points:

• use theSEQUENCE generator type (e.g., in PostgreSQL)
• configure thepooled algorithm as inAuthor.java entity
• insert a few records viapooled
• insert a few records natively (this acts as an external system that relies onNEXTVAL('hilo_sequence') and is not aware ofpooled presence and/or behavior)

Conclusion: In contrast to the classicalhi/lo algorithm, the Hibernatepooled algorithm doesn't cause issues to external systems that wants to interact with our tables. In other words, external systems can concurrently insert rows in the tables relying onpooled algorithm. Nevertheless, old versions of Hibernate can raise exceptions caused byINSERT statements triggered by external systems that uses the lowest boundary as identifier. This is a good reason to update to Hibernate latest versions (e.g., Hibernate 5.x), which have fixed this issue.

140. How To Generate Sequences Of Identifiers Via Hibernatepooled-lo Algorithm

Note: Rely onpooled-lo orpooled especially if, beside your application, external systems needs to insert rows in your tables. Don't rely onhi/lo since, in such cases, it may cause errors resulted from generating duplicated identifiers.

Description: This is a Spring Boot example of using thepooled-lo algorithm. Thepooled-lo is an optimization ofhi/lo similar withpooled. Only that, the strategy of this algorithm fetches from the database the current sequence value and use it as the in-memory lowest boundary identifier. The number of in-memory generated identifiers is equal toincrement_size.

Key points:

• use theSEQUENCE generator type (e.g., in PostgreSQL)
• configure thepooled-lo algorithm as inAuthor.java entity
• insert a few records viapooled-lo
• insert a few records natively (this acts as an external system that relies onNEXTVAL('hilo_sequence') and is not aware ofpooled-lo presence and/or behavior)

141. Fetching Associations In Batches Via@BatchSize

Description: This application uses Hibernate specific@BatchSize at class/entity-level and collection-level. ConsiderAuthor andBook entities invovled in a bidirectional-lazy@OneToMany association.

• First use case fetches allAuthor entities via aSELECT query. Further, calling thegetBooks() method of the firstAuthor entity will trigger anotherSELECT query that initializes the collections of the first threeAuthor entities returned by the previousSELECT query. This is the effect of@BatchSize atAuthor's collection-level.

• Second use case fetches allBook entities via aSELECT query. Further, calling thegetAuthor() method of the firstBook entity will trigger anotherSELECT query that initializes the authors of the first threeBook entities returned by the previousSELECT query. This is the effect of@BatchSize atAuthor class-level.

Note: Fetching associated collections in the same query with their parent can be done viaJOIN FETCH or entity graphs as well. Fetching children with their parents in the same query can be done viaJOIN FETCH, entity graphs andJOIN as well.

Key points:

• Author andBook are in a lazy relationship (e.g.,@OneToMany bidirectional relationship)
• Author entity is annotated with@BatchSize(size = 3)
• Author's collection is annotated with@BatchSize(size = 3)

142. How To Use Entity Graphs (@NamedEntityGraph) In Spring Boot

Note: In a nutshell,entity graphs (aka,fetch plans) is a feature introduced in JPA 2.1 that help us to improve the performance of loading entities. Mainly, we specify the entity’s related associations and basic fields that should be loaded in a singleSELECT statement. We can define multipleentity graphs for the same entity andchain any number of entities and even usesub-graphs to create complexfetch plans. To override the currentFetchType semantics there are properties that can be set:

Fetch Graph (default),javax.persistence.fetchgraph
The attributes present inattributeNodes are treated asFetchType.EAGER. The remaining attributes are treated asFetchType.LAZY regardless of the default/explicitFetchType.

Load Graph,javax.persistence.loadgraph
The attributes present inattributeNodes are treated asFetchType.EAGER. The remaining attributes are treated according to their specified or defaultFetchType.

Nevertheless, the JPA specs doesn't apply in Hibernate for the basic (@Basic) attributes.. More detailshere.

Description: This is a sample application of usingentity graphs in Spring Boot.

Key points:

• define two entities,Author andBook, involved in a lazy bidirectional@OneToMany association
• inAuthor entity use the@NamedEntityGraph to define theentity graph (e.g., load in a singleSELECT the authors and the associatated books)
• inAuthorRepositry rely on Spring@EntityGraph annotation to indicate theentity graph defined at the previous step

143. How To Use Entity Sub-graphs In Spring Boot

Note: In a nutshell,entity graphs (aka,fetch plans) is a feature introduced in JPA 2.1 that help us to improve the performance of loading entities. Mainly, we specify the entity’s related associations and basic fields that should be loaded in a singleSELECT statement. We can define multipleentity graphs for the same entity andchain any number of entities and even usesub-graphs to create complexfetch plans. To override the currentFetchType semantics there are properties that can be set:

Fetch Graph (default),javax.persistence.fetchgraph
The attributes present inattributeNodes are treated asFetchType.EAGER. The remaining attributes are treated asFetchType.LAZY regardless of the default/explicitFetchType.

Load Graph,javax.persistence.loadgraph
The attributes present inattributeNodes are treated asFetchType.EAGER. The remaining attributes are treated according to their specified or defaultFetchType.

Nevertheless, the JPA specs doesn't apply in Hibernate for the basic (@Basic) attributes.. More detailshere.

Description: This is a sample application of usingentity sub-graphs in Spring Boot. There is one example based on@NamedSubgraph and one based on the dot notation (.) in an ad-hocentity graph.

Key points:

• define three entities,Author,Book andPublisher (Author andBook are involved in a lazy bidirectional@OneToMany relationship,Book andPublisher are also involved in a lazy bidirectional@OneToMany relationship; betweenAuthor andPublisher there is no relationship)

Using@NamedSubgraph

• inAuthor entity define anentity graph via@NamedEntityGraph; load the authors and the associatated books and use@NamedSubgraph to define asub-graph for loading the publishers associated with these books
• inAuthorRepository rely on Spring@EntityGraph annotation to indicate theentity graph defined at the previous step

Using the dot notation (.)

• inPublisherRepository define an ad-hocentity graph that fetches all publishers with associated books, and further, the authors associated with these books (e.g.,@EntityGraph(attributePaths = {"books.author"}).

144. How To Define Ad-Hoc Entity Graphs In Spring Boot

Note: In a nutshell,entity graphs (aka,fetch plans) is a feature introduced in JPA 2.1 that help us to improve the performance of loading entities. Mainly, we specify the entity’s related associations and basic fields that should be loaded in a singleSELECT statement. We can define multipleentity graphs for the same entity andchain any number of entities and even usesub-graphs to create complexfetch plans. To override the currentFetchType semantics there are properties that can be set:

Fetch Graph (default),javax.persistence.fetchgraph
The attributes present inattributeNodes are treated asFetchType.EAGER. The remaining attributes are treated asFetchType.LAZY regardless of the default/explicitFetchType.

Load Graph,javax.persistence.loadgraph
The attributes present inattributeNodes are treated asFetchType.EAGER. The remaining attributes are treated according to their specified or defaultFetchType.

Nevertheless, the JPA specs doesn't apply in Hibernate for the basic (@Basic) attributes.. More detailshere.

Description: This is a sample application of defining ad-hocentity graphs in Spring Boot.

Key points:

• define two entities,Author andBook, involved in a lazy bidirectional@OneToMany relationship
• theentity graph should load in a singleSELECT the authors and the associatated books
• inAuthorRepository rely on Spring@EntityGraph(attributePaths = {"books"}) annotation to indicate the ad-hocentity graph

145. How To Use Entity Graphs For@Basic Attributes In Hibernate And Spring Boot

Note: In a nutshell,entity graphs (aka,fetch plans) is a feature introduced in JPA 2.1 that help us to improve the performance of loading entities. Mainly, we specify the entity’s related associations and basic fields that should be loaded in a singleSELECT statement. We can define multipleentity graphs for the same entity andchain any number of entities and even usesub-graphs to create complexfetch plans. To override the currentFetchType semantics there are properties that can be set:

Fetch Graph (default),javax.persistence.fetchgraph
The attributes present inattributeNodes are treated asFetchType.EAGER. The remaining attributes are treated asFetchType.LAZY regardless of the default/explicitFetchType.

Load Graph,javax.persistence.loadgraph
The attributes present inattributeNodes are treated asFetchType.EAGER. The remaining attributes are treated according to their specified or defaultFetchType.

Nevertheless, the JPA specs doesn't apply in Hibernate for the basic (@Basic) attributes. In other words, by default, attributes are annotated with@Basic which rely on the default fetch policy. The default fetch policy isFetchType.EAGER. These attributes are also loaded in case offetch graph even if they are not explicitly specified via@NamedAttributeNode. Annotating the basic attributes that should not be fetched with@Basic(fetch = FetchType.LAZY) it is not enough. Both,fetch graph andload graph will ignore these settings as long as we don't addbytecode enhancement as well.

The main drawback consists of the fact the these basic attributes are fetchedLAZY by all other queries (e.g.,findById()) not only by the queries using the entity graph, and most probably, you will not want this behavior.

Description: This is a sample application of usingentity graphs with@Basic attributes in Spring Boot.

Key points:

• define two entities,Author andBook, involved in a lazy bidirectional@OneToMany association
• inAuthor entity use the@NamedEntityGraph to define theentity graph (e.g., load the authors names (only thename basic attribute; ignore the rest) and the associatated books)
• addbytecode enhancement
• annotate the basic attributes that should be ignored by theentity graph with@Basic(fetch = FetchType.LAZY)
• inAuthorRepository rely on Spring@EntityGraph annotation to indicate theentity graph defined at the previous step

146. How To Implement Soft Deletes ViaSoftDeleteRepository In Spring Boot Application

Note: Spring Data built-in support for soft deletes is discussed inDATAJPA-307.

Description: This application is an example of implementing soft deletes in Spring Data style via a repository named,SoftDeleteRepository.

Key points:

• define anabstract class,BaseEntity, annotated with@MappedSuperclass
• inBaseEntity define a flag-field nameddeleted (default this field tofalse or in other words, not deleted)
• every entity that wants to take advantage of soft deletes should extend theBaseEntity classs
• write a@NoRepositoryBean namedSoftDeleteRepository and extendJpaRepository
• override and implement the needed methods that provide the logic for soft deletes (check out the source code)
• repositories of entities should extendSoftDeleteRepository

Output example:

147. How To Implement Concurrent Table Based Queue ViaSKIP_LOCKED In MySQL 8

Description: This application is an example of how to implement concurrent table based queue viaSKIP_LOCKED in MySQL 8.SKIP_LOCKED can skip over locks achieved by other concurrent transactions, therefore is a great choice for implementing job queues. In this application, we run two concurrent transactions. The first transaction will lock the records with ids 1, 2 and 3. The second transaction will skip the records with ids 1, 2 and 3 and will lock the records with ids 4, 5 and 6.

Key points:

• define an entity that acts as a job queue (e.g., see theBook entity)
• inBookRepository setup@Lock(LockModeType.PESSIMISTIC_WRITE)
• inBookRepository use@QueryHint to setupjavax.persistence.lock.timeout toSKIP_LOCKED
• rely onorg.hibernate.dialect.MySQL8Dialect dialect
• run two concurrent transactions to see the effect ofSKIP_LOCKED

148. How To Implement Concurrent Table Based Queue ViaSKIP_LOCKED In PostgreSQL

Description: This application is an example of how to implement concurrent table based queue viaSKIP_LOCKED in PostgreSQL.SKIP_LOCKED can skip over locks achieved by other concurrent transactions, therefore is a great choice for implementing job queues. In this application, we run two concurrent transactions. The first transaction will lock the records with ids 1, 2 and 3. The second transaction will skip the records with ids 1, 2 and 3 and will lock the records with ids 4, 5 and 6.

Key points:

• define an entity that acts as a job queue (e.g., see theBook entity)
• inBookRepository setup@Lock(LockModeType.PESSIMISTIC_WRITE)
• inBookRepository use@QueryHint to setupjavax.persistence.lock.timeout toSKIP_LOCKED
• rely onorg.hibernate.dialect.PostgreSQL95Dialect dialect
• run two concurrent transactions to see the effect ofSKIP_LOCKED

149. JPA Inheritance -JOINED

Description: This application is a sample of JPA Join Table inheritance strategy (JOINED)

Key points:

• this inheritance strategy can be employed via@Inheritance(strategy=InheritanceType.JOINED)
• all the classes in an inheritance hierarchy (a.k.a., subclasses) are represented via individual tables
• by default, subclass-tables contains a primary key column that acts as a foreign key as well - this foreign key references thebase class table primary key
• customizing this foreign key can be done by annotating the subclasses with@PrimaryKeyJoinColumn

150. JPA Inheritance -TABLE_PER_CLASS

Description: This application is a sample of JPA Table-per-class inheritance strategy (TABLE_PER_CLASS)

Key points:

• this inheritance strategy doesn't allow the usage of theIDENTITY generator
• this inheritance strategy can be employed via@Inheritance(strategy=InheritanceType.TABLE_PER_CLASS)
• all the classes in an inheritance hierarchy (a.k.a., subclasses) are represented via individual tables
• each subclass-table stores the columns inherited from the superclass-table (base class)

151. JPA Inheritance -@MappedSuperclass

Description: This application is a sample of using the JPA@MappedSuperclass.

Key points:

• thebase class is not an entity, it can beabstract, and is annotated with@MappedSuperclass
• subclasses of thebase class are mapped in tables that contains columns for the inherited attributes and for their own attibutes
• when thebase class doens't need to be an entity, the@MappedSuperclass is the proper alternative to the JPA table-per-class inheritance strategy

152. How To Avoid Lazy Initialization Issues Caused By Disabling Open Session In View Via Hibernate5Module

Note:Hibernate5Module is anadd-on module for Jackson JSON processor which handles Hibernate datatypes; and specifically aspects of lazy-loading.

Description: By default, in Spring Boot, the Open Session in View anti-pattern is enabled. Now, imagine a lazy relationship (e.g.,@OneToMany) between two entities,Author andBook (an author has associated more books). Next, a REST controller endpoint fetches anAuthor without the associatedBook. But, the View (more precisely, Jackson), forces the lazy loading of the associatedBook as well. Since OSIV will supply the already openedSession, theProxy initializations take place successfully.

Of course, the correct decision is to disable OSIV by setting it tofalse, but this will not stop Jackson to try to force the lazy initialization of the associatedBook entities. Running the code again will result in an exception of type:Could not write JSON: failed to lazily initialize a collection of role: com.bookstore.entity.Author.books, could not initialize proxy - no Session; nested exception is com.fasterxml.jackson.databind.JsonMappingException: failed to lazily initialize a collection of role: com.bookstore.entity.Author.books, could not initialize proxy - no Session.

Well, among the Hibernate5Module features we have support for dealing with this aspect of lazy loading and eliminate this exception. Even if OSIV will continue to be enabled (not recommended), Jackson will not use theSession opened via OSIV.

Key points:

• for Maven, add the Hibernate5Module dependency inpom.xml
• add a@Bean that returns an instance ofHibernate5Module
• annotate theAuthor bean with@JsonInclude(Include.NON_EMPTY) to excludenull or what is considered empty from the returned JSON

Note: The presence of Hibernate5Module instructs Jackson to initialize the lazy associations with default values (e.g., a lazy associated collection will be initialized withnull). Hibernate5Module doesn't work for lazy loaded attributes. For such case considerthis item.

153. How To View Binding Params ViaprofileSQL=true In MySQL

Description: View the prepared statement binding parameters viaprofileSQL=true in MySQL.

Key points:

• inapplication.properties appendlogger=Slf4JLogger&profileSQL=true to the JDBC URL (e.g.,jdbc:mysql://localhost:3306/bookstoredb?createDatabaseIfNotExist=true&logger=Slf4JLogger&profileSQL=true)

Output sample:

154. How To Shuffle Small Result Sets

Description: This application is an example of shuffling small results sets.DO NOT USE this technique for large results sets, since is extremely expensive.

Key points:

• write a JPQLSELECT query and append to itORDER BY RAND()
• each RDBMS support a function similar toRAND() (e.g., in PostgreSQL israndom())

155. The Best Way To Remove Parent And Child Entities Via Bulk Deletions

Description: Commonly, deleting a parent and the associated children viaCascadeType.REMOVE and/ororphanRemoval=true involved several SQL statements (e.g., each child is deleted in a dedicatedDELETE statement). When the number of entities is significant, this is far from being efficient, therefore other approaches should be employed.

ConsiderAuthor andBook in a bidirectional-lazy@OneToMany association. This application exposes the best way to delete the parent(s) and the associated children in four scenarios listed below. These approaches relies onbulk deletions, therefore they are not useful if you want the deletions to take advantage of automatic optimistic locking mechanisms (e.g., via@Version):

Best way to delete author(s) and the associated books viabulk deletions when:

• OneAuthor is in Persistent Context, noBook
• MoreAuthor are in the Persistent Context, noBook
• OneAuthor and the associatedBook are in Persistent Context
• NoAuthor orBook is in Persistent Context

Note: The most efficient way to delete all entities via abulk deletion can be done via the built-indeleteAllInBatch().

156. How ToBulk Updates

Description:Bulk operations (updates and deletes) are faster than batching, can benefit from indexing, but they have three main drawbacks:

• bulk updates/deletes may leave the Persistence Context in an outdated state (it's up to you to prevent this issue by flushing the Persistence Context before update/delete and close/clear it after the update/delete to avoid issues created by potentially unflushed or outdated entities)
• bulk updates/deletes don't benefit of automatic optimistic locking mechanisms (e.g.,@Version is ignored), therefore thelost updates are not prevented (it is advisable to signal these updates by explicitly incrementingversion (if any is present))
• bulk deletes cannot take advantage of cascading removals (CascadeType.REMOVE) andorphanRemoval

This application provides examples ofbulk updates forAuthor andBook entities (betweenAuthor andBook there is a bidirectional lazy@OneToMany association). Both,Author andBook, has aversion field.

157. Why You Should Avoid Unidirectional@OneToMany And Prefer Bidirectional@OneToMany Relationship

Description: As a rule of thumb, unidirectional@OneToMany association is less efficient than the bidirectional@OneToMany or the unidirectional@ManyToOne associations. This application is a sample that exposes the DML statements generated for reads, writes and removal operations when the unidirectional@OneToMany mapping is used.

Key points:

• regular unidirectional@OneToMany is less efficient than bidirectional@OneToMany association
• using@OrderColumn come with some optimizations for removal operations but is still less efficient than bidirectional@OneToMany association
• using@JoinColumn eliminates the junction table but is still less efficient than bidirectional@OneToMany association
• usingSet instead ofList or bidirectional@OneToMany with@JoinColumn relationship (e.g.,@ManyToOne @JoinColumn(name = "author_id", updatable = false, insertable = false)) still performs worse than bidirectional@OneToMany association

158. How To Use Subqeries in JPQLWHERE/HAVING Clause

Description: This application is an example of using subqueries in JPQLWHERE clause (you can easily use it inHAVING clause as well).

Key points:
Keep in mind that subqueries and joins queries may or may not be semantically equivalent (joins may returns duplicates that can be removed viaDISTINCT).

Even if the Execution Plan is specific to the database, historically speaking joins are faster than subqueries among different databases, but this is not a rule (e.g., the amount of data may significantly influence the results). Of course, do not conclude that subqueries are just a replacement for joins that doesn't deserve attention. Tuning subqueries can increases their performance as well, but this is an SQL wide topic. So, benchmark! Benchmark! Benchmark!

As a rule of thumb, prefer subqueries only if you cannot use joins, or if you can prove that they are faster than the alternative joins.

159. How To Execute SQL Functions InWHERE Part Of JPQL Query And JPA 2.1

Note: Using SQL functions inSELECT part (not inWHERE part) of the query can be done ashere.

Description: Starting with JPA 2.1, a JPQL query can call SQL functions in theWHERE part viafunction(). This application is an example of calling the MySQL,concat_ws function, but user defined (custom) functions can be used as well.

Key points:

• use JPA 2.1,function()

160. Calling Stored Procedure That Returns A Value

Description: This application is an example of calling a MySQL stored procedure that returns a value (e.g., anInteger).

Key points:

• rely on@NamedStoredProcedureQuery to shape the stored procedure in the entity
• rely on@Procedure in repository

161. Calling Stored Procedure That Returns A Result Set (Entity And DTO)

Description: This application is an example of calling a MySQL stored procedure that returns a result set. The application fetches entities (e.g.,List<Author>) and DTO (e.g.,List<AuthorDto>).

Key points:

• rely onEntiyManager since Spring Data@Procedure will not work

162. Calling Stored Procedure That Returns A Result Set Via Native Query

Description: This application is an example of calling a MySQL stored procedure that returns a result set (entity or DTO) via a native query.

Key points:

• rely on a native call as@Query(value = "{CALL FETCH_AUTHOR_BY_GENRE (:p_genre)}", nativeQuery = true)

163. Calling Stored Procedure That Returns A Result Set ViaJdbcTemplate

Note: Most probably you'll like to process the result set viaBeanPropertyRowMapper ashere. This is less verbose than the approach used here. Nevertheless, this approach is useful to understand how the result set looks like.

Description: This application is an example of calling a MySQL stored procedure that returns a result set viaJdbcTemplate.

Key points:

• rely onJdbcTemplate andSimpleJdbcCall

164. How To Obtain Auto-Generated Keys

Description: This application is an example of retrieving the database auto-generated primary keys.

Key points:

• JPA style, retrieve the auto-generated keys viagetId()
• JDBC style, retrieve the auto-generated keys viaJdbcTemplate
• JDBC style, retrieve the auto-generated keys viaSimpleJdbcInsert

165. How To Unproxy A Proxy

Description: A Hibernate proxy can be useful when a child entity can be persisted with a reference to its parent (@ManyToOne or@OneToOne association). In such cases, fetching the parent entity from the database (execute theSELECT statement) is a performance penalty and a pointless action. Hibernate can set the underlying foreign key value for an uninitialized proxy. This topic is discussedhere.

A proxy can be unproxied viaHibernate.unproxy(). This method is available starting with Hibernate 5.2.10.

Key points:

• fetch a proxy viaJpaRepository#getOne()
• unproxy the fetched proxy viaHibernate.unproxy()

166. How To ConvertBoolean To Yes/No ViaAttributeConverter

Description: This application is an example of converting aBoolean toYes/No strings viaAttributeConverter. This kind of conversions are needed when we deal with legacy databases that connot be changed. In this case, the legacy database stores the booleans asYes/No.

Key points:

• implement a custom converter viaAttributeConverter

167. How Efficient Is Just@OManyToOne

Note: The@ManyToOne association maps exactly to the one-to-many table relationship. The underlying foreign key is under child-side control in unidirectional or bidirectional relationship.

Description: This application shows that using only@ManyToOne is quite efficient. On the other hand, using only@OneToMany is far away from being efficient. Always, prefer bidirectional@OneToMany or unidirectional@ManyToOne. Consider two entities,Author andBook in a unidirectional@ManyToOne relationship.

Key points:

• Adding a new book is efficient
• Fetching all books of an author is efficient via a JPQL
• Pagination of books is efficient
• Remove a book is efficient
• Even if the fetched collection is not managed,dirty checking mechanism works as expected

168. How To UseJOIN FETCH AndPageable Pagination

Description: Trying to combineJOIN FETCH/LEFT JOIN FETCH andPageable results in an exception of typeorg.hibernate.QueryException: query specified join fetching, but the owner of the fetched association was not present in the select list. This application is a sample of how to avoid this exception.

Key points:

• usecountQuery
• use entity graph

Note: Fixing the above exception will lead to an warning of type HHH000104,firstResult / maxResults specified with collection fetch; applying in memory!. If this warning is a performance issue, and most probably it is, then follow by readinghere.

169. How To Avoid HHH000104 And Use Pagination Of Parent-Child

Description: HHH000104 is a Hibernate warning that tell us that pagination of a result set is tacking place in memory. For example, consider theAuthor andBook entities in a lazy-bidirectional@OneToMany association and the following query:

@Transactional
@Query(value = "SELECT a FROM Author a LEFT JOIN FETCH a.books WHERE a.genre = ?1",
            countQuery = "SELECT COUNT(a) FROM Author a WHERE a.genre = ?1")
Page<Author> fetchWithBooksByGenre(String genre, Pageable pageable);

CallingfetchWithBooksByGenre() works fine only that the following warning is signaled:HHH000104: firstResult / maxResults specified with collection fetch; applying in memory! Obviously, having pagination in memory cannot be good from performance perspective. This application implement a solution for moving pagination at database-level.

Key points:

• use three or two JPQL queries for fetchingPage of entities in read-write or read-only mode
• use two JPQL queries for fetchingSlice orList of entities in read-write or read-only mode

170. What@Transactional(readOnly=true) Actually Do

Description: This application is meant to reveal what is the difference between@Transactional(readOnly = false) and@Transactional(readOnly = true). In a nuthsell,readOnly = false (default) fetches entites inread-write mode (managed). Before Spring 5.1,readOnly = true just setFlushType.MANUAL/NEVER, therefore the automaticdirty checking mechanism will not take action since there is no flush. In other words, Hibernate keep in the Persistent Context the fetched entities and the hydrated (loaded) state. By comparing the entity state with the hydrated state, thedirty checking mechanism can decide to triggerUPDATE statements in our behalf. But, thedirty checking mechanism take place at flush time, therefore, without a flush, the hydrated state is kept in Persistent Context for nothing, representing a performance penalty. Starting with Spring 5.1, theread-only mode is propagated to Hibernate, therefore the hydrated state is discarded immediately after loading the entities. Even if theread-only mode discards the hydrated state the entities are still loaded in the Persistent Context, therefore, forread-only data, relying on DTO (Spring projection) is better.

Key points:

• readOnly = false load data inread-write mode (managed)
• readOnly = true discard the hydrated state (starting with Spring 5.1)

171. Get Transaction Id In MySQL

Description: This application is an example of getting the current database transaction id in MySQL. Only read-write database transactions gets an id in MySQL. Every database has a specific query for getting the transaction id.Here it is a list of these queries.

Key points:

• rely on the following query,SELECT tx.trx_id FROM information_schema.innodb_trx tx WHERE tx.trx_mysql_thread_id = connection_id()

172. Inspect Persistent Context

Description: This application is a sample of inspecting the Persistent Context content viaorg.hibernate.engine.spi.PersistenceContext.

Key points:

• get the current Persistent Context via HibernateSharedSessionContractImplementor
• rely onPersistenceContextAPI

173. How To Extract Tables Metadata

Description: This application is an example of using the Hibernate SPI,org.hibernate.integrator.spi.Integrator for extracting tables metadata.

Key points:

• implementorg.hibernate.integrator.spi.Integrator and overrideintegrate() method to returnmetadata.getDatabase()
• register thisIntegrator viaLocalContainerEntityManagerFactoryBean

174. How To Map@ManyToOne Relationship To A SQL Query Via The Hibernate@JoinFormula

Description: This application is an example of mapping the JPA@ManyToOne relationship to a SQL query via the Hibernate@JoinFormula annotation. We start with two entities,Author andBook, involved in a unidirectional@ManyToOne relationship. Each book has a price. While we fetch a book by id (let's call it bookA), we want to fetch another bookB of the same author whose price is the next smaller price in comparison with bookA price.

Key points:

• fetching the bookB is done via@JoinFormula

175. How To Fetch Data From A MySQL Database View

Description: This application is an example of fetching a read-only MySQL database view in a JPA immutable entity.

Key points:

• the database view is available indata-mysql.sql file
• the entity used to map the database view isGenreAndTitleView.java

176. How To Update/Insert/Delete Data From/In A MySQL Database View

Description: This application is an example of updating, inserting and deleting data in a MySQL database view. Every update/insert/delete will automatically update the contents of the underlying table(s).

Key points:

• the database views are available indata-mysql.sql file
• respectMySQL requirements for updatable and insertable database views

177. How To Prevent A MySQL Database View From Updating/Inserting Rows That Are Not Visible Through It ViaWITH CHECK OPTION

Description: This application is an example of preventing inserts/updates of a MySQL view that are not visible through this view viaWITH CHECK OPTION. In other words, whenever you insert or update a row of the base tables through a view, MySQL ensures that the this operation is conformed with the definition of the view.

Key points:

• addWITH CHECK OPTION to the view
• this application will throw an exception of typejava.sql.SQLException: CHECK OPTION failed 'bookstoredb.author_anthology_view

178. How To Efficiently Assign A Database Temporary Sequence Of Values To Rows

Description: This application is an example of assigning a database temporary sequence of values to rows via the window function,ROW_NUMBER(). This window function is available in almost all databases, and starting with version 8.x is available in MySQL as well.