Java, JDBC : Does closing a PreparedStatement also free up its memory footprint in the database connection?

Question

据我了解，每次初始化 PreparedStatement 时，语句都会缓存在为数据库连接分配的内存中。因此，如果初始化过多的 PreparedStatement 变量，则存在溢出连接可用内存的风险。

1. 是否通过在 PreparedStatement 实例上调用 close() 释放缓存内存？

2. 包含相同 SQL 的两个 PreparedStatement 是否会创建重复的缓存事件，或者数据库是否足够智能，不会缓存重复的 PreparedStatement 的新实例？

例一，连接内存会溢出吗？:

while (true) {
    PreparedStatement ps = connection.prepareStatement("SELECT id + ? FROM tbl");
    ps.setDouble(1, Math.random());
    ps.executeQuery();
    ps.close();
}

如果可以，那么这个呢？:

while (true) {
    PreparedStatement ps = connection.prepareStatement("SELECT id FROM tbl");
    ps.executeQuery();
    ps.close();
}

Answer 1

两者的答案:这完全取决于 JDBC 驱动程序。

例如对于 PostgreSQL JDBC 驱动程序，请参阅 Server Prepared Statements :

The driver uses server side prepared statements by default when PreparedStatement API is used. In order to get to server-side prepare, you need to execute the query 5 times (that can be configured via prepareThreshold connection property). An internal counter keeps track of how many times the statement has been executed and when it reaches the threshold it will start to use server side prepared statements.

It is generally a good idea to reuse the same PreparedStatement object for performance reasons, however the driver is able to server-prepare statements automatically across connection.prepareStatement(...) calls.

[...]

Server-prepared statements consume memory both on the client and the server, so pgjdbc limits the number of server-prepared statements per connection. It can be configured via preparedStatementCacheQueries (default 256, the number of queries known to pgjdbc), and preparedStatementCacheSizeMiB (default 5, that is the client side cache size in megabytes per connection). Only a subset of statement cache is server-prepared as some of the statements might fail to reach prepareThreshold.

对于 MS SQL Server JDBC 驱动程序，请参阅 Prepared Statement Metadata Caching for the JDBC Driver .

对于 MySQL JDBC 驱动程序，请参阅对 Prepared Statement Cache with MySQL & JDBC 的回答.

我没有看到 Oracle JDBC 驱动程序有任何此类功能。