CODEWITHSUNDEEP
javasqlsql-server-2008
sky scraper
sky scraper

Reputation: 2184

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

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.

Upvotes: 7

Views: 92078

Answers (4)

Eyal Sooliman
Eyal Sooliman

Reputation: 2248

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

Upvotes: -1

Hami Torun
Hami Torun

Reputation: 553

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

Upvotes: 21

sky scraper
sky scraper

Reputation: 2184

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.

Upvotes: 19

Wayan Wiprayoga
Wayan Wiprayoga

Reputation: 4562

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.

Upvotes: 0

Related Questions