Speed Up Your SpringBoot App with Druid Connection Pool and SQL Optimization

While logging into the backend, the author noticed a 10‑second loading delay caused by an init API that took 7 seconds to return data from multiple tables.

Inspecting the network request revealed the init endpoint aggregates data from the blog, comment, user, and visit tables.

@ApiOperation(value = "首页初始化数据", notes = "首页初始化数据", response = String.class)
@RequestMapping(value = "/init", method = RequestMethod.GET)
public String init() {
    Map<String, Object> map = new HashMap<>(Constants.NUM_FOUR);
    map.put(SysConf.BLOG_COUNT, blogService.getBlogCount(EStatus.ENABLE));
    CommentVO commentVO = new CommentVO();
    map.put(SysConf.COMMENT_COUNT, commentService.getCommentCount(commentVO));
    map.put(SysConf.USER_COUNT, userService.getUserCount(EStatus.ENABLE));
    map.put(SysConf.VISIT_COUNT, webVisitService.getWebVisitCount());
    return ResultUtil.result(SysConf.SUCCESS, map);
}

To avoid the overhead of repeatedly creating database connections, a connection pool is used. The article introduces the concept of a connection pool and shows a typical JDBC boiler‑plate example.

public class TEST {
    static String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    static String DB_URL = "jdbc:mysql://localhost:3306/webcourse";
    static String USER = "root";
    static String PASS = "121314";
    public static void main(String[] args) throws Exception {
        Connection conn = null;
        Statement stmt = null;
        try {
            Class.forName("com.mysql.jdbc.Driver");
            System.out.println("连接数据库...");
            conn = DriverManager.getConnection(DB_URL, USER, PASS);
            System.out.println(" 实例化Statement对...");
            stmt = conn.createStatement();
            String sql = "SELECT * FROM bbs";
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String content = rs.getString("content");
                System.out.print("ID: " + id);
                System.out.print(",姓名: " + name);
                System.out.print(",内容: " + content);
                System.out.print("
");
            }
            rs.close();
            stmt.close();
            conn.close();
        } catch (SQLException se) {
            se.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (stmt != null) stmt.close();
            if (conn != null) conn.close();
        }
        System.out.println("测试结束");
    }
}

Druid is an Alibaba‑open‑source database connection pool designed for monitoring. It offers strong monitoring features without affecting performance and protects against SQL injection.

Github: https://github.com/alibaba/druid

Access the Druid monitoring UI by visiting http://localhost:8601/druid/index.html and logging in with the default credentials (admin / 123456). The UI shows version, driver, and Java information, as well as data‑source parameters.

In the SQL monitoring tab, the slowest query took 8 seconds. Clicking the query reveals details such as execution time, timestamp, and parameters.

Original SQL to count unique IPs for a day:

SELECT COUNT(ip) FROM (
    SELECT ip FROM t_web_visit
    WHERE create_time >= "2022-08-08 00:00:00"
      AND create_time <= "2022-08-08 23:59:59"
    GROUP BY ip
) tmp;

This query took 10 seconds in SQLyog. Optimizing it with DISTINCT reduces the time to 0.57 seconds:

SELECT COUNT(DISTINCT(ip)) FROM t_web_visit
WHERE create_time >= "2022-08-08 00:00:00"
  AND create_time <= "2022-08-08 23:59:59";

Running EXPLAIN shows the query does not use an index and scans 658,559 rows. Adding an index on create_time improves performance dramatically.

ALTER TABLE t_web_visit ADD INDEX _create_time(`create_time`);

After adding the index, the first query runs in 0.18 seconds and the second in 0.046 seconds. The backend page load drops from 10 seconds to about 0.9 seconds.

SpringBoot Integration of Druid

Add the Druid dependency to pom.xml:

<!-- 引入druid数据源 -->
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.8</version>
</dependency>

Configure the datasource in application.yml:

spring:
  datasource:
    username: root
    password: root
    url: jdbc:mysql://localhost:3306/mogu_blog_business?useUnicode=true&allowMultiQueries=true&characterEncoding=utf-8&zeroDateTimeBehavior=convertToNull&transformedBitIsBoolean=true&useSSL=false&serverTimezone=Asia/Shanghai
    driver-class-name: com.mysql.cj.jdbc.Driver
    type: com.alibaba.druid.pool.DruidDataSource
    initialSize: 20
    minIdle: 5
    maxActive: 200
    maxWait: 60000
    timeBetweenEvictionRunsMillis: 60000
    minEvictableIdleTimeMillis: 300000
    validationQuery: SELECT 1 FROM DUAL
    testWhileIdle: true
    testOnBorrow: false
    testOnReturn: false
    poolPreparedStatements: true
    maxPoolPreparedStatementPerConnectionSize: 20
    useGlobalDataSourceStat: true
    connectionProperties: druid.stat.mergeSql=true;druid.stat.slowSqlMillis=500
    filters: stat,wall,log4j

