基于SSH(JSch)的MySQL连接池实现：解决重复连接慢问题

Hey there! Let's cut through the confusion here—you don't need two connection pools, and fixing the slow inserts is simpler than you think. The core issue is that creating a new database connection every time is a huge performance drain, and connection pools solve this perfectly, even with your existing SSH tunnel setup.

First: Fix the SSH Tunnel Reuse #

Before diving into connection pools, make sure you're not re-establishing the SSH tunnel every time you insert data. Your JSch tunnel should be initialized once when your project starts (e.g., in a static initialization block, or a startup method if you're using a framework like Spring). Reusing the same tunnel avoids the overhead of re-negotiating SSH connections repeatedly.

The Simplest Connection Pool Solution: HikariCP #

HikariCP is the go-to for Java projects—it's lightweight, fast, and requires minimal configuration, which is ideal for a university project. Here's how to adapt it to your SSH tunnel setup:

Step 1: Understand the Tunnel + Connection Pool Dynamic

Your SSH tunnel maps a local port (e.g., localhost:33060) to the remote MySQL server's port. The connection pool only needs to target this local mapped port—all pool connections will automatically route through the existing SSH tunnel. No separate pool for SSH is needed.

Step 2: Add HikariCP to Your Project

If you're using Maven, add this dependency to your pom.xml:

<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
    <version>5.0.1</version> <!-- Use the latest stable version -->
</dependency>

Step 3: Create a Reusable Connection Pool Singleton

Create a simple class to manage your connection pool as a singleton (so you only have one pool instance running):

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import java.sql.Connection;
import java.sql.SQLException;

public class DBConnectionPool {
    private static HikariDataSource dataSource;

    // Initialize SSH tunnel and connection pool once on class load
    static {
        // First, initialize your JSch SSH tunnel here (reuse this single tunnel)
        // Example JSch setup (adjust with your server details):
        /*
        JSch jsch = new JSch();
        Session session = jsch.getSession("ssh_username", "ssh_host", 22);
        session.setPassword("ssh_password");
        session.setConfig("StrictHostKeyChecking", "no");
        session.connect();
        session.setPortForwardingL(33060, "mysql_host", 3306); // Local port 33060 maps to remote MySQL 3306
        */

        // Now configure HikariCP
        HikariConfig config = new HikariConfig();
        config.setJdbcUrl("jdbc:mysql://localhost:33060/your_database_name?useSSL=false&serverTimezone=UTC");
        config.setUsername("mysql_username");
        config.setPassword("mysql_password");
        config.setDriverClassName("com.mysql.cj.jdbc.Driver");

        // Basic pool settings for a university project
        config.setMaximumPoolSize(10); // Max 10 concurrent connections
        config.setMinimumIdle(2); // Keep 2 connections warm when idle
        config.setConnectionTimeout(30000); // Wait 30s for a connection before timing out

        dataSource = new HikariDataSource(config);
    }

    // Get a connection from the pool
    public static Connection getConnection() throws SQLException {
        return dataSource.getConnection();
    }

    // Shutdown the pool when your project ends (e.g., in a shutdown hook)
    public static void shutdown() {
        if (dataSource != null) {
            dataSource.close();
        }
    }
}

Step 4: Use the Pool for Inserts

Instead of creating a new Connection every time, grab one from the pool, use it, and close it (closing returns it to the pool, doesn't terminate the connection):

try (Connection conn = DBConnectionPool.getConnection();
     PreparedStatement stmt = conn.prepareStatement("INSERT INTO your_table (col1, col2) VALUES (?, ?)")) {

    // For bulk inserts (even faster!)
    for (YourDataObject data : yourDataList) {
        stmt.setString(1, data.getCol1());
        stmt.setInt(2, data.getCol2());
        stmt.addBatch(); // Add to batch instead of executing one by one
    }
    stmt.executeBatch(); // Execute all inserts at once

} catch (SQLException e) {
    e.printStackTrace();
}

Bonus: Bulk Inserts for Even More Speed #

If you're inserting multiple records at once, using addBatch() and executeBatch() (as shown above) will drastically reduce the number of round-trips to the database, which is another big win for speed—way better than inserting one record at a time.

Why You Don't Need Two Connection Pools #

Your SSH tunnel is a network-level forwarder. All database connections from the pool go through this single tunnel, so there's no need to split connections into separate pools. The pool manages the database connections, and the tunnel manages the secure routing—they work independently together.

Hope this gets your insertion speed up quickly—happy coding on your university project!

内容的提问来源于stack exchange，提问作者user9109814