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

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*X*().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 the CachedRowSet instead of the database.

Answer 1

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 2

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 3

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 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).