solon下使用阿里云计算平台druid连接池监控

solon默认不支持servlet体系,我通过阅读阿里云计算平台druid,根据源码让solon体系支持druid数据库sql监控

下面我以最简单的方案实现solon阿里druid连接池监控。

方案

编写一个controller实现druidweb功能即可,此时是 2025年7月5日

1.依赖

<dependency>
  <groupId>com.alibaba</groupId>
  <artifactId>druid</artifactId>
  <version>1.2.25</version>
</dependency>

  <!-- 引入 Web 快速开发集成包 -->
<dependency>
  <groupId>org.noear</groupId>
  <artifactId>solon-web</artifactId>
  <version>3.3.0</version>
  <scope>provided</scope>
</dependency>

<!-- Mysql驱动包 -->
<dependency>
  <groupId>mysql</groupId>
  <artifactId>mysql-connector-java</artifactId>
  <version>8.0.33</version>
  <!--<scope>provided</scope>-->
</dependency>

2.main入口

package top.lingkang.druid.jdbc;

import org.noear.solon.Solon;
import org.noear.solon.annotation.Controller;
import org.noear.solon.annotation.Mapping;

/**
 * @author lingkang
 * created by 2025/7/5 21:49
 */
@Controller
public class TestJdbc {
    public static void main(String[] args) {
        Solon.start(TestJdbc.class, args, solonApp -> {
            solonApp.context().beanScan("top.lingkang.druid.solon");
        });
    }

    // 用于调用测试
    @Mapping("/sql")
    public String sql() {
        DbConfig bean = Solon.context().getBean(DbConfig.class);
        return bean.query();
    }

}

3.数据库配置

/**
 * @author lingkang
 * created by 2025/7/5 23:20
 */
@Configuration
public class DbConfig {

    @Init
    public void init() {
       // 用于监控测试
        query();
    }

