javasqljdbcdb2resultset

How do you get values from all columns using ResultSet.getBinaryStream() in jdbc?


How do I to write an entire table to a flat file (text file) using jdbc? So far I've attempted the following:

Statement statement = connection.createStatement();
   ResultSet result = statement.executeQuery("SELECT * FROM tablename");
   BufferedInputStream buffer;
   FileOutputStream out = new FileOutputStream("flatfile.txt");
   while(result.next() )
   {
      buffer =  new BufferedInputStream(result.getBinaryStream("????") );
      byte[] buf = new byte[4 * 1024]; //4K buffer
      int len;
      while( (len = buffer.read(buf, 0, buf.length) ) != -1 )
      {
          out.write(buf, 0, len );
      }
   }
   out.close();

"????" is just my placeholder. I am stuck on what to pass in as an argument.


Solution

  • You can get all the column names and the entire data from your table using the code below. writeToFile method will contain the logic to writing to file (if that was not obvious enough :) )

        ResultSetMetaData metadata = rs.getMetaData();
        int columnCount = metadata.getColumnCount();    
        for (int i = 1; i <= columnCount; i++) {
            writeToFile(metadata.getColumnName(i) + ", ");      
        }
        System.out.println();
        while (rs.next()) {
            String row = "";
            for (int i = 1; i <= columnCount; i++) {
                row += rs.getString(i) + ", ";          
            }
            System.out.println();
            writeToFile(row);
    
        }