diff --git a/src/org/openbravo/replication/symmetricds/SetupSymmetricListener.java b/src/org/openbravo/replication/symmetricds/SetupSymmetricListener.java --- a/src/org/openbravo/replication/symmetricds/SetupSymmetricListener.java +++ b/src/org/openbravo/replication/symmetricds/SetupSymmetricListener.java @@ -37,7 +37,6 @@ import javax.servlet.ServletContextListener; import org.apache.commons.lang.StringUtils; -import org.hibernate.query.Query; import org.jumpmind.symmetric.service.RegistrationFailedException; import org.jumpmind.symmetric.web.ServerSymmetricEngine; import org.jumpmind.symmetric.web.SymmetricEngineHolder; @@ -55,7 +54,6 @@ import org.openbravo.replication.symmetricds.util.ReplicationUtils; import org.openbravo.replication.symmetricds.util.SynchronizationPropertiesFileBuilder; import org.openbravo.service.db.DalConnectionProvider; -import org.openbravo.service.importprocess.ImportEntry; import org.openbravo.service.importprocess.ImportEntryManager; import org.slf4j.Logger; import org.slf4j.LoggerFactory; @@ -598,13 +596,29 @@ } private int countInitialImportEntries() { - final Query qry = OBDal - .getInstance() - .getSession() - .createQuery( - "select count(id) from " + ImportEntry.ENTITY_NAME + " where " - + ImportEntry.PROPERTY_IMPORTSTATUS + "='Initial'", Number.class); - return qry.uniqueResult().intValue(); + PreparedStatement st = null; + ConnectionProvider cp = null; + try { + cp = new DalConnectionProvider(); + st = cp + .getPreparedStatement("SELECT count(*) FROM c_import_entry where importstatus='Initial'"); + ResultSet result = st.executeQuery(); + result.next(); + int num = result.getInt(1); + result.close(); + return num; + } catch (Throwable t) { + logger.error(t.getMessage(), t); + throw new OBException(t.getMessage(), t); + } finally { + try { + if (cp != null) { + cp.releasePreparedStatement(st); + } + } catch (Exception e) { + log.error("Error while releasing a prepared statement", e); + } + } } public void setOBContext(OBContext obContext) { diff --git a/src/org/openbravo/replication/symmetricds/datasource/RegisteredServersDS.java b/src/org/openbravo/replication/symmetricds/datasource/RegisteredServersDS.java --- a/src/org/openbravo/replication/symmetricds/datasource/RegisteredServersDS.java +++ b/src/org/openbravo/replication/symmetricds/datasource/RegisteredServersDS.java @@ -20,6 +20,8 @@ package org.openbravo.replication.symmetricds.datasource; +import java.sql.PreparedStatement; +import java.sql.ResultSet; import java.util.ArrayList; import java.util.Collection; import java.util.HashMap; @@ -33,19 +35,21 @@ import org.codehaus.jettison.json.JSONException; import org.codehaus.jettison.json.JSONObject; import org.hibernate.criterion.Restrictions; -import org.hibernate.query.Query; import org.jumpmind.symmetric.model.Node; import org.jumpmind.symmetric.model.OutgoingBatch; import org.jumpmind.symmetric.model.OutgoingBatch.Status; import org.jumpmind.symmetric.web.ServerSymmetricEngine; +import org.openbravo.base.exception.OBException; import org.openbravo.base.model.Entity; import org.openbravo.base.model.ModelProvider; import org.openbravo.dal.core.OBContext; import org.openbravo.dal.service.OBCriteria; import org.openbravo.dal.service.OBDal; +import org.openbravo.database.ConnectionProvider; import org.openbravo.mobile.core.MobileServerDefinition; import org.openbravo.replication.symmetricds.SynchronizationEngineProvider; import org.openbravo.service.datasource.ReadOnlyDataSourceService; +import org.openbravo.service.db.DalConnectionProvider; import org.openbravo.service.json.JsonUtils; /** @@ -122,14 +126,29 @@ private Set getNodesWithProcessErrors() { Set nodesWithProcessErrors = new HashSet(); - final Query qry = OBDal - .getInstance() - .getSession() - .createQuery("select distinct obmobcServerDefinition from OBSTSYN_Error", - MobileServerDefinition.class); - List mobileServers = qry.list(); - for (MobileServerDefinition mobileServer : mobileServers) { - nodesWithProcessErrors.add(mobileServer.getMobileServerKey()); + PreparedStatement st = null; + ConnectionProvider cp = null; + try { + cp = new DalConnectionProvider(); + st = cp + .getPreparedStatement("SELECT mobile_server_key from obmobc_server_definition where obmobc_server_definition_id " + + "in (select obmobc_server_definition_id FROM obstsyn_error)"); + ResultSet result = st.executeQuery(); + while (result.next()) { + String serverKey = result.getString(1); + nodesWithProcessErrors.add(serverKey); + } + } catch (Throwable t) { + log.error(t.getMessage(), t); + throw new OBException(t.getMessage(), t); + } finally { + try { + if (cp != null) { + cp.releasePreparedStatement(st); + } + } catch (Exception e) { + log.error("Error while releasing a prepared statement", e); + } } return nodesWithProcessErrors; } diff --git a/src/org/openbravo/replication/symmetricds/task/SetMandatoryReloadFlagTask.java b/src/org/openbravo/replication/symmetricds/task/SetMandatoryReloadFlagTask.java --- a/src/org/openbravo/replication/symmetricds/task/SetMandatoryReloadFlagTask.java +++ b/src/org/openbravo/replication/symmetricds/task/SetMandatoryReloadFlagTask.java @@ -20,13 +20,16 @@ package org.openbravo.replication.symmetricds.task; +import java.sql.PreparedStatement; + import org.apache.log4j.Logger; -import org.hibernate.query.NativeQuery; import org.openbravo.base.exception.OBException; import org.openbravo.base.session.OBPropertiesProvider; import org.openbravo.dal.core.DalInitializingTask; import org.openbravo.dal.service.OBDal; +import org.openbravo.database.ConnectionProvider; import org.openbravo.mobile.core.servercontroller.MobileServerController; +import org.openbravo.service.db.DalConnectionProvider; /** * Set the reload status of a server definition to do mandatory reload. @@ -51,16 +54,32 @@ "This is the central server. This task should not be executed on the central server"); } - final String qryString = "UPDATE obmobc_server_definition SET em_strsync_reload_status='MANDATORY_ON_STARTUP' WHERE mobile_server_key='" - + mobileServerKey + "'"; - @SuppressWarnings("rawtypes") - final NativeQuery qry = OBDal.getInstance().getSession().createNativeQuery(qryString); - final int updatedRecords = qry.executeUpdate(); - if (updatedRecords == 0) { - throw new OBException("Update of flag did not update any record, is the iscurrent flag set?"); + PreparedStatement st = null; + ConnectionProvider cp = null; + try { + cp = new DalConnectionProvider(); + final String qryString = "UPDATE obmobc_server_definition SET em_strsync_reload_status='MANDATORY_ON_STARTUP' WHERE mobile_server_key=?"; + st = cp.getPreparedStatement(qryString); + st.setString(1, mobileServerKey); + int updatedRecords = st.executeUpdate(); + if (updatedRecords == 0) { + throw new OBException( + "Update of flag did not update any record, is the iscurrent flag set?"); + } + log.info("Executed query to set reload status to mandatory on startup"); + OBDal.getInstance().commitAndClose(); + log.info("Committed reload status to mandatory on startup"); + } catch (Throwable t) { + log.error(t.getMessage(), t); + throw new OBException(t.getMessage(), t); + } finally { + try { + if (cp != null) { + cp.releasePreparedStatement(st); + } + } catch (Exception e) { + log.error("Error while releasing a prepared statement", e); + } } - log.info("Executed query to set reload status to mandatory on startup"); - OBDal.getInstance().commitAndClose(); - log.info("Committed reload status to mandatory on startup"); } } diff --git a/src/org/openbravo/replication/symmetricds/util/OutgoingBatchReloader.java b/src/org/openbravo/replication/symmetricds/util/OutgoingBatchReloader.java --- a/src/org/openbravo/replication/symmetricds/util/OutgoingBatchReloader.java +++ b/src/org/openbravo/replication/symmetricds/util/OutgoingBatchReloader.java @@ -20,9 +20,12 @@ package org.openbravo.replication.symmetricds.util; -import org.hibernate.query.Query; -import org.openbravo.dal.service.OBDal; +import java.sql.PreparedStatement; + +import org.openbravo.base.exception.OBException; +import org.openbravo.database.ConnectionProvider; import org.openbravo.mobile.core.servercontroller.MobileServerController; +import org.openbravo.service.db.DalConnectionProvider; import org.slf4j.Logger; import org.slf4j.LoggerFactory; @@ -54,12 +57,28 @@ } log.info("Resending outgoing batches of channel " + channelId + " to mobile server " + storeServerKey + " from batch " + fromBatch); - final String sql = "update sym_outgoing_batch e set status='RS' where channel_id = :channelId and node_id = :storeServerKey and batch_id > :fromBatch and status = 'OK'"; - @SuppressWarnings("rawtypes") - final Query query = OBDal.getInstance().getSession().createNativeQuery(sql); - query.setParameter("channelId", channelId); - query.setParameter("storeServerKey", storeServerKey); - query.setParameter("fromBatch", fromBatch); - query.executeUpdate(); + final String sql = "update sym_outgoing_batch e set status='RS' where channel_id = ? and " + + "node_id = ? and batch_id > ? and status = 'OK'"; + PreparedStatement st = null; + ConnectionProvider cp = null; + try { + cp = new DalConnectionProvider(); + st = cp.getPreparedStatement(sql); + st.setString(1, channelId); + st.setString(2, storeServerKey); + st.setLong(3, fromBatch); + st.executeUpdate(); + } catch (Throwable t) { + log.error(t.getMessage(), t); + throw new OBException(t.getMessage(), t); + } finally { + try { + if (cp != null) { + cp.releasePreparedStatement(st); + } + } catch (Exception e) { + log.error("Error while releasing a prepared statement", e); + } + } } } diff --git a/src/org/openbravo/replication/symmetricds/validation/DataValidationError.java b/src/org/openbravo/replication/symmetricds/validation/DataValidationError.java --- a/src/org/openbravo/replication/symmetricds/validation/DataValidationError.java +++ b/src/org/openbravo/replication/symmetricds/validation/DataValidationError.java @@ -20,21 +20,27 @@ package org.openbravo.replication.symmetricds.validation; +import java.sql.PreparedStatement; +import java.sql.ResultSet; import java.util.ArrayList; import java.util.List; import org.apache.ddlutils.io.DataSetTableQueryGenerator; import org.apache.ddlutils.io.DataSetTableQueryGeneratorExtraProperties; -import org.hibernate.query.NativeQuery; +import org.openbravo.base.exception.OBException; import org.openbravo.base.model.Entity; import org.openbravo.base.model.ModelProvider; import org.openbravo.base.structure.BaseOBObject; import org.openbravo.dal.service.OBDal; +import org.openbravo.database.ConnectionProvider; import org.openbravo.ddlutils.util.OBDatasetTable; import org.openbravo.model.ad.datamodel.Table; import org.openbravo.model.ad.utility.DataSetColumn; import org.openbravo.model.ad.utility.DataSetTable; import org.openbravo.model.common.enterprise.Organization; +import org.openbravo.service.db.DalConnectionProvider; +import org.slf4j.Logger; +import org.slf4j.LoggerFactory; /** * Class that represents a data validation error, used to store errors found by the @@ -56,6 +62,7 @@ */ public class DataValidationError implements Comparable { private static final int MAX_ROWS_TO_EXPAND_PER_MISSING_ID = 10; + private static final Logger log = LoggerFactory.getLogger(DataValidationError.class); private String sourceTableName; private String sourceColumnName; @@ -103,33 +110,44 @@ } private void logRowsThatReferenceMissingId() { + Entity sourceEntity = ModelProvider.getInstance().getEntityByTableName(sourceTableName); System.out.println("Referenced by " + sourceTableName + "." + sourceColumnName + " (rowId, organization, identifier):"); - @SuppressWarnings("rawtypes") - NativeQuery query = buildQueryToObtainRowsThatReferenceMissingId(); - @SuppressWarnings("unchecked") - List results = query.list(); - if (results.size() == MAX_ROWS_TO_EXPAND_PER_MISSING_ID) { - System.out.println("Only showing the first " + MAX_ROWS_TO_EXPAND_PER_MISSING_ID - + " results."); - } - Entity sourceEntity = ModelProvider.getInstance().getEntityByTableName(sourceTableName); - for (Object result : results) { - String sourceRecordId = (String) result; - BaseOBObject row = OBDal.getInstance().get(sourceEntity.getName(), sourceRecordId); - Organization organization = null; - if ("Organization".equals(sourceEntity.getName())) { - organization = (Organization) row; - } else { - organization = (Organization) row.get("organization"); + String query = buildQueryToObtainRowsThatReferenceMissingId(); + PreparedStatement st = null; + ConnectionProvider cp = null; + try { + cp = new DalConnectionProvider(); + st = cp.getPreparedStatement(query); + st.setMaxRows(MAX_ROWS_TO_EXPAND_PER_MISSING_ID + 1); + ResultSet result = st.executeQuery(); + while (result.next()) { + String sourceRecordId = result.getString(1); + BaseOBObject row = OBDal.getInstance().get(sourceEntity.getName(), sourceRecordId); + Organization organization = null; + if ("Organization".equals(sourceEntity.getName())) { + organization = (Organization) row; + } else { + organization = (Organization) row.get("organization"); + } + System.out.println(sourceRecordId + ", " + organization.getName() + ", " + + row.getIdentifier()); } - System.out.println(sourceRecordId + ", " + organization.getName() + ", " - + row.getIdentifier()); + result.close(); + } catch (Throwable t) { + throw new OBException(t.getMessage(), t); + } finally { + try { + if (cp != null) { + cp.releasePreparedStatement(st); + } + } catch (Exception e) { + log.error("Error while releasing a prepared statement", e); + } } } - @SuppressWarnings("rawtypes") - private NativeQuery buildQueryToObtainRowsThatReferenceMissingId() { + private String buildQueryToObtainRowsThatReferenceMissingId() { Entity sourceEntity = ModelProvider.getInstance().getEntityByTableName(sourceTableName); String sourceKeyColumName = sourceEntity.getIdProperties().get(0).getColumnName(); List columnNames = new ArrayList<>(); @@ -140,10 +158,7 @@ extraProperties.addWhereClauseExpression(sourceColumnName, " = ", "'" + targetRecordId + "'"); String queryString = dataSetTableQueryGenerator.generateQuery(obDataSetTables, columnNames, extraProperties); - NativeQuery query = OBDal.getInstance().getSession().createNativeQuery(queryString); - // Limit to MAX_ROWS_TO_EXPAND_PER_MISSING_ID to prevent making the log too long - query.setMaxResults(MAX_ROWS_TO_EXPAND_PER_MISSING_ID); - return query; + return queryString; } // returns a list of OBDatasetTable that can be passed to the dataset table query generator to diff --git a/src/org/openbravo/replication/symmetricds/validation/ExportedTableValidator.java b/src/org/openbravo/replication/symmetricds/validation/ExportedTableValidator.java --- a/src/org/openbravo/replication/symmetricds/validation/ExportedTableValidator.java +++ b/src/org/openbravo/replication/symmetricds/validation/ExportedTableValidator.java @@ -20,6 +20,8 @@ package org.openbravo.replication.symmetricds.validation; +import java.sql.PreparedStatement; +import java.sql.ResultSet; import java.util.ArrayList; import java.util.HashSet; import java.util.List; @@ -31,16 +33,15 @@ import org.apache.commons.lang.StringUtils; import org.apache.ddlutils.io.DataSetTableQueryGeneratorExtraProperties; -import org.hibernate.ScrollMode; -import org.hibernate.ScrollableResults; import org.hibernate.criterion.Restrictions; -import org.hibernate.query.NativeQuery; +import org.openbravo.base.exception.OBException; import org.openbravo.base.model.Entity; import org.openbravo.base.model.ModelProvider; import org.openbravo.base.model.Property; import org.openbravo.dal.core.OBContext; import org.openbravo.dal.service.OBCriteria; import org.openbravo.dal.service.OBDal; +import org.openbravo.database.ConnectionProvider; import org.openbravo.ddlutils.util.OBDatasetTable; import org.openbravo.model.ad.datamodel.Table; import org.openbravo.model.ad.utility.DataSet; @@ -48,6 +49,9 @@ import org.openbravo.model.ad.utility.DataSetTable; import org.openbravo.replication.symmetricds.export.StoreDataSetTableQueryGenerator; import org.openbravo.replication.symmetricds.export.StoreDataSetTableQueryGenerator.QueryPlaceholders; +import org.openbravo.service.db.DalConnectionProvider; +import org.slf4j.Logger; +import org.slf4j.LoggerFactory; /** * Validates the data that will be exported to a store for a given table. It checks that all the @@ -56,6 +60,7 @@ * The invalid rows are detected using a query (see getMissingForeignKeys) */ public class ExportedTableValidator { + private static final Logger log = LoggerFactory.getLogger(ExportedTableValidator.class); private final String tableName; private final DataSet dataSet; @@ -146,11 +151,28 @@ } queryString = getQueryToCheckMissingForeignKeys(dataSetTable, foreignKeyColumnName, referencedTable); - @SuppressWarnings("rawtypes") - NativeQuery query = OBDal.getInstance().getSession().createNativeQuery(queryString); - final ScrollableResults scroller = query.scroll(ScrollMode.FORWARD_ONLY); - while (scroller.next()) { - missingForeignKeys.add((String) scroller.get()[0]); + PreparedStatement st = null; + ConnectionProvider cp = null; + try { + cp = new DalConnectionProvider(); + st = cp.getPreparedStatement(queryString); + st.setFetchDirection(ResultSet.TYPE_FORWARD_ONLY); + st.setFetchSize(5000); + ResultSet result = st.executeQuery(); + while (result.next()) { + missingForeignKeys.add(result.getString(1)); + } + result.close(); + } catch (Throwable t) { + throw new OBException(t.getMessage(), t); + } finally { + try { + if (cp != null) { + cp.releasePreparedStatement(st); + } + } catch (Exception e) { + log.error("Error while releasing a prepared statement", e); + } } } if (!missingForeignKeys.isEmpty()) { diff --git a/src/org/openbravo/replication/symmetricds/validation/SynchronizationStoreDataValidator.java b/src/org/openbravo/replication/symmetricds/validation/SynchronizationStoreDataValidator.java --- a/src/org/openbravo/replication/symmetricds/validation/SynchronizationStoreDataValidator.java +++ b/src/org/openbravo/replication/symmetricds/validation/SynchronizationStoreDataValidator.java @@ -20,6 +20,7 @@ package org.openbravo.replication.symmetricds.validation; +import java.sql.PreparedStatement; import java.util.ArrayList; import java.util.Arrays; import java.util.Collections; @@ -39,12 +40,14 @@ import org.apache.commons.lang.StringUtils; import org.apache.tools.ant.BuildException; import org.hibernate.criterion.Restrictions; +import org.openbravo.base.exception.OBException; import org.openbravo.base.model.Entity; import org.openbravo.base.model.ModelProvider; import org.openbravo.base.model.Property; import org.openbravo.dal.core.OBContext; import org.openbravo.dal.service.OBCriteria; import org.openbravo.dal.service.OBDal; +import org.openbravo.database.ConnectionProvider; import org.openbravo.ddlutils.util.OBDatasetTable; import org.openbravo.mobile.core.MobileServerDefinition; import org.openbravo.mobile.core.servercontroller.MobileServerController; @@ -53,6 +56,7 @@ import org.openbravo.model.ad.utility.DataSetTable; import org.openbravo.replication.symmetricds.OrganizationNaturalTreeForMobileServerProvider; import org.openbravo.replication.symmetricds.export.StoreDataSetTableQueryGenerator; +import org.openbravo.service.db.DalConnectionProvider; import org.slf4j.Logger; import org.slf4j.LoggerFactory; @@ -224,7 +228,23 @@ String materializedTableQuery = "CREATE MATERIALIZED VIEW " + materializedViewName + " AS " + dsTableQuery; System.out.println("Creating materialized view for table " + tableName); - OBDal.getInstance().getSession().createNativeQuery(materializedTableQuery).executeUpdate(); + PreparedStatement st = null; + ConnectionProvider cp = null; + try { + cp = new DalConnectionProvider(); + st = cp.getPreparedStatement(materializedTableQuery); + st.executeUpdate(); + } catch (Throwable t) { + throw new OBException(t.getMessage(), t); + } finally { + try { + if (cp != null) { + cp.releasePreparedStatement(st); + } + } catch (Exception e) { + log.error("Error while releasing a prepared statement", e); + } + } tablesWithMaterializedViews.put(tableName, materializedViewName); }