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</code> implementation to 47 * propagate changes back to the data source from which the rowset got its data. 48 * <P> 49 * A <code>CachedRowSetWriter</code> object, called a writer, has the public 50 * method <code>writeData</code> 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</code> 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</code>. 57 * <P> 58 * Typically the <code>SyncFactory</code> manages the <code>RowSetReader</code> and 59 * the <code>RowSetWriter</code> implementations using <code>SyncProvider</code> objects. 60 * Standard JDBC RowSet implementations provide an object instance of this 61 * writer by invoking the <code>SyncProvider.getRowSetWriter()</code> 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</code> 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</code> command that this writer will call 80 * internally. The method <code>initSQLStatements</code> 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</code> command that this writer will call 90 * internally to write data to the rowset's underlying data source. 91 * The method <code>initSQLStatements</code> builds this <code>String</code> 92 * object. 93 * 94 * @serial 95 */ 96 private String updateCmd; 97 98 /** 99 * The SQL <code>WHERE</code> clause the writer will use for update 100 * statements in the <code>PreparedStatement</code> object 101 * it sends to the underlying data source. 102 * 103 * @serial 104 */ 105 private String updateWhere; 106 107 /** 108 * The SQL <code>DELETE</code> 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</code> clause the writer will use for delete 117 * statements in the <code>PreparedStatement</code> 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</code> command that this writer will internally use 126 * to insert data into the rowset's underlying data source. The method 127 * <code>initSQLStatements</code> 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</code> object 137 * for which this <code>CachedRowSetWriter</code> 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</code> object that this 146 * writer will execute. 147 * 148 * @serial 149 */ 150 private Object[] params; 151 152 /** 153 * The <code>CachedRowSetReader</code> object that has been 154 * set as the reader for the <code>CachedRowSet</code> object 155 * for which this <code>CachedRowSetWriter</code> object is the writer. 156 * 157 * @serial 158 */ 159 private CachedRowSetReader reader; 160 161 /** 162 * The <code>ResultSetMetaData</code> object that contains information 163 * about the columns in the <code>CachedRowSet</code> object 164 * for which this <code>CachedRowSetWriter</code> object is the writer. 165 * 166 * @serial 167 */ 168 private ResultSetMetaData callerMd; 169 170 /** 171 * The number of columns in the <code>CachedRowSet</code> object 172 * for which this <code>CachedRowSetWriter</code> object is the writer. 173 * 174 * @serial 175 */ 176 private int callerColumnCount; 177 178 /** 179 * This <code>CachedRowSet<code> 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</code> object 213 * back to its underlying data source and returns <code>true</code> 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</code>. 225 * The <code>writeData</code> 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</code> does, much of which is accomplished 229 * through calls to its own internal methods. 230 * <OL> 231 * <LI>Creates a <code>CachedRowSet</code> object from the given 232 * <code>RowSet</code> 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</code> to initialize new SQL statements 241 * <UL> 242 * <LI>Builds new <code>SELECT</code>, <code>UPDATE</code>, 243 * <code>INSERT</code>, and <code>DELETE</code> statements 244 * <LI>Uses the <code>CachedRowSet</code> 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</code> object back to its underlying data source 250 * <UL> 251 * <LI>Iterates through each row of the <code>CachedRowSet</code> 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</code> 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</code> if changes to the rowset were successfully 264 * written to the rowset's underlying data source; 265 * <code>false</code> 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 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</code> object's underlying data 439 * source so that updates to the rowset are reflected in the original 440 * data source, and returns <code>false</code> if the update was successful. 441 * A return value of <code>true</code> 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</code> 449 * if a row in the <code>CachedRowSet</code> object for which this 450 * <code>CachedRowSetWriter</code> object is the writer has been updated. 451 * 452 * @return <code>false</code> if the update to the underlying data source is 453 * successful; <code>true</code> 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 788 } 789 } catch (SQLException ex) { 790 ex.printStackTrace(); 791 // if executeUpdate fails it will come here, 792 // update crsResolve with null rows 793 this.crsResolve.moveToInsertRow(); 794 795 for(i = 1; i <= callerColumnCount; i++) { 796 this.crsResolve.updateNull(i); 797 } 798 799 this.crsResolve.insertRow(); 800 this.crsResolve.moveToCurrentRow(); 801 802 return true; 803 } 804 } 805 806 /** 807 * Inserts a row that has been inserted into the given 808 * <code>CachedRowSet</code> object into the data source from which 809 * the rowset is derived, returning <code>false</code> if the insertion 810 * was successful. 811 * 812 * @param crs the <code>CachedRowSet</code> object that has had a row inserted 813 * and to whose underlying data source the row will be inserted 814 * @param pstmt the <code>PreparedStatement</code> object that will be used 815 * to execute the insertion 816 * @return <code>false</code> to indicate that the insertion was successful; 817 * <code>true</code> otherwise 818 * @throws SQLException if a database access error occurs 819 */ 820 private boolean insertNewRow(CachedRowSet crs, 821 PreparedStatement pstmt, CachedRowSetImpl crsRes) throws SQLException { 822 823 boolean returnVal = false; 824 825 try (PreparedStatement pstmtSel = con.prepareStatement(selectCmd, 826 ResultSet.TYPE_SCROLL_SENSITIVE, 827 ResultSet.CONCUR_READ_ONLY); 828 ResultSet rs = pstmtSel.executeQuery(); 829 ResultSet rs2 = con.getMetaData().getPrimaryKeys(null, null, 830 crs.getTableName()) 831 ) { 832 833 ResultSetMetaData rsmd = crs.getMetaData(); 834 int icolCount = rsmd.getColumnCount(); 835 String[] primaryKeys = new String[icolCount]; 836 int k = 0; 837 while (rs2.next()) { 900 * Cursor will come here if executeUpdate fails. 901 * There can be many reasons why the insertion failed, 902 * one can be violation of primary key. 903 * Hence we cannot exactly identify why the insertion failed, 904 * present the current row as a null row to the caller. 905 */ 906 this.crsResolve.moveToInsertRow(); 907 908 for (int i = 1; i <= icolCount; i++) { 909 this.crsResolve.updateNull(i); 910 } 911 912 this.crsResolve.insertRow(); 913 this.crsResolve.moveToCurrentRow(); 914 915 return true; 916 } 917 } 918 } 919 920 /** 921 * Deletes the row in the underlying data source that corresponds to 922 * a row that has been deleted in the given <code> CachedRowSet</code> object 923 * and returns <code>false</code> if the deletion was successful. 924 * <P> 925 * This method is called internally by this writer's <code>writeData</code> 926 * method when a row in the rowset has been deleted. The values in the 927 * deleted row are the same as those that are stored in the original row 928 * of the given <code>CachedRowSet</code> object. If the values in the 929 * original row differ from the row in the underlying data source, the row 930 * in the data source is not deleted, and <code>deleteOriginalRow</code> 931 * returns <code>true</code> to indicate that there was a conflict. 932 * 933 * 934 * @return <code>false</code> if the deletion was successful, which means that 935 * there was no conflict; <code>true</code> otherwise 936 * @throws SQLException if there was a database access error 937 */ 938 private boolean deleteOriginalRow(CachedRowSet crs, CachedRowSetImpl crsRes) throws SQLException { 939 PreparedStatement pstmt; 940 int i; 941 int idx = 0; 942 String strSelect; 943 // Select the row from the database. 944 ResultSet origVals = crs.getOriginalRow(); 945 origVals.next(); 946 947 deleteWhere = buildWhereClause(deleteWhere, origVals); 948 pstmt = con.prepareStatement(selectCmd + deleteWhere, 949 ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); 950 951 for (i = 0; i < keyCols.length; i++) { 952 if (params[i] != null) { 953 pstmt.setObject(++idx, params[i]); 954 } else { 955 continue; 1039 1040 /** 1041 * Sets the reader for this writer to the given reader. 1042 * 1043 * @throws SQLException if a database access error occurs 1044 */ 1045 public void setReader(CachedRowSetReader reader) throws SQLException { 1046 this.reader = reader; 1047 } 1048 1049 /** 1050 * Gets the reader for this writer. 1051 * 1052 * @throws SQLException if a database access error occurs 1053 */ 1054 public CachedRowSetReader getReader() throws SQLException { 1055 return reader; 1056 } 1057 1058 /** 1059 * Composes a <code>SELECT</code>, <code>UPDATE</code>, <code>INSERT</code>, 1060 * and <code>DELETE</code> statement that can be used by this writer to 1061 * write data to the data source backing the given <code>CachedRowSet</code> 1062 * object. 1063 * 1064 * @ param caller a <code>CachedRowSet</code> object for which this 1065 * <code>CachedRowSetWriter</code> object is the writer 1066 * @throws SQLException if a database access error occurs 1067 */ 1068 private void initSQLStatements(CachedRowSet caller) throws SQLException { 1069 1070 int i; 1071 1072 callerMd = caller.getMetaData(); 1073 callerColumnCount = callerMd.getColumnCount(); 1074 if (callerColumnCount < 1) 1075 // No data, so return. 1076 return; 1077 1078 /* 1079 * If the RowSet has a Table name we should use it. 1080 * This is really a hack to get round the fact that 1081 * a lot of the jdbc drivers can't provide the tab. 1082 */ 1083 String table = caller.getTableName(); 1084 if (table == null) { 1085 /* 1160 insertCmd += ")"; 1161 } 1162 1163 /* 1164 * Compose a DELETE statement. 1165 */ 1166 deleteCmd = "DELETE FROM " + buildTableName(dbmd, catalog, schema, table); 1167 1168 /* 1169 * set the key desriptors that will be 1170 * needed to construct where clauses. 1171 */ 1172 buildKeyDesc(caller); 1173 } 1174 1175 /** 1176 * Returns a fully qualified table name built from the given catalog and 1177 * table names. The given metadata object is used to get the proper order 1178 * and separator. 1179 * 1180 * @param dbmd a <code>DatabaseMetaData</code> object that contains metadata 1181 * about this writer's <code>CachedRowSet</code> object 1182 * @param catalog a <code>String</code> object with the rowset's catalog 1183 * name 1184 * @param table a <code>String</code> object with the name of the table from 1185 * which this writer's rowset was derived 1186 * @return a <code>String</code> object with the fully qualified name of the 1187 * table from which this writer's rowset was derived 1188 * @throws SQLException if a database access error occurs 1189 */ 1190 private String buildTableName(DatabaseMetaData dbmd, 1191 String catalog, String schema, String table) throws SQLException { 1192 1193 // trim all the leading and trailing whitespaces, 1194 // white spaces can never be catalog, schema or a table name. 1195 1196 String cmd = ""; 1197 1198 catalog = catalog.trim(); 1199 schema = schema.trim(); 1200 table = table.trim(); 1201 1202 if (dbmd.isCatalogAtStart() == true) { 1203 if (catalog != null && catalog.length() > 0) { 1204 cmd += catalog + dbmd.getCatalogSeparator(); 1205 } 1206 if (schema != null && schema.length() > 0) { 1207 cmd += schema + "."; 1208 } 1209 cmd += table; 1210 } else { 1211 if (schema != null && schema.length() > 0) { 1212 cmd += schema + "."; 1213 } 1214 cmd += table; 1215 if (catalog != null && catalog.length() > 0) { 1216 cmd += dbmd.getCatalogSeparator() + catalog; 1217 } 1218 } 1219 cmd += " "; 1220 return cmd; 1221 } 1222 1223 /** 1224 * Assigns to the given <code>CachedRowSet</code> object's 1225 * <code>params</code> 1226 * field an array whose length equals the number of columns needed 1227 * to uniquely identify a row in the rowset. The array is given 1228 * values by the method <code>buildWhereClause</code>. 1229 * <P> 1230 * If the <code>CachedRowSet</code> object's <code>keyCols</code> 1231 * field has length <code>0</code> or is <code>null</code>, the array 1232 * is set with the column number of every column in the rowset. 1233 * Otherwise, the array in the field <code>keyCols</code> is set with only 1234 * the column numbers of the columns that are required to form a unique 1235 * identifier for a row. 1236 * 1237 * @param crs the <code>CachedRowSet</code> object for which this 1238 * <code>CachedRowSetWriter</code> object is the writer 1239 * 1240 * @throws SQLException if a database access error occurs 1241 */ 1242 private void buildKeyDesc(CachedRowSet crs) throws SQLException { 1243 1244 keyCols = crs.getKeyColumns(); 1245 ResultSetMetaData resultsetmd = crs.getMetaData(); 1246 if (keyCols == null || keyCols.length == 0) { 1247 ArrayList<Integer> listKeys = new ArrayList<Integer>(); 1248 1249 for (int i = 0; i < callerColumnCount; i++ ) { 1250 if(resultsetmd.getColumnType(i+1) != java.sql.Types.CLOB && 1251 resultsetmd.getColumnType(i+1) != java.sql.Types.STRUCT && 1252 resultsetmd.getColumnType(i+1) != java.sql.Types.SQLXML && 1253 resultsetmd.getColumnType(i+1) != java.sql.Types.BLOB && 1254 resultsetmd.getColumnType(i+1) != java.sql.Types.ARRAY && 1255 resultsetmd.getColumnType(i+1) != java.sql.Types.OTHER ) 1256 listKeys.add(i+1); 1257 } 1258 keyCols = new int[listKeys.size()]; 1259 for (int i = 0; i < listKeys.size(); i++ ) 1260 keyCols[i] = listKeys.get(i); 1261 } 1262 params = new Object[keyCols.length]; 1263 } 1264 1265 /** 1266 * Constructs an SQL <code>WHERE</code> clause using the given 1267 * string as a starting point. The resulting clause will contain 1268 * a column name and " = ?" for each key column, that is, each column 1269 * that is needed to form a unique identifier for a row in the rowset. 1270 * This <code>WHERE</code> clause can be added to 1271 * a <code>PreparedStatement</code> object that updates, inserts, or 1272 * deletes a row. 1273 * <P> 1274 * This method uses the given result set to access values in the 1275 * <code>CachedRowSet</code> object that called this writer. These 1276 * values are used to build the array of parameters that will serve as 1277 * replacements for the "?" parameter placeholders in the 1278 * <code>PreparedStatement</code> object that is sent to the 1279 * <code>CachedRowSet</code> object's underlying data source. 1280 * 1281 * @param whereClause a <code>String</code> object that is an empty 1282 * string ("") 1283 * @param rs a <code>ResultSet</code> object that can be used 1284 * to access the <code>CachedRowSet</code> object's data 1285 * @return a <code>WHERE</code> clause of the form "<code>WHERE</code> 1286 * columnName = ? AND columnName = ? AND columnName = ? ..." 1287 * @throws SQLException if a database access error occurs 1288 */ 1289 private String buildWhereClause(String whereClause, 1290 ResultSet rs) throws SQLException { 1291 whereClause = "WHERE "; 1292 1293 for (int i = 0; i < keyCols.length; i++) { 1294 if (i > 0) { 1295 whereClause += "AND "; 1296 } 1297 whereClause += callerMd.getColumnName(keyCols[i]); 1298 params[i] = rs.getObject(keyCols[i]); 1299 if (rs.wasNull() == true) { 1300 whereClause += " IS NULL "; 1301 } else { 1302 whereClause += " = ? "; 1303 } 1304 } 1305 return whereClause; | 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 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 788 } 789 } catch (SQLException ex) { 790 ex.printStackTrace(); 791 // if executeUpdate fails it will come here, 792 // update crsResolve with null rows 793 this.crsResolve.moveToInsertRow(); 794 795 for(i = 1; i <= callerColumnCount; i++) { 796 this.crsResolve.updateNull(i); 797 } 798 799 this.crsResolve.insertRow(); 800 this.crsResolve.moveToCurrentRow(); 801 802 return true; 803 } 804 } 805 806 /** 807 * Inserts a row that has been inserted into the given 808 * {@code CachedRowSet} object into the data source from which 809 * the rowset is derived, returning {@code false} if the insertion 810 * was successful. 811 * 812 * @param crs the {@code CachedRowSet} object that has had a row inserted 813 * and to whose underlying data source the row will be inserted 814 * @param pstmt the {@code PreparedStatement} object that will be used 815 * to execute the insertion 816 * @return {@code false} to indicate that the insertion was successful; 817 * {@code true} otherwise 818 * @throws SQLException if a database access error occurs 819 */ 820 private boolean insertNewRow(CachedRowSet crs, 821 PreparedStatement pstmt, CachedRowSetImpl crsRes) throws SQLException { 822 823 boolean returnVal = false; 824 825 try (PreparedStatement pstmtSel = con.prepareStatement(selectCmd, 826 ResultSet.TYPE_SCROLL_SENSITIVE, 827 ResultSet.CONCUR_READ_ONLY); 828 ResultSet rs = pstmtSel.executeQuery(); 829 ResultSet rs2 = con.getMetaData().getPrimaryKeys(null, null, 830 crs.getTableName()) 831 ) { 832 833 ResultSetMetaData rsmd = crs.getMetaData(); 834 int icolCount = rsmd.getColumnCount(); 835 String[] primaryKeys = new String[icolCount]; 836 int k = 0; 837 while (rs2.next()) { 900 * Cursor will come here if executeUpdate fails. 901 * There can be many reasons why the insertion failed, 902 * one can be violation of primary key. 903 * Hence we cannot exactly identify why the insertion failed, 904 * present the current row as a null row to the caller. 905 */ 906 this.crsResolve.moveToInsertRow(); 907 908 for (int i = 1; i <= icolCount; i++) { 909 this.crsResolve.updateNull(i); 910 } 911 912 this.crsResolve.insertRow(); 913 this.crsResolve.moveToCurrentRow(); 914 915 return true; 916 } 917 } 918 } 919 920 /** 921 * Deletes the row in the underlying data source that corresponds to 922 * a row that has been deleted in the given {@code CachedRowSet} object 923 * and returns {@code false} if the deletion was successful. 924 * <P> 925 * This method is called internally by this writer's {@code writeData} 926 * method when a row in the rowset has been deleted. The values in the 927 * deleted row are the same as those that are stored in the original row 928 * of the given {@code CachedRowSet} object. If the values in the 929 * original row differ from the row in the underlying data source, the row 930 * in the data source is not deleted, and {@code deleteOriginalRow} 931 * returns {@code true} to indicate that there was a conflict. 932 * 933 * 934 * @return {@code false} if the deletion was successful, which means that 935 * there was no conflict; {@code true} otherwise 936 * @throws SQLException if there was a database access error 937 */ 938 private boolean deleteOriginalRow(CachedRowSet crs, CachedRowSetImpl crsRes) throws SQLException { 939 PreparedStatement pstmt; 940 int i; 941 int idx = 0; 942 String strSelect; 943 // Select the row from the database. 944 ResultSet origVals = crs.getOriginalRow(); 945 origVals.next(); 946 947 deleteWhere = buildWhereClause(deleteWhere, origVals); 948 pstmt = con.prepareStatement(selectCmd + deleteWhere, 949 ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY); 950 951 for (i = 0; i < keyCols.length; i++) { 952 if (params[i] != null) { 953 pstmt.setObject(++idx, params[i]); 954 } else { 955 continue; 1039 1040 /** 1041 * Sets the reader for this writer to the given reader. 1042 * 1043 * @throws SQLException if a database access error occurs 1044 */ 1045 public void setReader(CachedRowSetReader reader) throws SQLException { 1046 this.reader = reader; 1047 } 1048 1049 /** 1050 * Gets the reader for this writer. 1051 * 1052 * @throws SQLException if a database access error occurs 1053 */ 1054 public CachedRowSetReader getReader() throws SQLException { 1055 return reader; 1056 } 1057 1058 /** 1059 * Composes a {@code SELECT}, {@code UPDATE}, {@code INSERT}, 1060 * and {@code DELETE} statement that can be used by this writer to 1061 * write data to the data source backing the given {@code CachedRowSet} 1062 * object. 1063 * 1064 * @param caller a {@code CachedRowSet} object for which this 1065 * {@code CachedRowSetWriter} object is the writer 1066 * @throws SQLException if a database access error occurs 1067 */ 1068 private void initSQLStatements(CachedRowSet caller) throws SQLException { 1069 1070 int i; 1071 1072 callerMd = caller.getMetaData(); 1073 callerColumnCount = callerMd.getColumnCount(); 1074 if (callerColumnCount < 1) 1075 // No data, so return. 1076 return; 1077 1078 /* 1079 * If the RowSet has a Table name we should use it. 1080 * This is really a hack to get round the fact that 1081 * a lot of the jdbc drivers can't provide the tab. 1082 */ 1083 String table = caller.getTableName(); 1084 if (table == null) { 1085 /* 1160 insertCmd += ")"; 1161 } 1162 1163 /* 1164 * Compose a DELETE statement. 1165 */ 1166 deleteCmd = "DELETE FROM " + buildTableName(dbmd, catalog, schema, table); 1167 1168 /* 1169 * set the key desriptors that will be 1170 * needed to construct where clauses. 1171 */ 1172 buildKeyDesc(caller); 1173 } 1174 1175 /** 1176 * Returns a fully qualified table name built from the given catalog and 1177 * table names. The given metadata object is used to get the proper order 1178 * and separator. 1179 * 1180 * @param dbmd a {@code DatabaseMetaData} object that contains metadata 1181 * about this writer's {@code CachedRowSet} object 1182 * @param catalog a {@code String} object with the rowset's catalog 1183 * name 1184 * @param table a {@code String} object with the name of the table from 1185 * which this writer's rowset was derived 1186 * @return a {@code String} object with the fully qualified name of the 1187 * table from which this writer's rowset was derived 1188 * @throws SQLException if a database access error occurs 1189 */ 1190 private String buildTableName(DatabaseMetaData dbmd, 1191 String catalog, String schema, String table) throws SQLException { 1192 1193 // trim all the leading and trailing whitespaces, 1194 // white spaces can never be catalog, schema or a table name. 1195 1196 String cmd = ""; 1197 1198 catalog = catalog.trim(); 1199 schema = schema.trim(); 1200 table = table.trim(); 1201 1202 if (dbmd.isCatalogAtStart() == true) { 1203 if (catalog != null && catalog.length() > 0) { 1204 cmd += catalog + dbmd.getCatalogSeparator(); 1205 } 1206 if (schema != null && schema.length() > 0) { 1207 cmd += schema + "."; 1208 } 1209 cmd += table; 1210 } else { 1211 if (schema != null && schema.length() > 0) { 1212 cmd += schema + "."; 1213 } 1214 cmd += table; 1215 if (catalog != null && catalog.length() > 0) { 1216 cmd += dbmd.getCatalogSeparator() + catalog; 1217 } 1218 } 1219 cmd += " "; 1220 return cmd; 1221 } 1222 1223 /** 1224 * Assigns to the given {@code CachedRowSet} object's 1225 * {@code params} 1226 * field an array whose length equals the number of columns needed 1227 * to uniquely identify a row in the rowset. The array is given 1228 * values by the method {@code buildWhereClause}. 1229 * <P> 1230 * If the {@code CachedRowSet} object's {@code keyCols} 1231 * field has length {@code 0} or is {@code null}, the array 1232 * is set with the column number of every column in the rowset. 1233 * Otherwise, the array in the field {@code keyCols} is set with only 1234 * the column numbers of the columns that are required to form a unique 1235 * identifier for a row. 1236 * 1237 * @param crs the {@code CachedRowSet} object for which this 1238 * {@code CachedRowSetWriter} object is the writer 1239 * 1240 * @throws SQLException if a database access error occurs 1241 */ 1242 private void buildKeyDesc(CachedRowSet crs) throws SQLException { 1243 1244 keyCols = crs.getKeyColumns(); 1245 ResultSetMetaData resultsetmd = crs.getMetaData(); 1246 if (keyCols == null || keyCols.length == 0) { 1247 ArrayList<Integer> listKeys = new ArrayList<Integer>(); 1248 1249 for (int i = 0; i < callerColumnCount; i++ ) { 1250 if(resultsetmd.getColumnType(i+1) != java.sql.Types.CLOB && 1251 resultsetmd.getColumnType(i+1) != java.sql.Types.STRUCT && 1252 resultsetmd.getColumnType(i+1) != java.sql.Types.SQLXML && 1253 resultsetmd.getColumnType(i+1) != java.sql.Types.BLOB && 1254 resultsetmd.getColumnType(i+1) != java.sql.Types.ARRAY && 1255 resultsetmd.getColumnType(i+1) != java.sql.Types.OTHER ) 1256 listKeys.add(i+1); 1257 } 1258 keyCols = new int[listKeys.size()]; 1259 for (int i = 0; i < listKeys.size(); i++ ) 1260 keyCols[i] = listKeys.get(i); 1261 } 1262 params = new Object[keyCols.length]; 1263 } 1264 1265 /** 1266 * Constructs an SQL {@code WHERE} clause using the given 1267 * string as a starting point. The resulting clause will contain 1268 * a column name and " = ?" for each key column, that is, each column 1269 * that is needed to form a unique identifier for a row in the rowset. 1270 * This {@code WHERE} clause can be added to 1271 * a {@code PreparedStatement} object that updates, inserts, or 1272 * deletes a row. 1273 * <P> 1274 * This method uses the given result set to access values in the 1275 * {@code CachedRowSet} object that called this writer. These 1276 * values are used to build the array of parameters that will serve as 1277 * replacements for the "?" parameter placeholders in the 1278 * {@code PreparedStatement} object that is sent to the 1279 * {@code CachedRowSet} object's underlying data source. 1280 * 1281 * @param whereClause a {@code String} object that is an empty 1282 * string ("") 1283 * @param rs a {@code ResultSet} object that can be used 1284 * to access the {@code CachedRowSet} object's data 1285 * @return a {@code WHERE} clause of the form "{@code WHERE} 1286 * columnName = ? AND columnName = ? AND columnName = ? ..." 1287 * @throws SQLException if a database access error occurs 1288 */ 1289 private String buildWhereClause(String whereClause, 1290 ResultSet rs) throws SQLException { 1291 whereClause = "WHERE "; 1292 1293 for (int i = 0; i < keyCols.length; i++) { 1294 if (i > 0) { 1295 whereClause += "AND "; 1296 } 1297 whereClause += callerMd.getColumnName(keyCols[i]); 1298 params[i] = rs.getObject(keyCols[i]); 1299 if (rs.wasNull() == true) { 1300 whereClause += " IS NULL "; 1301 } else { 1302 whereClause += " = ? "; 1303 } 1304 } 1305 return whereClause; |