0001: /*
0002: * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS HEADER.
0003: *
0004: * Copyright 1997-2007 Sun Microsystems, Inc. All rights reserved.
0005: *
0006: * The contents of this file are subject to the terms of either the GNU
0007: * General Public License Version 2 only ("GPL") or the Common
0008: * Development and Distribution License("CDDL") (collectively, the
0009: * "License"). You may not use this file except in compliance with the
0010: * License. You can obtain a copy of the License at
0011: * http://www.netbeans.org/cddl-gplv2.html
0012: * or nbbuild/licenses/CDDL-GPL-2-CP. See the License for the
0013: * specific language governing permissions and limitations under the
0014: * License. When distributing the software, include this License Header
0015: * Notice in each file and include the License file at
0016: * nbbuild/licenses/CDDL-GPL-2-CP. Sun designates this
0017: * particular file as subject to the "Classpath" exception as provided
0018: * by Sun in the GPL Version 2 section of the License file that
0019: * accompanied this code. If applicable, add the following below the
0020: * License Header, with the fields enclosed by brackets [] replaced by
0021: * your own identifying information:
0022: * "Portions Copyrighted [year] [name of copyright owner]"
0023: *
0024: * Contributor(s):
0025: *
0026: * The Original Software is NetBeans. The Initial Developer of the Original
0027: * Software is Sun Microsystems, Inc. Portions Copyright 1997-2007 Sun
0028: * Microsystems, Inc. All Rights Reserved.
0029: *
0030: * If you wish your version of this file to be governed by only the CDDL
0031: * or only the GPL Version 2, indicate your decision by adding
0032: * "[Contributor] elects to include this software in this distribution
0033: * under the [CDDL or GPL Version 2] license." If you do not indicate a
0034: * single choice of license, a recipient has the option to distribute
0035: * your version of this file under either the CDDL, the GPL Version 2 or
0036: * to extend the choice of license to its licensees as provided above.
0037: * However, if you add GPL Version 2 code and therefore, elected the GPL
0038: * Version 2 license, then the option applies only if the new code is
0039: * made subject to such option by the copyright holder.
0040: */
0041:
0042: package com.sun.sql.rowset.internal;
0043:
0044: import java.sql.*;
0045: import javax.sql.*;
0046: import java.util.*;
0047: import java.io.*;
0048:
0049: import javax.sql.rowset.*;
0050: import javax.sql.rowset.spi.*;
0051:
0052: import com.sun.sql.rowset.CachedRowSetX;
0053: import com.sun.sql.rowset.CachedRowSetXImpl;
0054: import com.sun.sql.rowset.RowSetMetaDataXImpl;
0055:
0056: import java.text.MessageFormat;
0057: import java.util.Locale;
0058: import java.util.ResourceBundle;
0059:
0060: /**
0061: * An implementation of RowSetWriter to work with CachedRowSetX rowsets.
0062: *
0063: * Based on the Sun reference implementation of CachedRowSetWriter.
0064: *
0065: * @see javax.sql.rowset.spi.SyncProvider
0066: * @see javax.sql.rowset.spi.SyncFactory
0067: * @see javax.sql.rowset.spi.SyncFactoryException
0068: */
0069: public class CachedRowSetXWriter implements TransactionalWriter,
0070: Serializable {
0071:
0072: private static ResourceBundle rb = ResourceBundle.getBundle(
0073: "com.sun.sql.rowset.internal.Bundle", Locale.getDefault()); // NOI18N
0074:
0075: /**
0076: * The <code>Connection</code> object that this writer will use to make a
0077: * connection to the data source to which it will write data.
0078: *
0079: * @serial
0080: */
0081: private Connection con;
0082:
0083: /**
0084: * The SQL <code>SELECT</code> command that this writer will call
0085: * internally. The method <code>initSQLStatements</code> builds this
0086: * command by supplying the words "SELECT" and "FROM," and using
0087: * metadata to get the table name and column names .
0088: *
0089: * @serial
0090: */
0091: private String selectCmd;
0092:
0093: /**
0094: * The SQL <code>UPDATE</code> command that this writer will call
0095: * internally to write data to the rowset's underlying data source.
0096: * The method <code>initSQLStatements</code> builds this <code>String</code>
0097: * object.
0098: *
0099: * @serial
0100: */
0101: private String updateCmd;
0102:
0103: /**
0104: * The SQL <code>WHERE</code> clause the writer will use for update
0105: * statements in the <code>PreparedStatement</code> object
0106: * it sends to the underlying data source.
0107: *
0108: * @serial
0109: */
0110: private String updateWhere;
0111:
0112: /**
0113: * The SQL <code>DELETE</code> command that this writer will call
0114: * internally to delete a row in the rowset's underlying data source.
0115: *
0116: * @serial
0117: */
0118: private String deleteCmd;
0119:
0120: /**
0121: * The SQL <code>WHERE</code> clause the writer will use for delete
0122: * statements in the <code>PreparedStatement</code> object
0123: * it sends to the underlying data source.
0124: *
0125: * @serial
0126: */
0127: private String deleteWhere;
0128:
0129: /**
0130: * The SQL <code>INSERT INTO</code> command that this writer will internally use
0131: * to insert data into the rowset's underlying data source. The method
0132: * <code>initSQLStatements</code> builds this command with a question
0133: * mark parameter placeholder for each column in the rowset.
0134: *
0135: * @serial
0136: */
0137: private String insertCmd;
0138:
0139: /**
0140: * The columns to include in an insert. Only the columns from one
0141: * table can be inserted. This list is computed when the insertCmd
0142: * is built.
0143: *
0144: * @serial
0145: */
0146: private List insertableColumnsInThisTable;
0147:
0148: /**
0149: * The columns to include in an update. Only the columns from one
0150: * table can be updated. This list is computed when the updateCmd
0151: * is built.
0152: *
0153: * *NOTE*: The values are 1 based Integers!
0154: *
0155: * @serial
0156: */
0157: private List updatableColumnsInThisTable;
0158:
0159: /**
0160: * The columns to include in a where clause. This list is computed when the
0161: * sql commands are bullt.
0162: *
0163: * *NOTE*: The values are 1 based Integers!
0164: *
0165: * *ALSO NOTE*: We could have separate lists for selectColumns and whereColumns
0166: * At present, it is not known that this is necessary and, for now,
0167: * we won't add this in the interests of not adding more complexity
0168: * and not adding something that might not work. If we did add this,
0169: * we would compute the selectColumnsInThisTable list and then pare
0170: * that list down (by calling isSearchable on the column #), to get
0171: * the whereColumnsInThisTable list. If isSearchable is not reliable,
0172: * we might need to also add a searchableColumns boolean[] property on
0173: * the rowset.
0174: *
0175: * @serial
0176: */
0177: private List whereColumnsInThisTable;
0178:
0179: /**
0180: * Real column names.
0181: *
0182: * @serial
0183: */
0184: private String[] realColumnNames;
0185:
0186: /**
0187: * An array containing the column numbers of the columns that are
0188: * needed to uniquely identify a row in the <code>CachedRowSet</code> object
0189: * for which this <code>CachedRowSetXWriter</code> object is the writer.
0190: *
0191: * NOTE: keyColumns in CachedRowSetXImpl are ignored and they are computed
0192: * from whereColumnsInThisTable
0193: *
0194: * @serial
0195: */
0196: private int[] keyCols;
0197:
0198: /**
0199: * An array of the parameters that should be used to set the parameter
0200: * placeholders in a <code>PreparedStatement</code> object that this
0201: * writer will execute.
0202: *
0203: * @serial
0204: */
0205: private Object[] params;
0206:
0207: /**
0208: * The <code>CachedRowSetXReader</code> object that has been
0209: * set as the reader for the <code>CachedRowSet</code> object
0210: * for which this <code>CachedRowSetXWriter</code> object is the writer.
0211: *
0212: * @serial
0213: */
0214: private CachedRowSetXReader reader;
0215:
0216: /**
0217: * The <code>ResultSetMetaData</code> object that contains information
0218: * about the columns in the <code>CachedRowSet</code> object
0219: * for which this <code>CachedRowSetXWriter</code> object is the writer.
0220: *
0221: * @serial
0222: */
0223: private ResultSetMetaData callerMd;
0224:
0225: /**
0226: * The number of columns in the <code>CachedRowSet</code> object
0227: * for which this <code>CachedRowSetXWriter</code> object is the writer.
0228: *
0229: * @serial
0230: */
0231: private int callerColumnCount;
0232:
0233: /**
0234: * This <code>CachedRowSet<code> will hold the conflicting values
0235: * retrieved from the db and hold it.
0236: */
0237: private CachedRowSetXImpl crsResolve;
0238:
0239: /**
0240: * This <code>ArrayList<code> will hold the values of SyncResolver.*
0241: */
0242: private ArrayList status;
0243:
0244: /**
0245: * This <code>ArrayList<code> will hold the SQLExceptions of SyncResolver.*
0246: */
0247: private ArrayList exceptions;
0248:
0249: /**
0250: * This will check whether the same field value has changed both
0251: * in database and CachedRowSet.
0252: */
0253: private int iChangedValsInDbAndCRS;
0254:
0255: /**
0256: * This will hold the number of cols for which the values have
0257: * changed only in database.
0258: */
0259: private int iChangedValsinDbOnly;
0260:
0261: /**
0262: * Propagates changes in the given <code>RowSet</code> object
0263: * back to its underlying data source and returns <code>true</code>
0264: * if successful. The writer will check to see if
0265: * the data in the pre-modified rowset (the original values) differ
0266: * from the data in the underlying data source. If data in the data
0267: * source has been modified by someone else, there is a conflict,
0268: * and in that case, the writer will not write to the data source.
0269: * In other words, the writer uses an optimistic concurrency algorithm:
0270: * It checks for conflicts before making changes rather than restricting
0271: * access for concurrent users.
0272: * <P>
0273: * This method is called by the rowset internally when
0274: * the application invokes the method <code>acceptChanges</code>.
0275: * The <code>writeData</code> method in turn calls private methods that
0276: * it defines internally.
0277: * The following is a general summary of what the method
0278: * <code>writeData</code> does, much of which is accomplished
0279: * through calls to its own internal methods.
0280: * <OL>
0281: * <LI>Creates a <code>CachedRowSet</code> object from the given
0282: * <code>RowSet</code> object
0283: * <LI>Makes a connection with the data source
0284: * <UL>
0285: * <LI>Disables autocommit mode if it is not already disabled
0286: * <LI>Sets the transaction isolation level to that of the rowset
0287: * </UL>
0288: * <LI>Checks to see if the reader has read new data since the writer
0289: * was last called and, if so, calls the method
0290: * <code>initSQLStatements</code> to initialize new SQL statements
0291: * <UL>
0292: * <LI>Builds new <code>SELECT</code>, <code>UPDATE</code>,
0293: * <code>INSERT</code>, and <code>DELETE</code> statements
0294: * <LI>Uses the <code>CachedRowSet</code> object's metadata to
0295: * determine the table name, column names, and the columns
0296: * that make up the primary key
0297: * </UL>
0298: * <LI>When there is no conflict, propagates changes made to the
0299: * <code>CachedRowSet</code> object back to its underlying data source
0300: * <UL>
0301: * <LI>Iterates through each row of the <code>CachedRowSet</code> object
0302: * to determine whether it has been updated, inserted, or deleted
0303: * <LI>If the corresponding row in the data source has not been changed
0304: * since the rowset last read its
0305: * values, the writer will use the appropriate command to update,
0306: * insert, or delete the row
0307: * <LI>If any data in the data source does not match the original values
0308: * for the <code>CachedRowSet</code> object, the writer will roll
0309: * back any changes it has made to the row in the data source.
0310: * </UL>
0311: * </OL>
0312: *
0313: * @return <code>true</code> if changes to the rowset were successfully
0314: * written to the rowset's underlying data source;
0315: * <code>false</code> otherwise
0316: */
0317: public boolean writeData(RowSetInternal caller) throws SQLException {
0318: boolean conflict = false;
0319: boolean showDel = false;
0320: PreparedStatement pstmtIns = null;
0321: iChangedValsInDbAndCRS = 0;
0322: iChangedValsinDbOnly = 0;
0323:
0324: // We assume caller is a CachedRowSet
0325: CachedRowSetXImpl crs = (CachedRowSetXImpl) caller;
0326: // crsResolve = new CachedRowSetXImpl();
0327: this .crsResolve = CachedRowSetXImpl.createInternalUseInstance();
0328:
0329: // The reader is registered with the writer at design time.
0330: // This is not required, in general. The reader has logic
0331: // to get a JDBC connection, so call it.
0332:
0333: con = reader.connect(caller);
0334:
0335: if (con == null) {
0336: throw new SQLException(rb
0337: .getString("UNABLE_TO_GET_CONNECTION")); //NOI18N
0338: }
0339: if (con.getAutoCommit() == true) {
0340: con.setAutoCommit(false);
0341: }
0342:
0343: con.setTransactionIsolation(crs.getTransactionIsolation());
0344:
0345: initSQLStatements(crs);
0346: int iColCount;
0347:
0348: RowSetMetaDataXImpl rsmdWrite = (RowSetMetaDataXImpl) crs
0349: .getMetaData();
0350: RowSetMetaDataXImpl rsmdResolv = new RowSetMetaDataXImpl();
0351:
0352: iColCount = rsmdWrite.getColumnCount();
0353: int sz = crs.size() + 1;
0354: status = new ArrayList(sz);
0355: exceptions = new ArrayList(sz);
0356:
0357: status.add(0, null);
0358: exceptions.add(0, null);
0359: rsmdResolv.setColumnCount(iColCount);
0360:
0361: boolean[] insertableColumns = crs.getInsertableColumns();
0362: boolean[] updatableColumns = crs.getUpdatableColumns();
0363: for (int i = 1; i <= iColCount; i++) {
0364: rsmdResolv.setColumnType(i, rsmdWrite.getColumnType(i));
0365: rsmdResolv.setColumnName(i, rsmdWrite.getColumnName(i));
0366: rsmdResolv.setNullable(i,
0367: ResultSetMetaData.columnNullableUnknown);
0368: rsmdResolv.setColumnClassName(i, rsmdWrite
0369: .getColumnClassName(i));
0370: /*
0371: * We face a dilemna here. The RI always says columns are writable.
0372: * We'd like to give a better answer; but since we allow joins that
0373: * would result in the database saying all columns are readonly, we
0374: * can't just hand back the information the driver gives about the
0375: * resultset. As such, we would really like to compute if the column is
0376: * writable in much the same way that CachedRowSetXWriter computes this
0377: * information. For now, we'll return true unless the user has provided
0378: * information otherwise in the updatable columns on the rowset.
0379: *
0380: * Actually, even this will cause problems as there is a difference between
0381: * insertable and updatable. We want to allow new rows to specify insertable
0382: * columns that are not updatable. So we will actually honor insertable if it
0383: * is present. If not, we'll use updatable if present. If not, we'll say the
0384: * column is writable.
0385: */
0386: if (insertableColumns != null
0387: && insertableColumns.length >= i) {
0388: rsmdResolv.setWritable(i, insertableColumns[i - 1]);
0389: rsmdResolv.setDefinitelyWritable(i,
0390: insertableColumns[i - 1]);
0391: rsmdResolv.setReadOnly(i, !insertableColumns[i - 1]);
0392: } else if (updatableColumns != null
0393: && updatableColumns.length >= i) {
0394: rsmdResolv.setWritable(i, updatableColumns[i - 1]);
0395: rsmdResolv.setDefinitelyWritable(i,
0396: updatableColumns[i - 1]);
0397: rsmdResolv.setReadOnly(i, !updatableColumns[i - 1]);
0398: } else {
0399: rsmdResolv.setWritable(i, true);
0400: rsmdResolv.setDefinitelyWritable(i, false); // We don't know for sure
0401: rsmdResolv.setReadOnly(i, false);
0402: }
0403: }
0404: this .crsResolve.setMetaData(rsmdResolv);
0405:
0406: // moved outside the insert inner loop
0407: if (crs.getPrintStatements()) {
0408: System.out.println(insertCmd);
0409: }
0410: pstmtIns = con.prepareStatement(insertCmd);
0411:
0412: if (callerColumnCount < 1) {
0413: // No data, so return success.
0414: // moved outside the insert inner loop
0415: if (crs.getPrintStatements()) {
0416: System.out
0417: .println("No columns found (callerColumnCount==0).");
0418: }
0419: if (reader.getCloseConnection() == true) {
0420: try {
0421: if (!con.getAutoCommit()) {
0422: con.rollback();
0423: }
0424: } catch (Exception dummy) {
0425: /*
0426: * not an error condition, we're closing anyway, but
0427: * we'd like to clean up any locks if we can since
0428: * it is not clear the connection pool will clean
0429: * these connections in a timely manner
0430: */
0431: }
0432: con.close();
0433: con = null;
0434: }
0435: return true;
0436: }
0437: // We need to see rows marked for deletion.
0438: showDel = crs.getShowDeleted();
0439: crs.setShowDeleted(true);
0440:
0441: // Look at all the rows.
0442: crs.beforeFirst();
0443:
0444: int rows = 1;
0445: while (crs.next()) {
0446: // System.out.println("row is 1:"+rows);
0447: if (crs.rowDeleted()) {
0448: // The row has been deleted.
0449: if (crs.rowInserted()) {
0450: // Special case: row was inserted and then deleted
0451: status.add(rows, new Integer(
0452: SyncResolver.NO_ROW_CONFLICT));
0453: exceptions.add(rows, null);
0454: } else {
0455: try {
0456: deleteOriginalRow(crs, this .crsResolve);
0457: status.add(rows, new Integer(
0458: SyncResolver.NO_ROW_CONFLICT));
0459: exceptions.add(rows, null);
0460: } catch (SQLException sqle) {
0461: status.add(rows, new Integer(
0462: SyncResolver.DELETE_ROW_CONFLICT));
0463: exceptions.add(rows, sqle);
0464: }
0465: }
0466: } else if (crs.rowInserted()) {
0467: // The row has been inserted.
0468: try {
0469: insertNewRow(crs, pstmtIns, this .crsResolve);
0470: // insert happened without any occurrence of conflicts
0471: // so update status accordingly
0472: status.add(rows, new Integer(
0473: SyncResolver.NO_ROW_CONFLICT));
0474: exceptions.add(rows, null);
0475: } catch (SQLException sqle) {
0476: status.add(rows, new Integer(
0477: SyncResolver.INSERT_ROW_CONFLICT));
0478: exceptions.add(rows, sqle);
0479: }
0480: } else if (crs.rowUpdated()) {
0481: // The row has been updated.
0482: try {
0483: updateOriginalRow(crs);
0484: status.add(rows, new Integer(
0485: SyncResolver.NO_ROW_CONFLICT));
0486: exceptions.add(rows, null);
0487: } catch (SQLException sqle) {
0488: status.add(rows, new Integer(
0489: SyncResolver.UPDATE_ROW_CONFLICT));
0490: exceptions.add(rows, sqle);
0491: }
0492: } else {
0493: /** The row is neither of inserted, updated or deleted.
0494: * So set nulls in the this.crsResolve for this row,
0495: * as nothing is to be done for such rows.
0496: * Also note that if such a row has been changed in database
0497: * and we have not changed(inserted, updated or deleted)
0498: * that is fine.
0499: **/
0500: int icolCount = crs.getMetaData().getColumnCount();
0501: status.add(rows, new Integer(
0502: SyncResolver.NO_ROW_CONFLICT));
0503: exceptions.add(rows, null);
0504:
0505: this .crsResolve.moveToInsertRow();
0506: for (int cols = 0; cols < iColCount; cols++) {
0507: this .crsResolve.updateNull(cols + 1);
0508: } //end for
0509:
0510: this .crsResolve.insertRow();
0511: this .crsResolve.moveToCurrentRow();
0512:
0513: } //end if
0514: rows++;
0515: } //end while
0516:
0517: // close the insert statement
0518: try {
0519: pstmtIns.close();
0520: } catch (SQLException e) {
0521: System.err.println(MessageFormat.format(rb
0522: .getString("NON_FATAL_ERROR"), //NOI18N
0523: new Object[] { e }));
0524: //System.err.println("Non-Fatal error stackTrace follows:");
0525: //e.printStackTrace();
0526: }
0527: // reset
0528: crs.setShowDeleted(showDel);
0529:
0530: boolean boolConf = false;
0531: int noOfConflicts = 0;
0532: for (int j = 1; j < status.size(); j++) {
0533: // ignore status for index = 0 which is set to null
0534: if (!((status.get(j)).equals(new Integer(
0535: SyncResolver.NO_ROW_CONFLICT)))) {
0536: // there is at least one conflict which needs to be resolved
0537: boolConf = true;
0538: noOfConflicts++;
0539: //break;
0540: }
0541: }
0542:
0543: crs.beforeFirst();
0544: this .crsResolve.beforeFirst();
0545:
0546: if (boolConf) {
0547:
0548: SyncProviderException spe = new SyncProviderException(
0549: MessageFormat
0550: .format(rb.getString("NO_OF_CONFLICTS"), //NOI18N
0551: new Object[] { new Integer(
0552: noOfConflicts) }));
0553: spe.setSyncResolver(new SyncResolverXImpl());
0554: //SyncResolver syncRes = spe.getSyncResolver();
0555:
0556: SyncResolverXImpl syncResImpl = (SyncResolverXImpl) spe
0557: .getSyncResolver();
0558:
0559: syncResImpl.setCachedRowSet(crs);
0560: syncResImpl.setCachedRowSetResolver(this .crsResolve);
0561:
0562: syncResImpl.setStatus(status);
0563: syncResImpl.setExceptions(exceptions);
0564: syncResImpl.setCachedRowSetXWriter(this );
0565:
0566: throw spe;
0567: } else {
0568: return true;
0569: }
0570: } //end writeData
0571:
0572: /**
0573: * Updates the given <code>CachedRowSet</code> object's underlying data
0574: * source so that updates to the rowset are reflected in the original
0575: * data source, and returns <code>false</code> if the update was successful.
0576: * A return value of <code>true</code> indicates that there is a conflict,
0577: * meaning that a value updated in the rowset has already been changed by
0578: * someone else in the underlying data source. A conflict can also exist
0579: * if, for example, more than one row in the data source would be affected
0580: * by the update or if no rows would be affected. In any case, if there is
0581: * a conflict, this method does not update the underlying data source.
0582: * <P>
0583: * This method is called internally by the method <code>writeData</code>
0584: * if a row in the <code>CachedRowSet</code> object for which this
0585: * <code>CachedRowSetXWriter</code> object is the writer has been updated.
0586: *
0587: * @return <code>false</code> if the update to the underlying data source is
0588: * successful; <code>true</code> otherwise
0589: * @throws SQLException if a database access error occurs
0590: */
0591: private void updateOriginalRow(CachedRowSet crs)
0592: throws SQLException {
0593: PreparedStatement pstmt;
0594: int i = 0;
0595: int idx = 0;
0596:
0597: // Select the row from the database.
0598: ResultSet origVals = crs.getOriginalRow();
0599: origVals.next();
0600:
0601: try {
0602: updateWhere = buildWhereClause(updateWhere, origVals);
0603:
0604: if (((CachedRowSetX) crs).getPrintStatements()) {
0605: System.out.println(selectCmd + updateWhere);
0606: }
0607: pstmt = con.prepareStatement(selectCmd + updateWhere,
0608: ResultSet.TYPE_SCROLL_SENSITIVE,
0609: ResultSet.CONCUR_READ_ONLY);
0610:
0611: StringBuffer msg = null;
0612: for (i = 0; i < keyCols.length; i++) {
0613: if (((CachedRowSetX) crs).getPrintStatements()) {
0614: if (msg == null)
0615: msg = new StringBuffer(100);
0616: msg.append(" Paramm[" + (idx + 1) + "]=(");
0617: if (params[i] != null) {
0618: msg.append(params[i].getClass().getName() + ","
0619: + params[i].toString() + ")");
0620: } else {
0621: msg.append("null)");
0622: }
0623: }
0624: if (params[i] != null) {
0625: pstmt.setObject(++idx, params[i]);
0626: } else {
0627: continue;
0628: }
0629: }
0630: if (msg != null)
0631: System.out.println(msg);
0632:
0633: // drivers may not support the following, so ignore exceptions.
0634: try {
0635: pstmt.setMaxRows(2); // we only care if there's not 1!
0636: } catch (Exception ex) {
0637: ;
0638: }
0639: try {
0640: pstmt.setMaxFieldSize(crs.getMaxFieldSize());
0641: } catch (Exception ex) {
0642: ;
0643: }
0644: try {
0645: pstmt.setEscapeProcessing(crs.getEscapeProcessing());
0646: } catch (Exception ex) {
0647: ;
0648: }
0649: try {
0650: pstmt.setQueryTimeout(crs.getQueryTimeout());
0651: } catch (Exception ex) {
0652: ;
0653: }
0654:
0655: ResultSet rs = null;
0656: if (((CachedRowSetX) crs).getPrintStatements()) {
0657: System.out
0658: .println("Writer: executing pre-update SELECT");
0659: }
0660: rs = pstmt.executeQuery();
0661: if (rs.next() == true) {
0662:
0663: if (rs.next()) {
0664: /** More than one row conflict.
0665: * If rs has only one row we are able to
0666: * uniquely identify the row where update
0667: * have to happen else if more than one
0668: * row implies we cannot uniquely identify the row
0669: * where we have to do updates.
0670: * crs.setKeyColumns needs to be set to
0671: * come out of this situation.
0672: */
0673: if (((CachedRowSetX) crs).getPrintStatements()) {
0674: System.out
0675: .println(" Writer: pre-update SELECT returned >1 row: BAD");
0676: }
0677:
0678: return;
0679: }
0680:
0681: // don't close the rs
0682: // we require the record in rs to be used.
0683: // rs.close();
0684: // pstmt.close();
0685: rs.first();
0686:
0687: // how many fields need to be updated
0688: int colsNotChanged = 0;
0689: Vector cols = new Vector();
0690: String updateExec = new String(updateCmd);
0691: Object orig;
0692: Object curr;
0693: Object rsval;
0694: boolean boolNull = true;
0695: Object objVal = null;
0696:
0697: // There's only one row and the cursor
0698: // needs to be on that row.
0699:
0700: boolean first = true;
0701:
0702: this .crsResolve.moveToInsertRow();
0703:
0704: for (i = 1; i <= callerColumnCount; i++) {
0705: if (!updatableColumnsInThisTable
0706: .contains(new Integer(i))) {
0707: colsNotChanged++;
0708: continue;
0709: }
0710: orig = origVals.getObject(i);
0711: curr = crs.getObject(i);
0712: // The object is NOT at position i, it is at the whatever place it was added
0713: // in whereColumnsInThisTable!!!
0714: rsval = rs.getObject(whereColumnsInThisTable
0715: .indexOf(new Integer(i)) + 1);
0716: // reset boolNull if it had been set
0717: boolNull = true;
0718:
0719: if ((rsval == null && orig != null)
0720: || (rsval != null && !rsval.equals(orig))) {
0721: // value in db has changed
0722: // don't proceed with synchronization
0723: // get the value in db and pass it to the resolver.
0724:
0725: iChangedValsinDbOnly++;
0726: // Set the boolNull to false,
0727: // in order to set the actual value;
0728: boolNull = false;
0729: objVal = rsval;
0730: } else if ((orig == null || curr == null)) {
0731: if (first == false) {
0732: updateExec += ", "; //NOI18N
0733: }
0734: updateExec += crs.getMetaData()
0735: .getColumnName(i);
0736: cols.add(new Integer(i));
0737: updateExec += " = ? "; //NOI18N
0738: first = false;
0739:
0740: } else if ((orig == null && curr == null)
0741: || (orig != null && orig.equals(curr))) {
0742: colsNotChanged++;
0743: //nothing to update in this case since values are equal
0744:
0745: } else if ((orig == null && curr != null)
0746: || orig.equals(curr) == false) {
0747: // When values from db and values in CachedRowSet are not equal,
0748: // if db value is same as before updation for each col in
0749: // the row before fetching into CachedRowSet,
0750: // only then we go ahead with updation, else we
0751: // throw SyncProviderException.
0752:
0753: // if value has changed in db after fetching from db
0754: // for some cols of the row and at the same time, some other cols
0755: // have changed in CachedRowSet, no synchronization happens
0756:
0757: // Synchronization happens only when data when fetching is
0758: // same or at most has changed in cachedrowset
0759:
0760: // check orig value with what is there in crs for a column
0761: // before updation in crs.
0762:
0763: if (crs.columnUpdated(i)) {
0764: if (rsval.equals(orig)) {
0765: // At this point we are sure that
0766: // the value updated in crs was from
0767: // what is in db now and has not changed
0768: if (first == false) {
0769: updateExec += ", "; //NOI18N
0770: }
0771: updateExec += crs.getMetaData()
0772: .getColumnName(i);
0773: cols.add(new Integer(i));
0774: updateExec += " = ? "; //NOI18N
0775: first = false;
0776: } else {
0777: // Here the value has changed in the db after
0778: // data was fetched
0779: // Plus store this row from CachedRowSet and keep it
0780: // in a new CachedRowSet
0781: boolNull = false;
0782: objVal = rsval;
0783: iChangedValsInDbAndCRS++;
0784: }
0785: }
0786: }
0787:
0788: if (!boolNull) {
0789: this .crsResolve.updateObject(i, objVal);
0790: } else {
0791: this .crsResolve.updateNull(i);
0792: }
0793: } //end for
0794:
0795: /**
0796: * if nothing has changed return now - this can happen
0797: * if column is updated to the same value.
0798: * if colsNotChanged == callerColumnCount implies we are updating
0799: * the database with ALL COLUMNS HAVING SAME VALUES,
0800: * so skip going to database, else do as usual.
0801: **/
0802: if ((first == false && cols.size() == 0)
0803: || colsNotChanged == callerColumnCount) {
0804: if (((CachedRowSetX) crs).getPrintStatements()) {
0805: System.out
0806: .println("Writer: no columns changed, nothing to update"); //NOI18N
0807: }
0808: return;
0809: }
0810:
0811: if (iChangedValsInDbAndCRS != 0
0812: || iChangedValsinDbOnly != 0) {
0813: throw new SQLException(rb
0814: .getString("VALUES_CHANGED_IN_DB")); //NOI18N
0815: }
0816:
0817: updateExec += updateWhere;
0818: if (((CachedRowSetX) crs).getPrintStatements()) {
0819: System.out.println("Writer: " + updateExec);
0820: }
0821: pstmt = con.prepareStatement(updateExec);
0822:
0823: // Comments needed here
0824: msg = null; // for logging (optional)
0825: for (i = 0; i < cols.size(); i++) {
0826: if (updatableColumnsInThisTable
0827: .contains((Integer) cols.get(i))) {
0828:
0829: Object obj = crs.getObject(((Integer) cols
0830: .get(i)).intValue());
0831: if (((CachedRowSetX) crs).getPrintStatements()) {
0832: if (msg == null)
0833: msg = new StringBuffer(100);
0834: msg.append(" UpdateCol[" + (i + 1) + "]=(");
0835: if (obj != null) {
0836: msg.append(obj.getClass().getName()
0837: + "," + obj.toString() + ")");
0838: } else {
0839: msg
0840: .append("null:"
0841: + crs
0842: .getMetaData()
0843: .getColumnType(
0844: ((Integer) cols
0845: .get(i))
0846: .intValue())
0847: + ")");
0848: }
0849: }
0850: if (obj != null)
0851: pstmt.setObject(i + 1, obj);
0852: else {
0853: pstmt.setNull(i + 1, crs.getMetaData()
0854: .getColumnType(
0855: ((Integer) cols.get(i))
0856: .intValue()));
0857: }
0858: }
0859: }
0860: if (msg != null)
0861: System.out.println(msg); // only if ( getPrintStatements() )
0862: idx = i;
0863:
0864: for (i = 0; i < keyCols.length; i++) {
0865: if (params[i] != null) {
0866: pstmt.setObject(++idx, params[i]);
0867: } else {
0868: continue;
0869: }
0870: }
0871: if (((CachedRowSetX) crs).getPrintStatements()) {
0872: System.out.println("Writer: executing update() ");
0873: }
0874: i = pstmt.executeUpdate();
0875:
0876: this .crsResolve.insertRow();
0877: this .crsResolve.moveToCurrentRow();
0878:
0879: /**
0880: * i should be equal to 1(row count), because we update
0881: * one row(returned as row count) at a time, if all goes well.
0882: * if 1 != 1, this implies we have not been able to
0883: * do updations properly i.e there is a conflict in database
0884: * versus what is in CachedRowSet for this particular row.
0885: **/
0886:
0887: return;
0888:
0889: } else {
0890: /**
0891: * Cursor will be here, if the ResultSet may not return even a single row
0892: * i.e. we can't find the row where to update because it has been updated
0893: * or deleted from the db.
0894: * Present the whole row as null to user, to force null to be sync'ed
0895: * and hence nothing to be synced.
0896: **/
0897: if (((CachedRowSetX) crs).getPrintStatements()) {
0898: System.out
0899: .println(" Writer: pre-update SELECT returned 0 rows");
0900: }
0901: throw new SQLException(rb
0902: .getString("ATTEMPT_TO_UPDATE_ROW_UP_OR_DEL")); //NOI18N
0903: }
0904: } catch (SQLException ex) {
0905: //System.out.println("updateOriginalRow: caught exception: " + ex);
0906: // if executeUpdate fails it will come here,
0907: // update crsResolve with null rows
0908: this .crsResolve.moveToInsertRow();
0909:
0910: for (i = 1; i <= callerColumnCount; i++) {
0911: this .crsResolve.updateNull(i);
0912: }
0913:
0914: this .crsResolve.insertRow();
0915: this .crsResolve.moveToCurrentRow();
0916:
0917: throw ex;
0918: }
0919: }
0920:
0921: /**
0922: * Inserts a row that has been inserted into the given
0923: * <code>CachedRowSet</code> object into the data source from which
0924: * the rowset is derived, returning <code>false</code> if the insertion
0925: * was successful.
0926: *
0927: * @param crs the <code>CachedRowSet</code> object that has had a row inserted
0928: * and to whose underlying data source the row will be inserted
0929: * @param pstmt the <code>PreparedStatement</code> object that will be used
0930: * to execute the insertion
0931: * @return <code>false</code> to indicate that the insertion was successful;
0932: * <code>true</code> otherwise
0933: * @throws SQLException if a database access error occurs
0934: */
0935: private void insertNewRow(CachedRowSet crs,
0936: PreparedStatement pstmt, CachedRowSetXImpl crsRes)
0937: throws SQLException {
0938: int i = 0;
0939: int icolCount = 0;
0940:
0941: try {
0942: icolCount = crs.getMetaData().getColumnCount();
0943:
0944: int pstmtColIdx = 1;
0945: StringBuffer msg = null;
0946: for (i = 1; i <= icolCount; i++) {
0947: if (insertableColumnsInThisTable
0948: .contains(new Integer(i))) {
0949: Object obj = crs.getObject(i);
0950: if (((CachedRowSetX) crs).getPrintStatements()) {
0951: if (msg == null)
0952: msg = new StringBuffer(", params: ");
0953: msg.append(" Col[" + (i) + "]=(");
0954: if (obj != null) {
0955: msg.append(obj.getClass().getName() + ","
0956: + obj.toString() + ")");
0957: } else {
0958: msg.append("null:"
0959: + crs.getMetaData()
0960: .getColumnType(i) + ")");
0961: }
0962: }
0963: if (obj != null) {
0964: pstmt.setObject(pstmtColIdx++, obj);
0965: } else {
0966: pstmt.setNull(pstmtColIdx++, crs.getMetaData()
0967: .getColumnType(i));
0968: }
0969: }
0970: }
0971:
0972: if (((CachedRowSetX) crs).getPrintStatements()) {
0973: System.out.println("Writer: executing insert "
0974: + (msg == null ? "." : msg.toString()));
0975: }
0976: i = pstmt.executeUpdate();
0977:
0978: this .crsResolve.moveToInsertRow();
0979: this .crsResolve.insertRow();
0980: this .crsResolve.moveToCurrentRow();
0981:
0982: } catch (SQLException ex) {
0983: //System.out.println("InsertNewRow: caught exception: " + ex);
0984: /**
0985: * Cursor will come here if executeUpdate fails.
0986: * There can be many reasons why the insertion failed,
0987: * one can be violation of primary key.
0988: * Hence we cannot exactly identify why the insertion failed
0989: * Present the current row as a null row to the user.
0990: **/
0991: this .crsResolve.moveToInsertRow();
0992: for (i = 1; i <= icolCount; i++) {
0993: this .crsResolve.updateNull(i);
0994: }
0995: this .crsResolve.insertRow();
0996: this .crsResolve.moveToCurrentRow();
0997:
0998: throw ex;
0999: }
1000: }
1001:
1002: /**
1003: * Deletes the row in the underlying data source that corresponds to
1004: * a row that has been deleted in the given <code> CachedRowSet</code> object
1005: * and returns <code>false</code> if the deletion was successful.
1006: * <P>
1007: * This method is called internally by this writer's <code>writeData</code>
1008: * method when a row in the rowset has been deleted. The values in the
1009: * deleted row are the same as those that are stored in the original row
1010: * of the given <code>CachedRowSet</code> object. If the values in the
1011: * original row differ from the row in the underlying data source, the row
1012: * in the data source is not deleted, and <code>deleteOriginalRow</code>
1013: * returns <code>true</code> to indicate that there was a conflict.
1014: *
1015: *
1016: * @return <code>false</code> if the deletion was successful, which means that
1017: * there was no conflict; <code>true</code> otherwise
1018: * @throws SQLException if there was a database access error
1019: */
1020: private void deleteOriginalRow(CachedRowSet crs,
1021: CachedRowSetXImpl crsRes) throws SQLException {
1022:
1023: PreparedStatement pstmt;
1024: int i;
1025: int idx = 0;
1026: String strSelect;
1027: // Select the row from the database.
1028: ResultSet origVals = crs.getOriginalRow();
1029: origVals.next();
1030:
1031: deleteWhere = buildWhereClause(deleteWhere, origVals);
1032: if (((CachedRowSetX) crs).getPrintStatements()) {
1033: System.out.println("Writer: pre-delete select "
1034: + selectCmd + deleteWhere);
1035: }
1036: pstmt = con.prepareStatement(selectCmd + deleteWhere,
1037: ResultSet.TYPE_SCROLL_SENSITIVE,
1038: ResultSet.CONCUR_READ_ONLY);
1039:
1040: StringBuffer msg = null;
1041: for (i = 0; i < keyCols.length; i++) {
1042: if (params[i] != null) {
1043: pstmt.setObject(++idx, params[i]);
1044: if (((CachedRowSetX) crs).getPrintStatements()) {
1045: if (msg == null)
1046: msg = new StringBuffer(100);
1047: msg.append(" DeleteParam[" + (idx) + "]=(");
1048: if (params[i] != null) {
1049: msg.append(params[i].getClass().getName() + ","
1050: + params[i].toString() + ")");
1051: } else {
1052: msg.append("null");
1053: }
1054: }
1055: } else {
1056: continue;
1057: }
1058: }
1059: if (msg != null)
1060: System.out.println(msg); // log delete parameters
1061:
1062: // drivers may not support the following, so ignore exceptions.
1063: try {
1064: pstmt.setMaxRows(2); // we only care if there's not 1!
1065: } catch (Exception ex) {
1066: ;
1067: }
1068: try {
1069: pstmt.setMaxFieldSize(crs.getMaxFieldSize());
1070: } catch (Exception ex) {
1071: ;
1072: }
1073: try {
1074: pstmt.setEscapeProcessing(crs.getEscapeProcessing());
1075: } catch (Exception ex) {
1076: ;
1077: }
1078: try {
1079: pstmt.setQueryTimeout(crs.getQueryTimeout());
1080: } catch (Exception ex) {
1081: ;
1082: }
1083:
1084: if (((CachedRowSetX) crs).getPrintStatements()) {
1085: System.out.println("Writer: executing pre-delete select");
1086: }
1087: ResultSet rs = pstmt.executeQuery();
1088:
1089: if (rs.next() == true) {
1090: if (rs.next()) {
1091: // more than one row
1092: throw new SQLException(rb
1093: .getString("MORE_THAN_ONE_ROW_MATCHED_DELETE")); //NOI18N
1094: }
1095: rs.first();
1096:
1097: // Now check all the values in rs to be same in
1098: // db also before actually going ahead with deleting
1099: boolean boolChanged = false;
1100:
1101: crsRes.moveToInsertRow();
1102: int rsColIdx = 1;
1103: for (i = 1; i <= crs.getMetaData().getColumnCount(); i++) {
1104: if (whereColumnsInThisTable.contains(new Integer(i))) {
1105: if (origVals.getObject(i) == null
1106: && rs.getObject(rsColIdx) == null) {
1107: crsRes.updateNull(i);
1108: } else if ((origVals.getObject(i) == null && rs
1109: .getObject(rsColIdx) != null)
1110: || (origVals.getObject(i) != null && rs
1111: .getObject(rsColIdx) == null)
1112: || !(((origVals.getObject(i)).toString())
1113: .equals((rs.getObject(rsColIdx))
1114: .toString()))) {
1115: boolChanged = true;
1116: crsRes.updateObject(i, origVals.getObject(i));
1117: } else {
1118: crsRes.updateNull(i);
1119: }
1120: rsColIdx++;
1121: }
1122: }
1123: crsRes.insertRow();
1124: crsRes.moveToCurrentRow();
1125:
1126: if (boolChanged) {
1127: // do not delete as values in db have changed
1128: // deletion will not happen for this row from db
1129: // exit now returning true. i.e. conflict
1130: throw new SQLException(
1131: rb
1132: .getString("WILL_NOT_DELETE_AS_DB_VALS_CHANGED")); //NOI18N
1133: } else {
1134: // delete the row.
1135: // Go ahead with deleting,
1136: // don't do anything here
1137: }
1138:
1139: String cmd = deleteCmd + deleteWhere;
1140:
1141: pstmt = con.prepareStatement(cmd);
1142:
1143: idx = 0;
1144: for (i = 0; i < keyCols.length; i++) {
1145: if (params[i] != null) {
1146: pstmt.setObject(++idx, params[i]);
1147: } else {
1148: continue;
1149: }
1150: }
1151:
1152: if (((CachedRowSetX) crs).getPrintStatements()) {
1153: System.out.println("Writer: executing delete " + cmd);
1154: }
1155: if (pstmt.executeUpdate() != 1) {
1156: throw new SQLException(rb
1157: .getString("DELETED_MORE_THAN_ONE_ROW")); //NOI18N
1158: }
1159: pstmt.close();
1160: } else {
1161: // didn't find the row
1162: throw new SQLException(rb
1163: .getString("DID_NOT_FIND_ROW_TO_DELETE")); //NOI18N
1164: }
1165:
1166: }
1167:
1168: /**
1169: * Sets the reader for this writer to the given reader.
1170: *
1171: * @throws SQLException if a database access error occurs
1172: */
1173: public void setReader(CachedRowSetXReader reader)
1174: throws SQLException {
1175: this .reader = reader;
1176: }
1177:
1178: /**
1179: * Gets the reader for this writer.
1180: *
1181: * @throws SQLException if a database access error occurs
1182: */
1183: public CachedRowSetXReader getReader() throws SQLException {
1184: return reader;
1185: }
1186:
1187: /**
1188: * Composes a <code>SELECT</code>, <code>UPDATE</code>, <code>INSERT</code>,
1189: * and <code>DELETE</code> statement that can be used by this writer to
1190: * write data to the data source backing the given <code>CachedRowSet</code>
1191: * object.
1192: *
1193: * @ param caller a <code>CachedRowSet</code> object for which this
1194: * <code>CachedRowSetXWriter</code> object is the writer
1195: * @throws SQLException if a database access error occurs
1196: */
1197: private void initSQLStatements(CachedRowSet caller)
1198: throws SQLException {
1199:
1200: int i;
1201:
1202: callerMd = caller.getMetaData();
1203: callerColumnCount = callerMd.getColumnCount();
1204: if (callerColumnCount < 1)
1205: // No data, so return.
1206: return;
1207:
1208: /*
1209: * If the RowSet has a Table name we should use it.
1210: * This is really a hack to get round the fact that
1211: * a lot of the jdbc drivers can't provide the tab.
1212: */
1213: String table = caller.getTableName();
1214: if (table == null) {
1215: /*
1216: * attempt to build a table name using the info
1217: * that the driver gave us for the first column
1218: * in the source result set.
1219: */
1220: table = callerMd.getTableName(1);
1221: if (table == null || table.length() == 0) {
1222: throw new SQLException(rb
1223: .getString("CANNOT_DETERMINE_TABLE_NAME")); //NOI18N
1224: }
1225: }
1226: /*
1227: * If the rowset has a catalogName, we will use it.
1228: */
1229: String catalog = ((CachedRowSetX) caller).getCatalogName();
1230: if (catalog == null) {
1231: catalog = callerMd.getCatalogName(1);
1232: if (catalog != null && catalog.length() == 0) {
1233: catalog = null;
1234: }
1235: }
1236: /*
1237: * If the rowset has a schemaName, we will use it.
1238: */
1239: String schema = ((CachedRowSetX) caller).getSchemaName();
1240: if (schema == null) {
1241: schema = callerMd.getSchemaName(1);
1242: if (schema != null && schema.length() == 0) {
1243: schema = null;
1244: }
1245: }
1246: DatabaseMetaData dbmd = con.getMetaData();
1247:
1248: /* NOTE: first do whereColumns,
1249: * then do updataable -- same if updateable is null
1250: * then dto insertable -- same as updatable if insertable is null
1251: */
1252:
1253: // compute the real column names
1254: realColumnNames = new String[callerColumnCount];
1255: String[] callerColumnNames = ((CachedRowSetX) caller)
1256: .getColumnNames();
1257: for (i = 0; i < callerColumnCount; i++) {
1258: realColumnNames[i] = callerMd.getColumnName(i + 1);
1259: if (callerColumnNames != null
1260: && callerColumnNames.length > i
1261: && callerColumnNames[i] != null) {
1262: realColumnNames[i] = callerColumnNames[i];
1263: }
1264: }
1265:
1266: // compute the where columns for the table
1267: whereColumnsInThisTable = new ArrayList();
1268: String[] columnCatalogNames = ((CachedRowSetX) caller)
1269: .getColumnCatalogNames();
1270: String[] columnSchemaNames = ((CachedRowSetX) caller)
1271: .getColumnSchemaNames();
1272: String[] columnTableNames = ((CachedRowSetX) caller)
1273: .getColumnTableNames();
1274:
1275: /*
1276: * If the user has not set catalog, schema nor any catalog, schema, or table for an column,
1277: * then perhaps we should not be so harsh in rejecting columns that match the table.
1278: *
1279: * That is, if the user has not set any advanced properties, then lets allow the
1280: * "relaxed" column matching that existed before adding the advanced properties. Yes,
1281: * one can get into trouble this way, but that can still be fixed by setting one ore
1282: * more advanced properties. Once any of these are set, the relaxed column matching
1283: * is turned off.
1284: *
1285: * This change will allow, without the user setting any properties, updates on Oracle
1286: * databases even though it returns blank table names for all columns in a result set.
1287: */
1288: boolean relaxed = (columnCatalogNames == null
1289: && columnSchemaNames == null
1290: && columnTableNames == null
1291: && ((CachedRowSetX) caller).getCatalogName() == null && ((CachedRowSetX) caller)
1292: .getSchemaName() == null);
1293:
1294: for (i = 0; i < callerColumnCount; i++) {
1295:
1296: // attempt to match on catalog
1297: if (!matcher(i, catalog, columnCatalogNames, callerMd
1298: .getCatalogName(i + 1), relaxed)) {
1299: continue;
1300: }
1301: // attempt to match on schema
1302: if (!matcher(i, schema, columnSchemaNames, callerMd
1303: .getSchemaName(i + 1), relaxed)) {
1304: continue;
1305: }
1306: // attempt to match on table
1307: if (!matcher(i, table, columnTableNames, callerMd
1308: .getTableName(i + 1), relaxed)) {
1309: continue;
1310: }
1311: // We are part of the table (or we don't know because we have nulls)
1312: // tricky for cases where we have nulls
1313: // we don't know whether we should call isColumnPartOfTable!!!!!
1314: boolean match = true;
1315: if (columnCatalogNames == null && columnSchemaNames == null
1316: && columnTableNames == null) {
1317: // do things the old way, look up column in table
1318: match = isColumnPartOfTable(dbmd, realColumnNames[i],
1319: catalog, schema, table, callerMd
1320: .getTableName(i + 1));
1321: }
1322: if (match) {
1323: whereColumnsInThisTable.add(new Integer(i + 1));
1324: }
1325: }
1326: /*
1327: * compute columns to be updated
1328: * if caller.getUpdatableColumns() is null, just use the where columns
1329: */
1330: updatableColumnsInThisTable = new ArrayList();
1331: boolean[] callerUpdatableColumns = ((CachedRowSetX) caller)
1332: .getUpdatableColumns();
1333: for (i = 0; i < callerColumnCount; i++) {
1334: boolean match;
1335: if (callerUpdatableColumns != null
1336: && callerUpdatableColumns.length > i) {
1337: match = callerUpdatableColumns[i];
1338: } else {
1339: match = whereColumnsInThisTable.contains(new Integer(
1340: i + 1));
1341: }
1342: if (match) {
1343: updatableColumnsInThisTable.add(new Integer(i + 1));
1344: }
1345: }
1346: /*
1347: * compute columns to be inserted
1348: * if caller.getInsertableColumns() is null, just use the updatable columns
1349: */
1350: insertableColumnsInThisTable = new ArrayList();
1351: boolean[] callerInsertableColumns = ((CachedRowSetX) caller)
1352: .getInsertableColumns();
1353: for (i = 0; i < callerColumnCount; i++) {
1354: boolean match;
1355: if (callerInsertableColumns != null
1356: && callerInsertableColumns.length > i) {
1357: match = callerInsertableColumns[i];
1358: } else {
1359: match = updatableColumnsInThisTable
1360: .contains(new Integer(i + 1));
1361: }
1362: if (match) {
1363: insertableColumnsInThisTable.add(new Integer(i + 1));
1364: }
1365: }
1366: // Compose SELECT statement
1367: boolean firstTime = true;
1368: selectCmd = "SELECT "; //NOI18N
1369:
1370: /*
1371: * Compose a SELECT statement. There are three parts.
1372: * JK It is currently unclear what columns should constitute the select. For now, we'll
1373: * use the same columns as in the where clause -- that is, we might include columns
1374: * that are not updatable.
1375: */
1376: if (whereColumnsInThisTable.size() == 0) {
1377: throw new SQLException(MessageFormat.format(rb
1378: .getString("NO_COLS_IN_TABLE"), //NOI18N
1379: new Object[] { table }));
1380: }
1381: for (i = 0; i < callerColumnCount; i++) {
1382: if (whereColumnsInThisTable.contains(new Integer(i + 1))) {
1383: if (firstTime) {
1384: firstTime = false;
1385: } else {
1386: selectCmd += ", "; //NOI18N
1387: }
1388: selectCmd += realColumnNames[i];
1389: }
1390: }
1391: selectCmd += " "; //NOI18N
1392:
1393: // FROM clause.
1394: selectCmd += "FROM "
1395: + buildTableName(dbmd, catalog, schema, table); //NOI18N
1396:
1397: /*
1398: * Compose an UPDATE statement.
1399: */
1400: updateCmd = "UPDATE "
1401: + buildTableName(dbmd, catalog, schema, table); //NOI18N
1402: updateCmd += "SET "; //NOI18N
1403:
1404: /*
1405: * Compose an INSERT statement.
1406: */
1407: insertCmd = "INSERT INTO "
1408: + buildTableName(dbmd, catalog, schema, table); //NOI18N
1409: // Column list
1410: insertCmd += "("; //NOI18N
1411: firstTime = true;
1412: for (i = 0; i < callerColumnCount; i++) {
1413: if (insertableColumnsInThisTable
1414: .contains(new Integer(i + 1))) {
1415: if (firstTime) {
1416: firstTime = false;
1417: } else {
1418: insertCmd += ", "; //NOI18N
1419: }
1420: insertCmd += realColumnNames[i];
1421: }
1422: }
1423: insertCmd += ") VALUES ("; //NOI18N
1424: firstTime = true;
1425: for (i = 0; i < callerColumnCount; i++) {
1426: if (insertableColumnsInThisTable
1427: .contains(new Integer(i + 1))) {
1428: if (firstTime) {
1429: firstTime = false;
1430: } else {
1431: insertCmd += ", "; //NOI18N
1432: }
1433: insertCmd += "?"; //NOI18N
1434: }
1435: }
1436: insertCmd += ")"; //NOI18N
1437:
1438: /*
1439: * Compose a DELETE statement.
1440: */
1441: deleteCmd = "DELETE FROM "
1442: + buildTableName(dbmd, catalog, schema, table); //NOI18N
1443:
1444: /*
1445: * set the key desriptors that will be
1446: * needed to construct where clauses.
1447: */
1448: buildKeyDesc(caller);
1449: }
1450:
1451: /**
1452: * Returns a fully qualified table name built from the given catalog and
1453: * table names. The given metadata object is used to get the proper order
1454: * and separator.
1455: *
1456: * @param dbmd a <code>DatabaseMetaData</code> object that contains metadata
1457: * about this writer's <code>CachedRowSet</code> object
1458: * @param catalog a <code>String</code> object with the rowset's catalog
1459: * name
1460: * @param table a <code>String</code> object with the name of the table from
1461: * which this writer's rowset was derived
1462: * @return a <code>String</code> object with the fully qualified name of the
1463: * table from which this writer's rowset was derived
1464: * @throws SQLException if a database access error occurs
1465: */
1466: private String buildTableName(DatabaseMetaData dbmd,
1467: String catalog, String schema, String table)
1468: throws SQLException {
1469:
1470: // trim all the leading and trailing whitespaces,
1471: // white spaces can never be catalog, schema or a table name.
1472:
1473: String cmd = new String();
1474:
1475: if (catalog != null) {
1476: catalog = catalog.trim();
1477: }
1478: if (schema != null) {
1479: schema = schema.trim();
1480: }
1481: if (table != null) {
1482: table = table.trim();
1483: }
1484:
1485: if (dbmd.isCatalogAtStart() == true) {
1486: if (catalog != null && catalog.length() > 0) {
1487: cmd += catalog + dbmd.getCatalogSeparator();
1488: }
1489: if (schema != null && schema.length() > 0) {
1490: cmd += schema + "."; //NOI18N
1491: }
1492: cmd += table;
1493: } else {
1494: if (schema != null && schema.length() > 0) {
1495: cmd += schema + "."; //NOI18N
1496: }
1497: cmd += table;
1498: if (catalog != null && catalog.length() > 0) {
1499: cmd += dbmd.getCatalogSeparator() + catalog;
1500: }
1501: }
1502: cmd += " "; //NOI18N
1503: return cmd;
1504: }
1505:
1506: /*
1507: * matcher:
1508: *
1509: * idx = i (zero based)
1510: * value = catalog | schema | table
1511: * columnValueNames = columnCatalogNames | columnSchemaNames | columnTableNames
1512: * rsmdColumnValueName = callerMd.getCatalogName(i+1) | getSchemaName(i+1) | getTableName(i+1)
1513: */
1514: private boolean matcher(int idx, String value,
1515: String[] columnValueNames, String rsmdName,
1516: boolean relaxedMatch) {
1517: /*
1518: * some drivers, e.g., mysql, will return schemaName.tableName for rsmd.getTableName()
1519: * let's strip it (i.e., let's strip any prepended value from rsmdName)
1520: */
1521: int pos = rsmdName.lastIndexOf('.');
1522: if (pos != -1) {
1523: rsmdName = rsmdName.substring(pos + 1);
1524: }
1525: // attempt to match on value
1526: String columnValue = null;
1527: if (columnValueNames != null && columnValueNames.length > idx
1528: && columnValueNames[idx] != null) {
1529: columnValue = columnValueNames[idx];
1530: }
1531: // if columnValue is still null, attempt to get it from the rsmd
1532: if (columnValue == null) {
1533: columnValue = rsmdName;
1534: if (columnValue != null && columnValue.length() == 0) {
1535: columnValue = null;
1536: }
1537: }
1538: // let's see if there is a match with value
1539: if ((value == null && columnValue != null)
1540: || (value != null && columnValue == null)) {
1541: /*
1542: * one null, the other not, don't include it in the where
1543: * except in the case of relaxedMatch, then we don't want to preclude a match
1544: */
1545: if (relaxedMatch) {
1546: return true;
1547: }
1548: return false;
1549: } else if (value == null && columnValue == null) {
1550: // columns match, fall through as it has potential for inclusion
1551: } else if (!columnValue.equals(value)) {
1552: // both are non-null and they don't match, don't include it in the where
1553: return false;
1554: }
1555: // at this point, either the column value matched or both it and value were null
1556: // as such, we have a potential for inclusion in the where clause
1557: return true;
1558: }
1559:
1560: /**
1561: * Note: We ignore keyColumns in cachedRowSet and build the keyColumns
1562: * with whereColumnsInThisTable
1563: *
1564: * Assigns to the given <code>CachedRowSet</code> object's
1565: * <code>params</code>
1566: * field an array whose length equals the number of columns needed
1567: * to uniquely identify a row in the rowset. The array is given
1568: * values by the method <code>buildWhereClause</code>.
1569: * <P>
1570: * If the <code>CachedRowSet</code> object's <code>keyCols</code>
1571: * field has length <code>0</code> or is <code>null</code>, the array
1572: * is set with the column number of every column in the rowset.
1573: * Otherwise, the array in the field <code>keyCols</code> is set with only
1574: * the column numbers of the columns that are required to form a unique
1575: * identifier for a row.
1576: *
1577: * @param crs the <code>CachedRowSet</code> object for which this
1578: * <code>CachedRowSetXWriter</code> object is the writer
1579: *
1580: * @throws SQLException if a database access error occurs
1581: */
1582: private void buildKeyDesc(CachedRowSet crs) throws SQLException {
1583:
1584: /* We now always construct keyCols from whereColumnsInThisTable
1585: keyCols = crs.getKeyColumns();
1586: keyCols = null;
1587: if (keyCols == null || keyCols.length == 0) {
1588: keyCols = new int[callerColumnCount];
1589: for (int i = 0; i < keyCols.length; ) {
1590: keyCols[i] = ++i;
1591: }
1592: }
1593: */
1594: keyCols = new int[whereColumnsInThisTable.size()];
1595: int idx = 0;
1596: for (Iterator i = whereColumnsInThisTable.iterator(); i
1597: .hasNext();) {
1598: keyCols[idx++] = ((Integer) i.next()).intValue();
1599: }
1600: params = new Object[keyCols.length];
1601: }
1602:
1603: /**
1604: * <p>
1605: * Constructs an SQL <code>WHERE</code> clause using the given
1606: * string as a starting point. The resulting clause will contain
1607: * a column name and " = ?" for each key column, that is, each column
1608: * that is needed to form a unique identifier for a row in the rowset.
1609: * This <code>WHERE</code> clause can be added to
1610: * a <code>PreparedStatement</code> object that updates, inserts, or
1611: * deletes a row.
1612: * </p>
1613: *
1614: * <p>
1615: * Note: In the case of a join, the where clause will only cotain
1616: * columns from the table specified by the CachedRowSet's
1617: * table property.
1618: * </p>
1619: *
1620: * <p>
1621: * This method uses the given result set to access values in the
1622: * <code>CachedRowSet</code> object that called this writer. These
1623: * values are used to build the array of parameters that will serve as
1624: * replacements for the "?" parameter placeholders in the
1625: * <code>PreparedStatement</code> object that is sent to the
1626: * <code>CachedRowSet</code> object's underlying data source.
1627: * </p>
1628: *
1629: * @param whereClause a <code>String</code> object that is an empty
1630: * string ("")
1631: * @param rs a <code>ResultSet</code> object that can be used
1632: * to access the <code>CachedRowSet</code> object's data
1633: * @return a <code>WHERE</code> clause of the form "<code>WHERE</code>
1634: * columnName = ? AND columnName = ? AND columnName = ? ..."
1635: * @throws SQLException if a database access error occurs
1636: */
1637: private String buildWhereClause(String whereClause, ResultSet rs)
1638: throws SQLException {
1639: whereClause = "WHERE "; //NOI18N
1640:
1641: boolean firstTime = true;
1642: for (int i = 0; i < keyCols.length; i++) {
1643: if (firstTime) {
1644: firstTime = false;
1645: } else {
1646: whereClause += "AND "; //NOI18N
1647: }
1648: whereClause += callerMd.getColumnName(keyCols[i]);
1649: params[i] = rs.getObject(keyCols[i]);
1650: if (rs.wasNull() == true) {
1651: whereClause += " IS NULL "; //NOI18N
1652: } else {
1653: whereClause += " = ? "; //NOI18N
1654: }
1655: }
1656: return whereClause;
1657: }
1658:
1659: /**
1660: * determine if if this column is part of table tableName
1661: */
1662: private static boolean isColumnPartOfTable(DatabaseMetaData dbmd,
1663: String columnName, String catalogName, String schemaName,
1664: String tableName, String columnTableName)
1665: throws SQLException {
1666:
1667: // some databases return a qualified tableName for ResultSetMetaData.getTableName(int)
1668: // so we will strip it
1669: int idx = columnTableName.lastIndexOf('.');
1670: if (idx != -1) {
1671: columnTableName = columnTableName.substring(idx + 1);
1672: }
1673:
1674: ResultSet colRs = dbmd.getColumns(
1675: (catalogName == null || catalogName.equals("")) ? null
1676: : catalogName, //NOI18N
1677: (schemaName == null || schemaName.equals("")) ? null
1678: : schemaName, //NOI18N
1679: tableName, columnName);
1680: boolean rc = colRs.next();
1681: colRs.close();
1682: if (rc) {
1683: rc = columnTableName == null || columnTableName.equals("")
1684: || //NOI18N
1685: columnTableName.equals(tableName);
1686: }
1687: return rc;
1688: }
1689:
1690: void updateResolvedConflictToDB(CachedRowSet crs, Connection con)
1691: throws SQLException {
1692: //String updateExe = ;
1693: PreparedStatement pStmt;
1694: String strWhere = "WHERE "; //NOI18N
1695: String strExec = " "; //NOI18N
1696: String strUpdate = "UPDATE "; //NOI18N
1697: int icolCount = crs.getMetaData().getColumnCount();
1698: int keyColumns[] = crs.getKeyColumns();
1699: Object param[];
1700: String strSet = ""; //NOI18N
1701:
1702: strWhere = buildWhereClause(strWhere, crs);
1703:
1704: if (keyColumns == null || keyColumns.length == 0) {
1705: keyColumns = new int[icolCount];
1706: for (int i = 0; i < keyColumns.length;) {
1707: keyColumns[i] = ++i;
1708: }
1709: }
1710: param = new Object[keyColumns.length];
1711:
1712: strUpdate = "UPDATE "
1713: + buildTableName(con.getMetaData(), //NOI18N
1714: crs.getMetaData().getCatalogName(1), crs
1715: .getMetaData().getSchemaName(1), crs
1716: .getTableName());
1717:
1718: // changed or updated values will become part of
1719: // set clause here
1720: strUpdate += "SET "; //NOI18N
1721:
1722: boolean first = true;
1723:
1724: for (int i = 1; i <= icolCount; i++) {
1725: if (crs.columnUpdated(i)) {
1726: if (first == false) {
1727: strSet += ", "; //NOI18N
1728: }
1729: strSet += crs.getMetaData().getColumnName(i);
1730: strSet += " = ? "; //NOI18N
1731: first = false;
1732: } //end if
1733: } //end for
1734:
1735: // keycols will become part of where clause
1736: strUpdate += strSet;
1737: strWhere = "WHERE "; //NOI18N
1738:
1739: for (int i = 0; i < keyColumns.length; i++) {
1740: if (i > 0) {
1741: strWhere += "AND "; //NOI18N
1742: }
1743: strWhere += crs.getMetaData().getColumnName(keyColumns[i]);
1744: param[i] = crs.getObject(keyColumns[i]);
1745: if (crs.wasNull() == true) {
1746: strWhere += " IS NULL "; //NOI18N
1747: } else {
1748: strWhere += " = ? "; //NOI18N
1749: }
1750: }
1751: strUpdate += strWhere;
1752:
1753: if (((CachedRowSetX) crs).getPrintStatements()) {
1754: System.out.println(strUpdate);
1755: }
1756: pStmt = con.prepareStatement(strUpdate);
1757:
1758: int idx = 0;
1759: for (int i = 0; i < icolCount; i++) {
1760: if (crs.columnUpdated(i + 1)) {
1761: Object obj = crs.getObject(i + 1);
1762: if (obj != null) {
1763: pStmt.setObject(++idx, obj);
1764: } else {
1765: pStmt.setNull(++idx, crs.getMetaData()
1766: .getColumnType(i + 1));
1767: } //end if ..else
1768: } //end if crs.column...
1769: } //end for
1770:
1771: // Set the key cols for after WHERE =? clause
1772: for (int i = 0; i < keyColumns.length; i++) {
1773: if (param[i] != null) {
1774: pStmt.setObject(++idx, param[i]);
1775: }
1776: }
1777:
1778: int id = pStmt.executeUpdate();
1779: }
1780:
1781: /**
1782: *
1783: */
1784: public void commit() throws SQLException {
1785: con.commit();
1786: if (reader.getCloseConnection() == true) {
1787: con.close();
1788: con = null;
1789: }
1790: }
1791:
1792: /**
1793: *
1794: */
1795: public void rollback() throws SQLException {
1796: con.rollback();
1797: if (reader.getCloseConnection() == true) {
1798: con.close();
1799: con = null;
1800: }
1801: }
1802:
1803: /**
1804: *
1805: */
1806: public void rollback(Savepoint s) throws SQLException {
1807: con.rollback(s);
1808: if (reader.getCloseConnection() == true) {
1809: con.close();
1810: con = null;
1811: }
1812: }
1813:
1814: public void closeConnection() throws SQLException {
1815: if (con != null && reader.getCloseConnection() == true) {
1816: try {
1817: if (!con.getAutoCommit()) {
1818: con.rollback();
1819: }
1820: } catch (Exception dummy) {
1821: /*
1822: * not an error condition, we're closing anyway, but
1823: * we'd like to clean up any locks if we can since
1824: * it is not clear the connection pool will clean
1825: * these connections in a timely manner
1826: */
1827: }
1828: con.close();
1829: }
1830: }
1831: }
|