I'm trying to retrieve all of the column names from a database table but only "[COLUMN_NAME]" is returned?

Question

For some reason only [COLUMN_NAME] is returned? It is connecting to a MySQL database running on a local host.

Here is my code:

public static String[] getCol() throws Exception {
    try {
        Connection con = dbc.getConnection();
        PreparedStatement statement = con.prepareStatement("select column_name from information_schema.columns where table_name='particles'");
        ResultSet result = statement.executeQuery();
        ResultSetMetaData rs =  result.getMetaData();
        int count = rs.getColumnCount();
        String[] ColName = new String[count];
        String[] COLS;
        for (int i=1 ; i <= count; i++) {
            ColName[i-1] = rs.getColumnName(i);
        }
        System.out.println(Arrays.toString(ColName));
        return ColName;
    } catch(Exception e) {
        System.out.println(e);
    }
    return null;
}

If I understood you correctly, the problem is in SQL. You select only `column_name`, use `select * ...` instead. — streetturtle, Oct 06 '16 at 18:42
duplicated http://stackoverflow.com/questions/19094999/java-how-to-get-column-name-on-result-set — staszek, Oct 06 '16 at 18:42

spencer7593 · Accepted Answer · 2016-10-06T19:51:19.913

Each of the values of column_name is going to be on a separate row. The expected return from your query will be a resultset like

column_name
-----------
id
name
weight
charge
flavor
spin

To get the actual values returned by the query, you will need to spin through the rows of the resultset, just like you would fetch rows from any other resultset you returned.

The code you are showing is only looking at the metadata of the resultsest... names assigned to the column(s) in the resultset. In this case, the resultset contains a single column, with the name column_name. That's why you are observing the behavior you report.

If your query was written

 SELECT column_name AS foo
   FROM information_schema.columns
  WHERE table_name='particles'

You'd be seeing foo as the column name, rather than column_name.

It doesn't matter one whit to SQL that the values in the rows of the resultset happen to be column names... as far as SQL is concerned, the query is returning a set of rows, a single column of values. Yes, it happens to be metadata about the columns in a table (because you are running a query against a special view named information_schema.columns) but the return from that query is really no different than if you ran any other query that return a VARCHAR column from any other table. For example:

SELECT mystringcol FROM mytable

If you used that query in your code, the metadata for the resultset would be a single column, with a name mystringcol. And your code wouldn't be doing anything with the rows returned in the resultset, the actual values of mystringcol.

I'm trying to retrieve all of the column names from a database table but only "[COLUMN_NAME]" is returned?

1 Answers1