Spring JDBC连接池和InputStream结果 [英] Spring JDBC connection pool and InputStream results

问题描述

我正在编写一个Web服务，该服务允许用户发布文件，然后通过URL检索它们(基本上将其视为RESTful Amazon S3).我遇到的问题是从Oracle查询(Spring JDBC)返回一个byte []，然后返回InputStream，然后将数据分块地流回客户端.这个(IMO)是一个更好的主意，因为我对文件没有大小限制，并且我不希望内存中有2GB字节数组.

I am writing a webservice that allows users to post files and then retrieve them at a URL (basically think of it as the RESTful Amazon S3). The issue I came across was rather then return a byte[] from my Oracle query (Spring JDBC) I am returning an InputStream and then streaming the data back to the client in chunks. This (IMO) is a much better idea since I put no size restriction on the file and I don't want 2GB byte arrays in memory.

起初它似乎工作正常，但是我遇到了一个负载很重的情况，有时在先前的servlet可以发送文件之前，有时会重用Connection.似乎在JDBC调用返回InputStream之后，Connection将返回到池中(Spring会调用conn.close()，但不会清除关联的ResultSet).因此，如果没有其他任何关于Connection的请求，那么InputStream仍然有效并且可以从中读取，但是如果Connection被赋予新的请求，则InputStream将为null，并且先前的请求将失败.

At first it seemed to work fine, but I ran into a case during heavy load that sometimes a Connection would get reused before the previous servlet could send the file. It seems after the JDBC call that returned the InputStream, the Connection would be returned to the pool (Spring would call conn.close(), but not clear the associated ResultSet). So if no other request was given that Connection then the InputStream would still be valid and could be read from, but if the Connection was given to a new request then the InputStream would be null and the previous request would fail.

我的解决方案是创建一个InputStream的子类，该类也将Connection用作构造函数arg，并且在重写的 public close()方法中也关闭Connection.我不得不放弃Spring JDBC，只进行常规的PreparedStatement调用，否则Spring总是会将连接返回到池中.

My solution was to create a subclass of InputStream that also takes a Connection as a constructor arg, and in the overridden public close() method also close the Connection. I had to ditch the Spring JDBC and just make a normal PreparedStatement call, otherwise Spring would always return the connection to the pool.

public class ConnectionInputStream extends InputStream {

   private Connection conn;
   private InputStream stream;

   public ConnectionInputStream(InputStream s, Connection c) {
      conn = c;
      stream = s;
   }

   // all InputStream methods call the same method on the variable stream

   @Override
   public void close() throws IOException {
      try {
         stream.close();
      } catch (IOException ioex) {
          //do something
      } finally {
         try {
             conn.close();
         } catch (SQLException sqlex) {
             //ignore
         }
      }
   }
} 

有人能找到更优雅的解决方案，还是看到我的解决方案有任何明显的问题?另外，此代码不是从我的实际代码中剪切/粘贴的，因此，如果有错字，请忽略它.

Does anyone have a more elegant solution, or see any glaring problems with my solution? Also this code wasn't cut/paste from my actual code so if there is a typo just ignore it.

推荐答案

不幸的是，当您问这个问题时，我的想象力变得疯狂了.我不知道这种解决方案是否被认为更优雅.但是，这些类很简单并且易于重用，因此如果它们不令人满意，您可能会发现它们的用途.您将在最后看到一切……

Unfortunately, my imagination went wild when you asked this question. I don't know if this solution is considered more elegant. However, these classes are simple and easily re-usable so you may find a use for them if they are not satisfactory. You will see everything coming together at the end...

public class BinaryCloseable implements Closeable {

    private Closeable first;
    private Closeable last;

    public BinaryCloseable(Closeable first, Closeable last) {
        this.first = first;
        this.last = last;
    }

    @Override
    public void close() throws IOException {
        try {
            first.close();
        } finally {
            last.close();
        }
    }

}

BinaryCloseable由CompositeCloseable使用:

public class CompositeCloseable implements Closeable {

    private Closeable target;

    public CompositeCloseable(Closeable... closeables) {
        target = new Closeable() { public void close(){} };
        for (Closeable closeable : closeables) {
            target = new BinaryCloseable(target, closeable);
        }
    }

    @Override
    public void close() throws IOException {
        target.close();
    }

}

ResultSetCloser关闭ResultSet对象:

public class ResultSetCloser implements Closeable {

    private ResultSet resultSet;

    public ResultSetCloser(ResultSet resultSet) {
        this.resultSet = resultSet;
    }

    @Override
    public void close() throws IOException {
        try {
            resultSet.close();
        } catch (SQLException e) {
            throw new IOException("Exception encountered while closing result set", e);
        }
    }

}

PreparedStatementCloser关闭PreparedStatement对象:

public class PreparedStatementCloser implements Closeable {

    private PreparedStatement preparedStatement;

    public PreparedStatementCloser(PreparedStatement preparedStatement) {
        this.preparedStatement = preparedStatement;
    }

    @Override
    public void close() throws IOException {
        try {
            preparedStatement.close();
        } catch (SQLException e) {
            throw new IOException("Exception encountered while closing prepared statement", e);
        }
    }

}

ConnectionCloser关闭Connection对象:

public class ConnectionCloser implements Closeable {

    private Connection connection;

    public ConnectionCloser(Connection connection) {
        this.connection = connection;
    }

    @Override
    public void close() throws IOException {
        try {
            connection.close();
        } catch (SQLException e) {
            throw new IOException("Exception encountered while closing connection", e);
        }
    }

}

我们现在将您最初的InputStream想法重构为:

We now refactor your original InputStream idea into:

public class ClosingInputStream extends InputStream {

    private InputStream stream;
    private Closeable closer;

    public ClosingInputStream(InputStream stream, Closeable closer) {
        this.stream = stream;
        this.closer = closer;
    }

    // The other InputStream methods...

    @Override
    public void close() throws IOException {
        closer.close();
    }

}

最后，它们全部组合为:

Finally, it all comes together as:

new ClosingInputStream(
        stream,
        new CompositeCloseable(
                stream,
                new ResultSetCloser(resultSet),
                new PreparedStatementCloser(statement),
                new ConnectionCloser(connection)
            )
    );

调用此ClosingInputStream的close()方法时，实际上是发生了这种情况(为清晰起见，省略了异常处理):

When this ClosingInputStream's close() method is called, this is effectively what happens (with exception handling omitted for clarity's sake):

public void close() {
    try {
        try {
            try {
                try {
                    // This is empty due to the first line in `CompositeCloseable`'s constructor
                } finally {
                    stream.close();
                }
            } finally {
                resultSet.close();
            }
        } finally {
            preparedStatement.close();
        }
    } finally {
        connection.close();
    }
}

您现在可以随意关闭任意多个Closeable对象.

You're now free to close as many Closeable objects as you like.

这篇关于Spring JDBC连接池和InputStream结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！