- android - 多次调用 OnPrimaryClipChangedListener
- android - 无法更新 RecyclerView 中的 TextView 字段
- android.database.CursorIndexOutOfBoundsException : Index 0 requested, 光标大小为 0
- android - 使用 AppCompat 时,我们是否需要明确指定其 UI 组件(Spinner、EditText)颜色
我想对两个 CachedRowSet 执行左连接。好像 JoinRowSetImpl 只支持 INNER JOIN 。我可以用什么来让左连接工作?
感谢和问候。
最佳答案
似乎以这种方式重建 JoinRowSetImpl 类就可以解决问题。 initJOIN 方法重建如下(适用于我的目的):
/**
* Internal initialization of <code>JoinRowSet</code>.
*/
private void initJOIN(CachedRowSet rowset) throws SQLException {
try {
CachedRowSetImpl cRowset = (CachedRowSetImpl) rowset;
// Create a new CachedRowSet object local to this function.
CachedRowSetImpl crsTemp = new CachedRowSetImpl();
RowSetMetaDataImpl rsmd = new RowSetMetaDataImpl();
/*
* The following 'if block' seems to be always going true. commenting this out
* for present
*
* if (!supportedJOINs[1]) { throw new
* SQLException(resBundle.handleGetObject("joinrowsetimpl.notsupported").
* toString()); }
*
*/
if (vecRowSetsInJOIN.isEmpty()) {
// implies first cRowset to be added to the Join
// simply add this as a CachedRowSet.
// Also add it to the class variable of type vector
// do not need to check "type" of Join but it should be set.
crsInternal = (CachedRowSetImpl) rowset.createCopy();
crsInternal.setMetaData((RowSetMetaDataImpl) cRowset.getMetaData());
// metadata will also set the MatchColumn.
vecRowSetsInJOIN.add(cRowset);
} else {
// At this point we are ready to add another rowset to 'this' object
// Check the size of vecJoinType and vecRowSetsInJoin
// If nothing is being set, internally call setJoinType()
// to set to JoinRowSet.INNER_JOIN.
// For two rowsets one (valid) entry should be there in vecJoinType
// For three rowsets two (valid) entries should be there in vecJoinType
// Maintain vecRowSetsInJoin = vecJoinType + 1
if ((vecRowSetsInJOIN.size() - vecJoinType.size()) == 2) {
// we are going to add next rowset and setJoinType has not been set
// recently, so set it to setJoinType() to JoinRowSet.INNER_JOIN.
// the default join type
setJoinType(JoinRowSet.INNER_JOIN);
} else if ((vecRowSetsInJOIN.size() - vecJoinType.size()) == 1) {
// do nothing setjoinType() has been set by programmer
}
// Add the table names to the class variable of type vector.
vecTableNames.add(crsInternal.getTableName());
vecTableNames.add(cRowset.getTableName());
// Now we have two rowsets crsInternal and cRowset which need
// to be INNER JOIN'ED to form a new rowset
// Compare table1.MatchColumn1.value1 == { table2.MatchColumn2.value1
// ... upto table2.MatchColumn2.valueN }
// ...
// Compare table1.MatchColumn1.valueM == { table2.MatchColumn2.value1
// ... upto table2.MatchColumn2.valueN }
//
// Assuming first rowset has M rows and second N rows.
int rowCount2 = cRowset.size();
int rowCount1 = crsInternal.size();
// total columns in the new CachedRowSet will be sum of both -1
// (common column)
int matchColumnCount = 0;
for (int i = 0; i < crsInternal.getMatchColumnIndexes().length; i++) {
if (crsInternal.getMatchColumnIndexes()[i] != -1)
++matchColumnCount;
else
break;
}
rsmd.setColumnCount(crsInternal.getMetaData().getColumnCount() + cRowset.getMetaData().getColumnCount()
- matchColumnCount);
crsTemp.setMetaData(rsmd);
crsInternal.beforeFirst();
cRowset.beforeFirst();
for (int i = 1; i <= rowCount1; i++) {
if (crsInternal.isAfterLast()) {
break;
}
if (crsInternal.next()) {
cRowset.beforeFirst();
//
int p;
int colc = 0;
// ****first with null***
crsTemp.moveToInsertRow();
boolean match = true;
// create a new rowset crsTemp with data from first rowset
for (p = 1; p <= crsInternal.getMetaData().getColumnCount(); p++) {
match = false;
for (int k = 0; k < matchColumnCount; k++) {
if (p == crsInternal.getMatchColumnIndexes()[k]) {
match = true;
break;
}
}
if (!match) {
crsTemp.updateObject(++colc, crsInternal.getObject(p));
// column type also needs to be passed.
rsmd.setColumnName(colc, crsInternal.getMetaData().getColumnName(p));
rsmd.setTableName(colc, crsInternal.getTableName());
rsmd.setColumnType(p, crsInternal.getMetaData().getColumnType(p));
rsmd.setAutoIncrement(p, crsInternal.getMetaData().isAutoIncrement(p));
rsmd.setCaseSensitive(p, crsInternal.getMetaData().isCaseSensitive(p));
rsmd.setCatalogName(p, crsInternal.getMetaData().getCatalogName(p));
rsmd.setColumnDisplaySize(p,
crsInternal.getMetaData().getColumnDisplaySize(p));
rsmd.setColumnLabel(p, crsInternal.getMetaData().getColumnLabel(p));
rsmd.setColumnType(p, crsInternal.getMetaData().getColumnType(p));
rsmd.setColumnTypeName(p, crsInternal.getMetaData().getColumnTypeName(p));
rsmd.setCurrency(p, crsInternal.getMetaData().isCurrency(p));
rsmd.setNullable(p, crsInternal.getMetaData().isNullable(p));
rsmd.setPrecision(p, crsInternal.getMetaData().getPrecision(p));
rsmd.setScale(p, crsInternal.getMetaData().getScale(p));
rsmd.setSchemaName(p, crsInternal.getMetaData().getSchemaName(p));
rsmd.setSearchable(p, crsInternal.getMetaData().isSearchable(p));
rsmd.setSigned(p, crsInternal.getMetaData().isSigned(p));
} else {
// will happen only once, for that merged column pass
// the types as OBJECT, if types not equal
crsTemp.updateObject(++colc, crsInternal.getObject(p));
rsmd.setColumnName(colc, crsInternal.getMetaData().getColumnName(p));
rsmd.setTableName(colc,
crsInternal.getTableName() + "#" + cRowset.getTableName());
rsmd.setColumnType(p, crsInternal.getMetaData().getColumnType(p));
rsmd.setAutoIncrement(p, crsInternal.getMetaData().isAutoIncrement(p));
rsmd.setCaseSensitive(p, crsInternal.getMetaData().isCaseSensitive(p));
rsmd.setCatalogName(p, crsInternal.getMetaData().getCatalogName(p));
rsmd.setColumnDisplaySize(p,
crsInternal.getMetaData().getColumnDisplaySize(p));
rsmd.setColumnLabel(p, crsInternal.getMetaData().getColumnLabel(p));
rsmd.setColumnType(p, crsInternal.getMetaData().getColumnType(p));
rsmd.setColumnTypeName(p, crsInternal.getMetaData().getColumnTypeName(p));
rsmd.setCurrency(p, crsInternal.getMetaData().isCurrency(p));
rsmd.setNullable(p, crsInternal.getMetaData().isNullable(p));
rsmd.setPrecision(p, crsInternal.getMetaData().getPrecision(p));
rsmd.setScale(p, crsInternal.getMetaData().getScale(p));
rsmd.setSchemaName(p, crsInternal.getMetaData().getSchemaName(p));
rsmd.setSearchable(p, crsInternal.getMetaData().isSearchable(p));
rsmd.setSigned(p, crsInternal.getMetaData().isSigned(p));
// don't do ++colc in the above statement
} // end if
} // end for
//*********second**************
for (int q = 1; q <= cRowset.getMetaData().getColumnCount(); q++) {
match = false;
for (int k = 0; k < matchColumnCount; k++) {
if (q == cRowset.getMatchColumnIndexes()[k]) {
match = true;
break;
}
}
if (!match) {
crsTemp.updateNull(++colc);
rsmd.setColumnName(colc, cRowset.getMetaData().getColumnName(q));
rsmd.setTableName(colc, cRowset.getTableName());
/**
* This will happen for a special case scenario. The value of 'p' will
* always be one more than the number of columns in the first rowset in the
* join. So, for a value of 'q' which is the number of columns in the second
* rowset that participates in the join. So decrement value of 'p' by 1 else
* `p+q-1` will be out of range.
**/
// if((p+q-1) > ((crsInternal.getMetaData().getColumnCount()) +
// (cRowset.getMetaData().getColumnCount()) - 1)) {
// --p;
// }
rsmd.setColumnType(p + q - 1, cRowset.getMetaData().getColumnType(q));
rsmd.setAutoIncrement(p + q - 1, cRowset.getMetaData().isAutoIncrement(q));
rsmd.setCaseSensitive(p + q - 1, cRowset.getMetaData().isCaseSensitive(q));
rsmd.setCatalogName(p + q - 1, cRowset.getMetaData().getCatalogName(q));
rsmd.setColumnDisplaySize(p + q - 1,
cRowset.getMetaData().getColumnDisplaySize(q));
rsmd.setColumnLabel(p + q - 1, cRowset.getMetaData().getColumnLabel(q));
rsmd.setColumnType(p + q - 1, cRowset.getMetaData().getColumnType(q));
rsmd.setColumnTypeName(p + q - 1,
cRowset.getMetaData().getColumnTypeName(q));
rsmd.setCurrency(p + q - 1, cRowset.getMetaData().isCurrency(q));
rsmd.setNullable(p + q - 1, cRowset.getMetaData().isNullable(q));
rsmd.setPrecision(p + q - 1, cRowset.getMetaData().getPrecision(q));
rsmd.setScale(p + q - 1, cRowset.getMetaData().getScale(q));
rsmd.setSchemaName(p + q - 1, cRowset.getMetaData().getSchemaName(q));
rsmd.setSearchable(p + q - 1, cRowset.getMetaData().isSearchable(q));
rsmd.setSigned(p + q - 1, cRowset.getMetaData().isSigned(q));
} else {
--p;
}
}
//**********second updates if foud match**************
colc = 13;
for (int j = 1; j <= rowCount2; j++) {
if (cRowset.isAfterLast()) {
break;
}
if(cRowset.next()) {
// System.out.println(cRowset.getInt(1));
match = true;
for (int k = 0; k < matchColumnCount; k++) {
if (!crsInternal.getObject(crsInternal.getMatchColumnIndexes()[k])
.equals(cRowset.getObject(cRowset.getMatchColumnIndexes()[k]))) {
match = false;
break;
}
}
if (match) {
// append the rowset crsTemp, with data from second rowset
for (int q = 1; q <= cRowset.getMetaData().getColumnCount(); q++) {
match = false;
for (int k = 0; k < matchColumnCount; k++) {
if (q == cRowset.getMatchColumnIndexes()[k]) {
match = true;
break;
}
}
if (!match) {
crsTemp.updateObject(++colc, cRowset.getObject(q));
rsmd.setColumnName(colc, cRowset.getMetaData().getColumnName(q));
rsmd.setTableName(colc, cRowset.getTableName());
/**
* This will happen for a special case scenario. The value of 'p' will
* always be one more than the number of columns in the first rowset in the
* join. So, for a value of 'q' which is the number of columns in the second
* rowset that participates in the join. So decrement value of 'p' by 1 else
* `p+q-1` will be out of range.
**/
// if((p+q-1) > ((crsInternal.getMetaData().getColumnCount()) +
// (cRowset.getMetaData().getColumnCount()) - 1)) {
// --p;
// }
rsmd.setColumnType(p + q - 1, cRowset.getMetaData().getColumnType(q));
rsmd.setAutoIncrement(p + q - 1, cRowset.getMetaData().isAutoIncrement(q));
rsmd.setCaseSensitive(p + q - 1, cRowset.getMetaData().isCaseSensitive(q));
rsmd.setCatalogName(p + q - 1, cRowset.getMetaData().getCatalogName(q));
rsmd.setColumnDisplaySize(p + q - 1,
cRowset.getMetaData().getColumnDisplaySize(q));
rsmd.setColumnLabel(p + q - 1, cRowset.getMetaData().getColumnLabel(q));
rsmd.setColumnType(p + q - 1, cRowset.getMetaData().getColumnType(q));
rsmd.setColumnTypeName(p + q - 1,
cRowset.getMetaData().getColumnTypeName(q));
rsmd.setCurrency(p + q - 1, cRowset.getMetaData().isCurrency(q));
rsmd.setNullable(p + q - 1, cRowset.getMetaData().isNullable(q));
rsmd.setPrecision(p + q - 1, cRowset.getMetaData().getPrecision(q));
rsmd.setScale(p + q - 1, cRowset.getMetaData().getScale(q));
rsmd.setSchemaName(p + q - 1, cRowset.getMetaData().getSchemaName(q));
rsmd.setSearchable(p + q - 1, cRowset.getMetaData().isSearchable(q));
rsmd.setSigned(p + q - 1, cRowset.getMetaData().isSigned(q));
} else {
--p;
}
}
cRowset.deleteRow();
break;
} else {
}
}
}
crsTemp.insertRow();
crsTemp.moveToCurrentRow();
}
} // end outer for
crsTemp.setMetaData(rsmd);
crsTemp.setOriginal();
// Now the join is done.
// Make crsInternal = crsTemp, to be ready for next merge, if at all.
int[] pCol = new int[matchColumnCount];
for (int i = 0; i < matchColumnCount; i++)
pCol[i] = crsInternal.getMatchColumnIndexes()[i];
crsInternal = (CachedRowSetImpl) crsTemp.createCopy();
// Because we add the first rowset as crsInternal to the
// merged rowset, so pCol will point to the Match column.
// until reset, am not sure we should set this or not(?)
// if this is not set next inner join won't happen
// if we explicitly do not set a set MatchColumn of
// the new crsInternal.
crsInternal.setMatchColumn(pCol);
// Add the merged rowset to the class variable of type vector.
crsInternal.setMetaData(rsmd);
vecRowSetsInJOIN.add(cRowset);
} // end if
} catch (SQLException sqle) {
// %%% Exception should not dump here:
sqle.printStackTrace();
throw new SQLException(resBundle.handleGetObject("joinrowsetimpl.initerror").toString() + sqle);
} catch (Exception e) {
e.printStackTrace();
throw new SQLException(resBundle.handleGetObject("joinrowsetimpl.genericerr").toString() + e);
}
}
关于java - CachedRowSet 上的左外连接,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/58952333/
我正在使用CachedRowSet。但是当我调用 insertRow() 方法时,出现 SQLException failed to insert row。 这是我的代码: static final
我想对两个 CachedRowSet 执行左连接。好像 JoinRowSetImpl 只支持 INNER JOIN 。我可以用什么来让左连接工作? 感谢和问候。 最佳答案 似乎以这种方式重建 Join
我正在开发一个应用程序,我的应用程序将从其他源以分隔格式(原始数据)获取数据,然后我需要将原始数据转换为结果集或缓存行集,然后我将构建的结果集/缓存行集传递给客户端应用程序(它调用我的应用程序来获取数
我想编写一个 java 函数,它接受一个 SQL 查询并返回一个 ResultSet 以供在别处处理。这无法完成,因为一旦连接关闭,ResultSet 就会失效。 谷歌搜索我发现了一个 VERY OL
我正在清理一个做得非常糟糕的大型网络应用程序。我无法一次性完成所有工作,所以我将其分成几个部分。我想做的一件事是实现一个带有接受 SQL 语句然后返回集合的函数的类。我的计划是,一旦解决了这个问题,我
我正在使用 CachedRowSetImpl,我可以从数据库中获取数据,但无法插入。 这是代码: public class NewClass { static final String DAT
我有以下 Java 7 代码来创建 CachedRowSet。 CachedRowSet crs = RowSetProvider.newFactory().createCachedRowSet();
我正在从可能发生变化的 View 中查询数据。在执行 crs.get******() 之前,我需要知道该列是否存在。 我发现我可以像这样查询元数据,在我从中请求数据之前查看列是否存在: ResultS
我正在使用 MySQL 数据库,我有一个包含 2 列的 Employee 表:Id(Int;主键)和 Name(String ).我已经编写了一些代码来向 Employee 表中插入一行,但是 acc
考虑以下代码片段: public void getUsers() { CachedRowSet rowSet = new CachedRowSetImpl(); .........
直到今天,我在处理查询结果时一直使用 ResultSet。但是今天我读了一些关于RowSet的内容和 CachedRowset我意识到它们可以更好地服务于我的目的。虽然在我阅读的所有示例中,RowSe
我正在使用我在代码中传递的javax.sql.rowset.CachedRowSet对象。在将 CachedRowSet 传递给我的代码的其余部分之前,填充它的 ResultSet 被关闭(仅用于阅读
我正在尝试通过jdbc类型4(microsoft jdbc 3.0)将图像文件(.png,200KB)插入Sql服务器(列类型varbinary(max)),这是我的代码: c
代码: OracleCachedRowSet rowSet = new OracleCachedRowSet(); ResultObject obj = new Resu
在我的 java 代码中,我使用 select 语句访问 oracle 数据库表。我收到很多行(大约 50.000 行),因此 rs.next() 需要一些时间来处理所有行。 using Result
我有一个 Derby SQL 数据库,其中有一个表,其中包含需要包含序列化对象的 blob 字段。我通过 JDBC 访问它。问题是,当我使用 ResultSet 反序列化对象时,一切正常,但如果我使用
我试图将一行插入到具有 CachedRowSet 增量 id 的表中(我正在使用 Java wit Java DB),但出现以下 SQLException: java.sql.SQLException
我很困惑什么是jdbcRowSet、CachedRowSet和WebRowSet。请给我最佳答案。 最佳答案 请参阅下面的所有三个示例。我想你会清楚地了解这些 RowSet接口(interface)。
我正在尝试使用 CachedRowSet 编写一个方法来执行 SQl 查询。示例代码如下(之前定义了用户和密码), public CachedRowSet getContentsOfCoffeesTa
我需要使用 CachedRowSet 或任何其他可能的方式复制 ResultSet 的帮助。我现在就在这个阶段 ResultSet rs = stmt.executeQuery(query); Cac
我是一名优秀的程序员,十分优秀!