How to print column name and data from DB, using java

Question

Can anyone suggest hot to print column name and value in output in console:

I have below code which only prints the value of the column, but i need to print value with correspondence column name as well:

import java.io.*;  
import java.sql.*;  

public class RetrieveFile {  
    public static void main(String args[]) throws Exception {
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver");
            Connection con = DriverManager.getConnection("jdbc:oracle:thin:@123.32.23.123:8080/orcl", "test1", "******");

            PreparedStatement ps = con.prepareStatement("select * from MSG where MID='1234'");
            ResultSet rs = ps.executeQuery();

            try {
                printResultColumns(rs);
            } catch (SQLException e) {
                System.err.println(e.getMessage());
            }

            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static void printResultColumns(ResultSet resultSet) throws SQLException, IOException {
    ResultSetMetaData rsmd = resultSet.getMetaData();
    int columnCount = rsmd.getColumnCount();

    while (resultSet.next()) {
        // you get a single result row in here, not the entire ResultSet
        for (int i = 1; i <= columnCount; i++) {
            switch (rsmd.getColumnType(i)) {
            case Types.VARCHAR:
            case Types.LONGVARCHAR:
            case Types.CHAR:
                System.out.println(resultSet.getString(i));
                break;
            case Types.DOUBLE:
                System.out.println(resultSet.getDouble(i));
                break;
            case Types.INTEGER:
                System.out.println(resultSet.getInt(i));
                break;
            case Types.DATE:
                System.out.println(resultSet.getDate(i).toString());
                break;
            case Types.TIMESTAMP:
                System.out.println(resultSet.getTimestamp(i).toString());
                break;
            case Types.BOOLEAN:
                System.out.println(resultSet.getBoolean(i));
                break;
            case Types.DECIMAL:
            case Types.NUMERIC:
                System.out.println(resultSet.getBigDecimal(i));
                break;
            default:
                //System.out.println(rsmd.getColumnClassName(i)



            }
        }
    }


}
    }

Current Output:- null 1961108001406E00 389 OUR NOW USD

Expected Output:- Col1-null Col2-1961108001406E00 Col3-389 Col4-OUR Col5-NOW Col6-USD

OR Expected Out put:-
Col1  Col2  Col3  Col4  Col5  Col6
-----------------------------------
null  1961   389  our   now    usd

Possible duplicate of [Retrieve column names from java.sql.ResultSet](https://stackoverflow.com/questions/696782/retrieve-column-names-from-java-sql-resultset) — Amongalen, Jul 25 '19 at 12:34

deHaar · Accepted Answer · 2019-07-25T17:44:29.700

2

You can get it by the column name stored in the ResultSetMetaData:

ResultSetMetaData rsmd = resultSet.getMetaData();
int columnCount = rsmd.getColumnCount();

while (resultSet.next()) {
    // you get a single result row in here, not the entire ResultSet
    for (int i = 1; i <= columnCount; i++) {
        int type = rsmd.getColumnType(i);
        String typeName = rsmd.getColumnTypeName(i);
        String name = rsmd.getColumnName(i);
        String value;
        switch (type) {
        case Types.VARCHAR:
            value = resultSet.getString(i) == null ? "null" : resultSet.getString(i);
            System.out.println(name + ": " + value);
            break;
        case Types.DOUBLE:
            value = resultSet.getString(i) == null ? "null" : String.valueOf(resultSet.getDouble(i));
            System.out.println(name + " [" + typeName + "]: " + value);
            break;
        case Types.INTEGER:
            value = resultSet.getString(i) == null ? "null" : String.valueOf(resultSet.getInt(i));
            System.out.println(name + " [" + typeName + "]: " + value);
            break;
        case Types.DATE:
            value = resultSet.getString(i) == null ? "null" : String.valueOf(resultSet.getDate(i).toString());
            System.out.println(name + " [" + typeName + "]: " + value);
            break;
        case Types.TIMESTAMP:
            value = resultSet.getString(i) == null ? "null" : resultSet.getTimestamp(i).toString();
            System.out.println(name + " [" + typeName + "]: " + value);
            break;
        case Types.BOOLEAN:
            value = resultSet.getString(i) == null ? "null" : (resultSet.getBoolean(i) ? "true" : "false");
            System.out.println(name + " [" + typeName + "]: " + value);
            break;
        case Types.DECIMAL:
        case Types.NUMERIC:
            value = resultSet.getString(i) == null ? "null" : resultSet.getBigDecimal(i).toString();
            System.out.println(name + " [" + typeName + "]: " + value);
            break;
        default:
            System.out.println("The column type (" + rsmd.getColumnTypeName(i)
                    + " for column " + rsmd.getColumnName(i) + ", Label: " + rsmd.getColumnLabel(i)
                    + ") is currently not supported in method \"printResultColumns\".\nAdd it as case there.");
        }

    }
}

If you have given the column some aliases, maybe use getColumnLabel instead (see default, it is used there).

For the case you want to output something like your table, iterate the meta data for the column names and print them once before printing the values of the result rows.

edited Jul 25 '19 at 17:44

answered Jul 25 '19 at 12:38

deHaar

17,687
10
38
51

you beauty, but one issue its failing were there is longchar data type. – Routray Jul 25 '19 at 13:01
@Routray Does it throw an `Exception` or does it print weird things? What's the *fail* exactly? – deHaar Jul 25 '19 at 13:03
i have added case as "case Types.LONGVARCHAR: System.out.println(rsmd.getColumnName(i) + ": " + resultSet.getLong(i));" but its printing as datatype+column name+column name – Routray Jul 25 '19 at 13:06
@Routray If you don't have given the columns aliases in SQL, `getColumnName` and `getColumnLabel` are likely to return the same `String`, so just use one of them or give each column an alias (which is not possible via `SELECT * FROM ...`, it should then be `SELECT id AS identification, someColumn AS SomeValue`. Then you will get different return values from those methods. – deHaar Jul 25 '19 at 13:13
@Routray Btw, `LONGVARCHAR` maps to `String`, so you cannot do `resultSet.getLong(i)` on it! – deHaar Jul 25 '19 at 13:37
thanks for the response, after specifying alias in column name it's works fine...but there is a column its repeating two times, any clue why it's behaving like that as FEE: 389 FEE: 389 – Routray Jul 25 '19 at 13:57
@Routray sorry, not really... What type does that column have? – deHaar Jul 25 '19 at 14:04
now it's works fine, issue was i have missed a break in between. Thanks – Routray Jul 25 '19 at 14:38
sure, how to handle NULL value, if the column contains NULL value i want print that as well. – Routray Jul 25 '19 at 15:27
@Routray You have to check for `null` and I would use a String variable to store the result or `"null"`. Then use that variable for printing. – deHaar Jul 25 '19 at 15:33
i think we can't used duplicate case, although we are saving different value, can you please pass some code. Sorry for asking too many... – Routray Jul 25 '19 at 15:41
@Routray see last edit, just updated code and nothing else. – deHaar Jul 25 '19 at 17:44
i have observed a issue if there is a column type longchar, there its not printing the column value, its print only the column heading. Any solution for this?Thanks – Routray Jul 30 '19 at 09:33
@Routray sorry, never heard of a type `LONGCHAR`, but `LONGVARCHAR`. Are you sure it is `LONGCHAR`? It doesn't seem to make much sense, because a `CHAR` is a single character. What would be the use of a `LONGCHAR` (long char) then? – deHaar Jul 30 '19 at 09:35
extremely sorry, its LONGVARCHAR. – Routray Jul 30 '19 at 09:37
@Routray I don't really know, many documents say it is treated as `String` like `VARCHAR`. You can add a `case LONGVARCHAR:` above the `case VARCHAR:` as you have done. Maybe a special thing about the database engine. – deHaar Jul 30 '19 at 09:38
thanks for the response. I have added this into the code"case Types.LONGVARCHAR:" but still its not retrieving the value of the code. its printing like CHARMIDMID(2 time column name with datatype prifix) – Routray Jul 30 '19 at 09:46
@Routray What a value is in that column? You can have a look at [how mapping types is done in JDBC](https://www.cis.upenn.edu/~bcpierce/courses/629/jdkdocs/guide/jdbc/getstart/mapping.doc.html). You may find that `LONGVARCHAR` *can* be retrieved as `String`, but does not have a recommended type. – deHaar Jul 30 '19 at 09:50
1

thanks for the input. Now this issue solved, as i was missing CHAR data type in CASE. Thanks – Routray Jul 30 '19 at 09:53

score 1 · Answer 2 · answered Jul 25 '19 at 12:34

1

You can use metadata of result set.

ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM TABLE2");
 ResultSetMetaData rsmd = rs.getMetaData();
 String name = rsmd.getColumnName(1);

answered Jul 25 '19 at 12:34

parthivrshah

634
2
7
14

thanks for the response, if you see my implementation, i have also used metadata, but only values are getting print not column name. – Routray Jul 25 '19 at 12:45
1

You have not use getColumnName() method – parthivrshah Jul 25 '19 at 12:47

score 0 · Answer 3 · answered Jul 26 '19 at 08:23

Issue Resolved:-

ResultSetMetaData rsmd = resultSet.getMetaData();
int columnCount = rsmd.getColumnCount();

while (resultSet.next()) {
    // you get a single result row in here, not the entire ResultSet
    for (int i = 1; i <= columnCount; i++) {
        int type = rsmd.getColumnType(i);
        String typeName = rsmd.getColumnTypeName(i);
        String name = rsmd.getColumnName(i);
        String value;
        switch (type) {
        case Types.VARCHAR:
            value = resultSet.getString(i) == null ? "null" : resultSet.getString(i);
            System.out.println(name + ": " + value);
            break;
        case Types.DOUBLE:
            value = resultSet.getString(i) == null ? "null" : String.valueOf(resultSet.getDouble(i));
            System.out.println(name + " [" + typeName + "]: " + value);
            break;
        case Types.INTEGER:
            value = resultSet.getString(i) == null ? "null" : String.valueOf(resultSet.getInt(i));
            System.out.println(name + " [" + typeName + "]: " + value);
            break;
        case Types.DATE:
            value = resultSet.getString(i) == null ? "null" : String.valueOf(resultSet.getDate(i).toString());
            System.out.println(name + " [" + typeName + "]: " + value);
            break;
        case Types.TIMESTAMP:
            value = resultSet.getString(i) == null ? "null" : resultSet.getTimestamp(i).toString();
            System.out.println(name + " [" + typeName + "]: " + value);
            break;
        case Types.BOOLEAN:
            value = resultSet.getString(i) == null ? "null" : (resultSet.getBoolean(i) ? "true" : "false");
            System.out.println(name + " [" + typeName + "]: " + value);
            break;
        case Types.DECIMAL:
        case Types.NUMERIC:
            value = resultSet.getString(i) == null ? "null" : resultSet.getBigDecimal(i).toString();
            System.out.println(name + " [" + typeName + "]: " + value);
            break;
        default:
            System.out.println("The column type (" + rsmd.getColumnTypeName(i)
                    + " for column " + rsmd.getColumnName(i) + ", Label: " + rsmd.getColumnLabel(i)
                    + ") is currently not supported in method \"printResultColumns\".\nAdd it as case there.");
        }

    }
}

How to print column name and data from DB, using java

3 Answers3