How to display user-friendly entity history

Most systems need data audit/versioning. This usually means that for every audited table FOO there is table FOO_AUD, which contains the current and all previous values of records from FOO. FOO_AUD can be populated using Hibernate Envers or simply by triggers on table FOO.

The question is, how to present historical/audit data to the user? Suppose we have a bug tracking system with tables ISSUE and ISSUE_AUD. Obviously we could display the results of the following query:
select * from issue_aud where issue_id = 10;

 rev  | revtype | issue_id |    issue_title    | issue_status | issue_assignee
 1000 |       0 |       10 | Bar always fails! | Open         |
 1011 |       1 |       10 | Bar always fails! | In Progress  | Developer A
 1037 |       1 |       10 | Bar always fails! | Resolved     | Reporter B
 1057 |       1 |       10 | Bar always fails! | Closed       |
but that's not readable and user-friendly. Ideally, we would like to present data in the following format:
 rev  |    property    | prev_value  |    curr_value
 1000 | issue_status   |             | Open
 1000 | issue_title    |             | Bar always fails!
 .... | ...........    | ..........  | .................
In this post I would like to show you how this can be done using SQL query only.


7 tips to improve data quality using database constraints

When it comes to validating application's data, most Java developers think about frontend/backend validation, JSR 303 etc. Sadly, many ignore the database, even though it allows some easy to use, declarative validations.


The number one mistake when using Hibernate

Hibernate is one of the most popular ORM solutions for Java, yet many developers are surprised when they find out that it is state based not SQL based framework.

This means that when you call Hibernate methods like session.save(entity), session.update(entity) etc. it does not go directly to the DB to issue an INSERT/UPDATE etc. but only associates the object passed as an argument with its session and assigns some internal state to it.

At a later point in time (typically before transaction commits) Hibernate performs a flush - it synchronizes the state of objects held in memory with the database, executing any number of INSERT/UPDATE/DELETE statements.

Hibernate is very easy to use. IMHO it is even too easy to use, because it makes an impression, that it is a simple framework. It's definitely not, there is a lot of complexity under the hood and if you ignore it, you can easily shoot yourself in the foot.


Writing more testable code - a simple trick

While writing tests for service layer you can:
  • create unit tests with mocked dependencies
  • create integration tests with real dependencies (typically - DB)
Both solutions have their pros and cons and neither is 100% convenient. This post shows an alternative approach.