/*
* Copyright (c) 2003, 2013, Oracle and/or its affiliates. All rights reserved.
* DO NOT ALTER OR REMOVE COPYRIGHT NOTICES OR THIS FILE HEADER.
*
* This code is free software; you can redistribute it and/or modify it
* under the terms of the GNU General Public License version 2 only, as
* published by the Free Software Foundation. Oracle designates this
* particular file as subject to the "Classpath" exception as provided
* by Oracle in the LICENSE file that accompanied this code.
*
* This code is distributed in the hope that it will be useful, but WITHOUT
* ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or
* FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License
* version 2 for more details (a copy is included in the LICENSE file that
* accompanied this code).
*
* You should have received a copy of the GNU General Public License version
* 2 along with this work; if not, write to the Free Software Foundation,
* Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA.
*
* Please contact Oracle, 500 Oracle Parkway, Redwood Shores, CA 94065 USA
* or visit www.oracle.com if you need additional information or have any
* questions.
*/
package com.sun.rowset.internal;
import java.sql.*;
import javax.sql.*;
import java.util.*;
import java.io.*;
import sun.reflect.misc.ReflectUtil;
import com.sun.rowset.*;
import java.text.MessageFormat;
import javax.sql.rowset.*;
import javax.sql.rowset.serial.SQLInputImpl;
import javax.sql.rowset.serial.SerialArray;
import javax.sql.rowset.serial.SerialBlob;
import javax.sql.rowset.serial.SerialClob;
import javax.sql.rowset.serial.SerialStruct;
import javax.sql.rowset.spi.*;
/**
* The facility called on internally by the {@code RIOptimisticProvider} implementation to
* propagate changes back to the data source from which the rowset got its data.
* <P>
* A {@code CachedRowSetWriter} object, called a writer, has the public
* method {@code writeData} for writing modified data to the underlying data source.
* This method is invoked by the rowset internally and is never invoked directly by an application.
* A writer also has public methods for setting and getting
* the {@code CachedRowSetReader} object, called a reader, that is associated
* with the writer. The remainder of the methods in this class are private and
* are invoked internally, either directly or indirectly, by the method
* {@code writeData}.
* <P>
* Typically the {@code SyncFactory} manages the {@code RowSetReader} and
* the {@code RowSetWriter} implementations using {@code SyncProvider} objects.
* Standard JDBC RowSet implementations provide an object instance of this
* writer by invoking the {@code SyncProvider.getRowSetWriter()} method.
*
* @version 0.2
* @author Jonathan Bruce
* @see javax.sql.rowset.spi.SyncProvider
* @see javax.sql.rowset.spi.SyncFactory
* @see javax.sql.rowset.spi.SyncFactoryException
*/
public class CachedRowSetWriter implements TransactionalWriter, Serializable {
/**
* The {@code Connection} object that this writer will use to make a
* connection to the data source to which it will write data.
*
*/
private transient Connection con;
/**
* The SQL {@code SELECT} command that this writer will call
* internally. The method {@code initSQLStatements} builds this
* command by supplying the words "SELECT" and "FROM," and using
* metadata to get the table name and column names .
*
* @serial
*/
private String selectCmd;
/**
* The SQL {@code UPDATE} command that this writer will call
* internally to write data to the rowset's underlying data source.
* The method {@code initSQLStatements} builds this {@code String}
* object.
*
* @serial
*/
private String updateCmd;
/**
* The SQL {@code WHERE} clause the writer will use for update
* statements in the {@code PreparedStatement} object
* it sends to the underlying data source.
*
* @serial
*/
private String updateWhere;
/**
* The SQL {@code DELETE} command that this writer will call
* internally to delete a row in the rowset's underlying data source.
*
* @serial
*/
private String deleteCmd;
/**
* The SQL {@code WHERE} clause the writer will use for delete
* statements in the {@code PreparedStatement} object
* it sends to the underlying data source.
*
* @serial
*/
private String deleteWhere;
/**
* The SQL {@code INSERT INTO} command that this writer will internally use
* to insert data into the rowset's underlying data source. The method
* {@code initSQLStatements} builds this command with a question
* mark parameter placeholder for each column in the rowset.
*
* @serial
*/
private String insertCmd;
/**
* An array containing the column numbers of the columns that are
* needed to uniquely identify a row in the {@code CachedRowSet} object
* for which this {@code CachedRowSetWriter} object is the writer.
*
* @serial
*/
private int[] keyCols;
/**
* An array of the parameters that should be used to set the parameter
* placeholders in a {@code PreparedStatement} object that this
* writer will execute.
*
* @serial
*/
private Object[] params;
/**
* The {@code CachedRowSetReader} object that has been
* set as the reader for the {@code CachedRowSet} object
* for which this {@code CachedRowSetWriter} object is the writer.
*
* @serial
*/
private CachedRowSetReader reader;
/**
* The {@code ResultSetMetaData} object that contains information
* about the columns in the {@code CachedRowSet} object
* for which this {@code CachedRowSetWriter} object is the writer.
*
* @serial
*/
private ResultSetMetaData callerMd;
/**
* The number of columns in the {@code CachedRowSet} object
* for which this {@code CachedRowSetWriter} object is the writer.
*
* @serial
*/
private int callerColumnCount;
/**
* This {@code CachedRowSet} will hold the conflicting values
* retrieved from the db and hold it.
*/
private CachedRowSetImpl crsResolve;
/**
* This {@code ArrayList} will hold the values of SyncResolver.*
*/
private ArrayList<Integer> status;
/**
* This will check whether the same field value has changed both
* in database and CachedRowSet.
*/
private int iChangedValsInDbAndCRS;
/**
* This will hold the number of cols for which the values have
* changed only in database.
*/
private int iChangedValsinDbOnly ;
private JdbcRowSetResourceBundle resBundle;
public CachedRowSetWriter() {
try {
resBundle = JdbcRowSetResourceBundle.getJdbcRowSetResourceBundle();
} catch(IOException ioe) {
throw new RuntimeException(ioe);
}
}
/**
* Propagates changes in the given {@code RowSet} object
* back to its underlying data source and returns {@code true}
* if successful. The writer will check to see if
* the data in the pre-modified rowset (the original values) differ
* from the data in the underlying data source. If data in the data
* source has been modified by someone else, there is a conflict,
* and in that case, the writer will not write to the data source.
* In other words, the writer uses an optimistic concurrency algorithm:
* It checks for conflicts before making changes rather than restricting
* access for concurrent users.
* <P>
* This method is called by the rowset internally when
* the application invokes the method {@code acceptChanges}.
* The {@code writeData} method in turn calls private methods that
* it defines internally.
* The following is a general summary of what the method
* {@code writeData} does, much of which is accomplished
* through calls to its own internal methods.
* <OL>
* <LI>Creates a {@code CachedRowSet} object from the given
* {@code RowSet} object
* <LI>Makes a connection with the data source
* <UL>
* <LI>Disables autocommit mode if it is not already disabled
* <LI>Sets the transaction isolation level to that of the rowset
* </UL>
* <LI>Checks to see if the reader has read new data since the writer
* was last called and, if so, calls the method
* {@code initSQLStatements} to initialize new SQL statements
* <UL>
* <LI>Builds new {@code SELECT}, {@code UPDATE},
* {@code INSERT}, and {@code DELETE} statements
* <LI>Uses the {@code CachedRowSet} object's metadata to
* determine the table name, column names, and the columns
* that make up the primary key
* </UL>
* <LI>When there is no conflict, propagates changes made to the
* {@code CachedRowSet} object back to its underlying data source
* <UL>
* <LI>Iterates through each row of the {@code CachedRowSet} object
* to determine whether it has been updated, inserted, or deleted
* <LI>If the corresponding row in the data source has not been changed
* since the rowset last read its
* values, the writer will use the appropriate command to update,
* insert, or delete the row
* <LI>If any data in the data source does not match the original values
* for the {@code CachedRowSet} object, the writer will roll
* back any changes it has made to the row in the data source.
* </UL>
* </OL>
*
* @return {@code true} if changes to the rowset were successfully
* written to the rowset's underlying data source;
* {@code false} otherwise
*/
public boolean writeData(RowSetInternal caller) throws SQLException {
long conflicts = 0;
boolean showDel = false;
PreparedStatement pstmtIns = null;
iChangedValsInDbAndCRS = 0;
iChangedValsinDbOnly = 0;
// We assume caller is a CachedRowSet
CachedRowSetImpl crs = (CachedRowSetImpl)caller;
// crsResolve = new CachedRowSetImpl();
this.crsResolve = new CachedRowSetImpl();;
// The reader is registered with the writer at design time.
// This is not required, in general. The reader has logic
// to get a JDBC connection, so call it.
con = reader.connect(caller);
if (con == null) {
throw new SQLException(resBundle.handleGetObject("crswriter.connect").toString());
}
/*
// Fix 6200646.
// Don't change the connection or transaction properties. This will fail in a
// J2EE container.
if (con.getAutoCommit() == true) {
con.setAutoCommit(false);
}
con.setTransactionIsolation(crs.getTransactionIsolation());
*/
initSQLStatements(crs);
int iColCount;
RowSetMetaDataImpl rsmdWrite = (RowSetMetaDataImpl)crs.getMetaData();
RowSetMetaDataImpl rsmdResolv = new RowSetMetaDataImpl();
iColCount = rsmdWrite.getColumnCount();
int sz= crs.size()+1;
status = new ArrayList<>(sz);
status.add(0,null);
rsmdResolv.setColumnCount(iColCount);
for(int i =1; i <= iColCount; i++) {
rsmdResolv.setColumnType(i, rsmdWrite.getColumnType(i));
rsmdResolv.setColumnName(i, rsmdWrite.getColumnName(i));
rsmdResolv.setNullable(i, ResultSetMetaData.columnNullableUnknown);
}
this.crsResolve.setMetaData(rsmdResolv);
// moved outside the insert inner loop
//pstmtIns = con.prepareStatement(insertCmd);
if (callerColumnCount < 1) {
// No data, so return success.
if (reader.getCloseConnection() == true)
con.close();
return true;
}
// We need to see rows marked for deletion.
showDel = crs.getShowDeleted();
crs.setShowDeleted(true);
// Look at all the rows.
crs.beforeFirst();
int rows =1;
while (crs.next()) {
if (crs.rowDeleted()) {
// The row has been deleted.
if (deleteOriginalRow(crs, this.crsResolve)) {
status.add(rows, SyncResolver.DELETE_ROW_CONFLICT);
conflicts++;
} else {
// delete happened without any occurrence of conflicts
// so update status accordingly
status.add(rows, SyncResolver.NO_ROW_CONFLICT);
}
} else if (crs.rowInserted()) {
// The row has been inserted.
pstmtIns = con.prepareStatement(insertCmd);
if (insertNewRow(crs, pstmtIns, this.crsResolve)) {
status.add(rows, SyncResolver.INSERT_ROW_CONFLICT);
conflicts++;
} else {
// insert happened without any occurrence of conflicts
// so update status accordingly
status.add(rows, SyncResolver.NO_ROW_CONFLICT);
}
} else if (crs.rowUpdated()) {
// The row has been updated.
if (updateOriginalRow(crs)) {
status.add(rows, SyncResolver.UPDATE_ROW_CONFLICT);
conflicts++;
} else {
// update happened without any occurrence of conflicts
// so update status accordingly
status.add(rows, SyncResolver.NO_ROW_CONFLICT);
}
} else {
/** The row is neither of inserted, updated or deleted.
* So set nulls in the this.crsResolve for this row,
* as nothing is to be done for such rows.
* Also note that if such a row has been changed in database
* and we have not changed(inserted, updated or deleted)
* that is fine.
**/
int icolCount = crs.getMetaData().getColumnCount();
status.add(rows, SyncResolver.NO_ROW_CONFLICT);
this.crsResolve.moveToInsertRow();
for(int cols=0;cols<iColCount;cols++) {
this.crsResolve.updateNull(cols+1);
} //end for
this.crsResolve.insertRow();
this.crsResolve.moveToCurrentRow();
} //end if
rows++;
} //end while
// close the insert statement
if(pstmtIns!=null)
pstmtIns.close();
// reset
crs.setShowDeleted(showDel);
crs.beforeFirst();
this.crsResolve.beforeFirst();
if(conflicts != 0) {
SyncProviderException spe = new SyncProviderException(conflicts + " " +
resBundle.handleGetObject("crswriter.conflictsno").toString());
//SyncResolver syncRes = spe.getSyncResolver();
SyncResolverImpl syncResImpl = (SyncResolverImpl) spe.getSyncResolver();
syncResImpl.setCachedRowSet(crs);
syncResImpl.setCachedRowSetResolver(this.crsResolve);
syncResImpl.setStatus(status);
syncResImpl.setCachedRowSetWriter(this);
throw spe;
} else {
return true;
}
/*
if (conflict == true) {
con.rollback();
return false;
} else {
con.commit();
if (reader.getCloseConnection() == true) {
con.close();
}
return true;
}
*/
} //end writeData
/**
* Updates the given {@code CachedRowSet} object's underlying data
* source so that updates to the rowset are reflected in the original
* data source, and returns {@code false} if the update was successful.
* A return value of {@code true} indicates that there is a conflict,
* meaning that a value updated in the rowset has already been changed by
* someone else in the underlying data source. A conflict can also exist
* if, for example, more than one row in the data source would be affected
* by the update or if no rows would be affected. In any case, if there is
* a conflict, this method does not update the underlying data source.
* <P>
* This method is called internally by the method {@code writeData}
* if a row in the {@code CachedRowSet} object for which this
* {@code CachedRowSetWriter} object is the writer has been updated.
*
* @return {@code false} if the update to the underlying data source is
* successful; {@code true} otherwise
* @throws SQLException if a database access error occurs
*/
private boolean updateOriginalRow(CachedRowSet crs)
throws SQLException {
PreparedStatement pstmt;
int i = 0;
int idx = 0;
// Select the row from the database.
ResultSet origVals = crs.getOriginalRow();
origVals.next();
try {
updateWhere = buildWhereClause(updateWhere, origVals);
/**
* The following block of code is for checking a particular type of
* query where in there is a where clause. Without this block, if a
* SQL statement is built the "where" clause will appear twice hence
* the DB errors out and a SQLException is thrown. This code also
* considers that the where clause is in the right place as the
* CachedRowSet object would already have been populated with this
* query before coming to this point.
**/
String tempselectCmd = selectCmd.toLowerCase();
int idxWhere = tempselectCmd.indexOf("where");
if(idxWhere != -1)
{
String tempSelect = selectCmd.substring(0,idxWhere);
selectCmd = tempSelect;
}
pstmt = con.prepareStatement(selectCmd + updateWhere,
ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
for (i = 0; i < keyCols.length; i++) {
if (params[i] != null) {
pstmt.setObject(++idx, params[i]);
} else {
continue;
}
}
try {
pstmt.setMaxRows(crs.getMaxRows());
pstmt.setMaxFieldSize(crs.getMaxFieldSize());
pstmt.setEscapeProcessing(crs.getEscapeProcessing());
pstmt.setQueryTimeout(crs.getQueryTimeout());
} catch (Exception ex) {
// Older driver don't support these operations.
}
ResultSet rs = null;
rs = pstmt.executeQuery();
ResultSetMetaData rsmd = rs.getMetaData();
if (rs.next()) {
if (rs.next()) {
/** More than one row conflict.
* If rs has only one row we are able to
* uniquely identify the row where update
* have to happen else if more than one
* row implies we cannot uniquely identify the row
* where we have to do updates.
* crs.setKeyColumns needs to be set to
* come out of this situation.
*/
return true;
}
// don't close the rs
// we require the record in rs to be used.
// rs.close();
// pstmt.close();
rs.first();
// how many fields need to be updated
int colsNotChanged = 0;
Vector<Integer> cols = new Vector<>();
String updateExec = updateCmd;
Object orig;
Object curr;
Object rsval;
boolean boolNull = true;
Object objVal = null;
// There's only one row and the cursor
// needs to be on that row.
boolean first = true;
boolean flag = true;
this.crsResolve.moveToInsertRow();
for (i = 1; i <= callerColumnCount; i++) {
orig = origVals.getObject(i);
curr = crs.getObject(i);
rsval = rs.getObject(i);
/*
* the following block creates equivalent objects
* that would have been created if this rs is populated
* into a CachedRowSet so that comparison of the column values
* from the ResultSet and CachedRowSet are possible
*/
Map<String, Class<?>> map = (crs.getTypeMap() == null)?con.getTypeMap():crs.getTypeMap();
if (rsval instanceof Struct) {
Struct s = (Struct)rsval;
// look up the class in the map
Class<?> c = null;
c = map.get(s.getSQLTypeName());
if (c != null) {
// create new instance of the class
SQLData obj = null;
try {
ReflectUtil.checkPackageAccess(c);
@SuppressWarnings("deprecation")
Object tmp = c.newInstance();
obj = (SQLData)tmp;
} catch (Exception ex) {
throw new SQLException("Unable to Instantiate: ", ex);
}
// get the attributes from the struct
Object attribs[] = s.getAttributes(map);
// create the SQLInput "stream"
SQLInputImpl sqlInput = new SQLInputImpl(attribs, map);
// read the values...
obj.readSQL(sqlInput, s.getSQLTypeName());
rsval = obj;
}
} else if (rsval instanceof SQLData) {
rsval = new SerialStruct((SQLData)rsval, map);
} else if (rsval instanceof Blob) {
rsval = new SerialBlob((Blob)rsval);
} else if (rsval instanceof Clob) {
rsval = new SerialClob((Clob)rsval);
} else if (rsval instanceof java.sql.Array) {
rsval = new SerialArray((java.sql.Array)rsval, map);
}
// reset boolNull if it had been set
boolNull = true;
/** This addtional checking has been added when the current value
* in the DB is null, but the DB had a different value when the
* data was actaully fetched into the CachedRowSet.
**/
if(rsval == null && orig != null) {
// value in db has changed
// don't proceed with synchronization
// get the value in db and pass it to the resolver.
iChangedValsinDbOnly++;
// Set the boolNull to false,
// in order to set the actual value;
boolNull = false;
objVal = rsval;
}
/** Adding the checking for rsval to be "not" null or else
* it would through a NullPointerException when the values
* are compared.
**/
else if(rsval != null && (!rsval.equals(orig)))
{
// value in db has changed
// don't proceed with synchronization
// get the value in db and pass it to the resolver.
iChangedValsinDbOnly++;
// Set the boolNull to false,
// in order to set the actual value;
boolNull = false;
objVal = rsval;
} else if ( (orig == null || curr == null) ) {
/** Adding the additonal condition of checking for "flag"
* boolean variable, which would otherwise result in
* building a invalid query, as the comma would not be
* added to the query string.
**/
if (first == false || flag == false) {
updateExec += ", ";
}
updateExec += crs.getMetaData().getColumnName(i);
cols.add(i);
updateExec += " = ? ";
first = false;
/** Adding the extra condition for orig to be "not" null as the
* condition for orig to be null is take prior to this, if this
* is not added it will result in a NullPointerException when
* the values are compared.
**/
} else if (orig.equals(curr)) {
colsNotChanged++;
//nothing to update in this case since values are equal
/** Adding the extra condition for orig to be "not" null as the
* condition for orig to be null is take prior to this, if this
* is not added it will result in a NullPointerException when
* the values are compared.
**/
} else if(orig.equals(curr) == false) {
// When values from db and values in CachedRowSet are not equal,
// if db value is same as before updation for each col in
// the row before fetching into CachedRowSet,
// only then we go ahead with updation, else we
// throw SyncProviderException.
// if value has changed in db after fetching from db
// for some cols of the row and at the same time, some other cols
// have changed in CachedRowSet, no synchronization happens
// Synchronization happens only when data when fetching is
// same or at most has changed in cachedrowset
// check orig value with what is there in crs for a column
// before updation in crs.
if(crs.columnUpdated(i)) {
if(rsval.equals(orig)) {
// At this point we are sure that
// the value updated in crs was from
// what is in db now and has not changed
if (flag == false || first == false) {
updateExec += ", ";
}
updateExec += crs.getMetaData().getColumnName(i);
cols.add(i);
updateExec += " = ? ";
flag = false;
} else {
// Here the value has changed in the db after
// data was fetched
// Plus store this row from CachedRowSet and keep it
// in a new CachedRowSet
boolNull= false;
objVal = rsval;
iChangedValsInDbAndCRS++;
}
}
}
if(!boolNull) {
this.crsResolve.updateObject(i,objVal);
} else {
this.crsResolve.updateNull(i);
}
} //end for
rs.close();
pstmt.close();
this.crsResolve.insertRow();
this.crsResolve.moveToCurrentRow();
/**
* if nothing has changed return now - this can happen
* if column is updated to the same value.
* if colsNotChanged == callerColumnCount implies we are updating
* the database with ALL COLUMNS HAVING SAME VALUES,
* so skip going to database, else do as usual.
**/
if ( (first == false && cols.size() == 0) ||
colsNotChanged == callerColumnCount ) {
return false;
}
if(iChangedValsInDbAndCRS != 0 || iChangedValsinDbOnly != 0) {
return true;
}
updateExec += updateWhere;
pstmt = con.prepareStatement(updateExec);
// Comments needed here
for (i = 0; i < cols.size(); i++) {
Object obj = crs.getObject(cols.get(i));
if (obj != null)
pstmt.setObject(i + 1, obj);
else
pstmt.setNull(i + 1,crs.getMetaData().getColumnType(i + 1));
}
idx = i;
// Comments needed here
for (i = 0; i < keyCols.length; i++) {
if (params[i] != null) {
pstmt.setObject(++idx, params[i]);
} else {
continue;
}
}
i = pstmt.executeUpdate();
/**
* i should be equal to 1(row count), because we update
* one row(returned as row count) at a time, if all goes well.
* if 1 != 1, this implies we have not been able to
* do updations properly i.e there is a conflict in database
* versus what is in CachedRowSet for this particular row.
**/
return false;
} else {
/**
* Cursor will be here, if the ResultSet may not return even a single row
* i.e. we can't find the row where to update because it has been deleted
* etc. from the db.
* Present the whole row as null to user, to force null to be sync'ed
* and hence nothing to be synced.
*
* NOTE:
* ------
* In the database if a column that is mapped to java.sql.Types.REAL stores
* a Double value and is compared with value got from ResultSet.getFloat()
* no row is retrieved and will throw a SyncProviderException. For details
* see bug Id 5053830
**/
return true;
}
} catch (SQLException ex) {
ex.printStackTrace();
// if executeUpdate fails it will come here,
// update crsResolve with null rows
this.crsResolve.moveToInsertRow();
for(i = 1; i <= callerColumnCount; i++) {
this.crsResolve.updateNull(i);
}
this.crsResolve.insertRow();
this.crsResolve.moveToCurrentRow();
return true;
}
}
/**
* Inserts a row that has been inserted into the given
* {@code CachedRowSet} object into the data source from which
* the rowset is derived, returning {@code false} if the insertion
* was successful.
*
* @param crs the {@code CachedRowSet} object that has had a row inserted
* and to whose underlying data source the row will be inserted
* @param pstmt the {@code PreparedStatement} object that will be used
* to execute the insertion
* @return {@code false} to indicate that the insertion was successful;
* {@code true} otherwise
* @throws SQLException if a database access error occurs
*/
private boolean insertNewRow(CachedRowSet crs,
PreparedStatement pstmt, CachedRowSetImpl crsRes) throws SQLException {
boolean returnVal = false;
try (PreparedStatement pstmtSel = con.prepareStatement(selectCmd,
ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
ResultSet rs = pstmtSel.executeQuery();
ResultSet rs2 = con.getMetaData().getPrimaryKeys(null, null,
crs.getTableName())
) {
ResultSetMetaData rsmd = crs.getMetaData();
int icolCount = rsmd.getColumnCount();
String[] primaryKeys = new String[icolCount];
int k = 0;
while (rs2.next()) {
primaryKeys[k] = rs2.getString("COLUMN_NAME");
k++;
}
if (rs.next()) {
for (String pkName : primaryKeys) {
if (!isPKNameValid(pkName, rsmd)) {
/* We came here as one of the primary keys
* of the table is not present in the cached
* rowset object, it should be an autoincrement column
* and not included while creating CachedRowSet
* Object, proceed to check for other primary keys
*/
continue;
}
Object crsPK = crs.getObject(pkName);
if (crsPK == null) {
/*
* It is possible that the PK is null on some databases
* and will be filled in at insert time (MySQL for example)
*/
break;
}
String rsPK = rs.getObject(pkName).toString();
if (crsPK.toString().equals(rsPK)) {
returnVal = true;
this.crsResolve.moveToInsertRow();
for (int i = 1; i <= icolCount; i++) {
String colname = (rs.getMetaData()).getColumnName(i);
if (colname.equals(pkName))
this.crsResolve.updateObject(i,rsPK);
else
this.crsResolve.updateNull(i);
}
this.crsResolve.insertRow();
this.crsResolve.moveToCurrentRow();
}
}
}
if (returnVal) {
return returnVal;
}
try {
for (int i = 1; i <= icolCount; i++) {
Object obj = crs.getObject(i);
if (obj != null) {
pstmt.setObject(i, obj);
} else {
pstmt.setNull(i,crs.getMetaData().getColumnType(i));
}
}
pstmt.executeUpdate();
return false;
} catch (SQLException ex) {
/*
* Cursor will come here if executeUpdate fails.
* There can be many reasons why the insertion failed,
* one can be violation of primary key.
* Hence we cannot exactly identify why the insertion failed,
* present the current row as a null row to the caller.
*/
this.crsResolve.moveToInsertRow();
for (int i = 1; i <= icolCount; i++) {
this.crsResolve.updateNull(i);
}
this.crsResolve.insertRow();
this.crsResolve.moveToCurrentRow();
return true;
}
}
}
/**
* Deletes the row in the underlying data source that corresponds to
* a row that has been deleted in the given {@code CachedRowSet} object
* and returns {@code false} if the deletion was successful.
* <P>
* This method is called internally by this writer's {@code writeData}
* method when a row in the rowset has been deleted. The values in the
* deleted row are the same as those that are stored in the original row
* of the given {@code CachedRowSet} object. If the values in the
* original row differ from the row in the underlying data source, the row
* in the data source is not deleted, and {@code deleteOriginalRow}
* returns {@code true} to indicate that there was a conflict.
*
*
* @return {@code false} if the deletion was successful, which means that
* there was no conflict; {@code true} otherwise
* @throws SQLException if there was a database access error
*/
private boolean deleteOriginalRow(CachedRowSet crs, CachedRowSetImpl crsRes) throws SQLException {
PreparedStatement pstmt;
int i;
int idx = 0;
String strSelect;
// Select the row from the database.
ResultSet origVals = crs.getOriginalRow();
origVals.next();
deleteWhere = buildWhereClause(deleteWhere, origVals);
pstmt = con.prepareStatement(selectCmd + deleteWhere,
ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
for (i = 0; i < keyCols.length; i++) {
if (params[i] != null) {
pstmt.setObject(++idx, params[i]);
} else {
continue;
}
}
try {
pstmt.setMaxRows(crs.getMaxRows());
pstmt.setMaxFieldSize(crs.getMaxFieldSize());
pstmt.setEscapeProcessing(crs.getEscapeProcessing());
pstmt.setQueryTimeout(crs.getQueryTimeout());
} catch (Exception ex) {
/*
* Older driver don't support these operations...
*/
;
}
ResultSet rs = pstmt.executeQuery();
if (rs.next() == true) {
if (rs.next()) {
// more than one row
return true;
}
rs.first();
// Now check all the values in rs to be same in
// db also before actually going ahead with deleting
boolean boolChanged = false;
crsRes.moveToInsertRow();
for (i = 1; i <= crs.getMetaData().getColumnCount(); i++) {
Object original = origVals.getObject(i);
Object changed = rs.getObject(i);
if(original != null && changed != null ) {
if(! (original.toString()).equals(changed.toString()) ) {
boolChanged = true;
crsRes.updateObject(i,origVals.getObject(i));
}
} else {
crsRes.updateNull(i);
}
}
crsRes.insertRow();
crsRes.moveToCurrentRow();
if(boolChanged) {
// do not delete as values in db have changed
// deletion will not happen for this row from db
// exit now returning true. i.e. conflict
return true;
} else {
// delete the row.
// Go ahead with deleting,
// don't do anything here
}
String cmd = deleteCmd + deleteWhere;
pstmt = con.prepareStatement(cmd);
idx = 0;
for (i = 0; i < keyCols.length; i++) {
if (params[i] != null) {
pstmt.setObject(++idx, params[i]);
} else {
continue;
}
}
if (pstmt.executeUpdate() != 1) {
return true;
}
pstmt.close();
} else {
// didn't find the row
return true;
}
// no conflict
return false;
}
/**
* Sets the reader for this writer to the given reader.
*
* @throws SQLException if a database access error occurs
*/
public void setReader(CachedRowSetReader reader) throws SQLException {
this.reader = reader;
}
/**
* Gets the reader for this writer.
*
* @throws SQLException if a database access error occurs
*/
public CachedRowSetReader getReader() throws SQLException {
return reader;
}
/**
* Composes a {@code SELECT}, {@code UPDATE}, {@code INSERT},
* and {@code DELETE} statement that can be used by this writer to
* write data to the data source backing the given {@code CachedRowSet}
* object.
*
* @param caller a {@code CachedRowSet} object for which this
* {@code CachedRowSetWriter} object is the writer
* @throws SQLException if a database access error occurs
*/
private void initSQLStatements(CachedRowSet caller) throws SQLException {
int i;
callerMd = caller.getMetaData();
callerColumnCount = callerMd.getColumnCount();
if (callerColumnCount < 1)
// No data, so return.
return;
/*
* If the RowSet has a Table name we should use it.
* This is really a hack to get round the fact that
* a lot of the jdbc drivers can't provide the tab.
*/
String table = caller.getTableName();
if (table == null) {
/*
* attempt to build a table name using the info
* that the driver gave us for the first column
* in the source result set.
*/
table = callerMd.getTableName(1);
if (table == null || table.length() == 0) {
throw new SQLException(resBundle.handleGetObject("crswriter.tname").toString());
}
}
String catalog = callerMd.getCatalogName(1);
String schema = callerMd.getSchemaName(1);
DatabaseMetaData dbmd = con.getMetaData();
/*
* Compose a SELECT statement. There are three parts.
*/
// Project List
selectCmd = "SELECT ";
for (i=1; i <= callerColumnCount; i++) {
selectCmd += callerMd.getColumnName(i);
if ( i < callerMd.getColumnCount() )
selectCmd += ", ";
else
selectCmd += " ";
}
// FROM clause.
selectCmd += "FROM " + buildTableName(dbmd, catalog, schema, table);
/*
* Compose an UPDATE statement.
*/
updateCmd = "UPDATE " + buildTableName(dbmd, catalog, schema, table);
/**
* The following block of code is for checking a particular type of
* query where in there is a where clause. Without this block, if a
* SQL statement is built the "where" clause will appear twice hence
* the DB errors out and a SQLException is thrown. This code also
* considers that the where clause is in the right place as the
* CachedRowSet object would already have been populated with this
* query before coming to this point.
**/
String tempupdCmd = updateCmd.toLowerCase();
int idxupWhere = tempupdCmd.indexOf("where");
if(idxupWhere != -1)
{
updateCmd = updateCmd.substring(0,idxupWhere);
}
updateCmd += "SET ";
/*
* Compose an INSERT statement.
*/
insertCmd = "INSERT INTO " + buildTableName(dbmd, catalog, schema, table);
// Column list
insertCmd += "(";
for (i=1; i <= callerColumnCount; i++) {
insertCmd += callerMd.getColumnName(i);
if ( i < callerMd.getColumnCount() )
insertCmd += ", ";
else
insertCmd += ") VALUES (";
}
for (i=1; i <= callerColumnCount; i++) {
insertCmd += "?";
if (i < callerColumnCount)
insertCmd += ", ";
else
insertCmd += ")";
}
/*
* Compose a DELETE statement.
*/
deleteCmd = "DELETE FROM " + buildTableName(dbmd, catalog, schema, table);
/*
* set the key desriptors that will be
* needed to construct where clauses.
*/
buildKeyDesc(caller);
}
/**
* Returns a fully qualified table name built from the given catalog and
* table names. The given metadata object is used to get the proper order
* and separator.
*
* @param dbmd a {@code DatabaseMetaData} object that contains metadata
* about this writer's {@code CachedRowSet} object
* @param catalog a {@code String} object with the rowset's catalog
* name
* @param table a {@code String} object with the name of the table from
* which this writer's rowset was derived
* @return a {@code String} object with the fully qualified name of the
* table from which this writer's rowset was derived
* @throws SQLException if a database access error occurs
*/
private String buildTableName(DatabaseMetaData dbmd,
String catalog, String schema, String table) throws SQLException {
// trim all the leading and trailing whitespaces,
// white spaces can never be catalog, schema or a table name.
String cmd = "";
catalog = catalog.trim();
schema = schema.trim();
table = table.trim();
if (dbmd.isCatalogAtStart() == true) {
if (catalog != null && catalog.length() > 0) {
cmd += catalog + dbmd.getCatalogSeparator();
}
if (schema != null && schema.length() > 0) {
cmd += schema + ".";
}
cmd += table;
} else {
if (schema != null && schema.length() > 0) {
cmd += schema + ".";
}
cmd += table;
if (catalog != null && catalog.length() > 0) {
cmd += dbmd.getCatalogSeparator() + catalog;
}
}
cmd += " ";
return cmd;
}
/**
* Assigns to the given {@code CachedRowSet} object's
* {@code params}
* field an array whose length equals the number of columns needed
* to uniquely identify a row in the rowset. The array is given
* values by the method {@code buildWhereClause}.
* <P>
* If the {@code CachedRowSet} object's {@code keyCols}
* field has length {@code 0} or is {@code null}, the array
* is set with the column number of every column in the rowset.
* Otherwise, the array in the field {@code keyCols} is set with only
* the column numbers of the columns that are required to form a unique
* identifier for a row.
*
* @param crs the {@code CachedRowSet} object for which this
* {@code CachedRowSetWriter} object is the writer
*
* @throws SQLException if a database access error occurs
*/
private void buildKeyDesc(CachedRowSet crs) throws SQLException {
keyCols = crs.getKeyColumns();
ResultSetMetaData resultsetmd = crs.getMetaData();
if (keyCols == null || keyCols.length == 0) {
ArrayList<Integer> listKeys = new ArrayList<Integer>();
for (int i = 0; i < callerColumnCount; i++ ) {
if(resultsetmd.getColumnType(i+1) != java.sql.Types.CLOB &&
resultsetmd.getColumnType(i+1) != java.sql.Types.STRUCT &&
resultsetmd.getColumnType(i+1) != java.sql.Types.SQLXML &&
resultsetmd.getColumnType(i+1) != java.sql.Types.BLOB &&
resultsetmd.getColumnType(i+1) != java.sql.Types.ARRAY &&
resultsetmd.getColumnType(i+1) != java.sql.Types.OTHER )
listKeys.add(i+1);
}
keyCols = new int[listKeys.size()];
for (int i = 0; i < listKeys.size(); i++ )
keyCols[i] = listKeys.get(i);
}
params = new Object[keyCols.length];
}
/**
* Constructs an SQL {@code WHERE} clause using the given
* string as a starting point. The resulting clause will contain
* a column name and " = ?" for each key column, that is, each column
* that is needed to form a unique identifier for a row in the rowset.
* This {@code WHERE} clause can be added to
* a {@code PreparedStatement} object that updates, inserts, or
* deletes a row.
* <P>
* This method uses the given result set to access values in the
* {@code CachedRowSet} object that called this writer. These
* values are used to build the array of parameters that will serve as
* replacements for the "?" parameter placeholders in the
* {@code PreparedStatement} object that is sent to the
* {@code CachedRowSet} object's underlying data source.
*
* @param whereClause a {@code String} object that is an empty
* string ("")
* @param rs a {@code ResultSet} object that can be used
* to access the {@code CachedRowSet} object's data
* @return a {@code WHERE} clause of the form "{@code WHERE}
* columnName = ? AND columnName = ? AND columnName = ? ..."
* @throws SQLException if a database access error occurs
*/
private String buildWhereClause(String whereClause,
ResultSet rs) throws SQLException {
whereClause = "WHERE ";
for (int i = 0; i < keyCols.length; i++) {
if (i > 0) {
whereClause += "AND ";
}
whereClause += callerMd.getColumnName(keyCols[i]);
params[i] = rs.getObject(keyCols[i]);
if (rs.wasNull() == true) {
whereClause += " IS NULL ";
} else {
whereClause += " = ? ";
}
}
return whereClause;
}
void updateResolvedConflictToDB(CachedRowSet crs, Connection con) throws SQLException {
//String updateExe = ;
PreparedStatement pStmt ;
String strWhere = "WHERE " ;
String strExec =" ";
String strUpdate = "UPDATE ";
int icolCount = crs.getMetaData().getColumnCount();
int keyColumns[] = crs.getKeyColumns();
Object param[];
String strSet="";
strWhere = buildWhereClause(strWhere, crs);
if (keyColumns == null || keyColumns.length == 0) {
keyColumns = new int[icolCount];
for (int i = 0; i < keyColumns.length; ) {
keyColumns[i] = ++i;
}
}
param = new Object[keyColumns.length];
strUpdate = "UPDATE " + buildTableName(con.getMetaData(),
crs.getMetaData().getCatalogName(1),
crs.getMetaData().getSchemaName(1),
crs.getTableName());
// changed or updated values will become part of
// set clause here
strUpdate += "SET ";
boolean first = true;
for (int i=1; i<=icolCount;i++) {
if (crs.columnUpdated(i)) {
if (first == false) {
strSet += ", ";
}
strSet += crs.getMetaData().getColumnName(i);
strSet += " = ? ";
first = false;
} //end if
} //end for
// keycols will become part of where clause
strUpdate += strSet;
strWhere = "WHERE ";
for (int i = 0; i < keyColumns.length; i++) {
if (i > 0) {
strWhere += "AND ";
}
strWhere += crs.getMetaData().getColumnName(keyColumns[i]);
param[i] = crs.getObject(keyColumns[i]);
if (crs.wasNull() == true) {
strWhere += " IS NULL ";
} else {
strWhere += " = ? ";
}
}
strUpdate += strWhere;
pStmt = con.prepareStatement(strUpdate);
int idx =0;
for (int i = 0; i < icolCount; i++) {
if(crs.columnUpdated(i+1)) {
Object obj = crs.getObject(i+1);
if (obj != null) {
pStmt.setObject(++idx, obj);
} else {
pStmt.setNull(i + 1,crs.getMetaData().getColumnType(i + 1));
} //end if ..else
} //end if crs.column...
} //end for
// Set the key cols for after WHERE =? clause
for (int i = 0; i < keyColumns.length; i++) {
if (param[i] != null) {
pStmt.setObject(++idx, param[i]);
}
}
int id = pStmt.executeUpdate();
}
/**
*
*/
public void commit() throws SQLException {
con.commit();
if (reader.getCloseConnection() == true) {
con.close();
}
}
public void commit(CachedRowSetImpl crs, boolean updateRowset) throws SQLException {
con.commit();
if(updateRowset) {
if(crs.getCommand() != null)
crs.execute(con);
}
if (reader.getCloseConnection() == true) {
con.close();
}
}
/**
*
*/
public void rollback() throws SQLException {
con.rollback();
if (reader.getCloseConnection() == true) {
con.close();
}
}
/**
*
*/
public void rollback(Savepoint s) throws SQLException {
con.rollback(s);
if (reader.getCloseConnection() == true) {
con.close();
}
}
private void readObject(ObjectInputStream ois) throws IOException, ClassNotFoundException {
// Default state initialization happens here
ois.defaultReadObject();
// Initialization of Res Bundle happens here .
try {
resBundle = JdbcRowSetResourceBundle.getJdbcRowSetResourceBundle();
} catch(IOException ioe) {
throw new RuntimeException(ioe);
}
}
static final long serialVersionUID =-8506030970299413976L;
/**
* Validate whether the Primary Key is known to the CachedRowSet. If it is
* not, it is an auto-generated key
* @param pk - Primary Key to validate
* @param rsmd - ResultSetMetadata for the RowSet
* @return true if found, false otherwise (auto generated key)
*/
private boolean isPKNameValid(String pk, ResultSetMetaData rsmd) throws SQLException {
boolean isValid = false;
int cols = rsmd.getColumnCount();
for(int i = 1; i<= cols; i++) {
String colName = rsmd.getColumnClassName(i);
if(colName.equalsIgnoreCase(pk)) {
isValid = true;
break;
}
}
return isValid;
}
}