How to Visualize SQL Call Trees in SpringBoot with MyBatis and D3.js

Project Background

In complex business systems, an interface often executes multiple SQL statements. This article builds a SQL call‑tree visualization system using SpringBoot and MyBatis interceptor.

System Features

The system provides:

Core Features

MyBatis Interceptor : captures SQL execution without modifying business code.

Call‑Tree Construction : automatically builds hierarchical relationships of SQL calls.

Visualization : renders the tree with D3.js.

Performance Monitoring : records execution time and flags slow SQL.

Statistics : offers execution statistics and performance analysis.

Data Management : supports query, cleanup and export of data.

Technical Implementation

Backend : Spring Boot 3.4.5, MyBatis 3.0.3, H2 database.

Frontend : HTML5, Tailwind CSS, D3.js v7.

Configuration : dynamic slow‑SQL threshold and trace switch.

Project Structure

springboot-sql-tree/
├── src/main/java/com/example/sqltree/
│   ├── SqlTreeApplication.java          # main class
│   ├── SqlInterceptor.java              # MyBatis interceptor
│   ├── SqlCallTreeContext.java          # call‑tree context manager
│   ├── SqlNode.java                     # SQL node model
│   ├── SqlTreeController.java           # REST API controller
│   ├── DemoController.java              # demo API
│   ├── UserService.java                  # demo service
│   ├── UserMapper.java                   # MyBatis mapper
│   └── OrderMapper.java                  # MyBatis mapper
├── src/main/resources/
│   ├── application.yml                   # configuration
│   ├── schema.sql                       # table definitions
│   ├── data.sql                         # sample data
│   └── static/
│       ├── index.html                   # front‑end page
│       └── sql-tree.js                  # visualization script
└── pom.xml                               # Maven build file

Core Implementation Details

1. MyBatis Interceptor – Zero‑intrusion core

The interceptor uses MyBatis plugin mechanism to capture SQL execution, record start time, extract SQL, service and method names, and store a SqlNode in the thread‑local context.

@Component
@Intercepts({
    @Signature(type = Executor.class, method = "query", args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}),
    @Signature(type = Executor.class, method = "update", args = {MappedStatement.class, Object.class})
})
public class SqlInterceptor implements Interceptor {
    @Autowired
    private SqlCallTreeContext sqlCallTreeContext;

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        if (!sqlCallTreeContext.isTraceEnabled()) {
            return invocation.proceed();
        }
        long startTime = System.currentTimeMillis();
        Object[] args = invocation.getArgs();
        MappedStatement ms = (MappedStatement) args[0];
        Object param = args[1];
        BoundSql boundSql = ms.getBoundSql(param);
        String sql = boundSql.getSql();
        String sqlType = ms.getSqlCommandType().name();
        StackTraceElement[] stack = Thread.currentThread().getStackTrace();
        String serviceName = extractServiceName(stack);
        String methodName = extractMethodName(stack);
        SqlNode node = SqlNode.builder()
                .nodeId(UUID.randomUUID().toString())
                .sql(formatSql(sql))
                .sqlType(sqlType)
                .threadName(Thread.currentThread().getName())
                .serviceName(serviceName)
                .methodName(methodName)
                .startTime(LocalDateTime.now())
                .parameters(extractParameters(boundSql, param))
                .depth(sqlCallTreeContext.getCurrentDepth() + 1)
                .build();
        sqlCallTreeContext.enter(node);
        try {
            Object result = invocation.proceed();
            long execTime = System.currentTimeMillis() - startTime;
            int affected = calculateAffectedRows(result, sqlType);
            sqlCallTreeContext.exit(node, affected, null);
            return result;
        } catch (Exception e) {
            sqlCallTreeContext.exit(node, 0, e.getMessage());
            throw e;
        }
    }
    // helper methods omitted for brevity
}

2. Call‑Tree Context – Thread‑safe data manager

The SqlCallTreeContext stores a thread‑local stack of SqlNode objects, builds parent‑child relationships, aggregates statistics and persists completed sessions in a concurrent map.

@Component
public class SqlCallTreeContext {
    private final ThreadLocal<Stack<SqlNode>> callStack = ThreadLocal.withInitial(Stack::new);
    private final ThreadLocal<List<SqlNode>> rootNodes = ThreadLocal.withInitial(ArrayList::new);
    private final Map<String, List<SqlNode>> globalSessions = new ConcurrentHashMap<>();
    private final AtomicLong totalSqlCount = new AtomicLong();
    private final AtomicLong slowSqlCount = new AtomicLong();
    private final AtomicLong errorSqlCount = new AtomicLong();
    private final AtomicLong totalExecutionTime = new AtomicLong();
    private volatile long slowSqlThreshold = 1000;
    private volatile boolean traceEnabled = true;
    // enter, exit, getters, statistics, etc.
}

3. RESTful API

The SqlTreeController exposes endpoints to retrieve the current tree, all sessions, a specific session, statistics, update configuration, export data and clear stored trees.

@RestController
@RequestMapping("/api/sql-tree")
public class SqlTreeController {
    @Autowired
    private SqlCallTreeContext sqlCallTreeContext;
    @GetMapping("/current")
    public ResponseEntity<List<SqlNode>> getCurrentTree() {
        return ResponseEntity.ok(sqlCallTreeContext.getRootNodes());
    }
    // other endpoints omitted for brevity
}

4. Front‑end Visualization

The JavaScript class SqlTreeVisualizer creates an SVG container with D3.js, builds a hierarchical layout, draws links and nodes, colors nodes by status, and shows tooltips and detail panels on hover/click.

// sql-tree.js – main visualization logic
class SqlTreeVisualizer {
    constructor() {
        this.width = 1200;
        this.height = 800;
        this.svg = d3.select('#tree-container')
            .append('svg')
            .attr('width', this.width)
            .attr('height', this.height);
        // layout, tooltip, etc.
    }
    render(sessions) { /* … */ }
    // other methods omitted
}

Quick Start

Prerequisites

Java 21+ Maven 3.6+ Modern browser (ES6+)

Run the Application

Start the SpringBoot app and open http://localhost:8080/index.html for the visualization and http://localhost:8080/h2-console for the H2 console.

Configuration

Key dependencies are listed in pom.xml (Spring Boot starter web, MyBatis starter, H2, Lombok). Application settings are in application.yml, including server port, datasource, MyBatis options and H2 console enablement.

Real‑World Scenarios

Complex Query Performance Analysis

When calling /api/demo/user/1/detail, the system records a tree such as:

UserService.getUserDetailWithOrders()
├── SELECT * FROM users WHERE id = ? (2ms)
└── SELECT * FROM orders WHERE user_id = ? (15ms)
    └── SELECT * FROM order_items WHERE order_id IN (...) (45ms)

The UI shows total time, depth and highlights the slowest node.

Slow‑SQL Detection

SQL statements whose execution exceeds the threshold (default 1000 ms) are marked with slowSql:true and displayed in orange.

{
  "nodeId":"uuid-123",
  "sql":"SELECT * FROM orders o JOIN users u ON o.user_id = u.id WHERE o.status = ?",
  "executionTime":1250,
  "slowSql":true,
  "serviceName":"OrderService",
  "methodName":"getOrdersWithUserInfo"
}

Statistics

{
  "totalSqlCount":1247,
  "slowSqlCount":23,
  "errorSqlCount":5,
  "averageExecutionTime":35.6,
  "slowSqlThreshold":1000,
  "traceEnabled":true
}

Conclusion

The project demonstrates how to combine Spring Boot, MyBatis and D3.js to create a practical SQL monitoring and visualization tool that aids debugging and performance tuning.