38、JDBC 性能优化：预编译语句与连接池的应用

最新推荐文章于 2025-10-26 17:10:37 发布

keras9composer

最新推荐文章于 2025-10-26 17:10:37 发布

阅读量59

点赞数

CC 4.0 BY-SA版权

分类专栏： Servlets与JSP深度解析文章标签： JDBC 预编译语句连接池

本文链接：https://blog.youkuaiyun.com/keras9composer/article/details/150464301

Servlets与JSP深度解析专栏收录该内容

42 篇文章 ¥499.90

订阅专栏¥69.90

会员秒杀 ¥9.9 重磅福利

超级会员免费看

JDBC 性能优化：预编译语句与连接池的应用

在数据库操作中，性能优化是一个至关重要的话题。本文将介绍两种提升数据库操作性能的有效方法：预编译语句（Prepared Statements）和连接池（Connection Pooling），并通过具体的代码示例和性能测试结果来展示它们的优势。

预编译语句（Prepared Statements）

当需要多次执行相似的 SQL 语句时，使用预编译语句比每次执行原始查询更为高效。预编译语句的核心思想是创建一个参数化的标准语句，在实际使用前将其发送到数据库进行编译。在语句中，使用问号（?）来表示需要替换值的位置。每次使用预编译语句时，只需使用相应的 setXxx 方法（如 setInt 、 setString 等）替换标记的参数，然后使用 executeQuery （如果需要返回 ResultSet ）或 execute / executeUpdate （用于产生副作用）来执行语句。

以下是一个给员工数据库中所有人员加薪的示例代码：

Connection connection = DriverManager.getConnection(url, user, password);
String template = "UPDATE employees SET salary = ? WHERE id = ?";
PreparedStatement statement = connection.prepareStatement(template);
float[] newSalaries = getNewSalaries();
int[] employeeIDs = getIDs();
for(int i=0; i<employeeIDs.length; i++) {
    statement.setFloat(1, newSalaries[i]);
    statement.setInt(2, employeeIDs[i]);
    statement.execute();
}

预编译语句的性能优势因服务器对预编译查询的支持程度以及驱动程序处理原始查询的效率而异。为了测试性能差异，我们可以使用以下代码：

package coreservlets;
import java.sql.*;

public class PreparedStatements {
    public static void main(String[] args) {
        if (args.length < 5) {
            printUsage();
            return;
        }
        String vendorName = args[4];
        int vendor = DriverUtilities.getVendor(vendorName);
        if (vendor == DriverUtilities.UNKNOWN) {
            printUsage();
            return;
        }
        String driver = DriverUtilities.getDriver(vendor);
        String host = args[0];
        String dbName = args[1];
        String url = DriverUtilities.makeURL(host, dbName, vendor);
        String username = args[2];
        String password = args[3];
        boolean print = false;
        if ((args.length > 5) && (args[5].equals("print"))) {
            print = true;
        }
        Connection connection = getConnection(driver, url, username, password);
        if (connection != null) {
            doPreparedStatements(connection, print);
            doRawQueries(connection, print);
        }
    }

    private static void doPreparedStatements(Connection conn, boolean print) {
        try {
            String queryFormat = "SELECT lastname FROM employees WHERE salary > ?";
            PreparedStatement statement = conn.prepareStatement(queryFormat);
            long startTime = System.currentTimeMillis();
            for(int i=0; i<40; i++) {
                statement.setFloat(1, i*5000);
                ResultSet results = statement.executeQuery();
                if (print) {
                    showResults(results);
                }
            }
            long stopTime = System.currentTimeMillis();
            double elapsedTime = (stopTime - startTime)/1000.0;
            System.out.println("Executing prepared statement 40 times took " + elapsedTime + " seconds.");
        } catch(SQLException sqle) {
            System.out.println("Error executing statement: " + sqle);
        }
    }

    public static void doRawQueries(Connection conn, boolean print) {
        try {
            String queryFormat = "SELECT lastname FROM employees WHERE salary > ";
            Statement statement = conn.createStatement();
            long startTime = System.currentTimeMillis();
            for(int i=0; i<40; i++) {
                ResultSet results = statement.executeQuery(queryFormat + (i*5000));
                if (print) {
                    showResults(results);
                }
            }
            long stopTime = System.currentTimeMillis();
            double elapsedTime = (stopTime - startTime)/1000.0;
            System.out.println("Executing raw query 40 times took " + elapsedTime + " seconds.");
        } catch(SQLException sqle) {
            System.out.println("Error executing query: " + sqle);
        }
    } 

    private static void showResults(ResultSet results) throws SQLException {
        while(results.next()) {
            System.out.print(results.getString(1) + " ");
        }
        System.out.println();
    }

    private static Connection getConnection(String driver, String url, String username, String password) {
        try {
            Class.forName(driver);
            Connection connection = DriverManager.getConnection(url, username, password);
            return(connection);
        } catch(ClassNotFoundException cnfe) {
            System.err.println("Error loading driver: " + cnfe);
            return(null);
        } catch(SQLException sqle) {
            System.err.println("Error connecting: " + sqle);
            return(null);
        }
    }

    private static void printUsage() {
        System.out.println("Usage: PreparedStatements host dbName username password oracle|sybase [print].");
    }
}

通过以上代码，我们可以对比预编译语句和原始查询在执行 40 次查询时的耗时。测试结果表明，在不同的网络连接和数据库类型下，预编译语句通常能显著减少执行时间。例如，在使用 28.8K 调制解调器连接到 Oracle 数据库时，预编译语句执行 40 次查询平均耗时 17.5 秒，而原始查询平均耗时 35 秒；在使用快速 LAN 连接到同一 Oracle 数据库时，预编译语句平均耗时 0.22 秒，原始查询平均耗时 0.31 秒。但在 Sybase 数据库中，预编译语句和原始查询的执行时间几乎相同。

连接池（Connection Pooling）

打开数据库连接是一个耗时的过程，对于短查询来说，打开连接的时间可能比实际执行数据库检索的时间还要长。因此，在反复连接同一数据库的应用程序中，重用 Connection 对象是很有意义的。连接池就是一种预先分配数据库连接并在客户端连接时进行回收的技术。

一个连接池类应该具备以下功能：
1. 预分配连接 ：在类的构造函数中完成连接的预分配。预先分配更多的连接可以在后续有大量并发请求时加快速度，但会导致初始延迟。
2. 管理可用连接 ：当需要连接且有空闲连接时，将其放入繁忙连接列表并返回。在返回连接之前，需要确认连接是否仍然打开，如果连接已关闭，则丢弃该连接并重复获取连接的过程。
3. 分配新连接 ：当需要连接且没有空闲连接，并且未达到连接限制时，启动一个后台线程来分配新连接，然后等待第一个可用连接。
4. 等待连接可用 ：当没有空闲连接且已达到连接限制时，使用 wait 方法等待连接可用，避免持续轮询。
5. 在需要时关闭连接 ：可以显式地关闭所有连接，但需要注意在调用该方法时确保没有连接正在使用。

以下是一个连接池类的完整代码：

package coreservlets;
import java.sql.*;
import java.util.*;

public class ConnectionPool implements Runnable {
    private String driver, url, username, password;
    private int maxConnections;
    private boolean waitIfBusy;
    private Vector availableConnections, busyConnections;
    private boolean connectionPending = false;

    public ConnectionPool(String driver, String url, String username, String password, int initialConnections, int maxConnections, boolean waitIfBusy) throws SQLException {
        this.driver = driver;
        this.url = url;
        this.username = username;
        this.password = password;
        this.maxConnections = maxConnections;
        this.waitIfBusy = waitIfBusy;
        if (initialConnections > maxConnections) {
            initialConnections = maxConnections;
        }
        availableConnections = new Vector(initialConnections);
        busyConnections = new Vector();
        for(int i=0; i<initialConnections; i++) {
            availableConnections.addElement(makeNewConnection());
        }
    }

    public synchronized Connection getConnection() throws SQLException {
        if (!availableConnections.isEmpty()) {
            Connection existingConnection = (Connection)availableConnections.lastElement();
            int lastIndex = availableConnections.size() - 1;
            availableConnections.removeElementAt(lastIndex);
            if (existingConnection.isClosed()) {
                notifyAll();
                return(getConnection());
            } else {
                busyConnections.addElement(existingConnection);
                return(existingConnection);
            }
        } else {
            if ((totalConnections() < maxConnections) && !connectionPending) {
                makeBackgroundConnection();
            } else if (!waitIfBusy) {
                throw new SQLException("Connection limit reached");
            }
            try {
                wait();
            } catch(InterruptedException ie) {}
            return(getConnection());
        }
    }

    private void makeBackgroundConnection() {
        connectionPending = true;
        try {
            Thread connectThread = new Thread(this);
            connectThread.start();
        } catch(OutOfMemoryError oome) {
            // Give up on new connection
        }
    }

    public void run() {
        try {
            Connection connection = makeNewConnection();
            synchronized(this) {
                availableConnections.addElement(connection);
                connectionPending = false;
                notifyAll();
            }
        } catch(Exception e) {
            // Give up on new connection and wait for existing one to free up.
        }
    }

    private Connection makeNewConnection() throws SQLException {
        try {
            Class.forName(driver);
            Connection connection = DriverManager.getConnection(url, username, password);
            return(connection);
        } catch(ClassNotFoundException cnfe) {
            throw new SQLException("Can’t find class for driver: " + driver);
        }
    }

    public synchronized void free(Connection connection) {
        busyConnections.removeElement(connection);
        availableConnections.addElement(connection);
        notifyAll(); 
    }

    public synchronized int totalConnections() {
        return(availableConnections.size() + busyConnections.size());
    }

    public synchronized void closeAllConnections() {
        closeConnections(availableConnections);
        availableConnections = new Vector();
        closeConnections(busyConnections);
        busyConnections = new Vector();
    }

    private void closeConnections(Vector connections) {
        try {
            for(int i=0; i<connections.size(); i++) {
                Connection connection = (Connection)connections.elementAt(i);
                if (!connection.isClosed()) {
                    connection.close();
                }
            }
        } catch(SQLException sqle) {
            // Ignore errors; garbage collect anyhow
        }
    }

    public synchronized String toString() {
        String info = "ConnectionPool(" + url + "," + username + ")" + ", available=" + availableConnections.size() + ", busy=" + busyConnections.size() + ", max=" + maxConnections;
        return(info);
    }
}

连接池的性能测试

为了验证连接池的性能优势，我们进行了一个案例研究。使用一个简单的 Servlet，在其 init 方法中分配一个连接池，然后对于每个请求，执行一个简单的数据库查询并将结果放入 HTML 表格中。通过在一个包含 25 个框架单元格的 HTML 文档中放置该 Servlet 的副本，模拟 25 个近乎同时的 HTTP 请求，从而进行 25 个近乎同时的数据库查询。

同时，我们还测试了使用单个连接的“池”和不使用连接池的情况。测试结果如下表所示：
| 条件 | 平均时间 |
| — | — |
| 慢速调制解调器连接到数据库，10 个初始连接，50 个最大连接（ConnectionPoolServlet） | 11 秒 |
| 慢速调制解调器连接到数据库，回收单个连接（ConnectionPoolServlet2） | 22 秒 |
| 慢速调制解调器连接到数据库，不使用连接池（ConnectionPoolServlet3） | 82 秒 |

从测试结果可以看出，使用连接池可以显著提高数据库操作的性能，尤其是在高并发场景下。

综上所述，预编译语句和连接池是提升数据库操作性能的有效方法。在实际应用中，我们可以根据具体的数据库类型、网络连接和业务需求，合理使用这两种技术来优化数据库操作。

JDBC 性能优化：预编译语句与连接池的应用（续）

预编译语句与连接池的综合应用

在实际的数据库应用中，预编译语句和连接池并不是相互独立的，而是可以结合使用以进一步提升性能。以下是一个综合应用预编译语句和连接池的示例流程：

初始化连接池 ：在应用启动时，创建一个连接池对象，并预分配一定数量的数据库连接。

// 初始化连接池
ConnectionPool pool = new ConnectionPool("com.mysql.jdbc.Driver", "jdbc:mysql://localhost:3306/mydb", "username", "password", 10, 50, true);

从连接池获取连接 ：在需要执行数据库操作时，从连接池中获取一个可用的连接。

try {
    Connection conn = pool.getConnection();
    // 使用连接执行预编译语句
    String queryFormat = "SELECT lastname FROM employees WHERE salary > ?";
    PreparedStatement statement = conn.prepareStatement(queryFormat);
    for(int i = 0; i < 40; i++) {
        statement.setFloat(1, i * 5000);
        ResultSet results = statement.executeQuery();
        // 处理查询结果
    }
    // 释放连接
    pool.free(conn);
} catch (SQLException e) {
    e.printStackTrace();
}

使用预编译语句执行查询 ：在获取到连接后，创建预编译语句对象，并设置参数，执行查询操作。
释放连接 ：在完成数据库操作后，将连接释放回连接池，以便其他请求可以继续使用。

通过以上步骤，我们可以充分利用预编译语句和连接池的优势，提高数据库操作的性能和效率。

性能优化的注意事项

在使用预编译语句和连接池进行性能优化时，还需要注意以下几点：

数据库驱动的选择 ：不同的数据库驱动对预编译语句和连接池的支持程度可能不同，因此需要选择合适的数据库驱动。例如，在使用 MySQL 数据库时，建议使用 MySQL Connector/J 驱动。
连接池参数的配置 ：连接池的参数配置对性能有重要影响，需要根据实际情况进行调整。例如，初始连接数、最大连接数、等待超时时间等参数都需要根据应用的并发量和数据库的性能进行合理设置。
异常处理 ：在使用预编译语句和连接池时，需要正确处理可能出现的异常。例如，在获取连接时可能会出现连接超时、数据库不可用等异常，需要进行相应的处理，避免应用崩溃。
资源管理 ：在使用完预编译语句和连接后，需要及时释放资源，避免资源泄漏。例如，在使用完 ResultSet 对象后，需要调用 close 方法关闭结果集。

总结

本文介绍了预编译语句和连接池这两种提升数据库操作性能的有效方法，并通过具体的代码示例和性能测试结果展示了它们的优势。预编译语句可以在多次执行相似 SQL 语句时提高效率，而连接池可以减少打开和关闭数据库连接的开销，从而提高应用的性能和响应速度。

在实际应用中，我们可以根据具体的业务需求和数据库环境，合理使用预编译语句和连接池，同时注意性能优化的注意事项，以达到最佳的性能效果。通过不断地优化和调整，我们可以让数据库应用更加高效、稳定地运行。

以下是一个简单的 mermaid 流程图，展示了使用连接池和预编译语句的基本流程：

graph TD;
    A[应用启动] --> B[初始化连接池];
    B --> C[等待请求];
    C --> D{有请求到来};
    D -- 是 --> E[从连接池获取连接];
    E --> F[创建预编译语句];
    F --> G[设置参数并执行查询];
    G --> H[处理查询结果];
    H --> I[释放连接到连接池];
    I --> C;
    D -- 否 --> C;

通过以上的介绍和示例，相信你对预编译语句和连接池有了更深入的理解，并且可以在实际项目中应用这些技术来优化数据库操作。如果你在使用过程中遇到任何问题，欢迎留言讨论。