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.

Do i just replace '????' with "" to obtain all columns or is there another function within ResultSet or some other class to obtain a whole row? I just want the entire row. Not just one column of that record. I don't have db connectivity to test yet so I'm just trying to figure it out in advance so I'm ready. — Harish Vangavolu, Jul 24 '14 at 21:06
`ResultSet` doesn't provide methods that deal with the entire row. You need to deal with columns individually. You might find the [getMetaData()](http://docs.oracle.com/javase/8/docs/api/java/sql/ResultSet.html#getMetaData--) method useful. — GriffeyDog, Jul 24 '14 at 21:09
Plus `getBinaryStream()` only works for `BLOB` columns - nothing else. — a_horse_with_no_name, Jul 24 '14 at 21:13
Ok that's what I thought and java.sql.Statement doesn't seem to have anything other than executeQuery() so is there some other class that would help with this? — Harish Vangavolu, Jul 24 '14 at 21:15
don't know exactly what you want to do... you could just try the DBMS native dump command (all decent DBMSes have one), which is probably easier and faster to use. — Leo, Jul 24 '14 at 23:10

score 40 · Accepted Answer · answered Jul 25 '14 at 01:57

40

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

    }

answered Jul 25 '14 at 01:57

Adarsh

3,413
1
17
35

2

ah, too bad there is no ResultSet.getRow method! – gokul_uf Sep 16 '16 at 22:52
@gokul_uf, To get particular row we have to move cursor to respective row in ResultSet by using absolute(rowNum) then deal with columns within the row. ResultSet.getRow will itself gets confused which row to be returned ?? – Siddappa Walake Jun 18 '18 at 10:32

rogerdpack · Answer 2 · 2020-10-21T18:33:40.893

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 COLUMN1=VARCHAR COLUMN2=VARDHAR
 ID=1 COLUMN1=abc COLUMN2=null
 ID=2 COLUMN1=def COLUMN2=ghi
 ...

score 3 · Answer 3 · answered Jul 25 '14 at 00:25

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

answered Jul 25 '14 at 00:25

sendon1982

7,088
42
36

Thank you sendon1982, I guess I can use this to loop through each column in the table using getBinaryCount() and just pass in the column index to print out the whole table. I was juts wondering if there was a way to spit out a whole table with a single function that's all, but this will do. – Harish Vangavolu Jul 25 '14 at 01:02
You are welcome. No, you have to use loop to show them one by one. – sendon1982 Jul 25 '14 at 04:23

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

3 Answers3

Linked