Creating Tables for Hibernate Envers with Liquibase


Posted by Steven

Recently, I wanted to keep the change history of entities saved via Hibernate and also have a versioned database. Hence, I used Hibernate Envers and Liquibase.

The tables for envers, both the revinfo table and the _AUD tables for each entity, have to be included in the changeset of Liquibase:

  1. <?xml version="1.0" encoding="UTF-8"?>
  2. <databaseChangeLog
  3. xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  4. xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
  5. xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
  6. http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd">
  7.  
  8. <property name="long_type" value="bigint" dbms="postgresql"/>
  9. <property name="long_type" value="long" dbms="h2"/>
  10.  
  11. <changeSet id="CreateBasicSchema" author="Steven Schwenke">
  12.  
  13. <createSequence sequenceName="hibernate_sequence" startValue="0" incrementBy="1"/>
  14.  
  15. <createTable tableName="revinfo">
  16. <column name="rev" type="integer" autoIncrement="true">
  17. <constraints primaryKey="true"/>
  18. </column>
  19. <column name="revtstmp" type="bigint"/>
  20. </createTable>
  21.  
  22. <createTable tableName="stuff">
  23. <column name="id" type="${long_type}">
  24. <constraints nullable="false" unique="true" primaryKey="true"/>
  25. </column>
  26. <column name="text" type="varchar(36)">
  27. <constraints nullable="false"/>
  28. </column>
  29. </createTable>
  30.  
  31. <createTable tableName="stuff_aud">
  32. <column name="id" type="${long_type}">
  33. <constraints nullable="false" />
  34. </column>
  35. <column name="rev" type="integer">
  36. <constraints referencedTableName="revinfo"
  37. foreignKeyName="fk_brands_stuff_revinfo"
  38. referencedColumnNames="rev"
  39. nullable="false"/>
  40. </column>
  41. <column name="revtype" type="integer">
  42. <constraints nullable="false"/>
  43. </column>
  44. <column name="stuff" type="varchar(36)">
  45. <constraints nullable="true"/>
  46. </column>
  47. </createTable>
  48. <addPrimaryKey tableName="stuff_aud" columnNames="id, rev" />
  49. </changeSet>
  50. </databaseChangeLog>

The autoIncrement="true" in the revinfo table is quite important. Without it, new revisions don't have an ID and this exception occurs:

  1. o.h.engine.jdbc.spi.SqlExceptionHelper : SQL Error: 23502, SQLState: 23502
  2. o.h.engine.jdbc.spi.SqlExceptionHelper : NULL nicht zulässig für Feld "REV"
  3. NULL not allowed for column "REV"; SQL statement:
  4. /* insert org.hibernate.envers.DefaultRevisionEntity */ insert into revinfo (rev, revtstmp) values (null, ?) [23502-200]
  5. org.springframework.dao.DataIntegrityViolationException: could not execute statement ...

The best way to prevent this exception from happening is the mentionend autoIncrement. However, it's also possible to create an entity for the revision:

  1. import org.hibernate.envers.RevisionEntity;
  2. import org.hibernate.envers.RevisionNumber;
  3. import org.hibernate.envers.RevisionTimestamp;
  4.  
  5. import javax.persistence.*;
  6.  
  7. @RevisionEntity
  8. @Table(name = "revinfo")
  9. public class Revision {
  10. @Id
  11. @GeneratedValue(strategy = GenerationType.SEQUENCE)
  12. @Column(nullable = false, name = "REV")
  13. @RevisionNumber
  14. private long rev;
  15.  
  16. @Column(name = "revtstmp")
  17. @RevisionTimestamp
  18. private long timestamp;
  19.  
  20. public long getRev() {
  21. return rev;
  22. }
  23.  
  24. public void setRev(long rev) {
  25. this.rev = rev;
  26. }
  27.  
  28. public long getTimestamp() {
  29. return timestamp;
  30. }
  31.  
  32. public void setTimestamp(long timestamp) {
  33. this.timestamp = timestamp;
  34. }
  35. }


Here's a great article about "The best way to implement an audit log using hibernate envers" by Vlad Mihalcea.

Category: 
Share: