How can I determine if the column name exist in the ResultSet?

Question

As the ResultSet contains the data returned from the dynamic SQL, if there are any method to determine if the ResultSet contains a particular column name?

For example , if I run rs.getString("Column_ABC") but "Column_ABC" does not really exist, it will throw out the exception.

How can I test if the ResultSet can get a data from a column named "Column_ABC"?

score 143 · Accepted Answer · edited Mar 29 '13 at 07:21

143

Use the ResultSetMetaData class.

public static boolean hasColumn(ResultSet rs, String columnName) throws SQLException {
    ResultSetMetaData rsmd = rs.getMetaData();
    int columns = rsmd.getColumnCount();
    for (int x = 1; x <= columns; x++) {
        if (columnName.equals(rsmd.getColumnName(x))) {
            return true;
        }
    }
    return false;
}

The thing I don't understand is why this function would ever be needed. The query or stored procedure being executed should have known results. The columns of the query should be known. Needing a function like this may be a sign that there is a design problem somewhere.

edited Mar 29 '13 at 07:21

Mohsen

3,512
3
38
66

answered Aug 30 '10 at 11:38

Erick Robertson

32,125
13
69
98

When executing user queries in tools like TOAD the application does not know the query structure and must use `ResultSetMetaData`. But yes, searching for a particular column name is strange. – gpeche Aug 31 '10 at 07:05
Just commenting... in your example, you're retrieving all column info. For a user query, I expect this to be standard. This is still not searching to see if a specific column is included. – Erick Robertson Aug 31 '10 at 11:12
13

This is basically correct but `getColumnName` takes its parameter starting from 1 not 0. You need `for (int x = 1; x <= columns; x++)` – Adrian Smith May 03 '12 at 14:33
take in mind that if you use spring with JdbcTemplate you need to close the connection by your one. – German Attanasio Oct 17 '12 at 02:43
51

Nothing strange about searching for a column name. In a highly dynamic, user-configurable system column sets may vary widely based on user selections. Yes, the set can be determined but to assume it's always hard-coded and therefore known is incorrect. – Gullbyrd Jun 19 '14 at 15:41
10

You can use `ResultSetMetaData.getColumnLabel` if you are interested in getting the name as defined by the `AS` clause of your SQL query. – Santosh Feb 18 '15 at 10:46
I've seen cases where for backward compatibility it's appropriate to ask whether field "FOO" exists or not. Older DB instances won't yet have the new column, and it should be handled correctly. – paiego Feb 16 '16 at 01:56
Guys, instead of sending the ResultSet, send ResultSetMetaData.`public static boolean hasColumn(ResultSetMetaData rsmd, String columnName) throws SQLException { int columns = rsmd.getColumnCount(); for (int x = 1; x <= columns; x++) { if (columnName.equals(rsmd.getColumnName(x))) { return true; } } return false; }` – Feb 15 '17 at 20:31
It is a good solution but it can fail with aliases (for example if you rename some column, in some cases you get the original column name). The 'dirty' approach from Zip184 works always. – Juangui Jordán May 05 '17 at 16:48
1

@ErickRobertson Your answer is perfect. But is there any better way of doing this check, without any performance issue. As these conditional loop statements would impact the performance, right? – Santosh Anantharamaiah Jul 25 '17 at 17:05
@SantoshAnantharamaiah Certainly they would impact performance. That's why you should already know your schema so you don't have to look into the result set meta-data for this information. This information should be static and never change, thus never requiring checks like this. – Erick Robertson Sep 11 '17 at 14:55
4

I suggest using `equalsIgnoreCase` to check if the name is the same. In my case i have a lowercase column name in the query that gets returned as UPPERCASE from the `getColumnName()` function – Luca Regazzi Jan 24 '18 at 11:27
I agree with @LucaRegazzi and created an edit suggestion. sql is case insensitive anyway so it seems appropriate. – Pieter De Bie Nov 09 '18 at 06:56
7

Yes, the **DB** schema should be well-known. **However**, you may have a situation where one stored proc returns a column from a `JOIN`, and another stored proc does not return that column. It may be good for performance to only do joins that are needed, rather than selecting unnecessary columns with default values to match a theoretical schema that your app might consume. By having something like `rs.containsColumn(x)`, one method could read the result of either stored proc, rather than a 2nd method with one fewer `rs.get***()` that's otherwise identical to the first. No? – s.co.tt Oct 25 '19 at 03:27
2

The use case that I encountered was that I had to use the same `RowMapper` for 2 different queries. And both the queries has just one column difference in the result set. – Prince Bansal Oct 19 '20 at 16:34
hi, is this answer more efficient than Talick answer below https://stackoverflow.com/a/59727839/15435022? I am trying to find optimal , and shortcode method best for or team and company. I know its been long time, just curious, thanks – mattsmith5 Sep 12 '22 at 02:45

