List of usage examples for java.sql PreparedStatement setBoolean
void setBoolean(int parameterIndex, boolean x) throws SQLException;
boolean value. From source file:com.flexive.ejb.beans.PhraseEngineBean.java
/** * {@inheritDoc}//from w w w . j a v a 2s. co m */ @Override @TransactionAttribute(TransactionAttributeType.REQUIRED) public void assignPhrase(int category, long assignmentOwner, long nodeId, long nodeMandator, long phraseId, long phraseMandator, long pos, boolean checkPositioning) throws FxNotFoundException, FxNoAccessException { checkMandatorAccess(assignmentOwner, FxContext.getUserTicket()); Connection con = null; PreparedStatement ps = null; try { // Obtain a database connection con = Database.getDbConnection(); ps = con.prepareStatement("SELECT ID FROM " + TBL_PHRASE_TREE + " WHERE ID=? AND MANDATOR=? AND CAT=?"); ps.setLong(1, nodeId); ps.setLong(2, nodeMandator); ps.setInt(3, category); ResultSet rs = ps.executeQuery(); if (rs == null || !(rs.next())) throw new FxNotFoundException("ex.phrases.node.notFound.id", nodeId, nodeMandator); ps.close(); ps = con.prepareStatement("SELECT POS FROM " + TBL_PHRASE_MAP + " WHERE MANDATOR=? AND NODEID=? AND NODEMANDATOR=? AND PHRASEID=? AND PMANDATOR=? AND CAT=? AND DIRECT=TRUE"); ps.setLong(1, assignmentOwner); ps.setLong(2, nodeId); ps.setLong(3, nodeMandator); ps.setLong(4, phraseId); ps.setLong(5, phraseMandator); ps.setInt(6, category); rs = ps.executeQuery(); if (rs != null && rs.next()) { long orgPos = rs.getLong(1); if (!rs.wasNull()) { if (orgPos == pos) return; if (pos <= 1 && orgPos == 1) return; //already at the top updatePhrasePosition(con, category, assignmentOwner, nodeId, nodeMandator, phraseId, phraseMandator, pos, checkPositioning); return; } } //insert ps.close(); //remove from phrase map in case it is already there as indirect ps = con.prepareStatement("DELETE FROM " + TBL_PHRASE_MAP + " WHERE MANDATOR=? AND CAT=? AND NODEID=? AND NODEMANDATOR=? AND PHRASEID=? AND PMANDATOR=?"); ps.setLong(1, assignmentOwner); ps.setInt(2, category); ps.setLong(3, nodeId); ps.setLong(4, nodeMandator); ps.setLong(5, phraseId); ps.setLong(6, phraseMandator); ps.executeUpdate(); ps.close(); ps = con.prepareStatement("INSERT INTO " + TBL_PHRASE_MAP + "(MANDATOR,CAT,NODEID,NODEMANDATOR,PHRASEID,PMANDATOR,POS,DIRECT)VALUES(?,?,?,?,?,?,?,?)"); ps.setLong(1, assignmentOwner); ps.setInt(2, category); ps.setLong(3, nodeId); ps.setLong(4, nodeMandator); ps.setLong(5, phraseId); ps.setLong(6, phraseMandator); ps.setLong(7, pos); ps.setBoolean(8, true); ps.executeUpdate(); if (checkPositioning) updatePhrasePosition(con, category, assignmentOwner, nodeId, nodeMandator, phraseId, phraseMandator, pos, checkPositioning); rebuildPhraseChildMapping(con, assignmentOwner, category, phraseId, phraseMandator); } catch (SQLException exc) { EJBUtils.rollback(ctx); throw new FxDbException(LOG, exc, "ex.db.sqlError", exc.getMessage()).asRuntimeException(); } finally { Database.closeObjects(PhraseEngineBean.class, con, ps); } }
From source file:org.moqui.impl.entity.EntityJavaUtil.java
public static void setPreparedStatementValue(PreparedStatement ps, int index, Object value, FieldInfo fi, boolean useBinaryTypeForBlob, EntityFacade efi) throws EntityException { try {/* w w w .j a v a2s .co m*/ // allow setting, and searching for, String values for all types; JDBC driver should handle this okay if (value instanceof CharSequence) { ps.setString(index, value.toString()); } else { switch (fi.typeValue) { case 1: if (value != null) { ps.setString(index, value.toString()); } else { ps.setNull(index, Types.VARCHAR); } break; case 2: if (value != null) { Class valClass = value.getClass(); if (valClass == Timestamp.class) { ps.setTimestamp(index, (Timestamp) value, efi.getCalendarForTzLc()); } else if (valClass == java.sql.Date.class) { ps.setDate(index, (java.sql.Date) value, efi.getCalendarForTzLc()); } else if (valClass == java.util.Date.class) { ps.setTimestamp(index, new Timestamp(((java.util.Date) value).getTime()), efi.getCalendarForTzLc()); } else { throw new IllegalArgumentException("Class " + valClass.getName() + " not allowed for date-time (Timestamp) fields, for field " + fi.entityName + "." + fi.name); } } else { ps.setNull(index, Types.TIMESTAMP); } break; case 3: Time tm = (Time) value; // logger.warn("=================== setting time tm=${tm} tm long=${tm.getTime()}, cal=${cal}") if (value != null) { ps.setTime(index, tm, efi.getCalendarForTzLc()); } else { ps.setNull(index, Types.TIME); } break; case 4: if (value != null) { Class valClass = value.getClass(); if (valClass == java.sql.Date.class) { java.sql.Date dt = (java.sql.Date) value; // logger.warn("=================== setting date dt=${dt} dt long=${dt.getTime()}, cal=${cal}") ps.setDate(index, dt, efi.getCalendarForTzLc()); } else if (valClass == Timestamp.class) { ps.setDate(index, new java.sql.Date(((Timestamp) value).getTime()), efi.getCalendarForTzLc()); } else if (valClass == java.util.Date.class) { ps.setDate(index, new java.sql.Date(((java.util.Date) value).getTime()), efi.getCalendarForTzLc()); } else { throw new IllegalArgumentException("Class " + valClass.getName() + " not allowed for date fields, for field " + fi.entityName + "." + fi.name); } } else { ps.setNull(index, Types.DATE); } break; case 5: if (value != null) { ps.setInt(index, ((Number) value).intValue()); } else { ps.setNull(index, Types.NUMERIC); } break; case 6: if (value != null) { ps.setLong(index, ((Number) value).longValue()); } else { ps.setNull(index, Types.NUMERIC); } break; case 7: if (value != null) { ps.setFloat(index, ((Number) value).floatValue()); } else { ps.setNull(index, Types.NUMERIC); } break; case 8: if (value != null) { ps.setDouble(index, ((Number) value).doubleValue()); } else { ps.setNull(index, Types.NUMERIC); } break; case 9: if (value != null) { Class valClass = value.getClass(); // most common cases BigDecimal, Double, Float; then allow any Number if (valClass == BigDecimal.class) { ps.setBigDecimal(index, (BigDecimal) value); } else if (valClass == Double.class) { ps.setDouble(index, (Double) value); } else if (valClass == Float.class) { ps.setFloat(index, (Float) value); } else if (value instanceof Number) { ps.setDouble(index, ((Number) value).doubleValue()); } else { throw new IllegalArgumentException("Class " + valClass.getName() + " not allowed for number-decimal (BigDecimal) fields, for field " + fi.entityName + "." + fi.name); } } else { ps.setNull(index, Types.NUMERIC); } break; case 10: if (value != null) { ps.setBoolean(index, (Boolean) value); } else { ps.setNull(index, Types.BOOLEAN); } break; case 11: if (value != null) { try { ByteArrayOutputStream os = new ByteArrayOutputStream(); ObjectOutputStream oos = new ObjectOutputStream(os); oos.writeObject(value); oos.close(); byte[] buf = os.toByteArray(); os.close(); ByteArrayInputStream is = new ByteArrayInputStream(buf); ps.setBinaryStream(index, is, buf.length); is.close(); } catch (IOException ex) { throw new EntityException( "Error setting serialized object, for field " + fi.entityName + "." + fi.name, ex); } } else { if (useBinaryTypeForBlob) { ps.setNull(index, Types.BINARY); } else { ps.setNull(index, Types.BLOB); } } break; case 12: if (value instanceof byte[]) { ps.setBytes(index, (byte[]) value); /* } else if (value instanceof ArrayList) { ArrayList valueAl = (ArrayList) value; byte[] theBytes = new byte[valueAl.size()]; valueAl.toArray(theBytes); ps.setBytes(index, theBytes); */ } else if (value instanceof ByteBuffer) { ByteBuffer valueBb = (ByteBuffer) value; ps.setBytes(index, valueBb.array()); } else if (value instanceof Blob) { Blob valueBlob = (Blob) value; // calling setBytes instead of setBlob // ps.setBlob(index, (Blob) value) // Blob blb = value ps.setBytes(index, valueBlob.getBytes(1, (int) valueBlob.length())); } else { if (value != null) { throw new IllegalArgumentException("Type not supported for BLOB field: " + value.getClass().getName() + ", for field " + fi.entityName + "." + fi.name); } else { if (useBinaryTypeForBlob) { ps.setNull(index, Types.BINARY); } else { ps.setNull(index, Types.BLOB); } } } break; case 13: if (value != null) { ps.setClob(index, (Clob) value); } else { ps.setNull(index, Types.CLOB); } break; case 14: if (value != null) { ps.setTimestamp(index, (Timestamp) value); } else { ps.setNull(index, Types.TIMESTAMP); } break; // TODO: is this the best way to do collections and such? case 15: if (value != null) { ps.setObject(index, value, Types.JAVA_OBJECT); } else { ps.setNull(index, Types.JAVA_OBJECT); } break; } } } catch (SQLException sqle) { throw new EntityException("SQL Exception while setting value [" + value + "](" + (value != null ? value.getClass().getName() : "null") + "), type " + fi.type + ", for field " + fi.entityName + "." + fi.name + ": " + sqle.toString(), sqle); } catch (Exception e) { throw new EntityException( "Error while setting value for field " + fi.entityName + "." + fi.name + ": " + e.toString(), e); } }
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"); }/* www.j a va 2 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:com.flexive.ejb.beans.structure.AssignmentEngineBean.java
/** * {@inheritDoc}/*ww w . jav a2 s . co m*/ */ @Override @TransactionAttribute(TransactionAttributeType.REQUIRED) public long createProperty(long typeId, FxPropertyEdit property, String parentXPath, String assignmentAlias) throws FxApplicationException { FxPermissionUtils.checkRole(FxContext.getUserTicket(), Role.StructureManagement); Connection con = null; PreparedStatement ps = null; StringBuilder sql = new StringBuilder(2000); long newPropertyId; long newAssignmentId; try { parentXPath = parentXPath.toUpperCase(); assignmentAlias = assignmentAlias.toUpperCase(); FxType type = CacheAdmin.getEnvironment().getType(typeId); FxAssignment tmp = type.getAssignment(parentXPath); if (tmp != null && tmp instanceof FxPropertyAssignment) throw new FxInvalidParameterException("ex.structure.assignment.noGroup", parentXPath); property.checkConsistency(); //parentXPath is valid, create the property, then assign it to root newPropertyId = seq.getId(FxSystemSequencer.TYPEPROP); FxValue defValue = property.getDefaultValue(); ContentStorage storage = StorageManager.getContentStorage(type.getStorageMode()); con = Database.getDbConnection(); if (defValue instanceof FxBinary) { storage.prepareBinary(con, (FxBinary) defValue); } final String _def = defValue == null || defValue.isEmpty() ? null : ConversionEngine.getXStream().toXML(defValue); if (_def != null && (property.getDefaultValue() instanceof FxReference)) { //check if the type matches the instance checkReferencedType(con, (FxReference) property.getDefaultValue(), property.getReferencedType()); } // do not allow to add mandatory properties (i.e. min multiplicity > 0) to types for which content exists if (storage.getTypeInstanceCount(con, typeId) > 0 && property.getMultiplicity().getMin() > 0) { throw new FxCreateException("ex.structure.property.creation.existingContentMultiplicityError", property.getName(), property.getMultiplicity().getMin()); } //create property, no checks for existing names are performed as this is handled with unique keys sql.append("INSERT INTO ").append(TBL_STRUCT_PROPERTIES). // 1 2 3 4 5 6 7 append("(ID,NAME,DEFMINMULT,DEFMAXMULT,MAYOVERRIDEMULT,DATATYPE,REFTYPE," + //8 9 10 11 12 "ISFULLTEXTINDEXED,ACL,MAYOVERRIDEACL,REFLIST,UNIQUEMODE," + //13 14 "SYSINTERNAL,DEFAULT_VALUE)VALUES(" + "?,?,?,?,?," + "?,?,?,?,?,?,?,?,?)"); ps = con.prepareStatement(sql.toString()); ps.setLong(1, newPropertyId); ps.setString(2, property.getName()); ps.setInt(3, property.getMultiplicity().getMin()); ps.setInt(4, property.getMultiplicity().getMax()); ps.setBoolean(5, property.mayOverrideBaseMultiplicity()); ps.setLong(6, property.getDataType().getId()); if (property.hasReferencedType()) ps.setLong(7, property.getReferencedType().getId()); else ps.setNull(7, java.sql.Types.NUMERIC); ps.setBoolean(8, property.isFulltextIndexed()); ps.setLong(9, property.getACL().getId()); ps.setBoolean(10, property.mayOverrideACL()); if (property.hasReferencedList()) ps.setLong(11, property.getReferencedList().getId()); else ps.setNull(11, java.sql.Types.NUMERIC); ps.setInt(12, property.getUniqueMode().getId()); ps.setBoolean(13, false); if (_def == null) ps.setNull(14, java.sql.Types.VARCHAR); else ps.setString(14, _def); if (!property.isAutoUniquePropertyName()) ps.executeUpdate(); else { //fetch used property names PreparedStatement ps2 = null; try { ps2 = con.prepareStatement( "SELECT NAME FROM " + TBL_STRUCT_PROPERTIES + " WHERE NAME LIKE ? OR NAME=?"); ps2.setString(1, property.getName() + "_%"); ps2.setString(2, property.getName()); ResultSet rs = ps2.executeQuery(); int max = -1; while (rs.next()) { String last = rs.getString(1); if (last.equals(property.getName()) || last.startsWith(property.getName() + "_")) { if (last.equals(property.getName())) { max = Math.max(0, max); } else if (last.startsWith(property.getName() + "_")) { final String suffix = last.substring(last.lastIndexOf("_") + 1); if (StringUtils.isNumeric(suffix)) { max = Math.max(Integer.parseInt(suffix), max); } } } if (max != -1) { final String autoName = property.getName() + "_" + (max + 1); ps.setString(2, autoName); LOG.info("Assigning unique property name [" + autoName + "] to [" + type.getName() + "." + property.getName() + "]"); } } } finally { Database.closeObjects(AssignmentEngineBean.class, ps2); } ps.executeUpdate(); } Database.storeFxString(new FxString[] { property.getLabel(), property.getHint() }, con, TBL_STRUCT_PROPERTIES, new String[] { "DESCRIPTION", "HINT" }, "ID", newPropertyId); ps.close(); sql.setLength(0); //calc new position sql.append("SELECT COALESCE(MAX(POS)+1,0) FROM ").append(TBL_STRUCT_ASSIGNMENTS) .append(" WHERE PARENTGROUP=? AND TYPEDEF=?"); ps = con.prepareStatement(sql.toString()); ps.setLong(1, (tmp == null ? FxAssignment.NO_PARENT : tmp.getId())); ps.setLong(2, typeId); ResultSet rs = ps.executeQuery(); long pos = 0; if (rs != null && rs.next()) pos = rs.getLong(1); ps.close(); storeOptions(con, TBL_STRUCT_PROPERTY_OPTIONS, "ID", newPropertyId, null, property.getOptions()); sql.setLength(0); //create root assignment sql.append("INSERT INTO ").append(TBL_STRUCT_ASSIGNMENTS). // 1 2 3 4 5 6 7 8 9 10 11 12 13 append("(ID,ATYPE,ENABLED,TYPEDEF,MINMULT,MAXMULT,DEFMULT,POS,XPATH,XALIAS,BASE,PARENTGROUP,APROPERTY," + //14 15 "ACL,DEFAULT_VALUE)" + "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"); ps = con.prepareStatement(sql.toString()); newAssignmentId = seq.getId(FxSystemSequencer.ASSIGNMENT); ps.setLong(1, newAssignmentId); ps.setInt(2, FxAssignment.TYPE_PROPERTY); ps.setBoolean(3, true); ps.setLong(4, typeId); ps.setInt(5, property.getMultiplicity().getMin()); ps.setInt(6, property.getMultiplicity().getMax()); if (property.getMultiplicity().isValid(property.getAssignmentDefaultMultiplicity())) { ps.setInt(7, property.getAssignmentDefaultMultiplicity()); } else { //default is min(min,1). ps.setInt(7, property.getMultiplicity().getMin() > 1 ? property.getMultiplicity().getMin() : 1); } ps.setLong(8, pos); if (parentXPath == null || "/".equals(parentXPath)) parentXPath = ""; ps.setString(9, type.getName() + XPathElement.stripType(parentXPath) + "/" + assignmentAlias); ps.setString(10, assignmentAlias); ps.setNull(11, Types.NUMERIC); if (tmp == null) ps.setLong(12, FxAssignment.NO_PARENT); else ps.setLong(12, tmp.getId()); ps.setLong(13, newPropertyId); ps.setLong(14, property.getACL().getId()); ps.setString(15, _def); ps.executeUpdate(); Database.storeFxString(new FxString[] { property.getLabel(), property.getHint() }, con, TBL_STRUCT_ASSIGNMENTS, new String[] { "DESCRIPTION", "HINT" }, "ID", newAssignmentId); StructureLoader.reloadAssignments(FxContext.get().getDivisionId()); if (divisionConfig.isFlatStorageEnabled() && divisionConfig.get(SystemParameters.FLATSTORAGE_AUTO)) { final FxFlatStorage fs = FxFlatStorageManager.getInstance(); FxPropertyAssignment pa = (FxPropertyAssignment) CacheAdmin.getEnvironment() .getAssignment(newAssignmentId); if (fs.isFlattenable(pa)) { fs.flatten(con, fs.getDefaultStorage(), pa); StructureLoader.reloadAssignments(FxContext.get().getDivisionId()); } } htracker.track(type, "history.assignment.createProperty", property.getName(), type.getId(), type.getName()); if (type.getId() != FxType.ROOT_ID) createInheritedAssignments(CacheAdmin.getEnvironment().getAssignment(newAssignmentId), con, sql, type.getDerivedTypes()); } catch (FxNotFoundException e) { EJBUtils.rollback(ctx); throw new FxCreateException(e); } catch (FxLoadException e) { EJBUtils.rollback(ctx); throw new FxCreateException(e); } catch (SQLException e) { final boolean uniqueConstraintViolation = StorageManager.isUniqueConstraintViolation(e); EJBUtils.rollback(ctx); if (uniqueConstraintViolation) throw new FxEntryExistsException("ex.structure.property.exists", property.getName(), (parentXPath.length() == 0 ? "/" : parentXPath)); throw new FxCreateException(LOG, e, "ex.db.sqlError", e.getMessage()); } finally { Database.closeObjects(AssignmentEngineBean.class, con, ps); } return newAssignmentId; }
From source file:org.openmrs.module.reporting.report.util.SqlUtils.java
/** * Binds the given paramMap to the query by replacing all named parameters (e.g. :paramName) * with their corresponding values in the parameter map. TODO copied from * HibernateCohortQueryDAO//from ww w. ja va 2 s . co m * * @param connection * @param query * @param paramMap * @throws SQLException */ @SuppressWarnings("unchecked") public static PreparedStatement prepareStatement(Connection connection, String query, Map<String, Object> paramMap) throws SQLException { PreparedStatement statement; if (!isSelectQuery(query)) { throw new IllegalDatabaseAccessException(); } boolean containParams = query.indexOf(":") > 0; if (containParams) { // the first process is replacing the :paramName with ? // implementation taken from: http://www.javaworld.com/javaworld/jw-04-2007/jw-04-jdbc.html?page=2 Map<String, List<Integer>> params = new HashMap<String, List<Integer>>(); StringBuffer parsedQuery = new StringBuffer(); int index = 1; for (int i = 0; i < query.length(); i++) { // we can use charAt here, but we might need to append "(?, ?, ?)" when the where parameter is a list // http://stackoverflow.com/questions/178479/alternatives-for-java-sql-preparedstatement-in-clause-issue // http://www.javaranch.com/journal/200510/Journal200510.jsp#a2 String s = query.substring(i, i + 1); if (StringUtils.equals(s, ":") && i + 1 < query.length() && Character.isJavaIdentifierStart(query.charAt(i + 1))) { // we already make sure that (i + 1) is a valid character, now check the next one after (i + 1) int j = i + 2; while (j < query.length() && Character.isJavaIdentifierPart(query.charAt(j))) j++; String name = query.substring(i + 1, j); Object paramValue = paramMap.get(name); // are we dealing with collection or not int size = 1; if (paramValue != null) if (Cohort.class.isAssignableFrom(paramValue.getClass())) size = ((Cohort) paramValue).getSize(); else if (Collection.class.isAssignableFrom(paramValue.getClass())) size = ((Collection<?>) paramValue).size(); // skip until the end of the param name i += name.length(); String[] sqlParams = new String[size]; for (int k = 0; k < sqlParams.length; k++) { sqlParams[k] = "?"; // record the location of the parameter in the sql statemet List<Integer> indexList = params.get(name); if (indexList == null) { indexList = new LinkedList<Integer>(); params.put(name, indexList); } indexList.add(new Integer(index)); index++; } s = StringUtils.join(sqlParams, ","); // for the "IN" query, we need to add bracket if (size > 1) s = "(" + s + ")"; } parsedQuery.append(s); } // the query string contains parameters, re-create the prepared statement with the new parsed query string statement = connection.prepareStatement(parsedQuery.toString()); // Iterate over parameters and bind them to the Query object for (String paramName : paramMap.keySet()) { Object paramValue = paramMap.get(paramName); // Indicates whether we should bind this parameter in the query // Make sure parameter value is not null if (paramValue == null) { // TODO Should try to convert 'columnName = null' to 'columnName IS NULL' throw new ParameterException("Cannot bind an empty value to parameter " + paramName + ". " + "Please provide a real value or use the 'IS NULL' constraint in your query (e.g. 'table.columnName IS NULL')."); } int i = 0; List<Integer> positions = params.get(paramName); if (positions != null) { // Cohort (needs to be first, otherwise it will resolve as OpenmrsObject) if (Cohort.class.isAssignableFrom(paramValue.getClass())) { Cohort cohort = (Cohort) paramValue; for (Integer patientId : cohort.getMemberIds()) { statement.setInt(positions.get(i++), patientId); } } // OpenmrsObject (e.g. Location) else if (OpenmrsObject.class.isAssignableFrom(paramValue.getClass())) { for (Integer position : positions) { statement.setInt(position, ((OpenmrsObject) paramValue).getId()); } } // List<OpenmrsObject> (e.g. List<Location>) else if (List.class.isAssignableFrom(paramValue.getClass())) { // If first element in the list is an OpenmrsObject if (OpenmrsObject.class.isAssignableFrom(((List<?>) paramValue).get(0).getClass())) { List<Integer> openmrsObjectIds = SqlUtils .openmrsObjectIdListHelper((List<OpenmrsObject>) paramValue); for (Integer openmrsObjectId : openmrsObjectIds) { statement.setInt(positions.get(i++), openmrsObjectId); } } // a List of Strings, Integers? else { List<String> strings = SqlUtils.objectListHelper((List<Object>) paramValue); for (String string : strings) { statement.setString(positions.get(i++), string); } } } // java.util.Date and subclasses else if (paramValue instanceof Date) { for (Integer position : positions) { statement.setDate(position, new java.sql.Date(((Date) paramValue).getTime())); } } else if (paramValue instanceof Integer || paramValue instanceof Long) { for (Integer position : positions) { statement.setLong(position, (Integer) paramValue); } } else if (paramValue instanceof Boolean) { for (Integer position : positions) { statement.setBoolean(position, (Boolean) paramValue); } } // String, et al (this might break since this is a catch all for all other classes) else { for (Integer position : positions) { statement.setString(position, new String(paramValue.toString())); } } } } } else statement = connection.prepareStatement(query); return statement; }
From source file:com.zimbra.cs.db.DbMailItem.java
public static PendingDelete getLeafNodes(Mailbox mbox, List<Folder> folders, int before, boolean globalMessages, Boolean unread, boolean useChangeDate, Integer maxItems) throws ServiceException { DbConnection conn = mbox.getOperationConnection(); PreparedStatement stmt = null; ResultSet rs = null;/* w w w .j a v a 2 s.com*/ if (folders == null) { folders = Collections.emptyList(); } try { StringBuilder constraint = new StringBuilder(); String dateColumn = (useChangeDate ? "change_date" : "date"); if (globalMessages) { constraint.append(dateColumn).append(" < ? AND ").append(typeIn(MailItem.Type.MESSAGE)); } else { constraint.append(dateColumn).append(" < ? AND type NOT IN ").append(NON_SEARCHABLE_TYPES); if (!folders.isEmpty()) { constraint.append(" AND ").append(DbUtil.whereIn("folder_id", folders.size())); } } if (unread != null) { constraint.append(" AND unread = ?"); } String orderByLimit = ""; if (maxItems != null && Db.supports(Db.Capability.LIMIT_CLAUSE)) { orderByLimit = " ORDER BY " + dateColumn + " " + Db.getInstance().limit(maxItems); } stmt = conn.prepareStatement("SELECT " + LEAF_NODE_FIELDS + " FROM " + getMailItemTableName(mbox) + " WHERE " + IN_THIS_MAILBOX_AND + constraint + orderByLimit); if (globalMessages || getTotalFolderSize(folders) > RESULTS_STREAMING_MIN_ROWS) { Db.getInstance().enableStreaming(stmt); } int pos = 1; pos = setMailboxId(stmt, mbox, pos); stmt.setInt(pos++, before); if (!globalMessages) { for (Folder folder : folders) { stmt.setInt(pos++, folder.getId()); } } if (unread != null) { stmt.setBoolean(pos++, unread); } PendingDelete info = accumulateDeletionInfo(mbox, stmt); stmt = null; return info; } catch (SQLException e) { throw ServiceException.FAILURE("fetching list of items for purge", e); } finally { DbPool.closeResults(rs); DbPool.closeStatement(stmt); } }
From source file:com.flexive.ejb.beans.PhraseEngineBean.java
/** * {@inheritDoc}//from w w w . j ava 2 s. c om */ @Override @TransactionAttribute(TransactionAttributeType.REQUIRED) public void assignPhrases(int category, long position, long assignmentOwner, long nodeId, long nodeMandator, FxPhrase[] phrases) throws FxApplicationException { if (phrases == null || phrases.length == 0) return; checkMandatorAccess(assignmentOwner, FxContext.getUserTicket()); Connection con = null; PreparedStatement ps = null; try { // Obtain a database connection con = Database.getDbConnection(); //check categories ps = con.prepareStatement("SELECT ID FROM " + TBL_PHRASE_TREE + " WHERE ID=? AND MANDATOR=? AND CAT=?"); ps.setLong(1, nodeId); ps.setLong(2, nodeMandator); ps.setInt(3, category); ResultSet rs = ps.executeQuery(); if (rs == null || !(rs.next())) throw new FxNotFoundException("ex.phrases.node.notFound.id", nodeId, nodeMandator); ps.close(); long startPhraseId = -1, startPhraseMandator = -1; ps = con.prepareStatement("SELECT PHRASEID,PMANDATOR FROM " + TBL_PHRASE_MAP + " WHERE MANDATOR=? AND NODEID=? AND NODEMANDATOR=? AND POS>=? AND CAT=? AND DIRECT=TRUE ORDER BY POS ASC"); ps.setLong(1, assignmentOwner); ps.setLong(2, nodeId); ps.setLong(3, nodeMandator); ps.setLong(4, position); ps.setInt(5, category); rs = ps.executeQuery(); while (rs != null && rs.next()) { long pid = rs.getLong(1); long mid = rs.getLong(2); if (!phrasesContains(phrases, pid, mid)) { startPhraseId = pid; startPhraseMandator = mid; break; } } ps.close(); boolean useMaxPos = startPhraseId == -1 || startPhraseMandator == -1; //remove all contained phrases ps = con.prepareStatement("DELETE FROM " + TBL_PHRASE_MAP + " WHERE MANDATOR=? AND NODEID=? AND NODEMANDATOR=? AND PHRASEID=? AND PMANDATOR=? AND CAT=? AND DIRECT=TRUE"); ps.setLong(1, assignmentOwner); ps.setLong(2, nodeId); ps.setLong(3, nodeMandator); ps.setInt(6, category); for (FxPhrase rm : phrases) { ps.setLong(4, rm.getId()); ps.setLong(5, rm.getMandator()); ps.addBatch(); } ps.executeBatch(); ps.close(); //close gaps and reposition updatePhrasePosition(con, category, assignmentOwner, nodeId, nodeMandator, -1, -1, 0, true); int insertPos = -1; if (!useMaxPos) { ps = con.prepareStatement("SELECT POS FROM " + TBL_PHRASE_MAP + " WHERE MANDATOR=? AND NODEID=? AND NODEMANDATOR=? AND PHRASEID=? AND PMANDATOR=? AND CAT=? AND DIRECT=?"); ps.setLong(1, assignmentOwner); ps.setLong(2, nodeId); ps.setLong(3, nodeMandator); ps.setLong(4, startPhraseId); ps.setLong(5, startPhraseMandator); ps.setInt(6, category); ps.setBoolean(7, true); rs = ps.executeQuery(); if (rs != null && rs.next()) insertPos = rs.getInt(1); ps.close(); } if (insertPos == -1) useMaxPos = true; if (!useMaxPos) { //make room for the phrases to insert ps = con.prepareStatement("UPDATE " + TBL_PHRASE_MAP + " SET POS=POS+" + (phrases.length) + " WHERE MANDATOR=? AND NODEID=? AND NODEMANDATOR=? AND POS>? AND CAT=? AND DIRECT=?"); ps.setLong(1, assignmentOwner); ps.setLong(2, nodeId); ps.setLong(3, nodeMandator); ps.setLong(4, insertPos); ps.setInt(5, category); ps.setBoolean(6, true); ps.executeUpdate(); ps.close(); } else { ps = con.prepareStatement("SELECT MAX(POS) FROM " + TBL_PHRASE_MAP + " WHERE MANDATOR=? AND NODEID=? AND NODEMANDATOR=? AND CAT=? AND DIRECT=?"); ps.setLong(1, assignmentOwner); ps.setLong(2, nodeId); ps.setLong(3, nodeMandator); ps.setInt(4, category); ps.setBoolean(5, true); rs = ps.executeQuery(); if (rs != null && rs.next()) insertPos = rs.getInt(1); else insertPos = 1; //fallback: first entry ps.close(); } ps = con.prepareStatement("INSERT INTO " + TBL_PHRASE_MAP + "(MANDATOR,CAT,NODEID,NODEMANDATOR,PHRASEID,PMANDATOR,POS,DIRECT)VALUES(?,?,?,?,?,?,?,?)"); ps.setLong(1, assignmentOwner); ps.setInt(2, category); ps.setLong(3, nodeId); ps.setLong(4, nodeMandator); ps.setBoolean(8, true); for (FxPhrase phrase : phrases) { ps.setLong(5, phrase.getId()); ps.setLong(6, phrase.getMandator()); ps.setLong(7, ++insertPos); ps.addBatch(); } ps.executeBatch(); if (phrases.length > 10) rebuildPhraseChildMapping(con, assignmentOwner, category, -1, -1); else { for (FxPhrase phrase : phrases) { rebuildPhraseChildMapping(con, assignmentOwner, category, phrase.getId(), phrase.getMandator()); } } } catch (SQLException exc) { EJBUtils.rollback(ctx); throw new FxDbException(LOG, exc, "ex.db.sqlError", exc.getMessage()).asRuntimeException(); } finally { Database.closeObjects(PhraseEngineBean.class, con, ps); } }
From source file:edu.ku.brc.specify.conversion.SpecifyDBConverter.java
/** * @param oldDBConn/*from w ww .j a v a2 s . co m*/ * @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
/** * Used by both the public saveRecord and globalEdit methods *///from w w w. j a va2 s. c om 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:org.apache.tajo.catalog.store.AbstractDBStore.java
@Override public void createIndex(final IndexDescProto proto) throws UndefinedDatabaseException, UndefinedTableException, DuplicateIndexException { Connection conn = null;/*w w w.j av a 2 s .c o m*/ PreparedStatement pstmt = null; final String databaseName = proto.getTableIdentifier().getDatabaseName(); final String tableName = extractSimpleName(proto.getTableIdentifier().getTableName()); try { // indexes table int databaseId = getDatabaseId(databaseName); int tableId = getTableId(databaseId, databaseName, tableName); String sql = String.format("SELECT INDEX_NAME FROM %s WHERE DB_ID=? AND INDEX_NAME=?", TB_INDEXES); conn = getConnection(); conn.setAutoCommit(false); pstmt = conn.prepareStatement(sql); pstmt.setInt(1, databaseId); pstmt.setString(2, proto.getIndexName()); ResultSet res = pstmt.executeQuery(); if (res.next()) { throw new DuplicateIndexException(proto.getIndexName()); } pstmt.close(); res.close(); sql = "INSERT INTO " + TB_INDEXES + " (" + COL_DATABASES_PK + ", " + COL_TABLES_PK + ", INDEX_NAME, " + "INDEX_TYPE, PATH, COLUMN_NAMES, DATA_TYPES, ORDERS, NULL_ORDERS, IS_UNIQUE, IS_CLUSTERED) " + "VALUES (?,?,?,?,?,?,?,?,?,?,?)"; if (LOG.isDebugEnabled()) { LOG.debug(sql); } final SortSpec[] keySortSpecs = new SortSpec[proto.getKeySortSpecsCount()]; for (int i = 0; i < keySortSpecs.length; i++) { keySortSpecs[i] = new SortSpec(proto.getKeySortSpecs(i)); } StringBuilder columnNamesBuilder = new StringBuilder(); StringBuilder dataTypesBuilder = new StringBuilder(); StringBuilder ordersBuilder = new StringBuilder(); StringBuilder nullOrdersBuilder = new StringBuilder(); for (SortSpec columnSpec : keySortSpecs) { // Since the key columns are always sorted in order of their occurrence position in the relation schema, // the concatenated name can be uniquely identified. columnNamesBuilder.append(columnSpec.getSortKey().getSimpleName()).append(","); dataTypesBuilder.append(columnSpec.getSortKey().getDataType().getType().name()).append("|"); ordersBuilder.append(columnSpec.isAscending()).append(","); nullOrdersBuilder.append(columnSpec.isNullsFirst()).append(","); } columnNamesBuilder.deleteCharAt(columnNamesBuilder.length() - 1); dataTypesBuilder.deleteCharAt(dataTypesBuilder.length() - 1); ordersBuilder.deleteCharAt(ordersBuilder.length() - 1); nullOrdersBuilder.deleteCharAt(nullOrdersBuilder.length() - 1); pstmt = conn.prepareStatement(sql); pstmt.setInt(1, databaseId); pstmt.setInt(2, tableId); pstmt.setString(3, proto.getIndexName()); // index name pstmt.setString(4, proto.getIndexMethod().toString()); // index type pstmt.setString(5, proto.getIndexPath()); // index path pstmt.setString(6, columnNamesBuilder.toString()); pstmt.setString(7, dataTypesBuilder.toString()); pstmt.setString(8, ordersBuilder.toString()); pstmt.setString(9, nullOrdersBuilder.toString()); pstmt.setBoolean(10, proto.hasIsUnique() && proto.getIsUnique()); pstmt.setBoolean(11, proto.hasIsClustered() && proto.getIsClustered()); pstmt.executeUpdate(); conn.commit(); } catch (SQLException se) { throw new TajoInternalError(se); } finally { CatalogUtil.closeQuietly(pstmt); } }