@Override
  public HTTPResponse update(HTTPRequest request, HTTPResponse response) {
    Key key = Key.create(request, response);
    Connection connection = getConnection();

    PreparedStatement statement = null;
    try {
      JdbcUtil.startTransaction(connection);
      statement =
          connection.prepareStatement(
              "update response set headers = ?, cachetime = ? where uri = ? and vary = ?");
      statement.setString(1, response.getHeaders().toJSON());
      statement.setTimestamp(2, new Timestamp(DateTimeUtils.currentTimeMillis()));
      statement.setString(3, key.getURI().toString());
      statement.setString(4, key.getVary().toJSON());
      statement.executeUpdate();
      connection.commit();
      return getImpl(connection, key);
    } catch (SQLException e) {
      JdbcUtil.rollback(connection);
      JdbcUtil.close(connection);
      throw new DataAccessException(e);
    } finally {
      JdbcUtil.endTransaction(connection);
      JdbcUtil.close(statement);
    }
  }
 @Override
 public void clear() {
   Connection connection = getConnection();
   PreparedStatement statement = null;
   try {
     JdbcUtil.startTransaction(connection);
     statement = connection.prepareStatement("delete from response");
     statement.executeUpdate();
     connection.commit();
   } catch (SQLException e) {
     throw new IllegalStateException("Unable to clear", e);
   } finally {
     JdbcUtil.endTransaction(connection);
     JdbcUtil.close(statement);
     JdbcUtil.close(connection);
   }
 }
 @Override
 public void invalidate(final URI uri) {
   Connection connection = getConnection();
   PreparedStatement statement = null;
   try {
     JdbcUtil.startTransaction(connection);
     statement = connection.prepareStatement("delete from response where uri = ?");
     statement.setString(1, uri.toString());
     statement.executeUpdate();
     connection.commit();
   } catch (SQLException e) {
     JdbcUtil.rollback(connection);
     throw new DataAccessException("Unable to invalidate", e);
   } finally {
     JdbcUtil.endTransaction(connection);
     JdbcUtil.close(statement);
     JdbcUtil.close(connection);
   }
 }
  @Override
  public HTTPResponse insert(HTTPRequest request, HTTPResponse response) {
    Key key = Key.create(request, response);
    Connection connection = getConnection();

    String sql =
        "insert into response(uri, vary, status, headers, payload, mimeType, cachetime) values (?, ?, ?, ?, ?, ?, ?)";
    PreparedStatement statement = null;
    try {
      JdbcUtil.startTransaction(connection);
      invalidate(key, connection);
      statement = connection.prepareStatement(sql);
      statement.setString(1, key.getURI().toString());
      statement.setString(2, key.getVary().toJSON());
      statement.setInt(3, response.getStatus().getCode());
      statement.setString(4, response.getHeaders().toJSON());
      InputStream inputStream = null;
      if (response.hasPayload() && response.getPayload().isAvailable()) {
        statement.setString(6, response.getPayload().getMimeType().toString());
        inputStream = response.getPayload().getInputStream();
        statement.setBinaryStream(5, inputStream);
      } else {
        statement.setNull(5, Types.BLOB);
        statement.setNull(6, Types.VARCHAR);
      }
      statement.setTimestamp(7, new Timestamp(DateTimeUtils.currentTimeMillis()));
      try {
        statement.executeUpdate();
      } finally {
        IOUtils.closeQuietly(inputStream);
      }
      connection.commit();
      return getImpl(connection, key);
    } catch (SQLException e) {
      JdbcUtil.rollback(connection);
      JdbcUtil.close(connection);
      throw new DataAccessException(e);
    } finally {
      JdbcUtil.endTransaction(connection);
      JdbcUtil.close(statement);
    }
  }
 private void createTable(String sql, Connection connection) {
   PreparedStatement statement = null;
   try {
     statement = connection.prepareStatement(sql);
     statement.executeUpdate();
   } catch (SQLException e) {
     throw new DataAccessException(e);
   } finally {
     close(statement);
   }
 }
 @Override
 public int size() {
   Connection connection = getConnection();
   PreparedStatement statement = null;
   ResultSet rs = null;
   try {
     statement = connection.prepareStatement("select count(*) from response");
     rs = statement.executeQuery();
     if (rs.next()) {
       return rs.getInt(1);
     }
   } catch (SQLException e) {
     throw new DataAccessException("Unable to query for count", e);
   } finally {
     JdbcUtil.close(rs);
     JdbcUtil.close(statement);
     JdbcUtil.close(connection);
   }
   return 0;
 }
 @Override
 public CacheItem get(HTTPRequest request) {
   Connection connection = getConnection();
   PreparedStatement statement = null;
   try {
     statement = connection.prepareStatement("select * from response where uri = ?");
     statement.setString(1, request.getRequestURI().toString());
     ResultSet rs = statement.executeQuery();
     while (rs.next()) {
       CacheItemHolder holder = mapper.mapRow(rs, connection);
       if (holder.getVary().matches(request)) {
         return holder.getCacheItem();
       }
     }
   } catch (SQLException e) {
     throw new DataAccessException(e);
   } finally {
     JdbcUtil.close(statement);
   }
   return null;
 }
 @Override
 public Iterator<Key> iterator() {
   Connection connection = getConnection();
   PreparedStatement statement = null;
   ResultSet rs = null;
   List<Key> keys = new ArrayList<Key>();
   try {
     statement = connection.prepareStatement("select uri,vary from response");
     rs = statement.executeQuery();
     while (rs.next()) {
       String uri = rs.getString(1);
       String vary = rs.getString(2);
       keys.add(Key.create(URI.create(uri), mapper.convertToVary(vary)));
     }
   } catch (SQLException ignore) {
   } finally {
     JdbcUtil.close(rs);
     JdbcUtil.close(statement);
     JdbcUtil.close(connection);
   }
   return ImmutableList.copyOf(keys).iterator();
 }
 private void invalidate(final Key key, final Connection connection) {
   PreparedStatement statement = null;
   try {
     statement = connection.prepareStatement("delete from response where uri = ? and vary = ?");
     statement.setString(1, key.getURI().toString());
     statement.setString(2, key.getVary().toJSON());
     statement.executeUpdate();
   } catch (SQLException e) {
     throw new DataAccessException(e);
   } finally {
     JdbcUtil.close(statement);
   }
 }
 private HTTPResponse getImpl(Connection connection, final Key key) {
   PreparedStatement statement = null;
   try {
     statement = connection.prepareStatement("select * from response where uri = ? and vary = ?");
     statement.setString(1, key.getURI().toString());
     statement.setString(2, key.getVary().toJSON());
     ResultSet rs = statement.executeQuery();
     if (rs.next()) {
       CacheItemHolder holder = mapper.mapRow(rs, connection);
       return holder.getCacheItem().getResponse();
     }
   } catch (SQLException e) {
     throw new DataAccessException(e);
   } finally {
     JdbcUtil.close(statement);
   }
   return null;
 }