从servlet访问数据 [英] Accessing data from servlet
问题描述
我要求只能从localhost访问mysql数据库。我必须实现一个访问数据库的servlet,允许该系统中的其他服务器访问数据(servlet将作为代理工作)。但是,该系统由一个远程服务器组成,该服务器下载执行语句的大部分数据,如:
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;
有人可以建议我如何编写一个能够有效地传输这些数据的servlet(我是新的数据库)?
Can somebody suggest me how to write a servlet that would stream such data in a efficient way (I am new to databases)?
推荐答案
首先,我不建议为此使用servlet。请参阅aioobe和mdma的答案以获得正确的方法。但如果真的没有其他选择,那么继续阅读:
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:
只需立即将数据写入响应 随着数据的进入。不要将所有内容存储在Java的内存中。所以基本上: writer.write(resultSet.getString(col))
。此外,默认情况下,MySQL JDBC驱动程序会在向 ResultSet#next()
提供任何内容之前缓存Java内存中的所有内容。你想让它通过设置 语句#setFetchSize()
根据 MySQL JDBC驱动程序文档。
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.
这是一个启动示例,假设您想以CSV格式输出数据:
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) {}
}
}
这篇关于从servlet访问数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!