Build a Real‑Time SQL Call Tree Visualizer for SpringBoot & MyBatis using D3.js

Project Background

In complex business systems a single interface often executes multiple SQL statements. Developers need a way to see the call relationships and execution details of these SQLs.

This article builds a SQL call‑tree visualization system using SpringBoot and MyBatis.

System Feature Overview

The system provides the following core capabilities:

Core Features

MyBatis Interceptor : Captures SQL execution without modifying business code.

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

Visualization : Uses D3.js to display the tree structure.

Performance Monitoring : Records execution time and flags slow SQL.

Statistics Analysis : Provides execution statistics and performance analysis.

Data Management : Supports query, cleanup and export of collected data.

Technical Implementation

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

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

Configuration : Dynamic configuration of slow‑SQL threshold and tracing switch.

Project Structure

springboot-sql-tree/
├── src/main/java/com/example/sqltree/
│   ├── SqlTreeApplication.java          # Startup class
│   ├── SqlInterceptor.java              # MyBatis interceptor
│   ├── SqlCallTreeContext.java          # Call‑tree context manager
│   ├── SqlNode.java                     # SQL node data model
│   ├── SqlTreeController.java           # REST API controller
│   ├── DemoController.java              # Demo API
│   ├── UserService.java                 # Example service
│   ├── UserMapper.java                  # User DAO
│   └── OrderMapper.java                 # Order DAO
├── src/main/resources/
│   ├── application.yml                 # Application configuration
│   ├── schema.sql                      # Database schema
│   ├── data.sql                        # Sample data
│   └── static/
│       ├── index.html                  # Front‑end page
│       └── sql-tree.js                  # Front‑end JavaScript
└── pom.xml                              # Maven configuration

1. MyBatis Interceptor – Zero‑Intrusion Core

This component intercepts query and update methods, records start time, extracts SQL, service and method names, creates a SqlNode, and pushes it onto a thread‑local stack.

@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 mappedStatement = (MappedStatement) args[0];
        Object parameter = args[1];
        BoundSql boundSql = mappedStatement.getBoundSql(parameter);
        String sql = boundSql.getSql();
        String sqlType = mappedStatement.getSqlCommandType().name();

        StackTraceElement[] stackTrace = Thread.currentThread().getStackTrace();
        String serviceName = extractServiceName(stackTrace);
        String methodName = extractMethodName(stackTrace);

        SqlNode sqlNode = 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, parameter))
                .depth(sqlCallTreeContext.getCurrentDepth() + 1)
                .build();

        sqlCallTreeContext.enter(sqlNode);
        try {
            Object result = invocation.proceed();
            long executionTime = System.currentTimeMillis() - startTime;
            int affectedRows = calculateAffectedRows(result, sqlType);
            sqlCallTreeContext.exit(sqlNode, affectedRows, null);
            return result;
        } catch (Exception e) {
            sqlCallTreeContext.exit(sqlNode, 0, e.getMessage());
            throw e;
        }
    }
    // helper methods omitted for brevity
}

Key Characteristics :

Precise interception of both query and update operations.

Exception‑safe – business logic is unaffected by monitoring.

Rich information – automatically extracts service name, method, parameters, and execution statistics.

2. Call‑Tree Context Manager – Thread‑Safe Data Handling

The SqlCallTreeContext uses ThreadLocal stacks to maintain isolation between threads, builds parent‑child relationships, and aggregates global statistics.

@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(0);
    private final AtomicLong slowSqlCount = new AtomicLong(0);
    private final AtomicLong errorSqlCount = new AtomicLong(0);
    private final AtomicLong totalExecutionTime = new AtomicLong(0);

    private volatile long slowSqlThreshold = 1000;
    private volatile boolean traceEnabled = true;

    public SqlNode enter(SqlNode sqlNode) {
        if (!traceEnabled) return sqlNode;
        Stack<SqlNode> stack = callStack.get();
        sqlNode.setDepth(stack.size() + 1);
        if (!stack.isEmpty()) {
            SqlNode parent = stack.peek();
            parent.addChild(sqlNode);
            sqlNode.setParentId(parent.getNodeId());
        } else {
            rootNodes.get().add(sqlNode);
        }
        stack.push(sqlNode);
        return sqlNode;
    }

    public void exit(SqlNode sqlNode, int affectedRows, String errorMessage) {
        if (!traceEnabled) return;
        sqlNode.setEndTime(LocalDateTime.now());
        sqlNode.setAffectedRows(affectedRows);
        sqlNode.setErrorMessage(errorMessage);
        long executionTime = Duration.between(sqlNode.getStartTime(), sqlNode.getEndTime()).toMillis();
        sqlNode.setExecutionTime(executionTime);
        if (executionTime > slowSqlThreshold) {
            sqlNode.setSlowSql(true);
            slowSqlCount.incrementAndGet();
        }
        if (errorMessage != null) {
            errorSqlCount.incrementAndGet();
        }
        totalSqlCount.incrementAndGet();
        totalExecutionTime.addAndGet(executionTime);
        Stack<SqlNode> stack = callStack.get();
        if (!stack.isEmpty()) {
            stack.pop();
            if (stack.isEmpty()) {
                String sessionKey = generateSessionKey();
                globalSessions.put(sessionKey, new ArrayList<>(rootNodes.get()));
                rootNodes.get().clear();
            }
        }
    }
    // other methods omitted for brevity
}

