Introduction
The removal of business objects via bulk operation or via import (DELETE mode) is normally based on individual treatment of business objects. This approach is useful for a manageable quantity of objects but reaches its limits with large amounts of data.
For the removal of products, therefore, a deletion process has been implemented that works with JDBC/batch processing based on direct SQL statements. This is handled by the manager class ProductMgrJDBCBatchImpl that implements the interface ProductMgrJDBCBatch.
As the product unsharing via JDBC/batch is similar to product deletion in many ways, unsharing operations were also included in ProductMgrJDBCBatch and its implementation.
Also for the removal of a catalog and its subcategories, a deletion process has been implemented that works with JDBC/batch processing based on direct SQL statements. This is handled by the manager class CatalogMgrJDBCBatchImpl that implements the interface CatalogMgrJDBCBatch.
References
Implementation Details
Executing SQL Statements
The ProductMgrJDBCBatchImpl as well as the CatalogMgrJDBCBatchImpl use JDBC/batch processing based on direct SQL statements.
This means they execute SQL statements like delete from PRODUCT where uuid in (?) where (?) is a placeholder for a dynamically extended list of ? depending on the number of items, e.g., for 10 items to be deleted: (?,?,?,?,?,?,?,?,?,?). The table name PRODUCT is also just an example. A list of all affected tables can be found in section Affected Tables.
The SQL statements are executed as java.sql.PreparedStatement using the methods addBatch and executeBatch. The method executeBatch is only executed after a certain number of addBatch invocations.
Transaction Handling
All methods of ProductMgrJDBCBatchImpl and CatalogMgrJDBCBatchImpl mentioned in the table below will open a new transaction and commit afterwards in case there is no active transaction.
If there is already an active transaction, the removal will be executed and the transaction will be stored afterwards.
ProductMgrJDBCBatch
Class Structure
Methods
Method | Description |
|---|---|
| Removes all local products. Products shared to the given domain will be ignored. If products are shared from the given domain to another domain, they will be removed there as well. |
| Removes all local products that match the provided UUIDs. Products shared to the given domain will be ignored. If products are shared from the given domain to another domain, they will be removed there as well. |
| Removes all local products that match the provided SKUs. Products shared to the given domain will be ignored. If products are shared from the given domain to another domain, they will be removed there as well. |
| Unshares the given product sets from the domain. Only product sets with explicitly assigned products will be removed. Should some products be shared to the target domain through another product set, those products will remain available with all their data. In case no further product sets are assigned to the target domain after unsharing, a special empty product set will be assigned. If no such product set exists, it will be created. |
| Unshares all products/product sets from the target domain that originate from the source domain. Additionally, a special empty product set will be assigned to the target domain. If no such product set exists, it will be created. |
| Unshares all products/product sets from the target domains that originate from the source domain. |
| Unshares the given product sets from the domain and deletes the product sets afterwards. Only product sets with explicitly assigned products will be removed. In case no further product sets are assigned to the target domain after unsharing, a special empty product set will be assigned. If no such product set exists, it will be created. |
| Unshares the products from all domains that are assigned to the product set. |
Events
Product price change events only need to be created for products that have a price change. If a product gets removed, all product price change events can be deleted for that product in the domain.
A global cache clearing is executed on all affected objects. These are:
com.intershop.beehive.xcs.internal.price.ProductPriceChangeEventPO com.intershop.component.mvc.internal.product.ProductLabelAssignmentPO com.intershop.component.search.internal.searchaction.RedirectActionPO com.intershop.component.mvc.internal.assignments.PageletEntryPointProductAssignmentPO com.intershop.component.product.rating.orm.internal.ProductRatingAssignmentPO com.intershop.component.rating.orm.internal.RatingCommentPO com.intershop.component.rating.orm.internal.RatingCommentPOAttributeValuePO com.intershop.component.shipping.internal.productextracharge.ProductShippingChargeOverridePO com.intershop.component.shipping.internal.productextracharge.ProductShippingChargeOverridePOAttributeValuePO com.intershop.component.shipping.internal.productextracharge.ProductShippingSurchargePO com.intershop.component.shipping.internal.productextracharge.ProductShippingSurchargePOAttributeValuePO com.intershop.component.marketing.internal.rebate.ActivePromoProductsPO com.intershop.component.marketing.internal.rebate.RebateFilterObjectAssignmentPO com.intershop.beehive.xcs.internal.productcompleteness.ProductCompletenessPO com.intershop.component.product.validation.internal.result.orm.ProductValidationResultPO com.intershop.beehive.xcs.internal.productapproval.ProductApprovalPO com.intershop.beehive.xcs.internal.productapproval.ProductApprovalPOAttributeValuePO com.intershop.beehive.xcs.internal.image.ImageProductAssignmentPO com.intershop.component.image.internal.orm.ImageReferencePO com.intershop.beehive.xcs.internal.price.DerivedProductListPricePO com.intershop.beehive.xcs.internal.price.DerivedProductCostPricePO com.intershop.beehive.xcs.internal.product.DerivedProductPO com.intershop.beehive.xcs.internal.productset.ProductSetAssignmentPO com.intershop.beehive.xcs.internal.catalog.TypedProductCategoryAssignmentPO com.intershop.beehive.xcs.internal.product.ProductLinkPositionPO com.intershop.beehive.xcs.internal.product.ProductLinkPO com.intershop.beehive.xcs.internal.product.ProductLinkPOAttributeValuePO com.intershop.beehive.xcs.internal.product.BundleAssignmentPO com.intershop.beehive.xcs.internal.productvariation.VariableVariationAttributePO com.intershop.beehive.xcs.internal.productvariation.VariableVariationAttributePOAttributeValuePO com.intershop.beehive.xcs.internal.productvariation.ProductVariationPO com.intershop.beehive.xcs.internal.productvariation.ProductVariationPOAttributeValuePO com.intershop.beehive.xcs.internal.price.ProductCostPricePO com.intershop.beehive.xcs.internal.price.ProductListPricePO com.intershop.component.pricing.internal.pricelist.PriceScaleTableAssignmentPO com.intershop.component.pricing.internal.pricelist.PriceScaleTableAssignmentPOAttributeValuePO com.intershop.component.pricing.internal.scale.PriceScaleEntryPO com.intershop.component.pricing.internal.scale.PriceScaleEntryPOAttributeValuePO com.intershop.component.pricing.internal.scale.PriceScaleTablePO com.intershop.component.pricing.internal.scale.PriceScaleTablePOAttributeValuePO com.intershop.beehive.xcs.internal.catalog.ProductCategoryAssignmentPO com.intershop.beehive.xcs.internal.product.ProductPORoleAssignmentPO com.intershop.component.mvc.internal.alert.AlertPO com.intershop.component.mvc.internal.alert.AlertPOAttributeValuePO com.intershop.component.mvc.internal.filter.FilterProductAssignmentPO com.intershop.beehive.xcs.internal.price.UserGroupPricePO com.intershop.component.foundation.internal.syndication.SyndicationPO com.intershop.component.foundation.internal.syndication.SyndicationPOAttributeValuePO com.intershop.beehive.xcs.internal.product.ProductPO com.intershop.beehive.xcs.internal.productset.ProductSetDomainAssignmentPO com.intershop.beehive.xcs.internal.productset.ProductSetPO com.intershop.beehive.xcs.internal.productset.ProductSetPOAttributeValuePO
Affected Tables
The following database tables are affected (in alphabetical order):
ACTIVEPROMOPRODUCTS ALERT ALERT_AV BUNDLEASSIGNMENT DERIVEDPRODUCT DERIVEDPRODUCTLISTPRICE DERIVEDPRODUCTCOSTPRICE FILTERPRODUCTASSIGNMENT IMAGEPRODUCTASSIGNMENT IMAGEREFERENCE PRICESCALEENTRY PRICESCALEENTRY_AV PRICESCALETABLE PRICESCALETABLE_AV PRICESCALETABLEASSIGNMENT PRICESCALETABLEASSIG_AV PRODUCT PRODUCT_PEPA PRODUCT_VR PRODUCT_RA PRODUCTAPPROVAL PRODUCTAPPROVAL_AV PRODUCTCATEGORYASSIGNMENT PRODUCTCOMPLETENESS PRODUCTCOSTPRICE PRODUCTLABELASSIGNMENT PRODUCTLINK PRODUCTLINK_AV PRODUCTLINKPOSITION PRODUCTLISTPRICE PRODUCTPRICECHANGEEVENT PRODUCTRATINGASSIGNMENT PRODUCTSET PRODUCTSET_AV PRODUCTSETASSIGNMENT PRODUCTSETDOMAINASSIGNMENT PRODUCTVARIATION PRODUCTVARIATION_AV RATINGCOMMENT RATINGCOMMENT_AV REBATEFILTEROBJECTASSIGNMENT REDIRECTACTION SHIPPINGOVERRIDE SHIPPINGOVERRIDE_AV SHIPPINGSURCHARGE SHIPPINGSURCHARGE_AV SYNDICATION SYNDICATION_AV TYPEDPRODUCTCATEGORYASSM USERGROUPPRICE VARIABLEVARIATIONATTRIBUTE VARIABLEVARIATIONATT_AV
Configuration
The are some configuration values which can be used to change the behavior of the product removal with JDBC/batch.
If no property is specified, the old behavior is used.
Batch Size
This value defines the number of SQL statements sent together to the database.
If no property is specified, the default value is used.
intershop.productMgrJDBCBatch.removeProducts.batchSize
Default value: 50
IN Clause Size
The queries that delete the products from the database use a SQL IN clause which allows to specify multiple values in a WHERE clause. Since the number of elements in an IN clause is limited depending on the used database system, this property can be set accordingly.
intershop.productMgrJDBCBatch.removeProducts.inClauseSize
Default value: 500
Constraints for Product Deletion
The removal of products with JDBC/batch processing only works when the recycle bin option is deactivated. Otherwise, the standard “move to recycle bin” operation will be executed. See screenshot of the Preferences page in Intershop Commerce Management.
CatalogMgrJDBCBatch
Class Structure
Methods
Method | Description |
|---|---|
| Removes the given catalog and its subcategories recursively, but keeps its root category. This is necessary for the BMECat import (version update). |
| Removes the given catalog and its subcategories recursively. It also removes its root category and the catalog itself. |
| Removes the given catalog categories identified by their UUIDs and their subcategories recursively. |
Clear Cache
A global cache clearing is executed on all affected objects. These are:
com.intershop.beehive.xcs.internal.catalog.ProductCategoryAssignmentPO com.intershop.beehive.xcs.internal.catalog.TypedProductCategoryAssignmentPO com.intershop.beehive.xcs.internal.image.ImageCategoryAssignmentPO com.intershop.beehive.xcs.internal.image.ImageSetDefinitionCategoryAssignmentPOAttributeValuePO com.intershop.beehive.xcs.internal.image.ImageSetDefinitionCategoryAssignmentPO com.intershop.component.pmc.internal.pagelet.PageletEntryPointLabelAssignmtPO com.intershop.component.pmc.internal.pagelet.PageletEntryPointPOAttributeValuePO com.intershop.component.pmc.internal.pagelet.entrypoint.assignment.PageletPageletEntryPointAssignmentPO com.intershop.component.pmc.validation.internal.result.pep.PageletEntryPointValidationResultPO com.intershop.component.pmc.internal.pagelet.PageletEntryPointPO com.intershop.component.mvc.internal.assignments.PageletEntryPointCategoryAssignmentPO com.intershop.beehive.xcs.internal.catalog.CatalogCategoryPORoleAssignmentPO com.intershop.component.mvc.internal.catalog.CatalogCategoryLabelAssignmentPO com.intershop.beehive.xcs.internal.catalog.CatalogCategoryLinkPOAttributeValuePO com.intershop.beehive.xcs.internal.catalog.CatalogCategoryLinkPO com.intershop.component.image.internal.orm.ImageReferencePO com.intershop.beehive.xcs.internal.product.ProductPO com.intershop.beehive.xcs.internal.product.DerivedProductPO com.intershop.component.marketing.internal.rebate.RebateFilterObjectAssignmentPO com.intershop.component.mvc.internal.catalog.CatalogPO com.intershop.component.mvc.internal.catalog.CatalogPOAttributeValuePO com.intershop.beehive.xcs.internal.catalog.CatalogCategoryPO com.intershop.beehive.xcs.internal.catalog.CatalogCategoryPOAttributeValuePO
Affected Tables
The following database tables are affected:
CATALOG CATALOG_AV CATALOGCATEGORY CATALOGCATEGORY_AV CATALOGCATEGORY_PEPA CATALOGCATEGORY_RA CATALOGCATEGORYLABELASSIGNMENT CATALOGCATEGORYLINK_AV CATALOGCATEGORYLINK DERIVEDPRODUCT IMAGECATEGORYASSIGNMENT IMAGEREFERENCE IMAGESETCATEGORYASSI_AV IMAGESETCATEGORYASSIGNMENT PAGELETENTRYPOINT PAGELETENTRYPOINT_AV PAGELETENTRYPOINT_PA PAGELETENTRYPOINT_VR PAGELETENTRYPOINTLABELASSIGNMT PRODUCT PRODUCTCATEGORYASSIGNMENT REBATEFILTEROBJECTASSIGNMENT TYPEDPRODUCTCATEGORYASSM
Configuration
The are some configuration values which can be used to change the behavior of the catalog/category removal with JDBC/batch.
If no property is specified, the old behavior is used.
IN Clause Size
The queries that delete the categories from the database use a SQL IN clause which allows to specify multiple values in a WHERE clause. Since the number of elements in an IN clause is limited depending on the used database system, this property can be set accordingly.
intershop.catalogMgrJDBCBatch.removeCatalogCategories.inClauseSize
Default value: 500
Areas of Application
Product Deletion
BMECat Import (also Subcategory Deletion)
The BMECat import does not support explicit import modes such as "INITIAL", "UPDATE" or "DELETE". There is no possibility of deletion. Instead, the import is carried out using the specified version number. It is not possible to perform two imports with the same version number. When importing data with a higher version number, all existing data including all products in the catalog from a previous import are completely removed (the catalog itself remains). As this deletion of all categories and products can take a considerable amount of time, both the previous categories and products can be removed with the approach via JDBC/batch processing instead.
Example catalog versions: (Second file with version 2.0 will automatically overwrite import with version 1.0)
<HEADER> <CATALOG> <LANGUAGE>deu</LANGUAGE> <CATALOG_ID>1</CATALOG_ID> <CATALOG_VERSION>1.0</CATALOG_VERSION> // version 1.0 <DATETIME type="generation_date"> <DATE>2023-06-07</DATE> <TIME>11:46:14</TIME> </DATETIME> <CURRENCY>EUR</CURRENCY> </CATALOG> ...
<HEADER> <CATALOG> <LANGUAGE>deu</LANGUAGE> <CATALOG_ID>1</CATALOG_ID> <CATALOG_VERSION>2.0</CATALOG_VERSION> // version 2.0 <DATETIME type="generation_date"> <DATE>2023-10-12</DATE> <TIME>09:11:56</TIME> </DATETIME> <CURRENCY>EUR</CURRENCY> </CATALOG> ...
In order to activate the removal of products and/or catalog (and subcategories) via JDBC/batch processing, the following properties/preferences can be set:
intershop.bmeCatImport.removeProductsWithJDBCBatch=trueintershop.bmeCatImport.removeCatalogWithJDBCBatch=true
Product Bulk Deletion
Bulk deletion can be used to delete all products in a channel or master repository or to delete products based on search results or manual selection. Here it is suitable to use the approach via JDBC/batch processing.
The bulk deletion option is available at the master product and channel product level (also in partner channels) with the "Edit Selected" or "Edit All" buttons only. JDBC/batch processing is not applied if the standard "Delete" button is used, as no bulk deletion is performed.
The selected product deletion preference (for using the recycle bin option or not) will be applied to this product bulk deletion operation. See Constraints for Product Deletion.
In order to activate the bulk removal of products via JDBC/batch processing, the following property can be set:
intershop.productBulkOperation.removeProductsWithJDBCBatch=true
Standard Product Import (XML) in DELETE Mode
There is also an effective way to remove a mass of products from the system by using the standard XML product import in DELETE mode. The product import is available at organization level for the master repository as well as on channel level for the channel repository.
Further performance improvements could be achieved by using the deletion approach via JDBC/batch processing for the DELETE mode. The other product import modes like “INITIAL” or “UPDATE” have not changed.
The selected product deletion preference (for using the recycle bin option or not) will also be applied to this standard product import (XML) in DELETE mode. See Constraints for Product Deletion.
It is possible to configure the number of products that are processed in a chunk. The products to be deleted are collected in the bulk method until the required number that has been configured is reached. Then, the accumulated products are deleted all at once. At the end of the process, the remaining products are also deleted in the finish method. The default chunk size is 5000.
To change the default chunk size, the following property can be set (e.g., 1000):
intershop.productImport.deleteMode.chunkSize.removeProductsWithJDBCBatch=1000
In order to activate the removal of products in product import with DELETE mode via JDBC/batch processing, the following property can be set:
intershop.productImport.deleteMode.removeProductsWithJDBCBatch=true
Product Unsharing
The unsharing of products via JDBC/batch processing is available on organization and partner channel level. To activate this feature, the following property can be set:
intershop.productSharing.unshareProductsWithJDBCBatch=true
All of the following actions can use JDBC/batch processing:
Delete Channel Sharing Assignments
Delete Sharing Groups
Remove Products from a Sharing Group
Delete Channel Assignments from a Sharing Group
Delete Sharing Group Assignments from a Channel
Change Sharing Type of a Channel to “Share Nothing”
Catalog/Category Deletion
In Intershop Commerce Management it is also possible to delete a catalog with all subcategories. The catalog assignment of the products is also deleted but the products themselves are retained.
As this deletion of a large category tree can take quite some time, the catalog/categories can be removed via JDBC/batch processing.
In order to activate the removal of catalogs and categories via JDBC/batch processing, the following property can be set:
intershop.catalogAction.removeCatalogWithJDBCBatch=true
Catalog/Category Unsharing
In Intershop Commerce Management it is possible to unshare a master catalog shared with the channel. The catalog’s product assignments, product and category links, label assignments, promotion assignments, dynamic product assignment filters, image assignments, and content configurations created in the channel are deleted. The channel products and/or the products sharing from master are retained.
As this deletion of a large number of relations of the channel products and channel level changes of catalog content can take quite some time, the catalog can be removed via JDBC/batch processing.
In order to activate the removal of catalogs and categories via JDBC/batch processing, the following property can be set:
intershop.mastercatalogAction.unshareCatalogWithJDBCBatch
