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 @SuppressWarnings("deprecation")
578 Object tmp = c.newInstance();
579 obj = (SQLData)tmp;
580 } catch (Exception ex) {
581 throw new SQLException("Unable to Instantiate: ", ex);
582 }
583 // get the attributes from the struct
584 Object attribs[] = s.getAttributes(map);
585 // create the SQLInput "stream"
586 SQLInputImpl sqlInput = new SQLInputImpl(attribs, map);
587 // read the values...
588 obj.readSQL(sqlInput, s.getSQLTypeName());
589 rsval = obj;
590 }
591 } else if (rsval instanceof SQLData) {
592 rsval = new SerialStruct((SQLData)rsval, map);
593 } else if (rsval instanceof Blob) {
594 rsval = new SerialBlob((Blob)rsval);
595 } else if (rsval instanceof Clob) {
596 rsval = new SerialClob((Clob)rsval);
597 } else if (rsval instanceof java.sql.Array) {
598 rsval = new SerialArray((java.sql.Array)rsval, map);
599 }
600
601 // reset boolNull if it had been set
602 boolNull = true;
603
604 /** This addtional checking has been added when the current value
605 * in the DB is null, but the DB had a different value when the
606 * data was actaully fetched into the CachedRowSet.
607 **/
608
609 if(rsval == null && orig != null) {
610 // value in db has changed
611 // don't proceed with synchronization
612 // get the value in db and pass it to the resolver.
613
614 iChangedValsinDbOnly++;
615 // Set the boolNull to false,
616 // in order to set the actual value;
617 boolNull = false;
618 objVal = rsval;
619 }
620
621 /** Adding the checking for rsval to be "not" null or else
622 * it would through a NullPointerException when the values
623 * are compared.
624 **/
625
626 else if(rsval != null && (!rsval.equals(orig)))
627 {
628 // value in db has changed
629 // don't proceed with synchronization
630 // get the value in db and pass it to the resolver.
631
632 iChangedValsinDbOnly++;
633 // Set the boolNull to false,
634 // in order to set the actual value;
635 boolNull = false;
636 objVal = rsval;
637 } else if ( (orig == null || curr == null) ) {
638
639 /** Adding the additonal condition of checking for "flag"
640 * boolean variable, which would otherwise result in
641 * building a invalid query, as the comma would not be
642 * added to the query string.
643 **/
644
645 if (first == false || flag == false) {
646 updateExec += ", ";
647 }
648 updateExec += crs.getMetaData().getColumnName(i);
649 cols.add(i);
650 updateExec += " = ? ";
651 first = false;
652
653 /** Adding the extra condition for orig to be "not" null as the
654 * condition for orig to be null is take prior to this, if this
655 * is not added it will result in a NullPointerException when
656 * the values are compared.
657 **/
658
659 } else if (orig.equals(curr)) {
660 colsNotChanged++;
661 //nothing to update in this case since values are equal
662
663 /** Adding the extra condition for orig to be "not" null as the
664 * condition for orig to be null is take prior to this, if this
665 * is not added it will result in a NullPointerException when
666 * the values are compared.
667 **/
668
669 } else if(orig.equals(curr) == false) {
670 // When values from db and values in CachedRowSet are not equal,
671 // if db value is same as before updation for each col in
672 // the row before fetching into CachedRowSet,
673 // only then we go ahead with updation, else we
674 // throw SyncProviderException.
675
676 // if value has changed in db after fetching from db
677 // for some cols of the row and at the same time, some other cols
678 // have changed in CachedRowSet, no synchronization happens
679
680 // Synchronization happens only when data when fetching is
681 // same or at most has changed in cachedrowset
682
683 // check orig value with what is there in crs for a column
684 // before updation in crs.
685
686 if(crs.columnUpdated(i)) {
687 if(rsval.equals(orig)) {
688 // At this point we are sure that
689 // the value updated in crs was from
690 // what is in db now and has not changed
691 if (flag == false || first == false) {
692 updateExec += ", ";
693 }
694 updateExec += crs.getMetaData().getColumnName(i);
695 cols.add(i);
696 updateExec += " = ? ";
697 flag = false;
698 } else {
699 // Here the value has changed in the db after
700 // data was fetched
701 // Plus store this row from CachedRowSet and keep it
702 // in a new CachedRowSet
703 boolNull= false;
704 objVal = rsval;
705 iChangedValsInDbAndCRS++;
706 }
707 }
708 }
709
710 if(!boolNull) {
711 this.crsResolve.updateObject(i,objVal);
712 } else {
713 this.crsResolve.updateNull(i);
714 }
715 } //end for
716
717 rs.close();
718 pstmt.close();
719
720 this.crsResolve.insertRow();
721 this.crsResolve.moveToCurrentRow();
722
723 /**
724 * if nothing has changed return now - this can happen
725 * if column is updated to the same value.
726 * if colsNotChanged == callerColumnCount implies we are updating
727 * the database with ALL COLUMNS HAVING SAME VALUES,
728 * so skip going to database, else do as usual.
729 **/
730 if ( (first == false && cols.size() == 0) ||
731 colsNotChanged == callerColumnCount ) {
732 return false;
733 }
734
735 if(iChangedValsInDbAndCRS != 0 || iChangedValsinDbOnly != 0) {
736 return true;
737 }
738
739
740 updateExec += updateWhere;
741
742 pstmt = con.prepareStatement(updateExec);
743
744 // Comments needed here
745 for (i = 0; i < cols.size(); i++) {
746 Object obj = crs.getObject(cols.get(i));
747 if (obj != null)
748 pstmt.setObject(i + 1, obj);
749 else
750 pstmt.setNull(i + 1,crs.getMetaData().getColumnType(i + 1));
751 }
752 idx = i;
753
754 // Comments needed here
755 for (i = 0; i < keyCols.length; i++) {
756 if (params[i] != null) {
757 pstmt.setObject(++idx, params[i]);
758 } else {
759 continue;
760 }
761 }
762
763 i = pstmt.executeUpdate();
764
765 /**
766 * i should be equal to 1(row count), because we update
767 * one row(returned as row count) at a time, if all goes well.
768 * if 1 != 1, this implies we have not been able to
769 * do updations properly i.e there is a conflict in database
770 * versus what is in CachedRowSet for this particular row.
771 **/
772
773 return false;
774
775 } else {
776 /**
777 * Cursor will be here, if the ResultSet may not return even a single row
778 * i.e. we can't find the row where to update because it has been deleted
779 * etc. from the db.
780 * Present the whole row as null to user, to force null to be sync'ed
781 * and hence nothing to be synced.
782 *
783 * NOTE:
784 * ------
785 * In the database if a column that is mapped to java.sql.Types.REAL stores
786 * a Double value and is compared with value got from ResultSet.getFloat()
787 * no row is retrieved and will throw a SyncProviderException. For details
788 * see bug Id 5053830
789 **/
790 return true;
791 }
792 } catch (SQLException ex) {
793 ex.printStackTrace();
794 // if executeUpdate fails it will come here,
795 // update crsResolve with null rows
796 this.crsResolve.moveToInsertRow();
797
798 for(i = 1; i <= callerColumnCount; i++) {
799 this.crsResolve.updateNull(i);
800 }
801
802 this.crsResolve.insertRow();
803 this.crsResolve.moveToCurrentRow();
804
805 return true;
806 }
807 }
808
809 /**
810 * Inserts a row that has been inserted into the given
811 * {@code CachedRowSet} object into the data source from which
812 * the rowset is derived, returning {@code false} if the insertion
813 * was successful.
814 *
815 * @param crs the {@code CachedRowSet} object that has had a row inserted
816 * and to whose underlying data source the row will be inserted
817 * @param pstmt the {@code PreparedStatement} object that will be used
818 * to execute the insertion
819 * @return {@code false} to indicate that the insertion was successful;
820 * {@code true} otherwise
821 * @throws SQLException if a database access error occurs
822 */
823 private boolean insertNewRow(CachedRowSet crs,
824 PreparedStatement pstmt, CachedRowSetImpl crsRes) throws SQLException {
825
826 boolean returnVal = false;
827
828 try (PreparedStatement pstmtSel = con.prepareStatement(selectCmd,
829 ResultSet.TYPE_SCROLL_SENSITIVE,
830 ResultSet.CONCUR_READ_ONLY);
831 ResultSet rs = pstmtSel.executeQuery();
832 ResultSet rs2 = con.getMetaData().getPrimaryKeys(null, null,
833 crs.getTableName())
834 ) {
835
836 ResultSetMetaData rsmd = crs.getMetaData();
837 int icolCount = rsmd.getColumnCount();
838 String[] primaryKeys = new String[icolCount];
839 int k = 0;
840 while (rs2.next()) {
841 primaryKeys[k] = rs2.getString("COLUMN_NAME");
842 k++;
843 }
844
845 if (rs.next()) {
846 for (String pkName : primaryKeys) {
847 if (!isPKNameValid(pkName, rsmd)) {
848
849 /* We came here as one of the primary keys
850 * of the table is not present in the cached
851 * rowset object, it should be an autoincrement column
852 * and not included while creating CachedRowSet
853 * Object, proceed to check for other primary keys
854 */
855 continue;
856 }
857
858 Object crsPK = crs.getObject(pkName);
859 if (crsPK == null) {
860 /*
861 * It is possible that the PK is null on some databases
862 * and will be filled in at insert time (MySQL for example)
863 */
864 break;
865 }
866
867 String rsPK = rs.getObject(pkName).toString();
868 if (crsPK.toString().equals(rsPK)) {
869 returnVal = true;
870 this.crsResolve.moveToInsertRow();
871 for (int i = 1; i <= icolCount; i++) {
872 String colname = (rs.getMetaData()).getColumnName(i);
873 if (colname.equals(pkName))
874 this.crsResolve.updateObject(i,rsPK);
875 else
876 this.crsResolve.updateNull(i);
877 }
878 this.crsResolve.insertRow();
879 this.crsResolve.moveToCurrentRow();
880 }
881 }
882 }
883
884 if (returnVal) {
885 return returnVal;
886 }
887
888 try {
889 for (int i = 1; i <= icolCount; i++) {
890 Object obj = crs.getObject(i);
891 if (obj != null) {
892 pstmt.setObject(i, obj);
893 } else {
894 pstmt.setNull(i,crs.getMetaData().getColumnType(i));
895 }
896 }
897
898 pstmt.executeUpdate();
899 return false;
900
901 } catch (SQLException ex) {
902 /*
903 * Cursor will come here if executeUpdate fails.
904 * There can be many reasons why the insertion failed,
905 * one can be violation of primary key.
906 * Hence we cannot exactly identify why the insertion failed,
907 * present the current row as a null row to the caller.
908 */
909 this.crsResolve.moveToInsertRow();
910
911 for (int i = 1; i <= icolCount; i++) {
912 this.crsResolve.updateNull(i);
913 }
914
915 this.crsResolve.insertRow();
916 this.crsResolve.moveToCurrentRow();
917
918 return true;
919 }
920 }
921 }
922
923 /**
924 * Deletes the row in the underlying data source that corresponds to
925 * a row that has been deleted in the given {@code CachedRowSet} object
926 * and returns {@code false} if the deletion was successful.
927 * <P>
928 * This method is called internally by this writer's {@code writeData}
929 * method when a row in the rowset has been deleted. The values in the
930 * deleted row are the same as those that are stored in the original row
931 * of the given {@code CachedRowSet} object. If the values in the
932 * original row differ from the row in the underlying data source, the row
933 * in the data source is not deleted, and {@code deleteOriginalRow}
934 * returns {@code true} to indicate that there was a conflict.
935 *
936 *
937 * @return {@code false} if the deletion was successful, which means that
938 * there was no conflict; {@code true} otherwise
939 * @throws SQLException if there was a database access error
940 */
941 private boolean deleteOriginalRow(CachedRowSet crs, CachedRowSetImpl crsRes) throws SQLException {
942 PreparedStatement pstmt;
943 int i;
944 int idx = 0;
945 String strSelect;
946 // Select the row from the database.
947 ResultSet origVals = crs.getOriginalRow();
948 origVals.next();
949
950 deleteWhere = buildWhereClause(deleteWhere, origVals);
951 pstmt = con.prepareStatement(selectCmd + deleteWhere,
952 ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
953
954 for (i = 0; i < keyCols.length; i++) {
955 if (params[i] != null) {
956 pstmt.setObject(++idx, params[i]);
957 } else {
958 continue;
959 }
960 }
961
962 try {
963 pstmt.setMaxRows(crs.getMaxRows());
964 pstmt.setMaxFieldSize(crs.getMaxFieldSize());
965 pstmt.setEscapeProcessing(crs.getEscapeProcessing());
966 pstmt.setQueryTimeout(crs.getQueryTimeout());
967 } catch (Exception ex) {
968 /*
969 * Older driver don't support these operations...
970 */
971 ;
972 }
973
974 ResultSet rs = pstmt.executeQuery();
975
976 if (rs.next() == true) {
977 if (rs.next()) {
978 // more than one row
979 return true;
980 }
981 rs.first();
982
983 // Now check all the values in rs to be same in
984 // db also before actually going ahead with deleting
985 boolean boolChanged = false;
986
987 crsRes.moveToInsertRow();
988
989 for (i = 1; i <= crs.getMetaData().getColumnCount(); i++) {
990
991 Object original = origVals.getObject(i);
992 Object changed = rs.getObject(i);
993
994 if(original != null && changed != null ) {
995 if(! (original.toString()).equals(changed.toString()) ) {
996 boolChanged = true;
997 crsRes.updateObject(i,origVals.getObject(i));
998 }
999 } else {
1000 crsRes.updateNull(i);
1001 }
1002 }
1003
1004 crsRes.insertRow();
1005 crsRes.moveToCurrentRow();
1006
1007 if(boolChanged) {
1008 // do not delete as values in db have changed
1009 // deletion will not happen for this row from db
1010 // exit now returning true. i.e. conflict
1011 return true;
1012 } else {
1013 // delete the row.
1014 // Go ahead with deleting,
1015 // don't do anything here
1016 }
1017
1018 String cmd = deleteCmd + deleteWhere;
1019 pstmt = con.prepareStatement(cmd);
1020
1021 idx = 0;
1022 for (i = 0; i < keyCols.length; i++) {
1023 if (params[i] != null) {
1024 pstmt.setObject(++idx, params[i]);
1025 } else {
1026 continue;
1027 }
1028 }
1029
1030 if (pstmt.executeUpdate() != 1) {
1031 return true;
1032 }
1033 pstmt.close();
1034 } else {
1035 // didn't find the row
1036 return true;
1037 }
1038
1039 // no conflict
1040 return false;
1041 }
1042
1043 /**
1044 * Sets the reader for this writer to the given reader.
1045 *
1046 * @throws SQLException if a database access error occurs
1047 */
1048 public void setReader(CachedRowSetReader reader) throws SQLException {
1049 this.reader = reader;
1050 }
1051
1052 /**
1053 * Gets the reader for this writer.
1054 *
1055 * @throws SQLException if a database access error occurs
1056 */
1057 public CachedRowSetReader getReader() throws SQLException {
1058 return reader;
1059 }
1060
1061 /**
1062 * Composes a {@code SELECT}, {@code UPDATE}, {@code INSERT},
1063 * and {@code DELETE} statement that can be used by this writer to
1064 * write data to the data source backing the given {@code CachedRowSet}
1065 * object.
1066 *
1067 * @param caller a {@code CachedRowSet} object for which this
1068 * {@code CachedRowSetWriter} object is the writer
1069 * @throws SQLException if a database access error occurs
1070 */
1071 private void initSQLStatements(CachedRowSet caller) throws SQLException {
1072
1073 int i;
1074
1075 callerMd = caller.getMetaData();
1076 callerColumnCount = callerMd.getColumnCount();
1077 if (callerColumnCount < 1)
1078 // No data, so return.
1079 return;
1080
1081 /*
1082 * If the RowSet has a Table name we should use it.
1083 * This is really a hack to get round the fact that
1084 * a lot of the jdbc drivers can't provide the tab.
1085 */
1086 String table = caller.getTableName();
1087 if (table == null) {
1088 /*
1089 * attempt to build a table name using the info
1090 * that the driver gave us for the first column
1091 * in the source result set.
1092 */
1093 table = callerMd.getTableName(1);
1094 if (table == null || table.length() == 0) {
1095 throw new SQLException(resBundle.handleGetObject("crswriter.tname").toString());
1096 }
1097 }
1098 String catalog = callerMd.getCatalogName(1);
1099 String schema = callerMd.getSchemaName(1);
1100 DatabaseMetaData dbmd = con.getMetaData();
1101
1102 /*
1103 * Compose a SELECT statement. There are three parts.
1104 */
1105
1106 // Project List
1107 selectCmd = "SELECT ";
1108 for (i=1; i <= callerColumnCount; i++) {
1109 selectCmd += callerMd.getColumnName(i);
1110 if ( i < callerMd.getColumnCount() )
1111 selectCmd += ", ";
1112 else
1113 selectCmd += " ";
1114 }
1115
1116 // FROM clause.
1117 selectCmd += "FROM " + buildTableName(dbmd, catalog, schema, table);
1118
1119 /*
1120 * Compose an UPDATE statement.
1121 */
1122 updateCmd = "UPDATE " + buildTableName(dbmd, catalog, schema, table);
1123
1124
1125 /**
1126 * The following block of code is for checking a particular type of
1127 * query where in there is a where clause. Without this block, if a
1128 * SQL statement is built the "where" clause will appear twice hence
1129 * the DB errors out and a SQLException is thrown. This code also
1130 * considers that the where clause is in the right place as the
1131 * CachedRowSet object would already have been populated with this
1132 * query before coming to this point.
1133 **/
1134
1135 String tempupdCmd = updateCmd.toLowerCase();
1136
1137 int idxupWhere = tempupdCmd.indexOf("where");
1138
1139 if(idxupWhere != -1)
1140 {
1141 updateCmd = updateCmd.substring(0,idxupWhere);
1142 }
1143 updateCmd += "SET ";
1144
1145 /*
1146 * Compose an INSERT statement.
1147 */
1148 insertCmd = "INSERT INTO " + buildTableName(dbmd, catalog, schema, table);
1149 // Column list
1150 insertCmd += "(";
1151 for (i=1; i <= callerColumnCount; i++) {
1152 insertCmd += callerMd.getColumnName(i);
1153 if ( i < callerMd.getColumnCount() )
1154 insertCmd += ", ";
1155 else
1156 insertCmd += ") VALUES (";
1157 }
1158 for (i=1; i <= callerColumnCount; i++) {
1159 insertCmd += "?";
1160 if (i < callerColumnCount)
1161 insertCmd += ", ";
1162 else
1163 insertCmd += ")";
1164 }
1165
1166 /*
1167 * Compose a DELETE statement.
1168 */
1169 deleteCmd = "DELETE FROM " + buildTableName(dbmd, catalog, schema, table);
1170
1171 /*
1172 * set the key desriptors that will be
1173 * needed to construct where clauses.
1174 */
1175 buildKeyDesc(caller);
1176 }
1177
1178 /**
1179 * Returns a fully qualified table name built from the given catalog and
1180 * table names. The given metadata object is used to get the proper order
1181 * and separator.
1182 *
1183 * @param dbmd a {@code DatabaseMetaData} object that contains metadata
1184 * about this writer's {@code CachedRowSet} object
1185 * @param catalog a {@code String} object with the rowset's catalog
1186 * name
1187 * @param table a {@code String} object with the name of the table from
1188 * which this writer's rowset was derived
1189 * @return a {@code String} object with the fully qualified name of the
1190 * table from which this writer's rowset was derived
1191 * @throws SQLException if a database access error occurs
1192 */
1193 private String buildTableName(DatabaseMetaData dbmd,
1194 String catalog, String schema, String table) throws SQLException {
1195
1196 // trim all the leading and trailing whitespaces,
1197 // white spaces can never be catalog, schema or a table name.
1198
1199 String cmd = "";
1200
1201 catalog = catalog.trim();
1202 schema = schema.trim();
1203 table = table.trim();
1204
1205 if (dbmd.isCatalogAtStart() == true) {
1206 if (catalog != null && catalog.length() > 0) {
1207 cmd += catalog + dbmd.getCatalogSeparator();
1208 }
1209 if (schema != null && schema.length() > 0) {
1210 cmd += schema + ".";
1211 }
1212 cmd += table;
1213 } else {
1214 if (schema != null && schema.length() > 0) {
1215 cmd += schema + ".";
1216 }
1217 cmd += table;
1218 if (catalog != null && catalog.length() > 0) {
1219 cmd += dbmd.getCatalogSeparator() + catalog;
1220 }
1221 }
1222 cmd += " ";
1223 return cmd;
1224 }
1225
1226 /**
1227 * Assigns to the given {@code CachedRowSet} object's
1228 * {@code params}
1229 * field an array whose length equals the number of columns needed
1230 * to uniquely identify a row in the rowset. The array is given
1231 * values by the method {@code buildWhereClause}.
1232 * <P>
1233 * If the {@code CachedRowSet} object's {@code keyCols}
1234 * field has length {@code 0} or is {@code null}, the array
1235 * is set with the column number of every column in the rowset.
1236 * Otherwise, the array in the field {@code keyCols} is set with only
1237 * the column numbers of the columns that are required to form a unique
1238 * identifier for a row.
1239 *
1240 * @param crs the {@code CachedRowSet} object for which this
1241 * {@code CachedRowSetWriter} object is the writer
1242 *
1243 * @throws SQLException if a database access error occurs
1244 */
1245 private void buildKeyDesc(CachedRowSet crs) throws SQLException {
1246
1247 keyCols = crs.getKeyColumns();
1248 ResultSetMetaData resultsetmd = crs.getMetaData();
1249 if (keyCols == null || keyCols.length == 0) {
1250 ArrayList<Integer> listKeys = new ArrayList<Integer>();
1251
1252 for (int i = 0; i < callerColumnCount; i++ ) {
1253 if(resultsetmd.getColumnType(i+1) != java.sql.Types.CLOB &&
1254 resultsetmd.getColumnType(i+1) != java.sql.Types.STRUCT &&
1255 resultsetmd.getColumnType(i+1) != java.sql.Types.SQLXML &&
1256 resultsetmd.getColumnType(i+1) != java.sql.Types.BLOB &&
1257 resultsetmd.getColumnType(i+1) != java.sql.Types.ARRAY &&
1258 resultsetmd.getColumnType(i+1) != java.sql.Types.OTHER )
1259 listKeys.add(i+1);
1260 }
1261 keyCols = new int[listKeys.size()];
1262 for (int i = 0; i < listKeys.size(); i++ )
1263 keyCols[i] = listKeys.get(i);
1264 }
1265 params = new Object[keyCols.length];
1266 }
1267
1268 /**
1269 * Constructs an SQL {@code WHERE} clause using the given
1270 * string as a starting point. The resulting clause will contain
1271 * a column name and " = ?" for each key column, that is, each column
1272 * that is needed to form a unique identifier for a row in the rowset.
1273 * This {@code WHERE} clause can be added to
1274 * a {@code PreparedStatement} object that updates, inserts, or
1275 * deletes a row.
1276 * <P>
1277 * This method uses the given result set to access values in the
1278 * {@code CachedRowSet} object that called this writer. These
1279 * values are used to build the array of parameters that will serve as
1280 * replacements for the "?" parameter placeholders in the
1281 * {@code PreparedStatement} object that is sent to the
1282 * {@code CachedRowSet} object's underlying data source.
1283 *
1284 * @param whereClause a {@code String} object that is an empty
1285 * string ("")
1286 * @param rs a {@code ResultSet} object that can be used
1287 * to access the {@code CachedRowSet} object's data
1288 * @return a {@code WHERE} clause of the form "{@code WHERE}
1289 * columnName = ? AND columnName = ? AND columnName = ? ..."
1290 * @throws SQLException if a database access error occurs
1291 */
1292 private String buildWhereClause(String whereClause,
1293 ResultSet rs) throws SQLException {
1294 whereClause = "WHERE ";
1295
1296 for (int i = 0; i < keyCols.length; i++) {
1297 if (i > 0) {
1298 whereClause += "AND ";
1299 }
1300 whereClause += callerMd.getColumnName(keyCols[i]);
1301 params[i] = rs.getObject(keyCols[i]);
1302 if (rs.wasNull() == true) {
1303 whereClause += " IS NULL ";
1304 } else {
1305 whereClause += " = ? ";
1306 }
1307 }
1308 return whereClause;
1309 }
1310
1311 void updateResolvedConflictToDB(CachedRowSet crs, Connection con) throws SQLException {
1312 //String updateExe = ;
1313 PreparedStatement pStmt ;
1314 String strWhere = "WHERE " ;
1315 String strExec =" ";
1316 String strUpdate = "UPDATE ";
1317 int icolCount = crs.getMetaData().getColumnCount();
1318 int keyColumns[] = crs.getKeyColumns();
1319 Object param[];
1320 String strSet="";
1321
1322 strWhere = buildWhereClause(strWhere, crs);
1323
1324 if (keyColumns == null || keyColumns.length == 0) {
1325 keyColumns = new int[icolCount];
1326 for (int i = 0; i < keyColumns.length; ) {
1327 keyColumns[i] = ++i;
1328 }
1329 }
1330 param = new Object[keyColumns.length];
1331
1332 strUpdate = "UPDATE " + buildTableName(con.getMetaData(),
1333 crs.getMetaData().getCatalogName(1),
1334 crs.getMetaData().getSchemaName(1),
1335 crs.getTableName());
1336
1337 // changed or updated values will become part of
1338 // set clause here
1339 strUpdate += "SET ";
1340
1341 boolean first = true;
1342
1343 for (int i=1; i<=icolCount;i++) {
1344 if (crs.columnUpdated(i)) {
1345 if (first == false) {
1346 strSet += ", ";
1347 }
1348 strSet += crs.getMetaData().getColumnName(i);
1349 strSet += " = ? ";
1350 first = false;
1351 } //end if
1352 } //end for
1353
1354 // keycols will become part of where clause
1355 strUpdate += strSet;
1356 strWhere = "WHERE ";
1357
1358 for (int i = 0; i < keyColumns.length; i++) {
1359 if (i > 0) {
1360 strWhere += "AND ";
1361 }
1362 strWhere += crs.getMetaData().getColumnName(keyColumns[i]);
1363 param[i] = crs.getObject(keyColumns[i]);
1364 if (crs.wasNull() == true) {
1365 strWhere += " IS NULL ";
1366 } else {
1367 strWhere += " = ? ";
1368 }
1369 }
1370 strUpdate += strWhere;
1371
1372 pStmt = con.prepareStatement(strUpdate);
1373
1374 int idx =0;
1375 for (int i = 0; i < icolCount; i++) {
1376 if(crs.columnUpdated(i+1)) {
1377 Object obj = crs.getObject(i+1);
1378 if (obj != null) {
1379 pStmt.setObject(++idx, obj);
1380 } else {
1381 pStmt.setNull(i + 1,crs.getMetaData().getColumnType(i + 1));
1382 } //end if ..else
1383 } //end if crs.column...
1384 } //end for
1385
1386 // Set the key cols for after WHERE =? clause
1387 for (int i = 0; i < keyColumns.length; i++) {
1388 if (param[i] != null) {
1389 pStmt.setObject(++idx, param[i]);
1390 }
1391 }
1392
1393 int id = pStmt.executeUpdate();
1394 }
1395
1396
1397 /**
1398 *
1399 */
1400 public void commit() throws SQLException {
1401 con.commit();
1402 if (reader.getCloseConnection() == true) {
1403 con.close();
1404 }
1405 }
1406
1407 public void commit(CachedRowSetImpl crs, boolean updateRowset) throws SQLException {
1408 con.commit();
1409 if(updateRowset) {
1410 if(crs.getCommand() != null)
1411 crs.execute(con);
1412 }
1413
1414 if (reader.getCloseConnection() == true) {
1415 con.close();
1416 }
1417 }
1418
1419 /**
1420 *
1421 */
1422 public void rollback() throws SQLException {
1423 con.rollback();
1424 if (reader.getCloseConnection() == true) {
1425 con.close();
1426 }
1427 }
1428
1429 /**
1430 *
1431 */
1432 public void rollback(Savepoint s) throws SQLException {
1433 con.rollback(s);
1434 if (reader.getCloseConnection() == true) {
1435 con.close();
1436 }
1437 }
1438
1439 private void readObject(ObjectInputStream ois) throws IOException, ClassNotFoundException {
1440 // Default state initialization happens here
1441 ois.defaultReadObject();
1442 // Initialization of Res Bundle happens here .
1443 try {
1444 resBundle = JdbcRowSetResourceBundle.getJdbcRowSetResourceBundle();
1445 } catch(IOException ioe) {
1446 throw new RuntimeException(ioe);
1447 }
1448
1449 }
1450
1451 static final long serialVersionUID =-8506030970299413976L;
1452
1453 /**
1454 * Validate whether the Primary Key is known to the CachedRowSet. If it is
1455 * not, it is an auto-generated key
1456 * @param pk - Primary Key to validate
1457 * @param rsmd - ResultSetMetadata for the RowSet
1458 * @return true if found, false otherwise (auto generated key)
1459 */
1460 private boolean isPKNameValid(String pk, ResultSetMetaData rsmd) throws SQLException {
1461 boolean isValid = false;
1462 int cols = rsmd.getColumnCount();
1463 for(int i = 1; i<= cols; i++) {
1464 String colName = rsmd.getColumnClassName(i);
1465 if(colName.equalsIgnoreCase(pk)) {
1466 isValid = true;
1467 break;
1468 }
1469 }
1470
1471 return isValid;
1472 }
1473
1474 }