Automate Audit Fields in Spring Boot with MyBatis: A Generic Solution for CRUD Operations

In modern web applications, maintaining audit trails for data changes is crucial for tracking who made changes and when. If you’re building a Spring Boot application with REST APIs, using MyBatis for data persistence, and MSSQL as your database, you might face the challenge of managing common audit fields like createdDate, createdBy, updatedDate, and updatedBy across multiple tables and CRUD operations. Manually handling these fields in every query can be repetitive and error-prone. In this blog post, we’ll explore a generic, scalable solution to automate audit fields using a MyBatis interceptor, handle concurrent user requests, and support bulk inserts — all while keeping your code clean and maintainable.

Also Read: Externalizing MyBatis SQL Queries in a Spring Boot Application

Why Automate Audit Fields in Spring Boot with MyBatis?

Audit fields such as createdDate, createdBy, updatedDate, and updatedBy are essential for tracking data modifications in applications. When building REST APIs that perform CRUD operations on multiple tables in a Spring Boot application with MyBatis and MSSQL, you might find yourself repeatedly adding these fields to every insert and update query. This approach is not only tedious but also increases the risk of errors and maintenance overhead.

A generic solution can:

Reduce code duplication.
Ensure consistency across all entities.
Handle concurrent user requests safely.
Support bulk operations like bulk inserts seamlessly.

Let’s dive into a step-by-step guide to implement this solution.

1. Define a Base Class for Audit Fields

Start by creating a base class or interface that all your entities can inherit or implement. This ensures that every entity has the audit fields we need to manage.

public abstract class AuditableEntity {
    private Date createdDate;
    private String createdBy;
    private Date updatedDate;
    private String updatedBy;

    // Getters and setters
    public Date getCreatedDate() { return createdDate; }
    public void setCreatedDate(Date createdDate) { this.createdDate = createdDate; }
    public String getCreatedBy() { return createdBy; }
    public void setCreatedBy(String createdBy) { this.createdBy = createdBy; }
    public Date getUpdatedDate() { return updatedDate; }
    public void setUpdatedDate(Date updatedDate) { this.updatedDate = updatedDate; }
    public String getUpdatedBy() { return updatedBy; }
    public void setUpdatedBy(String updatedBy) { this.updatedBy = updatedBy; }
}

Your entity classes, like User, can extend this:

public class User extends AuditableEntity {
    private Long id;
    private String name;
    // Other fields, getters, setters
}

This abstraction ensures all entities share the same audit structure, making it easier to apply generic logic.

2. Create a MyBatis Interceptor for Audit Fields

MyBatis interceptors allow you to intercept SQL executions and modify parameters or mapped objects dynamically. We’ll use an interceptor to automatically set audit fields before executing insert or update queries.

import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.plugin.*;
import java.util.Collection;
import java.util.Date;
import java.util.Map;
import java.util.Properties;
import org.springframework.stereotype.Component; 

@Intercepts({
    @Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class})
})
@Component
public class AuditInterceptor implements Interceptor {

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        MappedStatement mappedStatement = (MappedStatement) invocation.getArgs()[0];
        Object parameter = invocation.getArgs()[1];
        String sqlCommandType = mappedStatement.getSqlCommandType().name();
        String userId = UserContext.getCurrentUserId(); // From ThreadLocal or SecurityContext
        Date now = new Date();

        // Handle single entity
        if (parameter instanceof AuditableEntity) {
            handleEntity((AuditableEntity) parameter, sqlCommandType, userId, now);
        }
        // Handle collections (e.g., List<AuditableEntity>)
        else if (parameter instanceof Collection) {
            Collection<?> collection = (Collection<?>) parameter;
            for (Object item : collection) {
                if (item instanceof AuditableEntity) {
                    handleEntity((AuditableEntity) item, sqlCommandType, userId, now);
                }
            }
        }
        // Handle Map (MyBatis might wrap the parameter in a Map)
        else if (parameter instanceof Map) {
            Map<?, ?> paramMap = (Map<?, ?>) parameter;
            for (Object value : paramMap.values()) {
                if (value instanceof Collection) {
                    Collection<?> collection = (Collection<?>) value;
                    for (Object item : collection) {
                        if (item instanceof AuditableEntity) {
                            handleEntity((AuditableEntity) item, sqlCommandType, userId, now);
                        }
                    }
                } else if (value instanceof AuditableEntity) {
                    handleEntity((AuditableEntity) value, sqlCommandType, userId, now);
                }
            }
        }

