How does a relational database work

Introduction While doing my High-Performance Java Persistence training, I came to realize that it’s worth explaining how a relational database works, as otherwise, it is very difficult to grasp many transaction-related concepts like atomicity, durability, and checkpoints. In this post, I’m going to give a high-level explanation of how a relational database works internally while also hinting some database-specific implementation details.

How to run integration tests at warp speed using Docker and tmpfs

Introduction In this article, I’m going to show you how to run integration tests on PostgreSQL, MySQL, MariaDB 20 times faster using Docker and mapping the data folder on tmpfs. As previously explained, you can run database integration tests 20 times faster! The trick is to map the data directory in memory, and my previous article showed you what changes you need to do when you have a PostgreSQL or MySQL instance on your machine. In this post, I’m going to expand the original idea, and show you how you can achieve… Read More

Why should not use the AUTO JPA GenerationType with MySQL and Hibernate

Introduction As I already mentioned, you should never use the TABLE identifier generator since it does not scale properly. In this post, I’ll show you why you should not rely on the AUTO GenerationType strategy if you’re Hibernate application uses MySQL.

How does MySQL result set streaming perform vs fetching the whole JDBC ResultSet at once

Introduction I read a very interesting article by KreŇ°imir Nesek regarding MySQL result set streaming when it comes to reducing memory usage. Mark Paluch, from Spring Data, asked if we could turn the MySQL result set streaming by default whenever we are using Query#stream or Query#scroll. That being said, the HHH-11260 issue was created, and I started working on it. During Peer Review, Steve Ebersole (Hibernate ORM team leader) and Sanne Grinovero (Hibernate Search Team Leader) expressed their concerns regarding making such a change. First of all, the MySQL result set streaming… Read More

How to map JSON objects using generic Hibernate Types

Introduction In this article, we are going to see how you can map a JSON column to a JPA entity attribute using the Hypersistence Utils open-source project. While you can create your own custom Hibernate Types, for mapping JSON column types on Oracle, SQL Server, PostgreSQL, or MySQL, you don’t need to implement your own Hibernate Type since the Hypersistence Utils project already offers this functionality.

How to run database integration tests 20 times faster

Introduction In-memory databases such as H2, HSQLDB, and Derby are great to speed up integration tests. Although most database queries can be run against these in-memory databases, many enterprise systems make use of complex native queries which can only be tested against an actual production-like relational database. In this post, I’m going to show you how you can run PostgreSQL and MySQL integration tests almost as fast as any in-memory database.

MySQL metadata locking and database transaction ending

Introduction As previously explained, every SQL statement must be executed in the context of a database transaction. For modifying statements (e.g. INSERT, UPDATE, DELETE), row-level locks must be taken to ensure recoverability and avoid the data anomalies. Next, I’ll demonstrate what can happen when a database transaction is not properly ended.

How does the MySQL JDBC driver handle prepared statements

Prepared statement types While researching for the Statement Caching chapter in my High-Performance Java Persistence book, I got the chance to compare how Oracle, SQL Server, PostgreSQL and MySQL handle prepare statements. Thanks to Jess Balint (MySQL JDBC driver contributor), who gave a wonderful answer on StackOverflow, I managed to get a better understanding of how MySQL handles prepared statements from a database performance point of view.