0039144: DAL queries should be replaced by SQL queries to avoid the change of Hibernate API

diff --git a/src/org/openbravo/retail/storeserver/synchronization/IgnoreVersionCheckPreferenceEventHandler.java b/src/org/openbravo/retail/storeserver/synchronization/IgnoreVersionCheckPreferenceEventHandler.java
--- a/src/org/openbravo/retail/storeserver/synchronization/IgnoreVersionCheckPreferenceEventHandler.java
+++ b/src/org/openbravo/retail/storeserver/synchronization/IgnoreVersionCheckPreferenceEventHandler.java
@@ -23,7 +23,6 @@
 import javax.enterprise.event.Observes;
 
 import org.apache.log4j.Logger;
-import org.hibernate.resource.transaction.spi.TransactionStatus;
 import org.openbravo.base.exception.OBException;
 import org.openbravo.base.model.Entity;
 import org.openbravo.base.model.ModelProvider;
@@ -96,9 +95,10 @@
   public void transactionCompleted(@Observes TransactionCompletedEvent event) {
     final boolean doReset = null != resetVersion.get() && resetVersion.get();
     resetVersion.set(null);
-    // recompute the version status if applicable
-    if (doReset && event.getTransaction().getStatus() == TransactionStatus.COMMITTED
-        && MobileServerController.getInstance().isThisAStoreServer()) {
+    // recompute the version status if applicable, always recompute also
+    // if transaction is not committed, transaction is almost always commited anyway
+    // and only if the preference changes is the recompute done which is practically never
+    if (doReset && MobileServerController.getInstance().isThisAStoreServer()) {
       StoreServerVersionHandler.getInstance().resetCachedInformation();
     }
   }
diff --git a/src/org/openbravo/retail/storeserver/synchronization/StoreServerVersionHandler.java b/src/org/openbravo/retail/storeserver/synchronization/StoreServerVersionHandler.java
--- a/src/org/openbravo/retail/storeserver/synchronization/StoreServerVersionHandler.java
+++ b/src/org/openbravo/retail/storeserver/synchronization/StoreServerVersionHandler.java
@@ -22,13 +22,14 @@
 import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.util.HashMap;
+import java.util.List;
 import java.util.Map;
 
-import org.hibernate.query.Query;
 import org.openbravo.base.exception.OBException;
 import org.openbravo.base.provider.OBProvider;
 import org.openbravo.dal.core.OBContext;
 import org.openbravo.dal.service.OBDal;
+import org.openbravo.dal.service.OBQuery;
 import org.openbravo.database.ConnectionProvider;
 import org.openbravo.erpCommon.businessUtility.Preferences;
 import org.openbravo.mobile.core.MobileServerDefinition;
@@ -42,38 +43,33 @@
 import org.slf4j.LoggerFactory;
 
 /**
- * Class which manages store server versions and version checks to prevent servers in different
- * versions to exchange information.
+ * Class which manages store server versions and version checks to prevent servers in different versions to exchange information.
  * 
- * Some background information: - when a server starts the system checks if modules have been
- * installed/updated - if this is the case then the server will regenerate the modules-by-server
- * table for this server and compute - a new version number - the list of modules by server are
- * replicated, from a store to central, and for the central server (so the modules of the central
- * server) to all stores.
+ * Some background information: - when a server starts the system checks if modules have been installed/updated - if this is the
+ * case then the server will regenerate the modules-by-server table for this server and compute - a new version number - the list
+ * of modules by server are replicated, from a store to central, and for the central server (so the modules of the central server)
+ * to all stores.
  * 
  * For the central and store server there are different consequences.
  * 
- * If the central server modules change then the new module list is replicated to all store servers.
- * The {@link VersionCacheResetWriterFilterAdapter} class runs in the store server and listens to
- * this replication. If the central server modules change then automatically the store server
- * version information is recomputed/checked against the new central server version. If the store
- * and central server have different versions for the same modules then the store server will move
- * to offline and to the Different-From-Central version status.
+ * If the central server modules change then the new module list is replicated to all store servers. The
+ * {@link VersionCacheResetWriterFilterAdapter} class runs in the store server and listens to this replication. If the central
+ * server modules change then automatically the store server version information is recomputed/checked against the new central
+ * server version. If the store and central server have different versions for the same modules then the store server will move to
+ * offline and to the Different-From-Central version status.
  * 
- * If the store server modules change then the store server will recompute its version and replicate
- * its modules-by-server to the central server.
+ * If the store server modules change then the store server will recompute its version and replicate its modules-by-server to the
+ * central server.
  * 
- * There is also a writer adapter {@link PreferenceWriterFilterAdapter} which listens to replication
- * of the writer filter adapter from the central to the store server. If the ignore-version-check
- * preference changes then the store servers version status is recomputed also.
+ * There is also a writer adapter {@link PreferenceWriterFilterAdapter} which listens to replication of the writer filter adapter
+ * from the central to the store server. If the ignore-version-check preference changes then the store servers version status is
+ * recomputed also.
  * 
- * When the store server is incompatible with the central server then the
- * {@link StoreServerIncomingSynchronizationPreventer} will prevent data to be replicated from
- * central to the store.
+ * When the store server is incompatible with the central server then the {@link StoreServerIncomingSynchronizationPreventer} will
+ * prevent data to be replicated from central to the store.
  * 
- * The {@link RetailSynchronizationRequestHandler} plays a role in preventing a webpos which is not
- * loaded from the store server to connect to the store server if the store/central server have
- * different versions and ss is offline.
+ * The {@link RetailSynchronizationRequestHandler} plays a role in preventing a webpos which is not loaded from the store server
+ * to connect to the store server if the store/central server have different versions and ss is offline.
  * 
  * @author mtaal
  */
@@ -119,9 +115,8 @@
   }
 
   /**
-   * Returns true if the caller should take into account if the store and central server have
-   * different versions. Returns false in other cases, for example if the ignore version differences
-   * preference is set.
+   * Returns true if the caller should take into account if the store and central server have different versions. Returns false in
+   * other cases, for example if the ignore version differences preference is set.
    */
   public boolean doVersionCheck() {
     if (doResetCachedInformation || ignoreVersionCheck == null) {
@@ -229,8 +224,7 @@
   }
 
   /**
-   * Creates the entries in the modules by server table. Only does this if the version of the
-   * modules changed.
+   * Creates the entries in the modules by server table. Only does this if the version of the modules changed.
    */
   public void recomputeCurrentServerVersions() {
     // read the modules and add the version
@@ -256,14 +250,28 @@
   private void clearCurrentServerVersions() {
     log.info("Removing version info from "
         + MobileServerController.getInstance().getMobileServerKey());
-    OBDal
-        .getInstance()
-        .getSession()
-        .createQuery(
-            "delete from " + MobileServerDefinitionModule.ENTITY_NAME + " where "
-                + MobileServerDefinitionModule.PROPERTY_OBMOBCSERVERDEFINITION + ".id=:serverId")
-        .setParameter("serverId", MobileServerController.getInstance().getThisServerDefinitionId())
-        .executeUpdate();
+
+    PreparedStatement st = null;
+    ConnectionProvider cp = null;
+    try {
+      cp = new DalConnectionProvider();
+      st = cp
+          .getPreparedStatement("delete from obstsyn_server_module where obstsyn_server_module_id=?");
+      st.setString(1, MobileServerController.getInstance().getThisServerDefinitionId());
+      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);
+      }
+    }
+
   }
 
   private void recomputeCurrentServerVersionsInternal() {
@@ -277,22 +285,22 @@
         return;
       }
 
