How automatically update fields like created/modified Date when using MyBatis?

Question

I am using MyBatis and want to implement 2 fields on every table 'created', 'modified'. Both off them are date fields. Is there a way of automatically update these fields on an insert or update? Of course, I can adjust the mappings, but I was wondering if there is a more generic and DRY way of doing this?

Solution 1

No, mybatis has no mechanism to do this automatically without you coding your sql maps to update the columns.

One alternative would be database triggers. I'm not certain I would recommend that though, we just code it in the sql maps.

You could code it in the SQL maps like this,

<insert id="someInsert">    
     insert into dummy_table    
     ( 
         SOME_COLUMN,
         CREATED_DT    
     )    
     values
    (
        #{someValue},
        sysdate    
     ) 
</insert>

or,

<update id="someUpdate">
   update some_table
   set some_column = #{someValue}, modified=sysdate
   where some_id = #{someId}
</update>

Solution 2

you can use mybatis Interceptor
here is my example (using springboot):
in mycase, BaseEntity is the super class of all entity,i need do some thing before update or insert to database by mybatis.
step 1: create init method in BaseEntity for update or insert

public class BaseEntity{
    private Date created;
    private Date updated;

    //getter,setter
    public void initCreateEntity() {  
        this.created = new Date()
        this.updated = new Date()
    }

    public void initUpdateEntity() {  
        this.created = new Date()
        this.updated = new Date()
    }
  }  

step 2: add a mybatis interceptor

import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlCommandType;
import org.apache.ibatis.plugin.*;

    /**
     * add time interceptor for update
     */
    @Intercepts(@Signature(type = Executor.class, method = "update", args={MappedStatement.class, Object.class}))
    public class BaseEntityInterceptor implements Interceptor {
        @Override
        public Object intercept(Invocation invocation) throws Throwable {
            MappedStatement mappedStatement = (MappedStatement)invocation.getArgs()[0];
            // get sql
            SqlCommandType sqlCommandType = mappedStatement.getSqlCommandType();
            // get parameter , this is the target object that you want to handle
            Object parameter = invocation.getArgs()[1];
            // make sure super class is BaseEntity 
            if (parameter instanceof BaseEntity) {
                //init 
                BaseEntity baseEntity = (BaseEntity) parameter;
                if (SqlCommandType.INSERT.equals(sqlCommandType)) {
                    baseEntity.initCreateEntity();
                } else if (SqlCommandType.UPDATE.equals(sqlCommandType)) {
                    baseEntity.initUpdateEntity();
                }
            }
    
            return invocation.proceed();
       }
    
        @Override
        public Object plugin(Object o) {
            return Plugin.wrap(o, this);
        }
    
        @Override
        public void setProperties(Properties properties) {
    
        }
      }

step 3: add to bean Context in springboot config

@Configuration
public class MyBatisConfig {

    @Bean
    public BaseEntityInterceptor baseEntityInterceptor() {
        return new BaseEntityInterceptor();
    }
}

step 4: Dao and Mapper.xml

//base update or insert sql incloude column created and updated

eg:Dao

@Mapper
   public interface BaseDao {
      int update(BaseEntity baseEntity);
   }

Mapper.xml

    <update id="update" parameterType="com.package.to.BaseEntity">
        update baseentity_table set created = #{createTime, jdbcType=TIMESTAMP}
updated = #{createTime, jdbcType=TIMESTAMP} 
    </update>

step 5: test

   baseDao.update(new BaseEntity);

More information here: https://mybatis.org/mybatis-3/configuration.html#plugins

Comments

[Marco]

Makes perfect sense! Thanks for the example. It seems easy to do at then end :). I was on the wrong track. Another question that arises is what is prefered to store these queries like this. I started using annotations, but i tend to go the xml mapper way now. I have little experience so any thoughts on this are appraciated. Again thanks a lot for the example!

[Marco]

I am wondering if i also should make the date values part of the object or not? Do you embedded data attributes in the actual business object or do you just modifiy the record on the DB and look at this being meta data which is not required in the domain/object model?

[Andy]

Depends, in our use case we have no need for in the domain model for the created date (yet), so we just store it in the database for our reference. I think I would prefer using sysdate (or another database command) to grab the time stamps for inserts and update. Then just using the date objects in your model when querying for data. I don't think either approach you mention is bad though.