Retrieve column names from java.sql.ResultSet

Question

With java.sql.ResultSet is there a way to get a column's name as a String by using the column's index? I had a look through the API doc but I can't find anything.

score 422 · Accepted Answer · edited Jun 16 '15 at 13:41

422

You can get this info from the ResultSet metadata. See ResultSetMetaData

e.g.

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

and you can get the column name from there. If you do

select x as y from table

then rsmd.getColumnLabel() will get you the retrieved label name too.

edited Jun 16 '15 at 13:41

Sergey Maksimenko

587
7
22

answered Mar 30 '09 at 11:13

Brian Agnew

268,207
37
334
440

26

See also `rsmd.getColumnLabel` if you retrieves columns with labels (for example `SELECT columnName AS ColumnLabel` – T30 Mar 28 '14 at 13:45
24

You may be surprised as me seeing column count starting at 1. You can iterate through column names with `for (int i = 1; i <= rsmd.getColumnCount(); i++) String name = rsmd.getColumnName(i);` – Alphaaa Nov 19 '15 at 07:03
Does `getColumnName()` return the original column name if not using `AS` alias naming? – membersound Mar 22 '18 at 16:02
3

@membersound Yes, as documented in [its Javadoc](https://docs.oracle.com/javase/10/docs/api/java/sql/ResultSetMetaData.html#getColumnLabel(int)): _"If a SQL `AS` is not specified, the value returned from `getColumnLabel` will be the same as the value returned by the `getColumnName` method."_. In almost all case you should use `getColumnLabel` instead of `getColumnName`. – Mark Rotteveel Aug 17 '18 at 07:15
2

This will fail if table is empty. – andronix Mar 13 '19 at 12:57
Why it's not directly in Result? – Alex78191 Apr 24 '19 at 11:47

score 162 · Answer 2 · edited Sep 17 '19 at 14:18

162

In addition to the above answers, if you're working with a dynamic query and you want the column names but do not know how many columns there are, you can use the ResultSetMetaData object to get the number of columns first and then cycle through them.

Amending Brian's code:

ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM TABLE2");
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();

// The column count starts from 1
for (int i = 1; i <= columnCount; i++ ) {
  String name = rsmd.getColumnName(i);
  // Do stuff with name
}

edited Sep 17 '19 at 14:18

Brian Agnew

268,207
37
334
440

answered Sep 29 '10 at 04:24

Cyntech

5,362
6
33
47

isn't this right? for (int i = 1; i <= columnCount + 1; i++ ) { ... } – Martin Ille Nov 02 '14 at 15:13
3

@Martin No, because that will attempt to get Column n + 1 that doesn't exist. If you want to be absolutely concise, then it'd be `i <= columnCount`. – Cyntech Nov 02 '14 at 23:09

simon · Answer 3 · 2015-06-18T10:22:26.487

20

You can use the the ResultSetMetaData (http://java.sun.com/javase/6/docs/api/java/sql/ResultSetMetaData.html) object for that, like this:

ResultSet rs = stmt.executeQuery("SELECT * FROM table");
ResultSetMetaData rsmd = rs.getMetaData();
String firstColumnName = rsmd.getColumnName(1);

edited Jun 18 '15 at 10:22

answered Mar 30 '09 at 11:15

simon

12,666
26
78
113

1

thanx it helped me... i used it as: resultSet.getString(resultSet.findColumn("fullname")) – C Sharper Sep 10 '13 at 05:51
Limit the records fetched to 1. Otherwise if the table is too big, unnecessary overhead then. Eg for teradatabase: use query "SELECT * FROM table SAMPLE 1" – josepainumkal Oct 10 '19 at 18:30

score 13 · Answer 4 · answered Dec 02 '13 at 05:04

This question is old and so are the correct previous answers. But what I was looking for when I found this topic was something like this solution. Hopefully it helps someone.

// Loading required libraries    
import java.util.*;
import java.sql.*;

public class MySQLExample {
  public void run(String sql) {
    // JDBC driver name and database URL
    String JDBC_DRIVER = "com.mysql.jdbc.Driver";
    String DB_URL = "jdbc:mysql://localhost/demo";

    // Database credentials
    String USER = "someuser"; // Fake of course.
    String PASS = "somepass"; // This too!

    Statement stmt = null;
    ResultSet rs = null;
    Connection conn = null;
    Vector<String> columnNames = new Vector<String>();

    try {
      // Register JDBC driver
      Class.forName(JDBC_DRIVER);

      // Open a connection
      conn = DriverManager.getConnection(DB_URL, USER, PASS);

      // Execute SQL query
      stmt = conn.createStatement();
      rs = stmt.executeQuery(sql);
      if (rs != null) {
        ResultSetMetaData columns = rs.getMetaData();
        int i = 0;
        while (i < columns.getColumnCount()) {
          i++;
          System.out.print(columns.getColumnName(i) + "\t");
          columnNames.add(columns.getColumnName(i));
        }
        System.out.print("\n");

        while (rs.next()) {
          for (i = 0; i < columnNames.size(); i++) {
            System.out.print(rs.getString(columnNames.get(i))
                + "\t");

          }
          System.out.print("\n");
        }

      }
    } catch (Exception e) {
      System.out.println("Exception: " + e.toString());
    }

    finally {
      try {
        if (rs != null) {
          rs.close();
        }
        if (stmt != null) {
          stmt.close();
        }
        if (conn != null) {
          conn.close();
        }
      } catch (Exception mysqlEx) {
        System.out.println(mysqlEx.toString());
      }

    }
  }
}

score 6 · Answer 5 · edited Sep 07 '17 at 14:22

6

SQLite 3

Using getMetaData();

DatabaseMetaData md = conn.getMetaData();
ResultSet rset = md.getColumns(null, null, "your_table_name", null);

System.out.println("your_table_name");
while (rset.next())
{
    System.out.println("\t" + rset.getString(4));
}

EDIT: This works with PostgreSQL as well

edited Sep 07 '17 at 14:22

Prem Kurian Philip

188
7

answered Mar 10 '17 at 04:30

SedJ601

12,173
3
41
59

Tried it on a teradata database and got the error "[Teradata Database] [TeraJDBC 16.20.00.02] [Error 9719] [SQLState HY000] QVCI feature is disabled." – josepainumkal Oct 10 '19 at 18:29

score 2 · Answer 6 · edited Aug 01 '17 at 09:48

import java.sql.*;

public class JdbcGetColumnNames {

    public static void main(String args[]) {
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;

        try {
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection(
                    "jdbc:mysql://localhost:3306/komal", "root", "root");

            st = con.createStatement();

            String sql = "select * from person";
            rs = st.executeQuery(sql);
            ResultSetMetaData metaData = rs.getMetaData();

            int rowCount = metaData.getColumnCount();

            System.out.println("Table Name : " + metaData.getTableName(2));
            System.out.println("Field  \tDataType");

            for (int i = 0; i < rowCount; i++) {
                System.out.print(metaData.getColumnName(i + 1) + "  \t");
                System.out.println(metaData.getColumnTypeName(i + 1));
            }
        } catch (Exception e) {
            System.out.println(e);
        }
    }
}

Table Name : person Field DataType id VARCHAR cname VARCHAR dob DATE

score 2 · Answer 7 · edited May 18 '19 at 08:34

2

while (rs.next()) {
   for (int j = 1; j < columncount; j++) {
       System.out.println( rsd.getColumnName(j) + "::" + rs.getString(j));      
   }
}

edited May 18 '19 at 08:34

vezunchik

3,669
3
16
25

answered May 18 '19 at 08:01

Jagadish Chenna

21
1

6

Please, can you extend your answer with more detailed explanation? This will be very useful for understanding. Thank you! – vezunchik May 18 '19 at 08:34

score 1 · Answer 8 · edited Dec 10 '19 at 15:01

The SQL statements that read data from a database query return the data in a result set. The SELECT statement is the standard way to select rows from a database and view them in a result set. The **java.sql.ResultSet** interface represents the result set of a database query.

Get methods: used to view the data in the columns of the current row being pointed to by the cursor.

Using MetaData of a result set to fetch the exact column count

ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM TABLE2");
ResultSetMetaData rsmd = rs.getMetaData();
int numberOfColumns = rsmd.getColumnCount();
boolean b = rsmd.isSearchable(1);

http://docs.oracle.com/javase/7/docs/api/java/sql/ResultSetMetaData.html

and further more to bind it to data model table

public static void main(String[] args) {
    Connection conn = null;
    Statement stmt = null;
    try {
        //STEP 2: Register JDBC driver
        Class.forName("com.mysql.jdbc.Driver");

        //STEP 3: Open a connection
        System.out.println("Connecting to a selected database...");
        conn = DriverManager.getConnection(DB_URL, USER, PASS);
        System.out.println("Connected database successfully...");

        //STEP 4: Execute a query
        System.out.println("Creating statement...");
        stmt = conn.createStatement();

        String sql = "SELECT id, first, last, age FROM Registration";
        ResultSet rs = stmt.executeQuery(sql);
        //STEP 5: Extract data from result set
        while(rs.next()){
            //Retrieve by column name
            int id  = rs.getInt("id");
            int age = rs.getInt("age");
            String first = rs.getString("first");
            String last = rs.getString("last");

            //Display values
            System.out.print("ID: " + id);
            System.out.print(", Age: " + age);
            System.out.print(", First: " + first);
            System.out.println(", Last: " + last);
        }
        rs.close();
    } catch(SQLException se) {
        //Handle errors for JDBC
        se.printStackTrace();
    } catch(Exception e) {
        //Handle errors for Class.forName
        e.printStackTrace();
    } finally {
        //finally block used to close resources
        try {
            if(stmt!=null)
                conn.close();
        } catch(SQLException se) {
        } // do nothing
        try {
            if(conn!=null)
                conn.close();
        } catch(SQLException se) {
            se.printStackTrace();
        } //end finally try
    }//end try
    System.out.println("Goodbye!");
}//end main
//end JDBCExample

very nice tutorial here : http://www.tutorialspoint.com/jdbc/

ResultSetMetaData meta = resultset.getMetaData();  // for a valid resultset object after executing query

Integer columncount = meta.getColumnCount();

int count = 1 ; // start counting from 1 always

String[] columnNames = null;

while(columncount <=count) {
    columnNames [i] = meta.getColumnName(i);
}

System.out.println (columnNames.size() ); //see the list and bind it to TableModel object. the to your jtbale.setModel(your_table_model);

score 1 · Answer 9 · edited Mar 13 '16 at 22:34

1

When you need the column names, but do not want to grab entries:

PreparedStatement stmt = connection.prepareStatement("SHOW COLUMNS FROM `yourTable`");

ResultSet set = stmt.executeQuery();

//store all of the columns names
List<String> names = new ArrayList<>();
while (set.next()) { names.add(set.getString("Field")); }

NOTE: Only works with MySQL

edited Mar 13 '16 at 22:34

Community

1
1

answered Dec 16 '15 at 21:42

Hunter S

1,293
1
15
26

1

Only this worked for me!!. Had to come way down for this. Not sure why getColumnName(i) & getColumnLabel(i), retrieved me unexpected weird data. Many thanks! – VipiN Negi Jan 07 '19 at 17:45
Glad this helped you! – Hunter S Jan 08 '19 at 01:42

score 0 · Answer 10 · answered Jan 14 '14 at 11:48

@Cyntech is right.

Incase your table is empty and you still need to get table column names you can get your column as type Vector,see the following:

ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM TABLE2");
ResultSetMetaData rsmd = rs.getMetaData();
int columnCount = rsmd.getColumnCount();

Vector<Vector<String>>tableVector = new Vector<Vector<String>>(); 
boolean isTableEmpty = true;
int col = 0;

 while(rs.next())
    {
      isTableEmpty = false;  //set to false since rs.next has data: this means the table is not empty
       if(col != columnCount)
          {
            for(int x = 1;x <= columnCount;x++){
                 Vector<String> tFields = new Vector<String>(); 
                 tFields.add(rsmd.getColumnName(x).toString());
                 tableVector.add(tFields);
             }
            col = columnCount;
          }
     } 


      //if table is empty then get column names only
  if(isTableEmpty){  
      for(int x=1;x<=colCount;x++){
           Vector<String> tFields = new Vector<String>(); 
           tFields.add(rsmd.getColumnName(x).toString());
           tableVector.add(tFields);
        }
      }

 rs.close();
 stmt.close();

 return tableVector;

score 0 · Answer 11 · edited Aug 12 '14 at 15:34

ResultSet rsTst = hiSession.connection().prepareStatement(queryStr).executeQuery(); 
ResultSetMetaData meta = rsTst.getMetaData();
int columnCount = meta.getColumnCount();
// The column count starts from 1

String nameValuePair = "";
while (rsTst.next()) {
    for (int i = 1; i < columnCount + 1; i++ ) {
        String name = meta.getColumnName(i);
        // Do stuff with name

        String value = rsTst.getString(i); //.getObject(1);
        nameValuePair = nameValuePair + name + "=" +value + ",";
        //nameValuePair = nameValuePair + ", ";
    }
    nameValuePair = nameValuePair+"||" + "\t";
}

score 0 · Answer 12 · answered Nov 07 '19 at 13:52

If you want to use spring jdbctemplate and don't want to deal with connection staff, you can use following:

jdbcTemplate.query("select * from books", new RowCallbackHandler() {
        public void processRow(ResultSet resultSet) throws SQLException {
            ResultSetMetaData rsmd = resultSet.getMetaData();
            for (int i = 1; i <= rsmd.getColumnCount(); i++ ) {
                String name = rsmd.getColumnName(i);
                // Do stuff with name
            }
        }
    });

score 0 · Answer 13 · answered Mar 17 '20 at 09:54

U can get column name and value from resultSet.getMetaData(); This code work for me:

Connection conn = null;
PreparedStatement preparedStatement = null;
    try {
        Class.forName("com.mysql.cj.jdbc.Driver");
        conn = MySQLJDBCUtil.getConnection();
        preparedStatement = conn.prepareStatement(sql);
        if (params != null) {
            for (int i = 0; i < params.size(); i++) {
                preparedStatement.setObject(i + 1, params.get(i).getSqlValue());
            }
            ResultSet resultSet = preparedStatement.executeQuery();
            ResultSetMetaData md = resultSet.getMetaData();
            while (resultSet.next()) {
                int counter = md.getColumnCount();
                String colName[] = new String[counter];
                Map<String, Object> field = new HashMap<>();
                for (int loop = 1; loop <= counter; loop++) {
                    int index = loop - 1;
                    colName[index] = md.getColumnLabel(loop);
                    field.put(colName[index], resultSet.getObject(colName[index]));
                }
                rows.add(field);
            }
        }
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        if (preparedStatement != null) {
            try {
                preparedStatement.close();
            }catch (Exception e1) {
                e1.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    return rows;

score 0 · Answer 14 · answered Jun 05 '20 at 16:02

I know, this question is already answered but probably somebody like me needs to access a column name from DatabaseMetaData by label instead of index:

ResultSet resultSet = null;
DatabaseMetaData metaData = null;

    try {
        metaData  = connection.getMetaData();
        resultSet = metaData.getColumns(null, null, tableName, null);

        while (resultSet.next()){
            String name = resultSet.getString("COLUMN_NAME");
        }
    }

Retrieve column names from java.sql.ResultSet

14 Answers14

Linked

Related