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

Question

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 1

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);

    }

Solution 2

Here's how I dump a table from a JDBC connection, very useful for debugging if you want to see all rows that are in an in memory (ex: HSQL) DB for instance:

  public static void spitOutAllTableRows(String tableName, Connection conn) {
    try {
      System.out.println("current " + tableName + " is:");
      try (PreparedStatement selectStmt = conn.prepareStatement(
              "SELECT * from " + tableName, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
           ResultSet rs = selectStmt.executeQuery()) {
        if (!rs.isBeforeFirst()) {
          System.out.println("no rows found");
        }
        else {
          System.out.println("types:");
          for (int i = 0; i < rs.getMetaData().getColumnCount(); i++) {
            System.out.print(rs.getMetaData().getColumnName(i + 1) + ":" + rs.getMetaData().getColumnTypeName(i + 1) + " ");
          }
          System.out.println();
          while (rs.next()) {
            for (int i = 1; i < rs.getMetaData().getColumnCount() + 1; i++) {
              System.out.print(" " + rs.getMetaData().getColumnName(i) + "=" + rs.getObject(i));
            }
            System.out.println("");
          }
        }
      }
    }
    catch (SQLException e) {
      throw new RuntimeException(e);
    }
  }

output is like

 current <yourtablename> is:
 types:ID:INT YOURCOLUMN1:VARCHAR YOURCOLUMN2:VARCHAR
 ID=1 YOURCOLUMN1=abc YOURCOLUMN2=null
 ID=2 YOURCOLUMN1=def YOURCOLUMN2=ghi
 ...

Solution 3

result.getBinaryStream("????") will only return for the value for that column as you put as placeholder.

If you want to get all the column, you need to use ResultSetMetaData from ResultSet

    ResultSetMetaData metadata = resultSet.getMetaData();
    int columnCount = metadata.getColumnCount();
    for (int i=1; i<=columnCount; i++) 
    {
        String columnName = metadata.getColumnName(i);
        System.out.println(columnName);
    }