How to display or print the contents of a database table as is?

Question

I want to fetch a table from a database using Java code. The sample code which I tried gets only two columns. I want the fetched data to be presented exactly like it is in the table. How do I do that ?

This code only gives me two rows, side by side -

while (rs.next()) {
System.out.println(rs.getString(4) + " " + rs.getString(6));

}

Full example at -

http://msdn.microsoft.com/en-us/library/aa342339.aspx

This is what I tried - 

         int size = 0;
         if(rs != null){

            rs.beforeFirst();  
            rs.last();  
            size = rs.getRow();  

         }

         System.out.println("cols = " + size);

And got an error - The requested operation is not supported on forward only result sets.

Answer 1

Use this code

Statement st = conn.createStatement();
ResultSet rs = st.executeQuery(query);
ResultSetMetaData rsmd = rs.getMetaData();

int columnsNumber = rsmd.getColumnCount();

Source - How to get the number of columns from a JDBC ResultSet?

After using that code, one can display the results like they are displayed by the DBMS as follows -

ResultSetMetaData rsmd = rs.getMetaData();
int columnsNumber = rsmd.getColumnCount();                     

// Iterate through the data in the result set and display it. 

while (rs.next()) {
//Print one row          
for(int i = 1 ; i <= columnsNumber; i++){

      System.out.print(rs.getString(i) + " "); //Print one element of a row

}

  System.out.println();//Move to the next line to print the next row.           

    }

Column names are not displayed in this example.

Answer 2

I posted this answer to a similar question here, but I believe this one is also relevant, maybe more so. In short, I wrote a simple utility class to print db table rows to standard out (for part fun, part learning). It may be useful to someone (at least I hope so).

Here is the link to the code repo at GitHub: https://github.com/htorun/dbtableprinter

And here is the basic usage:

// Create a connection to the database
Connection conn = DriverManager.getConnection(url, username, password);

// Just pass the connection and the table name to printTable()
DBTablePrinter.printTable(conn, "employees");

It should print something like this:

Printing 10 rows from table(s) EMPLOYEES
+--------+------------+------------+-----------+--------+-------------+
| EMP_NO | BIRTH_DATE | FIRST_NAME | LAST_NAME | GENDER |  HIRE_DATE  |
+--------+------------+------------+-----------+--------+-------------+
|  10001 | 1953-09-02 | Georgi     | Facello   | M      |  1986-06-26 |
+--------+------------+------------+-----------+--------+-------------+
|  10002 | 1964-06-02 | Bezalel    | Simmel    | F      |  1985-11-21 |
+--------+------------+------------+-----------+--------+-------------+
    .
    .