Let me give you an introduction. If you want to skip and jump quickly into the implementation part then scroll down to the Examples section. First of all, what is an SQL Expression? It’s a combination of one or more values, operators and SQL functions that evaluates to a value. We usually use queries combined with some expressions to fetch data. Some commonly used expressions types are Boolean, Numeric, Date, etc.
There are situations in which you have to use complex expressions to read (for displaying) and write (to database) data. One of the most appropriate examples for this is an encrypted column value in your database. Your application might take sensitive information like users’ credit card details and passwords as input. Ideally such details should be encrypted and stored. So you can’t write to or read from database without performing some operations like encryption and decryption. Be it in code or query, it’s tedious to have these read and write operations performed at all places, especially if you are working on a very large project.
What if you can perform such operations on any database column by just using an annotaion in your entity? Sounds like a lot of time and effort saved, right? Hibernate’s @ColumnTransformer annotaion is what we are talking about. It helps you map your entity field to a database column which stores the result of above-mentioned operation without having any code or query changes. @ColumnTransformer helps us to provide a custom SQL expression which is used to read the value from and write a value to a database column.
Example-1:
@ColumnTransformer(read = "AES_DECRYPT(UNHEX(cardNumber), 'SECRET KEY')", write = "HEX(AES_ENCRYPT(?, 'SECRET KEY'))")
private String cardNumber; //field to store the encrypted form of credit card number
With the above code in place, when we retrieve the column cardNumber using a query or when the entity containing cardNumber is loaded, Hibernate executes the AES_DECRYPT function to convert the card number into readable format for display. Also, when we save, Hibernate executes the AES_ENCRYPT function. What we need to do is just annotate the specific entity field with the @ColumnTransformer annotaion. Please note, AES_DECRYPT returns the result in a binary form so we use HEX and UNHEX functions to deal with the value in hexadecimal format.
Example-2:
@ColumnTransformer(write = "DATE_FORMAT(?,'%Y-%m-%d %H:%i')")
private Date createdDate; //to store the date of creation
Date and Time column values in database are stored in a specific format. Your application may accept a date from users or create a Date object at many places. You need to convert those into a database acceptable format. Here @ColumnTransformer helps you to achieve it by just annotating the date field.
@ColumnTransformer annotation has 3 optional attributes – forColumn, read and write. The forColumn attribute can be left out if the annotated field is mapped to a single column in the database. We already saw how to use read and write attributes of the annotation.
Note: In the above examples, we used MySQL specific functions like AES_ENCRYPT(), AES_DECRYPT() and DATE_FORMAT() for encryption, decryption and formatting the date respectively. You should use the functions specific to the database you use. With that said, you can understand the disadvatnage of using database engine specific functions along with @ColumnTransformer. Yes, you lose Hibernate’s database independence feature.