        return invocation.proceed();
    }

    private void handleEntity(AuditableEntity entity, String sqlCommandType, String userId, Date now) {
        if ("INSERT".equalsIgnoreCase(sqlCommandType)) {
            entity.setCreatedDate(now);
            entity.setCreatedBy(userId);
            entity.setUpdatedDate(now); // Optional: set on insert
            entity.setUpdatedBy(userId);
        } else if ("UPDATE".equalsIgnoreCase(sqlCommandType)) {
            entity.setUpdatedDate(now);
            entity.setUpdatedBy(userId);
        }
    }

    @Override
    public Object plugin(Object target) {
        return Plugin.wrap(target, this);
    }

    @Override
    public void setProperties(Properties properties) {
        // No properties needed
    }
}

This interceptor:

Handles single entities, collections (e.g., List<User>), and Map parameters (common in MyBatis bulk operations).
Sets createdDate, createdBy, updatedDate, and updatedBy based on the SQL command type (INSERT or UPDATE).
Uses UserContext to fetch the current user ID, which we’ll implement next.

3. Safely Handle User ID in Concurrent Requests

Since your REST APIs handle multiple users simultaneously, you need to manage the userId securely to avoid concurrency issues. We’ll use a ThreadLocal-based UserContext with a filter for lifecycle management.

public class UserContext {
    private static final ThreadLocal<String> currentUserId = new ThreadLocal<>();

    public static void setCurrentUserId(String userId) {
        currentUserId.set(userId);
    }

    public static String getCurrentUserId() {
        return currentUserId.get() != null ? currentUserId.get() : "system"; // Fallback to "system"
    }

    public static void clear() {
        currentUserId.remove(); // Explicitly clear the ThreadLocal
    }
}

4. Use a Filter for Lifecycle Management

Add a UserContextFilter to set and clear the userId for each request:

import org.springframework.stereotype.Component;
import org.springframework.web.filter.OncePerRequestFilter;

import javax.servlet.FilterChain;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;

@Component
public class UserContextFilter extends OncePerRequestFilter {

    @Override
    protected void doFilterInternal(HttpServletRequest request, HttpServletResponse response, FilterChain filterChain)
            throws ServletException, IOException {
        try {
            // Extract userId from request header (e.g., X-User-Id)
            String userId = request.getHeader("X-User-Id");
            if (userId != null && !userId.trim().isEmpty()) {
                UserContext.setCurrentUserId(userId);
            } else {
                UserContext.setCurrentUserId("system"); // Fallback
            }
            filterChain.doFilter(request, response);
        } finally {
            UserContext.clear();
        }
    }
}

This ensures each request has its own userId, preventing concurrency issues.

5. Create Mapper Interface for CRUD Operations

You can create a mapper interface with methods required for Create or Update operations on the data. The Audit interceptor will take care of inserting the actual values for the 4 Audit related fields.

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

@Mapper
public interface UserMapper {

    @Insert({
        "<script>",
        "INSERT INTO users (name, created_date, created_by, updated_date, updated_by)",
        "VALUES",
        "<foreach collection='list' item='user' separator=','>",
        "(#{user.name}, #{user.createdDate}, #{user.createdBy}, #{user.updatedDate}, #{user.updatedBy})",
        "</foreach>",
        "</script>"
    })
    void bulkInsert(List<User> users);

    @Insert("INSERT INTO users (name, created_date, created_by, updated_date, updated_by) VALUES (#{user.name}, #{user.createdDate}, #{user.createdBy}, #{user.updatedDate}, #{user.updatedBy}")
    void insert(User user); 
}

6. Create Controller and Service classes

Here’s how to use the setup in your application:

import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController
@RequestMapping("/api/users")
public class UserController {

    private final UserService userService;

    public UserController(UserService userService) {
        this.userService = userService;
    }

    @PostMapping("/")
    public ResponseEntity<Void> bulkCreateUsers(@RequestBody List<User> users) {
        userService.createUsers(users);
        return ResponseEntity.ok().build();
    }
}

import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class UserService {

    private final UserMapper userMapper;

    public UserService(UserMapper userMapper) {
        this.userMapper = userMapper;
    }

    public void createUsers(List<User> users) {
        userMapper.bulkInsert(users);
    }
}

Benefits of This Approach

Generic and Scalable: Works for all entities extending AuditableEntity, reducing code duplication.
Concurrent Safety: Handles multiple users safely using ThreadLocal and a filter.
Bulk Insert Support: Seamlessly manages audit fields for bulk operations.
Clean Code: Keeps your mappers and services free of repetitive audit logic.

Conclusion

Automating audit fields in a Spring Boot application with MyBatis is a powerful way to maintain consistency and reduce boilerplate code. By using a MyBatis interceptor, handling concurrent user requests with ThreadLocal, and supporting bulk inserts with annotations, you can create a robust, scalable solution for your CRUD operations on MSSQL databases. Follow the steps and code snippets above to implement this in your project, and enjoy cleaner, more maintainable code.

If you’re facing specific challenges or want to extend this solution, drop a comment below—I’m here to help! Don’t forget to share this post with your developer community on social media to spread the knowledge.