Fixed issue 39755 added in OBDAL lockForNoKeyUpdate
authorSandra Huguet <sandra.huguet@openbravo.com>
Tue, 04 Dec 2018 15:42:37 +0100
changeset 35207 18fd96916ab5
parent 35206 708c5c4d51a0
child 35208 26214ce6e789
Fixed issue 39755 added in OBDAL lockForNoKeyUpdate

new lockForNoKeyUpdate method to allow the use "FOR NO KEY UPDATE",
because "FOR UPDATE" in PostgreSQL is too aggressive.
src/org/openbravo/dal/service/OBDal.java
--- a/src/org/openbravo/dal/service/OBDal.java	Tue Dec 04 15:36:12 2018 +0100
+++ b/src/org/openbravo/dal/service/OBDal.java	Tue Dec 04 15:42:37 2018 +0100
@@ -21,6 +21,8 @@
 
 import java.io.Serializable;
 import java.sql.Connection;
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
 import java.util.ArrayList;
 import java.util.HashMap;
 import java.util.List;
@@ -51,6 +53,7 @@
 import org.openbravo.database.SessionInfo;
 import org.openbravo.model.ad.system.Client;
 import org.openbravo.model.common.enterprise.Organization;
+import org.openbravo.service.db.DalConnectionProvider;
 
 /**
  * The OBDal class offers the main external access to the Data Access Layer. The variety of data
@@ -726,4 +729,48 @@
     final Entity e = ModelProvider.getInstance().getEntity(entityName);
     OBContext.getOBContext().getEntityAccessChecker().checkReadable(e);
   }
+
+  /**
+   * Creates a lock for no key update
+   * 
+   * @param entity
+   *          the type to create the query for
+   * @param id
+   *          identifier of the record
+   * @return id
+   */
+  public String lockForNoKeyUpdate(Entity entity, String id) {
+    final String RDBMS = new DalConnectionProvider(false).getRDBMS();
+    String strSQL;
+    if ("ORACLE".equals(RDBMS)) {
+      strSQL = "SELECT * FROM " + entity.getTableName() + " WHERE " + entity.getTableName()
+          + "_id =? FOR UPDATE";
+    } else {
+      strSQL = "SELECT * FROM " + entity.getTableName() + " WHERE " + entity.getTableName()
+          + "_id =? FOR NO KEY UPDATE";
+    }
+    PreparedStatement sqlQuery = null;
+    ResultSet rs = null;
+    try {
+      sqlQuery = new DalConnectionProvider(false).getPreparedStatement(strSQL);
+      sqlQuery.setString(1, id);
+      sqlQuery.execute();
+      sqlQuery.setMaxRows(1);
+      rs = sqlQuery.getResultSet();
+      while (rs.next()) {
+        return rs.getString(1);
+      }
+    } catch (Exception e) {
+      log.error("Error when executing query", e);
+    } finally {
+      try {
+        if (sqlQuery != null) {
+          sqlQuery.close();
+        }
+      } catch (Exception e) {
+        log.error("Error when closing statement", e);
+      }
+    }
+    return null;
+  }
 }
\ No newline at end of file