Output SQL query results to flat-file with Java

Question

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?

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.

Hopefully that makes sense.

Answer 1

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.

There's also JdbcTool, which is a command line client

Answer 2

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.

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.

Answer 3

Here's a reference to a few different ways to do this. AskTom

Answer 4

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 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.

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 think this is possible indeed. But I'm years light away from being a DBA so I don't know "how" to do it.