如何检查CachedRowSet中是否存在列名？

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.

我正在查询可能会发生变化的视图中的数据。我需要知道在执行crs.get ******()之前列是否存在。我发现我可以查询这样的元数据,看看在我请求数据之前是否存在列。

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.

编辑:它必须是数据库不可知的。这就是我引用CachedRowSet而不是数据库的原因。

6 个解决方案

#1

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

一般的JDBC API没有更简单的方法(至少不是我所知道的,或者可以找到......我在我自己开发的工具集中有完全相同的代码。)

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

话虽这么说,如果您使用专有的,特定于数据库的API和/或SQL查询,我相信您可以找到更优雅的方法来做同样的事情......但是您必须为每个数据库编写自定义代码需要处理。如果我是你,我会坚持使用JDBC API。

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

您提出的解决方案是否存在让您认为不正确的问题?这对我来说似乎很简单......

#2

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.

如果列不在CachedRowSet中,你可以采用更短的方法来使用findColumn()为InvalidColumName抛出SQLException这一事实。

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.

可能是滥用异常处理(根据EffectiveJava第2版第57项),但它是循环遍历元数据中所有列的替代方法。

#3

Which Database?

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

我认为在Oracle中列出了列的列表。

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'

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.

每个RDBMS都应该有类似的东西。

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.

并且从元数据中获取列,如果您希望它在以前知道列是否存在之前避免获取数据。

#4

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.

不,真的没有更好的方法。您可能想要重新查看问题。如果您可以重新定义问题,有时它会使解决方案更简单,因为问题已经改变。

#5

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

如果我没记错的话,当oracle缓存的行集实现与连接池一起使用时,有一个神秘的问题会让它变得如此丑陋。似乎存在对缓存的行集对象中保持的连接的静默引用(即使它应该断开连接),这将关闭随后在池中对垃圾收集打开的另一个连接。出于这个原因,我最终放弃并编写了自己的数据对象层(这些天我将其交给spring和hibernate)。

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

如果我们不必在lambda中捕获异常(没有一些丑陋的黑客),这将是非常好的

#1