How to Build a Real‑Time SQL Call Tree Visualizer with Spring Boot and D3.js

Project Background

In everyday development we often encounter scenarios where a single API triggers multiple SQL statements, making it difficult to trace performance issues, understand complex query chains, or debug development problems.

This project implements a SQL call‑tree visualization system based on Spring Boot 3.4.5 and MyBatis 3.0.3.

System Features

The system provides the following core capabilities:

Core Functions

MyBatis Interceptor : Captures SQL execution without modifying business code.

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

Visualization : Uses D3.js to render a tree‑structured view.

Performance Monitoring : Records execution time and flags slow SQL.

Statistics : Offers aggregated execution statistics.

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

Technical Implementation

Backend Technologies : Spring Boot 3.4.5, MyBatis 3.0.3, H2 (in‑memory) database.

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

Configuration Management : 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                 ## Demo 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                ## Frontend page
│       └── sql-tree.js               ## Frontend JavaScript
└── pom.xml                              ## Maven configuration

1. MyBatis Interceptor – Zero Intrusion Core

The interceptor uses MyBatis plugin mechanism to capture SQL execution without affecting business logic.

@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 ...
}

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

The SqlCallTreeContext component manages construction and storage of the SQL call tree using ThreadLocal for isolation.

@Component
public class SqlCallTreeContext {
    private final ThreadLocal<Stack<SqlNode>> callStack = new ThreadLocal<>() {
        @Override
        protected Stack<SqlNode> initialValue() { return new Stack<>(); }
    };
    private final ThreadLocal<List<SqlNode>> rootNodes = new ThreadLocal<>() {
        @Override
        protected List<SqlNode> initialValue() { return new ArrayList<>(); }
    };
    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; // ms
    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 management methods omitted for brevity ...
}

3. Data Model – Complete SQL Node Information

@Data
public class SqlNode {
    private String nodeId;            // Unique identifier
    private String sql;               // Original SQL
    private String formattedSql;       // Formatted SQL
    private String sqlType;           // SELECT, INSERT, etc.
    private int depth;                // Call 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 for analysis omitted for brevity
}

4. RESTful API – Full Data Interface

The SqlTreeController provides endpoints for retrieving the current thread’s call tree, all sessions, specific session data, clearing data, statistics, and configuration updates.

@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("/session/{sessionKey}")
    public ResponseEntity<List<SqlNode>> getSessionTree(@PathVariable String sessionKey) {
        List<SqlNode> sessionTree = sqlCallTreeContext.getAllSessions().get(sessionKey);
        return sessionTree != null ? ResponseEntity.ok(sessionTree) : ResponseEntity.notFound().build();
    }

    @DeleteMapping("/clear")
    public ResponseEntity<Map<String, Object>> clearAllTrees() {
        sqlCallTreeContext.clearSessions();
        Map<String, Object> resp = new HashMap<>();
        resp.put("success", true);
        resp.put("message", "All SQL trees cleared successfully");
        resp.put("timestamp", LocalDateTime.now());
        return ResponseEntity.ok(resp);
    }

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

    @PostMapping("/config")
    public ResponseEntity<Map<String, Object>> updateConfig(@RequestBody Map<String, Object> config) {
        Map<String, Object> resp = new HashMap<>();
        if (config.containsKey("slowSqlThreshold")) {
            long threshold = ((Number) config.get("slowSqlThreshold")).longValue();
            sqlCallTreeContext.setSlowSqlThreshold(threshold);
            resp.put("slowSqlThreshold", threshold);
        }
        if (config.containsKey("traceEnabled")) {
            boolean enabled = (Boolean) config.get("traceEnabled");
            sqlCallTreeContext.setTraceEnabled(enabled);
            resp.put("traceEnabled", enabled);
        }
        resp.put("success", true);
        resp.put("message", "Configuration updated successfully");
        return ResponseEntity.ok(resp);
    }
    // Additional endpoints for slow‑SQL analysis, export, health check omitted for brevity
}

5. Frontend Visualization

The JavaScript visualizer builds a D3.js tree, assigns colors based on SQL type, execution time, and error status, and provides interactive tooltips and detail panels.

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);
    }
    render(sessions) {
        this.g.selectAll('*').remove();
        if (!sessions || Object.keys(sessions).length===0) { this.showEmptyState(); return; }
        const sessionKey = Object.keys(sessions)[0];
        const rootNodes = sessions[sessionKey];
        if (rootNodes && rootNodes.length>0) this.renderTree(rootNodes[0]);
    }
    // Rendering logic, color mapping, tooltip handling omitted for brevity
}

Quick Start

Environment Requirements

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

Access URLs

After starting the application you can visit:

Visualization UI: http://localhost:8080/index.html H2 Console: http://localhost:8080/h2-console (JDBC URL jdbc:h2:mem:testdb, username sa, no password)

Project Configuration (pom.xml)

<dependencies>
    <!-- Spring Boot 3.4.5 -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
        <version>3.4.5</version>
    </dependency>
    <!-- MyBatis 3.0.3 -->
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>3.0.3</version>
    </dependency>
    <!-- H2 Database -->
    <dependency>
        <groupId>com.h2database</groupId>
        <artifactId>h2</artifactId>
        <scope>runtime</scope>
    </dependency>
    <!-- Lombok (optional) -->
    <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

Practical Scenarios

Development Debugging

When calling /api/demo/user/1/detail, the system captures the full SQL call chain, showing total execution time, depth, and identifying the bottleneck query.

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

Slow‑SQL Identification

SQL statements exceeding the configurable threshold (default 1000 ms) are automatically flagged as slow.

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

Aggregated statistics example:

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

Conclusion

This project demonstrates how to combine Spring Boot, MyBatis, and D3.js to build a practical SQL monitoring and visualization tool, offering valuable insights for performance tuning and debugging in complex Java applications.