    public String query() {
        String result = null;
        DruidDataSource dataSource = Solon.context().getBean(DruidDataSource.class);
        String sql = "select * from sys_job_log";

        try (DruidPooledConnection conn = dataSource.getConnection();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {

            // 设置查询参数
            // pstmt.setInt(1, 18);

            // 执行查询
            try (ResultSet rs = pstmt.executeQuery()) {
                // 处理查询结果
                while (rs.next()) {
                    int id = rs.getInt("job_log_id");
                    String name = rs.getString("job_name");
                    int age = rs.getInt("status");
                    result = String.format("ID: %s, 姓名: %s, 年龄: %d%n", id, name, age);
                    System.out.printf(result);
                }
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return result;
    }

    @Bean
    public DruidDataSource dataSource() throws SQLException {
        DruidDataSource dataSource = new DruidDataSource();
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        dataSource.setUrl("jdbc:mysql://localhost:3306/ry-vue?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=GMT%2B8");
        dataSource.setUsername("root");
        dataSource.setPassword("123456");

        // 配置连接池参数
        dataSource.setInitialSize(1);
        dataSource.setMinIdle(1);
        dataSource.setMaxActive(5);
        dataSource.setMaxWait(60000);
        dataSource.setTimeBetweenEvictionRunsMillis(60000);
        dataSource.setMinEvictableIdleTimeMillis(300000);
        dataSource.setValidationQuery("SELECT 1");
        dataSource.setTestWhileIdle(true);
        dataSource.setTestOnBorrow(false);
        dataSource.setTestOnReturn(false);
        dataSource.setPoolPreparedStatements(true);
        dataSource.setMaxPoolPreparedStatementPerConnectionSize(20);
        

        // 配置监控统计拦截的filters
        dataSource.setFilters("stat,wall"); // stat:SQL监控 wall:防火墙 log4j:日志记录

        // SQL监控配置
        dataSource.setConnectionProperties("druid.stat.mergeSql=true;druid.stat.slowSqlMillis=1000");
        // mergeSql:合并SQL slowSqlMillis:超过1秒的SQL视为慢SQL

        return dataSource;
    }
}

4.编写controller

注意,代码指定了本地才能访问,账号密码是:druid/123456

  • 没有实现url监控(非全局过滤,提高性能)
  • 没有实现session监控(无须session监控)
  • 没有spring监控
package top.lingkang.druid.solon;

import com.alibaba.druid.DbType;
import com.alibaba.druid.pool.DruidDataSource;
import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.visitor.SchemaStatVisitor;
import com.alibaba.druid.stat.DruidStatManagerFacade;
import com.alibaba.druid.support.http.stat.WebAppStat;
import com.alibaba.druid.support.http.stat.WebAppStatManager;
import com.alibaba.druid.support.json.JSONUtils;
import com.alibaba.druid.support.spring.stat.SpringStatManager;
import com.alibaba.druid.util.MapComparator;
import com.alibaba.druid.util.StringUtils;
import com.alibaba.druid.util.Utils;
import org.noear.solon.Solon;
import org.noear.solon.annotation.Controller;
import org.noear.solon.annotation.Init;
import org.noear.solon.annotation.Mapping;
import org.noear.solon.core.handle.Context;

import javax.sql.DataSource;
import java.io.IOException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @author lingkang
 * created by 2025/7/5 22:27
 */
@Controller
@Mapping("/druid")
public class DruidController {
    DruidDataSource druidDataSource;

    public static final int RESULT_CODE_SUCCESS = 1;
    public static final int RESULT_CODE_ERROR = -1;

    private static final int DEFAULT_PAGE = 1;
    private static final int DEFAULT_PER_PAGE_COUNT = Integer.MAX_VALUE;
    private static final String ORDER_TYPE_DESC = "desc";
    private static final String ORDER_TYPE_ASC = "asc";
    private static final String DEFAULT_ORDER_TYPE = ORDER_TYPE_ASC;
    private static final String DEFAULT_ORDERBY = "SQL";

    public static final String SESSION_USER_KEY = "druid-user";
    public static final String PARAM_NAME_USERNAME = "loginUsername";
    public static final String PARAM_NAME_PASSWORD = "loginPassword";

    public static final String druid_username = "druid";
    public static final String druid_password = "123456";

    private static DruidStatManagerFacade statManagerFacade = DruidStatManagerFacade.getInstance();
    private String contextPath;

    private WebAppStat webAppStat;

    @Init
    public void init() {
        List<DataSource> list = Solon.context().getBeansOfType(DataSource.class);
        for (DataSource ds : list) {
            if (ds instanceof DruidDataSource) {
                druidDataSource = (DruidDataSource) ds;
                break;
            }
        }

        contextPath = Solon.cfg().get("server.contextPath", "");
        if (contextPath.endsWith("/"))
            contextPath = contextPath.substring(0, contextPath.length() - 1);

        if (webAppStat == null) {
            webAppStat = new WebAppStat("".equals(contextPath) ? "/" : contextPath, 1000);
        }
        WebAppStatManager.getInstance().addWebAppStatSet(webAppStat);
    }

    @Mapping("/**")
    public void handler(Context ctx) throws IOException {
        String ip = ctx.realIp();
        if (!"127.0.0.1".equals(ip) && !"0:0:0:0:0:0:0:1".equals(ip)) {// 只允许本地访问
            String text = Utils.readFromResource("support/http/resources/nopermit.html");
            ctx.outputAsHtml(text);
            return;
        }

        String url = ctx.path();
        url = url.substring(contextPath.length() + "/druid".length());

        if ("".equals(url)) {
            if (contextPath.equals("") || contextPath.equals("/")) {
                ctx.redirect("/druid/index.html");
            } else {
                ctx.redirect("index.html");
            }
            return;
        }
        if ("/".equals(url)) {
            ctx.redirect("index.html");
            return;
        }

        if (isResource(url)) {
            String text = Utils.readFromResource("support/http/resources" + url);
            if (text == null)
                return;

            if (url.endsWith(".html")) {
                if (ctx.session(SESSION_USER_KEY) == null && !url.endsWith("/login.html")) {
                    // 未登录
                    ctx.redirect("/druid/login.html");
                    return;
                }
                if (url.endsWith("/spring.html")) {
                    text += "<h1 style=\"color: red; text-align: center;\">solon项目无法监控spring体系</h1>";
                }
                ctx.outputAsHtml(text);
                return;
            }

            if (url.endsWith(".js")) {
                ctx.contentType("text/javascript");
                ctx.output(text);
                return;
            }

            if (url.endsWith(".css")) {
                ctx.contentType("text/css");
                ctx.output(text);
                return;
            }

            return;
        }

        if ("/submitLogin".equals(url)) {// 登录
            String usernameParam = ctx.param(PARAM_NAME_USERNAME);
            String passwordParam = ctx.param(PARAM_NAME_PASSWORD);
            if (druid_username.equals(usernameParam) && druid_password.equals(passwordParam)) {
                ctx.sessionSet(SESSION_USER_KEY, druid_username);
                ctx.output("success");
            } else {
                ctx.output("error");
            }
            return;
        }

        if (ctx.session(SESSION_USER_KEY) == null) {
            // 未登录
            ctx.redirect("/druid/login.html");
            return;
        }

        service(url, ctx);
    }

    private boolean isResource(String path) {
        return path.endsWith(".html") || path.endsWith(".jpg") || path.endsWith(".js") || path.endsWith(".css");
    }

    private Map<String, String> getParams(Context ctx) {
        Map<String, String> params = new HashMap<>();
        Collection<String> paramNames = ctx.paramNames();
        for (String paramName : paramNames) {
            params.put(paramName, ctx.param(paramName));
        }
        return params;
    }

    private void service(String url, Context ctx) {
        String result = null;
        if (url.equals("/basic.json")) {
            result = returnJSONResult(RESULT_CODE_SUCCESS, statManagerFacade.returnJSONBasicStat());
        } else if (url.equals("/reset-all.json")) {
            statManagerFacade.resetAll();
            result = returnJSONResult(RESULT_CODE_SUCCESS, null);
        } else if (url.equals("/log-and-reset.json")) {
            statManagerFacade.logAndResetDataSource();
            result = returnJSONResult(RESULT_CODE_SUCCESS, null);
        } else if (url.equals("/datasource.json")) {
            result = returnJSONResult(RESULT_CODE_SUCCESS, statManagerFacade.getDataSourceStatDataList());
        } else if (url.equals("/activeConnectionStackTrace.json")) {
            result = returnJSONResult(RESULT_CODE_SUCCESS, statManagerFacade.getActiveConnStackTraceList());
        } else if (url.startsWith("/datasource-")) {
            Integer id = StringUtils.subStringToInteger(url, "datasource-", ".");
            Object res = statManagerFacade.getDataSourceStatData(id);
            result = returnJSONResult(res == null ? RESULT_CODE_ERROR : RESULT_CODE_SUCCESS, res);
        } else if (url.startsWith("/connectionInfo-") && url.endsWith(".json")) {
            Integer id = StringUtils.subStringToInteger(url, "connectionInfo-", ".");
            List<?> connectionInfoList = statManagerFacade.getPoolingConnectionInfoByDataSourceId(id);
            result = returnJSONResult(connectionInfoList == null ? RESULT_CODE_ERROR : RESULT_CODE_SUCCESS,
                    connectionInfoList);
        } else if (url.startsWith("/activeConnectionStackTrace-") && url.endsWith(".json")) {
            Integer id = StringUtils.subStringToInteger(url, "activeConnectionStackTrace-", ".");
            result = returnJSONActiveConnectionStackTrace(id);
        } else if (url.startsWith("/sql.json")) {
            result = returnJSONResult(RESULT_CODE_SUCCESS, getSqlStatDataList(getParams(ctx)));
        } else if (url.startsWith("/wall.json")) {
            result = returnJSONResult(RESULT_CODE_SUCCESS, getWallStatMap(getParams(ctx)));
        } else if (url.startsWith("/wall-") && url.indexOf(".json") > 0) {
            Integer dataSourceId = StringUtils.subStringToInteger(url, "wall-", ".json");
            Object res = statManagerFacade.getWallStatMap(dataSourceId);
            result = returnJSONResult(res == null ? RESULT_CODE_ERROR : RESULT_CODE_SUCCESS, res);
        } else if (url.startsWith("/sql-") && url.indexOf(".json") > 0) {
            Integer id = StringUtils.subStringToInteger(url, "sql-", ".json");
            result = getSqlStat(id);
        } else if (url.startsWith("/weburi.json")) {
            result = returnJSONResult(RESULT_CODE_SUCCESS, getWebURIStatDataList(getParams(ctx)));
        } else if (url.startsWith("/weburi-") && url.indexOf(".json") > 0) {
            String uri = StringUtils.subString(url, "weburi-", ".json", true);
            result = returnJSONResult(RESULT_CODE_SUCCESS, getWebURIStatData(uri));
        } else if (url.startsWith("/webapp.json")) {
            result = returnJSONResult(RESULT_CODE_SUCCESS, getWebAppStatDataList(getParams(ctx)));
        } else if (url.startsWith("/websession.json")) {
            result = returnJSONResult(RESULT_CODE_SUCCESS, getWebSessionStatDataList(getParams(ctx)));
        } else if (url.startsWith("/websession-") && url.indexOf(".json") > 0) {
            String id = StringUtils.subString(url, "websession-", ".json");
            result = returnJSONResult(RESULT_CODE_SUCCESS, getWebSessionStatData(id));
        } else if (url.startsWith("/spring.json")) {
            result = returnJSONResult(RESULT_CODE_SUCCESS, getSpringStatDataList(getParams(ctx)));
        } else if (url.startsWith("/spring-detail.json")) {
            Map<String, String> parameters = getParams(ctx);
            String clazz = parameters.get("class");
            String method = parameters.get("method");
            result = returnJSONResult(RESULT_CODE_SUCCESS, getSpringMethodStatData(clazz, method));
        }


        if (result == null) {
            ctx.outputAsJson(returnJSONResult(RESULT_CODE_ERROR, "Do not support this request, please contact with administrator."));
        } else {
            ctx.outputAsJson(result);
        }

    }

    private String returnJSONActiveConnectionStackTrace(Integer id) {
        List<String> result = statManagerFacade.getActiveConnectionStackTraceByDataSourceId(id);

        if (result == null) {
            return returnJSONResult(RESULT_CODE_ERROR, "require set removeAbandoned=true");
        }
        return returnJSONResult(RESULT_CODE_SUCCESS, result);
    }

    private List<Map<String, Object>> getSqlStatDataList(Map<String, String> parameters) {
        Integer dataSourceId = null;

        String dataSourceIdParam = parameters.get("dataSourceId");
        if (dataSourceIdParam != null && dataSourceIdParam.length() > 0) {
            dataSourceId = Integer.parseInt(dataSourceIdParam);
        }

        List<Map<String, Object>> array = statManagerFacade.getSqlStatDataList(dataSourceId);
        List<Map<String, Object>> sortedArray = comparatorOrderBy(array, parameters);
        return sortedArray;
    }

    private List<Map<String, Object>> comparatorOrderBy(List<Map<String, Object>> array,
                                                        Map<String, String> parameters) {
        // when open the stat page before executing some sql
        if (array == null || array.isEmpty()) {
            return null;
        }

        // when parameters is null
        String orderBy, orderType = null;
        Integer page = DEFAULT_PAGE;
        Integer perPageCount = DEFAULT_PER_PAGE_COUNT;
        if (parameters == null) {
            orderBy = DEFAULT_ORDERBY;
            orderType = DEFAULT_ORDER_TYPE;
            page = DEFAULT_PAGE;
            perPageCount = DEFAULT_PER_PAGE_COUNT;
        } else {
            orderBy = parameters.get("orderBy");
            orderType = parameters.get("orderType");
            String pageParam = parameters.get("page");
            if (pageParam != null && pageParam.length() != 0) {
                page = Integer.parseInt(pageParam);
            }
            String pageCountParam = parameters.get("perPageCount");
            if (pageCountParam != null && pageCountParam.length() > 0) {
                perPageCount = Integer.parseInt(pageCountParam);
            }
        }

        // others,such as order
        orderBy = orderBy == null ? DEFAULT_ORDERBY : orderBy;
        orderType = orderType == null ? DEFAULT_ORDER_TYPE : orderType;

        if (!ORDER_TYPE_DESC.equals(orderType)) {
            orderType = ORDER_TYPE_ASC;
        }

        // orderby the statData array
        if (orderBy.trim().length() != 0) {
            Collections.sort(array, new MapComparator<String, Object>(orderBy, ORDER_TYPE_DESC.equals(orderType)));
        }

        // page
        int fromIndex = (page - 1) * perPageCount;
        int toIndex = page * perPageCount;
        if (toIndex > array.size()) {
            toIndex = array.size();
        }

        return array.subList(fromIndex, toIndex);
    }


    private String getSqlStat(Integer id) {
        Map<String, Object> map = statManagerFacade.getSqlStatData(id);

        if (map == null) {
            return returnJSONResult(RESULT_CODE_ERROR, null);
        }

        DbType dbType = DbType.of((String) map.get("DbType"));
        String sql = (String) map.get("SQL");

        map.put("formattedSql", SQLUtils.format(sql, dbType));
        List<SQLStatement> statementList = SQLUtils.parseStatements(sql, dbType);

        if (!statementList.isEmpty()) {
            SQLStatement sqlStmt = statementList.get(0);
            SchemaStatVisitor visitor = SQLUtils.createSchemaStatVisitor(dbType);
            sqlStmt.accept(visitor);
            map.put("parsedTable", visitor.getTables().toString());
            map.put("parsedFields", visitor.getColumns().toString());
            map.put("parsedConditions", visitor.getConditions().toString());
            map.put("parsedRelationships", visitor.getRelationships().toString());
            map.put("parsedOrderbycolumns", visitor.getOrderByColumns().toString());
        }

        DateFormat format = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss:SSS");
        Date maxTimespanOccurTime = (Date) map.get("MaxTimespanOccurTime");
        if (maxTimespanOccurTime != null) {
            map.put("MaxTimespanOccurTime", format.format(maxTimespanOccurTime));
        }

        return returnJSONResult(map == null ? RESULT_CODE_ERROR : RESULT_CODE_SUCCESS, map);
    }

    private List<Map<String, Object>> getWebURIStatDataList(Map<String, String> parameters) {
        List<Map<String, Object>> array = WebAppStatManager.getInstance().getURIStatData();
        return comparatorOrderBy(array, parameters);
    }

    private Map<String, Object> getWebURIStatData(String uri) {
        return WebAppStatManager.getInstance().getURIStatData(uri);
    }

    private List<Map<String, Object>> getWebAppStatDataList(Map<String, String> parameters) {
        List<Map<String, Object>> array = WebAppStatManager.getInstance().getWebAppStatData();
        return comparatorOrderBy(array, parameters);
    }

    private List<Map<String, Object>> getWebSessionStatDataList(Map<String, String> parameters) {
        List<Map<String, Object>> array = WebAppStatManager.getInstance().getSessionStatData();
        return comparatorOrderBy(array, parameters);
    }

    private Map<String, Object> getWebSessionStatData(String sessionId) {
        return WebAppStatManager.getInstance().getSessionStat(sessionId);
    }

    private List<Map<String, Object>> getSpringStatDataList(Map<String, String> parameters) {
        List<Map<String, Object>> array = SpringStatManager.getInstance().getMethodStatData();
        return comparatorOrderBy(array, parameters);
    }

    private Map<String, Object> getSpringMethodStatData(String clazz, String method) {
        return SpringStatManager.getInstance().getMethodStatData(clazz, method);
    }

    public Map<String, Object> getWallStatMap(Map<String, String> parameters) {
        Integer dataSourceId = null;

        String dataSourceIdParam = parameters.get("dataSourceId");
        if (dataSourceIdParam != null && dataSourceIdParam.length() > 0) {
            dataSourceId = Integer.parseInt(dataSourceIdParam);
        }

        Map<String, Object> result = statManagerFacade.getWallStatMap(dataSourceId);

        if (result != null) {
            List<Map<String, Object>> tables = (List<Map<String, Object>>) result.get("tables");
            if (tables != null) {
                List<Map<String, Object>> sortedArray = comparatorOrderBy(tables, parameters);
                result.put("tables", sortedArray);
            }

            List<Map<String, Object>> functions = (List<Map<String, Object>>) result.get("functions");
            if (functions != null) {
                List<Map<String, Object>> sortedArray = comparatorOrderBy(functions, parameters);
                result.put("functions", sortedArray);
            }
        } else {
            result = Collections.emptyMap();
        }

        return result;
    }

    public static String returnJSONResult(int resultCode, Object content) {
        Map<String, Object> dataMap = new LinkedHashMap<String, Object>();
        dataMap.put("ResultCode", resultCode);
        dataMap.put("Content", content);
        return JSONUtils.toJSONString(dataMap);
    }

}

5.效果

访问web界面:http://localhost:8080/druid/

image-1751737634935

登录效果
image-1751737684134

sql界面
image-1751737704751

防火墙
image-1751737725186

image-1751737773369