List of usage examples for java.sql PreparedStatement addBatch
void addBatch() throws SQLException;
PreparedStatement object's batch of commands. From source file:com.oltpbenchmark.benchmarks.tpcc.TPCCLoader.java
protected int loadStock(int whseKount, int itemKount) { int k = 0;/*from ww w.j a v a2s. c o m*/ int t = 0; int randPct = 0; int len = 0; int startORIGINAL = 0; try { PreparedStatement stckPrepStmt = getInsertStatement(TPCCConstants.TABLENAME_STOCK); now = new java.util.Date(); t = (whseKount * itemKount); LOG.debug("\nStart Stock Load for " + t + " units @ " + now + " ..."); if (outputFiles == true) { out = new PrintWriter(new FileOutputStream(fileLocation + "stock.csv")); LOG.debug("\nWriting Stock file to: " + fileLocation + "stock.csv"); } Stock stock = new Stock(); for (int i = 1; i <= itemKount; i++) { for (int w = 1; w <= whseKount; w++) { stock.s_i_id = i; stock.s_w_id = w; stock.s_quantity = TPCCUtil.randomNumber(10, 100, gen); stock.s_ytd = 0; stock.s_order_cnt = 0; stock.s_remote_cnt = 0; // s_data randPct = TPCCUtil.randomNumber(1, 100, gen); len = TPCCUtil.randomNumber(26, 50, gen); if (randPct > 10) { // 90% of time i_data isa random string of length [26 .. // 50] stock.s_data = TPCCUtil.randomStr(len); } else { // 10% of time i_data has "ORIGINAL" crammed somewhere // in middle startORIGINAL = TPCCUtil.randomNumber(2, (len - 8), gen); stock.s_data = TPCCUtil.randomStr(startORIGINAL - 1) + "ORIGINAL" + TPCCUtil.randomStr(len - startORIGINAL - 9); } stock.s_dist_01 = TPCCUtil.randomStr(24); stock.s_dist_02 = TPCCUtil.randomStr(24); stock.s_dist_03 = TPCCUtil.randomStr(24); stock.s_dist_04 = TPCCUtil.randomStr(24); stock.s_dist_05 = TPCCUtil.randomStr(24); stock.s_dist_06 = TPCCUtil.randomStr(24); stock.s_dist_07 = TPCCUtil.randomStr(24); stock.s_dist_08 = TPCCUtil.randomStr(24); stock.s_dist_09 = TPCCUtil.randomStr(24); stock.s_dist_10 = TPCCUtil.randomStr(24); k++; if (outputFiles == false) { stckPrepStmt.setLong(1, stock.s_w_id); stckPrepStmt.setLong(2, stock.s_i_id); stckPrepStmt.setLong(3, stock.s_quantity); stckPrepStmt.setDouble(4, stock.s_ytd); stckPrepStmt.setLong(5, stock.s_order_cnt); stckPrepStmt.setLong(6, stock.s_remote_cnt); stckPrepStmt.setString(7, stock.s_data); stckPrepStmt.setString(8, stock.s_dist_01); stckPrepStmt.setString(9, stock.s_dist_02); stckPrepStmt.setString(10, stock.s_dist_03); stckPrepStmt.setString(11, stock.s_dist_04); stckPrepStmt.setString(12, stock.s_dist_05); stckPrepStmt.setString(13, stock.s_dist_06); stckPrepStmt.setString(14, stock.s_dist_07); stckPrepStmt.setString(15, stock.s_dist_08); stckPrepStmt.setString(16, stock.s_dist_09); stckPrepStmt.setString(17, stock.s_dist_10); stckPrepStmt.addBatch(); if ((k % configCommitCount) == 0) { long tmpTime = new java.util.Date().getTime(); String etStr = " Elasped Time(ms): " + ((tmpTime - lastTimeMS) / 1000.000) + " "; LOG.debug(etStr.substring(0, 30) + " Writing record " + k + " of " + t); lastTimeMS = tmpTime; stckPrepStmt.executeBatch(); stckPrepStmt.clearBatch(); transCommit(); } } else { String str = ""; str = str + stock.s_i_id + ","; str = str + stock.s_w_id + ","; str = str + stock.s_quantity + ","; str = str + stock.s_ytd + ","; str = str + stock.s_order_cnt + ","; str = str + stock.s_remote_cnt + ","; str = str + stock.s_data + ","; str = str + stock.s_dist_01 + ","; str = str + stock.s_dist_02 + ","; str = str + stock.s_dist_03 + ","; str = str + stock.s_dist_04 + ","; str = str + stock.s_dist_05 + ","; str = str + stock.s_dist_06 + ","; str = str + stock.s_dist_07 + ","; str = str + stock.s_dist_08 + ","; str = str + stock.s_dist_09 + ","; str = str + stock.s_dist_10; out.println(str); if ((k % configCommitCount) == 0) { long tmpTime = new java.util.Date().getTime(); String etStr = " Elasped Time(ms): " + ((tmpTime - lastTimeMS) / 1000.000) + " "; LOG.debug(etStr.substring(0, 30) + " Writing record " + k + " of " + t); lastTimeMS = tmpTime; } } } // end for [w] } // end for [i] long tmpTime = new java.util.Date().getTime(); String etStr = " Elasped Time(ms): " + ((tmpTime - lastTimeMS) / 1000.000) + " "; LOG.debug(etStr.substring(0, 30) + " Writing final records " + k + " of " + t); lastTimeMS = tmpTime; if (outputFiles == false) { stckPrepStmt.executeBatch(); } transCommit(); now = new java.util.Date(); LOG.debug("End Stock Load @ " + now); } catch (SQLException se) { LOG.debug(se.getMessage()); transRollback(); } catch (Exception e) { e.printStackTrace(); transRollback(); } return (k); }
From source file:org.wso2.carbon.identity.application.mgt.dao.impl.ApplicationDAOImpl.java
/** * @param applicationId/* w w w. java 2 s. c om*/ * @param outBoundProvisioningConfig * @param connection * @throws SQLException */ private void updateOutboundProvisioningConfiguration(int applicationId, OutboundProvisioningConfig outBoundProvisioningConfig, Connection connection) throws SQLException { int tenantID = CarbonContext.getThreadLocalCarbonContext().getTenantId(); PreparedStatement outboundProConfigPrepStmt = null; IdentityProvider[] proProviders = outBoundProvisioningConfig.getProvisioningIdentityProviders(); try { if (outBoundProvisioningConfig == null || proProviders == null || proProviders.length == 0) { // no in-bound authentication requests defined. return; } outboundProConfigPrepStmt = connection.prepareStatement(ApplicationMgtDBQueries.STORE_PRO_CONNECTORS); // TENANT_ID, IDP_NAME, CONNECTOR_NAME, APP_ID for (IdentityProvider proProvider : proProviders) { if (proProvider != null) { ProvisioningConnectorConfig proConnector = proProvider.getDefaultProvisioningConnectorConfig(); if (proConnector == null) { continue; } String jitEnabled = "0"; if (proProvider.getJustInTimeProvisioningConfig() != null && proProvider.getJustInTimeProvisioningConfig().isProvisioningEnabled()) { jitEnabled = "1"; } String blocking = "0"; if (proProvider.getDefaultProvisioningConnectorConfig() != null && proProvider.getDefaultProvisioningConnectorConfig().isBlocking()) { blocking = "1"; } outboundProConfigPrepStmt.setInt(1, tenantID); outboundProConfigPrepStmt.setString(2, CharacterEncoder.getSafeText(proProvider.getIdentityProviderName())); outboundProConfigPrepStmt.setString(3, CharacterEncoder.getSafeText(proConnector.getName())); outboundProConfigPrepStmt.setInt(4, applicationId); outboundProConfigPrepStmt.setString(5, CharacterEncoder.getSafeText(jitEnabled)); outboundProConfigPrepStmt.setString(6, CharacterEncoder.getSafeText(blocking)); outboundProConfigPrepStmt.addBatch(); } } outboundProConfigPrepStmt.executeBatch(); } finally { IdentityApplicationManagementUtil.closeStatement(outboundProConfigPrepStmt); } }
From source file:org.wso2.carbon.identity.application.mgt.dao.impl.ApplicationDAOImpl.java
/** * @param applicationId/*from w w w. java 2 s . c o m*/ * @param inBoundAuthenticationConfig * @param connection * @throws SQLException */ private void updateInboundAuthRequestConfiguration(int applicationId, InboundAuthenticationConfig inBoundAuthenticationConfig, Connection connection) throws SQLException { int tenantID = CarbonContext.getThreadLocalCarbonContext().getTenantId(); PreparedStatement inboundAuthReqConfigPrepStmt = null; try { if (inBoundAuthenticationConfig == null || inBoundAuthenticationConfig.getInboundAuthenticationRequestConfigs() == null || inBoundAuthenticationConfig.getInboundAuthenticationRequestConfigs().length == 0) { // no in-bound authentication requests defined. return; } inboundAuthReqConfigPrepStmt = connection.prepareStatement(ApplicationMgtDBQueries.STORE_CLIENT_INFO); InboundAuthenticationRequestConfig[] authRequests = inBoundAuthenticationConfig .getInboundAuthenticationRequestConfigs(); for (InboundAuthenticationRequestConfig authRequest : authRequests) { if (authRequest == null || authRequest.getInboundAuthKey() == null || authRequest.getInboundAuthType() == null) { log.warn("Invalid in-bound authentication request"); // not a valid authentication request. Must have client and a type. continue; } // TENANT_ID, INBOUND_AUTH_KEY,INBOUND_AUTH_TYPE,PROP_NAME, PROP_VALUE, APP_ID Property[] properties = authRequest.getProperties(); if (properties != null && properties.length > 0) { for (Property prop : properties) { inboundAuthReqConfigPrepStmt.setInt(1, tenantID); inboundAuthReqConfigPrepStmt.setString(2, CharacterEncoder.getSafeText(authRequest.getInboundAuthKey())); inboundAuthReqConfigPrepStmt.setString(3, CharacterEncoder.getSafeText(authRequest.getInboundAuthType())); inboundAuthReqConfigPrepStmt.setString(4, CharacterEncoder.getSafeText(prop.getName())); inboundAuthReqConfigPrepStmt.setString(5, CharacterEncoder.getSafeText(prop.getValue())); inboundAuthReqConfigPrepStmt.setInt(6, applicationId); inboundAuthReqConfigPrepStmt.addBatch(); } } else { inboundAuthReqConfigPrepStmt.setInt(1, tenantID); inboundAuthReqConfigPrepStmt.setString(2, CharacterEncoder.getSafeText(authRequest.getInboundAuthKey())); inboundAuthReqConfigPrepStmt.setString(3, CharacterEncoder.getSafeText(authRequest.getInboundAuthType())); inboundAuthReqConfigPrepStmt.setString(4, null); inboundAuthReqConfigPrepStmt.setString(5, null); inboundAuthReqConfigPrepStmt.setInt(6, applicationId); inboundAuthReqConfigPrepStmt.addBatch(); } if (debugMode) { log.debug("Updating inbound authentication request configuration of the application " + applicationId + "inbound auth key: " + authRequest.getInboundAuthKey() + " inbound auth type: " + authRequest.getInboundAuthType()); } } inboundAuthReqConfigPrepStmt.executeBatch(); } finally { IdentityApplicationManagementUtil.closeStatement(inboundAuthReqConfigPrepStmt); } }
From source file:org.jamwiki.db.AnsiQueryHandler.java
/** * *//*w w w .j a va 2s .c o m*/ public void updateNamespaceTranslations(List<Namespace> namespaces, String virtualWiki, int virtualWikiId, Connection conn) throws SQLException { PreparedStatement stmt = null; try { // delete any existing translation then add the new one stmt = conn.prepareStatement(STATEMENT_DELETE_NAMESPACE_TRANSLATIONS); stmt.setInt(1, virtualWikiId); stmt.executeUpdate(); } finally { DatabaseConnection.closeStatement(stmt); } try { stmt = conn.prepareStatement(STATEMENT_INSERT_NAMESPACE_TRANSLATION); String translatedNamespace; for (Namespace namespace : namespaces) { translatedNamespace = namespace.getLabel(virtualWiki); if (translatedNamespace.equals(namespace.getDefaultLabel())) { continue; } stmt.setInt(1, namespace.getId()); stmt.setInt(2, virtualWikiId); stmt.setString(3, translatedNamespace); stmt.addBatch(); } stmt.executeBatch(); } finally { DatabaseConnection.closeStatement(stmt); } }
From source file:com.flexive.core.storage.genericSQL.GenericHierarchicalStorage.java
/** * Update all (multiple) ACL entries for a content instance * * @param con an open and valid connection * @param content the content containing the ACL'S * @param pk primary key of the content * @param newEntry is this a new entry?//from w ww. j a va 2s . c o m * @throws SQLException on errors * @throws FxCreateException on errors * @throws FxUpdateException on errors */ protected void updateACLEntries(Connection con, FxContent content, FxPK pk, boolean newEntry) throws SQLException, FxCreateException, FxUpdateException { PreparedStatement ps = null; try { if (content.getAclIds().isEmpty() || (content.getAclIds().size() == 1 && content.getAclIds().get(0) == ACL.NULL_ACL_ID)) { if (newEntry) { throw new FxCreateException(LOG, "ex.content.noACL", pk); } else { throw new FxUpdateException(LOG, "ex.content.noACL", pk); } } if (!newEntry) { // first remove all ACLs, then update them ps = con.prepareStatement(CONTENT_ACLS_CLEAR); ps.setLong(1, pk.getId()); ps.setInt(2, pk.getVersion()); ps.executeUpdate(); } final List<Long> aclIds = content.getAclIds(); if (aclIds.size() <= 1) { return; // ACL saved in main table } //insert ACLs ps = con.prepareStatement(CONTENT_ACL_INSERT); for (long aclId : aclIds) { ps.setLong(1, pk.getId()); ps.setInt(2, pk.getVersion()); ps.setLong(3, aclId); ps.addBatch(); } ps.executeBatch(); } finally { Database.closeObjects(GenericHierarchicalStorage.class, null, ps); } }
From source file:com.flexive.core.storage.genericSQL.GenericHierarchicalStorage.java
/** * Update a properties data and/or position or a groups position * * @param change the change applied//ww w . j a v a 2 s .c om * @param prop the property unless change is a group change * @param allData all content data unless change is a group change * @param con an open and valid connection * @param ps batch prepared statement for detail updates * @param pk primary key * @param data property data unless change is a group change * @throws SQLException on errors * @throws FxDbException on errors * @throws FxUpdateException on errors * @throws FxNoAccessException for FxNoAccess values */ protected void updatePropertyData(FxDelta.FxDeltaChange change, FxProperty prop, List<FxData> allData, Connection con, PreparedStatement ps, FxPK pk, FxPropertyData data) throws SQLException, FxDbException, FxUpdateException, FxNoAccessException { if ((change.isProperty() && (data == null || data.isEmpty() || data.getPropertyAssignment().isFlatStorageEntry())) || !(change.isDataChange() || change.isPositionChange())) return; clearPreparedStatement(ps, 1, UPDATE_ID_POS); ps.setLong(3, 0); //FSELECT has to be set to 0 and not null! if (change.isPositionChange()) ps.setInt(UPDATE_POS_POS, change.getNewData().getPos()); else ps.setInt(UPDATE_POS_POS, change.getOriginalData().getPos()); ps.setLong(UPDATE_ID_POS, pk.getId()); ps.setInt(UPDATE_ID_POS + 1, pk.getVersion()); ps.setLong(UPDATE_ID_POS + 3, change.getNewData().getAssignmentId()); ps.setString(UPDATE_ID_POS + 4, FxArrayUtils.toStringArray(change.getNewData().getIndices(), ',')); if (change.isGroup()) { ps.setInt(UPDATE_ID_POS + 2, (int) FxLanguage.SYSTEM_ID); ps.setBoolean(UPDATE_MLDEF_POS, true); if (batchContentDataChanges()) ps.addBatch(); else ps.executeUpdate(); return; } if (change.isPositionChange() && !change.isDataChange()) { //just update positions assert data != null; for (long lang : data.getValue().getTranslatedLanguages()) { ps.setInt(UPDATE_ID_POS + 2, (int) lang); setPropertyData(false, prop, allData, con, data, ps, null, getUppercaseColumnPos(prop, false), false); } return; } setPropertyData(false, prop, allData, con, data, ps, null, getUppercaseColumnPos(prop, false), true); }
From source file:com.flexive.ejb.beans.structure.TypeEngineBean.java
/** * Create a new type/*from w ww. ja v a2 s . co m*/ * * @param type the type to create * @return id of the new type * @throws FxApplicationException on errors */ private long create(FxTypeEdit type) throws FxApplicationException { 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.create.nameMissing"); if (!type.getStorageMode().isSupported()) throw new FxInvalidParameterException("STORAGEMODE", "ex.structure.typeStorageMode.notSupported", type.getStorageMode().getLabel().getBestTranslation(ticket)); if (type.getACL().getCategory() != ACLCategory.STRUCTURE) throw new FxInvalidParameterException("aclId", "ex.acl.category.invalid", type.getACL().getCategory().name(), ACLCategory.STRUCTURE.name()); if (type.hasDefaultInstanceACL() && type.getDefaultInstanceACL().getCategory() != ACLCategory.INSTANCE) throw new FxInvalidParameterException("DEFACL", "ex.acl.category.invalid", type.getDefaultInstanceACL().getCategory(), ACLCategory.INSTANCE); Connection con = null; PreparedStatement ps = null; long newId = seq.getId(FxSystemSequencer.TYPEDEF); final long NOW = System.currentTimeMillis(); try { con = Database.getDbConnection(); ps = con.prepareStatement(TYPE_CREATE); ps.setLong(1, newId); ps.setString(2, type.getName()); if (type.getParent() != null) ps.setLong(3, type.getParent().getId()); else ps.setNull(3, java.sql.Types.INTEGER); ps.setInt(4, type.getStorageMode().getId()); ps.setInt(5, type.getCategory().getId()); ps.setInt(6, type.getMode().getId()); ps.setInt(7, type.getLanguage().getId()); ps.setInt(8, type.getState().getId()); ps.setByte(9, type.getBitCodedPermissions()); ps.setBoolean(10, type.isTrackHistory()); ps.setLong(11, type.getHistoryAge()); ps.setLong(12, type.getMaxVersions()); ps.setInt(13, type.getMaxRelSource()); ps.setInt(14, type.getMaxRelDestination()); ps.setLong(15, ticket.getUserId()); ps.setLong(16, NOW); ps.setLong(17, ticket.getUserId()); ps.setLong(18, NOW); ps.setLong(19, type.getACL().getId()); ps.setLong(20, type.getWorkflow().getId()); if (type.getIcon().isEmpty()) ps.setNull(21, java.sql.Types.INTEGER); else ps.setLong(21, type.getIcon().getDefaultTranslation().getId()); ps.setBoolean(22, type.isUseInstancePermissions() && type.isMultipleContentACLs()); ps.setBoolean(23, type.isIncludedInSupertypeQueries()); if (type.hasDefaultInstanceACL()) ps.setLong(24, type.getDefaultInstanceACL().getId()); else ps.setNull(24, java.sql.Types.INTEGER); ps.setBoolean(25, type.isAutoVersion()); ps.executeUpdate(); Database.storeFxString(type.getLabel(), con, TBL_STRUCT_TYPES, "DESCRIPTION", "ID", newId); StructureLoader.reload(con); FxType thisType = CacheAdmin.getEnvironment().getType(newId); htracker.track(thisType, "history.type.create", type.getName(), newId); //store relations ps.close(); if (type.getAddedRelations().size() > 0) { ps = con.prepareStatement("INSERT INTO " + TBL_STRUCT_TYPERELATIONS + " (TYPEDEF,TYPESRC,TYPEDST,MAXSRC,MAXDST)VALUES(?,?,?,?,?)"); ps.setLong(1, thisType.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(thisType, "history.type.create.relation.add", type.getName(), rel.getSource().getName(), rel.getMaxSource(), rel.getDestination().getName(), rel.getMaxDestination()); } ps.executeBatch(); } if (type.getParent() == null) { for (FxPropertyAssignment spa : environment.getSystemInternalRootPropertyAssignments()) { final FxPropertyAssignmentEdit derived = FxPropertyAssignmentEdit.createNew(spa, thisType, spa.getAlias(), "/"); assignmentEngine.save( updateAclAssignmentMultiplicity(type, derived).setEnabled(true)._setSystemInternal(), false); } } else { //create parent assignments List<FxAssignment> parentAssignments = type.getParent().getConnectedAssignments("/"); for (FxAssignment as : parentAssignments) { if (as instanceof FxPropertyAssignment) { FxPropertyAssignmentEdit pae = FxPropertyAssignmentEdit.createNew((FxPropertyAssignment) as, thisType, as.getAlias(), "/"); pae.setEnabled(type.isEnableParentAssignments()); assignmentEngine.save(updateAclAssignmentMultiplicity(type, pae), false); } else if (as instanceof FxGroupAssignment) { FxGroupAssignmentEdit pge = FxGroupAssignmentEdit.createNew((FxGroupAssignment) as, thisType, as.getAlias(), "/"); pge.setEnabled(type.isEnableParentAssignments()); assignmentEngine.save(pge, true); } } } // store structure options storeTypeOptions(con, TBL_STRUCT_TYPES_OPTIONS, "ID", newId, type.getOptions(), false); StructureLoader.reload(con); } catch (SQLException e) { if (StorageManager.isUniqueConstraintViolation(e)) { EJBUtils.rollback(ctx); throw new FxCreateException("ex.structure.type.exists", type.getName()); } EJBUtils.rollback(ctx); throw new FxCreateException(LOG, e, "ex.db.sqlError", e.getMessage()); } catch (FxCacheException e) { EJBUtils.rollback(ctx); throw new FxCreateException(LOG, e, "ex.cache", e.getMessage()); } catch (FxLoadException e) { EJBUtils.rollback(ctx); throw new FxCreateException(e); } catch (FxNotFoundException e) { EJBUtils.rollback(ctx); throw new FxCreateException(e); } catch (FxEntryExistsException e) { EJBUtils.rollback(ctx); throw new FxCreateException(e); } catch (FxUpdateException e) { EJBUtils.rollback(ctx); throw new FxCreateException(e); } finally { Database.closeObjects(TypeEngineBean.class, con, ps); } return newId; }
From source file:com.flexive.core.storage.genericSQL.GenericSQLFulltextIndexer.java
/** * {@inheritDoc}//from w w w .j a v a 2 s. c om */ @Override public void rebuildIndexForProperty(long propertyId) { PreparedStatement ps = null; Statement stmtAssignment = null; Statement stmtFetch = null; try { final DBStorage storage = StorageManager.getStorageImpl(); final ContentStorage contentStorage = storage.getContentStorage(TypeStorageMode.Hierarchical); final String TRUE = storage.getBooleanTrueExpression(); stmtAssignment = con.createStatement(); stmtFetch = con.createStatement(); // 1 2 3 4 5 6 7 8 9 10 11 ResultSet rs = stmtAssignment.executeQuery( "SELECT a.id, p.datatype, m.typeid, m.tblname, m.colname, m.lvl, a.xpath, p.id, p.sysinternal, s.tbltype, m.group_assid " + "FROM " + DatabaseConst.TBL_STRUCT_PROPERTIES + " p, " + DatabaseConst.TBL_STRUCT_ASSIGNMENTS + " a LEFT JOIN " + DatabaseConst.TBL_STRUCT_FLATSTORE_MAPPING + " m ON (m.assid=a.id) LEFT JOIN " + DatabaseConst.TBL_STRUCT_FLATSTORE_INFO + " s ON (m.tblname=s.tblname)" + "WHERE p.ISFULLTEXTINDEXED=" + TRUE + " AND a.APROPERTY=p.id" + (propertyId >= 0 ? " AND p.id=" + propertyId : "") + " ORDER BY p.datatype"); //1..ID,2..VER,3..LANG,4..ASSIGN,5..XMULT,6..VALUE ps = con.prepareStatement(getInsertSql()); int batchCounter; long totalCount = 0; while (rs != null && rs.next()) { long assignmentId = rs.getLong(1); ps.setLong(4, assignmentId); //assignment id long currentPropertyId = rs.getLong(8); FxDataType dataType = FxDataType.getById(rs.getInt(2)); boolean systemInternalProperty = rs.getBoolean(9); if (systemInternalProperty || !dataType.isTextType()) continue; //do not index system internal properties or non-text properties long flatTypeId = rs.getLong(3); if (!rs.wasNull()) { //flatstorage String xpath = rs.getString(7); final String xmult; int xpathDepth = 1; if (!StringUtils.isEmpty(xpath)) { xpathDepth = XPathElement.getDepth(xpath); xmult = XPathElement.addDefaultIndices("", xpathDepth); } else { xmult = "1"; } ps.setString(5, xmult); final FxFlatStorageInfo.Type flatType = FxFlatStorageInfo.Type.forId(rs.getInt(10)); // 1 2 3 4 ResultSet rsData = stmtFetch.executeQuery("SELECT ID,VER,LANG," + rs.getString(5) + // 5 (flatType == TypeGroups ? ",XMULT" : "") + " FROM " + rs.getString(4) + " WHERE TYPEID=" + flatTypeId + " AND LVL=" + rs.getInt(6) + (flatType == TypeGroups ? " AND GROUP_ASSID=" + rs.getLong(11) : "")); batchCounter = 0; while (rsData != null && rsData.next()) { String value = rsData.getString(4); if (rsData.wasNull() || StringUtils.isEmpty(value)) continue; ps.setLong(1, rsData.getLong(1)); ps.setInt(2, rsData.getInt(2)); ps.setInt(3, rsData.getInt(3)); if (flatType == TypeGroups) { // xmult is based on the base group's xmult final String groupXMult = rsData.getString(5); final int missingIndices = Math.max(0, xpathDepth - XPathElement.getDepth(groupXMult)); ps.setString(5, XPathElement.addDefaultIndices(groupXMult, missingIndices)); } ps.setString(6, value.trim().toUpperCase()); ps.addBatch(); batchCounter++; totalCount++; if (batchCounter % 1000 == 0) ps.executeBatch(); //insert every 1000 rows } if (rsData != null) rsData.close(); ps.executeBatch(); } else { //hierarchical storage String[] columns = contentStorage.getColumns(currentPropertyId, systemInternalProperty, dataType); final String columnSelect = dataType == FxDataType.Binary ? "b.XMLMETA" : "d." + columns[0]; //first column is the one responsible for indexing final String tableAppend = dataType == FxDataType.Binary ? "," + DatabaseConst.TBL_CONTENT_BINARY + " b" : ""; final String whereAppend = dataType == FxDataType.Binary ? " AND b.ID=d." + columns[0] + " AND b.VER=1 AND b.QUALITY=1" : ""; // 1 2 3 4 5 ResultSet rsData = stmtFetch.executeQuery("SELECT d.ID,d.VER,d.LANG,d.XMULT," + columnSelect + " FROM " + DatabaseConst.TBL_CONTENT_DATA + " d" + tableAppend + " WHERE d.ASSIGN=" + assignmentId + whereAppend); batchCounter = 0; while (rsData != null && rsData.next()) { String value = rsData.getString(4); if (rsData.wasNull() || StringUtils.isEmpty(value)) continue; ps.setLong(1, rsData.getLong(1)); ps.setInt(2, rsData.getInt(2)); ps.setInt(3, rsData.getInt(3)); ps.setString(5, rsData.getString(4)); final String ftValue; switch (dataType) { case Binary: final String xmlMeta = rsData.getString(5); ftValue = FxXMLUtils.getElementData(xmlMeta, "text"); break; case HTML: case String1024: case Text: ftValue = rsData.getString(5); break; default: ftValue = null; } if (!StringUtils.isEmpty(ftValue)) { ps.setString(6, ftValue.trim().toUpperCase()); ps.addBatch(); batchCounter++; totalCount++; } if (batchCounter % 1000 == 0) ps.executeBatch(); //insert every 1000 rows } if (rsData != null) rsData.close(); ps.executeBatch(); } } LOG.info("Added " + totalCount + " entries to fulltext index."); commitChanges(); } catch (SQLException e) { throw new FxDbException(e, "ex.db.sqlError", e.getMessage()).asRuntimeException(); } catch (FxNotFoundException e) { //ContentStorage was not found throw e.asRuntimeException(); } finally { Database.closeObjects(GenericSQLFulltextIndexer.class, ps, stmtAssignment, stmtFetch); } }
From source file:org.hyperic.hq.measurement.server.session.DataManagerImpl.java
/** * This method inserts data into the data table. If any data points in the * list fail to get added (e.g. because of a constraint violation), it will * be returned in the result list./*w w w. j av a2s . c om*/ * * @param conn The connection. * @param data The data points to insert. * @param continueOnSQLException <code>true</code> to continue inserting the * rest of the data points even after a <code>SQLException</code> * occurs; <code>false</code> to throw the <code>SQLException</code>. * @return The list of data points that were not inserted. * @throws SQLException only if there is an exception for one of the data * point batch inserts and <code>continueOnSQLException</code> is * set to <code>false</code>. */ private List<DataPoint> insertData(Connection conn, List<DataPoint> data, boolean continueOnSQLException) throws SQLException { PreparedStatement stmt = null; final List<DataPoint> left = new ArrayList<DataPoint>(); final Map<String, List<DataPoint>> buckets = MeasRangeObj.getInstance().bucketData(data); final HQDialect dialect = measurementDAO.getHQDialect(); final boolean supportsDupInsStmt = dialect.supportsDuplicateInsertStmt(); final boolean supportsPLSQL = dialect.supportsPLSQL(); final StringBuilder buf = new StringBuilder(); for (final Entry<String, List<DataPoint>> entry : buckets.entrySet()) { buf.setLength(0); final String table = entry.getKey(); final List<DataPoint> dpts = entry.getValue(); try { if (supportsDupInsStmt) { stmt = conn.prepareStatement(buf.append("INSERT INTO ").append(table) .append(" (measurement_id, timestamp, value) VALUES (?, ?, ?)") .append(" ON DUPLICATE KEY UPDATE value = ?").toString()); } else if (supportsPLSQL) { final String sql = PLSQL.replaceAll(":table", table); stmt = conn.prepareStatement(sql); } else { stmt = conn.prepareStatement(buf.append("INSERT INTO ").append(table) .append(" (measurement_id, timestamp, value) VALUES (?, ?, ?)").toString()); } // TODO need to set synchronous commit to off for (DataPoint pt : dpts) { Integer metricId = pt.getMeasurementId(); MetricValue val = pt.getMetricValue(); BigDecimal bigDec; bigDec = new BigDecimal(val.getValue()); stmt.setInt(1, metricId.intValue()); stmt.setLong(2, val.getTimestamp()); stmt.setBigDecimal(3, getDecimalInRange(bigDec, metricId)); if (supportsDupInsStmt) { stmt.setBigDecimal(4, getDecimalInRange(bigDec, metricId)); } else if (supportsPLSQL) { stmt.setBigDecimal(4, getDecimalInRange(bigDec, metricId)); stmt.setLong(5, val.getTimestamp()); stmt.setInt(6, metricId.intValue()); } stmt.addBatch(); } int[] execInfo = stmt.executeBatch(); left.addAll(getRemainingDataPoints(dpts, execInfo)); } catch (BatchUpdateException e) { if (!continueOnSQLException) { throw e; } left.addAll(getRemainingDataPointsAfterBatchFail(dpts, e.getUpdateCounts())); } catch (SQLException e) { if (!continueOnSQLException) { throw e; } // If the batch insert is not within a transaction, then we // don't know which of the inserts completed successfully. // Assume they all failed. left.addAll(dpts); if (log.isDebugEnabled()) { log.debug("A general SQLException occurred during the insert. " + "Assuming that none of the " + dpts.size() + " data points were inserted.", e); } } finally { DBUtil.closeStatement(LOG_CTX, stmt); } } return left; }
From source file:com.flexive.ejb.beans.structure.AssignmentEngineBean.java
/** * Remove an assignment// w ww.j a v a 2 s . c om * * @param assignmentId assignment to remove * @param removeSubAssignments if assignment is a group, remove all attached properties and groups? * @param removeDerivedAssignments if derivates of this assignment in derived types exist, remove them as well? * @param disableAssignment if false, find all derived assignments, flag them as 'regular' assignments and set them as new base * @param allowDerivedRemoval allow removal of derived assignments * @throws FxApplicationException on errors */ private void removeAssignment(long assignmentId, boolean removeSubAssignments, boolean removeDerivedAssignments, boolean disableAssignment, boolean allowDerivedRemoval) throws FxApplicationException { final UserTicket ticket = FxContext.getUserTicket(); FxPermissionUtils.checkRole(ticket, Role.StructureManagement); FxAssignment assignment; assignment = CacheAdmin.getEnvironment().getAssignment(assignmentId); assert assignment != null : "Assignment retrieved was null"; if (!disableAssignment) { //if removal, check if its a derived assignment which may not be removed if (!allowDerivedRemoval && assignment.isDerivedAssignment()) throw new FxRemoveException("ex.structure.assignment.delete.derived", assignment.getXPath()); } Connection con = null; PreparedStatement ps = null; StringBuilder sql = new StringBuilder(500); try { con = Database.getDbConnection(); List<FxAssignment> affectedAssignments = new ArrayList<FxAssignment>(10); affectedAssignments.add(assignment); if (assignment instanceof FxGroupAssignment && removeSubAssignments) { FxGroupAssignment ga = (FxGroupAssignment) assignment; _addSubAssignments(affectedAssignments, ga); } if (removeDerivedAssignments) { //find all derived assignments sql.append("SELECT ID FROM ").append(TBL_STRUCT_ASSIGNMENTS).append(" WHERE BASE=?"); ps = con.prepareStatement(sql.toString()); long prevSize = 0; while (prevSize != affectedAssignments.size()) { //run until no derived assignments are found prevSize = affectedAssignments.size(); List<FxAssignment> adds = new ArrayList<FxAssignment>(5); for (FxAssignment check : affectedAssignments) { ps.setLong(1, check.getId()); ResultSet rs = ps.executeQuery(); if (rs != null && rs.next()) { FxAssignment derived = CacheAdmin.getEnvironment().getAssignment(rs.getLong(1)); if (!adds.contains(derived) && !affectedAssignments.contains(derived)) adds.add(derived); } } affectedAssignments.addAll(adds); } ps.close(); sql.setLength(0); } else if (!disableAssignment) { //find all (directly) derived assignments, flag them as 'regular' assignments and set them as new base breakAssignmentInheritance(con, sql, affectedAssignments.toArray(new FxAssignment[affectedAssignments.size()])); } //security checks if (!ticket.isGlobalSupervisor()) { //assignment permission StringBuilder assignmentList = new StringBuilder(200); for (FxAssignment check : affectedAssignments) { assignmentList.append(",").append(check.getId()); if (check instanceof FxPropertyAssignment && check.getAssignedType().isUsePropertyPermissions()) { FxPropertyAssignment pa = (FxPropertyAssignment) check; if (!ticket.mayDeleteACL(pa.getACL().getId(), 0/*owner is irrelevant here*/)) throw new FxNoAccessException("ex.acl.noAccess.delete", pa.getACL().getName()); } } //affected content permission sql.append("SELECT DISTINCT O.ACL FROM ").append(TBL_CONTENT) .append(" O WHERE O.ID IN(SELECT D.ID FROM ").append(TBL_CONTENT_DATA) .append(" D WHERE D.ASSIGN IN(").append(assignmentList.substring(1)).append("))"); java.lang.System.out.println("SQL==" + sql.toString()); ps = con.prepareStatement(sql.toString()); sql.setLength(0); ResultSet rs = ps.executeQuery(); while (rs != null && rs.next()) { if (!ticket.mayDeleteACL(rs.getInt(1), 0/*owner is irrelevant here*/)) throw new FxNoAccessException("ex.acl.noAccess.delete", CacheAdmin.getEnvironment().getACL(rs.getInt(1))); } ps.close(); } if (disableAssignment) sql.append("UPDATE ").append(TBL_STRUCT_ASSIGNMENTS).append(" SET ENABLED=? WHERE ID=?"); else sql.append("DELETE FROM ").append(TBL_STRUCT_ASSIGNMENTS).append(" WHERE ID=?"); ps = con.prepareStatement(sql.toString()); //batch remove all multi language entries and content datas PreparedStatement psML = null; PreparedStatement psData = null; PreparedStatement psDataFT = null; PreparedStatement psBinaryGet = null; PreparedStatement psBinaryRemove = null; PreparedStatement psPropertyOptionRemove = null; PreparedStatement psGroupOptionRemove = null; try { sql.setLength(0); sql.append("DELETE FROM ").append(TBL_STRUCT_ASSIGNMENTS).append(ML).append(" WHERE ID=?"); psML = con.prepareStatement(sql.toString()); sql.setLength(0); sql.append("DELETE FROM ").append(TBL_STRUCT_PROPERTY_OPTIONS).append(" WHERE ASSID=?"); psPropertyOptionRemove = con.prepareStatement(sql.toString()); sql.setLength(0); sql.append("DELETE FROM ").append(TBL_STRUCT_GROUP_OPTIONS).append(" WHERE ASSID=?"); psGroupOptionRemove = con.prepareStatement(sql.toString()); sql.setLength(0); sql.append("DELETE FROM ").append(TBL_CONTENT_DATA).append(" WHERE ASSIGN=?"); psData = con.prepareStatement(sql.toString()); sql.setLength(0); sql.append("DELETE FROM ").append(TBL_CONTENT_DATA_FT).append(" WHERE ASSIGN=?"); psDataFT = con.prepareStatement(sql.toString()); sql.setLength(0); sql.append("SELECT DISTINCT FBLOB FROM ").append(TBL_CONTENT_DATA) .append(" WHERE ASSIGN=? AND FBLOB IS NOT NULL"); psBinaryGet = con.prepareStatement(sql.toString()); sql.setLength(0); sql.append("DELETE FROM ").append(TBL_CONTENT_BINARY).append(" WHERE ID=?"); psBinaryRemove = con.prepareStatement(sql.toString()); for (FxAssignment ml : affectedAssignments) { if (!disableAssignment) { psML.setLong(1, ml.getId()); psML.addBatch(); } psData.setLong(1, ml.getId()); psData.addBatch(); if (ml instanceof FxPropertyAssignment) { if (!disableAssignment) { if (((FxPropertyAssignment) ml).isFlatStorageEntry()) FxFlatStorageManager.getInstance().removeAssignmentMappings(con, ml.getId()); } psDataFT.setLong(1, ml.getId()); psDataFT.addBatch(); psPropertyOptionRemove.setLong(1, ml.getId()); psPropertyOptionRemove.addBatch(); //only need to remove binaries if its a binary type... switch (((FxPropertyAssignment) ml).getProperty().getDataType()) { case Binary: psBinaryGet.setLong(1, ml.getId()); ResultSet rs = psBinaryGet.executeQuery(); while (rs != null && rs.next()) { psBinaryRemove.setLong(1, rs.getLong(1)); psBinaryRemove.addBatch(); } } } else if (ml instanceof FxGroupAssignment) { psGroupOptionRemove.setLong(1, ml.getId()); psGroupOptionRemove.addBatch(); } } if (!disableAssignment) { psML.executeBatch(); psPropertyOptionRemove.executeBatch(); psGroupOptionRemove.executeBatch(); psBinaryRemove.executeBatch(); psDataFT.executeBatch(); psData.executeBatch(); } } finally { Database.closeObjects(AssignmentEngineBean.class, null, psML); Database.closeObjects(AssignmentEngineBean.class, null, psData); Database.closeObjects(AssignmentEngineBean.class, null, psDataFT); Database.closeObjects(AssignmentEngineBean.class, null, psBinaryGet); Database.closeObjects(AssignmentEngineBean.class, null, psBinaryRemove); Database.closeObjects(AssignmentEngineBean.class, null, psGroupOptionRemove); Database.closeObjects(AssignmentEngineBean.class, null, psPropertyOptionRemove); } if (disableAssignment) ps.setBoolean(1, false); if (affectedAssignments.size() > 1) affectedAssignments = FxStructureUtils.resolveRemoveDependencies(affectedAssignments); for (FxAssignment rm : affectedAssignments) { ps.setLong(disableAssignment ? 2 : 1, rm.getId()); ps.executeUpdate(); } FxStructureUtils.removeOrphanedProperties(con); FxStructureUtils.removeOrphanedGroups(con); StructureLoader.reload(con); htracker.track(assignment.getAssignedType(), disableAssignment ? "history.assignment.remove" : "history.assignment.disable", assignment.getXPath(), assignmentId, removeSubAssignments, removeDerivedAssignments); } catch (SQLException e) { EJBUtils.rollback(ctx); throw new FxRemoveException(LOG, e, "ex.db.sqlError", e.getMessage()); } catch (FxCacheException e) { EJBUtils.rollback(ctx); throw new FxRemoveException(LOG, e, "ex.cache", e.getMessage()); } catch (FxLoadException e) { EJBUtils.rollback(ctx); throw new FxRemoveException(e); } finally { Database.closeObjects(TypeEngineBean.class, con, ps); } }