使用 Java 将 SQL 查询结果输出到平面文件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/364472/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Output SQL query results to flat-file with Java
提问by arti
Is there an easy or straightforward way in Java to output the results of a DB Query to a file (either csv, tab, etc). Perhaps even in Hibernate?
Java 中是否有一种简单或直接的方法可以将 DB 查询的结果输出到文件（csv、tab 等）。也许甚至在休眠中？
I know that a query results can be dumped to a flat file on the DB Server. I am looking for a way that an application can run a query and get those results into a file.
我知道查询结果可以转储到数据库服务器上的平面文件中。我正在寻找一种应用程序可以运行查询并将这些结果放入文件的方法。
I realize one option is to just iterate over the result set and output the records one row at a time separating with a delimiter. But if there is a built in way, or a Hibernate way - that would be much better!
我意识到一种选择是迭代结果集并一次输出一行记录，用分隔符分隔。但是，如果有内置方式或休眠方式 - 那会好得多！
What I am really looking for was a way to do this in code (like - when an application is running). That way a server could take a query, run it against the database, send the output to a flat-file.
我真正在寻找的是一种在代码中执行此操作的方法（例如 - 当应用程序运行时）。这样服务器就可以进行查询，对数据库运行它，将输出发送到平面文件。
The server sending the query doesn't really need the file itself, just to know it worked. So if there is SQL (for an Oracle DB) that could redirect the output to a flat-file in a directory that the Oracle DB Server has access to - that would work too. I don't really have to actually write the file in the Java Server - just trigger the file creation based on the query it has.
发送查询的服务器并不真正需要文件本身，只是为了知道它有效。因此，如果有 SQL（对于 Oracle DB）可以将输出重定向到 Oracle DB 服务器有权访问的目录中的平面文件 - 这也可以。我真的不必在 Java Server 中实际编写文件 - 只需根据它的查询触发文件创建。
Hopefully that makes sense.
希望这是有道理的。
回答by cliff.meyers
You can change the EntityMode of your Session to "DOM4J" so that Hibernate will return the data represented as an XML document instead of a POJO graph.
您可以将会话的 EntityMode 更改为“DOM4J”，以便 Hibernate 将返回表示为 XML 文档而不是 POJO 图的数据。
xmlSession = session.getSession(EntityMode.DOM4J);
Element elem = (Element) xmlSession.load(SomePersistentClass.class, id);
System.out.println(elem.asXML());
You could then easily write this to the file system or do subsequent transformations.
然后，您可以轻松地将其写入文件系统或进行后续转换。
回答by OscarRyz
I realize one option is to just iterate over the result set and output the records one row at a time separating with a delimiter. But if there is a built in way, or a Hibernate way - that would be much better!
我意识到一种选择是迭代结果集并一次输出一行记录，用分隔符分隔。但是，如果有内置方式或休眠方式 - 那会好得多！
No, there is not. But it is veryeasy:
不，那里没有。但这很容易：
public void printToFile( ResultSet rs, String path ) throws IOException {
PrintStream out = new FileOutputStream( path );
int cols = rs.getMetaData().getColumnCount();
while( rs.next() ) {
for( int i = 0; i < cols ; i++ ) {
out.printf("%s,", rs.getObject( i ) );
}
out.println();
}
// add exception handling and such...
// or move the from here.
out.close();
rs.close();
}
And then call it like this:
然后像这样调用它：
ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM TABLE2");
printToFile( rs, "/some/file" );
This is just from scratch, you'll need to move the closing of the file and rs to something that makes more sense.
这只是从头开始，您需要将文件和 rs 的关闭移动到更有意义的位置。
So if there is SQL (for an Oracle DB) that could redirect the output to a flat-file in a directory that the Oracle DB Server has access to - that would work too
因此，如果有 SQL（对于 Oracle DB）可以将输出重定向到 Oracle DB 服务器有权访问的目录中的平面文件 - 这也可以
I think this is possible indeed. But I'm years light away from being a DBA so I don't know "how" to do it.
我认为这确实是可能的。但是我离成为 DBA 还差几年，所以我不知道“如何”去做。
回答by arti
also change to
也改为
for( int i = 1; i < cols ; i++ ) {
out.printf("%s,", rs.getObject( i ) );
}
回答by sblundy
I seem to remember IntelliJ's JDBC db explorer having the ability to export the results of queries. I wouldn't be surprised if an Eclipse or Netbeans DB plugin or can do the same. Here's a whole bunch of open source clients.
我似乎记得 IntelliJ 的 JDBC db explorer 能够导出查询结果。如果 Eclipse 或 Netbeans DB 插件或可以做同样的事情，我不会感到惊讶。这里有一大堆开源客户端。
There's also JdbcTool, which is a command line client
还有JdbcTool，它是一个命令行客户端
