1 /*
2 * Copyright (c) 2003, 2013, Oracle and/or its affiliates. All rights reserved.
3 * DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS FILE HEADER.
4 *
5 * This code is free software; you can redistribute it and/or modify it
6 * under the terms of the GNU General Public License version 2 only, as
7 * published by the Free Software Foundation. Oracle designates this
8 * particular file as subject to the "Classpath" exception as provided
9 * by Oracle in the LICENSE file that accompanied this code.
10 *
11 * This code is distributed in the hope that it will be useful, but WITHOUT
12 * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
13 * FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License
14 * version 2 for more details (a copy is included in the LICENSE file that
15 * accompanied this code).
16 *
17 * You should have received a copy of the GNU General Public License version
18 * 2 along with this work; if not, write to the Free Software Foundation,
19 * Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA.
20 *
21 * Please contact Oracle, 500 Oracle Parkway, Redwood Shores, CA 94065 USA
22 * or visit www.oracle.com if you need additional information or have any
23 * questions.
24 */
25
26 package com.sun.rowset.internal;
27
28 import java.sql.*;
29 import javax.sql.*;
30 import java.util.*;
31 import java.io.*;
32 import sun.reflect.misc.ReflectUtil;
33
34 import com.sun.rowset.*;
35 import java.text.MessageFormat;
36 import javax.sql.rowset.*;
37 import javax.sql.rowset.serial.SQLInputImpl;
38 import javax.sql.rowset.serial.SerialArray;
39 import javax.sql.rowset.serial.SerialBlob;
40 import javax.sql.rowset.serial.SerialClob;
41 import javax.sql.rowset.serial.SerialStruct;
42 import javax.sql.rowset.spi.*;
43
44
45 /**
46 * The facility called on internally by the {@code RIOptimisticProvider} implementation to
47 * propagate changes back to the data source from which the rowset got its data.
48 * <P>
49 * A {@code CachedRowSetWriter} object, called a writer, has the public
50 * method {@code writeData} for writing modified data to the underlying data source.
51 * This method is invoked by the rowset internally and is never invoked directly by an application.
52 * A writer also has public methods for setting and getting
53 * the {@code CachedRowSetReader} object, called a reader, that is associated
54 * with the writer. The remainder of the methods in this class are private and
55 * are invoked internally, either directly or indirectly, by the method
56 * {@code writeData}.
57 * <P>
58 * Typically the {@code SyncFactory} manages the {@code RowSetReader} and
59 * the {@code RowSetWriter} implementations using {@code SyncProvider} objects.
60 * Standard JDBC RowSet implementations provide an object instance of this
61 * writer by invoking the {@code SyncProvider.getRowSetWriter()} method.
62 *
63 * @version 0.2
64 * @author Jonathan Bruce
65 * @see javax.sql.rowset.spi.SyncProvider
66 * @see javax.sql.rowset.spi.SyncFactory
67 * @see javax.sql.rowset.spi.SyncFactoryException
68 */
69 public class CachedRowSetWriter implements TransactionalWriter, Serializable {
70
71 /**
72 * The {@code Connection} object that this writer will use to make a
73 * connection to the data source to which it will write data.
74 *
75 */
76 private transient Connection con;
77
78 /**
79 * The SQL {@code SELECT} command that this writer will call
80 * internally. The method {@code initSQLStatements} builds this
81 * command by supplying the words "SELECT" and "FROM," and using
82 * metadata to get the table name and column names .
83 *
84 * @serial
85 */
86 private String selectCmd;
87
88 /**
89 * The SQL {@code UPDATE} command that this writer will call
90 * internally to write data to the rowset's underlying data source.
91 * The method {@code initSQLStatements} builds this {@code String}
92 * object.
93 *
94 * @serial
95 */
96 private String updateCmd;
97
98 /**
99 * The SQL {@code WHERE} clause the writer will use for update
100 * statements in the {@code PreparedStatement} object
101 * it sends to the underlying data source.
102 *
103 * @serial
104 */
105 private String updateWhere;
106
107 /**
108 * The SQL {@code DELETE} command that this writer will call
109 * internally to delete a row in the rowset's underlying data source.
110 *
111 * @serial
112 */
113 private String deleteCmd;
114
115 /**
116 * The SQL {@code WHERE} clause the writer will use for delete
117 * statements in the {@code PreparedStatement} object
118 * it sends to the underlying data source.
119 *
120 * @serial
121 */
122 private String deleteWhere;
123
124 /**
125 * The SQL {@code INSERT INTO} command that this writer will internally use
126 * to insert data into the rowset's underlying data source. The method
127 * {@code initSQLStatements} builds this command with a question
128 * mark parameter placeholder for each column in the rowset.
129 *
130 * @serial
131 */
132 private String insertCmd;
133
134 /**
135 * An array containing the column numbers of the columns that are
136 * needed to uniquely identify a row in the {@code CachedRowSet} object
137 * for which this {@code CachedRowSetWriter} object is the writer.
138 *
139 * @serial
140 */
141 private int[] keyCols;
142
143 /**
144 * An array of the parameters that should be used to set the parameter
145 * placeholders in a {@code PreparedStatement} object that this
146 * writer will execute.
147 *
148 * @serial
149 */
150 private Object[] params;
151
152 /**
153 * The {@code CachedRowSetReader} object that has been
154 * set as the reader for the {@code CachedRowSet} object
155 * for which this {@code CachedRowSetWriter} object is the writer.
156 *
157 * @serial
158 */
159 private CachedRowSetReader reader;
160
161 /**
162 * The {@code ResultSetMetaData} object that contains information
163 * about the columns in the {@code CachedRowSet} object
164 * for which this {@code CachedRowSetWriter} object is the writer.
165 *
166 * @serial
167 */
168 private ResultSetMetaData callerMd;
169
170 /**
171 * The number of columns in the {@code CachedRowSet} object
172 * for which this {@code CachedRowSetWriter} object is the writer.
173 *
174 * @serial
175 */
176 private int callerColumnCount;
177
178 /**
179 * This {@code CachedRowSet} will hold the conflicting values
180 * retrieved from the db and hold it.
181 */
182 private CachedRowSetImpl crsResolve;
183
184 /**
185 * This {@code ArrayList} will hold the values of SyncResolver.*
186 */
187 private ArrayList<Integer> status;
188
189 /**
190 * This will check whether the same field value has changed both
191 * in database and CachedRowSet.
192 */
193 private int iChangedValsInDbAndCRS;
194
195 /**
196 * This will hold the number of cols for which the values have
197 * changed only in database.
198 */
199 private int iChangedValsinDbOnly ;
200
201 private JdbcRowSetResourceBundle resBundle;
202
203 public CachedRowSetWriter() {
204 try {
205 resBundle = JdbcRowSetResourceBundle.getJdbcRowSetResourceBundle();
206 } catch(IOException ioe) {
207 throw new RuntimeException(ioe);
208 }
209 }
210
211 /**
212 * Propagates changes in the given {@code RowSet} object
213 * back to its underlying data source and returns {@code true}
214 * if successful. The writer will check to see if
215 * the data in the pre-modified rowset (the original values) differ
216 * from the data in the underlying data source. If data in the data
217 * source has been modified by someone else, there is a conflict,
218 * and in that case, the writer will not write to the data source.
219 * In other words, the writer uses an optimistic concurrency algorithm:
220 * It checks for conflicts before making changes rather than restricting
221 * access for concurrent users.
222 * <P>
223 * This method is called by the rowset internally when
224 * the application invokes the method {@code acceptChanges}.
225 * The {@code writeData} method in turn calls private methods that
226 * it defines internally.
227 * The following is a general summary of what the method
228 * {@code writeData} does, much of which is accomplished
229 * through calls to its own internal methods.
230 * <OL>
231 * <LI>Creates a {@code CachedRowSet} object from the given
232 * {@code RowSet} object
233 * <LI>Makes a connection with the data source
234 * <UL>
235 * <LI>Disables autocommit mode if it is not already disabled
236 * <LI>Sets the transaction isolation level to that of the rowset
237 * </UL>
238 * <LI>Checks to see if the reader has read new data since the writer
239 * was last called and, if so, calls the method
240 * {@code initSQLStatements} to initialize new SQL statements
241 * <UL>
242 * <LI>Builds new {@code SELECT}, {@code UPDATE},
243 * {@code INSERT}, and {@code DELETE} statements
244 * <LI>Uses the {@code CachedRowSet} object's metadata to
245 * determine the table name, column names, and the columns
246 * that make up the primary key
247 * </UL>
248 * <LI>When there is no conflict, propagates changes made to the
249 * {@code CachedRowSet} object back to its underlying data source
250 * <UL>
251 * <LI>Iterates through each row of the {@code CachedRowSet} object
252 * to determine whether it has been updated, inserted, or deleted
253 * <LI>If the corresponding row in the data source has not been changed
254 * since the rowset last read its
255 * values, the writer will use the appropriate command to update,
256 * insert, or delete the row
257 * <LI>If any data in the data source does not match the original values
258 * for the {@code CachedRowSet} object, the writer will roll
259 * back any changes it has made to the row in the data source.
260 * </UL>
261 * </OL>
262 *
263 * @return {@code true} if changes to the rowset were successfully
264 * written to the rowset's underlying data source;
265 * {@code false} otherwise
266 */
267 public boolean writeData(RowSetInternal caller) throws SQLException {
268 long conflicts = 0;
269 boolean showDel = false;
270 PreparedStatement pstmtIns = null;
271 iChangedValsInDbAndCRS = 0;
272 iChangedValsinDbOnly = 0;
273
274 // We assume caller is a CachedRowSet
275 CachedRowSetImpl crs = (CachedRowSetImpl)caller;
276 // crsResolve = new CachedRowSetImpl();
277 this.crsResolve = new CachedRowSetImpl();;
278
279 // The reader is registered with the writer at design time.
280 // This is not required, in general. The reader has logic
281 // to get a JDBC connection, so call it.
282
283 con = reader.connect(caller);
284
285
286 if (con == null) {
287 throw new SQLException(resBundle.handleGetObject("crswriter.connect").toString());
288 }
289
290 /*
291 // Fix 6200646.
292 // Don't change the connection or transaction properties. This will fail in a
293 // J2EE container.
294 if (con.getAutoCommit() == true) {
295 con.setAutoCommit(false);
296 }
297
298 con.setTransactionIsolation(crs.getTransactionIsolation());
299 */
300
301 initSQLStatements(crs);
302 int iColCount;
303
304 RowSetMetaDataImpl rsmdWrite = (RowSetMetaDataImpl)crs.getMetaData();
305 RowSetMetaDataImpl rsmdResolv = new RowSetMetaDataImpl();
306
307 iColCount = rsmdWrite.getColumnCount();
308 int sz= crs.size()+1;
309 status = new ArrayList<>(sz);
310
311 status.add(0,null);
312 rsmdResolv.setColumnCount(iColCount);
313
314 for(int i =1; i <= iColCount; i++) {
315 rsmdResolv.setColumnType(i, rsmdWrite.getColumnType(i));
316 rsmdResolv.setColumnName(i, rsmdWrite.getColumnName(i));
317 rsmdResolv.setNullable(i, ResultSetMetaData.columnNullableUnknown);
318 }
319 this.crsResolve.setMetaData(rsmdResolv);
320
321 // moved outside the insert inner loop
322 //pstmtIns = con.prepareStatement(insertCmd);
323
324 if (callerColumnCount < 1) {
325 // No data, so return success.
326 if (reader.getCloseConnection() == true)
327 con.close();
328 return true;
329 }
330 // We need to see rows marked for deletion.
331 showDel = crs.getShowDeleted();
332 crs.setShowDeleted(true);
333
334 // Look at all the rows.
335 crs.beforeFirst();
336
337 int rows =1;
338 while (crs.next()) {
339 if (crs.rowDeleted()) {
340 // The row has been deleted.
341 if (deleteOriginalRow(crs, this.crsResolve)) {
342 status.add(rows, SyncResolver.DELETE_ROW_CONFLICT);
343 conflicts++;
344 } else {
345 // delete happened without any occurrence of conflicts
346 // so update status accordingly
347 status.add(rows, SyncResolver.NO_ROW_CONFLICT);
348 }
349
350 } else if (crs.rowInserted()) {
351 // The row has been inserted.
352
353 pstmtIns = con.prepareStatement(insertCmd);
354 if (insertNewRow(crs, pstmtIns, this.crsResolve)) {
355 status.add(rows, SyncResolver.INSERT_ROW_CONFLICT);
356 conflicts++;
357 } else {
358 // insert happened without any occurrence of conflicts
359 // so update status accordingly
360 status.add(rows, SyncResolver.NO_ROW_CONFLICT);
361 }
362 } else if (crs.rowUpdated()) {
363 // The row has been updated.
364 if (updateOriginalRow(crs)) {
365 status.add(rows, SyncResolver.UPDATE_ROW_CONFLICT);
366 conflicts++;
367 } else {
368 // update happened without any occurrence of conflicts
369 // so update status accordingly
370 status.add(rows, SyncResolver.NO_ROW_CONFLICT);
371 }
372
373 } else {
374 /** The row is neither of inserted, updated or deleted.
375 * So set nulls in the this.crsResolve for this row,
376 * as nothing is to be done for such rows.
377 * Also note that if such a row has been changed in database
378 * and we have not changed(inserted, updated or deleted)
379 * that is fine.
380 **/
381 int icolCount = crs.getMetaData().getColumnCount();
382 status.add(rows, SyncResolver.NO_ROW_CONFLICT);
383
384 this.crsResolve.moveToInsertRow();
385 for(int cols=0;cols<iColCount;cols++) {
386 this.crsResolve.updateNull(cols+1);
387 } //end for
388
389 this.crsResolve.insertRow();
390 this.crsResolve.moveToCurrentRow();
391
392 } //end if
393 rows++;
394 } //end while
395
396 // close the insert statement
397 if(pstmtIns!=null)
398 pstmtIns.close();
399 // reset
400 crs.setShowDeleted(showDel);
401
402 crs.beforeFirst();
403 this.crsResolve.beforeFirst();
404
405 if(conflicts != 0) {
406 SyncProviderException spe = new SyncProviderException(conflicts + " " +
407 resBundle.handleGetObject("crswriter.conflictsno").toString());
408 //SyncResolver syncRes = spe.getSyncResolver();
409
410 SyncResolverImpl syncResImpl = (SyncResolverImpl) spe.getSyncResolver();
411
412 syncResImpl.setCachedRowSet(crs);
413 syncResImpl.setCachedRowSetResolver(this.crsResolve);
414
415 syncResImpl.setStatus(status);
416 syncResImpl.setCachedRowSetWriter(this);
417
418 throw spe;
419 } else {
420 return true;
421 }
422 /*
423 if (conflict == true) {
424 con.rollback();
425 return false;
426 } else {
427 con.commit();
428 if (reader.getCloseConnection() == true) {
429 con.close();
430 }
431 return true;
432 }
433 */
434
435 } //end writeData
436
437 /**
438 * Updates the given {@code CachedRowSet} object's underlying data
439 * source so that updates to the rowset are reflected in the original
440 * data source, and returns {@code false} if the update was successful.
441 * A return value of {@code true} indicates that there is a conflict,
442 * meaning that a value updated in the rowset has already been changed by
443 * someone else in the underlying data source. A conflict can also exist
444 * if, for example, more than one row in the data source would be affected
445 * by the update or if no rows would be affected. In any case, if there is
446 * a conflict, this method does not update the underlying data source.
447 * <P>
448 * This method is called internally by the method {@code writeData}
449 * if a row in the {@code CachedRowSet} object for which this
450 * {@code CachedRowSetWriter} object is the writer has been updated.
451 *
452 * @return {@code false} if the update to the underlying data source is
453 * successful; {@code true} otherwise
454 * @throws SQLException if a database access error occurs
455 */
456 private boolean updateOriginalRow(CachedRowSet crs)
457 throws SQLException {
458 PreparedStatement pstmt;
459 int i = 0;
460 int idx = 0;
461
462 // Select the row from the database.
463 ResultSet origVals = crs.getOriginalRow();
464 origVals.next();
465
466 try {
467 updateWhere = buildWhereClause(updateWhere, origVals);
468
469
470 /**
471 * The following block of code is for checking a particular type of
472 * query where in there is a where clause. Without this block, if a
473 * SQL statement is built the "where" clause will appear twice hence
474 * the DB errors out and a SQLException is thrown. This code also
475 * considers that the where clause is in the right place as the
476 * CachedRowSet object would already have been populated with this
477 * query before coming to this point.
478 **/
479
480
481 String tempselectCmd = selectCmd.toLowerCase();
482
483 int idxWhere = tempselectCmd.indexOf("where");
484
485 if(idxWhere != -1)
486 {
487 String tempSelect = selectCmd.substring(0,idxWhere);
488 selectCmd = tempSelect;
489 }
490
491 pstmt = con.prepareStatement(selectCmd + updateWhere,
492 ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
493
494 for (i = 0; i < keyCols.length; i++) {
495 if (params[i] != null) {
496 pstmt.setObject(++idx, params[i]);
497 } else {
498 continue;
499 }
500 }
501
502 try {
503 pstmt.setMaxRows(crs.getMaxRows());
504 pstmt.setMaxFieldSize(crs.getMaxFieldSize());
505 pstmt.setEscapeProcessing(crs.getEscapeProcessing());
506 pstmt.setQueryTimeout(crs.getQueryTimeout());
507 } catch (Exception ex) {
508 // Older driver don't support these operations.
509 }
510
511 ResultSet rs = null;
512 rs = pstmt.executeQuery();
513 ResultSetMetaData rsmd = rs.getMetaData();
514
515 if (rs.next()) {
516 if (rs.next()) {
517 /** More than one row conflict.
518 * If rs has only one row we are able to
519 * uniquely identify the row where update
520 * have to happen else if more than one
521 * row implies we cannot uniquely identify the row
522 * where we have to do updates.
523 * crs.setKeyColumns needs to be set to
524 * come out of this situation.
525 */
526
527 return true;
528 }
529
530 // don't close the rs
531 // we require the record in rs to be used.
532 // rs.close();
533 // pstmt.close();
534 rs.first();
535
536 // how many fields need to be updated
537 int colsNotChanged = 0;
538 Vector<Integer> cols = new Vector<>();
539 String updateExec = updateCmd;
540 Object orig;
541 Object curr;
542 Object rsval;
543 boolean boolNull = true;
544 Object objVal = null;
545
546 // There's only one row and the cursor
547 // needs to be on that row.
548
549 boolean first = true;
550 boolean flag = true;
551
552 this.crsResolve.moveToInsertRow();
553
554 for (i = 1; i <= callerColumnCount; i++) {
555 orig = origVals.getObject(i);
556 curr = crs.getObject(i);
557 rsval = rs.getObject(i);
558 /*
559 * the following block creates equivalent objects
560 * that would have been created if this rs is populated
561 * into a CachedRowSet so that comparison of the column values
562 * from the ResultSet and CachedRowSet are possible
563 */
564 Map<String, Class<?>> map = (crs.getTypeMap() == null)?con.getTypeMap():crs.getTypeMap();
565 if (rsval instanceof Struct) {
566
567 Struct s = (Struct)rsval;
568
569 // look up the class in the map
570 Class<?> c = null;
571 c = map.get(s.getSQLTypeName());
572 if (c != null) {
573 // create new instance of the class
574 SQLData obj = null;
575 try {
576 ReflectUtil.checkPackageAccess(c);
577 obj = (SQLData)c.newInstance();
578 } catch (Exception ex) {
579 throw new SQLException("Unable to Instantiate: ", ex);
580 }
581 // get the attributes from the struct
582 Object attribs[] = s.getAttributes(map);
583 // create the SQLInput "stream"
584 SQLInputImpl sqlInput = new SQLInputImpl(attribs, map);
585 // read the values...
586 obj.readSQL(sqlInput, s.getSQLTypeName());
587 rsval = obj;
588 }
589 } else if (rsval instanceof SQLData) {
590 rsval = new SerialStruct((SQLData)rsval, map);
591 } else if (rsval instanceof Blob) {
592 rsval = new SerialBlob((Blob)rsval);
593 } else if (rsval instanceof Clob) {
594 rsval = new SerialClob((Clob)rsval);
595 } else if (rsval instanceof java.sql.Array) {
596 rsval = new SerialArray((java.sql.Array)rsval, map);
597 }
598
599 // reset boolNull if it had been set
600 boolNull = true;
601
602 /** This addtional checking has been added when the current value
603 * in the DB is null, but the DB had a different value when the
604 * data was actaully fetched into the CachedRowSet.
605 **/
606
607 if(rsval == null && orig != null) {
608 // value in db has changed
609 // don't proceed with synchronization
610 // get the value in db and pass it to the resolver.
611
612 iChangedValsinDbOnly++;
613 // Set the boolNull to false,
614 // in order to set the actual value;
615 boolNull = false;
616 objVal = rsval;
617 }
618
619 /** Adding the checking for rsval to be "not" null or else
620 * it would through a NullPointerException when the values
621 * are compared.
622 **/
623
624 else if(rsval != null && (!rsval.equals(orig)))
625 {
626 // value in db has changed
627 // don't proceed with synchronization
628 // get the value in db and pass it to the resolver.
629
630 iChangedValsinDbOnly++;
631 // Set the boolNull to false,
632 // in order to set the actual value;
633 boolNull = false;
634 objVal = rsval;
635 } else if ( (orig == null || curr == null) ) {
636
637 /** Adding the additonal condition of checking for "flag"
638 * boolean variable, which would otherwise result in
639 * building a invalid query, as the comma would not be
640 * added to the query string.
641 **/
642
643 if (first == false || flag == false) {
644 updateExec += ", ";
645 }
646 updateExec += crs.getMetaData().getColumnName(i);
647 cols.add(i);
648 updateExec += " = ? ";
649 first = false;
650
651 /** Adding the extra condition for orig to be "not" null as the
652 * condition for orig to be null is take prior to this, if this
653 * is not added it will result in a NullPointerException when
654 * the values are compared.
655 **/
656
657 } else if (orig.equals(curr)) {
658 colsNotChanged++;
659 //nothing to update in this case since values are equal
660
661 /** Adding the extra condition for orig to be "not" null as the
662 * condition for orig to be null is take prior to this, if this
663 * is not added it will result in a NullPointerException when
664 * the values are compared.
665 **/
666
667 } else if(orig.equals(curr) == false) {
668 // When values from db and values in CachedRowSet are not equal,
669 // if db value is same as before updation for each col in
670 // the row before fetching into CachedRowSet,
671 // only then we go ahead with updation, else we
672 // throw SyncProviderException.
673
674 // if value has changed in db after fetching from db
675 // for some cols of the row and at the same time, some other cols
676 // have changed in CachedRowSet, no synchronization happens
677
678 // Synchronization happens only when data when fetching is
679 // same or at most has changed in cachedrowset
680
681 // check orig value with what is there in crs for a column
682 // before updation in crs.
683
684 if(crs.columnUpdated(i)) {
685 if(rsval.equals(orig)) {
686 // At this point we are sure that
687 // the value updated in crs was from
688 // what is in db now and has not changed
689 if (flag == false || first == false) {
690 updateExec += ", ";
691 }
692 updateExec += crs.getMetaData().getColumnName(i);
693 cols.add(i);
694 updateExec += " = ? ";
695 flag = false;
696 } else {
697 // Here the value has changed in the db after
698 // data was fetched
699 // Plus store this row from CachedRowSet and keep it
700 // in a new CachedRowSet
701 boolNull= false;
702 objVal = rsval;
703 iChangedValsInDbAndCRS++;
704 }
705 }
706 }
707
708 if(!boolNull) {
709 this.crsResolve.updateObject(i,objVal);
710 } else {
711 this.crsResolve.updateNull(i);
712 }
713 } //end for
714
715 rs.close();
716 pstmt.close();
717
718 this.crsResolve.insertRow();
719 this.crsResolve.moveToCurrentRow();
720
721 /**
722 * if nothing has changed return now - this can happen
723 * if column is updated to the same value.
724 * if colsNotChanged == callerColumnCount implies we are updating
725 * the database with ALL COLUMNS HAVING SAME VALUES,
726 * so skip going to database, else do as usual.
727 **/
728 if ( (first == false && cols.size() == 0) ||
729 colsNotChanged == callerColumnCount ) {
730 return false;
731 }
732
733 if(iChangedValsInDbAndCRS != 0 || iChangedValsinDbOnly != 0) {
734 return true;
735 }
736
737
738 updateExec += updateWhere;
739
740 pstmt = con.prepareStatement(updateExec);
741
742 // Comments needed here
743 for (i = 0; i < cols.size(); i++) {
744 Object obj = crs.getObject(cols.get(i));
745 if (obj != null)
746 pstmt.setObject(i + 1, obj);
747 else
748 pstmt.setNull(i + 1,crs.getMetaData().getColumnType(i + 1));
749 }
750 idx = i;
751
752 // Comments needed here
753 for (i = 0; i < keyCols.length; i++) {
754 if (params[i] != null) {
755 pstmt.setObject(++idx, params[i]);
756 } else {
757 continue;
758 }
759 }
760
761 i = pstmt.executeUpdate();
762
763 /**
764 * i should be equal to 1(row count), because we update
765 * one row(returned as row count) at a time, if all goes well.
766 * if 1 != 1, this implies we have not been able to
767 * do updations properly i.e there is a conflict in database
768 * versus what is in CachedRowSet for this particular row.
769 **/
770
771 return false;
772
773 } else {
774 /**
775 * Cursor will be here, if the ResultSet may not return even a single row
776 * i.e. we can't find the row where to update because it has been deleted
777 * etc. from the db.
778 * Present the whole row as null to user, to force null to be sync'ed
779 * and hence nothing to be synced.
780 *
781 * NOTE:
782 * ------
783 * In the database if a column that is mapped to java.sql.Types.REAL stores
784 * a Double value and is compared with value got from ResultSet.getFloat()
785 * no row is retrieved and will throw a SyncProviderException. For details
786 * see bug Id 5053830
787 **/
788 return true;
789 }
790 } catch (SQLException ex) {
791 ex.printStackTrace();
792 // if executeUpdate fails it will come here,
793 // update crsResolve with null rows
794 this.crsResolve.moveToInsertRow();
795
796 for(i = 1; i <= callerColumnCount; i++) {
797 this.crsResolve.updateNull(i);
798 }
799
800 this.crsResolve.insertRow();
801 this.crsResolve.moveToCurrentRow();
802
803 return true;
804 }
805 }
806
807 /**
808 * Inserts a row that has been inserted into the given
809 * {@code CachedRowSet} object into the data source from which
810 * the rowset is derived, returning {@code false} if the insertion
811 * was successful.
812 *
813 * @param crs the {@code CachedRowSet} object that has had a row inserted
814 * and to whose underlying data source the row will be inserted
815 * @param pstmt the {@code PreparedStatement} object that will be used
816 * to execute the insertion
817 * @return {@code false} to indicate that the insertion was successful;
818 * {@code true} otherwise
819 * @throws SQLException if a database access error occurs
820 */
821 private boolean insertNewRow(CachedRowSet crs,
822 PreparedStatement pstmt, CachedRowSetImpl crsRes) throws SQLException {
823
824 boolean returnVal = false;
825
826 try (PreparedStatement pstmtSel = con.prepareStatement(selectCmd,
827 ResultSet.TYPE_SCROLL_SENSITIVE,
828 ResultSet.CONCUR_READ_ONLY);
829 ResultSet rs = pstmtSel.executeQuery();
830 ResultSet rs2 = con.getMetaData().getPrimaryKeys(null, null,
831 crs.getTableName())
832 ) {
833
834 ResultSetMetaData rsmd = crs.getMetaData();
835 int icolCount = rsmd.getColumnCount();
836 String[] primaryKeys = new String[icolCount];
837 int k = 0;
838 while (rs2.next()) {
839 primaryKeys[k] = rs2.getString("COLUMN_NAME");
840 k++;
841 }
842
843 if (rs.next()) {
844 for (String pkName : primaryKeys) {
845 if (!isPKNameValid(pkName, rsmd)) {
846
847 /* We came here as one of the primary keys
848 * of the table is not present in the cached
849 * rowset object, it should be an autoincrement column
850 * and not included while creating CachedRowSet
851 * Object, proceed to check for other primary keys
852 */
853 continue;
854 }
855
856 Object crsPK = crs.getObject(pkName);
857 if (crsPK == null) {
858 /*
859 * It is possible that the PK is null on some databases
860 * and will be filled in at insert time (MySQL for example)
861 */
862 break;
863 }
864
865 String rsPK = rs.getObject(pkName).toString();
866 if (crsPK.toString().equals(rsPK)) {
867 returnVal = true;
868 this.crsResolve.moveToInsertRow();
869 for (int i = 1; i <= icolCount; i++) {
870 String colname = (rs.getMetaData()).getColumnName(i);
871 if (colname.equals(pkName))
872 this.crsResolve.updateObject(i,rsPK);
873 else
874 this.crsResolve.updateNull(i);
875 }
876 this.crsResolve.insertRow();
877 this.crsResolve.moveToCurrentRow();
878 }
879 }
880 }
881
882 if (returnVal) {
883 return returnVal;
884 }
885
886 try {
887 for (int i = 1; i <= icolCount; i++) {
888 Object obj = crs.getObject(i);
889 if (obj != null) {
890 pstmt.setObject(i, obj);
891 } else {
892 pstmt.setNull(i,crs.getMetaData().getColumnType(i));
893 }
894 }
895
896 pstmt.executeUpdate();
897 return false;
898
899 } catch (SQLException ex) {
900 /*
901 * Cursor will come here if executeUpdate fails.
902 * There can be many reasons why the insertion failed,
903 * one can be violation of primary key.
904 * Hence we cannot exactly identify why the insertion failed,
905 * present the current row as a null row to the caller.
906 */
907 this.crsResolve.moveToInsertRow();
908
909 for (int i = 1; i <= icolCount; i++) {
910 this.crsResolve.updateNull(i);
911 }
912
913 this.crsResolve.insertRow();
914 this.crsResolve.moveToCurrentRow();
915
916 return true;
917 }
918 }
919 }
920
921 /**
922 * Deletes the row in the underlying data source that corresponds to
923 * a row that has been deleted in the given {@code CachedRowSet} object
924 * and returns {@code false} if the deletion was successful.
925 * <P>
926 * This method is called internally by this writer's {@code writeData}
927 * method when a row in the rowset has been deleted. The values in the
928 * deleted row are the same as those that are stored in the original row
929 * of the given {@code CachedRowSet} object. If the values in the
930 * original row differ from the row in the underlying data source, the row
931 * in the data source is not deleted, and {@code deleteOriginalRow}
932 * returns {@code true} to indicate that there was a conflict.
933 *
934 *
935 * @return {@code false} if the deletion was successful, which means that
936 * there was no conflict; {@code true} otherwise
937 * @throws SQLException if there was a database access error
938 */
939 private boolean deleteOriginalRow(CachedRowSet crs, CachedRowSetImpl crsRes) throws SQLException {
940 PreparedStatement pstmt;
941 int i;
942 int idx = 0;
943 String strSelect;
944 // Select the row from the database.
945 ResultSet origVals = crs.getOriginalRow();
946 origVals.next();
947
948 deleteWhere = buildWhereClause(deleteWhere, origVals);
949 pstmt = con.prepareStatement(selectCmd + deleteWhere,
950 ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
951
952 for (i = 0; i < keyCols.length; i++) {
953 if (params[i] != null) {
954 pstmt.setObject(++idx, params[i]);
955 } else {
956 continue;
957 }
958 }
959
960 try {
961 pstmt.setMaxRows(crs.getMaxRows());
962 pstmt.setMaxFieldSize(crs.getMaxFieldSize());
963 pstmt.setEscapeProcessing(crs.getEscapeProcessing());
964 pstmt.setQueryTimeout(crs.getQueryTimeout());
965 } catch (Exception ex) {
966 /*
967 * Older driver don't support these operations...
968 */
969 ;
970 }
971
972 ResultSet rs = pstmt.executeQuery();
973
974 if (rs.next() == true) {
975 if (rs.next()) {
976 // more than one row
977 return true;
978 }
979 rs.first();
980
981 // Now check all the values in rs to be same in
982 // db also before actually going ahead with deleting
983 boolean boolChanged = false;
984
985 crsRes.moveToInsertRow();
986
987 for (i = 1; i <= crs.getMetaData().getColumnCount(); i++) {
988
989 Object original = origVals.getObject(i);
990 Object changed = rs.getObject(i);
991
992 if(original != null && changed != null ) {
993 if(! (original.toString()).equals(changed.toString()) ) {
994 boolChanged = true;
995 crsRes.updateObject(i,origVals.getObject(i));
996 }
997 } else {
998 crsRes.updateNull(i);
999 }
1000 }
1001
1002 crsRes.insertRow();
1003 crsRes.moveToCurrentRow();
1004
1005 if(boolChanged) {
1006 // do not delete as values in db have changed
1007 // deletion will not happen for this row from db
1008 // exit now returning true. i.e. conflict
1009 return true;
1010 } else {
1011 // delete the row.
1012 // Go ahead with deleting,
1013 // don't do anything here
1014 }
1015
1016 String cmd = deleteCmd + deleteWhere;
1017 pstmt = con.prepareStatement(cmd);
1018
1019 idx = 0;
1020 for (i = 0; i < keyCols.length; i++) {
1021 if (params[i] != null) {
1022 pstmt.setObject(++idx, params[i]);
1023 } else {
1024 continue;
1025 }
1026 }
1027
1028 if (pstmt.executeUpdate() != 1) {
1029 return true;
1030 }
1031 pstmt.close();
1032 } else {
1033 // didn't find the row
1034 return true;
1035 }
1036
1037 // no conflict
1038 return false;
1039 }
1040
1041 /**
1042 * Sets the reader for this writer to the given reader.
1043 *
1044 * @throws SQLException if a database access error occurs
1045 */
1046 public void setReader(CachedRowSetReader reader) throws SQLException {
1047 this.reader = reader;
1048 }
1049
1050 /**
1051 * Gets the reader for this writer.
1052 *
1053 * @throws SQLException if a database access error occurs
1054 */
1055 public CachedRowSetReader getReader() throws SQLException {
1056 return reader;
1057 }
1058
1059 /**
1060 * Composes a {@code SELECT}, {@code UPDATE}, {@code INSERT},
1061 * and {@code DELETE} statement that can be used by this writer to
1062 * write data to the data source backing the given {@code CachedRowSet}
1063 * object.
1064 *
1065 * @param caller a {@code CachedRowSet} object for which this
1066 * {@code CachedRowSetWriter} object is the writer
1067 * @throws SQLException if a database access error occurs
1068 */
1069 private void initSQLStatements(CachedRowSet caller) throws SQLException {
1070
1071 int i;
1072
1073 callerMd = caller.getMetaData();
1074 callerColumnCount = callerMd.getColumnCount();
1075 if (callerColumnCount < 1)
1076 // No data, so return.
1077 return;
1078
1079 /*
1080 * If the RowSet has a Table name we should use it.
1081 * This is really a hack to get round the fact that
1082 * a lot of the jdbc drivers can't provide the tab.
1083 */
1084 String table = caller.getTableName();
1085 if (table == null) {
1086 /*
1087 * attempt to build a table name using the info
1088 * that the driver gave us for the first column
1089 * in the source result set.
1090 */
1091 table = callerMd.getTableName(1);
1092 if (table == null || table.length() == 0) {
1093 throw new SQLException(resBundle.handleGetObject("crswriter.tname").toString());
1094 }
1095 }
1096 String catalog = callerMd.getCatalogName(1);
1097 String schema = callerMd.getSchemaName(1);
1098 DatabaseMetaData dbmd = con.getMetaData();
1099
1100 /*
1101 * Compose a SELECT statement. There are three parts.
1102 */
1103
1104 // Project List
1105 selectCmd = "SELECT ";
1106 for (i=1; i <= callerColumnCount; i++) {
1107 selectCmd += callerMd.getColumnName(i);
1108 if ( i < callerMd.getColumnCount() )
1109 selectCmd += ", ";
1110 else
1111 selectCmd += " ";
1112 }
1113
1114 // FROM clause.
1115 selectCmd += "FROM " + buildTableName(dbmd, catalog, schema, table);
1116
1117 /*
1118 * Compose an UPDATE statement.
1119 */
1120 updateCmd = "UPDATE " + buildTableName(dbmd, catalog, schema, table);
1121
1122
1123 /**
1124 * The following block of code is for checking a particular type of
1125 * query where in there is a where clause. Without this block, if a
1126 * SQL statement is built the "where" clause will appear twice hence
1127 * the DB errors out and a SQLException is thrown. This code also
1128 * considers that the where clause is in the right place as the
1129 * CachedRowSet object would already have been populated with this
1130 * query before coming to this point.
1131 **/
1132
1133 String tempupdCmd = updateCmd.toLowerCase();
1134
1135 int idxupWhere = tempupdCmd.indexOf("where");
1136
1137 if(idxupWhere != -1)
1138 {
1139 updateCmd = updateCmd.substring(0,idxupWhere);
1140 }
1141 updateCmd += "SET ";
1142
1143 /*
1144 * Compose an INSERT statement.
1145 */
1146 insertCmd = "INSERT INTO " + buildTableName(dbmd, catalog, schema, table);
1147 // Column list
1148 insertCmd += "(";
1149 for (i=1; i <= callerColumnCount; i++) {
1150 insertCmd += callerMd.getColumnName(i);
1151 if ( i < callerMd.getColumnCount() )
1152 insertCmd += ", ";
1153 else
1154 insertCmd += ") VALUES (";
1155 }
1156 for (i=1; i <= callerColumnCount; i++) {
1157 insertCmd += "?";
1158 if (i < callerColumnCount)
1159 insertCmd += ", ";
1160 else
1161 insertCmd += ")";
1162 }
1163
1164 /*
1165 * Compose a DELETE statement.
1166 */
1167 deleteCmd = "DELETE FROM " + buildTableName(dbmd, catalog, schema, table);
1168
1169 /*
1170 * set the key desriptors that will be
1171 * needed to construct where clauses.
1172 */
1173 buildKeyDesc(caller);
1174 }
1175
1176 /**
1177 * Returns a fully qualified table name built from the given catalog and
1178 * table names. The given metadata object is used to get the proper order
1179 * and separator.
1180 *
1181 * @param dbmd a {@code DatabaseMetaData} object that contains metadata
1182 * about this writer's {@code CachedRowSet} object
1183 * @param catalog a {@code String} object with the rowset's catalog
1184 * name
1185 * @param table a {@code String} object with the name of the table from
1186 * which this writer's rowset was derived
1187 * @return a {@code String} object with the fully qualified name of the
1188 * table from which this writer's rowset was derived
1189 * @throws SQLException if a database access error occurs
1190 */
1191 private String buildTableName(DatabaseMetaData dbmd,
1192 String catalog, String schema, String table) throws SQLException {
1193
1194 // trim all the leading and trailing whitespaces,
1195 // white spaces can never be catalog, schema or a table name.
1196
1197 String cmd = "";
1198
1199 catalog = catalog.trim();
1200 schema = schema.trim();
1201 table = table.trim();
1202
1203 if (dbmd.isCatalogAtStart() == true) {
1204 if (catalog != null && catalog.length() > 0) {
1205 cmd += catalog + dbmd.getCatalogSeparator();
1206 }
1207 if (schema != null && schema.length() > 0) {
1208 cmd += schema + ".";
1209 }
1210 cmd += table;
1211 } else {
1212 if (schema != null && schema.length() > 0) {
1213 cmd += schema + ".";
1214 }
1215 cmd += table;
1216 if (catalog != null && catalog.length() > 0) {
1217 cmd += dbmd.getCatalogSeparator() + catalog;
1218 }
1219 }
1220 cmd += " ";
1221 return cmd;
1222 }
1223
1224 /**
1225 * Assigns to the given {@code CachedRowSet} object's
1226 * {@code params}
1227 * field an array whose length equals the number of columns needed
1228 * to uniquely identify a row in the rowset. The array is given
1229 * values by the method {@code buildWhereClause}.
1230 * <P>
1231 * If the {@code CachedRowSet} object's {@code keyCols}
1232 * field has length {@code 0} or is {@code null}, the array
1233 * is set with the column number of every column in the rowset.
1234 * Otherwise, the array in the field {@code keyCols} is set with only
1235 * the column numbers of the columns that are required to form a unique
1236 * identifier for a row.
1237 *
1238 * @param crs the {@code CachedRowSet} object for which this
1239 * {@code CachedRowSetWriter} object is the writer
1240 *
1241 * @throws SQLException if a database access error occurs
1242 */
1243 private void buildKeyDesc(CachedRowSet crs) throws SQLException {
1244
1245 keyCols = crs.getKeyColumns();
1246 ResultSetMetaData resultsetmd = crs.getMetaData();
1247 if (keyCols == null || keyCols.length == 0) {
1248 ArrayList<Integer> listKeys = new ArrayList<Integer>();
1249
1250 for (int i = 0; i < callerColumnCount; i++ ) {
1251 if(resultsetmd.getColumnType(i+1) != java.sql.Types.CLOB &&
1252 resultsetmd.getColumnType(i+1) != java.sql.Types.STRUCT &&
1253 resultsetmd.getColumnType(i+1) != java.sql.Types.SQLXML &&
1254 resultsetmd.getColumnType(i+1) != java.sql.Types.BLOB &&
1255 resultsetmd.getColumnType(i+1) != java.sql.Types.ARRAY &&
1256 resultsetmd.getColumnType(i+1) != java.sql.Types.OTHER )
1257 listKeys.add(i+1);
1258 }
1259 keyCols = new int[listKeys.size()];
1260 for (int i = 0; i < listKeys.size(); i++ )
1261 keyCols[i] = listKeys.get(i);
1262 }
1263 params = new Object[keyCols.length];
1264 }
1265
1266 /**
1267 * Constructs an SQL {@code WHERE} clause using the given
1268 * string as a starting point. The resulting clause will contain
1269 * a column name and " = ?" for each key column, that is, each column
1270 * that is needed to form a unique identifier for a row in the rowset.
1271 * This {@code WHERE} clause can be added to
1272 * a {@code PreparedStatement} object that updates, inserts, or
1273 * deletes a row.
1274 * <P>
1275 * This method uses the given result set to access values in the
1276 * {@code CachedRowSet} object that called this writer. These
1277 * values are used to build the array of parameters that will serve as
1278 * replacements for the "?" parameter placeholders in the
1279 * {@code PreparedStatement} object that is sent to the
1280 * {@code CachedRowSet} object's underlying data source.
1281 *
1282 * @param whereClause a {@code String} object that is an empty
1283 * string ("")
1284 * @param rs a {@code ResultSet} object that can be used
1285 * to access the {@code CachedRowSet} object's data
1286 * @return a {@code WHERE} clause of the form "{@code WHERE}
1287 * columnName = ? AND columnName = ? AND columnName = ? ..."
1288 * @throws SQLException if a database access error occurs
1289 */
1290 private String buildWhereClause(String whereClause,
1291 ResultSet rs) throws SQLException {
1292 whereClause = "WHERE ";
1293
1294 for (int i = 0; i < keyCols.length; i++) {
1295 if (i > 0) {
1296 whereClause += "AND ";
1297 }
1298 whereClause += callerMd.getColumnName(keyCols[i]);
1299 params[i] = rs.getObject(keyCols[i]);
1300 if (rs.wasNull() == true) {
1301 whereClause += " IS NULL ";
1302 } else {
1303 whereClause += " = ? ";
1304 }
1305 }
1306 return whereClause;
1307 }
1308
1309 void updateResolvedConflictToDB(CachedRowSet crs, Connection con) throws SQLException {
1310 //String updateExe = ;
1311 PreparedStatement pStmt ;
1312 String strWhere = "WHERE " ;
1313 String strExec =" ";
1314 String strUpdate = "UPDATE ";
1315 int icolCount = crs.getMetaData().getColumnCount();
1316 int keyColumns[] = crs.getKeyColumns();
1317 Object param[];
1318 String strSet="";
1319
1320 strWhere = buildWhereClause(strWhere, crs);
1321
1322 if (keyColumns == null || keyColumns.length == 0) {
1323 keyColumns = new int[icolCount];
1324 for (int i = 0; i < keyColumns.length; ) {
1325 keyColumns[i] = ++i;
1326 }
1327 }
1328 param = new Object[keyColumns.length];
1329
1330 strUpdate = "UPDATE " + buildTableName(con.getMetaData(),
1331 crs.getMetaData().getCatalogName(1),
1332 crs.getMetaData().getSchemaName(1),
1333 crs.getTableName());
1334
1335 // changed or updated values will become part of
1336 // set clause here
1337 strUpdate += "SET ";
1338
1339 boolean first = true;
1340
1341 for (int i=1; i<=icolCount;i++) {
1342 if (crs.columnUpdated(i)) {
1343 if (first == false) {
1344 strSet += ", ";
1345 }
1346 strSet += crs.getMetaData().getColumnName(i);
1347 strSet += " = ? ";
1348 first = false;
1349 } //end if
1350 } //end for
1351
1352 // keycols will become part of where clause
1353 strUpdate += strSet;
1354 strWhere = "WHERE ";
1355
1356 for (int i = 0; i < keyColumns.length; i++) {
1357 if (i > 0) {
1358 strWhere += "AND ";
1359 }
1360 strWhere += crs.getMetaData().getColumnName(keyColumns[i]);
1361 param[i] = crs.getObject(keyColumns[i]);
1362 if (crs.wasNull() == true) {
1363 strWhere += " IS NULL ";
1364 } else {
1365 strWhere += " = ? ";
1366 }
1367 }
1368 strUpdate += strWhere;
1369
1370 pStmt = con.prepareStatement(strUpdate);
1371
1372 int idx =0;
1373 for (int i = 0; i < icolCount; i++) {
1374 if(crs.columnUpdated(i+1)) {
1375 Object obj = crs.getObject(i+1);
1376 if (obj != null) {
1377 pStmt.setObject(++idx, obj);
1378 } else {
1379 pStmt.setNull(i + 1,crs.getMetaData().getColumnType(i + 1));
1380 } //end if ..else
1381 } //end if crs.column...
1382 } //end for
1383
1384 // Set the key cols for after WHERE =? clause
1385 for (int i = 0; i < keyColumns.length; i++) {
1386 if (param[i] != null) {
1387 pStmt.setObject(++idx, param[i]);
1388 }
1389 }
1390
1391 int id = pStmt.executeUpdate();
1392 }
1393
1394
1395 /**
1396 *
1397 */
1398 public void commit() throws SQLException {
1399 con.commit();
1400 if (reader.getCloseConnection() == true) {
1401 con.close();
1402 }
1403 }
1404
1405 public void commit(CachedRowSetImpl crs, boolean updateRowset) throws SQLException {
1406 con.commit();
1407 if(updateRowset) {
1408 if(crs.getCommand() != null)
1409 crs.execute(con);
1410 }
1411
1412 if (reader.getCloseConnection() == true) {
1413 con.close();
1414 }
1415 }
1416
1417 /**
1418 *
1419 */
1420 public void rollback() throws SQLException {
1421 con.rollback();
1422 if (reader.getCloseConnection() == true) {
1423 con.close();
1424 }
1425 }
1426
1427 /**
1428 *
1429 */
1430 public void rollback(Savepoint s) throws SQLException {
1431 con.rollback(s);
1432 if (reader.getCloseConnection() == true) {
1433 con.close();
1434 }
1435 }
1436
1437 private void readObject(ObjectInputStream ois) throws IOException, ClassNotFoundException {
1438 // Default state initialization happens here
1439 ois.defaultReadObject();
1440 // Initialization of Res Bundle happens here .
1441 try {
1442 resBundle = JdbcRowSetResourceBundle.getJdbcRowSetResourceBundle();
1443 } catch(IOException ioe) {
1444 throw new RuntimeException(ioe);
1445 }
1446
1447 }
1448
1449 static final long serialVersionUID =-8506030970299413976L;
1450
1451 /**
1452 * Validate whether the Primary Key is known to the CachedRowSet. If it is
1453 * not, it is an auto-generated key
1454 * @param pk - Primary Key to validate
1455 * @param rsmd - ResultSetMetadata for the RowSet
1456 * @return true if found, false otherwise (auto generated key)
1457 */
1458 private boolean isPKNameValid(String pk, ResultSetMetaData rsmd) throws SQLException {
1459 boolean isValid = false;
1460 int cols = rsmd.getColumnCount();
1461 for(int i = 1; i<= cols; i++) {
1462 String colName = rsmd.getColumnClassName(i);
1463 if(colName.equalsIgnoreCase(pk)) {
1464 isValid = true;
1465 break;
1466 }
1467 }
1468
1469 return isValid;
1470 }
1471
1472 }