-      final Query<Object[]> moduleQry = OBDal
-          .getInstance()
-          .getSession()
-          .createQuery(
-              "select " + Module.PROPERTY_ID + "," + Module.PROPERTY_VERSION + ","
-                  + Module.PROPERTY_NAME + " from " + Module.ENTITY_NAME, Object[].class);
+      OBQuery<Module> obQuery = OBDal.getInstance().createQuery(Module.class, null);
+      obQuery.setFilterOnActive(false);
+      obQuery.setFilterOnReadableClients(false);
+      obQuery.setFilterOnReadableOrganization(false);
+      List<Module> modules = obQuery.list();
+
       long version1 = 0;
       long version2 = 0;
       long version3 = 0;
-      for (Object[] os : moduleQry.list()) {
-        final String modVersion = (String) os[1];
+      for (Module mod : modules) {
+        final String modVersion = mod.getVersion();
         try {
           final String[] versionSegments = modVersion.split("\\.");
           if (versionSegments.length != 3) {
             log.error("Version number of module is not parseable, ignoring it for version checking "
-                + modVersion + " " + os[0] + " " + os[2]);
+                + modVersion + " " + mod.getName());
             continue;
           }
           version1 += Long.parseLong(versionSegments[0]);
@@ -300,7 +308,7 @@
           version3 += Long.parseLong(versionSegments[2]);
         } catch (Exception e) {
           log.error("Version number of module is not parseable, ignoring it for version checking "
-              + modVersion + " " + os[0] + " " + os[2], e);
+              + modVersion + " " + mod.getName(), e);
         }
       }
       final String serverVersion = version1 + "." + version2 + "." + version3;
