使用Java将SQL查询结果输出到平面文件

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 Query的结果输出到文件(csv,tab等)。甚至在Hibernate中也许?

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!

我意识到一个选项是迭代结果集并一次输出一行与分隔符分隔的记录。但如果有内置方式或Hibernate方式 - 那会更好!

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 Server可以访问的目录中的平面文件 - 这也可以。我实际上不必在Java Server中编写文件 - 只是根据它的查询触发文件创建。

Hopefully that makes sense.

希望这是有道理的。

4 个解决方案

#1

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.

您可以将Session的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.

然后,您可以轻松地将其写入文件系统或进行后续转换。

#2

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,它是一个命令行客户端

#3

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!

我意识到一个选项是迭代结果集并一次输出一行与分隔符分隔的记录。但如果有内置方式或Hibernate方式 - 那会更好!

No, there is not. But it is very easy:

不,那里没有。但这很容易:

  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 Server可以访问的目录中的平面文件 - 这也会起作用

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,所以我不知道如何做到这一点。

#4

also change to

也改为

for( int i = 1; i < cols ; i++ ) { 
         out.printf("%s,", rs.getObject( i ) );
      }

#1