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.

@MitchWheat - I want to get the number of columns in a result set and then print them out. The api has no method like getNumberOfColumns(); — sky scraper, Mar 16 '13 at 02:53
@skyscraper - Please kindly to answer the question by providing your preferred solution here, to let the other knows.. ;) — Wayan Wiprayoga, Mar 18 '13 at 14:35

score 20 · Answer 1 · edited May 23 '17 at 12:09

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

score 18 · Accepted Answer · edited May 23 '17 at 11:46

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.

Wayan Wiprayoga · Answer 3 · 2013-03-16T02:49:50.103

0

It's because your code only get 2 value of the row. Notice that rs.getString(4) meant, get the value on current row at 4th column (using 0 based index) as String.

If you want to print all the column, you should write the rest rs.getXXXX(), where XXXX is column data type such as getString(), getInteger(), getLong(), etc. See this java documentation for reference.

edited Mar 16 '13 at 02:49

answered Mar 16 '13 at 02:44

Wayan Wiprayoga

4,472
4
20
30

If you want to get each row value, you should iterate `rs` variable. If you want to get row size, please check [this post](http://stackoverflow.com/questions/192078/how-do-i-get-the-size-of-a-java-sql-resultset) – Wayan Wiprayoga Mar 16 '13 at 03:10

score -1 · Answer 4 · answered Nov 23 '21 at 08:55

public static void printSqlTable(String selectQuery) {
      try {
           Statement statement = connection.createStatement();
           resultSet = statement.executeQuery(selectQuery);
           DBTablePrinter.printResultSet(resultSet);
              } catch (SQLException e) {
                  e.printStackTrace();
              } catch (Exception e) {
                  e.printStackTrace();
    }
}

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

4 Answers4

Linked