@@ -308,24 +316,16 @@
       // regenerate the server module version table
 
       // read the current version modules
-      final Query<MobileServerDefinitionModule> serverModuleQry = OBDal
-          .getInstance()
-          .getSession()
-          .createQuery(
-              "from " + MobileServerDefinitionModule.ENTITY_NAME + " where "
-                  + MobileServerDefinitionModule.PROPERTY_OBMOBCSERVERDEFINITION + ".id=:id",
-              MobileServerDefinitionModule.class);
-      serverModuleQry.setParameter("id", thisServer.getId());
       final Map<String, MobileServerDefinitionModule> serverModules = new HashMap<>();
-      for (MobileServerDefinitionModule mdd : serverModuleQry.list()) {
+      OBDal.getInstance().getSession().refresh(thisServer.getOBSTSYNSERVERMODULEList());
+      for (MobileServerDefinitionModule mdd : thisServer.getOBSTSYNSERVERMODULEList()) {
         serverModules.put(mdd.getModule(), mdd);
       }
 
       // and then for each module check if a new version should be created
-      for (Object o : moduleQry.list()) {
-        final Object[] os = (Object[]) o;
-        final String module = (String) os[0];
-        final String version = (String) os[1];
+      for (Module moduleObj : modules) {
+        final String module = moduleObj.getId();
+        final String version = moduleObj.getVersion();
 
         if (serverModules.containsKey(module)) {
           final MobileServerDefinitionModule mdd = serverModules.get(module);
@@ -342,9 +342,9 @@
         serverModule.setClient(thisServer.getClient());
         serverModule.setOrganization(thisServer.getOrganization());
         serverModule.setObmobcServerDefinition(thisServer);
-        serverModule.setModule((String) os[0]);
-        serverModule.setVersion((String) os[1]);
-        serverModule.setName((String) os[2]);
+        serverModule.setModule(moduleObj.getId());
+        serverModule.setVersion(moduleObj.getVersion());
+        serverModule.setName(moduleObj.getName());
         OBDal.getInstance().save(serverModule);
       }
 
@@ -442,18 +442,15 @@
 
   private boolean areModuleVersionsCompatible() {
     // read all the store server modules
-    final Query<Object[]> moduleQry = OBDal
-        .getInstance()
-        .getSession()
-        .createQuery(
-            "select " + Module.PROPERTY_ID + "," + Module.PROPERTY_VERSION + " from "
-                + Module.ENTITY_NAME, Object[].class);
+
+    OBQuery<Module> obQuery = OBDal.getInstance().createQuery(Module.class, null);
+    obQuery.setFilterOnActive(false);
+    obQuery.setFilterOnReadableClients(false);
+    obQuery.setFilterOnReadableOrganization(false);
+    List<Module> modules = obQuery.list();
     final Map<String, String> storeServerVersionsByModule = new HashMap<>();
-    for (Object[] os : moduleQry.list()) {
-      final String moduleId = (String) os[0];
-      final String moduleVersion = (String) os[1];
-
-      storeServerVersionsByModule.put(moduleId, moduleVersion);
+    for (Module module : modules) {
+      storeServerVersionsByModule.put(module.getId(), module.getVersion());
     }
 
     if (storeServerVersionsByModule.isEmpty()) {
@@ -461,23 +458,21 @@
     }
 
     // read all the central server modules
-    final Query<Object[]> centralModuleQry = OBDal
-        .getInstance()
-        .getSession()
-        .createQuery(
-            "select " + MobileServerDefinitionModule.PROPERTY_MODULE + ","
-                + MobileServerDefinitionModule.PROPERTY_VERSION + ","
-                + MobileServerDefinitionModule.PROPERTY_NAME + " from "
-                + MobileServerDefinitionModule.ENTITY_NAME + " where "
-                + MobileServerDefinitionModule.PROPERTY_OBMOBCSERVERDEFINITION
-                + ".id=:centralServerId", Object[].class);
-    centralModuleQry.setParameter("centralServerId", MobileServerController.getInstance()
+    OBQuery<MobileServerDefinitionModule> serverModulesQry = OBDal.getInstance().createQuery(
+        MobileServerDefinitionModule.class,
+        MobileServerDefinitionModule.PROPERTY_OBMOBCSERVERDEFINITION + ".id=:centralServerId");
+    serverModulesQry.setFilterOnActive(false);
+    serverModulesQry.setFilterOnReadableClients(false);
+    serverModulesQry.setFilterOnReadableOrganization(false);
+    serverModulesQry.setNamedParameter("centralServerId", MobileServerController.getInstance()
         .getCentralServer().getId());
+    List<MobileServerDefinitionModule> serverModules = serverModulesQry.list();
+
     differences = "";
-    for (Object[] os : centralModuleQry.list()) {
-      final String moduleId = (String) os[0];
-      final String centralServerModuleVersion = (String) os[1];
-      final String moduleName = (String) os[2];
+    for (MobileServerDefinitionModule sm : serverModules) {
+      final String moduleId = sm.getModule();
+      final String centralServerModuleVersion = sm.getVersion();
+      final String moduleName = sm.getName();
       final String storeServerModuleVersion = storeServerVersionsByModule.get(moduleId);
       if (storeServerModuleVersion != null
           && !centralServerModuleVersion.equals(storeServerModuleVersion)) {
@@ -496,8 +491,7 @@
   }
 
   /**
-   * Return true if the store and central versions are the same or if no version differences should
-   * be checked.
+   * Return true if the store and central versions are the same or if no version differences should be checked.
    * 
    * @return
    */
diff --git a/src/org/openbravo/retail/storeserver/synchronization/eventhandler/AutoRouteAndPushGenericEventHandler.java b/src/org/openbravo/retail/storeserver/synchronization/eventhandler/AutoRouteAndPushGenericEventHandler.java
--- a/src/org/openbravo/retail/storeserver/synchronization/eventhandler/AutoRouteAndPushGenericEventHandler.java
+++ b/src/org/openbravo/retail/storeserver/synchronization/eventhandler/AutoRouteAndPushGenericEventHandler.java
@@ -25,7 +25,6 @@
 
 import javax.enterprise.event.Observes;
 
-import org.hibernate.resource.transaction.spi.TransactionStatus;
 import org.openbravo.client.kernel.event.EntityPersistenceEventObserver;
 import org.openbravo.client.kernel.event.TransactionBeginEvent;
 import org.openbravo.client.kernel.event.TransactionCompletedEvent;
@@ -52,8 +51,7 @@
   public final void onTransactionCompleted(@Observes TransactionCompletedEvent event) {
     String mobileServerKey = mobileServerKeyPerThread.get();
     mobileServerKeyPerThread.set(null);
-    if ((mobileServerKey == null)
-        || event.getTransaction().getStatus() == TransactionStatus.ROLLED_BACK) {
+    if (mobileServerKey == null) {
       return;
     }
     Set<String> mobileServersToPushData = new HashSet<>();
diff --git a/src/org/openbravo/retail/storeserver/synchronization/process/BusinessPartnerAndRelatedTablesSynchronizer.java b/src/org/openbravo/retail/storeserver/synchronization/process/BusinessPartnerAndRelatedTablesSynchronizer.java
--- a/src/org/openbravo/retail/storeserver/synchronization/process/BusinessPartnerAndRelatedTablesSynchronizer.java
+++ b/src/org/openbravo/retail/storeserver/synchronization/process/BusinessPartnerAndRelatedTablesSynchronizer.java
@@ -22,13 +22,14 @@
 
 import java.sql.Connection;
 import java.sql.PreparedStatement;
+import java.sql.ResultSet;
 import java.util.HashSet;
 import java.util.Iterator;
 import java.util.List;
 import java.util.Set;
 
 import org.hibernate.criterion.Restrictions;
-import org.hibernate.query.Query;
+import org.openbravo.base.exception.OBException;
 import org.openbravo.base.model.Entity;
 import org.openbravo.base.model.ModelProvider;
 import org.openbravo.base.model.Property;
@@ -36,9 +37,11 @@
 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.model.ad.datamodel.Column;
 import org.openbravo.model.ad.datamodel.Table;
 import org.openbravo.retail.storeserver.synchronization.OBSTSYN_BusinessPartnerRelatedTable;
+import org.openbravo.service.db.DalConnectionProvider;
 import org.slf4j.Logger;
 import org.slf4j.LoggerFactory;
 
@@ -145,16 +148,33 @@
    * returns the values of the given foreign key column for the rows whose id is one of the provided
    */
   private Set<String> getForeignKeyValues(Table table, Column foreignKeyColumn, Set<String> ids) {
-    Entity entity = ModelProvider.getInstance().getEntityByTableId(table.getId());
-    Property property = entity.getPropertyByColumnName(foreignKeyColumn.getDBColumnName());
     StringBuilder updatedIdsQueryString = new StringBuilder();
-    updatedIdsQueryString.append("select " + property.getName() + ".id ");
-    updatedIdsQueryString.append("from " + entity.getName() + " ");
-    updatedIdsQueryString.append("where id in (:ids)");
-    Query<String> updatedIdsQuery = OBDal.getInstance().getSession()
-        .createQuery(updatedIdsQueryString.toString(), String.class);
-    updatedIdsQuery.setParameterList("ids", ids);
-    return new HashSet<String>(updatedIdsQuery.list());
+    updatedIdsQueryString.append("select " + foreignKeyColumn.getDBColumnName() + ".id ");
+    updatedIdsQueryString.append("from " + table.getDBTableName() + " ");
+    updatedIdsQueryString.append("where id in ?");
+
+    PreparedStatement st = null;
+    ConnectionProvider cp = null;
+    Set<String> result = new HashSet<String>();
+    try {
+      cp = new DalConnectionProvider();
+      st = cp.getPreparedStatement(updatedIdsQueryString.toString());
+      st.setObject(1, ids.toArray(new String[ids.size()]));
+      ResultSet rs = st.executeQuery();
+      while (rs.next()) {
+        result.add(rs.getString(1));
+      }
+      rs.close();
+      return result;
+    } catch (Exception ex) {
+      throw new OBException(ex.toString(), ex);
+    } finally {
+      try {
+        cp.releasePreparedStatement(st);
+      } catch (Exception e) {
+        log.error("Error while releasing a prepared statement", e);
+      }
+    }
   }
 
   /**
@@ -239,13 +259,31 @@
     }
     StringBuilder updatedIdsQueryString = new StringBuilder();
     updatedIdsQueryString.append("select id ");
-    updatedIdsQueryString.append("from " + entity.getName() + " ");
-    updatedIdsQueryString.append("where " + property.getName() + ".id ");
+    updatedIdsQueryString.append("from " + entity.getTableName() + " ");
+    updatedIdsQueryString.append("where " + property.getColumnName() + ".id ");
     updatedIdsQueryString.append(" in (:ids)");
-    Query<String> updatedIdsQuery = OBDal.getInstance().getSession()
-        .createQuery(updatedIdsQueryString.toString(), String.class);
-    updatedIdsQuery.setParameterList("ids", idSet);
-    Set<String> newIdsOfUpdatedRows = new HashSet<String>(updatedIdsQuery.list());
-    return newIdsOfUpdatedRows;
+
+    PreparedStatement st = null;
+    ConnectionProvider cp = null;
+    Set<String> result = new HashSet<String>();
+    try {
+      cp = new DalConnectionProvider();
+      st = cp.getPreparedStatement(updatedIdsQueryString.toString());
+      st.setObject(1, idSet.toArray(new String[idSet.size()]));
+      ResultSet rs = st.executeQuery();
+      while (rs.next()) {
+        result.add(rs.getString(1));
+      }
+      rs.close();
+      return result;
+    } catch (Exception ex) {
+      throw new OBException(ex.toString(), ex);
+    } finally {
+      try {
+        cp.releasePreparedStatement(st);
+      } catch (Exception e) {
+        log.error("Error while releasing a prepared statement", e);
+      }
+    }
   }
 }
diff --git a/src/org/openbravo/retail/storeserver/synchronization/router/AssortmentRouter.java b/src/org/openbravo/retail/storeserver/synchronization/router/AssortmentRouter.java
--- a/src/org/openbravo/retail/storeserver/synchronization/router/AssortmentRouter.java
+++ b/src/org/openbravo/retail/storeserver/synchronization/router/AssortmentRouter.java
@@ -20,24 +20,23 @@
 
 package org.openbravo.retail.storeserver.synchronization.router;
 
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
 import java.util.HashSet;
-import java.util.List;
 import java.util.Set;
 
 import javax.enterprise.context.ApplicationScoped;
 
-import org.hibernate.query.NativeQuery;
-import org.hibernate.query.Query;
 import org.jumpmind.symmetric.model.DataMetaData;
 import org.jumpmind.symmetric.model.Node;
 import org.jumpmind.symmetric.model.TriggerRouter;
 import org.jumpmind.symmetric.route.SimpleRouterContext;
 import org.openbravo.base.exception.OBException;
 import org.openbravo.dal.core.OBContext;
-import org.openbravo.dal.service.OBDal;
-import org.openbravo.model.common.enterprise.Organization;
+import org.openbravo.database.ConnectionProvider;
 import org.openbravo.replication.symmetricds.extensions.AbstractClientFilteredRouter;
 import org.openbravo.replication.symmetricds.util.MobileServerProvider;
+import org.openbravo.service.db.DalConnectionProvider;
 
 /**
  * Router that routes entries from OBRETCO_ProductList and OBRETCO_Prol_Product. Changes done to an
@@ -60,30 +59,55 @@
       String assortmentId = getColumnValue(ASSORTMENT_ID, dataMetaData);
 
       // find all the organizations using this assortment
-      final Query<String> qry = OBDal
-          .getInstance()
-          .getSession()
-          .createQuery(
-              "select id from " + Organization.ENTITY_NAME + " where "
-                  + Organization.PROPERTY_OBRETCOPRODUCTLIST + "=:assortmentId", String.class);
-      qry.setParameter("assortmentId", assortmentId);
-      final List<String> list = qry.list();
-
-      // and send the assortment to all the related nodes
       Set<String> allNodes = new HashSet<String>();
-      for (String orgId : list) {
-        allNodes.addAll(MobileServerProvider.getInstance().getNodeIdsForOrganization(orgId));
+      PreparedStatement st = null;
+      ConnectionProvider cp = null;
+      Set<String> result = new HashSet<String>();
+      try {
+        cp = new DalConnectionProvider();
+        st = cp
+            .getPreparedStatement("select ad_org_id from ad_org where em_obretco_productlist_id=?");
+        st.setString(1, assortmentId);
+        ResultSet rs = st.executeQuery();
+        // and send the assortment to all the related nodes
+        while (rs.next()) {
+          result.add(rs.getString(1));
+          allNodes.addAll(MobileServerProvider.getInstance().getNodeIdsForOrganization(
+              rs.getString(1)));
+        }
+        rs.close();
+      } catch (Exception ex) {
+        throw new OBException(ex.toString(), ex);
+      } finally {
+        try {
+          cp.releasePreparedStatement(st);
+        } catch (Exception e) {
+          log.error("Error while releasing a prepared statement", e);
+        }
       }
 
       // only send to nodes which are valid for the product org
       if (dataMetaData.getTable().getName().toLowerCase().equals("obretco_prol_product")) {
-        @SuppressWarnings("rawtypes")
-        final NativeQuery sqlQry = OBDal.getInstance().getSession()
-            .createNativeQuery("select ad_org_id from m_product where m_product_id=:productId");
-        sqlQry.setParameter("productId", getColumnValue("m_product_id", dataMetaData));
-        String productOrgId = (String) sqlQry.uniqueResult();
-        allNodes.retainAll(MobileServerProvider.getInstance().getNodeIdsForOrganization(
-            productOrgId));
+        try {
+          cp = new DalConnectionProvider();
+          st = cp.getPreparedStatement("select ad_org_id from m_product where m_product_id=?");
+          st.setString(1, assortmentId);
+          st.setMaxRows(1);
+          ResultSet rs = st.executeQuery();
+          rs.next();
+          String productOrgId = rs.getString(1);
+          allNodes.retainAll(MobileServerProvider.getInstance().getNodeIdsForOrganization(
+              productOrgId));
+          rs.close();
+        } catch (Exception ex) {
+          throw new OBException(ex.toString(), ex);
+        } finally {
+          try {
+            cp.releasePreparedStatement(st);
+          } catch (Exception e) {
+            log.error("Error while releasing a prepared statement", e);
+          }
+        }
       }
 
       // - the location belongs to a business partner that is part of the store
diff --git a/src/org/openbravo/retail/storeserver/synchronization/router/LocationRouter.java b/src/org/openbravo/retail/storeserver/synchronization/router/LocationRouter.java
--- a/src/org/openbravo/retail/storeserver/synchronization/router/LocationRouter.java
+++ b/src/org/openbravo/retail/storeserver/synchronization/router/LocationRouter.java
@@ -20,6 +20,8 @@
 
 package org.openbravo.retail.storeserver.synchronization.router;
 
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
 import java.util.HashSet;
 import java.util.List;
 import java.util.Set;
@@ -27,16 +29,19 @@
 import javax.enterprise.context.ApplicationScoped;
 
 import org.hibernate.criterion.Restrictions;
-import org.hibernate.query.Query;
 import org.jumpmind.symmetric.io.data.DataEventType;
 import org.jumpmind.symmetric.model.DataMetaData;
 import org.jumpmind.symmetric.model.Node;
 import org.jumpmind.symmetric.model.TriggerRouter;
 import org.jumpmind.symmetric.route.SimpleRouterContext;
 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.mobile.core.MobileServerDefinition;
 import org.openbravo.model.common.businesspartner.BusinessPartner;
 import org.openbravo.model.common.enterprise.OrganizationInformation;
@@ -45,6 +50,7 @@
 import org.openbravo.replication.symmetricds.extensions.AbstractClientFilteredRouter;
 import org.openbravo.replication.symmetricds.util.MobileServerProvider;
 import org.openbravo.retail.storeserver.synchronization.OBSTSYN_Server_BPartner;
+import org.openbravo.service.db.DalConnectionProvider;
 
 /**
  * Router that routes entries from c_location. Changes done to a location will be synchronized to
@@ -185,15 +191,30 @@
 
   private boolean locationBelongsTo(String entityName, String locationPropertyName,
       Location location) {
-    Query<Object> qry = OBDal
-        .getInstance()
-        .getSession()
-        .createQuery(
-            "select 1 from " + entityName + " where " + locationPropertyName + ".id=:locationId",
-            Object.class);
-    qry.setParameter("locationId", location.getId());
-    qry.setMaxResults(1);
-    return qry.list().size() > 0;
+    Entity entity = ModelProvider.getInstance().getEntity(entityName);
+    Property property = entity.getProperty(locationPropertyName);
+
+    PreparedStatement st = null;
+    ConnectionProvider cp = null;
+    try {
+      cp = new DalConnectionProvider();
+      st = cp.getPreparedStatement("select 1 from " + entity.getTableName() + " where "
+          + property.getColumnName() + "=?");
+      st.setString(1, location.getId());
+      st.setMaxRows(1);
+      ResultSet rs = st.executeQuery();
+      final boolean result = rs.next();
+      rs.close();
+      return result;
+    } catch (Exception ex) {
+      throw new OBException(ex.toString(), ex);
+    } finally {
+      try {
+        cp.releasePreparedStatement(st);
+      } catch (Exception e) {
+        log.error("Error while releasing a prepared statement", e);
+      }
+    }
   }
 
   @Override
diff --git a/src/org/openbravo/retail/storeserver/synchronization/router/ProductPriceRouter.java b/src/org/openbravo/retail/storeserver/synchronization/router/ProductPriceRouter.java
--- a/src/org/openbravo/retail/storeserver/synchronization/router/ProductPriceRouter.java
+++ b/src/org/openbravo/retail/storeserver/synchronization/router/ProductPriceRouter.java
@@ -20,24 +20,25 @@
 
 package org.openbravo.retail.storeserver.synchronization.router;
 
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
 import java.util.Set;
 
 import javax.enterprise.context.ApplicationScoped;
 
-import org.hibernate.query.NativeQuery;
 import org.jumpmind.symmetric.model.DataMetaData;
 import org.jumpmind.symmetric.model.Node;
 import org.jumpmind.symmetric.model.TriggerRouter;
 import org.jumpmind.symmetric.route.SimpleRouterContext;
 import org.openbravo.base.exception.OBException;
 import org.openbravo.dal.core.OBContext;
-import org.openbravo.dal.service.OBDal;
+import org.openbravo.database.ConnectionProvider;
 import org.openbravo.replication.symmetricds.extensions.AbstractOrganizationBasedRouter;
 import org.openbravo.replication.symmetricds.util.MobileServerProvider;
+import org.openbravo.service.db.DalConnectionProvider;
 
 /**
- * Router that routes product price list entries taking into account the product price list and
- * product orgs.
+ * Router that routes product price list entries taking into account the product price list and product orgs.
  */
 @ApplicationScoped
 public class ProductPriceRouter extends AbstractOrganizationBasedRouter {
@@ -49,13 +50,18 @@
 
     // initial set of of nodes based on product price org
     Set<String> nodeIds = toNodeIds(nodes, null);
+    OBContext.setAdminMode();
+
+    PreparedStatement st = null;
+    ConnectionProvider cp = null;
     try {
-      OBContext.setAdminMode();
-      @SuppressWarnings("rawtypes")
-      final NativeQuery sqlQry = OBDal.getInstance().getSession()
-          .createNativeQuery("select ad_org_id from m_product where m_product_id=:productId");
-      sqlQry.setParameter("productId", getColumnValue("m_product_id", dataMetaData));
-      String productOrgId = (String) sqlQry.uniqueResult();
+      cp = new DalConnectionProvider();
+      st = cp.getPreparedStatement("select ad_org_id from m_product where m_product_id=?");
+      st.setString(1, getColumnValue("m_product_id", dataMetaData));
+      st.setMaxRows(1);
+      ResultSet rs = st.executeQuery();
+      rs.next();
+      String productOrgId = rs.getString(1);
       if (productOrgId != null) {
         nodeIds.retainAll(MobileServerProvider.getInstance()
             .getNodeIdsForOrganization(productOrgId));
@@ -63,13 +69,19 @@
         // the deletion will be sent to all stores, it is safe as if it the deleted record does not
         // exist, the change will be ignored
       }
-    } catch (Exception e) {
+      rs.close();
+      return nodeIds;
+    } catch (Exception ex) {
       throw new OBException("Error while routing a product price "
-          + dataMetaData.getData().getExternalData(), e, true);
+          + dataMetaData.getData().getExternalData(), ex, true);
     } finally {
+      try {
+        cp.releasePreparedStatement(st);
+      } catch (Exception e) {
+        log.error("Error while releasing a prepared statement", e);
+      }
       OBContext.restorePreviousMode();
     }
-    return nodeIds;
   }
 
   @Override
diff --git a/src/org/openbravo/retail/storeserver/synchronization/router/UserTerminalAccessRouter.java b/src/org/openbravo/retail/storeserver/synchronization/router/UserTerminalAccessRouter.java
--- a/src/org/openbravo/retail/storeserver/synchronization/router/UserTerminalAccessRouter.java
+++ b/src/org/openbravo/retail/storeserver/synchronization/router/UserTerminalAccessRouter.java
@@ -20,23 +20,25 @@
 
 package org.openbravo.retail.storeserver.synchronization.router;
 
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
 import java.util.HashSet;
-import java.util.List;
 import java.util.Map;
 import java.util.Set;
 
 import javax.enterprise.context.ApplicationScoped;
 
-import org.hibernate.query.NativeQuery;
 import org.jumpmind.symmetric.model.DataMetaData;
 import org.jumpmind.symmetric.model.Node;
 import org.jumpmind.symmetric.model.TriggerRouter;
 import org.jumpmind.symmetric.route.SimpleRouterContext;
+import org.openbravo.base.exception.OBException;
 import org.openbravo.dal.core.OBContext;
-import org.openbravo.dal.service.OBDal;
+import org.openbravo.database.ConnectionProvider;
 import org.openbravo.replication.symmetricds.extensions.AbstractOrganizationBasedRouter;
 import org.openbravo.retail.posterminal.OBPOSApplications;
 import org.openbravo.retail.posterminal.TerminalAccess;
+import org.openbravo.service.db.DalConnectionProvider;
 
 /**
  * {@link TerminalAccess} records are sometimes accidentally created in the * organization. This
@@ -60,23 +62,37 @@
       String accessOrgId = dataMap.get("ad_org_id");
       String terminalId = dataMap.get("obpos_applications_id");
 
-      @SuppressWarnings("rawtypes")
-      final NativeQuery qry = OBDal
-          .getInstance()
-          .getSession()
-          .createNativeQuery(
-              "select ad_org_id from obpos_applications where obpos_applications_id=:terminalId");
-      qry.setParameter("terminalId", terminalId);
-      List<String> list = qry.list();
-      if (list.isEmpty()) {
-        // the POS Terminal has been deleted, it is safe to synchronize the deletion to all servers
-        return toNodeIds(nodes, null);
-      }
-      String terminalOrgId = list.get(0);
-      if (accessOrgId.equals(terminalOrgId)) {
-        nodeIds = toNodeIds(nodes, null);
-      } else {
-        nodeIds = new HashSet<>();
+      PreparedStatement st = null;
+      ConnectionProvider cp = null;
+      try {
+        cp = new DalConnectionProvider();
+        st = cp
+            .getPreparedStatement("select ad_org_id from obpos_applications where obpos_applications_id=?");
+        st.setString(1, terminalId);
+        st.setMaxRows(1);
+        ResultSet rs = st.executeQuery();
+        if (!rs.next()) {
+          // the POS Terminal has been deleted, it is safe to synchronize the deletion to all servers
+          return toNodeIds(nodes, null);
+        }
+
+        String terminalOrgId = rs.getString(1);
+        if (accessOrgId.equals(terminalOrgId)) {
+          nodeIds = toNodeIds(nodes, null);
+        } else {
+          nodeIds = new HashSet<>();
+        }
+        rs.close();
+      } catch (Exception ex) {
+        throw new OBException("Error while routing a product price "
+            + dataMetaData.getData().getExternalData(), ex, true);
+      } finally {
+        try {
+          cp.releasePreparedStatement(st);
+        } catch (Exception e) {
+          log.error("Error while releasing a prepared statement", e);
+        }
+        OBContext.restorePreviousMode();
       }
     } finally {
       OBContext.restorePreviousMode();

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<Number> 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<String> getNodesWithProcessErrors() {
     Set<String> nodesWithProcessErrors = new HashSet<String>();
-    final Query<MobileServerDefinition> qry = OBDal
-        .getInstance()
-        .getSession()
-        .createQuery("select distinct obmobcServerDefinition from OBSTSYN_Error",
-            MobileServerDefinition.class);
-    List<MobileServerDefinition> 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<DataValidationError> {
   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<Object> 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<String> 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);
     }