为什么以下简单的Java代码使用来自commons.dbcp块的池连接？

I have written a simple Java code, which attempts to store files in an hsqldb database. All it does is reads the files from a certain directory and puts them in the DB. It is single threaded, but I am using pooled connections from the apache commons.dbcp in order to be able to cope with the multithreaded access later.

我编写了一个简单的Java代码，它试图将文件存储在hsqldb数据库中。它只是从某个目录中读取文件并将它们放入数据库中。它是单线程的，但我使用来自apache commons.dbcp的池连接，以便以后能够处理多线程访问。

The problem is that the code blocks after reading a few files.

问题是在读取几个文件后代码块。

Please, find below the entire source code.

请在下面找到完整的源代码。

Program.java

Program.java

import java.io.File;
import java.io.IOException;
import java.sql.SQLException;

import javax.sql.DataSource;

import org.apache.commons.dbcp.ConnectionFactory;
import org.apache.commons.dbcp.DriverManagerConnectionFactory;
import org.apache.commons.dbcp.PoolableConnectionFactory;
import org.apache.commons.dbcp.PoolingDataSource;
import org.apache.commons.pool.KeyedObjectPoolFactory;
import org.apache.commons.pool.impl.GenericKeyedObjectPoolFactory;
import org.apache.commons.pool.impl.GenericObjectPool;

public class Program {
  public static DataSource getPoolingDataSource(String driverClass, String url, String user, String password) throws ClassNotFoundException {
    Class.forName(driverClass);
    ConnectionFactory connectionFactory = new DriverManagerConnectionFactory(url, user, password);
    GenericObjectPool connectionPool = new GenericObjectPool();
    KeyedObjectPoolFactory stmtPool = new GenericKeyedObjectPoolFactory(null);
    new PoolableConnectionFactory(connectionFactory, connectionPool, stmtPool, null, false, true);
    return new PoolingDataSource(connectionPool);
  }

  public static void main(String[] args) throws ClassNotFoundException, SQLException, IOException, InterruptedException {
    String root = args.length == 0 ? "c:/Work/java/ntxdb" : args[0];

    Runtime run = Runtime.getRuntime();
    Process pr = run.exec("cmd /c del /s/q c:\\tmp\\file.db*");
    pr.waitFor();
    DataSource ds = getPoolingDataSource("org.hsqldb.jdbcDriver", "jdbc:hsqldb:file:c:/tmp/file.db", "sa", "");
    HsqldbFileStorage fs = new HsqldbFileStorage(ds);
    putFiles(fs, new File(root));
  }

  private static void putFiles(HsqldbFileStorage fs, File parent) throws IOException, SQLException {
    for (File child : parent.listFiles()) {
      if (child.isDirectory()) {
        putFiles(fs, child);
      } else {
        System.out.println(child.getCanonicalPath());
        fs.put(child);
      }
    }
  }
}

HsqldbFileStorage.java

HsqldbFileStorage.java

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import javax.sql.DataSource;

public class HsqldbFileStorage {
  private static final String SET_SQL = "MERGE INTO test" +
    "  USING (VALUES ?, CAST(? AS BLOB)) I (name, data)" +
    "  ON (test.name=I.name)" +
    "  WHEN MATCHED THEN UPDATE SET test.data = I.data" +
    "  WHEN NOT MATCHED THEN INSERT (name, data) VALUES (I.name, I.data)";
  private DataSource m_dataSource;

  public HsqldbFileStorage(DataSource dataSource) throws SQLException {
    super();
    m_dataSource = dataSource;
    Connection c = dataSource.getConnection();
    c.createStatement().execute("Create Cached Table IF NOT EXISTS test (name VARCHAR(256), data BLOB(10M));");
  }

  public void put(File file) throws IOException, SQLException {
    put(file.getCanonicalPath(), file);
  }

  public void put(String name, File file) throws IOException, SQLException {
    InputStream is = new BufferedInputStream(new FileInputStream(file));
    try {
      put(name, is);
    } finally {
      is.close();
    }
  }

  public void put(String name, InputStream data) throws SQLException, IOException {
    PreparedStatement set = m_dataSource.getConnection().prepareStatement(SET_SQL);
    try {
      set.setString(1, name);
      set.setBinaryStream(2, data);
      set.executeUpdate();
    } finally {
      set.close();
    }
  }
}

The code depends on commons-dbcp 1.4, commons-pool 1.6 and hsqldb 2.2.9

代码依赖于commons-dbcp 1.4，commons-pool 1.6和hsqldb 2.2.9

Running it on the project directory itself should put 62 files in the DB (I have much more files there than the two aforementioned source files), printing a line for each file.

在项目目录本身上运行它应该在数据库中放入62个文件（我有比前面提到的两个源文件更多的文件），为每个文件打印一行。

Unfortunately, it blocks on the eighth file with the following stack trace:

不幸的是，它使用以下堆栈跟踪阻止第八个文件：

  at java.lang.Object.wait(Object.java:-1)
  at java.lang.Object.wait(Object.java:485)
  at org.apache.commons.pool.impl.GenericObjectPool.borrowObject(GenericObjectPool.java:1118)
  at org.apache.commons.dbcp.PoolingDataSource.getConnection(PoolingDataSource.java:106)
  at HsqldbFileStorage.put(HsqldbFileStorage.java:41)
  at HsqldbFileStorage.put(HsqldbFileStorage.java:34)
  at HsqldbFileStorage.put(HsqldbFileStorage.java:28)
  at Program.putFiles(Program.java:42)
  at Program.putFiles(Program.java:39)
  at Program.putFiles(Program.java:39)
  at Program.main(Program.java:33)

What am I doing wrong?

我究竟做错了什么？

1 个解决方案

#1

You are calling:

你在打电话：

m_dataSource.getConnection()

in your put(String, InputStream) method. This creates a new connection, which you never close.

在你的put（String，InputStream）方法中。这将创建一个您永远不会关闭的新连接。

So when you are hitting putting 62 files in at some stage it will hit the maximum number of connections in your pool, after which the pool will wait for a connection to be returned to the pool.

因此，当您在某个阶段打出62个文件时，它将达到池中的最大连接数，之后池将等待连接返回到池中。

If you modify your method as such:

如果您修改方法：

  public void put(String name, InputStream data) throws SQLException, IOException {
    Connection con = null;
    PreparedStatement set = null;
    try {
      con = m_dataSource.getConnection();
      set.prepareStatement(SET_SQL);
      set.setString(1, name);
      set.setBinaryStream(2, data);
      set.executeUpdate();
    } finally {
      if (set != null) {
        set.close();
      }
      if (con != null) {
        con.close();
      }
    }
  }

Note, when you access connections through a pool, you still need to call close. This won't actually close the connection but return it to the pool.

请注意，当您通过池访问连接时，仍需要调用close。这实际上不会关闭连接，而是将其返回到池中。

#1