List of usage examples for java.sql PreparedStatement setNull
void setNull(int parameterIndex, int sqlType) throws SQLException;
NULL. From source file:com.portfolio.data.provider.MysqlAdminProvider.java
@Override public String postAddNodeType(int userId, Integer type, Integer nodeid, Integer parentid, Integer instance, String data) {//w w w . j a v a2 s . com if (!credential.isAdmin(userId)) throw new RestWebApplicationException(Status.FORBIDDEN, "No admin right"); /** * Format que l'on reoit: * <asm*> * <asmResource xsi_type='nodeRes'>{node_data}</asmResource> * <asmResource xsi_type='context'>{node_data}</asmResource> * <asmResource xsi_type='*'>{node_data}</asmResource> * </asm*> */ String sql = ""; PreparedStatement st; Integer output = 0; Integer parentId = 0; String asmtype = ""; String xsitype = ""; try { /// Prpare les donnes pour les requtes // Parse DocumentBuilderFactory documentBuilderFactory = DocumentBuilderFactory.newInstance(); DocumentBuilder documentBuilder = documentBuilderFactory.newDocumentBuilder(); Document document = documentBuilder.parse(new ByteArrayInputStream(data.getBytes("UTF-8"))); // Traite le noeud racine des donnes, retourne l'identifiant du noeud racine Element nodeData = document.getDocumentElement(); asmtype = nodeData.getNodeName(); connection.setAutoCommit(true); // Utilise parentid si on rattache un autre groupe de noeud en dessous d'un noeud existant sql = "INSERT INTO definition_type(def_id,asm_type,parent_node,instance_rule) " + "VALUE(?,?,?,?)"; st = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); if (dbserveur.equals("oracle")) { st = connection.prepareStatement(sql, new String[] { "node_id" }); } st.setInt(1, type); st.setString(2, asmtype); if (parentid == null) st.setNull(3, Types.BIGINT); else st.setInt(3, parentid); if (instance == null) st.setNull(4, Types.BIGINT); else st.setInt(4, instance); output = st.executeUpdate(); ResultSet key = st.getGeneratedKeys(); // On rcupre l'identifiant du noeud 'racine' des donnes ajouts if (key.next()) parentId = key.getInt(1); st.close(); // Soit 2 ou 3 resources asmtype = "asmResource"; NodeList resources = document.getElementsByTagName("asmResource"); sql = "INSERT INTO definition_type(def_id,asm_type,xsi_type,parent_node,node_data,instance_rule) " + "VALUE(?,?,?,?,?,?)"; st = connection.prepareStatement(sql); st.setInt(1, type); st.setString(2, asmtype); st.setInt(4, parentId); for (int i = 0; i < resources.getLength(); ++i) { Element resource = (Element) resources.item(i); xsitype = resource.getAttribute("xsi_type"); String resContent = DomUtils.getInnerXml(resource); st.setString(3, xsitype); st.setString(5, resContent); if (instance == null) st.setNull(6, Types.BIGINT); else st.setInt(6, instance); // On ajoute les donnes des ressources restante output = st.executeUpdate(); } st.close(); } catch (Exception e) { try { connection.rollback(); } catch (SQLException e1) { e1.printStackTrace(); } e.printStackTrace(); } finally { try { connection.setAutoCommit(true); connection.close(); } catch (SQLException e) { e.printStackTrace(); } } return output.toString(); }
From source file:com.flexive.ejb.beans.structure.AssignmentEngineBean.java
/** * Updates a property//from w w w. j a v a 2 s. com * * @param con a valid and open connection * @param prop the instance of FxPropertyEdit to be changed * @return returns true if changes were made to the property * @throws FxApplicationException on errors */ private boolean updateProperty(Connection con, FxPropertyEdit prop) throws FxApplicationException { if (prop.isNew()) throw new FxInvalidParameterException("ex.structure.property.update.new", prop.getName()); boolean changes = false; boolean success = false; StringBuilder changesDesc = new StringBuilder(200); final FxEnvironment env = CacheAdmin.getEnvironment(); FxProperty org = env.getProperty(prop.getId()); PreparedStatement ps = null; try { if (!org.isSystemInternal() || FxContext.getUserTicket().isGlobalSupervisor()) { // change the multiplicity override prop if (org.mayOverrideBaseMultiplicity() != prop.mayOverrideBaseMultiplicity()) { if (!prop.mayOverrideBaseMultiplicity() && getPropertyInstanceCount(prop.getId()) > 0) { final long minMult = getPropertyInstanceMultiplicity(con, org.getId(), true); if (minMult > 0 && minMult < prop.getMultiplicity().getMin()) throw new FxUpdateException("ex.structure.modification.contentExists", "minimumMultiplicity"); if (getPropertyInstanceMultiplicity(con, org.getId(), false) > prop.getMultiplicity() .getMax()) throw new FxUpdateException("ex.structure.modification.contentExists", "maximumMultiplicity"); } ps = con.prepareStatement( "UPDATE " + TBL_STRUCT_PROPERTIES + " SET MAYOVERRIDEMULT=? WHERE ID=?"); ps.setBoolean(1, prop.mayOverrideBaseMultiplicity()); ps.setLong(2, prop.getId()); ps.executeUpdate(); ps.close(); if (changes) changesDesc.append(','); changesDesc.append("mayOverrideMultiplicity=").append(prop.mayOverrideBaseMultiplicity()); changes = true; } // change the props multiplicity if (org.getMultiplicity().getMin() != prop.getMultiplicity().getMin() || org.getMultiplicity().getMax() != prop.getMultiplicity().getMax()) { if (!prop.mayOverrideBaseMultiplicity()) { if (org.getMultiplicity().getMin() < prop.getMultiplicity().getMin()) { for (FxPropertyAssignment pa : CacheAdmin.getEnvironment() .getPropertyAssignments(prop.getId(), true)) checkChangePropertyAssignmentMinMultiplicity(con, pa, prop.getMultiplicity()); } if (org.getMultiplicity().getMax() > prop.getMultiplicity().getMax()) { if (getPropertyInstanceMultiplicity(con, org.getId(), false) > prop.getMultiplicity() .getMax()) throw new FxUpdateException("ex.structure.modification.contentExists", "maximumMultiplicity"); } } ps = con.prepareStatement( "UPDATE " + TBL_STRUCT_PROPERTIES + " SET DEFMINMULT=? ,DEFMAXMULT=? WHERE ID=?"); ps.setInt(1, prop.getMultiplicity().getMin()); ps.setInt(2, prop.getMultiplicity().getMax()); ps.setLong(3, prop.getId()); ps.executeUpdate(); ps.close(); if (changes) changesDesc.append(','); changesDesc.append("multiplicity=").append(prop.getMultiplicity()); changes = true; } //not supported yet if (!org.getName().equals(prop.getName())) { throw new FxUpdateException("ex.structure.modification.notSupported", "name"); /* if (ps != null) ps.close(); ps = con.prepareStatement("UPDATE " + TBL_STRUCT_PROPERTIES + " SET NAME=? WHERE ID=?"); ps.setString(1, prop.getName()); ps.setLong(2, prop.getId()); ps.executeUpdate(); if (changes) changesDesc.append(','); changesDesc.append("name=").append(prop.getName()); changes = true; */ } //may only change if there are no existing content instances that use this property already if (org.getDataType().getId() != prop.getDataType().getId()) { if (getPropertyInstanceCount(org.getId()) == 0) { ps = con.prepareStatement("UPDATE " + TBL_STRUCT_PROPERTIES + " SET DATATYPE=? WHERE ID=?"); ps.setLong(1, prop.getDataType().getId()); ps.setLong(2, prop.getId()); ps.executeUpdate(); ps.close(); //FX-858: get all assignments for this property and re-flatten if possible to reflect data type changes final List<FxPropertyAssignment> refAssignments = env .getReferencingPropertyAssignments(prop.getId()); final FxFlatStorage fs = FxFlatStorageManager.getInstance(); List<FxPropertyAssignment> flattened = new ArrayList<FxPropertyAssignment>( refAssignments.size()); for (FxPropertyAssignment refAssignment : refAssignments) { if (refAssignment.isFlatStorageEntry()) { fs.unflatten(con, refAssignment); flattened.add(refAssignment); } } if (flattened.size() > 0) { try { StructureLoader.reload(con); final FxEnvironment envNew = CacheAdmin.getEnvironment(); boolean needReload = false; for (FxPropertyAssignment ref : flattened) { final FxPropertyAssignment paNew = (FxPropertyAssignment) envNew .getAssignment(ref.getId()); if (fs.isFlattenable(paNew)) { fs.flatten(con, fs.getDefaultStorage(), paNew); needReload = true; } } if (needReload) StructureLoader.reload(con); } catch (FxCacheException e) { EJBUtils.rollback(ctx); throw new FxCreateException(e, "ex.cache", e.getMessage()); } } if (changes) changesDesc.append(','); changesDesc.append("dataType=").append(prop.getDataType().getName()); changes = true; } else throw new FxUpdateException("ex.structure.modification.contentExists", "dataType"); } //may only change if there are no existing content instances that use this property already if (org.getReferencedType() != null && prop.getReferencedType() != null && org.getReferencedType().getId() != prop.getReferencedType().getId() || org.hasReferencedType() != prop.hasReferencedType()) { if (getPropertyInstanceCount(org.getId()) == 0) { if (prop.isDefaultValueSet() && (prop.getDefaultValue() instanceof FxReference)) { //check if the type matches the instance checkReferencedType(con, (FxReference) prop.getDefaultValue(), prop.getReferencedType()); //check for referencing assignments final List<FxPropertyAssignment> refAssignments = env .getReferencingPropertyAssignments(prop.getId()); for (FxPropertyAssignment refAssignment : refAssignments) { if (refAssignment.hasAssignmentDefaultValue() && refAssignment.getDefaultValue() instanceof FxReference) checkReferencedType(con, (FxReference) refAssignment.getDefaultValue(), prop.getReferencedType()); } } ps = con.prepareStatement("UPDATE " + TBL_STRUCT_PROPERTIES + " SET REFTYPE=? WHERE ID=?"); ps.setLong(2, prop.getId()); if (prop.hasReferencedType()) { ps.setLong(1, prop.getReferencedType().getId()); } else ps.setNull(1, java.sql.Types.NUMERIC); ps.executeUpdate(); ps.close(); if (changes) changesDesc.append(','); changesDesc.append("referencedType=").append(prop.getReferencedType()); changes = true; } else throw new FxUpdateException("ex.structure.modification.contentExists", "referencedType"); } // set fulltext indexing for the property if (org.isFulltextIndexed() != prop.isFulltextIndexed()) { ps = con.prepareStatement( "UPDATE " + TBL_STRUCT_PROPERTIES + " SET ISFULLTEXTINDEXED=? WHERE ID=?"); ps.setBoolean(1, prop.isFulltextIndexed()); ps.setLong(2, prop.getId()); ps.executeUpdate(); ps.close(); if (changes) changesDesc.append(','); changesDesc.append("isFulltextIndexed=").append(prop.isFulltextIndexed()); FulltextIndexer ft = StorageManager.getFulltextIndexer(con); if (prop.isFulltextIndexed()) ft.rebuildIndexForProperty(prop.getId()); else ft.removeIndexForProperty(prop.getId()); changes = true; } // set ACL override flag if (org.mayOverrideACL() != prop.mayOverrideACL()) { ps = con.prepareStatement( "UPDATE " + TBL_STRUCT_PROPERTIES + " SET MAYOVERRIDEACL=? WHERE ID=?"); ps.setBoolean(1, prop.mayOverrideACL()); ps.setLong(2, prop.getId()); ps.executeUpdate(); ps.close(); if (changes) changesDesc.append(','); changesDesc.append("mayOverrideACL=").append(prop.mayOverrideACL()); changes = true; } //may only change if there are no existing content instances that use this property already if (org.getReferencedList() != null && prop.getReferencedList() != null && org.getReferencedList().getId() != prop.getReferencedList().getId() || org.hasReferencedList() != prop.hasReferencedList()) { if (getPropertyInstanceCount(org.getId()) == 0) { ps = con.prepareStatement("UPDATE " + TBL_STRUCT_PROPERTIES + " SET REFLIST=? WHERE ID=?"); ps.setLong(2, prop.getId()); if (prop.hasReferencedList()) { ps.setLong(1, prop.getReferencedList().getId()); } else ps.setNull(1, java.sql.Types.NUMERIC); ps.executeUpdate(); ps.close(); if (changes) changesDesc.append(','); changesDesc.append("referencedList=").append(prop.getReferencedList()); changes = true; } else throw new FxUpdateException("ex.structure.modification.contentExists", "referencedList"); } // set the unique mode if (org.getUniqueMode() != prop.getUniqueMode()) { boolean allowChange = getPropertyInstanceCount(org.getId()) == 0 || prop.getUniqueMode().equals(UniqueMode.None); if (!allowChange) { boolean hasFlat = false; for (FxPropertyAssignment pa : env.getPropertyAssignments(prop.getId(), true)) { if (pa.isFlatStorageEntry()) { hasFlat = true; break; } } if (!hasFlat) { boolean check = true; for (FxType type : env.getTypesForProperty(prop.getId())) { check = StorageManager.getContentStorage(TypeStorageMode.Hierarchical) .uniqueConditionValid(con, prop.getUniqueMode(), prop, type.getId(), null); if (!check) break; } allowChange = check; } } if (allowChange) { ps = con.prepareStatement( "UPDATE " + TBL_STRUCT_PROPERTIES + " SET UNIQUEMODE=? WHERE ID=?"); ps.setLong(1, prop.getUniqueMode().getId()); ps.setLong(2, prop.getId()); ps.executeUpdate(); ps.close(); if (changes) changesDesc.append(','); changesDesc.append("uniqueMode=").append(prop.getUniqueMode().getId()); changes = true; } else throw new FxUpdateException("ex.structure.modification.contentExists", "uniqueMode"); } // change the property's ACL if (org.getACL().getId() != prop.getACL().getId()) { ps = con.prepareStatement("UPDATE " + TBL_STRUCT_PROPERTIES + " SET ACL=? WHERE ID=?"); ps.setLong(1, prop.getACL().getId()); ps.setLong(2, prop.getId()); ps.executeUpdate(); ps.close(); if (changes) changesDesc.append(','); changesDesc.append("acl=").append(prop.getACL().getId()); changes = true; } // change the prop's label if (org.getLabel() != null && !org.getLabel().equals(prop.getLabel()) || org.getLabel() == null && prop.getLabel() != null || org.getHint() != null && !org.getHint().equals(prop.getHint()) || org.getHint() == null && prop.getHint() != null) { Database.storeFxString(new FxString[] { prop.getLabel(), prop.getHint() }, con, TBL_STRUCT_PROPERTIES, new String[] { "DESCRIPTION", "HINT" }, "ID", prop.getId()); if (changes) changesDesc.append(','); changesDesc.append("label=").append(prop.getLabel()).append(','); changesDesc.append("hint=").append(prop.getHint()).append(','); changes = true; } // change the default value if (org.getDefaultValue() != null && !org.getDefaultValue().equals(prop.getDefaultValue()) || org.getDefaultValue() == null && prop.getDefaultValue() != null) { if (changes) changesDesc.append(','); ps = con.prepareStatement( "UPDATE " + TBL_STRUCT_PROPERTIES + " SET DEFAULT_VALUE=? WHERE ID=?"); FxValue defValue = prop.getDefaultValue(); if (defValue instanceof FxBinary) { ContentStorage storage = StorageManager.getContentStorage(TypeStorageMode.Hierarchical); storage.prepareBinary(con, (FxBinary) defValue); } if (prop.isDefaultValueSet() && (defValue instanceof FxReference)) { //check if the type matches the instance checkReferencedType(con, (FxReference) defValue, prop.getReferencedType()); //check for referencing assignments final List<FxPropertyAssignment> refAssignments = env .getReferencingPropertyAssignments(prop.getId()); for (FxPropertyAssignment refAssignment : refAssignments) { if (refAssignment.hasAssignmentDefaultValue() && refAssignment.getDefaultValue() instanceof FxReference) checkReferencedType(con, (FxReference) refAssignment.getDefaultValue(), prop.getReferencedType()); } } final String _def = defValue == null || defValue.isEmpty() ? null : ConversionEngine.getXStream().toXML(defValue); if (_def == null) ps.setNull(1, java.sql.Types.VARCHAR); else ps.setString(1, _def); ps.setLong(2, prop.getId()); ps.executeUpdate(); ps.close(); changesDesc.append("defaultValue=").append(prop.getDefaultValue()); changes = true; } //update SystemInternal flag, this is a one way function, so it can only be set, but not reset!! if (!org.isSystemInternal() && prop.isSystemInternal()) { if (FxContext.getUserTicket().isGlobalSupervisor()) { ps = con.prepareStatement( "UPDATE " + TBL_STRUCT_PROPERTIES + " SET SYSINTERNAL=? WHERE ID=?"); ps.setBoolean(1, prop.isSystemInternal()); ps.setLong(2, prop.getId()); ps.executeUpdate(); ps.close(); if (changes) changesDesc.append(','); changesDesc.append("systemInternal=").append(prop.isSystemInternal()); changes = true; } else throw new FxUpdateException("ex.structure.modification.systemInternal.notGlobalSupervisor", prop.getName()); } if (org.isMultiLang() != prop.isMultiLang()) { if (getPropertyInstanceCount(org.getId()) > 0) throw new FxUpdateException("ex.structure.modification.contentExists", "multiLang"); } } if (updatePropertyOptions(con, prop)) { changesDesc.append(",options:"); List<FxStructureOption> options = prop.getOptions(); for (FxStructureOption option : options) { changesDesc.append(option.getKey()).append("=").append(option.getValue()) .append(" overridable=").append(option.isOverridable()).append(" isSet=") .append(option.isSet()); } changes = true; } if (changes) htracker.track("history.assignment.updateProperty", prop.getName(), prop.getId(), changesDesc.toString()); success = true; } catch (SQLException e) { EJBUtils.rollback(ctx); /*TODO: Determine if this must be checked if (Database.isUniqueConstraintViolation(e)) throw new FxEntryExistsException("ex.structure.assignment.property.exists", prop.getAlias(), prop.getXPath()); */ throw new FxCreateException(LOG, e, "ex.db.sqlError", e.getMessage()); } finally { Database.closeObjects(AssignmentEngineBean.class, null, ps); if (!success) { EJBUtils.rollback(ctx); } } return changes; }
From source file:com.flexive.ejb.beans.structure.TypeEngineBean.java
/** * Update an existing type// w ww. j a va 2 s. c o m * * @param type the type to update * @return id of the type * @throws FxApplicationException on errors */ private long update(FxTypeEdit type) throws FxApplicationException { if (!type.isChanged()) return type.getId(); final UserTicket ticket = FxContext.getUserTicket(); FxPermissionUtils.checkRole(ticket, Role.StructureManagement); final FxEnvironment environment = CacheAdmin.getEnvironment(); if (StringUtils.isEmpty(type.getName())) throw new FxInvalidParameterException("NAME", "ex.structure.update.nameMissing"); //security checks start if (!ticket.mayEditACL(type.getACL().getId(), 0)) throw new FxNoAccessException("ex.acl.noAccess.edit", type.getACL().getName()); //security checks end boolean needReload = false; //full reload only needed if assignments have changed Connection con = null; PreparedStatement ps = null; FxType orgType = environment.getType(type.getId()); StringBuilder sql = new StringBuilder(500); try { con = Database.getDbConnection(); long instanceCount = -1; //number of instances //start name change if (!orgType.getName().equals(type.getName())) { sql.setLength(0); sql.append("UPDATE ").append(TBL_STRUCT_TYPES).append(" SET NAME=? WHERE ID=?"); if (ps != null) ps.close(); ps = con.prepareStatement(sql.toString()); ps.setString(1, type.getName()); ps.setLong(2, type.getId()); ps.executeUpdate(); //update all xpaths affected ps.close(); sql.setLength(0); sql.append("UPDATE ").append(TBL_STRUCT_ASSIGNMENTS) .append(" SET XPATH=REPLACE(XPATH, ?, ?) WHERE TYPEDEF=? AND ") .append(StorageManager.getRegExpLikeOperator("XPATH", "?")); ps = con.prepareStatement(sql.toString()); ps.setString(1, orgType.getName() + "/"); ps.setString(2, type.getName() + "/"); ps.setLong(3, type.getId()); ps.setString(4, "^" + orgType.getName() + "/"); int changed = ps.executeUpdate(); if (changed > 0) needReload = true; htracker.track(orgType, "history.type.update.name", orgType.getName(), type.getName(), type.getId(), changed); } //end name change //start description change if (!orgType.getLabel().equals(type.getLabel())) { Database.storeFxString(type.getLabel(), con, TBL_STRUCT_TYPES, "DESCRIPTION", "ID", type.getId()); htracker.track(orgType, "history.type.update.description", orgType.getLabel(), type.getLabel()); } //end description change //start type mode changes if (type.getMode().getId() != orgType.getMode().getId()) { if (instanceCount < 0) instanceCount = getInstanceCount(con, type.getId()); //allow relation => content (removing all relation specific entries) but content => relation requires 0 instances! if ((type.getMode() == TypeMode.Relation && orgType.getMode() == TypeMode.Content && instanceCount > 0) || orgType.getMode() == TypeMode.Preload || type.getMode() == TypeMode.Preload) throw new FxUpdateException("ex.structure.type.typeMode.notUpgradeable", orgType.getMode(), type.getMode(), orgType.getName()); if (type.getMode() == TypeMode.Content) { if (type.getRelations().size() > 0) { //TODO: remove relation mappings throw new FxUpdateException("ex.notImplemented", "Remove all relation mappings for type"); } if (instanceCount > 0) { //TODO: remove all relation specific entries for existing contents throw new FxUpdateException("ex.notImplemented", "Remove all relation specific entries for existing contents"); } } sql.setLength(0); sql.append("UPDATE ").append(TBL_STRUCT_TYPES).append(" SET TYPE_MODE=? WHERE ID=?"); if (ps != null) ps.close(); ps = con.prepareStatement(sql.toString()); ps.setLong(1, type.getMode().getId()); ps.setLong(2, type.getId()); ps.executeUpdate(); htracker.track(type, "history.type.update.typeMode", orgType.getMode(), type.getMode()); } //end type mode changes //start relation changes if (type.getAddedRelations().size() > 0) { sql.setLength(0); sql.append("INSERT INTO ").append(TBL_STRUCT_TYPERELATIONS) .append(" (TYPEDEF,TYPESRC,TYPEDST,MAXSRC,MAXDST)VALUES(?,?,?,?,?)"); if (ps != null) ps.close(); ps = con.prepareStatement(sql.toString()); ps.setLong(1, type.getId()); for (FxTypeRelation rel : type.getAddedRelations()) { if (rel.getSource().isRelation()) throw new FxInvalidParameterException("ex.structure.type.relation.wrongTarget", type.getName(), rel.getSource().getName()); if (rel.getDestination().isRelation()) throw new FxInvalidParameterException("ex.structure.type.relation.wrongTarget", type.getName(), rel.getDestination().getName()); ps.setLong(2, rel.getSource().getId()); ps.setLong(3, rel.getDestination().getId()); ps.setLong(4, rel.getMaxSource()); ps.setLong(5, rel.getMaxDestination()); ps.addBatch(); htracker.track(type, "history.type.update.relation.add", type.getName(), rel.getSource().getName(), rel.getMaxSource(), rel.getDestination().getName(), rel.getMaxDestination()); } ps.executeBatch(); } if (type.getUpdatedRelations().size() > 0) { sql.setLength(0); sql.append("UPDATE ").append(TBL_STRUCT_TYPERELATIONS). // 1 2 3 4 5 append(" SET MAXSRC=?,MAXDST=? WHERE TYPEDEF=? AND TYPESRC=? AND TYPEDST=?"); if (ps != null) ps.close(); ps = con.prepareStatement(sql.toString()); ps.setLong(3, type.getId()); for (FxTypeRelation rel : type.getUpdatedRelations()) { if (rel.getSource().isRelation()) throw new FxInvalidParameterException("ex.structure.type.relation.wrongTarget", type.getName(), rel.getSource().getName()); if (rel.getDestination().isRelation()) throw new FxInvalidParameterException("ex.structure.type.relation.wrongTarget", type.getName(), rel.getDestination().getName()); //TODO: check if maxSource/maxDestination is not violated if > 0 ps.setLong(4, rel.getSource().getId()); ps.setLong(5, rel.getDestination().getId()); ps.setLong(1, rel.getMaxSource()); ps.setLong(2, rel.getMaxDestination()); ps.addBatch(); htracker.track(type, "history.type.update.relation.update", type.getName(), rel.getSource().getName(), rel.getMaxSource(), rel.getDestination().getName(), rel.getMaxDestination()); } ps.executeBatch(); } if (type.getRemovedRelations().size() > 0) { sql.setLength(0); sql.append("DELETE FROM ").append(TBL_STRUCT_TYPERELATIONS). // 1 2 3 append(" WHERE TYPEDEF=? AND TYPESRC=? AND TYPEDST=?"); if (ps != null) ps.close(); ps = con.prepareStatement(sql.toString()); ps.setLong(1, type.getId()); for (FxTypeRelation rel : type.getRemovedRelations()) { if (rel.getSource().isRelation()) throw new FxInvalidParameterException("ex.structure.type.relation.wrongTarget", type.getName(), rel.getSource().getName()); if (rel.getDestination().isRelation()) throw new FxInvalidParameterException("ex.structure.type.relation.wrongTarget", type.getName(), rel.getDestination().getName()); int[] rels = getRelationCount(con, type.getId(), rel.getSource().getId(), rel.getDestination().getId()); if (!type.isRemoveInstancesWithRelationTypes() && rels[0] > 0) throw new FxRemoveException("ex.structure.type.relation.relationsExist", type.getName(), rel.getSource().getName(), rel.getDestination().getName(), rels[0]); else if (type.isRemoveInstancesWithRelationTypes() && rels[0] > 0) { removeRelationEntries(con, type.getId(), rel.getSource().getId(), rel.getDestination().getId()); } ps.setLong(2, rel.getSource().getId()); ps.setLong(3, rel.getDestination().getId()); ps.addBatch(); htracker.track(type, "history.type.update.relation.remove", type.getName(), rel.getSource().getName(), rel.getMaxSource(), rel.getDestination().getName(), rel.getMaxDestination()); } ps.executeBatch(); } //end relation changes //start ACL changes if (!type.getACL().equals(orgType.getACL())) { if (type.getACL().getCategory() != ACLCategory.STRUCTURE) throw new FxInvalidParameterException("ACL", "ex.acl.category.invalid", type.getACL().getCategory(), ACLCategory.STRUCTURE); sql.setLength(0); sql.append("UPDATE ").append(TBL_STRUCT_TYPES).append(" SET ACL=? WHERE ID=?"); if (ps != null) ps.close(); ps = con.prepareStatement(sql.toString()); ps.setLong(1, type.getACL().getId()); ps.setLong(2, type.getId()); ps.executeUpdate(); htracker.track(type, "history.type.update.acl", orgType.getACL(), type.getACL()); } //end ACL changes //start default instance ACL changes if (type.hasDefaultInstanceACL() != orgType.hasDefaultInstanceACL() || !type.getDefaultInstanceACL().equals(orgType.getDefaultInstanceACL())) { if (type.hasDefaultInstanceACL() && type.getDefaultInstanceACL().getCategory() != ACLCategory.INSTANCE) throw new FxInvalidParameterException("DEFACL", "ex.acl.category.invalid", type.getDefaultInstanceACL().getCategory(), ACLCategory.INSTANCE); sql.setLength(0); sql.append("UPDATE ").append(TBL_STRUCT_TYPES).append(" SET DEFACL=? WHERE ID=?"); if (ps != null) ps.close(); ps = con.prepareStatement(sql.toString()); if (type.hasDefaultInstanceACL()) ps.setLong(1, type.getDefaultInstanceACL().getId()); else ps.setNull(1, java.sql.Types.INTEGER); ps.setLong(2, type.getId()); ps.executeUpdate(); htracker.track(type, "history.type.update.defacl", orgType.hasDefaultInstanceACL() ? orgType.getACL() : "-", type.hasDefaultInstanceACL() ? type.getDefaultInstanceACL() : "-"); } //end default instance ACL changes //start Workflow changes if (!type.getWorkflow().equals(orgType.getWorkflow())) { if (instanceCount < 0) instanceCount = getInstanceCount(con, type.getId()); if (instanceCount > 0) { //Workflow can not be changed with existing instances -> there is no way to reliably //map steps of one workflow to steps of another (even not using stepdefinitions since they //can be used multiple times). A possible solution would be to provide a mapping when changing //workflows but this should be to seldom the case to bother implementing it throw new FxUpdateException("ex.notImplemented", "Workflow changes with existing instance"); } sql.setLength(0); sql.append("UPDATE ").append(TBL_STRUCT_TYPES).append(" SET WORKFLOW=? WHERE ID=?"); if (ps != null) ps.close(); ps = con.prepareStatement(sql.toString()); ps.setLong(1, type.getWorkflow().getId()); ps.setLong(2, type.getId()); ps.executeUpdate(); htracker.track(type, "history.type.update.workflow", orgType.getWorkflow(), type.getWorkflow()); } //end Workflow changes //start Category changes if (!type.getCategory().equals(orgType.getCategory())) { if (!ticket.isGlobalSupervisor()) throw new FxUpdateException("ex.structure.type.category.notSupervisor", orgType.getCategory(), type.getCategory(), orgType.getName()); sql.setLength(0); sql.append("UPDATE ").append(TBL_STRUCT_TYPES).append(" SET CATEGORY=? WHERE ID=?"); if (ps != null) ps.close(); ps = con.prepareStatement(sql.toString()); ps.setInt(1, type.getCategory().getId()); ps.setLong(2, type.getId()); ps.executeUpdate(); } //end Category changes //start language mode changes if (!type.getLanguage().equals(orgType.getLanguage())) { if (instanceCount < 0) instanceCount = getInstanceCount(con, type.getId()); if (instanceCount <= 0 || orgType.getLanguage().isUpgradeable(type.getLanguage())) { sql.setLength(0); sql.append("UPDATE ").append(TBL_STRUCT_TYPES).append(" SET LANG_MODE=? WHERE ID=?"); if (ps != null) ps.close(); ps = con.prepareStatement(sql.toString()); ps.setInt(1, type.getLanguage().getId()); ps.setLong(2, type.getId()); ps.executeUpdate(); htracker.track(type, "history.type.update.languageMode", orgType.getLanguage().name(), type.getLanguage().name()); } else throw new FxUpdateException("ex.structure.type.languageMode.notUpgradeable", orgType.getLanguage(), type.getLanguage(), orgType.getName()); } //end language mode changes //start state changes if (type.getState().getId() != orgType.getState().getId()) { sql.setLength(0); sql.append("UPDATE ").append(TBL_STRUCT_TYPES).append(" SET TYPE_STATE=? WHERE ID=?"); if (ps != null) ps.close(); ps = con.prepareStatement(sql.toString()); ps.setInt(1, type.getState().getId()); ps.setLong(2, type.getId()); ps.executeUpdate(); htracker.track(type, "history.type.update.state", orgType.getState().name(), type.getState().name()); } //end state changes //start permission changes if (type.getBitCodedPermissions() != orgType.getBitCodedPermissions()) { sql.setLength(0); sql.append("UPDATE ").append(TBL_STRUCT_TYPES).append(" SET SECURITY_MODE=? WHERE ID=?"); if (ps != null) ps.close(); ps = con.prepareStatement(sql.toString()); ps.setInt(1, type.getBitCodedPermissions()); ps.setLong(2, type.getId()); ps.executeUpdate(); htracker.track(type, "history.type.update.perm", FxPermissionUtils.toString(orgType.getBitCodedPermissions()), FxPermissionUtils.toString(type.getBitCodedPermissions())); } //end permission changes //start multiple ACL setting changes if (type.isMultipleContentACLs() != orgType.isMultipleContentACLs()) { sql.setLength(0); sql.append("UPDATE ").append(TBL_STRUCT_TYPES).append(" SET MULTIPLE_CONTENT_ACLS=? WHERE ID=?"); if (ps != null) ps.close(); ps = con.prepareStatement(sql.toString()); ps.setBoolean(1, type.isMultipleContentACLs()); ps.setLong(2, type.getId()); ps.executeUpdate(); htracker.track(type, "history.type.update.multipleContentACLs", orgType.isMultipleContentACLs(), type.isMultipleContentACLs()); } //end multiple ACL setting changes //start isIncludedInSupertypeQueries setting changes if (type.isIncludedInSupertypeQueries() != orgType.isIncludedInSupertypeQueries()) { sql.setLength(0); sql.append("UPDATE ").append(TBL_STRUCT_TYPES).append(" SET INSUPERTYPEQUERY=? WHERE ID=?"); if (ps != null) ps.close(); ps = con.prepareStatement(sql.toString()); ps.setBoolean(1, type.isIncludedInSupertypeQueries()); ps.setLong(2, type.getId()); ps.executeUpdate(); htracker.track(type, "history.type.update.inSupertypeQueries", orgType.isIncludedInSupertypeQueries(), type.isIncludedInSupertypeQueries()); } //end isIncludedInSupertypeQueries setting changes //start history track/age changes if (type.isTrackHistory() != orgType.isTrackHistory() || type.getHistoryAge() != orgType.getHistoryAge()) { sql.setLength(0); sql.append("UPDATE ").append(TBL_STRUCT_TYPES) .append(" SET TRACKHISTORY=?, HISTORY_AGE=? WHERE ID=?"); if (ps != null) ps.close(); ps = con.prepareStatement(sql.toString()); ps.setBoolean(1, type.isTrackHistory()); ps.setLong(2, type.getHistoryAge()); ps.setLong(3, type.getId()); ps.executeUpdate(); htracker.track(type, "history.type.update.history", orgType.isTrackHistory(), type.isTrackHistory(), orgType.getHistoryAge(), type.getHistoryAge()); } //end history track/age changes //start max.ver changes if (type.getMaxVersions() != orgType.getMaxVersions()) { //TODO: remove any versions that would exceed this count sql.setLength(0); sql.append("UPDATE ").append(TBL_STRUCT_TYPES).append(" SET MAX_VERSIONS=? WHERE ID=?"); if (ps != null) ps.close(); ps = con.prepareStatement(sql.toString()); ps.setLong(1, type.getMaxVersions()); ps.setLong(2, type.getId()); ps.executeUpdate(); htracker.track(type, "history.type.update.maxVersions", orgType.getMaxVersions(), type.getMaxVersions()); } //end max.ver changes //start isAutoVersion setting changes if (type.isAutoVersion() != orgType.isAutoVersion()) { sql.setLength(0); sql.append("UPDATE ").append(TBL_STRUCT_TYPES).append(" SET AUTO_VERSION=? WHERE ID=?"); if (ps != null) ps.close(); ps = con.prepareStatement(sql.toString()); ps.setBoolean(1, type.isAutoVersion()); ps.setLong(2, type.getId()); ps.executeUpdate(); htracker.track(type, "history.type.update.isAutoVersion", orgType.isAutoVersion(), type.isAutoVersion()); } //end isAutoVersion setting changes //start max source relations changes if (type.isRelation() && type.getMaxRelSource() != orgType.getMaxRelSource()) { //TODO: check if the new condition is not violated by existing data sql.setLength(0); sql.append("UPDATE ").append(TBL_STRUCT_TYPES).append(" SET REL_TOTAL_MAXSRC=? WHERE ID=?"); if (ps != null) ps.close(); ps = con.prepareStatement(sql.toString()); ps.setLong(1, type.getMaxRelSource()); ps.setLong(2, type.getId()); ps.executeUpdate(); htracker.track(type, "history.type.update.maxRelSource", orgType.getMaxRelSource(), type.getMaxRelSource()); } //end max source relations changes //start max destination relations changes if (type.isRelation() && type.getMaxRelDestination() != orgType.getMaxRelDestination()) { //TODO: check if the new condition is not violated by existing data sql.setLength(0); sql.append("UPDATE ").append(TBL_STRUCT_TYPES).append(" SET REL_TOTAL_MAXDST=? WHERE ID=?"); if (ps != null) ps.close(); ps = con.prepareStatement(sql.toString()); ps.setLong(1, type.getMaxRelDestination()); ps.setLong(2, type.getId()); ps.executeUpdate(); htracker.track(type, "history.type.update.maxRelDest", orgType.getMaxRelDestination(), type.getMaxRelDestination()); } //end max destination relations changes //start icon if (!type.getIcon().equals(orgType.getIcon())) { sql.setLength(0); sql.append("UPDATE ").append(TBL_STRUCT_TYPES).append(" SET ICON_REF=? WHERE ID=?"); if (ps != null) ps.close(); ps = con.prepareStatement(sql.toString()); if (type.getIcon().isEmpty()) ps.setNull(1, java.sql.Types.INTEGER); else ps.setLong(1, type.getIcon().getDefaultTranslation().getId()); ps.setLong(2, type.getId()); ps.executeUpdate(); needReload = true; htracker.track(type, "history.type.update.icon", orgType.getIcon().getDefaultTranslation().getId(), type.getIcon().getDefaultTranslation().getId()); } //end icon // structure option changes boolean optionsChanged = updateTypeOptions(con, type, orgType); // check if any type options must be propagated to derived types if (type.getDerivedTypes().size() > 0) { final List<FxStructureOption> inherit = new ArrayList<FxStructureOption>(type.getOptions().size()); for (FxStructureOption o : type.getOptions()) { if (o.getIsInherited()) { inherit.add(o); } } if (inherit.size() > 0) { for (FxType derived : type.getDerivedTypes()) { updateDerivedTypeOptions(con, derived, inherit); } } } //sync back to cache try { if (needReload) StructureLoader.reload(con); else { StructureLoader.updateType(FxContext.get().getDivisionId(), loadType(con, type.getId())); // reload any derived types if type options have changed if (optionsChanged && type.getDerivedTypes().size() > 0) { for (FxType derivedType : type.getDerivedTypes()) { StructureLoader.updateType(FxContext.get().getDivisionId(), loadType(con, derivedType.getId())); } } } } catch (FxLoadException e) { throw new FxUpdateException(e); } catch (FxCacheException e) { LOG.fatal(e.getMessage(), e); } } catch (SQLException e) { EJBUtils.rollback(ctx); throw new FxUpdateException(LOG, e, "ex.db.sqlError", e.getMessage()); } finally { Database.closeObjects(TypeEngineBean.class, con, ps); } return type.getId(); }
From source file:com.portfolio.data.provider.MysqlDataProvider.java
@Override public String postAddNodeType(int userId, Integer type, Integer nodeid, Integer parentid, Integer instance, String data) {/* w ww. ja va 2 s .c o m*/ if (!credential.isAdmin(userId)) throw new RestWebApplicationException(Status.FORBIDDEN, "No admin right"); /** * Format que l'on reoit: * <asm*> * <asmResource xsi_type='nodeRes'>{node_data}</asmResource> * <asmResource xsi_type='context'>{node_data}</asmResource> * <asmResource xsi_type='*'>{node_data}</asmResource> * </asm*> */ String sql = ""; PreparedStatement st; Integer output = 0; Integer parentId = 0; String asmtype = ""; String xsitype = ""; try { /// Prpare les donnes pour les requtes // Parse DocumentBuilderFactory documentBuilderFactory = DocumentBuilderFactory.newInstance(); DocumentBuilder documentBuilder = documentBuilderFactory.newDocumentBuilder(); Document document = documentBuilder.parse(new ByteArrayInputStream(data.getBytes("UTF-8"))); // Traite le noeud racine des donnes, retourne l'identifiant du noeud racine Element nodeData = document.getDocumentElement(); asmtype = nodeData.getNodeName(); connection.setAutoCommit(true); // Utilise parentid si on rattache un autre groupe de noeud en dessous d'un noeud existant sql = "INSERT INTO definition_type(def_id,asm_type,parent_node,instance_rule) " + "VALUE(?,?,?,?)"; st = connection.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); if (dbserveur.equals("oracle")) { st = connection.prepareStatement(sql, new String[] { "node_id" }); } st.setInt(1, type); st.setString(2, asmtype); if (parentid == null) st.setNull(3, Types.BIGINT); else st.setInt(3, parentid); if (instance == null) st.setNull(4, Types.BIGINT); else st.setInt(4, instance); output = st.executeUpdate(); ResultSet key = st.getGeneratedKeys(); // On rcure l'identifiant du noeud 'racine' des donnes ajouts if (key.next()) parentId = key.getInt(1); st.close(); // Soit 2 ou 3 resources asmtype = "asmResource"; NodeList resources = document.getElementsByTagName("asmResource"); sql = "INSERT INTO definition_type(def_id,asm_type,xsi_type,parent_node,node_data,instance_rule) " + "VALUE(?,?,?,?,?,?)"; st = connection.prepareStatement(sql); st.setInt(1, type); st.setString(2, asmtype); st.setInt(4, parentId); for (int i = 0; i < resources.getLength(); ++i) { Element resource = (Element) resources.item(i); xsitype = resource.getAttribute("xsi_type"); String resContent = DomUtils.getInnerXml(resource); st.setString(3, xsitype); st.setString(5, resContent); if (instance == null) st.setNull(6, Types.BIGINT); else st.setInt(6, instance); // On ajoute les donnes des ressources restante output = st.executeUpdate(); } st.close(); } catch (Exception e) { try { connection.rollback(); } catch (SQLException e1) { e1.printStackTrace(); } e.printStackTrace(); } finally { try { connection.setAutoCommit(true); connection.close(); } catch (SQLException e) { e.printStackTrace(); } } return output.toString(); }
From source file:ca.sqlpower.matchmaker.address.AddressPool.java
/** * Inserts and updates the contents of the result table with the * {@link AddressResult} contents in this {@link AddressPool}. * AddressResults that are marked as {@link StorageState#DIRTY} are assumed * to be already in the database and are updated. AddressResults that are * marked as {@link StorageState#NEW} are assumed to be new entries that do * no yet exist in the database and are inserted. * //from w w w. ja va 2 s .c o m * It is worth noting that at the moment, new Address results won't have an * output street number yet (since they have not been validated yet) but a * {@link NullPointerException} gets thrown if we try to insert a null * Integer, so for the time being, I've set the 'null' steet number to be * -1, since I don't believe there's anyone with a negative street number, * but if I'm wrong, this will have to be changed. * * @throws SQLException * @throws SQLObjectException */ public void store(Logger engineLogger, boolean useBatchExecute, boolean debug) throws SQLException, SQLObjectException { setStarted(true); setFinished(false); setCancelled(false); setProgress(0); List<AddressResult> dirtyAddresses = new ArrayList<AddressResult>(); List<AddressResult> deleteAddresses = new ArrayList<AddressResult>(); List<AddressResult> newAddresses = new ArrayList<AddressResult>(); for (List<Object> key : addresses.keySet()) { AddressResult result = addresses.get(key); if (result.getStorageState() == StorageState.DELETE) { deleteAddresses.add(result); } else if (result.getStorageState() == StorageState.DIRTY) { dirtyAddresses.add(result); } else if (result.getStorageState() == StorageState.NEW) { newAddresses.add(result); } } setJobSize(deleteAddresses.size() + dirtyAddresses.size() + newAddresses.size()); engineLogger.debug("# of Delete Address Records:" + deleteAddresses.size()); engineLogger.debug("# of Dirty Address Records:" + dirtyAddresses.size()); engineLogger.debug("# of New Address Records:" + newAddresses.size()); Connection con = null; PreparedStatement ps = null; Statement stmt = null; StringBuilder sql = null; AddressResult result = null; try { con = project.createResultTableConnection(); con.setAutoCommit(false); boolean useBatchUpdates = useBatchExecute && con.getMetaData().supportsBatchUpdates(); SQLTable resultTable = project.getResultTable(); int keySize = project.getSourceTableIndex().getChildCount(); if (deleteAddresses.size() > 0) { stmt = con.createStatement(); for (AddressResult currentResult : deleteAddresses) { sql = new StringBuilder("DELETE FROM "); appendFullyQualifiedTableName(sql, resultTable); sql.append(" WHERE "); int j = 0; for (Object keyValue : currentResult.getKeyValues()) { if (j > 0) { sql.append("AND "); } if (keyValue == null) { sql.append(SOURCE_ADDRESS_KEY_COLUMN_BASE).append(j).append(" is null "); } else if (keyValue instanceof String || keyValue instanceof Character) { sql.append(SOURCE_ADDRESS_KEY_COLUMN_BASE).append(j) .append("=" + SQL.quote(keyValue.toString()) + " "); } else { sql.append(SOURCE_ADDRESS_KEY_COLUMN_BASE).append(j).append("=" + keyValue + " "); } j++; } engineLogger.debug("Preparing the following address result to be deleted: " + currentResult); engineLogger.debug("Executing statement " + sql); stmt.execute(sql.toString()); incrementProgress(); } if (stmt != null) stmt.close(); stmt = null; } Map<String, Integer> columnMetaData = this.getColumnMetaData(engineLogger, resultTable); /* For backward compatibility, see if old column names are being used. * NOTE: the database may return column names as upper case. */ boolean usingNewNames = true; if (columnMetaData.containsKey(OLD_OUTPUT_DELIVERY_INSTALLATION_NAME)) { usingNewNames = false; } engineLogger.debug("Using new shorter names? " + usingNewNames); if (dirtyAddresses.size() > 0) { //First, create and UPDATE PreparedStatement to update dirty records sql = new StringBuilder(); sql.append("UPDATE "); appendFullyQualifiedTableName(sql, resultTable); sql.append(" SET "); sql.append(INPUT_ADDRESS_LINE1).append("=?, "); // 1 sql.append(INPUT_ADDRESS_LINE2).append("=?, "); // 2 sql.append(INPUT_MUNICIPALITY).append("=?, "); // 3 sql.append(INPUT_PROVINCE).append("=?, "); // 4 sql.append(INPUT_COUNTRY).append("=?, "); // 5 sql.append(INPUT_POSTAL_CODE).append("=?, "); // 6 sql.append(OUTPUT_COUNTRY).append("=?, "); // 7 if (usingNewNames) { sql.append(OUTPUT_DELIVERY_INSTALLATION_NAME).append("=?, "); // 8 sql.append(OUTPUT_DELIVERY_INSTALLATION_TYPE).append("=?, "); // 9 } else { sql.append(OLD_OUTPUT_DELIVERY_INSTALLATION_NAME).append("=?, "); // 8 sql.append(OLD_OUTPUT_DELIVERY_INSTALLATION_TYPE).append("=?, "); // 9 } sql.append(OUTPUT_DIRECTION_PREFIX).append("=?, "); // 10 sql.append(OUTPUT_FAILED_PARSING_STRING).append("=?, "); // 11 sql.append(OUTPUT_GENERAL_DELIVERY_NAME).append("=?, "); // 12 sql.append(OUTPUT_LOCK_BOX_NUMBER).append("=?, "); // 13 sql.append(OUTPUT_LOCK_BOX_TYPE).append("=?, "); // 14 sql.append(OUTPUT_MUNICIPALITY).append("=?, "); // 15 sql.append(OUTPUT_POSTAL_CODE).append("=?, "); // 16 sql.append(OUTPUT_PROVINCE).append("=?, "); // 17 sql.append(OUTPUT_RURAL_ROUTE_NUMBER).append("=?, "); // 18 sql.append(OUTPUT_RURAL_ROUTE_TYPE).append("=?, "); // 19 sql.append(OUTPUT_STREET_DIRECTION).append("=?, "); // 20 sql.append(OUTPUT_STREET_NAME).append("=?, "); // 21 sql.append(OUTPUT_STREET_NUMBER).append("=?, "); // 22 sql.append(OUTPUT_STREET_NUMBER_SUFFIX).append("=?, "); // 23 if (usingNewNames) { sql.append(OUTPUT_STREET_NUMBER_SUFFIX_SEPARATE).append("=?, "); // 23.5 } else { sql.append(OLD_OUTPUT_STREET_NUMBER_SUFFIX_SEPARATE).append("=?, "); // 23.5 } sql.append(OUTPUT_STREET_TYPE).append("=?, "); // 24 sql.append(OUTPUT_STREET_TYPE_PREFIX).append("=?, "); // 25 sql.append(OUTPUT_SUITE).append("=?, "); // 26 sql.append(OUTPUT_SUITE_PREFIX).append("=?, "); // 27 sql.append(OUTPUT_SUITE_TYPE).append("=?, "); // 28 sql.append(OUTPUT_TYPE).append("=?, "); // 29 sql.append(OUTPUT_UNPARSED_ADDRESS).append("=?, "); // 30 sql.append(OUTPUT_URBAN_BEFORE_RURAL).append("=?, "); // 31 sql.append(OUTPUT_VALID).append("=? "); // 32 sql.append("WHERE "); String baseStatement = sql.toString(); int batchCount = 0; for (int i = 0; i < dirtyAddresses.size(); i++) { sql = new StringBuilder(baseStatement); result = dirtyAddresses.get(i); int j = 0; // I really wish there was a better way to handle this, // but unfortunately in SQL, <column> = null and <column> is // null are not the same thing, and you usually want 'is // null' Why they couldn't just use '= null' is beyond me. // Otherwise, we could just use a single prepared statement // for all the records. The main reason we had to switch // back to using prepared statements is because different RDBMS // platforms handle Booleans differently (some support // boolean explicitly, others use an integer (1 or 0) for (Object keyValue : result.getKeyValues()) { if (j > 0) { sql.append("AND "); } if (keyValue == null) { sql.append(SOURCE_ADDRESS_KEY_COLUMN_BASE).append(j).append(" is null "); // 18+ } else if (keyValue instanceof String || keyValue instanceof Character) { sql.append(SOURCE_ADDRESS_KEY_COLUMN_BASE).append(j) .append("=" + SQL.quote(keyValue.toString()) + " "); // 18+ } else { sql.append(SOURCE_ADDRESS_KEY_COLUMN_BASE).append(j).append("=" + keyValue + " "); // 18+ } j++; } ps = con.prepareStatement(sql.toString()); Address inputAddress = result.getInputAddress(); this.adjustInputAddress(inputAddress, columnMetaData); engineLogger.debug( "Setting input unparsed address line 1 to " + inputAddress.getUnparsedAddressLine1()); ps.setString(1, inputAddress.getUnparsedAddressLine1()); engineLogger.debug( "Setting input unparsed address line 2 to " + inputAddress.getUnparsedAddressLine2()); ps.setString(2, inputAddress.getUnparsedAddressLine2()); engineLogger.debug("Setting input municipality to " + inputAddress.getMunicipality()); ps.setString(3, inputAddress.getMunicipality()); engineLogger.debug("Setting input province to " + inputAddress.getProvince()); ps.setString(4, inputAddress.getProvince()); engineLogger.debug("Setting input country to " + inputAddress.getCountry()); ps.setString(5, inputAddress.getCountry()); engineLogger.debug("Setting input postal code to " + inputAddress.getPostalCode()); ps.setString(6, inputAddress.getPostalCode()); Address outputAddress = result.getOutputAddress(); this.adjustOutputAddress(outputAddress, columnMetaData, usingNewNames); engineLogger.debug("Setting output suite to " + outputAddress.getSuite()); ps.setString(7, outputAddress.getSuite()); engineLogger.debug("Setting output delivery installation name to " + outputAddress.getDeliveryInstallationName()); ps.setString(8, outputAddress.getDeliveryInstallationName()); engineLogger.debug("Setting output delivery nstallation type to " + outputAddress.getDeliveryInstallationType()); ps.setString(9, outputAddress.getDeliveryInstallationType()); engineLogger.debug("Setting output direction prefix to " + outputAddress.isDirectionPrefix()); ps.setBoolean(10, outputAddress.isDirectionPrefix()); engineLogger.debug( "Setting output failed parsing string to " + outputAddress.getFailedParsingString()); ps.setString(11, outputAddress.getFailedParsingString()); engineLogger.debug( "Setting output general delivery name to " + outputAddress.getGeneralDeliveryName()); ps.setString(12, outputAddress.getGeneralDeliveryName()); engineLogger.debug("Setting output lock box number to " + outputAddress.getLockBoxNumber()); ps.setString(13, outputAddress.getLockBoxNumber()); engineLogger.debug("Setting output lock box type to " + outputAddress.getLockBoxType()); ps.setString(14, outputAddress.getLockBoxType()); engineLogger.debug("Setting output municipality to " + outputAddress.getMunicipality()); ps.setString(15, outputAddress.getMunicipality()); engineLogger.debug("Setting output postal code to " + outputAddress.getPostalCode()); ps.setString(16, outputAddress.getPostalCode()); engineLogger.debug("Setting output province to " + outputAddress.getProvince()); ps.setString(17, outputAddress.getProvince()); engineLogger .debug("Setting output rural route number to " + outputAddress.getRuralRouteNumber()); ps.setString(18, outputAddress.getRuralRouteNumber()); engineLogger.debug("Setting output rural route type to " + outputAddress.getRuralRouteType()); ps.setString(19, outputAddress.getRuralRouteType()); engineLogger.debug("Setting output street direciton to " + outputAddress.getStreetDirection()); ps.setString(20, outputAddress.getStreetDirection()); engineLogger.debug("Setting output street to " + outputAddress.getStreet()); ps.setString(21, outputAddress.getStreet()); engineLogger.debug("Setting output street number to " + outputAddress.getStreetNumber()); Integer streetNumber = outputAddress.getStreetNumber(); if (streetNumber == null) { ps.setNull(22, Types.INTEGER); } else { ps.setInt(22, streetNumber); } engineLogger.debug( "Setting output street number suffix to " + outputAddress.getStreetNumberSuffix()); ps.setString(23, outputAddress.getStreetNumberSuffix()); engineLogger.debug("Setting output street number suffix separate to " + outputAddress.isStreetNumberSuffixSeparate()); Boolean isStreetNumberSuffixSeparate = outputAddress.isStreetNumberSuffixSeparate(); if (isStreetNumberSuffixSeparate == null) { ps.setNull(24, Types.BOOLEAN); } else { ps.setBoolean(24, outputAddress.isStreetNumberSuffixSeparate()); } engineLogger.debug("Setting output suite to " + outputAddress.getSuite()); ps.setString(25, outputAddress.getStreetType()); engineLogger.debug("Setting output streetTypePrefix to " + outputAddress.isStreetTypePrefix()); ps.setBoolean(26, outputAddress.isStreetTypePrefix()); engineLogger.debug("Setting output suite to " + outputAddress.getSuite()); ps.setString(27, outputAddress.getSuite()); engineLogger.debug("Setting output suitePrefix to " + outputAddress.isSuitePrefix()); ps.setBoolean(28, outputAddress.isSuitePrefix()); engineLogger.debug("Setting output suiteType to " + outputAddress.getSuiteType()); ps.setString(29, outputAddress.getSuiteType()); engineLogger.debug("Setting output type to " + outputAddress.getType()); RecordType type = outputAddress.getType(); ps.setString(30, type == null ? null : type.toString()); engineLogger.debug( "Setting output unparsedAddressLine1 to " + outputAddress.getUnparsedAddressLine1()); ps.setString(31, outputAddress.getUnparsedAddressLine1()); engineLogger.debug("Setting output urbanBeforeRural to " + outputAddress.isUrbanBeforeRural()); Boolean urbanBeforeRural = outputAddress.isUrbanBeforeRural(); if (urbanBeforeRural == null) { ps.setNull(32, Types.BOOLEAN); } else { ps.setBoolean(32, outputAddress.isUrbanBeforeRural()); } engineLogger.debug("Setting valid to " + result.isValid()); ps.setBoolean(33, result.isValid()); if (useBatchUpdates) { engineLogger.debug("Adding to batch"); ps.addBatch(); batchCount++; // TODO: The batchCount should be user setable if (batchCount > 1000) { engineLogger.debug("Executing batch"); ps.executeBatch(); batchCount = 0; } } else { engineLogger.debug("Executing statement"); ps.execute(); } incrementProgress(); } // Execute remaining batch statements if (batchCount > 0 && useBatchUpdates) { ps.executeBatch(); } if (ps != null) ps.close(); ps = null; } if (newAddresses.size() > 0) { //Next, let's meke an INSERT PreparedStatement to insert new records sql = new StringBuilder(); sql.append("INSERT INTO "); appendFullyQualifiedTableName(sql, resultTable); sql.append("("); for (int i = 0; i < keySize; i++) { sql.append(SOURCE_ADDRESS_KEY_COLUMN_BASE).append(i).append(", "); } sql.append(INPUT_ADDRESS_LINE1).append(", "); sql.append(INPUT_ADDRESS_LINE2).append(", "); sql.append(INPUT_MUNICIPALITY).append(", "); sql.append(INPUT_PROVINCE).append(", "); sql.append(INPUT_COUNTRY).append(", "); sql.append(INPUT_POSTAL_CODE).append(", "); sql.append(OUTPUT_COUNTRY).append(", "); if (usingNewNames) { sql.append(OUTPUT_DELIVERY_INSTALLATION_NAME).append(", "); sql.append(OUTPUT_DELIVERY_INSTALLATION_TYPE).append(", "); } else { sql.append(OLD_OUTPUT_DELIVERY_INSTALLATION_NAME).append(", "); sql.append(OLD_OUTPUT_DELIVERY_INSTALLATION_TYPE).append(", "); } sql.append(OUTPUT_DIRECTION_PREFIX).append(", "); sql.append(OUTPUT_FAILED_PARSING_STRING).append(", "); sql.append(OUTPUT_GENERAL_DELIVERY_NAME).append(", "); sql.append(OUTPUT_LOCK_BOX_NUMBER).append(", "); sql.append(OUTPUT_LOCK_BOX_TYPE).append(", "); sql.append(OUTPUT_MUNICIPALITY).append(", "); sql.append(OUTPUT_POSTAL_CODE).append(", "); sql.append(OUTPUT_PROVINCE).append(", "); sql.append(OUTPUT_RURAL_ROUTE_NUMBER).append(", "); sql.append(OUTPUT_RURAL_ROUTE_TYPE).append(", "); sql.append(OUTPUT_STREET_DIRECTION).append(", "); sql.append(OUTPUT_STREET_NAME).append(", "); sql.append(OUTPUT_STREET_NUMBER).append(", "); sql.append(OUTPUT_STREET_NUMBER_SUFFIX).append(", "); if (usingNewNames) { sql.append(OUTPUT_STREET_NUMBER_SUFFIX_SEPARATE).append(", "); } else { sql.append(OLD_OUTPUT_STREET_NUMBER_SUFFIX_SEPARATE).append(", "); } sql.append(OUTPUT_STREET_TYPE).append(", "); sql.append(OUTPUT_STREET_TYPE_PREFIX).append(", "); sql.append(OUTPUT_SUITE).append(", "); sql.append(OUTPUT_SUITE_PREFIX).append(", "); sql.append(OUTPUT_SUITE_TYPE).append(", "); sql.append(OUTPUT_TYPE).append(", "); sql.append(OUTPUT_UNPARSED_ADDRESS).append(", "); sql.append(OUTPUT_URBAN_BEFORE_RURAL).append(", "); sql.append(OUTPUT_VALID).append(")"); sql.append("VALUES("); for (int i = 0; i < keySize; i++) { sql.append("?, "); } sql.append( "?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"); ps = con.prepareStatement(sql.toString()); int batchCount = 0; for (int i = 0; i < newAddresses.size(); i++) { result = newAddresses.get(i); int j = 1; for (Object keyValue : result.getKeyValues()) { ps.setObject(j, keyValue); j++; engineLogger.debug("Setting key value " + j + " to " + keyValue); } Address inputAddress = result.getInputAddress(); this.adjustInputAddress(inputAddress, columnMetaData); engineLogger.debug( "Setting input unparsed address line 1 to " + inputAddress.getUnparsedAddressLine1()); ps.setString(j, inputAddress.getUnparsedAddressLine1()); engineLogger.debug( "Setting input unparsed address line 2 to " + inputAddress.getUnparsedAddressLine2()); ps.setString(j + 1, inputAddress.getUnparsedAddressLine2()); engineLogger.debug("Setting input municipality to " + inputAddress.getMunicipality()); ps.setString(j + 2, inputAddress.getMunicipality()); engineLogger.debug("Setting input province to " + inputAddress.getProvince()); ps.setString(j + 3, inputAddress.getProvince()); engineLogger.debug("Setting input country to " + inputAddress.getCountry()); ps.setString(j + 4, inputAddress.getCountry()); engineLogger.debug("Setting input postal code to " + inputAddress.getPostalCode()); ps.setString(j + 5, inputAddress.getPostalCode()); Address outputAddress = result.getOutputAddress(); this.adjustOutputAddress(outputAddress, columnMetaData, usingNewNames); engineLogger.debug("Setting output suite to " + outputAddress.getSuite()); ps.setString(j + 6, outputAddress.getSuite()); engineLogger.debug("Setting output delivery installation name to " + outputAddress.getDeliveryInstallationName()); ps.setString(j + 7, outputAddress.getDeliveryInstallationName()); engineLogger.debug("Setting output delivery nstallation type to " + outputAddress.getDeliveryInstallationType()); ps.setString(j + 8, outputAddress.getDeliveryInstallationType()); engineLogger.debug("Setting output direction prefix to " + outputAddress.isDirectionPrefix()); ps.setBoolean(j + 9, outputAddress.isDirectionPrefix()); engineLogger.debug( "Setting output failed parsing string to " + outputAddress.getFailedParsingString()); ps.setString(j + 10, outputAddress.getFailedParsingString()); engineLogger.debug( "Setting output general delivery name to " + outputAddress.getGeneralDeliveryName()); ps.setString(j + 11, outputAddress.getGeneralDeliveryName()); engineLogger.debug("Setting output lock box number to " + outputAddress.getLockBoxNumber()); ps.setString(j + 12, outputAddress.getLockBoxNumber()); engineLogger.debug("Setting output lock box type to " + outputAddress.getLockBoxType()); ps.setString(j + 13, outputAddress.getLockBoxType()); engineLogger.debug("Setting output municipality to " + outputAddress.getMunicipality()); ps.setString(j + 14, outputAddress.getMunicipality()); engineLogger.debug("Setting output postal code to " + outputAddress.getPostalCode()); ps.setString(j + 15, outputAddress.getPostalCode()); engineLogger.debug("Setting output province to " + outputAddress.getProvince()); ps.setString(j + 16, outputAddress.getProvince()); engineLogger .debug("Setting output rural route number to " + outputAddress.getRuralRouteNumber()); ps.setString(j + 17, outputAddress.getRuralRouteNumber()); engineLogger.debug("Setting output rural route type to " + outputAddress.getRuralRouteType()); ps.setString(j + 18, outputAddress.getRuralRouteType()); engineLogger.debug("Setting output street direciton to " + outputAddress.getStreetDirection()); ps.setString(j + 19, outputAddress.getStreetDirection()); engineLogger.debug("Setting output street to " + outputAddress.getStreet()); ps.setString(j + 20, outputAddress.getStreet()); engineLogger.debug("Setting output street number to " + outputAddress.getStreetNumber()); Integer streetNumber = outputAddress.getStreetNumber(); if (streetNumber == null) { ps.setNull(j + 21, Types.INTEGER); } else { ps.setInt(j + 21, streetNumber); } engineLogger.debug( "Setting output street number suffix to " + outputAddress.getStreetNumberSuffix()); ps.setString(j + 22, outputAddress.getStreetNumberSuffix()); engineLogger.debug("Setting output street number suffix separate to " + outputAddress.isStreetNumberSuffixSeparate()); Boolean isStreetNumberSuffixSeparate = outputAddress.isStreetNumberSuffixSeparate(); if (isStreetNumberSuffixSeparate == null) { ps.setNull(j + 23, Types.BOOLEAN); } else { ps.setBoolean(j + 23, outputAddress.isStreetNumberSuffixSeparate()); } engineLogger.debug("Setting output suite to " + outputAddress.getSuite()); ps.setString(j + 24, outputAddress.getStreetType()); engineLogger.debug("Setting output streetTypePrefix to " + outputAddress.isStreetTypePrefix()); ps.setBoolean(j + 25, outputAddress.isStreetTypePrefix()); engineLogger.debug("Setting output suite to " + outputAddress.getSuite()); ps.setString(j + 26, outputAddress.getSuite()); engineLogger.debug("Setting output suitePrefix to " + outputAddress.isSuitePrefix()); ps.setBoolean(j + 27, outputAddress.isSuitePrefix()); engineLogger.debug("Setting output suiteType to " + outputAddress.getSuiteType()); ps.setString(j + 28, outputAddress.getSuiteType()); engineLogger.debug("Setting output type to " + outputAddress.getType()); RecordType type = outputAddress.getType(); ps.setString(j + 29, type == null ? null : type.toString()); engineLogger.debug( "Setting output unparsedAddressLine1 to " + outputAddress.getUnparsedAddressLine1()); ps.setString(j + 30, outputAddress.getUnparsedAddressLine1()); engineLogger.debug("Setting output urbanBeforeRural to " + outputAddress.isUrbanBeforeRural()); Boolean urbanBeforeRural = outputAddress.isUrbanBeforeRural(); if (urbanBeforeRural == null) { ps.setNull(j + 31, Types.BOOLEAN); } else { ps.setBoolean(j + 31, outputAddress.isUrbanBeforeRural()); } engineLogger.debug("Setting valid to " + result.isValid()); ps.setBoolean(j + 32, result.isValid()); engineLogger.debug("Preparing the following address to be inserted: " + result); if (useBatchUpdates) { engineLogger.debug("Adding to batch"); ps.addBatch(); batchCount++; // TODO: The batchCount should be user setable if (batchCount > 1000) { engineLogger.debug("Executing batch"); ps.executeBatch(); batchCount = 0; } } else { engineLogger.debug("Executing statement"); ps.execute(); } incrementProgress(); } // Execute remaining batch statements if (batchCount > 0 && useBatchUpdates) { ps.executeBatch(); } if (ps != null) ps.close(); ps = null; } if (debug) { engineLogger.debug("Rolling back changes"); con.rollback(); } else { engineLogger.debug("Committing changes"); con.commit(); } for (AddressResult ar : addresses.values()) { ar.markClean(); } } catch (Exception ex) { try { con.rollback(); } catch (SQLException sqlEx) { engineLogger.error("Error while rolling back. " + "Suppressing this exception to prevent it from overshadowing the orginal exception.", sqlEx); } throw new RuntimeException("Unexpected exception while storing address validation results.\n" + "SQL statement: " + ((sql == null) ? "null" : sql.toString()) + "\n" + "Current result: " + ((result == null) ? "null" : "Input Address:\n" + result.getInputAddress() + "\n" + "Output Address:\n" + result.getOutputAddress()), ex); } finally { setFinished(true); if (ps != null) try { ps.close(); } catch (SQLException e) { engineLogger.error("Error while closing PreparedStatement", e); } if (stmt != null) try { stmt.close(); } catch (SQLException e) { engineLogger.error("Error while closing Statement", e); } if (con != null) try { con.close(); } catch (SQLException e) { engineLogger.error("Error while closing Connection", e); } } }
From source file:com.flexive.core.storage.genericSQL.GenericHierarchicalStorage.java
/** * Set a properties data for inserts or updates * * @param insert perform insert or update? * @param prop current property * @param allData all data of the instance (might be needed to buld references, etc.) * @param con an open and valid connection * @param data current property data * @param ps prepared statement for the data table * @param ft fulltext indexer * @param upperColumnPos position of the uppercase column (if present, else <code>-1</code>) * @param includeFullText add fulltext entries? Will be skipped for position only changes * @throws SQLException on errors * @throws FxUpdateException on errors * @throws FxDbException on errors * @throws FxNoAccessException for FxNoAccess values *//* w w w . jav a 2 s . co m*/ private void setPropertyData(boolean insert, FxProperty prop, List<FxData> allData, Connection con, FxPropertyData data, PreparedStatement ps, FulltextIndexer ft, int upperColumnPos, boolean includeFullText) throws SQLException, FxUpdateException, FxDbException, FxNoAccessException { FxValue value = data.getValue(); if (value instanceof FxNoAccess) throw new FxNoAccessException("ex.content.value.noaccess"); if (value.isMultiLanguage() != ((FxPropertyAssignment) data.getAssignment()).isMultiLang()) { if (((FxPropertyAssignment) data.getAssignment()).isMultiLang()) throw new FxUpdateException("ex.content.value.invalid.multilanguage.ass.multi", data.getXPathFull()); else throw new FxUpdateException("ex.content.value.invalid.multilanguage.ass.single", data.getXPathFull()); } int pos_lang = insert ? INSERT_LANG_POS : UPDATE_ID_POS + 2; int pos_isdef_lang = insert ? INSERT_ISDEF_LANG_POS : UPDATE_MLDEF_POS; final FxEnvironment env = CacheAdmin.getEnvironment(); if (prop.getDataType().isSingleRowStorage()) { //Data types that just use one db row can be handled in a very similar way Object translatedValue; GregorianCalendar gc = null; final long[] translatedLanguages = value.getTranslatedLanguages(); for (long translatedLanguage : translatedLanguages) { translatedValue = value.getTranslation(translatedLanguage); if (translatedValue == null) { LOG.warn("Translation for " + data.getXPath() + " is null!"); } ps.setLong(pos_lang, translatedLanguage); if (!value.isMultiLanguage()) ps.setBoolean(pos_isdef_lang, true); else ps.setBoolean(pos_isdef_lang, value.isDefaultLanguage(translatedLanguage)); if (upperColumnPos != -1) { final Locale locale = value.isMultiLanguage() ? env.getLanguage(translatedLanguage).getLocale() : Locale.getDefault(); ps.setString(upperColumnPos, translatedValue.toString().toUpperCase(locale)); } int[] pos = insert ? getColumnPosInsert(prop) : getColumnPosUpdate(prop); switch (prop.getDataType()) { case Double: checkDataType(FxDouble.class, value, data.getXPathFull()); ps.setDouble(pos[0], (Double) translatedValue); break; case Float: checkDataType(FxFloat.class, value, data.getXPathFull()); ps.setFloat(pos[0], (Float) translatedValue); break; case LargeNumber: checkDataType(FxLargeNumber.class, value, data.getXPathFull()); ps.setLong(pos[0], (Long) translatedValue); break; case Number: checkDataType(FxNumber.class, value, data.getXPathFull()); ps.setInt(pos[0], (Integer) translatedValue); break; case HTML: checkDataType(FxHTML.class, value, data.getXPathFull()); boolean useTidy = ((FxHTML) value).isTidyHTML(); ps.setBoolean(pos[1], useTidy); final String extractorInput = doTidy(data.getXPathFull(), (String) translatedValue); if (useTidy) { translatedValue = extractorInput; } final HtmlExtractor result = new HtmlExtractor(extractorInput, true); setBigString(ps, pos[2], result.getText()); setBigString(ps, pos[0], (String) translatedValue); break; case String1024: case Text: checkDataType(FxString.class, value, data.getXPathFull()); setBigString(ps, pos[0], (String) translatedValue); break; case Boolean: checkDataType(FxBoolean.class, value, data.getXPathFull()); ps.setBoolean(pos[0], (Boolean) translatedValue); break; case Date: checkDataType(FxDate.class, value, data.getXPathFull()); if (gc == null) gc = new GregorianCalendar(); gc.setTime((Date) translatedValue); //strip all time information, this might not be necessary since ps.setDate() strips them //for most databases but won't hurt either ;) gc.set(GregorianCalendar.HOUR, 0); gc.set(GregorianCalendar.MINUTE, 0); gc.set(GregorianCalendar.SECOND, 0); gc.set(GregorianCalendar.MILLISECOND, 0); ps.setDate(pos[0], new java.sql.Date(gc.getTimeInMillis())); break; case DateTime: checkDataType(FxDateTime.class, value, data.getXPathFull()); if (gc == null) gc = new GregorianCalendar(); gc.setTime((Date) translatedValue); ps.setTimestamp(pos[0], new Timestamp(gc.getTimeInMillis())); break; case DateRange: checkDataType(FxDateRange.class, value, data.getXPathFull()); if (gc == null) gc = new GregorianCalendar(); gc.setTime(((DateRange) translatedValue).getLower()); gc.set(GregorianCalendar.HOUR, 0); gc.set(GregorianCalendar.MINUTE, 0); gc.set(GregorianCalendar.SECOND, 0); gc.set(GregorianCalendar.MILLISECOND, 0); ps.setDate(pos[0], new java.sql.Date(gc.getTimeInMillis())); gc.setTime(((DateRange) translatedValue).getUpper()); gc.set(GregorianCalendar.HOUR, 0); gc.set(GregorianCalendar.MINUTE, 0); gc.set(GregorianCalendar.SECOND, 0); gc.set(GregorianCalendar.MILLISECOND, 0); ps.setDate(pos[1], new java.sql.Date(gc.getTimeInMillis())); break; case DateTimeRange: checkDataType(FxDateTimeRange.class, value, data.getXPathFull()); if (gc == null) gc = new GregorianCalendar(); gc.setTime(((DateRange) translatedValue).getLower()); ps.setTimestamp(pos[0], new Timestamp(gc.getTimeInMillis())); gc.setTime(((DateRange) translatedValue).getUpper()); ps.setTimestamp(pos[1], new Timestamp(gc.getTimeInMillis())); break; case Binary: checkDataType(FxBinary.class, value, data.getXPathFull()); BinaryDescriptor binary = (BinaryDescriptor) translatedValue; if (!binary.isNewBinary()) { ps.setLong(pos[0], binary.getId()); } else { try { //transfer the binary from the transit table to the binary table BinaryDescriptor created = binaryStorage.binaryTransit(con, binary); ps.setLong(pos[0], created.getId()); //check all other properties if they contain the same handle //and replace with the data of the new binary for (FxData _curr : allData) { if (_curr instanceof FxPropertyData && !_curr.isEmpty() && ((FxPropertyData) _curr).getValue() instanceof FxBinary) { FxBinary _val = (FxBinary) ((FxPropertyData) _curr).getValue(); _val._replaceHandle(binary.getHandle(), created); } } } catch (FxApplicationException e) { throw new FxDbException(e); } } break; case SelectOne: checkDataType(FxSelectOne.class, value, data.getXPathFull()); ps.setLong(pos[0], ((FxSelectListItem) translatedValue).getId()); break; case SelectMany: checkDataType(FxSelectMany.class, value, data.getXPathFull()); SelectMany sm = (SelectMany) translatedValue; for (int i1 = 0; i1 < sm.getSelected().size(); i1++) { FxSelectListItem item = sm.getSelected().get(i1); if (i1 > 0) { if (batchContentDataChanges()) ps.addBatch(); else ps.executeUpdate(); } ps.setLong(pos[0], item.getId()); ps.setString(pos[1], sm.getSelectedIdsList()); ps.setLong(pos[2], sm.getSelectedIds().size()); } if (sm.getSelected().size() == 0) ps.setLong(pos[0], 0); //write the virtual item as a marker to have a valid row break; case Reference: //reference integrity check is done prior to saving ps.setLong(pos[0], ((FxPK) translatedValue).getId()); break; case InlineReference: default: throw new FxDbException(LOG, "ex.db.notImplemented.store", prop.getDataType().getName()); } int valueDataPos = insert ? getValueDataInsertPos(prop.getDataType()) : getValueDataUpdatePos(prop.getDataType()); if (value.hasValueData(translatedLanguage)) { ps.setInt(valueDataPos, value.getValueDataRaw(translatedLanguage)); } else ps.setNull(valueDataPos, Types.NUMERIC); if (batchContentDataChanges()) ps.addBatch(); else { try { ps.executeUpdate(); } catch (SQLException e) { LOG.error(prop.getName(), e); throw e; } } } } else { switch (prop.getDataType()) { //TODO: implement datatype specific insert default: throw new FxDbException(LOG, "ex.db.notImplemented.store", prop.getDataType().getName()); } } if (ft != null && prop.isFulltextIndexed() && includeFullText) ft.index(data); }
From source file:com.flexive.ejb.beans.structure.AssignmentEngineBean.java
/** * @param con an existing connection * @param original the original property assignment to compare changes * against and update. if==null, the original will be fetched from the cache * @param modified the modified property assignment @return if any changes were found * @return true if the original assignment was modified * @throws FxApplicationException on errors *//*from w ww. ja v a 2 s . c o m*/ private boolean updatePropertyAssignment(Connection con, FxPropertyAssignment original, FxPropertyAssignmentEdit modified) throws FxApplicationException { if (modified.isNew()) throw new FxInvalidParameterException("ex.structure.assignment.update.new", modified.getXPath()); final StringBuilder sql = new StringBuilder(1000); boolean changes = false; boolean success = false; StringBuilder changesDesc = new StringBuilder(200); if (original == null) original = (FxPropertyAssignment) CacheAdmin.getEnvironment().getAssignment(modified.getId()); PreparedStatement ps = null; try { if (con == null) con = Database.getDbConnection(); sql.setLength(0); if (!original.isSystemInternal() || FxContext.getUserTicket().isGlobalSupervisor()) { // enable or disable a property assignment, remove the assignment if set to false if (original.isEnabled() != modified.isEnabled()) { if (!modified.isEnabled()) removeAssignment(original.getId(), true, false, true, false); else { ps = con.prepareStatement("UPDATE " + TBL_STRUCT_ASSIGNMENTS + " SET ENABLED=? WHERE ID=?"); ps.setBoolean(1, modified.isEnabled()); ps.setLong(2, original.getId()); ps.executeUpdate(); ps.close(); } if (changes) changesDesc.append(','); changesDesc.append("enabled=").append(modified.isEnabled()); changes = true; } // change the property assignment's default multiplicity if (original.getDefaultMultiplicity() != modified.getDefaultMultiplicity()) { ps = con.prepareStatement("UPDATE " + TBL_STRUCT_ASSIGNMENTS + " SET DEFMULT=? WHERE ID=?"); ps.setInt(1, modified.getDefaultMultiplicity()); ps.setLong(2, original.getId()); ps.executeUpdate(); ps.close(); if (changes) changesDesc.append(','); changesDesc.append("defaultMultiplicity=").append(modified.getDefaultMultiplicity()); changes = true; } boolean needMin = original.getMultiplicity().getMin() != modified.getMultiplicity().getMin(); boolean needMax = original.getMultiplicity().getMax() != modified.getMultiplicity().getMax(); // change the property assignment's multiplicity if (needMin || needMax) { if (original.getProperty().mayOverrideBaseMultiplicity()) { //only check if instances exist if (EJBLookup.getTypeEngine().getInstanceCount(original.getAssignedType().getId()) > 0) { if (needMin) checkChangePropertyAssignmentMinMultiplicity(con, original, modified.getMultiplicity()); if (needMax && getPropertyInstanceMultiplicity(con, original.getProperty().getId(), false) > modified.getMultiplicity().getMax()) throw new FxUpdateException("ex.structure.modification.contentExists", "maximumMultiplicity"); } } else { throw new FxUpdateException( "ex.structure.property.assignment.overrideBaseMultiplicityNotEnabled", original.getProperty().getId()); } ps = con.prepareStatement( "UPDATE " + TBL_STRUCT_ASSIGNMENTS + " SET MINMULT=? ,MAXMULT=? WHERE ID=?"); ps.setInt(1, modified.getMultiplicity().getMin()); ps.setInt(2, modified.getMultiplicity().getMax()); ps.setLong(3, original.getId()); ps.executeUpdate(); ps.close(); if (changes) changesDesc.append(','); changesDesc.append("multiplicity=").append(modified.getMultiplicity()); changes = true; } // set the assignment's position if (original.getPosition() != modified.getPosition()) { int finalPos = setAssignmentPosition(con, modified.getId(), modified.getPosition()); if (changes) changesDesc.append(','); changesDesc.append("position=").append(finalPos); changes = true; } // alias / xpath change if (!original.getXPath().equals(modified.getXPath()) || !original.getAlias().equals(modified.getAlias())) { if (!XPathElement.isValidXPath(XPathElement.stripType(modified.getXPath())) || modified.getAlias().equals(XPathElement .lastElement(XPathElement.stripType(original.getXPath())).getAlias())) throw new FxUpdateException("ex.structure.assignment.noXPath"); // generate correct XPATH if (!modified.getXPath().startsWith(modified.getAssignedType().getName())) modified.setXPath(modified.getAssignedType().getName() + modified.getXPath()); //avoid duplicates if (original.getAssignedType().isXPathValid(modified.getXPath(), true)) throw new FxUpdateException("ex.structure.assignment.exists", modified.getXPath(), modified.getAssignedType().getName()); // update db entries ps = con.prepareStatement( "UPDATE " + TBL_STRUCT_ASSIGNMENTS + " SET XPATH=?, XALIAS=? WHERE ID=?"); ps.setString(1, modified.getXPath()); ps.setString(2, modified.getAlias()); ps.setLong(3, modified.getId()); ps.executeUpdate(); ps.close(); // update the relevant content instances ContentStorage storage = StorageManager.getContentStorage(TypeStorageMode.Hierarchical); storage.updateXPath(con, modified.getId(), XPathElement.stripType(original.getXPath()), XPathElement.stripType(modified.getXPath())); if (changes) changesDesc.append(','); changesDesc.append("xPath=").append(modified.getXPath()).append(",alias=") .append(modified.getAlias()); changes = true; } // change the assignment's ACL if (original.getACL().getId() != modified.getACL().getId()) { ps = con.prepareStatement("UPDATE " + TBL_STRUCT_ASSIGNMENTS + " SET ACL=? WHERE ID=?"); ps.setLong(1, modified.getACL().getId()); ps.setLong(2, original.getId()); ps.executeUpdate(); ps.close(); if (changes) changesDesc.append(','); changesDesc.append("acl=").append(modified.getACL().getId()); changes = true; } // options are stored via storeOption method if (original.isMultiLang() != modified.isMultiLang()) { //Multi->Single: lang=system, values of the def. lang. are used, other are discarded //Single->Multi: lang=default language if (!original.getProperty().mayOverrideMultiLang()) //noinspection ThrowableInstanceNeverThrown throw new FxUpdateException("ex.structure.assignment.overrideNotAllowed.multiLang", original.getXPath(), original.getProperty().getName()).setAffectedXPath( original.getXPath(), FxContentExceptionCause.MultiLangOverride); if (modified.isFlatStorageEntry() && getAssignmentInstanceCount(modified.getId()) > 0) //noinspection ThrowableInstanceNeverThrown throw new FxUpdateException("ex.structure.assignment.overrideNotSupported.multiLang", original.getXPath(), original.getProperty().getName()).setAffectedXPath( original.getXPath(), FxContentExceptionCause.MultiLangOverride); StorageManager.getContentStorage(TypeStorageMode.Hierarchical).updateMultilanguageSettings(con, original.getId(), original.isMultiLang(), modified.isMultiLang(), modified.getDefaultLanguage()); if (changes) changesDesc.append(','); changesDesc.append("multiLang=").append(modified.isMultiLang()); changes = true; } // change the assignment's label if (original.getLabel() != null && !original.getLabel().equals(modified.getLabel()) || original.getLabel() == null && modified.getLabel() != null || original.getHint() != null && !original.getHint().equals(modified.getHint()) || original.getHint() == null && modified.getHint() != null) { Database.storeFxString(new FxString[] { modified.getLabel(), modified.getHint() }, con, TBL_STRUCT_ASSIGNMENTS, new String[] { "DESCRIPTION", "HINT" }, "ID", original.getId()); if (changes) changesDesc.append(','); changesDesc.append("label=").append(modified.getLabel()).append(','); changesDesc.append("hint=").append(modified.getHint()).append(','); changes = true; } // change the assigment's default value if (original.getDefaultValue() != null && !original.getDefaultValue().equals(modified.getDefaultValue()) || original.getDefaultValue() == null && modified.getDefaultValue() != null || original.hasAssignmentDefaultValue() != modified.hasAssignmentDefaultValue()) { if (changes) changesDesc.append(','); ps = con.prepareStatement( "UPDATE " + TBL_STRUCT_ASSIGNMENTS + " SET DEFAULT_VALUE=? WHERE ID=?"); FxValue defValue = modified.getDefaultValue(); if (defValue instanceof FxBinary && modified.hasAssignmentDefaultValue()) { ContentStorage storage = StorageManager .getContentStorage(modified.getAssignedType().getStorageMode()); storage.prepareBinary(con, (FxBinary) defValue); } final String _def = defValue == null || defValue.isEmpty() ? null : ConversionEngine.getXStream().toXML(defValue); if (_def != null && modified.hasAssignmentDefaultValue() && (modified.getDefaultValue() instanceof FxReference)) { //check if the type matches the instance checkReferencedType(con, (FxReference) modified.getDefaultValue(), modified.getProperty().getReferencedType()); } if (_def == null || !modified.hasAssignmentDefaultValue()) ps.setNull(1, java.sql.Types.VARCHAR); else ps.setString(1, _def); ps.setLong(2, original.getId()); ps.executeUpdate(); ps.close(); changesDesc.append("defaultValue=").append(original.getDefaultValue()); changes = true; } // change the default language if (original.getDefaultLanguage() != modified.getDefaultLanguage()) { ps = con.prepareStatement("UPDATE " + TBL_STRUCT_ASSIGNMENTS + " SET DEFLANG=? WHERE ID=?"); ps.setInt(1, (int) modified.getDefaultLanguage()); ps.setLong(2, original.getId()); ps.executeUpdate(); ps.close(); if (changes) changesDesc.append(','); changesDesc.append("defaultLanguage=").append(modified.getDefaultLanguage()); changes = true; } //update SystemInternal flag, this is a one way function, so it can only be set, but not reset!! if (!original.isSystemInternal() && modified.isSystemInternal()) { if (FxContext.getUserTicket().isGlobalSupervisor()) { ps = con.prepareStatement( "UPDATE " + TBL_STRUCT_ASSIGNMENTS + " SET SYSINTERNAL=? WHERE ID=?"); ps.setBoolean(1, modified.isSystemInternal()); ps.setLong(2, original.getId()); ps.executeUpdate(); ps.close(); if (changes) changesDesc.append(','); changesDesc.append("systemInternal=").append(modified.isSystemInternal()); changes = true; } else throw new FxUpdateException("ex.structure.modification.systemInternal.notGlobalSupervisor", modified.getLabel()); } // change the parentgroupassignment // if (original.getParentGroupAssignment().getId() != modified.getParentGroupAssignment().getId()) { // } /* if (changes) { //propagate changes to derived assignments List<FxAssignment> children = CacheAdmin.getEnvironment().getDerivedAssignments(modified.getId()); for (FxAssignment as : children) { if (as instanceof FxPropertyAssignment) { updatePropertyAssignment(null, null, null, (FxPropertyAssignment) as, modified); } } //if there are changes AND the assignment is a child, // break the inheritance and make it a "ROOT_BASE" assignment if(original.isDerivedAssignment()) { if (ps!=null) ps.close(); ps = con.prepareStatement("UPDATE " + TBL_STRUCT_ASSIGNMENTS + " SET BASE=? WHERE ID=?"); ps.setNull(1, Types.NUMERIC); ps.setLong(2, original.getId()); ps.executeUpdate(); changesDesc.append(",baseAssignment=null"); } } */ } else throw new FxUpdateException("ex.structure.systemInternal.forbidden", modified.getLabel()); if (updatePropertyAssignmentOptions(con, original, modified)) { changesDesc.append(",options:"); List<FxStructureOption> options = modified.getOptions(); for (FxStructureOption option : options) { changesDesc.append(option.getKey()).append("=").append(option.getValue()) .append(" overridable=").append(option.isOverridable()).append(" isSet=") .append(option.isSet()).append("isInherited").append(option.getIsInherited()); } changes = true; } //TODO: compare all possible modifications if (changes) { htracker.track(modified.getAssignedType(), "history.assignment.updatePropertyAssignment", original.getXPath(), modified.getAssignedType().getId(), modified.getAssignedType().getName(), modified.getProperty().getId(), modified.getProperty().getName(), changesDesc.toString()); } success = true; } catch (SQLException e) { final boolean uniqueConstraintViolation = StorageManager.isUniqueConstraintViolation(e); EJBUtils.rollback(ctx); if (uniqueConstraintViolation) throw new FxEntryExistsException("ex.structure.assignment.property.exists", original.getAlias(), original.getXPath()); throw new FxCreateException(LOG, e, "ex.db.sqlError", e.getMessage()); } finally { Database.closeObjects(AssignmentEngineBean.class, null, ps); if (!success) { EJBUtils.rollback(ctx); } } return changes; }
From source file:com.funambol.foundation.items.dao.PIMCalendarDAO.java
/** * Updates a calendar./*from w w w .j av a2 s. co m*/ * * @param cw as a CalendarWrapper object. If its last update time is null, * then it's set to the current time. * @return the UID of the contact * @throws DAOException * @throws java.lang.Exception * @see CalendarWrapper */ public String updateItem(CalendarWrapper cw) throws DAOException, Exception { Connection con = null; PreparedStatement ps = null; ResultSet rs = null; RecurrencePattern rp = null; String id = null; String allDay = null; String body = null; Boolean allDayBoolean = null; Short busyStatus = null; String categories = null; String companies = null; int duration = 0; Date dend = null; short importance = 0; String location = null; Short meetingStatus = null; String mileage = null; Date replyTime = null; short sensitivity = 0; Date dstart = null; String subject = null; short recurrenceType = -1; int interval = 0; short monthOfYear = 0; short dayOfMonth = 0; String dayOfWeekMask = null; String priority = null; short instance = 0; String startDatePattern = null; String noEndDate = null; String endDatePattern = null; int occurrences = -1; Reminder reminder = null; CalendarContent c = null; Date completed = null; String complete = null; short percentComplete = -1; String folder = null; String dstartTimeZone = null; String dendTimeZone = null; String reminderTimeZone = null; StringBuffer queryUpdateFunPimCalendar = null; try { Timestamp lastUpdate = (cw.getLastUpdate() == null) ? new Timestamp(System.currentTimeMillis()) : cw.getLastUpdate(); c = cw.getCalendar().getCalendarContent(); rp = c.getRecurrencePattern(); id = cw.getId(); boolean allDayB; allDayBoolean = c.getAllDay(); if (allDayBoolean != null && allDayBoolean.booleanValue()) { allDayB = true; allDay = "1"; } else { allDayB = false; allDay = "0"; } body = Property.stringFrom(c.getDescription()); if (c.getBusyStatus() != null) { busyStatus = new Short(c.getBusyStatus().shortValue()); } categories = Property.stringFrom(c.getCategories()); companies = Property.stringFrom(c.getOrganizer()); if (c.getPriority() != null) { priority = c.getPriority().getPropertyValueAsString(); if (priority != null && priority.length() > 0) { importance = Short.parseShort(priority); } // priority / importance ?? } location = Property.stringFrom(c.getLocation()); meetingStatus = c.getMeetingStatus(); if (c.getMileage() != null) { mileage = String.valueOf(c.getMileage()); } reminder = c.getReminder(); String rt = null; if (c instanceof Event) { rt = Property.stringFrom(((Event) c).getReplyTime()); replyTime = getDateFromString(allDayB, // @todo or false? rt, "000000"); } if (c.getAccessClass() != null) { String classEvent = null; classEvent = c.getAccessClass().getPropertyValueAsString(); if (classEvent != null && classEvent.length() > 0) { sensitivity = Short.parseShort(classEvent); } } if (c.getSummary() != null) { subject = c.getSummary().getPropertyValueAsString(); } else if (body != null && body.length() > 0) { String tmpBody = body; if (tmpBody.indexOf('.') != -1) { tmpBody = tmpBody.substring(0, tmpBody.indexOf('.')); } if (tmpBody.length() > SQL_SUBJECT_DIM) { tmpBody = tmpBody.substring(0, SQL_SUBJECT_DIM); } subject = tmpBody; } folder = Property.stringFrom(c.getFolder()); dstartTimeZone = timeZoneFrom(c.getDtStart()); dendTimeZone = timeZoneFrom(c.getDtEnd()); reminderTimeZone = timeZoneFrom(c.getReminder()); String sd = null; if (c.getDtStart() != null) { sd = c.getDtStart().getPropertyValueAsString(); dstart = getDateFromString(allDayB, sd, "000000"); } String ed = null; if ((sd != null && sd.length() > 0) || (c.getDtEnd() != null)) { ed = c.getDtEnd().getPropertyValueAsString(); // // Fix for Siemens S56 end date issue only for event // @todo: verify if is really need to do this // Due to this fix, in method getTwinItems() if the incoming // Event has an empty EndDate we seek into the db for Events // with EndDate equal to the StartDate value. // if (c instanceof Event) { if (ed == null || ed.length() == 0) { ed = sd; } } dend = getDateFromString(allDayB, ed, "235900"); } if (rp != null) { recurrenceType = rp.getTypeId(); interval = rp.getInterval(); monthOfYear = rp.getMonthOfYear(); dayOfMonth = rp.getDayOfMonth(); dayOfWeekMask = String.valueOf(rp.getDayOfWeekMask()); instance = rp.getInstance(); startDatePattern = rp.getStartDatePattern(); boolean noEndDateB = rp.isNoEndDate(); if (noEndDateB) { noEndDate = "1"; } else { noEndDate = "0"; } endDatePattern = rp.getEndDatePattern(); occurrences = rp.getOccurrences(); } String dc = null; if (c instanceof Task) { Task t = (Task) c; if (t.getDateCompleted() != null) { dc = t.getDateCompleted().getPropertyValueAsString(); completed = getDateFromString(allDayB, dc, "000000"); } complete = Property.stringFrom(t.getComplete()); if (complete != null && "1".equals(complete)) { percentComplete = 100; } else { try { percentComplete = Short.parseShort(Property.stringFrom(t.getPercentComplete())); if (percentComplete < 0 || percentComplete > 100) { throw new NumberFormatException("A percentage can't be " + percentComplete); } } catch (NumberFormatException nfe) { percentComplete = -1; // the short must go on } } meetingStatus = getTaskStatus(t); } queryUpdateFunPimCalendar = new StringBuffer(); queryUpdateFunPimCalendar.append(SQL_UPDATE_FNBL_PIM_CALENDAR_BEGIN).append(SQL_FIELD_LAST_UPDATE) .append(SQL_EQUALS_QUESTIONMARK_COMMA); if (allDayBoolean != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_ALL_DAY).append(SQL_EQUALS_QUESTIONMARK_COMMA); } if (body != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_BODY).append(SQL_EQUALS_QUESTIONMARK_COMMA); } queryUpdateFunPimCalendar.append(SQL_FIELD_BUSY_STATUS).append(SQL_EQUALS_QUESTIONMARK_COMMA); if (categories != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_CATEGORIES).append(SQL_EQUALS_QUESTIONMARK_COMMA); } if (companies != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_COMPANIES).append(SQL_EQUALS_QUESTIONMARK_COMMA); } queryUpdateFunPimCalendar.append(SQL_FIELD_DURATION).append(SQL_EQUALS_QUESTIONMARK_COMMA); if (dend != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_DATE_END).append(SQL_EQUALS_QUESTIONMARK_COMMA); } else if (ed != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_DATE_END).append(SQL_EQUALS_NULL_COMMA); } if (priority != null && priority.length() > 0) { queryUpdateFunPimCalendar.append(SQL_FIELD_IMPORTANCE).append(SQL_EQUALS_QUESTIONMARK_COMMA); } if (location != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_LOCATION).append(SQL_EQUALS_QUESTIONMARK_COMMA); } if (meetingStatus != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_MEETING_STATUS).append(SQL_EQUALS_QUESTIONMARK_COMMA); } if (mileage != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_MILEAGE).append(SQL_EQUALS_QUESTIONMARK_COMMA); } if (reminder != null) { if (reminder.isActive()) { queryUpdateFunPimCalendar.append(SQL_FIELD_REMINDER).append(SQL_EQUALS_ONE_COMMA); } else { queryUpdateFunPimCalendar.append(SQL_FIELD_REMINDER).append(SQL_EQUALS_ZERO_COMMA); } queryUpdateFunPimCalendar.append(SQL_FIELD_REMINDER_TIME).append(SQL_EQUALS_QUESTIONMARK_COMMA); queryUpdateFunPimCalendar.append(SQL_FIELD_REMINDER_REPEAT_COUNT) .append(SQL_EQUALS_QUESTIONMARK_COMMA); queryUpdateFunPimCalendar.append(SQL_FIELD_REMINDER_SOUND_FILE) .append(SQL_EQUALS_QUESTIONMARK_COMMA); queryUpdateFunPimCalendar.append(SQL_FIELD_REMINDER_OPTIONS).append(SQL_EQUALS_QUESTIONMARK_COMMA); } if (replyTime != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_REPLY_TIME).append(SQL_EQUALS_QUESTIONMARK_COMMA); } else if (rt != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_REPLY_TIME).append(SQL_EQUALS_NULL_COMMA); } queryUpdateFunPimCalendar.append(SQL_FIELD_SENSITIVITY).append(SQL_EQUALS_QUESTIONMARK_COMMA); if (dstart != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_DATE_START).append(SQL_EQUALS_QUESTIONMARK_COMMA); } else if (sd != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_DATE_START).append(SQL_EQUALS_NULL_COMMA); } if (subject != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_SUBJECT).append(SQL_EQUALS_QUESTIONMARK_COMMA); } queryUpdateFunPimCalendar.append(SQL_FIELD_RECURRENCE_TYPE).append(SQL_EQUALS_QUESTIONMARK_COMMA); queryUpdateFunPimCalendar.append(SQL_FIELD_INTERVAL).append(SQL_EQUALS_QUESTIONMARK_COMMA); queryUpdateFunPimCalendar.append(SQL_FIELD_MONTH_OF_YEAR).append(SQL_EQUALS_QUESTIONMARK_COMMA); queryUpdateFunPimCalendar.append(SQL_FIELD_DAY_OF_MONTH).append(SQL_EQUALS_QUESTIONMARK_COMMA); if (dayOfWeekMask != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_DAY_OF_WEEK_MASK).append(SQL_EQUALS_QUESTIONMARK_COMMA); } queryUpdateFunPimCalendar.append(SQL_FIELD_INSTANCE).append(SQL_EQUALS_QUESTIONMARK_COMMA); if (startDatePattern != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_START_DATE_PATTERN) .append(SQL_EQUALS_QUESTIONMARK_COMMA); } if (noEndDate != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_NO_END_DATE).append(SQL_EQUALS_QUESTIONMARK_COMMA); } if (endDatePattern != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_END_DATE_PATTERN).append(SQL_EQUALS_QUESTIONMARK_COMMA); } else { // // When NoEndDate is true, the PatterEndDate must be empty. // if ("1".equals(noEndDate)) { queryUpdateFunPimCalendar.append(SQL_FIELD_END_DATE_PATTERN).append(SQL_EQUALS_EMPTY_COMMA); } } queryUpdateFunPimCalendar.append(SQL_FIELD_OCCURRENCES).append(SQL_EQUALS_QUESTIONMARK_COMMA); if (completed != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_COMPLETED).append(SQL_EQUALS_QUESTIONMARK_COMMA); } else if (dc != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_COMPLETED).append(SQL_EQUALS_NULL_COMMA); } if (percentComplete != -1) { queryUpdateFunPimCalendar.append(SQL_FIELD_PERCENT_COMPLETE).append(SQL_EQUALS_QUESTIONMARK_COMMA); } else if ("0".equals(complete)) { queryUpdateFunPimCalendar.append(SQL_FIELD_PERCENT_COMPLETE).append(SQL_PERCENT_COMPLETE_FORMULA); } if (folder != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_FOLDER).append(SQL_EQUALS_QUESTIONMARK_COMMA); } if (dstartTimeZone != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_START_DATE_TIME_ZONE) .append(SQL_EQUALS_QUESTIONMARK_COMMA); } if (dendTimeZone != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_END_DATE_TIME_ZONE) .append(SQL_EQUALS_QUESTIONMARK_COMMA); } if (reminderTimeZone != null) { queryUpdateFunPimCalendar.append(SQL_FIELD_REMINDER_TIME_ZONE) .append(SQL_EQUALS_QUESTIONMARK_COMMA); } queryUpdateFunPimCalendar.append(SQL_FIELD_STATUS).append(SQL_EQUALS_QUESTIONMARK) .append(SQL_UPDATE_FNBL_PIM_CALENDAR_END); con = getUserDataSource().getRoutedConnection(userId); ps = con.prepareStatement(queryUpdateFunPimCalendar.toString()); int k = 1; // // lastUpdate // ps.setLong(k++, lastUpdate.getTime()); // // allDay // if (allDayBoolean != null) { ps.setString(k++, allDay); } // // body // if (body != null) { if (body.length() > SQL_BODY_DIM) { body = body.substring(0, SQL_BODY_DIM); } ps.setString(k++, body); } // // busy status // if (busyStatus != null) { ps.setShort(k++, busyStatus.shortValue()); } else { ps.setNull(k++, Types.SMALLINT); } // // categories // if (categories != null) { if (categories.length() > SQL_CATEGORIES_DIM) { categories = categories.substring(0, SQL_CATEGORIES_DIM); } ps.setString(k++, categories); } // // companies // if (companies != null) { if (companies.length() > SQL_COMPANIES_DIM) { companies = companies.substring(0, SQL_COMPANIES_DIM); } ps.setString(k++, companies); } // // duration // ps.setInt(k++, duration); // // date End // if (dend != null) { ps.setTimestamp(k++, new Timestamp(dend.getTime())); } // // priority // if (priority != null && priority.length() > 0) { ps.setShort(k++, importance); } // // location // if (location != null) { if (location.length() > SQL_COMPANIES_DIM) { location = location.substring(0, SQL_LOCATION_DIM); } ps.setString(k++, location); } // // meeting status // if (meetingStatus != null) { ps.setShort(k++, meetingStatus.shortValue()); } // // mileage // if (mileage != null) { ps.setString(k++, mileage); } // // reminder // if (reminder != null) { if (reminder.isActive()) { ps.setTimestamp(k++, getReminderTime(dstart, reminder)); ps.setInt(k++, reminder.getRepeatCount()); String soundFileValue = reminder.getSoundFile(); if (soundFileValue != null && soundFileValue.length() > SQL_SOUNDFILE_DIM) { soundFileValue = soundFileValue.substring(0, SQL_SOUNDFILE_DIM); } ps.setString(k++, soundFileValue); ps.setInt(k++, reminder.getOptions()); } else { ps.setNull(k++, Types.TIMESTAMP); ps.setInt(k++, 0); ps.setString(k++, null); ps.setInt(k++, 0); } } // // reply time // if (replyTime != null) { ps.setTimestamp(k++, new Timestamp(replyTime.getTime())); } // // sensitivity // ps.setShort(k++, sensitivity); // // date start // if (dstart != null) { ps.setTimestamp(k++, new Timestamp(dstart.getTime())); } // // subject // if (subject != null) { if (subject.length() > SQL_SUBJECT_DIM) { subject = subject.substring(0, SQL_SUBJECT_DIM); } ps.setString(k++, subject); } // // recurrence Type // ps.setShort(k++, recurrenceType); // // interval // ps.setInt(k++, interval); // // month of year // ps.setShort(k++, monthOfYear); // // day of month // ps.setShort(k++, dayOfMonth); // // day of week mask // if (dayOfWeekMask != null) { if (dayOfWeekMask.length() > SQL_DAYOFWEEKMASK_DIM) { dayOfWeekMask = dayOfWeekMask.substring(0, SQL_DAYOFWEEKMASK_DIM); } ps.setString(k++, dayOfWeekMask); } // // instance // ps.setShort(k++, instance); // // start date pattern // if (startDatePattern != null) { if (startDatePattern.length() > SQL_STARTDATEPATTERN_DIM) { startDatePattern = startDatePattern.substring(0, SQL_STARTDATEPATTERN_DIM); } ps.setString(k++, startDatePattern); } // // no end date // if (noEndDate != null) { ps.setString(k++, noEndDate); } // // end date pattern // if (endDatePattern != null) { if (endDatePattern.length() > SQL_ENDDATEPATTERN_DIM) { endDatePattern = endDatePattern.substring(0, SQL_ENDDATEPATTERN_DIM); } ps.setString(k++, endDatePattern); } // // occurrences // ps.setInt(k++, occurrences); // // completed // if (completed != null) { ps.setTimestamp(k++, new Timestamp(completed.getTime())); } // // percent completed // if (percentComplete != -1) { ps.setShort(k++, percentComplete); } // // folder // if (folder != null) { if (folder.length() > SQL_FOLDER_DIM) { folder = folder.substring(0, SQL_FOLDER_DIM); } ps.setString(k++, folder); } // // time zones // if (dstartTimeZone != null) { if (dstartTimeZone.length() > SQL_TIME_ZONE_DIM) { dstartTimeZone = dstartTimeZone.substring(0, SQL_TIME_ZONE_DIM); } ps.setString(k++, dstartTimeZone); } if (dendTimeZone != null) { if (dendTimeZone.length() > SQL_TIME_ZONE_DIM) { dendTimeZone = dendTimeZone.substring(0, SQL_TIME_ZONE_DIM); } ps.setString(k++, dendTimeZone); } if (reminderTimeZone != null) { if (reminderTimeZone.length() > SQL_TIME_ZONE_DIM) { reminderTimeZone = reminderTimeZone.substring(0, SQL_TIME_ZONE_DIM); } ps.setString(k++, reminderTimeZone); } // // status // ps.setString(k++, String.valueOf('U')); // // id // ps.setLong(k++, Long.parseLong(id)); // // user id // ps.setString(k++, cw.getUserId()); ps.executeUpdate(); DBTools.close(null, ps, null); ps = con.prepareStatement(SQL_DELETE_CALENDAR_EXCEPTIONS_BY_CALENDAR); ps.setLong(1, Long.parseLong(id)); ps.executeUpdate(); DBTools.close(null, ps, null); if (recurrenceType != -1) { List<ExceptionToRecurrenceRule> exceptions = rp.getExceptions(); for (ExceptionToRecurrenceRule etrr : exceptions) { ps = con.prepareStatement(SQL_INSERT_INTO_FNBL_PIM_CALENDAR_EXCEPTION); ps.setLong(1, Long.parseLong(id)); ps.setString(2, (etrr.isAddition() ? "1" : "0")); ps.setTimestamp(3, new Timestamp(getDateFromString(allDayB, etrr.getDate(), "000000").getTime())); ps.executeUpdate(); DBTools.close(null, ps, null); } } } catch (Exception e) { throw new DAOException("Error updating a calendar item: " + e.getMessage()); } finally { DBTools.close(con, ps, rs); } return id; }