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) { } } }