Building a Distributed Audit Log with YugabyteDB
Introduction In this article, we are going to see what is the best way you can build an Audit Log using YugabyteDB. And what’s even more interesting is that this solution is almost the same on PostgreSQL. The only difference is how we are building the audit log table PRIMARY KEY, but everything else is exactly the same, demonstrating how easily you can migrate from PostgreSQL to YugabyteDB.
How to map polymorphic JSON objects with JPA and Hibernate
Introduction In this article, I’m going to show you how you can map polymorphic JSON objects when using JPA and Hibernate. Since Hibernate doesn’t support JSON natively, I’m going to use the Hypersistence Utils library to achieve this goal.
Use Hibernate Dynamic Update for JSON properties
Introduction In this article, I’m going to explain why you should always use the Hibernate Dynamic Update feature for entities that map JSON properties. Since Hibernate ORM does not offer support for JSON column types, we are going to use the Hypersistence Utils library, which provides a JsonType that allows you to map String, Map, List, JsonNode, Java Records, or POJOs on JSON columns, no matter if you are using PostgreSQL, MySQL, Oracle, SQL Server, or H2.
How to index JSON columns using MySQL
Introduction In this article, I’m going to explain how we can index JSON columns when using MySQL. While other relational database systems provide GIN (Generalized Inverted Index) indexes, MySQL allows you to index a virtual column that mirrors the JSON path expression you are interested in indexing.
SQL Server OPENJSON – Map JSON to a relational table
Introduction In this article, I’m going to explain how the SQL Server OPENJSON function works and how it can help you transform a JSON object into a relational database table. When using a relational database system, it’s a very good idea to design the database schema according to the relational model. However, there are specific situations when the relational model is way too strict, and that’s when a JSON column type might be very handy. For instance, as I explained in this article, when designing an audit log table, it’s much more… Read More
How to get a JSON property value using PostgreSQL
Introduction In this article, I’m going to explain how you can get a JSON property value using the ->> PostgreSQL operator. This way, we can transform a JSON object that’s stored in a json or jsonb column into a virtual relational database table. While it’s a good idea to design the database schema according to the relational model, there are also situations when the relational model is way too strict. For instance, as I explained in this article, when implementing an audit log mechanism using database triggers, it’s very convenient to store… Read More
Mapping Java Records to JSON columns using Hibernate
Introduction In this article, I’m going to explain how we can map Java Records to JSON columns when using Hibernate. Because Hibernate ORM does not offer built-in support for JSON, we are going to use the Hypersistence Utils library, which allows you to map entity attributes to JSON column types, no matter if you are using Oracle, SQL Server, PostgreSQL, or MySQL.
SQL Server audit logging using triggers
Introduction In this article, we are going to see how we can implement an audit logging mechanism using SQL Server database triggers to store both the previous and the current state of a given target table record in JSON column types.
MySQL JSON_TABLE – Map a JSON object to a relational database table
Introduction In this article, I’m going to explain how the MySQL JSON_TABLE function works, and how you can use it to transform a JSON object into a relational database table. When using a relational database system, it’s best to design the database schema according to the relational model. However, there are certain use cases when the relational model is too strict, and we are better off storing data in a JSON column type. For instance, as I explained in this article, when designing an audit log table, it’s much more convenient to… Read More
How to encrypt and decrypt JSON properties with JPA
Introduction In this article, we are going to see how we can encrypt and decrypt JSON properties when using JPA and Hibernate. While encrypting the entire column value is very straightforward, when it comes to JSON columns, we need to preserve the JSON object structure while only encrypting the JSON property values.