LEFT OUTER JOIN on CachedRowSet

Question

I would like to perform left join on two CachedRowSet. Seems like JoinRowSetImpl only supports INNER JOIN. What can I use to get left join working?

Thanks and regards.

Answer 1

Seem like rebuilding JoinRowSetImpl class in this way does the trick. initJOIN methot rebuild as follows (works for my purpose):

    /**
     * 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);
        }
    }