public int update(String tableName, String colName, Object newValue, Object oldValue)
throws SQLException {
// syntax:
// update [table] set [column] = [new value]
// where [column] = [old value]
int updated = 0;
boolean hasWhere = false;
if ((oldValue != null) && !(oldValue.equals(""))) {
hasWhere = true;
}
PreparedStatement pstmt = pstmtBuilder.buildUpdateStatement(conn, tableName, colName, hasWhere);
try {
pstmt.setObject(1, newValue);
if (hasWhere) {
pstmt.setObject(2, oldValue);
}
updated = pstmt.executeUpdate();
} catch (SQLException sqle) {
System.out.println(sqle);
} catch (Exception e) {
System.out.println(e);
} finally {
try {
pstmt.close();
conn.close();
} catch (Exception e) {
System.out.println(e);
}
}
return updated;
}
public static void setNumeric(PreparedStatement stm, int i, Number value) throws SQLException {
if (value != null) {
stm.setObject(i, value, Types.NUMERIC);
} else {
stm.setNull(i, Types.NUMERIC);
}
}
private void setObject(PreparedStatement preparedStatement, int column, Object value)
throws SQLException {
// Allow java.util.Date conversion for JDBC drivers that don't provide this feature
if (value instanceof Date) {
value = new Timestamp(((Date) value).getTime());
} else if (value instanceof Calendar) {
value = new Timestamp(((Calendar) value).getTimeInMillis());
}
preparedStatement.setObject(column, value);
}
/**
* 根据数据库连接和表和相关列，做数据库的插入操作
*
* @param conn
* @param tablename
* @param colnames
* @throws SQLException
*/
public void doInsertDB(Connection conn, String tablename, String[] colnames) throws SQLException {
StringBuilder insertsql = new StringBuilder();
insertsql.append("insert into ").append(tablename);
StringBuilder lstr = new StringBuilder(), rstr = new StringBuilder();
lstr.append("(").append(colnames[0]);
rstr.append("(?");
for (int i = 1; i < colnames.length; i++) {
lstr.append(",").append(colnames[i]);
rstr.append(",?");
}
lstr.append(")");
rstr.append(")");
insertsql.append(lstr).append("values").append(rstr);
PreparedStatement ps = null;
try {
// System.out.println(insertsql.toString()) ;
ps = conn.prepareStatement(insertsql.toString());
for (int i = 0; i < colnames.length; i++) {
Object tmpo = GDB.prepareObjVal(this.getValue(colnames[i]));
if (tmpo != null) {
ps.setObject(i + 1, tmpo);
} else {
int sqlt =
this.belongToDT
.getColumn(colnames[i])
.getJdbcType(); // JavaColumnInfo.Class2SqlType(c)
if (sqlt == java.sql.Types.BLOB) // for sqlserver driver
// NullPointer error
ps.setObject(1 + i, new byte[0]);
else ps.setNull(1 + i, sqlt);
}
}
ps.executeUpdate();
} finally {
if (ps != null) ps.close();
}
}
public void doUpdateDB(Connection conn, String tablename, String uniquecol, String[] cols)
throws SQLException {
StringBuilder upsql = new StringBuilder();
upsql.append("update ").append(tablename).append(" set ");
upsql.append(cols[0]).append("=?");
for (int i = 1; i < cols.length; i++) {
upsql.append(",").append(cols[i]).append("=?");
}
upsql.append(" where ").append(uniquecol).append("=?");
String[] allcols = new String[cols.length + 1];
System.arraycopy(cols, 0, allcols, 0, cols.length);
allcols[cols.length] = uniquecol;
PreparedStatement ps = null;
try {
// System.out.println(upsql.toString());
ps = conn.prepareStatement(upsql.toString());
for (int i = 0; i < allcols.length; i++) {
Object tmpo = GDB.prepareObjVal(this.getValue(allcols[i]));
if (tmpo != null) {
ps.setObject(i + 1, tmpo);
} else {
int sqlt =
this.belongToDT
.getColumn(allcols[i])
.getJdbcType(); // JavaColumnInfo.Class2SqlType(c)
if (sqlt == java.sql.Types.BLOB) // for sqlserver driver
// NullPointer error
ps.setObject(1 + i, new byte[0]);
else ps.setNull(1 + i, sqlt);
}
}
ps.executeUpdate();
} finally {
if (ps != null) ps.close();
}
}
public void doDeleteDB(Connection conn, String tablename, String uniquecol) throws SQLException {
StringBuilder delsql = new StringBuilder();
delsql.append("delete from ").append(tablename);
delsql.append(" where ").append(uniquecol).append("=?");
PreparedStatement ps = null;
try {
ps = conn.prepareStatement(delsql.toString());
Object tmpo = GDB.prepareObjVal(this.getValue(uniquecol));
if (tmpo == null) throw new SQLException("no key value found");
ps.setObject(1, tmpo);
ps.executeUpdate();
} finally {
if (ps != null) ps.close();
}
}
public void runReplicate() throws InterruptedException {
if (verbose) log.append("Started at " + Calendar.getInstance().getTime() + "\r\n");
Connection connectionFrom = null;
Connection connectionTo = null;
try {
connectionFrom = providerI.getConnection(dbFrom);
connectionTo = providerI.getConnection(dbTo);
PreparedStatement preparedStatementFrom = null;
PreparedStatement preparedStatementTo = null;
ResultSet resultSet = null;
for (Iterator<HashMap<String, String>> HashMapIterator = tables.iterator();
HashMapIterator.hasNext(); ) {
try {
HashMap<String, String> h = HashMapIterator.next();
String name = h.get("name");
String name2 = h.get("name2");
String where1 = h.get("where1");
String where2 = h.get("where2");
if (verbose)
log.append("Replicating " + dbFrom + "/" + name + " to " + dbTo + "/" + name2 + "\r\n");
preparedStatementFrom =
connectionFrom.prepareStatement("select * from " + name + " WHERE 1=1 " + where1);
boolean b = preparedStatementFrom.execute();
HashMap meta = new HashMap();
// 1. find all ids from DB1
ArrayList db1 = new ArrayList();
String nullMark = "____NULL";
if (b) {
resultSet = preparedStatementFrom.getResultSet();
ResultSetMetaData setMetaData = resultSet.getMetaData();
for (int i = 1; i <= setMetaData.getColumnCount(); i++) {
meta.put(
setMetaData.getColumnLabel(i).toLowerCase(), setMetaData.getColumnClassName(i));
}
while (resultSet.next()) {
HashMap results = new HashMap();
for (int i = 1; i <= setMetaData.getColumnCount(); i++) {
String columnName = setMetaData.getColumnLabel(i).toLowerCase();
Object o = resultSet.getObject(i);
if (o != null) {
results.put(columnName, o);
} else {
results.put(columnName + nullMark, nullMark);
}
}
db1.add(results);
}
resultSet.close();
} else {
log.append("Couldn't execute select from " + dbFrom + "/" + name + " /r/n");
return;
}
StringBuffer allIds = new StringBuffer();
HashMap records1 = new HashMap();
HashMap toInsert = new HashMap();
HashMap toUpdate = new HashMap();
for (Iterator iterator = db1.iterator(); iterator.hasNext(); ) {
HashMap record = (HashMap) iterator.next();
if (allIds.length() != 0) {
allIds.append(",");
}
allIds.append(record.get("id"));
records1.put(record.get("id"), record);
}
toInsert.putAll(records1);
// 2. find all ids to delete in DB2;
if (allIds.length() > 0) {
preparedStatementTo =
connectionTo.prepareStatement(
"delete from "
+ name2
+ " where id not in ("
+ allIds.toString()
+ ")"
+ where2);
if (verbose)
log.append(
"deleted from "
+ dbTo
+ "/"
+ name2
+ " "
+ preparedStatementTo.executeUpdate()
+ " records;\r\n");
} else {
if (verbose)
log.append(
"No records in "
+ dbFrom
+ "/"
+ name
+ ", nothing to delete in "
+ dbTo
+ "/"
+ name2
+ " ;\r\n");
}
// 3. find all ids from DB2;
preparedStatementTo =
connectionTo.prepareStatement("select * from " + name2 + " WHERE 1=1 " + where2);
b = preparedStatementTo.execute();
HashMap meta2 = new HashMap();
ArrayList db2 = new ArrayList();
if (b) {
resultSet = preparedStatementTo.getResultSet();
ResultSetMetaData setMetaData = resultSet.getMetaData();
for (int i = 1; i <= setMetaData.getColumnCount(); i++) {
meta2.put(
setMetaData.getColumnLabel(i).toLowerCase(), setMetaData.getColumnClassName(i));
}
while (resultSet.next()) {
HashMap results = new HashMap();
for (int i = 1; i <= setMetaData.getColumnCount(); i++) {
String columnName = setMetaData.getColumnLabel(i).toLowerCase();
Object o = resultSet.getObject(i);
if (o != null) {
results.put(columnName, o);
} else {
results.put(columnName + nullMark, nullMark);
}
}
db2.add(results);
}
} else {
log.append("Couldn't execute select from " + dbTo + "/" + name2 + " /r/n");
return;
}
// compare meta-data;
{
HashMap temp = new HashMap();
{
temp.putAll(meta);
Set set = meta2.keySet();
for (Iterator iteratorSet = set.iterator(); iteratorSet.hasNext(); ) {
Object o = iteratorSet.next();
if (meta.containsKey(o)) {
if (meta.get(o).equals(meta2.get(o))) {
temp.remove(o);
}
} else {
log.append("ERROR: Meta data not equals! \r\n");
log.append(o + "\t" + temp.get(o) + " not present in table " + name + "\r\n");
}
}
}
if (!temp.isEmpty()) {
log.append("ERROR: Meta data not equals! \r\n");
Set set = temp.keySet();
for (Iterator iteratorSet = set.iterator(); iteratorSet.hasNext(); ) {
Object o = iteratorSet.next();
log.append(o + "\t" + temp.get(o) + " != " + meta2.get(o) + "\r\n");
}
return;
}
}
for (Iterator iterator = db2.iterator(); iterator.hasNext(); ) {
HashMap db2Record = (HashMap) iterator.next();
if (toInsert.containsKey(db2Record.get("id"))) {
HashMap db1Record = (HashMap) toInsert.get(db2Record.get("id"));
boolean equal = true;
Set set = meta2.keySet();
for (Iterator iteratorSet = set.iterator(); equal && iteratorSet.hasNext(); ) {
String columnName2 = (String) iteratorSet.next();
if (db2Record.containsKey(columnName2 + nullMark)
|| db1Record.containsKey(columnName2 + nullMark)) {
equal =
db2Record.containsKey(columnName2 + nullMark)
&& db1Record.containsKey(columnName2 + nullMark);
} else {
// checking not-null;
equal = equalRecords(db1Record.get(columnName2), db2Record.get(columnName2));
}
}
if (!equal) {
toUpdate.put(db2Record.get("id"), toInsert.get(db2Record.get("id")));
}
toInsert.remove(db2Record.get("id"));
} else {
// this case shouldn't happen at all, since we've deleted all such records
}
}
log.append(
"Found "
+ toUpdate.size()
+ " to update, and "
+ toInsert.size()
+ " to insert.\r\n");
int totalUpdated = 0;
// 4. calculate all to update in DB2
if (!toUpdate.isEmpty()) {
Set set = toUpdate.keySet();
for (Iterator iteratorSet = set.iterator(); iteratorSet.hasNext(); ) {
StringBuffer sql = new StringBuffer();
Object id = iteratorSet.next();
HashMap r = (HashMap) toUpdate.get(id);
sql.append("UPDATE " + name2 + " SET ");
StringBuffer values = new StringBuffer();
Set en = meta2.keySet();
for (Iterator iteratorSetEn = en.iterator(); iteratorSetEn.hasNext(); ) {
Object o = iteratorSetEn.next();
if (!o.equals("id")) {
if (values.length() != 0) {
values.append(",");
}
Object quote = dbQuotes.get(dbToType);
if (quote == null) {
quote = "";
}
values.append(quote).append(o).append(quote);
values.append(" = ? ");
}
}
values.append(" WHERE id = '" + r.get("id") + "';");
PreparedStatement statement =
connectionTo.prepareStatement(sql.toString() + values.toString());
en = meta2.keySet();
int i = 0;
for (Iterator iteratorSetEn = en.iterator(); iteratorSetEn.hasNext(); ) {
Object o = iteratorSetEn.next();
if (!o.equals("id")) {
i++;
statement.setObject(i, r.get(o));
}
}
try {
totalUpdated += statement.executeUpdate();
} catch (SQLException e) {
e.printStackTrace();
log.append("Error occured: " + e + "\r\n");
}
}
}
if (verbose) log.append("Updated " + totalUpdated + " records.\r\n");
// 4. calculate all to insert to DB2
if (!toInsert.isEmpty()) {
StringBuffer header = new StringBuffer();
if (header.length() == 0) {
header.append(" INSERT INTO " + name2 + " (");
StringBuffer columns = new StringBuffer();
Set en = meta2.keySet();
for (Iterator iteratorSetEn = en.iterator(); iteratorSetEn.hasNext(); ) {
Object o = iteratorSetEn.next();
if (columns.length() != 0) {
columns.append(",");
}
Object quote = dbQuotes.get(dbToType);
if (quote == null) {
quote = "";
}
columns.append(quote).append(o).append(quote);
}
header.append(columns.toString());
header.append(") VALUES ");
}
Set enumeration = toInsert.keySet();
for (Iterator iteratorSetX = enumeration.iterator(); iteratorSetX.hasNext(); ) {
Object id = iteratorSetX.next();
HashMap r = (HashMap) toInsert.get(id);
StringBuffer values = new StringBuffer();
if (values.length() != 0) {
values.append(",");
}
values.append("(");
StringBuffer columns = new StringBuffer();
Set en = meta2.keySet();
for (Iterator iteratorSetEn = en.iterator(); iteratorSetEn.hasNext(); ) {
Object o = iteratorSetEn.next();
if (columns.length() != 0) {
columns.append(",");
}
columns.append(" ? ");
}
values.append(columns.toString());
values.append(");");
PreparedStatement statement =
connectionTo.prepareStatement(header.toString() + values.toString());
en = meta2.keySet();
int i = 0;
for (Iterator iteratorSetEn = en.iterator(); iteratorSetEn.hasNext(); ) {
Object o = iteratorSetEn.next();
i++;
statement.setObject(i, r.get(o));
}
statement.execute();
}
}
if (verbose) log.append("Replication finished OK.\r\n");
} catch (Exception e) {
log.append("Some error occured: " + e + "\r\n");
log.append(e.getMessage() + "\r\n");
e.printStackTrace();
}
}
} catch (Exception e) {
log.append("Error with query = " + e);
log.append(e.getMessage());
return;
} finally {
try {
if (connectionFrom != null && !connectionFrom.isClosed()) {
connectionFrom.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
try {
if (connectionTo != null && !connectionTo.isClosed()) {
connectionTo.close();
}
} catch (SQLException e) {
e.printStackTrace();
}
}
if (verbose) log.append("Ended at " + Calendar.getInstance().getTime());
}
/**
* Method called by the Form panel to update existing data.
*
* @param oldPersistentObject original value object, previous to the changes
* @param persistentObject value object to save
* @return an ErrorResponse value object in case of errors, VOResponse if the operation is
* successfully completed
*/
public Response updateRecord(ValueObject oldPersistentObject, ValueObject persistentObject)
throws Exception {
PreparedStatement stmt = null;
try {
stmt =
conn.prepareStatement(
"update DEMO4 set TEXT=?,DECNUM=?,CURRNUM=?,THEDATE=?,COMBO=?,CHECK_BOX=?,RADIO=?,CODE=?,TA=?,FORMATTED_TEXT=?,URI=?,LINK_LABEL=?,YEAR=?,FILENAME=? where TEXT=?");
DetailTestVO vo = (DetailTestVO) persistentObject;
DetailTestVO oldVO = (DetailTestVO) oldPersistentObject;
stmt.setObject(
6, vo.getCheckValue() == null || !vo.getCheckValue().booleanValue() ? "N" : "Y");
stmt.setString(5, vo.getCombo().getCode());
stmt.setBigDecimal(3, vo.getCurrencyValue());
stmt.setDate(4, vo.getDateValue());
stmt.setBigDecimal(2, vo.getNumericValue());
stmt.setObject(
7,
vo.getRadioButtonValue() == null || !vo.getRadioButtonValue().booleanValue() ? "N" : "Y");
stmt.setString(1, vo.getStringValue());
stmt.setString(8, vo.getLookupValue());
stmt.setString(9, vo.getTaValue());
stmt.setString(10, vo.getFormattedTextValue());
stmt.setString(11, vo.getUri());
stmt.setString(12, vo.getLinkLabel());
stmt.setBigDecimal(13, vo.getYear());
stmt.setString(14, vo.getFilename());
stmt.setString(15, oldVO.getStringValue());
stmt.execute();
stmt.close();
stmt = conn.prepareStatement("delete from DEMO4_LIST_VALUES where TEXT=?");
stmt.setString(1, pk);
stmt.execute();
if (vo.getListValues() != null) {
stmt.close();
stmt = conn.prepareStatement("insert into DEMO4_LIST_VALUES(TEXT,CODE) values(?,?)");
for (int i = 0; i < vo.getListValues().size(); i++) {
stmt.setString(1, pk);
stmt.setString(2, vo.getListValues().get(i).toString());
stmt.execute();
}
}
try {
if (vo.getFilename() != null && vo.getFile() != null) {
File f = new File(vo.getFilename());
BufferedOutputStream out = new BufferedOutputStream(new FileOutputStream(f));
out.write(vo.getFile());
out.close();
}
} catch (Exception ex) {
ex.printStackTrace();
}
// this instruction is no more needed: the grid has been linked to the Form (see Form.linkGrid
// method...)
// gridFrame.reloadData();
return new VOResponse(vo);
} catch (SQLException ex) {
ex.printStackTrace();
return new ErrorResponse(ex.getMessage());
} finally {
try {
stmt.close();
conn.commit();
} catch (SQLException ex1) {
}
}
}