3. Data Model – Complete SQL Node Information

@Data
public class SqlNode {
    private String nodeId;
    private String sql;
    private String formattedSql;
    private String sqlType;
    private int depth;
    private String threadName;
    private String serviceName;
    private String methodName;
    private LocalDateTime startTime;
    private LocalDateTime endTime;
    private long executionTime;
    private boolean slowSql;
    private int affectedRows;
    private String errorMessage;
    private List<Object> parameters;
    private List<SqlNode> children;
    // helper methods omitted
}

4. RESTful API – Data Access Endpoints

The SqlTreeController exposes endpoints for retrieving the current call tree, all sessions, statistics, configuration updates, slow‑SQL analysis, data export and health checks.

@RestController
@RequestMapping("/api/sql-tree")
public class SqlTreeController {
    @Autowired
    private SqlCallTreeContext sqlCallTreeContext;

    @GetMapping("/current")
    public ResponseEntity<List<SqlNode>> getCurrentTree() {
        return ResponseEntity.ok(sqlCallTreeContext.getRootNodes());
    }

    @GetMapping("/sessions")
    public ResponseEntity<Map<String, List<SqlNode>>> getAllSessions() {
        return ResponseEntity.ok(sqlCallTreeContext.getAllSessions());
    }

    @GetMapping("/statistics")
    public ResponseEntity<Map<String, Object>> getStatistics() {
        SqlStatistics stats = sqlCallTreeContext.getStatistics();
        Map<String, Object> response = new HashMap<>();
        response.put("totalSqlCount", stats.getTotalSqlCount());
        response.put("slowSqlCount", stats.getSlowSqlCount());
        response.put("errorSqlCount", stats.getErrorSqlCount());
        response.put("averageExecutionTime", stats.getAverageExecutionTime());
        response.put("slowSqlThreshold", sqlCallTreeContext.getSlowSqlThreshold());
        response.put("traceEnabled", sqlCallTreeContext.isTraceEnabled());
        return ResponseEntity.ok(response);
    }

    // other endpoints omitted for brevity
}

5. Front‑End Visualization

The front‑end uses D3.js to render an interactive tree. Nodes are color‑coded (green for SELECT, blue for INSERT, purple for UPDATE, red for DELETE or errors, orange for slow SQL). Hover shows a tooltip; click displays detailed information.

class SqlTreeVisualizer {
    constructor() {
        this.width = 1200;
        this.height = 800;
        this.margin = {top: 50, right: 150, bottom: 50, left: 150};
        this.svg = d3.select('#tree-container')
            .append('svg')
            .attr('width', this.width)
            .attr('height', this.height);
        this.g = this.svg.append('g')
            .attr('transform', `translate(${this.margin.left},${this.margin.top})`);
        this.tree = d3.tree()
            .size([this.height - this.margin.top - this.margin.bottom,
                   this.width - this.margin.left - this.margin.right]);
        this.tooltip = d3.select('body').append('div')
            .attr('class', 'tooltip')
            .style('opacity', 0);
    }
    // rendering methods omitted for brevity
}

Core Advantages

Tree layout clearly shows SQL call hierarchy.

Color coding distinguishes normal, slow and error queries.

Interactive operations: click for details, hover for tooltip.

Smart filtering by execution time, SQL type, etc.

Real‑time refresh supports automatic or manual updates.

Quick Start

Environment Requirements

Java 21+

Maven 3.6+

Modern browser with ES6 support

Run the Application

After starting the SpringBoot application, access the visual interface at http://localhost:8080/index.html and the H2 console at http://localhost:8080/h2-console.

JDBC URL: jdbc:h2:mem:testdb
Username: sa
Password: (empty)

Project Configuration

Key dependencies in pom.xml include Spring Boot starter web, MyBatis Spring Boot starter, H2 database, and Lombok.

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
        <version>3.4.5</version>
    </dependency>
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>3.0.3</version>
    </dependency>
    <dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
        <scope>runtime</scope>
    </dependency>
    <dependency>
        <groupId>org.projectlombok</groupId>
        <artifactId>lombok</artifactId>
        <optional>true</optional>
    </dependency>
</dependencies>

Application Configuration (application.yml)

server:
  port: 8080

spring:
  application:
    name: springboot-sql-tree
  datasource:
    url: jdbc:h2:mem:testdb
    driver-class-name: org.h2.Driver
    username: sa
    password:
    schema: classpath:schema.sql
    data: classpath:data.sql
  h2:
    console:
      enabled: true
      path: /h2-console

mybatis:
  mapper-locations: classpath:mapper/*.xml
  type-aliases-package: com.example.sqltree.entity
  configuration:
    map-underscore-to-camel-case: true
    lazy-loading-enabled: true
    cache-enabled: true
    log-impl: org.apache.ibatis.logging.slf4j.Slf4jImpl

Real‑World Scenarios

Development Debugging

Scenario 1 – Complex Query Performance Analysis

Calling /api/demo/user/1/detail produces a call chain 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 execution time, depth, and identifies the bottleneck.

Scenario 2 – Slow SQL Identification

{
  "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"
}

Data Monitoring

Statistics example:

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

Slow‑SQL analysis returns a sorted list of the most time‑consuming statements.

Conclusion

The project demonstrates how to combine the SpringBoot ecosystem with front‑end technologies to build a practical SQL monitoring tool that aids daily development and performance optimization.

Source code: GitHub repository