[prev in list] [next in list] [prev in thread] [next in thread] 

List:       slide-dev
Subject:    Make CommonRDBMSAdapter#revokePermission use re-usable PreparedStatements
From:       Martin_Kalén <mkalen () apache ! org>
Date:       2005-04-07 8:13:06
Message-ID: 4254EB92.9010409 () apache ! org
[Download RAW message or body]

Greetings,
  as noted in my comment to bugzilla #34343 my previous patch
to fix CommonRDBMSAdapter#revokePermission only made the SQL
usable, not fully _re-usable_ as PreparedStatement.

A better approach is to use exactly the same technique as
the patch I filed in bugzilla for #34343, ie keep a statement
parameter ("?") in the SQL string and use JDBC setNull() if
the value is null.

In this way, the RDBMS can actually re-use the prepared statement
regardless of explicit revisionNumber or null-value. Previously
it was almost a waste of resources using prepared statement when
parts of the SQL were hardcoded anyway.

"SELECT * FROM TABLE WHERE A = ?" is reusable and the DB-server
only needs to parse/prepare it once for A = '1','2' etc.

"SELECT * FROM TABLE WHERE A = 1" and "SELECT * FROM TABLE WHERE A = 2"
are not interchangeably re-usable and needs to be parsed/prepared twice.

(In my second example it's actually better to use Statement instead
of PreparedStatement since there is no parameters and no chance of
re-use, thus we can skip any overhead for preparing the statement.)

I'm attaching a new unified patch for CommonRDBMSAdapter to implement
this, and also change the SQL to the syntax Tomasz Majchrzak used
for PostgreSQL. His syntax is IMO more readable and still works with
Oracle. Tested with Oracle9i.

Regards,
  Martin

["revokePermission_reusable_statement.patch" (text/plain)]

Index: src/stores/org/apache/slide/store/impl/rdbms/CommonRDBMSAdapter.java
===================================================================
RCS file: /home/cvspublic/jakarta-slide/src/stores/org/apache/slide/store/impl/rdbms/CommonRDBMSAdapter.java,v
 retrieving revision 1.10
diff -u -r1.10 CommonRDBMSAdapter.java
--- src/stores/org/apache/slide/store/impl/rdbms/CommonRDBMSAdapter.java	6 Apr 2005 \
                09:25:00 -0000	1.10
+++ src/stores/org/apache/slide/store/impl/rdbms/CommonRDBMSAdapter.java	7 Apr 2005 \
07:59:06 -0000 @@ -5,7 +5,7 @@
  *
  * ====================================================================
  *
- * Copyright 2004 The Apache Software Foundation 
+ * Copyright 2004-2005 The Apache Software Foundation
  *
  * Licensed under the Apache License, Version 2.0 (the "License");
  * you may not use this file except in compliance with the License.
@@ -23,11 +23,16 @@
 
 package org.apache.slide.store.impl.rdbms;
 
-import java.io.*;
+import java.io.File;
+import java.io.FileInputStream;
+import java.io.FileOutputStream;
+import java.io.IOException;
+import java.io.InputStream;
 import java.sql.Connection;
 import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.sql.SQLException;
+import java.sql.Types;
 
 import org.apache.slide.common.Service;
 import org.apache.slide.common.ServiceAccessException;
@@ -225,19 +230,20 @@
         if (permission == null) return;
         PreparedStatement statement = null;
         try {
-            final NodeRevisionNumber revisionNumber = \
                permission.getRevisionNumber();
-            final String versionNoCriteria;
-            
-            if (revisionNumber == null) {
-                versionNoCriteria = "IS NULL";
-            } else {
-                versionNoCriteria = "= '" + revisionNumber.toString() + "'";
-            }
+            final NodeRevisionNumber revisionNumber;
+            revisionNumber = permission.getRevisionNumber();
             statement = connection.prepareStatement(
-                    "delete from PERMISSIONS where PERMISSIONS.OBJECT_ID in (select \
ou.URI_ID from URI ou, URI su, URI au where ou.URI_STRING = ? and SUBJECT_ID = \
su.URI_ID and su.URI_STRING = ? and ACTION_ID = au.URI_ID and au.URI_STRING = ? and \
VERSION_NO " + versionNoCriteria + ")"); +                "delete from PERMISSIONS \
where (OBJECT_ID, SUBJECT_ID, ACTION_ID) IN" + +                " (SELECT ou.URI_ID, \
su.URI_ID, au.URI_ID FROM URI ou, URI su, URI au WHERE ou.URI_STRING = ? and \
su.URI_STRING = ? and au.URI_STRING = ?)" + +                " and VERSION_NO = ?");
             statement.setString(1, permission.getObjectUri());
             statement.setString(2, permission.getSubjectUri());
             statement.setString(3, permission.getActionUri());
+            if (revisionNumber == null) {
+                statement.setNull(4, Types.VARCHAR);
+            } else {
+                statement.setString(4, revisionNumber.toString());
+            }
             statement.executeUpdate();
         } catch (SQLException e) {
             throw createException(e, uri.toString());
@@ -290,7 +296,7 @@
     protected void storeContent(
         Connection connection, Uri uri,
 	NodeRevisionDescriptor revisionDescriptor,
-	NodeRevisionContent revisionContent) throws IOException, SQLException 
+	NodeRevisionContent revisionContent) throws IOException, SQLException
     {
         assureVersionInfo(connection, uri, revisionDescriptor);
         



---------------------------------------------------------------------
To unsubscribe, e-mail: slide-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: slide-dev-help@jakarta.apache.org

[prev in list] [next in list] [prev in thread] [next in thread]