MyBatis – Database Change Logging

Here's how I handle recording database record changes. Changes are logged to a database table with the old values, new values, and ID of the user that made the change.

I set up an abstract Java class, StandardTable, that is used to base the classes for the main database tables:

/**
 * 
 * @author rgolebiowski
 *All of the main table classes are extended from this class.
 *All of the main database tables use ID field for the primary key
 *
 */
public abstract class StandardTable {
    private Integer ID;
    
    public Integer getID() {
        return ID;
    }

    public void setID(Integer ID) {
        this.ID = ID;
    } 
}

All of the Java classes for the main tables are extended from StandardTable. Example:

/**
 * 
 * @author rgolebiowski
 *Model for the AppTable table
 *
 */
public class AppTable extends StandardTable implements Serializable{

I set up an abstract Java class, TableRecord, to encapsulate standard methods for reading and saving StandardTable records:

/**
 * Abstract class for reading in and saving (insert or update) a record
 * @author rgolebiowski
 *
 */
public abstract class TableRecord {
 protected StandardTable tblRecord;//record being viewed or edited
 
 public abstract void loadRecord(Integer id);
 public abstract void saveRecord(AppUser appUser);
 
 public Object getTblRecord() {
  return tblRecord;
 }

 public void setTblRecord(Object tblRecord) {
  this.tblRecord = (StandardTable) tblRecord;
 }
}

All of the Java classes for viewing and editing records from the main tables are extended from TableRecord . Example:

/**
 * TableRecord for the AppTable database
 * @author rgolebiowski
 *
 */
public class AppTableRecord extends TableRecord {
 
 public AppTableRecord(){  
 }
 
 public AppTableRecord(AppTable tblRecord){
  this.setTblRecord(tblRecord);
 }

 /**
  * @param id: Record ID for the record to be loaded
  */
 public void loadRecord(Integer id) {
  GenericDataService genService = new GenericDataService("AppTableMapper");
  this.setTblRecord(genService.getByID(id));
 }

 /**
  * @param appUser: User that is updating the table
  */
 public void saveRecord(AppUser appUser) {
   try {
     //Switch MyBatis to map to the AppTAble database
     GenericDataService genService = new GenericDataService("AppTableMapper");
     //get the current record
     Object current = genService.getByID(((AppTable) this.tblRecord).getID());
     //log changes
     LogChange.log(appUser.getUserName(), current, this.tblRecord); 
     //if this is a current record then update
     if(((AppTable) this.getTblRecord()).getID() != null){
       genService.updateByID(this.getTblRecord());
     }
     else{
       //do an insert an set the record ID with the returned record ID
       this.setTblRecord(genService.insert(this.getTblRecord()));
     }
   } catch (IntrospectionException e) {
    e.printStackTrace();
   } catch (IllegalAccessException e) {
    e.printStackTrace();
   } catch (InvocationTargetException e) {
    e.printStackTrace();
   }
 }
}

Class for logging the database changes:

/**
 * Logger for databases changes
 * @author rgolebiowski
 *
 */
public class LogChange {

 /**
  * Constructs the string used to log the database changes
  * @param userID: UserID of the user making the change
  * @param bOld: Old object
  * @param bNew: New object
  * @throws IntrospectionException
  * @throws IllegalAccessException
  * @throws InvocationTargetException
  */
 public static void log(String userID, Object bOld, Object bNew) throws IntrospectionException, IllegalAccessException, InvocationTargetException {
    String res = "";//String to hold the change record
    boolean changed = false;
    try {
   if(bOld != null){ //if this is an update
       BeanInfo beanInfo = Introspector.getBeanInfo(bOld.getClass());
       res = bOld.getClass().getSimpleName() + " - ";
       //loop and compare old values with new values and add them to our string if they are changed
       for (PropertyDescriptor prop : beanInfo.getPropertyDescriptors()) {
           Method getter = prop.getReadMethod();
           Object vOld = getter.invoke(bOld); //old value
           Object vNew = getter.invoke(bNew); //new value
           if (vOld == vNew || (vOld != null && vOld.equals(vNew))) {
             continue;
           }
           changed = true;
           res = res + "(" + prop.getName()  + ", " +  vOld  + ", " + vNew + ")";
       }
     }
     else{//this is a new record
           changed = true;     
    BeanInfo beanInfo = Introspector.getBeanInfo(bNew.getClass());
    res = bNew.getClass().getSimpleName() + " - "; 
    //loop and create the string to log the new record
    for (PropertyDescriptor prop : beanInfo.getPropertyDescriptors()) {
      if(prop.getName().equals("class")) continue;       
      Method getter = prop.getReadMethod();
      Object vNew = getter.invoke(bNew);
      if (vNew != null) {
         res = res + "(" + prop.getName()  + ", " + vNew + ")";
      }
    }  
     }
     if (changed){
        logToDB(userID, res);
     }
  }
  catch (IllegalArgumentException e) {
    e.printStackTrace();
  }
  }
   
   /**
    * Saves the record to the database
    * @param userID: UserID of the user making the change
    * @param message: This string contains the changes
    */
   private static void logToDB(String userID, String message){
  ChangeLog changeLog = new ChangeLog();
  changeLog.setUserID(userID);
  changeLog.setMessage(message);
  SqlSessionFactory sqlSessionFactory = MyBatisConnectionFactory_INform.getSqlSessionFactory();
  SqlSession session = sqlSessionFactory.openSession();
  try {
      session.insert("ChangeLogMapper.insert", changeLog);
      session.commit();
  } finally {
      session.close();
  } 
   }
}

Then in my Strusts action I just have a simple method that calls saveRecord for my class to save the record to the database and record the changes. I just save it and forget about it!

    /**
     * Used to save the record to the database
     * @return SUCCESS
     * @throws Exception
     */
    public String saveRecord() throws Exception {
     this.tableRecord.saveRecord((AppUser) SessionService.getAttributeFromSession(request, SessionService.AppUser));
     this.resMsg = "Record saved.";
     return SUCCESS;
    }

The SQL script to create the ChangeLog database:

CREATE TABLE [dbo].[ChangeLog](
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [Date] [datetime] NULL,
 [UserID] [varchar](50) NULL,
 [Message] [varchar](max) NULL,
 CONSTRAINT [PK_ChangeLog] PRIMARY KEY CLUSTERED 
(
 [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[ChangeLog] ADD  CONSTRAINT [DF_ChangeLog_Date]  DEFAULT (getdate()) FOR [Date]
GO