17622

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 -

<a href="http://msdn.microsoft.com/en-us/library/aa342339.aspx" rel="nofollow">http://msdn.microsoft.com/en-us/library/aa342339.aspx</a>

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.

Answer1:

Use this code

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

Source - <a href="https://stackoverflow.com/questions/2614416/how-to-get-the-number-of-columns-from-a-jdbc-resultset" rel="nofollow">How to get the number of columns from a JDBC ResultSet?</a>

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.

Answer2:

I posted this answer to a similar question <a href="https://stackoverflow.com/questions/5475896/system-out-println-from-database-into-a-table/26735511#26735511" rel="nofollow">here</a>, 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: <a href="https://github.com/htorun/dbtableprinter" rel="nofollow">https://github.com/htorun/dbtableprinter</a>

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

Answer3:

It's because your code only get 2 value of the row. Notice that rs.getString(4) meant, get the value on <strong>current row</strong> at <strong>4th column</strong> (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 <a href="http://docs.oracle.com/javase/6/docs/api/java/sql/ResultSet.html" rel="nofollow">documentation</a> for reference.

Recommend

  • Looping Columns in coldfusion along with values in correct order
  • Adding JCheckBox into JTable
  • CellDataFeatures.getValue.get() no longer supported?
  • Unreported exception SQLException; must be caught or declared to be thrown error
  • Android/Java JDBC MySQL - fetch rows - effective algorithm
  • Refreshing JTable when data has changed
  • JTable#repaint() not functioning as expected [closed]
  • Java SQL Resultset retrieval order is not correct
  • Multiple editable rows in JTable header
  • How to display or print the contents of a database table as is?
  • retrieving data from mysql and putting it on JTables
  • BreezeJs loading metadata 5 times on the page, trying to use fetchMetaData but it errors
  • ServiceContractGenerator CodeDomProvider Compile Errors
  • SQL response type
  • How do I prevent a Java Swing JTable column from being sorted when the user click on the header?
  • Accessing Clementine instance via D-Bus MPRIS in Java
  • Mixing Android widget old & new themes
  • database dump to text file with side-effects for “rows completed”
  • HQL joins in Grails
  • SpringBoot Couchbase Integration
  • Round bracket in string with JDBC prepared statement
  • In Jaspersoft Studio, Sql server procedure returning fewer fields than expected
  • SQL Error : integrity constraint violation : foreign key no parent
  • How to recognize PDF watermark and remove it using PDFBox
  • Cannot create an instance of an interface (PagedList)
  • Using Select and where statement in Criteria
  • How do I get name of the target table and column of foreign key column with plain JDBC
  • Locating unmatched delimiters in Clojurescript
  • ActiveRecord query for a count of new users by day
  • Can you perform a UNION without a subquery in SQLAlchemy?
  • PostgreSQL Query without WHERE only ORDER BY and LIMIT doesn't use index
  • Nant, Vault & Windows Integrated Authentication
  • Counter field in MS Access, how to generate?
  • Javascript + PHP Encryption with pidCrypt
  • Display Images one by one with next and previous functionality
  • Websockets service method fails during R startup
  • SQL merge duplicate rows and join values that are different
  • Why can't I rebase on to an ancestor of source changesets if on a different branch?