List of usage examples for java.sql PreparedStatement setTimestamp
void setTimestamp(int parameterIndex, java.sql.Timestamp x) throws SQLException;
java.sql.Timestamp value. From source file:com.gtwm.pb.model.manageData.DataManagement.java
/** * Used by both the public saveRecord and globalEdit methods *//*from www . j a v a2 s. co m*/ private void saveRecord(HttpServletRequest request, TableInfo table, LinkedHashMap<BaseField, BaseValue> dataToSave, boolean newRecord, Set<Integer> rowIds, SessionDataInfo sessionData, List<FileItem> multipartItems) throws InputRecordException, ObjectNotFoundException, SQLException, CantDoThatException, CodingErrorException, DisallowedException, MissingParametersException { if ((dataToSave.size() == 0) && (!newRecord)) { // Note: this does actually happen quite a lot, from two particular // users, therefore I've commented out the log warning. // Haven't tracked down the cause but it doesn't seem to be creating // a problem. // logger.warn("Call to saveRecord with no data to save. User = " // + request.getRemoteUser() + ", table = " + table + ", rowIds = " // + rowIds); return; } this.setHiddenFieldValues(request, table, dataToSave, newRecord); boolean globalEdit = false; int rowId = -1; if (rowIds.size() > 1) { globalEdit = true; } else if (rowIds.size() == 1) { rowId = (new LinkedList<Integer>(rowIds)).getFirst(); } else { throw new ObjectNotFoundException("Row ID list " + rowIds + " is invalid"); } StringBuilder SQLCodeBuilder = new StringBuilder(); // Generate CSV of fields and placeholders to use in update/insert SQL // string StringBuilder fieldsCsvBuilder = new StringBuilder(); StringBuilder fieldsAndPlaceholdersCsvBuilder = new StringBuilder(); StringBuilder valuePlaceholdersCsvBuilder = new StringBuilder(); for (BaseField field : dataToSave.keySet()) { fieldsCsvBuilder.append(field.getInternalFieldName()); fieldsCsvBuilder.append(", "); valuePlaceholdersCsvBuilder.append("?, "); fieldsAndPlaceholdersCsvBuilder.append(field.getInternalFieldName()); fieldsAndPlaceholdersCsvBuilder.append("=?, "); } // Used if doing an INSERT String fieldsCsv = fieldsCsvBuilder.toString(); String valuePlaceholdersCsv = valuePlaceholdersCsvBuilder.toString(); // Used if doing an UPDATE String fieldsAndPlaceholdersCsv = fieldsAndPlaceholdersCsvBuilder.toString(); if (!fieldsCsv.equals("")) { fieldsCsv = fieldsCsv.substring(0, fieldsCsv.length() - 2); valuePlaceholdersCsv = valuePlaceholdersCsv.substring(0, valuePlaceholdersCsv.length() - 2); fieldsAndPlaceholdersCsv = fieldsAndPlaceholdersCsv.substring(0, fieldsAndPlaceholdersCsv.length() - 2); } if (newRecord) { SQLCodeBuilder.append("INSERT INTO " + table.getInternalTableName()); if (fieldsCsv.equals("")) { SQLCodeBuilder.append(" VALUES(default)"); } else { SQLCodeBuilder.append("(" + fieldsCsv + ") VALUES (" + valuePlaceholdersCsv + ")"); } } else { SQLCodeBuilder.append("UPDATE " + table.getInternalTableName() + " SET " + fieldsAndPlaceholdersCsv); if (globalEdit) { // add filter for various row ids SQLCodeBuilder.append(" WHERE " + table.getPrimaryKey().getInternalFieldName() + " in (?"); for (int i = 1; i < rowIds.size(); i++) { SQLCodeBuilder.append(",?"); } SQLCodeBuilder.append(")"); } else { // add filter for single row id SQLCodeBuilder.append(" WHERE " + table.getPrimaryKey().getInternalFieldName() + "=?"); } } Connection conn = null; int fieldNumber = 0; // Will be set if we're inserting a record int newRowId = -1; TableDataInfo tableData = new TableData(table); try { conn = this.dataSource.getConnection(); conn.setAutoCommit(false); PreparedStatement statement = conn.prepareStatement(SQLCodeBuilder.toString()); for (BaseField field : dataToSave.keySet()) { // If an exception is raised, currentField will be the field // which caused it // currentField = field; fieldNumber++; BaseValue fieldValue = dataToSave.get(field); if (field instanceof FileField) { if (fieldValue.isNull() || fieldValue.toString().equals("")) { throw new InputRecordException("No file specified for the upload", field); } } if (fieldValue.isNull()) { statement.setNull(fieldNumber, Types.NULL); } else { if (fieldValue instanceof TextValue) { String textValue = ((TextValue) fieldValue).toXmlString(); statement.setString(fieldNumber, textValue); } else if (fieldValue instanceof IntegerValue) { // if no related value, set relation field to null if (field instanceof RelationField && (((IntegerValue) fieldValue).getValueInteger() == -1) || (fieldValue.isNull())) { statement.setNull(fieldNumber, Types.NULL); } else { statement.setInt(fieldNumber, ((IntegerValue) fieldValue).getValueInteger()); } } else if (fieldValue instanceof DurationValue) { statement.setString(fieldNumber, ((DurationValue) fieldValue).getSqlFormatInterval()); } else if (fieldValue instanceof DecimalValue) { statement.setDouble(fieldNumber, ((DecimalValue) fieldValue).getValueFloat()); } else if (fieldValue instanceof DateValue) { if (((DateValue) fieldValue).getValueDate() != null) { java.util.Date javaDateValue = ((DateValue) fieldValue).getValueDate().getTime(); java.sql.Timestamp sqlTimestampValue = new java.sql.Timestamp(javaDateValue.getTime()); statement.setTimestamp(fieldNumber, sqlTimestampValue); } else { statement.setTimestamp(fieldNumber, null); } } else if (fieldValue instanceof CheckboxValue) { statement.setBoolean(fieldNumber, ((CheckboxValue) fieldValue).getValueBoolean()); } else if (fieldValue instanceof FileValue) { statement.setString(fieldNumber, ((FileValue) fieldValue).toString()); } else { throw new CodingErrorException("Field value " + fieldValue + " is of unknown type " + fieldValue.getClass().getSimpleName()); } } } // We've finished setting individual fields, if an SQL error occurs // after here we won't know which // field caused it without looking for it by other means // currentField = null; if (!newRecord) { if (globalEdit) { // Fill in the 'WHERE [row id field] in (?,..,?)' for use in // the UPDATE statement for (Integer aRowId : rowIds) { if (tableData.isRecordLocked(conn, sessionData, aRowId)) { throw new CantDoThatException( "Record " + aRowId + " from table " + table + " is locked to prevent editing"); } statement.setInt(++fieldNumber, aRowId); } } else { // Fill in the 'WHERE [row id field]=?' for use in the // UPDATE statement if (tableData.isRecordLocked(conn, sessionData, rowId)) { throw new CantDoThatException( "Record " + rowId + " from table " + table + " is locked to prevent editing"); } statement.setInt(fieldNumber + 1, rowId); } } int numRowsAffected = statement.executeUpdate(); statement.close(); if ((numRowsAffected != 1) && (!globalEdit)) { conn.rollback(); if (numRowsAffected > 0) { throw new ObjectNotFoundException(String.valueOf(numRowsAffected) + " records would be altered during a single record save"); } else { throw new ObjectNotFoundException( "The current record can't be found to edit - perhaps someone else has deleted it"); } } if (newRecord) { // Find the newly inserted Row ID // postgres-specific code, not database independent String SQLCode = "SELECT currval('" + table.getInternalTableName() + "_" + table.getPrimaryKey().getInternalFieldName() + "_seq')"; statement = conn.prepareStatement(SQLCode); ResultSet results = statement.executeQuery(); if (results.next()) { newRowId = results.getInt(1); } else { results.close(); statement.close(); throw new SQLException( "Row ID not found for the newly inserted record. '" + SQLCodeBuilder + "' didn't work"); } results.close(); statement.close(); } conn.commit(); } catch (SQLException sqlex) { // Find out which field caused the error by looking for internal // field names in the error message String errorMessage = sqlex.getMessage(); for (BaseField possibleCauseField : dataToSave.keySet()) { if (errorMessage.contains(possibleCauseField.getInternalFieldName())) { if (errorMessage.contains("check constraint")) { errorMessage = "The value " + dataToSave.get(possibleCauseField) + " falls outside the allowed range"; } else if (errorMessage.contains("not-null constraint")) { errorMessage = "No value entered"; } else if (errorMessage.contains("unique constraint")) { errorMessage = "Value " + dataToSave.get(possibleCauseField) + " is already in the database and cannot be entered again"; } else if (errorMessage.contains("foreign key constraint") && possibleCauseField instanceof RelationField) { errorMessage = "Please select a valid " + ((RelationField) possibleCauseField).getRelatedTable() + " record first"; } else { errorMessage = "Value " + dataToSave.get(possibleCauseField) + " not allowed (" + Helpers.replaceInternalNames(errorMessage, table.getDefaultReport()) + ")"; } throw new InputRecordException(errorMessage, possibleCauseField, sqlex); } } // Not able to find field errorMessage = Helpers.replaceInternalNames(errorMessage, table.getDefaultReport()); throw new InputRecordException(errorMessage, null, sqlex); } finally { if (conn != null) { conn.close(); } } // If any fields were files to upload, do the actual uploads. // Do this after the commit in case the uploads take a long time and // time out the SQL connection. for (BaseField field : dataToSave.keySet()) { if (field instanceof FileField) { try { if (newRecord) { this.uploadFile(request, (FileField) field, (FileValue) dataToSave.get(field), newRowId, multipartItems); } else { this.uploadFile(request, (FileField) field, (FileValue) dataToSave.get(field), rowId, multipartItems); } } catch (CantDoThatException cdtex) { throw new InputRecordException("Error uploading file: " + cdtex.getMessage(), field, cdtex); } catch (FileUploadException fuex) { throw new InputRecordException("Error uploading file: " + fuex.getMessage(), field, fuex); } } } if (newRecord) { sessionData.setRowId(table, newRowId); } this.logLastDataChangeTime(request); logLastTableDataChangeTime(table); UsageLogger usageLogger = new UsageLogger(this.dataSource); AppUserInfo user = null; if (request.getRemoteUser() == null) { user = ServletUtilMethods.getPublicUserForRequest(request, this.authManager.getAuthenticator()); } else { user = this.authManager.getUserByUserName(request, request.getRemoteUser()); } // Send websocket notification // UsageLogger.sendNotification(user, table, sessionData.getReport(), // rowId, "edit", "Record saved: " + dataToSave); // Log everything apart from hidden (auto set) fields Map<BaseField, BaseValue> dataToLog = new LinkedHashMap<BaseField, BaseValue>(); for (Map.Entry<BaseField, BaseValue> entrySet : dataToSave.entrySet()) { BaseField field = entrySet.getKey(); if (!field.getHidden()) { BaseValue value = entrySet.getValue(); dataToLog.put(field, value); } } if (newRecord) { usageLogger.logDataChange(user, table, null, AppAction.SAVE_NEW_RECORD, newRowId, dataToLog.toString()); } else if (globalEdit) { // TODO: need better logging of global edits usageLogger.logDataChange(user, table, null, AppAction.GLOBAL_EDIT, rowId, dataToLog.toString()); } else { BaseField fieldUpdated = null; Set<BaseField> fieldSet = new TreeSet<BaseField>(); for (BaseField field : dataToSave.keySet()) { if (!field.getHidden()) { fieldSet.add(field); } } if (fieldSet.size() == 1) { fieldUpdated = new LinkedList<BaseField>(fieldSet).getFirst(); } usageLogger.logDataChange(user, table, fieldUpdated, AppAction.UPDATE_RECORD, rowId, dataToLog.toString()); } UsageLogger.startLoggingThread(usageLogger); }
From source file:com.oltpbenchmark.benchmarks.tpcc.TPCCLoader.java
protected int loadCust(int whseKount, int distWhseKount, int custDistKount) { int k = 0;/* ww w. jav a 2 s .c o m*/ int t = 0; Customer customer = new Customer(); History history = new History(); PrintWriter outHist = null; try { PreparedStatement custPrepStmt = getInsertStatement(TPCCConstants.TABLENAME_CUSTOMER); PreparedStatement histPrepStmt = getInsertStatement(TPCCConstants.TABLENAME_HISTORY); now = new java.util.Date(); if (outputFiles == true) { out = new PrintWriter(new FileOutputStream(fileLocation + "customer.csv")); LOG.debug("\nWriting Customer file to: " + fileLocation + "customer.csv"); outHist = new PrintWriter(new FileOutputStream(fileLocation + "cust-hist.csv")); LOG.debug("\nWriting Customer History file to: " + fileLocation + "cust-hist.csv"); } t = (whseKount * distWhseKount * custDistKount * 2); LOG.debug("\nStart Cust-Hist Load for " + t + " Cust-Hists @ " + now + " ..."); for (int w = 1; w <= whseKount; w++) { for (int d = 1; d <= distWhseKount; d++) { for (int c = 1; c <= custDistKount; c++) { Timestamp sysdate = new java.sql.Timestamp(System.currentTimeMillis()); customer.c_id = c; customer.c_d_id = d; customer.c_w_id = w; // discount is random between [0.0000 ... 0.5000] customer.c_discount = (float) (TPCCUtil.randomNumber(1, 5000, gen) / 10000.0); if (TPCCUtil.randomNumber(1, 100, gen) <= 10) { customer.c_credit = "BC"; // 10% Bad Credit } else { customer.c_credit = "GC"; // 90% Good Credit } if (c <= 1000) { customer.c_last = TPCCUtil.getLastName(c - 1); } else { customer.c_last = TPCCUtil.getNonUniformRandomLastNameForLoad(gen); } customer.c_first = TPCCUtil.randomStr(TPCCUtil.randomNumber(8, 16, gen)); customer.c_credit_lim = 50000; customer.c_balance = -10; customer.c_ytd_payment = 10; customer.c_payment_cnt = 1; customer.c_delivery_cnt = 0; customer.c_street_1 = TPCCUtil.randomStr(TPCCUtil.randomNumber(10, 20, gen)); customer.c_street_2 = TPCCUtil.randomStr(TPCCUtil.randomNumber(10, 20, gen)); customer.c_city = TPCCUtil.randomStr(TPCCUtil.randomNumber(10, 20, gen)); customer.c_state = TPCCUtil.randomStr(3).toUpperCase(); // TPC-C 4.3.2.7: 4 random digits + "11111" customer.c_zip = TPCCUtil.randomNStr(4) + "11111"; customer.c_phone = TPCCUtil.randomNStr(16); customer.c_since = sysdate; customer.c_middle = "OE"; customer.c_data = TPCCUtil.randomStr(TPCCUtil.randomNumber(300, 500, gen)); history.h_c_id = c; history.h_c_d_id = d; history.h_c_w_id = w; history.h_d_id = d; history.h_w_id = w; history.h_date = sysdate; history.h_amount = 10; history.h_data = TPCCUtil.randomStr(TPCCUtil.randomNumber(10, 24, gen)); k = k + 2; if (outputFiles == false) { custPrepStmt.setLong(1, customer.c_w_id); custPrepStmt.setLong(2, customer.c_d_id); custPrepStmt.setLong(3, customer.c_id); custPrepStmt.setDouble(4, customer.c_discount); custPrepStmt.setString(5, customer.c_credit); custPrepStmt.setString(6, customer.c_last); custPrepStmt.setString(7, customer.c_first); custPrepStmt.setDouble(8, customer.c_credit_lim); custPrepStmt.setDouble(9, customer.c_balance); custPrepStmt.setDouble(10, customer.c_ytd_payment); custPrepStmt.setLong(11, customer.c_payment_cnt); custPrepStmt.setLong(12, customer.c_delivery_cnt); custPrepStmt.setString(13, customer.c_street_1); custPrepStmt.setString(14, customer.c_street_2); custPrepStmt.setString(15, customer.c_city); custPrepStmt.setString(16, customer.c_state); custPrepStmt.setString(17, customer.c_zip); custPrepStmt.setString(18, customer.c_phone); custPrepStmt.setTimestamp(19, customer.c_since); custPrepStmt.setString(20, customer.c_middle); custPrepStmt.setString(21, customer.c_data); custPrepStmt.addBatch(); histPrepStmt.setInt(1, history.h_c_id); histPrepStmt.setInt(2, history.h_c_d_id); histPrepStmt.setInt(3, history.h_c_w_id); histPrepStmt.setInt(4, history.h_d_id); histPrepStmt.setInt(5, history.h_w_id); histPrepStmt.setTimestamp(6, history.h_date); histPrepStmt.setDouble(7, history.h_amount); histPrepStmt.setString(8, history.h_data); histPrepStmt.addBatch(); if ((k % configCommitCount) == 0) { long tmpTime = new java.util.Date().getTime(); String etStr = " Elasped Time(ms): " + ((tmpTime - lastTimeMS) / 1000.000) + " "; LOG.debug(etStr.substring(0, 30) + " Writing record " + k + " of " + t); lastTimeMS = tmpTime; custPrepStmt.executeBatch(); histPrepStmt.executeBatch(); custPrepStmt.clearBatch(); custPrepStmt.clearBatch(); transCommit(); } } else { String str = ""; str = str + customer.c_id + ","; str = str + customer.c_d_id + ","; str = str + customer.c_w_id + ","; str = str + customer.c_discount + ","; str = str + customer.c_credit + ","; str = str + customer.c_last + ","; str = str + customer.c_first + ","; str = str + customer.c_credit_lim + ","; str = str + customer.c_balance + ","; str = str + customer.c_ytd_payment + ","; str = str + customer.c_payment_cnt + ","; str = str + customer.c_delivery_cnt + ","; str = str + customer.c_street_1 + ","; str = str + customer.c_street_2 + ","; str = str + customer.c_city + ","; str = str + customer.c_state + ","; str = str + customer.c_zip + ","; str = str + customer.c_phone; out.println(str); str = ""; str = str + history.h_c_id + ","; str = str + history.h_c_d_id + ","; str = str + history.h_c_w_id + ","; str = str + history.h_d_id + ","; str = str + history.h_w_id + ","; str = str + history.h_date + ","; str = str + history.h_amount + ","; str = str + history.h_data; outHist.println(str); if ((k % configCommitCount) == 0) { long tmpTime = new java.util.Date().getTime(); String etStr = " Elasped Time(ms): " + ((tmpTime - lastTimeMS) / 1000.000) + " "; LOG.debug(etStr.substring(0, 30) + " Writing record " + k + " of " + t); lastTimeMS = tmpTime; } } } // end for [c] } // end for [d] } // end for [w] long tmpTime = new java.util.Date().getTime(); String etStr = " Elasped Time(ms): " + ((tmpTime - lastTimeMS) / 1000.000) + " "; LOG.debug(etStr.substring(0, 30) + " Writing record " + k + " of " + t); lastTimeMS = tmpTime; custPrepStmt.executeBatch(); histPrepStmt.executeBatch(); custPrepStmt.clearBatch(); histPrepStmt.clearBatch(); transCommit(); now = new java.util.Date(); if (outputFiles == true) { outHist.close(); } LOG.debug("End Cust-Hist Data Load @ " + now); } catch (SQLException se) { LOG.debug(se.getMessage()); transRollback(); if (outputFiles == true) { outHist.close(); } } catch (Exception e) { e.printStackTrace(); transRollback(); if (outputFiles == true) { outHist.close(); } } return (k); }
From source file:edu.ku.brc.specify.conversion.SpecifyDBConverter.java
/** * @param oldDBConn/*w ww . j av a2s. com*/ * @param newDBConn */ protected void fixLoanPreps(final Connection oldDBConn, final Connection newDBConn) { // Category == 0 -> Is a Loan, 1 is a Gift System.out.println("------------------------ Loans ----------------------------"); int fixCnt = 0; int totalCnt = 0; int skippedCnt = 0; int notFndCnt = 0; int noMatch = 0; IdMapperMgr.getInstance().setDBs(oldDBConn, newDBConn); IdTableMapper loanPrepsMapper = IdMapperMgr.getInstance().addTableMapper("loanphysicalobject", "LoanPhysicalObjectID", false); IdTableMapper loansMapper = IdMapperMgr.getInstance().addTableMapper("loan", "LoanID", false); IdTableMapper prepMapper = IdMapperMgr.getInstance().addTableMapper("collectionobject", "CollectionObjectID", false); Statement stmt = null; PreparedStatement newStmt = null; PreparedStatement pStmt = null; try { pStmt = newDBConn.prepareStatement( "UPDATE loanpreparation SET Quantity=?, QuantityResolved=?, QuantityReturned=?, IsResolved=?, TimestampModified=?, TimestampCreated=?, " + "LoanID=?, DescriptionOfMaterial=?, OutComments=?, InComments=?, PreparationID=?, Version=? " + "WHERE LoanPreparationID = ?"); newStmt = newDBConn.prepareStatement( "SELECT LoanPreparationID, TimestampModified, Version FROM loanpreparation WHERE LoanPreparationID = ?"); String sql = "SELECT lp.LoanPhysicalObjectID, lp.PhysicalObjectID, lp.LoanID, lp.Quantity, lp.DescriptionOfMaterial, lp.OutComments, lp.InComments, " + "lp.QuantityResolved, lp.QuantityReturned, lp.TimestampCreated, lp.TimestampModified, lp.LastEditedBy, l.Closed " + "FROM loanphysicalobject lp INNER JOIN loan l ON l.LoanID = lp.LoanID WHERE l.Category = 0"; stmt = oldDBConn.createStatement(); ResultSet rs = stmt.executeQuery(sql); while (rs.next()) { int id = rs.getInt(1); Timestamp oldCreatedTS = rs.getTimestamp(10); //System.out.println(id); Integer newID = loanPrepsMapper.get(id); if (newID != null) { newStmt.setInt(1, newID); ResultSet rs2 = newStmt.executeQuery(); if (rs2.next()) { Timestamp oldModifiedTS = rs.getTimestamp(11); if (rs2.getInt(3) == 0) // version { Integer prepId = rs.getInt(2); Integer loanId = rs.getInt(3); Integer newLoanId = loansMapper.get(loanId); Integer qty = rs.getInt(4); String descOfMat = rs.getString(5); String outComments = rs.getString(6); String inComments = rs.getString(7); Integer qtyRes = rs.getInt(8); Integer qtyRet = rs.getInt(9); String lasteditedBy = rs.getString(12); Boolean isLoanClosed = rs.getBoolean(13); isLoanClosed = isLoanClosed == null ? false : isLoanClosed; pStmt.setInt(1, qty); pStmt.setInt(2, qtyRes); pStmt.setInt(3, qtyRet); boolean isResolved = isLoanClosed; if (!isLoanClosed) // if Loan is Closed then all are resolved by definition { if (qty != null) { if (qtyRes != null && qty.equals(qtyRes)) { isResolved = true; } else if (qtyRet != null && qty.equals(qtyRet)) { isResolved = true; } } } pStmt.setBoolean(4, isResolved); pStmt.setTimestamp(5, oldModifiedTS); pStmt.setTimestamp(6, oldCreatedTS); pStmt.setInt(7, newLoanId); pStmt.setString(8, descOfMat); pStmt.setString(9, outComments); pStmt.setString(10, inComments); pStmt.setInt(11, prepId != null ? prepMapper.get(prepId) : null); pStmt.setInt(12, 1); // Version pStmt.setInt(13, newID); if (pStmt.executeUpdate() != 1) { log.error(String.format("*** Error updating OldID %d newID %d", rs.getInt(1), newID)); } else { fixCnt++; } } else { noMatch++; } } else { notFndCnt++; } rs2.close(); } else { //log.error(String.format("*** Error not new Id for OldID %d", rs.getInt(1))); skippedCnt++; } totalCnt++; } rs.close(); } catch (Exception ex) { ex.printStackTrace(); } finally { try { if (stmt != null) stmt.close(); if (newStmt != null) newStmt.close(); if (pStmt != null) pStmt.close(); } catch (Exception ex) { } } System.out.println(String.format("Total: %d Fixed: %d Skipped: %d NotFnd: %d noMatch: %d", totalCnt, fixCnt, skippedCnt, notFndCnt, noMatch)); }
From source file:com.gtwm.pb.model.manageData.DataManagement.java
public int importCSV(HttpServletRequest request, TableInfo table, boolean updateExistingRecords, BaseField recordIdentifierField, boolean generateRowIds, char separator, char quotechar, int numHeaderLines, boolean useRelationDisplayValues, boolean importSequenceValues, boolean requireExactRelationMatches, boolean trim, boolean merge, List<FileItem> multipartItems, String csvContent) throws SQLException, InputRecordException, IOException, CantDoThatException, ObjectNotFoundException, DisallowedException, CodingErrorException { if (!FileUpload.isMultipartContent(new ServletRequestContext(request))) { if (csvContent == null) { throw new CantDoThatException( "To import CSV content, a file must be uploaded (form posted as multi-part) or csv_content specified"); }/*from w ww. ja va2 s.c o m*/ } int numImportedRecords = 0; // get field set to import into. LinkedHashSet to ensure order is // retained so the right values are imported into the right fields LinkedHashSet<BaseField> fields = new LinkedHashSet<BaseField>(table.getFields()); // if row IDs aren't included in the data to import, remove ID from the // field set BaseField primaryKey = table.getPrimaryKey(); if (recordIdentifierField == null) { recordIdentifierField = primaryKey; } if (generateRowIds || (updateExistingRecords && !recordIdentifierField.equals(primaryKey))) { fields.remove(primaryKey); } Map<RelationField, Map<String, String>> relationLookups = new HashMap<RelationField, Map<String, String>>(); // Remove fields which shouldn't be modified during the import // For serial fields, if we need to set serial values explicitly, this // will have to be dealt with later for (BaseField field : table.getFields()) { if (field instanceof SequenceField && (!field.equals(primaryKey)) && (!importSequenceValues)) { fields.remove(field); } else if (field.getHidden()) { if (field.getFieldName().equals(HiddenFields.VIEW_COUNT.getFieldName()) || field.getFieldName().equals(HiddenFields.COMMENTS_FEED.getFieldName())) { fields.remove(field); } else if (updateExistingRecords) { if (field.getFieldName().equals(HiddenFields.DATE_CREATED.getFieldName()) || field.getFieldName().equals(HiddenFields.CREATED_BY.getFieldName())) { fields.remove(field); } } } else if (!field.getFieldCategory().savesData()) { fields.remove(field); } // Also, if importing relations by display value, look up // display/internal value mappings if (useRelationDisplayValues && field instanceof RelationField) { Map<String, String> displayToInternalValue = ((RelationFieldDefn) field).getItems(true, false); relationLookups.put((RelationField) field, displayToInternalValue); } } // Prepare SQL String insertSQLCode = null; String updateSQLCode = null; String logCreationSQLCode = null; // If updating, we'll need a record ID value. Depending on what the // identifier field is, this could be one of a couple of different types String recordIdentifierString = null; Integer recordIdentifierInteger = null; int recordIdentifierFieldNum = 0; DatabaseFieldType identifierFieldDbType = null; if (updateExistingRecords) { identifierFieldDbType = recordIdentifierField.getDbType(); if (!identifierFieldDbType.equals(DatabaseFieldType.VARCHAR) && !identifierFieldDbType.equals(DatabaseFieldType.INTEGER) && !identifierFieldDbType.equals(DatabaseFieldType.SERIAL)) { throw new CantDoThatException("The record identifier field has to be text or a whole number, " + recordIdentifierField + " is a " + identifierFieldDbType); } updateSQLCode = "UPDATE " + table.getInternalTableName() + " SET "; int fieldNum = 0; for (BaseField field : fields) { fieldNum += 1; if (merge) { // Update database only if there's a non-null value from the // spreadsheet updateSQLCode += field.getInternalFieldName() + " = COALESCE(?," + field.getInternalFieldName() + "), "; } else { updateSQLCode += field.getInternalFieldName() + " = ?, "; } if (field.equals(recordIdentifierField)) { recordIdentifierFieldNum = fieldNum; } } if (recordIdentifierFieldNum == 0) { throw new CantDoThatException("Can't find the field specified as record identifier (" + recordIdentifierField + ") in the list of table fields " + fields + " in table " + table); } updateSQLCode = updateSQLCode.substring(0, updateSQLCode.length() - 2); updateSQLCode += " WHERE " + recordIdentifierField.getInternalFieldName() + "=?"; logCreationSQLCode = "UPDATE " + table.getInternalTableName() + " SET " + table.getField(HiddenFields.DATE_CREATED.getFieldName()).getInternalFieldName() + "=?, " + table.getField(HiddenFields.CREATED_BY.getFieldName()).getInternalFieldName() + "=? WHERE " + primaryKey.getInternalFieldName() + "=?"; } insertSQLCode = "INSERT INTO " + table.getInternalTableName() + "("; String placeholders = ""; for (BaseField field : fields) { insertSQLCode += field.getInternalFieldName() + ", "; placeholders += "?, "; } placeholders = placeholders.substring(0, placeholders.length() - 2); insertSQLCode = insertSQLCode.substring(0, insertSQLCode.length() - 2) + ") VALUES (" + placeholders + ")"; // Find content to import Reader inputStreamReader = null; if (csvContent != null) { inputStreamReader = new StringReader(csvContent); } else { for (FileItem item : multipartItems) { // if item is a file if (!item.isFormField()) { if (item.getName().toLowerCase().endsWith(".xls")) { throw new CantDoThatException( "You need to upload as a CSV to import, Excel files can't be imported directly"); } inputStreamReader = new InputStreamReader(item.getInputStream()); break; } } } if (inputStreamReader == null) { throw new CantDoThatException("No file uploaded"); } CSVReader csvReader = new CSVReader(inputStreamReader, separator, quotechar, numHeaderLines); // returns a list of String arrays List<String[]> csvLines = (List<String[]>) csvReader.readAll(); // do db inserts Connection conn = null; PreparedStatement statement = null; // backupInsertStatement is for when an update returns 0 rows affected, // i.e. there's no matching row. In this case, do an insert PreparedStatement backupInsertStatement = null; PreparedStatement logCreationStatement = null; // These two variables used in exception handling int importLine = 0; BaseField fieldImported = null; Timestamp importTime = new Timestamp(System.currentTimeMillis()); AppUserInfo loggedInUser = authManager.getUserByUserName(request, request.getRemoteUser()); String fullname = loggedInUser.getForename() + " " + loggedInUser.getSurname() + " (" + loggedInUser.getUserName() + ")"; try { conn = this.dataSource.getConnection(); conn.setAutoCommit(false); if (updateExistingRecords) { statement = conn.prepareStatement(updateSQLCode); backupInsertStatement = conn.prepareStatement(insertSQLCode); logCreationStatement = conn.prepareStatement(logCreationSQLCode); } else { statement = conn.prepareStatement(insertSQLCode); } CSVLINE: for (String[] csvLineArray : csvLines) { // convert to an object rather than a primitive array - // easier to work with List<String> lineValues = Arrays.asList(csvLineArray); importLine++; // skip blank lines if (lineValues.size() == 1) { if (lineValues.get(0).length() == 0) { continue CSVLINE; } } int fieldNum = 0; for (BaseField field : fields) { fieldImported = field; fieldNum++; if (field.getHidden()) { String fieldName = field.getFieldName(); if (fieldName.equals(HiddenFields.LOCKED.getFieldName())) { statement.setBoolean(fieldNum, false); if (updateExistingRecords) { backupInsertStatement.setBoolean(fieldNum, false); } } else if (fieldName.equals(HiddenFields.DATE_CREATED.getFieldName()) || fieldName.equals(HiddenFields.LAST_MODIFIED.getFieldName())) { statement.setTimestamp(fieldNum, importTime); if (updateExistingRecords) { backupInsertStatement.setTimestamp(fieldNum, importTime); } } else if (fieldName.equals(HiddenFields.CREATED_BY.getFieldName()) || fieldName.equals(HiddenFields.MODIFIED_BY.getFieldName())) { statement.setString(fieldNum, fullname); if (updateExistingRecords) { backupInsertStatement.setString(fieldNum, fullname); } } } else if (fieldNum > lineValues.size()) { // booleans have a not null constraint if (field.getDbType().equals(Types.BOOLEAN)) { statement.setBoolean(fieldNum, false); if (updateExistingRecords) { backupInsertStatement.setBoolean(fieldNum, false); } } else { statement.setNull(fieldNum, Types.NULL); if (updateExistingRecords) { backupInsertStatement.setNull(fieldNum, Types.NULL); } } } else { String lineValue = lineValues.get(fieldNum - 1); if (lineValue != null) { if (trim) { lineValue = lineValue.trim(); } if (lineValue.equals("")) { // booleans have a not null constraint if (field.getDbType().equals(Types.BOOLEAN)) { statement.setBoolean(fieldNum, false); if (updateExistingRecords) { backupInsertStatement.setBoolean(fieldNum, false); } } else { statement.setNull(fieldNum, Types.NULL); if (updateExistingRecords) { backupInsertStatement.setNull(fieldNum, Types.NULL); } } } else { if ((field instanceof FileField) && (generateRowIds)) { throw new CantDoThatException( "Cannot generate row ids when importing file names. See line " + importLine + ", field '" + field.getFieldName() + "' with value '" + lineValue + "'"); } switch (field.getDbType()) { case VARCHAR: statement.setString(fieldNum, lineValue); if (updateExistingRecords) { backupInsertStatement.setString(fieldNum, lineValue); if (field.equals(recordIdentifierField)) { recordIdentifierString = lineValue; } } break; case TIMESTAMP: // deal with month and year // resolution dates exported if (lineValue.matches("^[a-zA-Z]{3}\\s\\d{2,4}$")) { lineValue = "01 " + lineValue; } else if (lineValue.matches("^\\d{2,4}")) { lineValue = "01 Jan " + lineValue; } try { Calendar calValue = CalendarParser.parse(lineValue, CalendarParser.DD_MM_YY); statement.setTimestamp(fieldNum, new Timestamp(calValue.getTimeInMillis())); if (updateExistingRecords) { backupInsertStatement.setTimestamp(fieldNum, new Timestamp(calValue.getTimeInMillis())); } } catch (CalendarParserException cpex) { throw new InputRecordException("Error importing line " + importLine + ", field " + field + ": " + cpex.getMessage(), field, cpex); } break; case FLOAT: lineValue = lineValue.trim().replaceAll("[^\\d\\.\\+\\-eE]", ""); statement.setDouble(fieldNum, Double.valueOf(lineValue)); if (updateExistingRecords) { backupInsertStatement.setDouble(fieldNum, Double.valueOf(lineValue)); } break; case INTEGER: if ((field instanceof RelationField) && (useRelationDisplayValues)) { // find key value for display value RelationField relationField = (RelationField) field; Map<String, String> valueKeyMap = relationLookups.get(relationField); String internalValueString = valueKeyMap.get(lineValue); if (internalValueString == null) { if (!requireExactRelationMatches) { // A very basic fuzzy matching // algorithm String potentialDisplayValue = null; String lineValueLowerCase = lineValue.toLowerCase(); FUZZYMATCH: for (Map.Entry<String, String> entry : valueKeyMap .entrySet()) { potentialDisplayValue = entry.getKey(); if (potentialDisplayValue.toLowerCase() .contains(lineValueLowerCase)) { internalValueString = entry.getValue(); break FUZZYMATCH; } } } if (internalValueString == null) { throw new CantDoThatException("Error importing line " + importLine + ", field " + relationField + ": Can't find a related '" + relationField.getRelatedTable() + "' for " + relationField.getDisplayField() + " '" + lineValue + "'. "); } } int keyValue = Integer.valueOf(internalValueString); statement.setInt(fieldNum, keyValue); if (updateExistingRecords) { backupInsertStatement.setInt(fieldNum, keyValue); if (field.equals(recordIdentifierField)) { recordIdentifierInteger = keyValue; } } } else { lineValue = lineValue.trim().replaceAll("[^\\d\\.\\+\\-eE]", ""); int keyValue = Integer.valueOf(lineValue); statement.setInt(fieldNum, keyValue); if (updateExistingRecords) { backupInsertStatement.setInt(fieldNum, keyValue); if (field.equals(recordIdentifierField)) { recordIdentifierInteger = keyValue; } } } break; case SERIAL: lineValue = lineValue.trim().replaceAll("[^\\d\\.\\+\\-eE]", ""); int keyValue = Integer.valueOf(lineValue); statement.setInt(fieldNum, keyValue); if (updateExistingRecords) { backupInsertStatement.setInt(fieldNum, keyValue); if (field.equals(recordIdentifierField)) { recordIdentifierInteger = keyValue; } } break; case BOOLEAN: boolean filterValueIsTrue = Helpers.valueRepresentsBooleanTrue(lineValue); statement.setBoolean(fieldNum, filterValueIsTrue); if (updateExistingRecords) { backupInsertStatement.setBoolean(fieldNum, filterValueIsTrue); } break; } } } else { // booleans have a not null constraint if (field.getDbType().equals(Types.BOOLEAN)) { statement.setBoolean(fieldNum, false); if (updateExistingRecords) { backupInsertStatement.setBoolean(fieldNum, false); } } else { statement.setNull(fieldNum, Types.NULL); if (updateExistingRecords) { backupInsertStatement.setNull(fieldNum, Types.NULL); } } } } } if (updateExistingRecords) { // for potential error messages String recordIdentifierDescription = null; if (identifierFieldDbType.equals(DatabaseFieldType.INTEGER) || identifierFieldDbType.equals(DatabaseFieldType.SERIAL)) { if (recordIdentifierInteger == null) { throw new InputRecordException( "Can't find a record identifier value at line " + importLine, recordIdentifierField); } recordIdentifierDescription = recordIdentifierField.getFieldName() + " = " + recordIdentifierInteger; // Set the 'WHERE recordIdentifier = ?' clause statement.setInt(fields.size() + 1, recordIdentifierInteger); } else { if (recordIdentifierString == null) { throw new InputRecordException( "Can't find a record identifier value at line " + importLine, recordIdentifierField); } recordIdentifierDescription = recordIdentifierField.getFieldName() + " = '" + recordIdentifierString + "'"; // Set the 'WHERE recordIdentifier = ?' clause statement.setString(fields.size() + 1, recordIdentifierString); } int rowsAffected = statement.executeUpdate(); if (rowsAffected == 0) { // If can't find a match to update, insert a record // instead backupInsertStatement.executeUpdate(); // NB Postgres specific code to find Row ID of newly // inserted record, not cross-db compatible String newRowIdSQLCode = "SELECT currval('" + table.getInternalTableName() + "_" + primaryKey.getInternalFieldName() + "_seq')"; PreparedStatement newRowIdStatement = conn.prepareStatement(newRowIdSQLCode); ResultSet newRowIdResults = newRowIdStatement.executeQuery(); if (newRowIdResults.next()) { int newRowId = newRowIdResults.getInt(1); // Add creation metadata to the new row logCreationStatement.setTimestamp(1, importTime); logCreationStatement.setString(2, fullname); logCreationStatement.setInt(3, newRowId); int creationLogRowsAffected = logCreationStatement.executeUpdate(); if (creationLogRowsAffected == 0) { throw new SQLException( "Unable to update creation metadata of newly inserted record, using query " + logCreationStatement); } } else { newRowIdResults.close(); newRowIdStatement.close(); throw new SQLException("Row ID not found for the newly inserted record. '" + newRowIdStatement + "' didn't work"); } newRowIdResults.close(); newRowIdStatement.close(); } else if (rowsAffected > 1) { throw new InputRecordException("Error importing line " + importLine + ". The record identifier field " + recordIdentifierDescription + " should match only 1 record in the database but it actually matches " + rowsAffected, recordIdentifierField); } // reset to null for the next line recordIdentifierString = null; recordIdentifierInteger = null; } else { statement.executeUpdate(); } numImportedRecords += 1; } statement.close(); if (backupInsertStatement != null) { backupInsertStatement.close(); } if (logCreationStatement != null) { logCreationStatement.close(); } // reset the primary key ID sequence so new records can be added resetSequence((SequenceField) primaryKey, conn); // and any other sequence fields if (importSequenceValues) { for (BaseField field : table.getFields()) { if ((!field.equals(primaryKey)) && field instanceof SequenceField) { resetSequence((SequenceField) field, conn); } } } // ANALYZE the table after import if (numImportedRecords > 1000) { Statement analyzeStatement = conn.createStatement(); analyzeStatement.execute("ANALYZE " + table.getInternalTableName()); analyzeStatement.close(); } conn.commit(); } catch (SQLException sqlex) { String databaseErrorMessage = Helpers.replaceInternalNames(sqlex.getMessage(), table.getDefaultReport()); logger.warn("Import failed, statement is " + statement); logger.warn("Backup insert statement is " + backupInsertStatement); String errorMessage = "Error importing CSV line " + importLine; if (!fieldImported.getHidden()) { errorMessage += ", field '" + fieldImported + "'"; } errorMessage += ": " + databaseErrorMessage; throw new InputRecordException(errorMessage, fieldImported, sqlex); } catch (NumberFormatException nfex) { String causeMessage = nfex.getMessage(); causeMessage = causeMessage.replaceAll("For input string", "value"); String errorMessage = "Error parsing number when importing CSV line " + importLine; if (!fieldImported.getHidden()) { errorMessage += ", field '" + fieldImported + "'"; } errorMessage += ": " + causeMessage; throw new InputRecordException(errorMessage, fieldImported, nfex); } finally { if (conn != null) { conn.close(); } } this.logLastDataChangeTime(request); logLastTableDataChangeTime(table); UsageLogger usageLogger = new UsageLogger(this.dataSource); String logMessage = "" + numImportedRecords; if (updateExistingRecords) { logMessage += " records imported"; } else { logMessage += " new records imported"; } if (csvContent != null) { logMessage += " from file"; } usageLogger.logDataChange(loggedInUser, table, null, AppAction.CSV_IMPORT, -1, logMessage); UsageLogger.startLoggingThread(usageLogger); return numImportedRecords; }
From source file:i5.las2peer.services.mobsos.SurveyService.java
/** * TODO: write documentation/*from w w w. ja v a 2 s . co m*/ * Updates a survey with a given id. The respective survey may only be deleted, if the active agent is the survey's owner. * * @param id * @return */ @PUT @Consumes(MediaType.APPLICATION_JSON) @Path("surveys/{id}") @Summary("update given survey.") @Notes("Requires authentication. Use parent resource to retrieve list of existing surveys.") @ApiResponses(value = { @ApiResponse(code = 200, message = "Survey updated successfully."), @ApiResponse(code = 400, message = "Survey data invalid."), @ApiResponse(code = 401, message = "Survey may only be updated by its owner."), @ApiResponse(code = 404, message = "Survey does not exist.") }) public HttpResponse updateSurvey(@PathParam("id") int id, @ContentParam String content) { if (getActiveAgent().getId() == getActiveNode().getAnonymous().getId()) { HttpResponse noauth = new HttpResponse("Please authenticate to update survey!"); noauth.setStatus(401); } String onAction = "updating survey " + id; try { Connection c = null; PreparedStatement s = null; ResultSet rs = null; // +++ dsi try { int exown; // survey may only be updated if survey exists and active agent is owner exown = checkExistenceOwnership(id, 0); // check if survey exists; if not, return 404. if (exown == -1) { HttpResponse result = new HttpResponse("Survey " + id + " does not exist."); result.setStatus(404); return result; } // if survey exists, check if active agent is owner. if not, return 401. else if (exown == 0) { HttpResponse result = new HttpResponse("Survey " + id + " may only be deleted by its owner."); result.setStatus(401); return result; } // if survey exists and active agent is owner, proceed. JSONObject o; // parse and validate content. If invalid, return 400 (bad request) try { o = parseSurvey(content); } catch (IllegalArgumentException e) { HttpResponse result = new HttpResponse("Invalid survey data! " + e.getMessage()); result.setStatus(400); return result; } c = dataSource.getConnection(); s = c.prepareStatement("update " + jdbcSchema + ".survey set organization=?, logo=?, name=?, description=?, resource=?, start=?, end=?, lang=? where id = ?"); s.setString(1, (String) o.get("organization")); s.setString(2, (String) o.get("logo")); s.setString(3, (String) o.get("name")); s.setString(4, (String) o.get("description")); s.setString(5, (String) o.get("resource")); s.setTimestamp(6, new Timestamp(DatatypeConverter.parseDateTime((String) o.get("start")).getTimeInMillis())); s.setTimestamp(7, new Timestamp(DatatypeConverter.parseDateTime((String) o.get("end")).getTimeInMillis())); s.setString(8, (String) o.get("lang")); s.setInt(9, id); s.executeUpdate(); HttpResponse result = new HttpResponse("Survey " + id + " updated successfully."); result.setStatus(200); return result; } catch (Exception e) { e.printStackTrace(); return internalError(onAction); } finally { try { if (rs != null) rs.close(); } catch (Exception e) { e.printStackTrace(); return internalError(onAction); } try { if (s != null) s.close(); } catch (Exception e) { e.printStackTrace(); return internalError(onAction); } try { if (c != null) c.close(); } catch (Exception e) { e.printStackTrace(); return internalError(onAction); } } // --- dsi } catch (Exception e) { e.printStackTrace(); return internalError(onAction); } }
From source file:com.funambol.foundation.items.dao.DataBaseFileDataObjectMetadataDAO.java
/** * Updates file data object metadata. <code>content</code> is not used. * @param fdow/* w ww. ja va 2s. c om*/ * @throws com.funambol.foundation.exception.DAOException */ public void updateItem(FileDataObjectWrapper fdow) throws DAOException { Connection con = null; PreparedStatement ps = null; ResultSet rs = null; try { FileDataObject fdo = fdow.getFileDataObject(); Long fdoId = Long.valueOf(fdow.getId()); Timestamp currentTime = new Timestamp(System.currentTimeMillis()); StringBuilder updateQuery = new StringBuilder(); updateQuery.append(SQL_UPDATE_FNBL_FILE_DATA_OBJECT_BEGIN); updateQuery.append(SQL_FIELD_LAST_UPDATE).append(SQL_EQUALS_QUESTIONMARK_COMMA); updateQuery.append(SQL_FIELD_STATUS).append(SQL_EQUALS_QUESTIONMARK_COMMA); updateQuery.append(SQL_FIELD_UPLOAD_STATUS).append(SQL_EQUALS_QUESTIONMARK_COMMA); String localName = fdow.getLocalName(); if (localName != null) { updateQuery.append(SQL_FIELD_LOCAL_NAME).append(SQL_EQUALS_QUESTIONMARK_COMMA); } else { // if the item was deleted and readded again with a sync // considering only the metadata, the local name must be set to // null, since the previous file was already deleted and the new // file would be uploaded later. updateQuery.append(SQL_FIELD_LOCAL_NAME).append(SQL_EQUALS_NULL_COMMA); } Long crc = Long.valueOf(fdow.getFileDataObject().getCrc()); updateQuery.append(SQL_FIELD_CRC).append(SQL_EQUALS_QUESTIONMARK_COMMA); String trueName = fdo.getName(); if (trueName != null) { updateQuery.append(SQL_FIELD_TRUE_NAME).append(SQL_EQUALS_QUESTIONMARK_COMMA); } MediaUtils.setFDODates(fdo, fdo.getCreated(), fdo.getModified()); Timestamp created = timestamp(fdo.getCreated()); if (created == null) { created = currentTime; } Timestamp modified = timestamp(fdo.getModified()); if (modified == null) { modified = currentTime; } updateQuery.append(SQL_FIELD_CREATED).append(SQL_EQUALS_QUESTIONMARK_COMMA); updateQuery.append(SQL_FIELD_MODIFIED).append(SQL_EQUALS_QUESTIONMARK_COMMA); Timestamp accessed = timestamp(fdo.getAccessed()); if (accessed != null) { updateQuery.append(SQL_FIELD_ACCESSED).append(SQL_EQUALS_QUESTIONMARK_COMMA); } Boolean hidden = fdo.getHidden(); if (hidden != null) { updateQuery.append(SQL_FIELD_H).append(SQL_EQUALS_QUESTIONMARK_COMMA); } Boolean system = fdo.getSystem(); if (system != null) { updateQuery.append(SQL_FIELD_S).append(SQL_EQUALS_QUESTIONMARK_COMMA); } Boolean archived = fdo.getArchived(); if (archived != null) { updateQuery.append(SQL_FIELD_A).append(SQL_EQUALS_QUESTIONMARK_COMMA); } Boolean deleted = fdo.getDeleted(); if (deleted != null) { updateQuery.append(SQL_FIELD_D).append(SQL_EQUALS_QUESTIONMARK_COMMA); } Boolean writable = fdo.getWritable(); if (writable != null) { updateQuery.append(SQL_FIELD_W).append(SQL_EQUALS_QUESTIONMARK_COMMA); } Boolean readable = fdo.getReadable(); if (readable != null) { updateQuery.append(SQL_FIELD_R).append(SQL_EQUALS_QUESTIONMARK_COMMA); } Boolean executable = fdo.getExecutable(); if (executable != null) { updateQuery.append(SQL_FIELD_X).append(SQL_EQUALS_QUESTIONMARK_COMMA); } String contentType = fdo.getContentType(); if (contentType != null) { updateQuery.append(SQL_FIELD_CTTYPE).append(SQL_EQUALS_QUESTIONMARK_COMMA); } Long size = fdo.getSize(); if (size != null) { updateQuery.append(SQL_FIELD_SIZE).append(SQL_EQUALS_QUESTIONMARK_COMMA); } Long sizeOnStorage = fdow.getSizeOnStorage(); if (sizeOnStorage != null) { updateQuery.append(SQL_FIELD_SIZE_ON_STORAGE).append(SQL_EQUALS_QUESTIONMARK_COMMA); } if (updateQuery.charAt(updateQuery.length() - 2) == ',') { updateQuery.deleteCharAt(updateQuery.length() - 2); } updateQuery.append(SQL_UPDATE_FNBL_FILE_DATA_OBJECT_END); // Looks up the data source when the first connection is created con = getUserDataSource().getRoutedConnection(userId); ps = con.prepareStatement(updateQuery.toString()); int k = 1; Timestamp lastUpdate = (fdow.getLastUpdate() == null) ? currentTime : fdow.getLastUpdate(); ps.setLong(k++, lastUpdate.getTime()); ps.setString(k++, String.valueOf(Def.PIM_STATE_UPDATED)); ps.setString(k++, String.valueOf(fdo.getUploadStatus())); if (localName != null) { ps.setString(k++, StringUtils.left(localName, SQL_LOCAL_NAME_DIM)); } ps.setLong(k++, crc); if (trueName != null) { ps.setString(k++, StringUtils.left(trueName, SQL_TRUE_NAME_DIM)); } // cannot be null ps.setTimestamp(k++, created); ps.setTimestamp(k++, modified); if (accessed != null) { ps.setTimestamp(k++, accessed); } if (hidden != null) { ps.setString(k++, hidden ? ONE : NIL); } if (system != null) { ps.setString(k++, system ? ONE : NIL); } if (archived != null) { ps.setString(k++, archived ? ONE : NIL); } if (deleted != null) { ps.setString(k++, deleted ? ONE : NIL); } if (writable != null) { ps.setString(k++, writable ? ONE : NIL); } if (readable != null) { ps.setString(k++, readable ? ONE : NIL); } if (executable != null) { ps.setString(k++, executable ? ONE : NIL); } if (contentType != null) { ps.setString(k++, StringUtils.left(contentType, SQL_CTTYPE_DIM)); } if (size != null) { ps.setLong(k++, size); } if (sizeOnStorage != null) { ps.setLong(k++, sizeOnStorage); } ps.setLong(k++, fdoId); ps.setString(k++, userId); ps.setString(k++, sourceURI); ps.executeUpdate(); // delete and add the properties associated to the new FDO removeAllProperties(fdow.getId()); addProperties(fdow); } catch (Exception e) { throw new DAOException("Error updating file data object.", e); } finally { DBTools.close(con, ps, rs); } }
From source file:edu.ku.brc.specify.toycode.mexconabio.BuildFromRecovery.java
/** * /*from w w w .ja v a 2 s . c om*/ */ public void process() throws SQLException { buildTags = new BuildTags(); buildTags.setDbConn(dbConn); buildTags.setDbConn2(dbConn); buildTags.initialPrepareStatements(); BasicSQLUtils.setDBConnection(dbConn); IdMapperMgr idMapperMgr = IdMapperMgr.getInstance(); idMapperMgr.setDBs(srcDBConn2, dbConn); geoStmt1 = dbConn.prepareStatement( "SELECT GeographyID FROM geography WHERE RankID = ? AND ParentID = ? AND LOWER(Abbrev) = ?"); geoStmt2 = dbConn .prepareStatement("SELECT GeographyID FROM geography WHERE RankID = ? AND LOWER(Abbrev) = ?"); agentStmt = dbConn .prepareStatement("SELECT AgentID FROM agent WHERE LOWER(FirstName) = ? AND LOWER(LastName) = ?"); tagStmt = dbConn.prepareStatement( "SELECT CollectionObjectID FROM collectionobject WHERE CollectionID = 4 AND LOWER(FieldNumber) = ?"); BasicSQLUtils.update(srcDBConn, "UPDATE recovery SET r_date = null WHERE r_date = '0000-00-00'"); boolean doTags = true; if (doTags) { int divId = 2; int dspId = 3; int colId = 32768; String sql = "SELECT tagid, " + "r_city, r_state, r_zip, r_country, r_date, r_lat, r_long, " + "reporter_first, reporter_last, reporter_city, reporter_state, reporter_country, reporter_zip, " + "dir, dist, gender, " + "t_first, t_middle, t_last, t_city, t_state, t_country, t_postalcode, t_org, t_lat, t_long, t_date FROM recovery ORDER BY recovid ASC"; Statement stmt = srcDBConn.createStatement(); stmt.setFetchSize(Integer.MIN_VALUE); log.debug("Querying for Tags..."); ResultSet rs = stmt.executeQuery(sql); int cnt = 0; log.debug("Done querying for Tags..."); Calendar cal = Calendar.getInstance(); Timestamp ts = new Timestamp(cal.getTime().getTime()); String common = "TimestampCreated, Version, CreatedByAgentID"; String coStr = String.format( "INSERT INTO collectionobject (CatalogNumber, FieldNumber, Text1, Text2, Remarks, CollectionID, CollectionMemberId, CollectingEventID, %s) VALUES(?,?,?,?,?,?,?,?,?,?,?)", common); String ceStr = String.format( "INSERT INTO collectingevent (StartDate, DisciplineID, LocalityID, %s) VALUES(?,?,?,?,?,?)", common); String lcStr = String.format( "INSERT INTO locality (Latitude1, Longitude1, SrcLatLongUnit, Lat1text, Long1text, LatLongType, DisciplineID, LocalityName, GeographyID, %s) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)", common); String clStr = String.format( "INSERT INTO collector (OrderNumber, IsPrimary, CollectingEventID, DivisionID, AgentID, %s) VALUES(?,?,?,?,?,?,?,?)", common); String rlStr = String.format( "INSERT INTO collectionrelationship (collectionRelTypeID, LeftSideCollectionID, RightSideCollectionID, %s) VALUES(?,?,?,?,?,?)", common); String agStr = String .format("INSERT INTO agent (AgentType, FirstName, LastName, %s) VALUES(?,?,?,?,?,?)", common); String adStr = String.format( "INSERT INTO address (City, State, PostalCode, Country, AgentID, %s) VALUES(?,?,?,?, ?,?,?,?)", common); String lcUpdateStr = "UPDATE locality SET Latitude1=?, Longitude1=?, SrcLatLongUnit=?, Lat1text=?, Long1text=?, LatLongType=? WHERE LocalityID = ?"; String lcStr2 = "SELECT LocalityID FROM locality WHERE LocalityName LIKE ? AND LocalityName LIKE ?"; PreparedStatement coStmt = dbConn.prepareStatement(coStr); PreparedStatement ceStmt = dbConn.prepareStatement(ceStr); PreparedStatement lcStmt = dbConn.prepareStatement(lcStr); PreparedStatement clStmt = dbConn.prepareStatement(clStr); PreparedStatement rlStmt = dbConn.prepareStatement(rlStr); PreparedStatement agStmt = dbConn.prepareStatement(agStr); PreparedStatement adStmt = dbConn.prepareStatement(adStr); PreparedStatement lcUpStmt = dbConn.prepareStatement(lcUpdateStr); PreparedStatement lcStmt2 = dbConn.prepareStatement(lcStr2); int recNum = 1; while (rs.next()) { String tag = rs.getString(1); String city = rs.getString(2); String state = rs.getString(3); String zip = rs.getString(4); String country = rs.getString(5); Date date = rs.getDate(6); double lat = rs.getDouble(7); boolean isLatNull = rs.wasNull(); double lon = rs.getDouble(8); boolean isLonNull = rs.wasNull(); String dir = rs.getString(9); String dist = rs.getString(10); String gender = rs.getString(11); String rep_first = rs.getString(12); String rep_last = rs.getString(13); String rep_city = rs.getString(14); String rep_state = rs.getString(15); String rep_country = rs.getString(16); String rep_zip = rs.getString(17); String t_first = rs.getString(18); //String t_middle = rs.getString(19); String t_last = rs.getString(20); String t_city = rs.getString(21); String t_state = rs.getString(22); String t_country = rs.getString(23); String t_zip = rs.getString(24); //String t_org = rs.getString(25); double t_lat = rs.getDouble(26); boolean isTLatNull = rs.wasNull(); double t_lon = rs.getDouble(27); boolean isTLonNull = rs.wasNull(); //String oldState = state; city = condense(rep_city, t_city, city); state = condense(rep_state, state, t_state); country = condense(rep_country, country, t_country); zip = condense(rep_zip, zip, t_zip); rep_first = condense(rep_first, t_first); rep_last = condense(rep_last, t_last); /*boolean debug = ((rep_state != null && rep_state.equals("IA")) || (t_state != null && t_state.equals("IA")) || (oldState != null && oldState.equals("IA"))); if (debug && (state == null || !state.equals("IA"))) { System.out.println("ouch"); }*/ if (rep_first != null && rep_first.length() > 50) { rep_first = rep_first.substring(0, 50); } lat = isLatNull && !isTLatNull ? t_lat : lat; lon = isLonNull && !isTLonNull ? t_lon : lon; try { // (Latitude1, Longitude1, SrcLatLongUnit, Lat1text, Long1text, LatLongType, DisciplineID, MaxElevation, LocalityName, GeographyID Integer geoId = buildTags.getGeography(country, state, null); // Latitude varies between -90 and 90, and Longitude between -180 and 180. if (lat < -90.0 || lat > 90.0) { lcStmt.setObject(1, null); lcStmt.setObject(4, null); } else { lcStmt.setDouble(1, lat); lcStmt.setString(4, Double.toString(lat)); lcUpStmt.setDouble(1, lat); lcUpStmt.setString(4, Double.toString(lat)); } if (lon < -180.0 || lon > 180.0) { lcStmt.setObject(2, null); lcStmt.setObject(5, null); } else { lcStmt.setDouble(2, lon); lcStmt.setString(5, Double.toString(lon)); lcUpStmt.setDouble(2, lon); lcUpStmt.setString(5, Double.toString(lon)); } String locName = null; String fullName = null; Integer locId = null; geoId = buildTags.getGeography(country, state, null); if (geoId != null) { fullName = geoFullNameHash.get(geoId); if (fullName == null) { fullName = BasicSQLUtils .querySingleObj("SELECT FullName FROM geography WHERE GeographyID = " + geoId); geoFullNameHash.put(geoId, fullName); } if (StringUtils.isNotEmpty(city)) { locName = city + ", " + fullName; } else { locName = fullName; } locId = localityHash.get(locName); if (locId == null) { lcStmt2.setString(1, "%" + city); lcStmt2.setString(2, country + "%"); ResultSet lcRS = lcStmt2.executeQuery(); if (lcRS.next()) { locId = lcRS.getInt(1); if (!lcRS.wasNull()) { localityHash.put(locName, locId); } } lcRS.close(); } } else { //unknown++; fullName = "Unknown"; locName = buildTags.buildLocalityName(city, fullName); geoId = 27507; // Unknown locId = localityHash.get(locName); //log.error("Couldn't find matching geography["+country+", "+state+", "+county+"]"); } if (locId == null) { lcStmt.setByte(3, (byte) 0); lcStmt.setString(6, "Point"); lcStmt.setInt(7, dspId); lcStmt.setString(8, getLocalityName(country, state, null, city)); lcStmt.setObject(9, geoId); lcStmt.setTimestamp(10, ts); lcStmt.setInt(11, 1); lcStmt.setInt(12, 1); lcStmt.executeUpdate(); locId = BasicSQLUtils.getInsertedId(lcStmt); } else if (!isLatNull && !isLonNull) { int count = BasicSQLUtils.getCountAsInt( "SELECT COUNT(*) FROM locality WHERE Latitude1 IS NULL AND Longitude1 IS NULL AND LocalityID = " + locId); if (count == 1) { lcUpStmt.setByte(3, (byte) 0); lcUpStmt.setString(6, "Point"); lcUpStmt.setInt(7, locId); lcUpStmt.executeUpdate(); } } // (StartDate, Method, DisciplineID, LocalityID ceStmt.setDate(1, date); ceStmt.setInt(2, dspId); ceStmt.setInt(3, locId); ceStmt.setTimestamp(4, ts); ceStmt.setInt(5, 1); ceStmt.setInt(6, 1); ceStmt.executeUpdate(); Integer ceId = BasicSQLUtils.getInsertedId(ceStmt); //(CatalogNumber, FieldNumber, Text1, Remarks, CollectionID, CollectionMemberId coStmt.setString(1, String.format("%09d", recNum++)); coStmt.setString(2, tag); coStmt.setString(3, gender); coStmt.setString(4, dir); coStmt.setString(5, dist); coStmt.setInt(6, colId); coStmt.setInt(7, colId); coStmt.setInt(8, ceId); coStmt.setTimestamp(9, ts); coStmt.setInt(10, 1); coStmt.setInt(11, 1); coStmt.executeUpdate(); //Integer coId = BasicSQLUtils.getInsertedId(coStmt); Integer agentId = getAgentId(agentStmt, rep_first, rep_last); if (agentId == null) { agStmt.setInt(1, 0); agStmt.setString(2, rep_first); agStmt.setString(3, rep_last); agStmt.setTimestamp(4, ts); agStmt.setInt(5, 1); agStmt.setInt(6, 1); agStmt.executeUpdate(); agentId = BasicSQLUtils.getInsertedId(agStmt); if (agentId != null) { adStmt.setString(1, rep_city); adStmt.setString(2, rep_state); adStmt.setString(3, rep_zip); adStmt.setString(4, rep_country); adStmt.setInt(5, agentId); adStmt.setTimestamp(6, ts); adStmt.setInt(7, 1); adStmt.setInt(8, 1); adStmt.executeUpdate(); } else { log.error("agentId is null after being created: " + rep_first + ", " + rep_last); } } // OrderIndex, IsPrimary, CollectingEventID, DivisionID, AgentID clStmt.setInt(1, 0); clStmt.setBoolean(2, true); clStmt.setInt(3, ceId); clStmt.setInt(4, divId); clStmt.setInt(5, agentId); clStmt.setTimestamp(6, ts); clStmt.setInt(7, 1); clStmt.setInt(8, 1); clStmt.executeUpdate(); } catch (Exception ex) { log.debug(recNum + " tag[" + tag + "]"); ex.printStackTrace(); } cnt++; if (cnt % 100 == 0) { System.out.println("Col Obj: " + cnt); } } coStmt.close(); ceStmt.close(); lcStmt.close(); clStmt.close(); rlStmt.close(); agStmt.close(); adStmt.close(); lcUpStmt.close(); buildTags.cleanup(); } }
From source file:gov.nih.nci.cadsr.sentinel.database.DBAlertOracle.java
/** * Update the Auto Run or Manual Run timestamp. * * @param id_/*from w w w . j a va2s . co m*/ * The alert id to update. * @param stamp_ * The new time. * @param run_ * true to update the auto run time, false to update the manual run * time * @param setInactive_ * true to set the alert status to inactive, false to leave the * status unchanged * @return 0 if successful, otherwise the database error code. */ public int updateRun(String id_, Timestamp stamp_, boolean run_, boolean setInactive_) { String update = "update sbrext.sn_alert_view_ext set " + ((run_) ? "last_auto_run" : "last_manual_run") + " = ?," + ((setInactive_) ? " al_status = 'I', " : "") + "modified_by = ? where al_idseq = ?"; PreparedStatement pstmt = null; int rc = 0; try { // Set all the SQL arguments. pstmt = _conn.prepareStatement(update); pstmt.setTimestamp(1, stamp_); pstmt.setString(2, _user); pstmt.setString(3, id_); pstmt.executeUpdate(); _needCommit = true; } catch (SQLException ex) { _errorCode = DBAlertUtil.getSQLErrorCode(ex); _errorMsg = _errorCode + ": " + update + "\n\n" + ex.toString(); _logger.error(_errorMsg); rc = _errorCode; } finally { closeCursors(pstmt, null); } return rc; }
From source file:gov.nih.nci.cadsr.sentinel.database.DBAlertOracle.java
/** * Pull rows changed in the date range specified. There are 3 different * patterns to handle:/*w w w.j a v a2 s .c o m*/ * <p> * <ul> * <li>[from/to, from/to] {2, 4} - This represents the from/to date pair * which may occur 2 or 4 times in the SQL. This pattern is handled by this * method argument list.</li> * <li>[in, from/to, from/to] {2, 4} - This represents a single "in" clause * of creators or modifiers followed by the from/to pair which may occur 2 * or 4 times in the SQL in this order.</li> * <li>[in, in, from/to, from/to] {2,4} - This represents an "in" clause * for the creators and an "in" clause for the modifiers followed by the * from/to pair which in total may appear 1 or 2 times.</li> * </ul> * </p> * * @param select_ * The SQL select for the specific data and table. * @param start_ * The date to start. * @param end_ * The date to end. * @param pairs_ * The number of pairs of (start, end) that appear in the SQL. * @return 0 if successful, otherwise the database error code. */ private ACData[] selectAC(String select_, Timestamp start_, Timestamp end_, int pairs_) { PreparedStatement pstmt = null; ResultSet rs = null; ACData[] list = null; try { pstmt = _conn.prepareStatement(select_); for (int ndx = 0; ndx < pairs_; ++ndx) { pstmt.setTimestamp((ndx * 2) + 1, start_); pstmt.setTimestamp((ndx * 2) + 2, end_); } rs = pstmt.executeQuery(); list = copyResults(rs); } catch (SQLException ex) { _errorCode = DBAlertUtil.getSQLErrorCode(ex); _errorMsg = _errorCode + ": " + select_ + "\n\n" + ex.toString(); _logger.error(_errorMsg); } finally { closeCursors(pstmt, rs); } return list; }
From source file:com.pari.nm.utils.db.InventoryDBHelper.java
public static void updateInventoryTime(int nodeId, long invUpdateTime) { Connection c = null;/*from ww w . j a v a2 s .com*/ PreparedStatement ps = null; try { c = DBHelper.getConnection(); ps = c.prepareStatement(DBHelperConstants.UPDATE_INVENTORY_TIME); ps.setTimestamp(1, (new Timestamp(invUpdateTime))); ps.setInt(2, nodeId); ps.executeUpdate(); } catch (Exception ee) { logger.warn("Error while updating inventory time for the device= " + nodeId, ee); } finally { try { ps.close(); } catch (Exception ee) { } try { DBHelper.releaseConnection(c); } catch (Exception ex) { } } }