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

In complex business systems a single API often executes multiple SQL statements; developers need a clear view of the SQL call relationships and execution details.

Project Background

To address complex query chains, performance troubleshooting, and debugging needs, we built a SQL call‑tree visualization system based on SpringBoot and MyBatis.

System Features

Core Capabilities

MyBatis Interceptor : Captures SQL execution without modifying business code.

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

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

Performance Monitoring : Records execution time and flags slow SQL.

Statistics : 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, Font Awesome, native JavaScript.

Configuration Management : Dynamic configuration of slow‑SQL thresholds and other parameters.

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                 # Service (demo)
│   ├── UserMapper.java                  # User DAO
│   └── OrderMapper.java                 # Order DAO
│   └── ...
│   └── resources/
│       ├── application.yml
│       ├── schema.sql
│       ├── data.sql
│       └── static/
│           ├── index.html
│           └── sql-tree.js
└── pom.xml

Core Implementation Details

1. MyBatis Interceptor – Zero‑Intrusion Core

@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

@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; // ms
    private volatile boolean traceEnabled = true;
    // enter, exit, getters, statistics, configuration 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;
    // utility methods omitted for brevity
}

4. RESTful API – Full Data Interface

@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> tree = sqlCallTreeContext.getAllSessions().get(sessionKey);
        return tree != null ? ResponseEntity.ok(tree) : 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 th = ((Number) config.get("slowSqlThreshold")).longValue();
            sqlCallTreeContext.setSlowSqlThreshold(th);
            resp.put("slowSqlThreshold", th);
        }
        if (config.containsKey("traceEnabled")) {
            boolean en = (Boolean) config.get("traceEnabled");
            sqlCallTreeContext.setTraceEnabled(en);
            resp.put("traceEnabled", en);
        }
        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 Implementation

// sql-tree.js – main visualization logic (simplified)
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]); }
    }
    renderTree(rootNode) {
        const root = d3.hierarchy(rootNode, d=>d.children);
        this.tree(root);
        const links = this.g.selectAll('.link').data(root.links()).enter().append('path')
            .attr('class','link')
            .attr('d', d3.linkHorizontal().x(d=>d.y).y(d=>d.x))
            .style('fill','none').style('stroke','#94a3b8').style('stroke-width','2px');
        const nodes = this.g.selectAll('.node').data(root.descendants()).enter().append('g')
            .attr('class','node')
            .attr('transform', d=>`translate(${d.y},${d.x})`);
        nodes.append('circle').attr('r',10)
            .style('fill', d=>this.getNodeColor(d.data))
            .style('stroke','#1e293b').style('stroke-width','2px')
            .style('cursor','pointer');
        nodes.append('text')
            .attr('dy','.35em')
            .attr('x', d=>d.children?-15:15)
            .style('text-anchor', d=>d.children?'end':'start')
            .style('font-size','12px')
            .style('font-weight','500')
            .style('fill','#1e293b')
            .text(d=>`${d.data.sqlType} (${d.data.executionTime||0}ms)`);
        nodes.on('mouseover',(e,d)=>this.showTooltip(e,d.data))
             .on('mouseout',()=>this.hideTooltip())
             .on('click',(e,d)=>this.showNodeDetails(d.data));
    }
    // helper methods for colors, tooltips, empty state, details omitted for brevity
}

Quick Start

Environment Requirements

Java 21+

Maven 3.6+

Modern browser (ES6+ support)

Access the System

After starting the application, open http://localhost:8080/index.html for the visualization UI and http://localhost:8080/h2-console for the H2 console.

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

Project Configuration

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

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 Debug Scenario

When calling /api/demo/user/1/detail, the system captures the following call chain:

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

Total execution time: 62 ms

SQL call depth: 2 levels

Performance bottleneck: order_items query (longest)

Slow‑SQL Identification

The system flags any SQL whose execution exceeds the default 1000 ms threshold:

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

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

Slow‑SQL Analysis Report

[
  {
    "sql": "SELECT COUNT(*) FROM orders WHERE created_at BETWEEN ? AND ?",
    "executionTime": 2150,
    "serviceName": "ReportService",
    "methodName": "generateDailyReport",
    "affectedRows": 1
  },
  {
    "sql": "UPDATE users SET last_login = ? WHERE id IN (...)",
    "executionTime": 1890,
    "serviceName": "UserService",
    "methodName": "batchUpdateLastLogin",
    "affectedRows": 156
  }
]

Conclusion

The project demonstrates how to combine Spring Boot, MyBatis, and modern front‑end technologies to create a practical SQL monitoring tool that aids development, performance tuning, and debugging in complex business applications.