Accessing data from servlet

Question

Hi,

I have got a requirement that mysql database can only be accessed from localhost. I have to implement a servlet that would access the database allowing other servers in this system to access data (servlet would work as a proxy). However, this system consists of a remote server which downloads large portions of data executing a statement like:

select * from database limit 100;

Can somebody suggest me how to write a servlet that would stream such data in a efficient way (I am new to databases)?

Answer 1

Well, if your goal is to completely open up the sql-server for queries by external hosts, but for some reason don't want to reconfigure it to accept external connections, I would suggest that you simply set up a tunnel for the port which the server listens on.

The remote host would connect to your application (running on localhost), which in turn simply connects to the sql-server and relays the stream of data back and forth.

Answer 2

A JDBC proxy would give you what you are looking for out of the box, such as Virtual JDBC.

Answer 3

First of all, I don't recommend to use a servlet for this. See the answers of aioobe and mdma for the right approach. But if there is really no other option, then continue reading:

---

Just write the data to the response immediately as the data comes in. Don't store everything in Java's memory. So basically: writer.write(resultSet.getString("col")). Further, the MySQL JDBC driver will by default cache everything in Java's memory before giving anything to ResultSet#next(). You'd like to let it give the data immediately row-by-row by setting the Statement#setFetchSize() as per the MySQL JDBC driver documentation.

Here's a kickoff example, assuming you'd like to output the data in CSV format:

protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    response.setContentType("text/csv");

    Connection connection = null;
    Statement statement = null;
    ResultSet resultSet = null;
    PrintWriter writer = response.getWriter();

    try {
        connection = database.getConnection();
        statement = connection.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        statement.setFetchSize(Integer.MIN_VALUE);
        resultSet = statement.executeQuery("SELECT col1, col2, col3 FROM tbl");

        while (resultSet.next()) {
            writer.append(resultSet.getString("col1")).append(',');
            writer.append(resultSet.getString("col2")).append(',');
            writer.append(resultSet.getString("col3")).println();
            // PS: don't forget to sanitize quotes/commas as per RFC4130.
        }
    } catch (SQLException e) {
        throw new ServletException("Query failed!", e);
    } finally { 
        if (resultSet != null) try { resultSet.close; } catch (SQLException logOrIgnore) {}
        if (statement != null) try { statement.close; } catch (SQLException logOrIgnore) {}
        if (connection != null) try { connection.close; } catch (SQLException logOrIgnore) {}
    }
}