大家帮看下这段代码有什么问题,我想要得到的是数据库的表字段,如果指定模式名,直接执行;如果有dblink,通过dblink的方式查找字段信息;如果是同义词表,用同义词的方法查找字段信息。现在的问题是,执行dblink的查找后,resultset有信息,即使不是同义词表,没有执行resultSet = stmt.executeQuery(sql); ,先前保留的resultset也会被清空,还有什么方法可以解决么
public List<String> getColByTab(String tableName) throws SQLException {
ResultSet resultSet_synonym;
ResultSet resultSet_dblink, resultSet22;
if (hasDefaultSchema()) {
String sql = "SELECT A.COLUMN_NAME as name"
+ " FROM DBA_TAB_COLUMNS A " + "WHERE A.OWNER = '"
+ defaultSchema + "' AND A.TABLE_NAME = '" + tableName
+ "'";
resultSet = stmt.executeQuery(sql);
} else {
String sql1 = "select object_name, OWNER from dba_objects where object_type='DATABASE LINK'";
resultSet_dblink = stmt.executeQuery(sql1);
while (resultSet_dblink.next()) {
String StrRS = resultSet_dblink.getString(1);
System.out.println("dblink=============" + StrRS);
String sql3 = "select owner from all_tables@" + StrRS
+ "" + " where table_name=upper('" + tableName
+ "')";
resultSet22 = stmt.executeQuery(sql3); while (resultSet22.next()) {
String sql_link = "select COLUMN_NAME from DBA_TAB_COLUMNS@"
+ StrRS
+ " where OWNER='"
+ resultSet22.getString(1).trim()
+ "'and TABLE_NAME='" + tableName + "'";
resultSet = stmt.executeQuery(sql_link);
}
}
String sql = "select TABLE_OWNER,TABLE_NAME "
+ "from dba_synonyms where synonym_name like '" + tableName
+ "'";
resultSet_synonym = stmt.executeQuery(sql);
while (resultSet_synonym.next()) {
sql = "SELECT COLUMN_NAME as name FROM DBA_TAB_COLUMNS "
+ "WHERE OWNER = '" + resultSet_synonym.getString(1)
+ "'" + " and TABLE_NAME like '"
+ resultSet_synonym.getString(2) + "'";
resultSet = stmt.executeQuery(sql);
}
}
if (resultSet == null) {
return null;
}
List<String> columnList = new ArrayList<String>();
while (resultSet.next()) {
for (int i = 1; i < ((ResultSetMetaData) resultSet.getMetaData())
.getColumnCount() + 1; i++) {
columnList.add(resultSet.getString(i));
}
}
return columnList;
}
public List<String> getColByTab(String tableName) throws SQLException {
ResultSet resultSet_synonym;
ResultSet resultSet_dblink, resultSet22;
if (hasDefaultSchema()) {
String sql = "SELECT A.COLUMN_NAME as name"
+ " FROM DBA_TAB_COLUMNS A " + "WHERE A.OWNER = '"
+ defaultSchema + "' AND A.TABLE_NAME = '" + tableName
+ "'";
resultSet = stmt.executeQuery(sql);
} else {
String sql1 = "select object_name, OWNER from dba_objects where object_type='DATABASE LINK'";
resultSet_dblink = stmt.executeQuery(sql1);
while (resultSet_dblink.next()) {
String StrRS = resultSet_dblink.getString(1);
System.out.println("dblink=============" + StrRS);
String sql3 = "select owner from all_tables@" + StrRS
+ "" + " where table_name=upper('" + tableName
+ "')";
resultSet22 = stmt.executeQuery(sql3); while (resultSet22.next()) {
String sql_link = "select COLUMN_NAME from DBA_TAB_COLUMNS@"
+ StrRS
+ " where OWNER='"
+ resultSet22.getString(1).trim()
+ "'and TABLE_NAME='" + tableName + "'";
resultSet = stmt.executeQuery(sql_link);
}
}
String sql = "select TABLE_OWNER,TABLE_NAME "
+ "from dba_synonyms where synonym_name like '" + tableName
+ "'";
resultSet_synonym = stmt.executeQuery(sql);
while (resultSet_synonym.next()) {
sql = "SELECT COLUMN_NAME as name FROM DBA_TAB_COLUMNS "
+ "WHERE OWNER = '" + resultSet_synonym.getString(1)
+ "'" + " and TABLE_NAME like '"
+ resultSet_synonym.getString(2) + "'";
resultSet = stmt.executeQuery(sql);
}
}
if (resultSet == null) {
return null;
}
List<String> columnList = new ArrayList<String>();
while (resultSet.next()) {
for (int i = 1; i < ((ResultSetMetaData) resultSet.getMetaData())
.getColumnCount() + 1; i++) {
columnList.add(resultSet.getString(i));
}
}
return columnList;
}
无论如何 都会执行到的 以为用的是同一个resultSet变量 后面的把前面的覆盖了if(同义词){
String sql = "select TABLE_OWNER,TABLE_NAME "
+ "from dba_synonyms where synonym_name like '" + tableName + "'";
resultSet_synonym = stmt.executeQuery(sql); while (resultSet_synonym.next()) {
sql = "SELECT COLUMN_NAME as name FROM DBA_TAB_COLUMNS "
+ "WHERE OWNER = '" + resultSet_synonym.getString(1)
+ "'" + " and TABLE_NAME like '"
+ resultSet_synonym.getString(2) + "'"; resultSet = stmt.executeQuery(sql);
}或者if(dblink){
从 dblink 查询...
}else{ 从synonyms 查询......
}
每个stmt好象只能保持一个当前查询或者更新的结果的
这样就能保证 stmt只被执行一次
而不是,每种情况都到库里执行一遍,然后遍历结果去判断是否存在
每开一个stmt都是会耗一部分资源的当然这样也可以‘实现’你想要的东西