score 23 · Answer 2 · edited Mar 26 '20 at 13:48

23

private boolean isThere(ResultSet rs, String column){
    try{
        rs.findColumn(column);
        return true;
    } catch (SQLException sqlex){
        logger.debug("column doesn't exist {}", column);
    }

    return false;
}

edited Mar 26 '20 at 13:48

Drew Stephens

17,207
15
66
82

answered Jan 14 '20 at 05:12

Talick

338
2
9

10

@mjuarez, The `findColumn` command return is not important in this case. Rightfully ignored. This `isThere` method definitely does not always return true. `findColumn` throws SQLException if the column is not found. This answer seems to be fine. – Freddie Feb 10 '20 at 19:56
is this answer better and more efficient than answer above Erick Robertson? I am trying to find optimal answer for our team & company, thanks https://stackoverflow.com/a/3599960/15435022 – mattsmith5 Sep 12 '22 at 02:44

Zip184 · Answer 3 · 2014-08-20T12:18:12.627

8

Not sure if this is more or less efficient than Erick's answer but it's easier.

String str;

try {
    str = rs.getString(columnName);
} catch (java.sql.SQLException e) {
    str = null;
}

edited Aug 20 '14 at 12:18

answered Jun 25 '14 at 18:15

Zip184

1,792
2
21
34

1

It's, without a doubt, easier to understand. However, and regardless that fact that I don't see the JDBC driver returning "SQLServer" Exceptions, the ResultSet methods return SQLException if the column name is invalid or if a more generic SQL error occurs (which dificults the job of knowing what happened: if wrong column name or if an actual error occurred) +info @ [ResultSet Javadoc](http://docs.oracle.com/javase/8/docs/api/java/sql/ResultSet.html#getObject-java.lang.String-) – João Dias Amaro Aug 12 '14 at 13:50
43

-1 Exceptions should be used only in exceptional circumstances, and then they should be logged. Taking the easy way out tends to create code that's difficult to understand and maintain. If you are using a server-side resultset and lost connection to the database before making this call, then this method will throw an exception even if the column exists. This type of scenario can lead to disasterous consequences if the code uses this check (for example) to determine if the entire database needs initialization. – Erick Robertson Aug 14 '14 at 11:18
Thanks João you're right. I meant SQLException. Tried to pick a generic one. – Zip184 Aug 20 '14 at 12:19

score -1 · Answer 4 · answered Mar 20 '20 at 12:26

-1

resultSet.getColumnMetaData().contains(columnName)

answered Mar 20 '20 at 12:26

sanket patwardhan

19
1

3

can you modify your answer according to the variable name? can you also provide an explanation of how it works? – Saurabh Prakash Mar 20 '20 at 13:25
3

the `ResultSet` interface does not have `getColumnMetaData` method – IdontCareAboutReputationPoints Sep 30 '20 at 04:42

VivekJ · Answer 5 · 2016-09-21T09:21:36.203

-4

/**
 * returns default value if column is not present in resultset
 * 
 * @param rs
 * @param columnLabel
 * @param defaultValue
 * @return
 */
@SuppressWarnings("unchecked")
private static <T> T getValueFromResultSet(final ResultSet rs,
        String columnLabel, T defaultValue) {
    try {
        return (T) rs.getObject(columnLabel);
    } catch (SQLException e) {
        return defaultValue;
    }
}

In java version >=7 you have a option of passing Class type in ResultSet#getObject method

edited Sep 21 '16 at 09:21

answered Sep 21 '16 at 09:16

VivekJ

49
2

1

This should be the correct answer. This is a safe way to provide a get object type from row, to fill / map an object regardless of whether it is meant to be filled or not, the client or underlying business logic should know what to expect, and be safety checking it's data. – thekevshow Sep 30 '16 at 05:45
1

I disagree, Erick Robertson explains why in the comments of the answer above. – Pieter De Bie Dec 14 '16 at 06:47
3

While this works, it abuses the idea behind exception handling. – Ben Nov 10 '17 at 18:40

score -19 · Answer 6 · answered Aug 30 '10 at 11:25

-19

if not rs.getString("Column_ABC")= nothing then ' your code here

answered Aug 30 '10 at 11:25

Amit

6,839
21
56
90

9

This is a bad idea, and it's also wrong. When a column doesn't exist, it doesn't return nothing. It throws an SQLException, which you have to catch and handle. You should never use an exception being thrown for a simple check like this. You should always look for a method that would actually return a boolean - something that will actually perform a proper check. – Erick Robertson Aug 30 '10 at 11:41

How can I determine if the column name exist in the ResultSet?

6 Answers6

Linked