Seize the deal!
Caching Best Practices
Imagine having a tool that can automatically detect if you are using JPA and Hibernate properly. Hypersistence Optimizer is that tool!
In this article, we are going to test the MySQL 8 implementation of custom SQL CHECK constraints. Although the CHECK clause is a standard SQL feature, prior to MySQL 8.0.16, the clause was parsed and ignored, so this functionality could only be emulated via BEFORE INSERT and UPDATE triggers.
As I explained in this article, custom SQL CHECK constraints are very useful to ensure non-nullability constraints for JPA entity subclass-specific attributes when using the SINGLE TABLE JPA inheritance strategy.
To understand the problem, consider we have the following domain model:
When using the SINGLE TABLE inheritance strategy, the base class and all subclasses share the same database table:
However, if the subclass-specific attributes like the
validUntil attributes of the
Announcement entities are required to be non-nullable, we cannot just add the
NOT NULL constraint at the SQL column level as, otherwise, we won’t be able to add a
post record as the
NOT NULL constraint will fail.
That’s exactly where SQL custom CHECK constraints can help us. Therefore, we could add the following two CHECK constraints:
ALTER TABLE topic ADD CONSTRAINT post_content_check CHECK ( CASE WHEN DTYPE = 'Post' THEN CASE WHEN content IS NOT NULL THEN 1 ELSE 0 END ELSE 1 END = 1 ) ALTER TABLE topic ADD CONSTRAINT announcement_validUntil_check CHECK ( CASE WHEN DTYPE = 'Announcement' THEN CASE WHEN validUntil IS NOT NULL THEN 1 ELSE 0 END ELSE 1 END = 1 )
Now, if we try to persist a
Post entity without a valid
MySQL 8.0.16 is going to throw the following
INSERT INTO topic ( board_id, createdOn, owner, title, content, DTYPE, id ) VALUES ( NULL(BIGINT), '2019-07-03 10:40:03.933', NULL(VARCHAR), NULL(VARCHAR), NULL(VARCHAR), 'Post', 4 ) -- SQL Error: 1644, SQLState: 45000 -- Post content cannot be NULL
The same happens if we try to update an existing
Post entity and set the
content attribute to
Post post = entityManager .createQuery( "select p " + "from Post p " + "where p.content = :content", Post.class) .setParameter("content", "Best practices") .getSingleResult(); post.setContent(null);
MySQL 8.0.16 is going to throw a
ConstraintViolationException because the custom
post_content_check CHECK constraint fails to validate the UPDATE statement:
UPDATE topic SET board_id = 1, createdOn = '2019-07-03 10:45:53.581', owner = 'John Doe', title = 'Inheritance', content = NULL(VARCHAR) WHERE id = 2 -- SQL Error: 1644, SQLState: 45000 -- Post content cannot be NULL
If you want to see what you had to do to emulate the
CHECK constraint on MySQL prior to 8.0.16. then check out this article.
If you enjoyed this article, I bet you are going to love my upcoming Online Workshops!
- Caching Best Practices with JPA and Hibernate (2.5 hours) on the 30th of September
- High-Performance SQL (4 hours) on the 6th of October in collaboration with Voxxed Days Ticino
- High-Performance SQL (12 hours) starting on the 28th of October in collaboration with Bouvet
If you are still using an older version of MySQL, you should definitely consider upgrading to the 8.0 version as there are many features that have been added to MySQL in this major version increment (e.g. Window Functions, SKIP LOCKED and NOWAIT, CTE or Recursive CTE).
Hypersistence Optimizer 2.2 has been released!