Create a configuration class DruidConfig to define beans for the datasource, the StatViewServlet, the WebStatFilter, and a WallFilter:

@Slf4j
@Configuration
public class DruidConfig {
    @Value("${spring.datasource.url}") private String dbUrl;
    @Value("${spring.datasource.username}") private String username;
    @Value("${spring.datasource.password}") private String password;
    @Value("${spring.datasource.driver-class-name}") private String driverClassName;
    @Value("${spring.datasource.initialSize}") private int initialSize;
    @Value("${spring.datasource.minIdle}") private int minIdle;
    @Value("${spring.datasource.maxActive}") private int maxActive;
    @Value("${spring.datasource.maxWait}") private int maxWait;
    @Value("${spring.datasource.timeBetweenEvictionRunsMillis}") private int timeBetweenEvictionRunsMillis;
    @Value("${spring.datasource.minEvictableIdleTimeMillis}") private int minEvictableIdleTimeMillis;
    @Value("${spring.datasource.validationQuery}") private String validationQuery;
    @Value("${spring.datasource.testWhileIdle}") private boolean testWhileIdle;
    @Value("${spring.datasource.testOnBorrow}") private boolean testOnBorrow;
    @Value("${spring.datasource.testOnReturn}") private boolean testOnReturn;
    @Value("${spring.datasource.poolPreparedStatements}") private boolean poolPreparedStatements;
    @Value("${spring.datasource.maxPoolPreparedStatementPerConnectionSize}") private int maxPoolPreparedStatementPerConnectionSize;
    @Value("${spring.datasource.filters}") private String filters;
    @Value("${spring.datasource.connectionProperties}") private String connectionProperties;

    @Bean
    @Primary
    public DataSource dataSource() {
        DruidDataSource datasource = new DruidDataSource();
        datasource.setUrl(this.dbUrl);
        datasource.setUsername(username);
        datasource.setPassword(password);
        datasource.setDriverClassName(driverClassName);
        datasource.setInitialSize(initialSize);
        datasource.setMinIdle(minIdle);
        datasource.setMaxActive(maxActive);
        datasource.setMaxWait(maxWait);
        datasource.setTimeBetweenEvictionRunsMillis(timeBetweenEvictionRunsMillis);
        datasource.setMinEvictableIdleTimeMillis(minEvictableIdleTimeMillis);
        datasource.setValidationQuery(validationQuery);
        datasource.setTestWhileIdle(testWhileIdle);
        datasource.setTestOnBorrow(testOnBorrow);
        datasource.setTestOnReturn(testOnReturn);
        datasource.setPoolPreparedStatements(poolPreparedStatements);
        datasource.setMaxPoolPreparedStatementPerConnectionSize(maxPoolPreparedStatementPerConnectionSize);
        try {
            List<Filter> filterList = new ArrayList<>();
            filterList.add(wallFilter());
            datasource.setProxyFilters(filterList);
            datasource.setFilters(filters);
        } catch (SQLException e) {
            log.error("druid configuration initialization filter");
        }
        datasource.setConnectionProperties(connectionProperties);
        return datasource;
    }

    @Bean
    public ServletRegistrationBean statViewServlet() {
        ServletRegistrationBean bean = new ServletRegistrationBean(new StatViewServlet(), "/druid/*");
        Map<String, String> initParams = new HashMap<>(2);
        initParams.put("loginUsername", "admin");
        initParams.put("loginPassword", " ");
        initParams.put("allow", "");
        bean.setInitParameters(initParams);
        return bean;
    }

    @Bean
    public FilterRegistrationBean webStatFilter() {
        FilterRegistrationBean bean = new FilterRegistrationBean();
        bean.setFilter(new WebStatFilter());
        Map<String, String> initParams = new HashMap<>(1);
        initParams.put("exclusions", "*.vue,*.js,*.gif,*.jpg,*.bmp,*.png,*.css,*.ico,/druid/*");
        bean.setInitParameters(initParams);
        bean.setUrlPatterns(Arrays.asList("/*"));
        return bean;
    }

    @Bean
    public WallFilter wallFilter() {
        WallFilter wallFilter = new WallFilter();
        WallConfig config = new WallConfig();
        config.setMultiStatementAllow(true);
        config.setNoneBaseStatementAllow(true);
        wallFilter.setConfig(config);
        return wallFilter;
    }
}

After these steps, Druid provides real‑time SQL performance monitoring and helps keep the application responsive.