How do I check to see if a column name exists in a

Question

I am querying data from views that are subject to change. I need to know if the column exists before I do a crs.get******().I have found that I can query the metadata like this to see if a column exist before I request the data from it.

ResultSetMetaData meta = crs.getMetaData();
int numCol = meta.getColumnCount();

for (int i = 1; i < numCol+1; i++) 
    if(meta.getColumnName(i).equals("name"))
        return true;

Is there a simpler way of checking to see if a column exists?

EDIT: It must be database agnostic. That is why I am referencing the CachedRowSet instead of the database.

Answer 1

you could take the shorter approach of using the fact that findColumn() will throw an SQLException for InvalidColumName if the column isn't in the CachedRowSet.

for example

 try {
     int foundColIndex = results.findColumn("nameOfColumn");
} catch {
  // do whatever else makes sense
}

Likely an abuse of Exception Handling (per EffectiveJava 2nd ed item 57) but it is an alternative to looping through all the columns from the meta data.

Answer 2

Which Database?

I think in Oracle there are tables where the columns are listed.

I don't remember if it work for views also, but I guess they do, it was something like:

select colum_name from all_views where view_name like 'myview'

or

select name from all_objects where object_name like 'myview' and object_type='view'

I don't remember exactly the syntax. You should have spacial permissions though.

Every RDBMS should have something similar.

You can also perform the query

select * from myView where 1 = 0 ; 

And from the metadata get the columns, if what you want it to avoid fetching the data before to know if the columns are present.

Answer 3

There's not a simpler way with the general JDBC API (at least not that I know of, or can find...I've got exactly the same code in my home-grown toolset.)

(Your code isn't complete):

ResultSetMetaData meta = crs.getMetaData();
 int numCol = meta.getColumnCount();

for (int i = 1; i < numCol+1; i++) 
{
    if(meta.getColumnName(i).equals("name"))
    {return true;}

}
return false;

That being said, if you use proprietary, database-specific API's and/or SQL queries, I'm sure you can find more elegant ways of doing the same thing...but you'd have to write custom code for each database you need to deal with. I'd stick with the JDBC APIs, if I were you.

Is there something about your proposed solution that makes you think it's incorrect? It seems simple enough to me...

Answer 4

WARNING: following comment purely from memory without any supporting paperwork :)

If I recall correctly there is a mysterious problem that rears its ever-so-ugly-head when the oracle cached rowset implementation is used with connection pooling. There appears to be a silent reference to the connection held within the cached rowset object (even though it's supposed to be disconnected) which closes another connection subsequently opened from pool on garbage collection. For this reason I eventually gave up and wrote my own data object layer (these days I'd hand that over to spring & hibernate).

Answer 5

No, there really isn't a better way. You may want to relook at the problem. If you can redefine the problem, sometimes it makes the solution simpler because the problem has changed.

Answer 6

Old thread, but I've just faced the same problem and ended up with an utility function:

private Set<String> getColumnNames(ResultSet cached) throws SQLException {
    ResultSetMetaData metaData = cached.getMetaData();
    return IntStream.range(1, metaData.getColumnCount())
                    .mapToObj(i -> {
                        try {
                            return metaData.getColumnName(i);
                        } catch (SQLException e) {
                            throw new RuntimeException(e);
                        }
                    }).collect(toSet());
}

It'd be quite elegent if we wouldn't have to catch exceptions inside a lambda (without some ugly hacks)