Introduction In this article, we are going to see how you can map SQL arrays to JPA entity attributes when using Hibernate. Hibernate custom Types allow you to map all sorts of database-specific column types, like IP address, JSON columns, bit sets, or SQL arrays. However, while you can create your own custom Hibernate Types, for mapping PostgreSQL arrays, you don’t need to implement your own Hibernate Type. All you need to do is use the Hibernate Types open-source project.
Introduction Each database application is unique. While most of the time, deleting a record is the best approach, there are times when the application requirements demand that database records should never be physically deleted. So who uses this technique? For instance, StackOverflow does it for all Posts (e.g. Questions and Answers). The StackOverflow Posts table has a ClosedDate column which acts as a soft delete mechanism since it hides an Answer for all users who have less than 10k reputation. If you’re using Oracle, you can take advantage of its Flashback capabilities,… Read More
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.
Introduction I’ve just finished the wonderful SQL Antipatterns book by Bill Karwin. The book is a must-have reference for any developer that has to interact with a relational database system. This post is a review of what this book is all about and why you should be interested in reading it.
Introduction While reading the wonderful SQL Antipatterns book by Bill Karwin, which is a great reference for any developer that needs to interact with a Relational Database System, I found an example where the SQL PIVOT clause would work like a charm. In this post, I’m going to explain how to transpose a ResultSet using PIVOT so that rows become columns.
Introduction As I explained in this previous article, you can map calculated properties using Hibernate @Formula, and the value is generated at query time. In this post, you’ll see how you can use a custom SQL fragment to customize the JOIN relationship between two entities, or an entity and a collection of embeddable types.
Introduction One of my readers asked me to answer the following StackOverflow question. Right from the start, I noticed that the entity query was constructed by concatenating strings, which can expose your application to SQL Injection attacks. Unfortunately, I’ve been seeing this problem over and over throughout my career, and not all developers are aware of the serious risk associated with SQL Injection. For this reason, this post is going to demonstrate what damage can SQL Injection do to your system.
Introduction When your relation database system uses multiple schemas, then you can instruct Hibernate to use a global schema using the hibernate.default_schema configuration property: While Hibernate can imply the default schema whenever dealing with entity queries, for native queries, you need a little trick. This post is going to demonstrate how you can imply the default schema for native SQL queries as well.
Introduction The JDBC API has always been cumbersome and error-prone and I’ve never been too fond of using it. The first major improvement was brought by the Spring JDBC framework which simply revitalized the JDBC usage with its JdbcTemplate or the SqlFunction classes, to name a few. But Spring JDBC doesn’t address the shortcoming of using string function or input parameters names and this opened the door for type-safe SQL wrappers such as jOOQ. JOOQ is the next major step towards a better JDBC API and ever since I started using it… Read More