Changeset 16883 for greenstone3

Timestamp:

2008-08-18T16:20:25+12:00 (16 years ago)

Author:

xiao

Message:

add changes to work with the ActivityManagement.java

Location:

greenstone3/trunk/src/java/org/greenstone/gsdl3/sql

Files:

• DatabaseFactory.java (modified) (3 diffs)
• MetadataDBWrapper.java (modified) (2 diffs)
• derby/DerbyDBWrapper.java (modified) (5 diffs)

greenstone3/trunk/src/java/org/greenstone/gsdl3/sql/DatabaseFactory.java

@@ -36 +36 @@
         return server;
     } catch (Exception e) {
-        logger.info("Couldn't load the database server "+ fullName);
+        logger.debug("Couldn't load the database server "+ fullName);
     }
     
@@ -57 +57 @@
        catch (Exception e2) {
            // failed again, give up
-           logger.info("Couldn't load the sql statement  "+ sqlstate);
+           logger.debug("Couldn't load the sql statement  "+ sqlstate);
        }
        }
@@ -97 +97 @@
             catch (Exception e2) {
             // failed again, give up
-            logger.info("Couldn't load the sql statement  "+ sqlstate);
+            logger.debug("Couldn't load the sql statement  "+ sqlstate);
             }
         }
         }
     } catch (Exception e) {
-        logger.info("Couldn't load the database wrapper "+ fullName);
+        logger.debug("Couldn't load the database wrapper "+ fullName);
     }
     

greenstone3/trunk/src/java/org/greenstone/gsdl3/sql/MetadataDBWrapper.java

@@ -2 +2 @@
 
 import java.util.ArrayList;
+
+import java.sql.ResultSet;
+import java.sql.SQLException;
+
 import org.greenstone.gsdl3.util.DBInfo;
 import org.greenstone.gsdl3.sql.SQLServer;
@@ -15 +19 @@
     public boolean openConnection(String databasepath);
 
+    public boolean openAndCreateConnection(String databasepath);
+
      //return a list of rows
     public ArrayList executeQuery(String query_statement);
+
+    public ResultSet queryResultSet(String query_statement);
+
+    public boolean execute(String stat);
+
+    public boolean executeUpdate(String stat);
+
+    public void check4Table(String stat) throws SQLException;
 
     public void closeConnection(String databasepath);

greenstone3/trunk/src/java/org/greenstone/gsdl3/sql/derby/DerbyDBWrapper.java

@@ -14 +14 @@
 import java.util.HashMap;
 import java.util.Iterator;
+
 
 import org.greenstone.gsdl3.util.DBInfo;
@@ -66 +67 @@
     }
     catch(SQLException sqle){
-        logger.error("Database Error occured when creating a statement object",sqle);
+        logger.debug("Database Error occured when creating a statement object",sqle);
         return false;
     }
@@ -72 +73 @@
     }
 
-    //return a list of rows array->hashmap
+    public boolean openAndCreateConnection(String databasepath){
+        connection = sqlServer.connectAndCreate(databasepath);
+
+        if (connection == null) {
+            logger.error("sql connection is null. database path="+databasepath);
+            return false;
+        }
+
+        try{
+            connection.setAutoCommit(true); 
+            //by passing the two arguments, the ResultSet returned from executeQuery is updatable
+            statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_UPDATABLE);
+            if (statement == null) {
+                logger.error("statement is null");
+                return false;
+            }
+
+        }
+        catch(SQLException sqle){
+            logger.debug("Database Error occured when creating a statement object",sqle);
+            return false;
+        }
+        return true;
+    }
+
     public synchronized ArrayList executeQuery(String query_statement){
     //the database hasn't been correct yet
@@ -89 +114 @@
            HashMap arow = new HashMap();
            for(int i = 1; i <= numOfColumns ; i++){
-           arow.put(rsmd.getColumnName(i).toLowerCase(), rs.getObject(i));
+               arow.put(rsmd.getColumnName(i).toLowerCase(), rs.getObject(i));
            }
            results.add(arow);  
@@ -95 +120 @@
     }
     catch(SQLException sqle){
-        logger.error("Database Error occured when executeQuery " + query_statement,sqle);
+        logger.debug("Database Error occured when executeQuery " + query_statement,sqle);
         return results;
     }
+    catch(Exception e){         
+        logger.debug(e);
+        return null;
+    }
     return results;
     }
-    
-     /** check for the existence of a table**/
-    public boolean check4Table (String check_table) {
-         try {
-      statement.execute(check_table);
-      }  catch (SQLException sqle) {
-      String theError = (sqle).getSQLState();
-      if (theError.equals("42X05"))   // Table does not exist
-          { 
-          return false;
-          }   
-      else{
-          return true;
-      }
-      }
-      //  System.out.println("Just got the warning - table exists OK ");
-      return true;
-    }   
+    /**
+     * Same as executeQuery except the return type is ResultSet
+     * @param stat
+     * @return ResultSet of querying the statement 'stat'
+     */
+    public synchronized ResultSet queryResultSet(String stat){
+        ResultSet rs = null;
+        try{            
+            if (statement == null){
+                logger.info("Null sql statement provided.");
+                return null;
+            }
+            logger.debug(stat);
+            rs = statement.executeQuery(stat);
+            
+            logger.info("sql stat="+stat+ " result="+rs);
+        }
+        catch(SQLException sqle){
+            logger.info("Database Error occured when execute query " + stat, sqle);
+            return null;
+        }
+        catch(Exception e){         
+            logger.info("Exception="+e);
+            return null;
+        }
+        logger.debug(" result="+rs);
+        return rs;
+    }
+    /**
+     * Used by create (table)
+     */
+    public synchronized boolean execute(String stat) {
+        boolean rs;
+        try{            
+            if (statement == null){
+                logger.info("statement is null.");
+                return false;
+            }
+
+            rs = statement.execute(stat);
+//          connection.commit();
+        }
+        catch(SQLException sqle){           
+            logger.debug("Database Error occured when execute query " + stat, sqle);
+            return false;
+        }
+        catch(Exception e){         
+            logger.debug(e);
+            return false;
+        }
+        return rs;
+    }
+    /**
+     * Used by insert, update, and delete
+     */
+    public synchronized boolean executeUpdate(String stat) {
+        int rs;
+        try{            
+            if (statement == null){
+                logger.info("statement is null.");
+                return false;
+            }
+            
+            //either the row count of INSERT, UPDATE OR DELETE statement, or 0 if the statement returns nothing
+            rs = statement.executeUpdate(stat);
+//          connection.commit();
+            logger.info("sql stat="+stat+ " result="+rs);
+        }
+        catch(SQLException sqle){           
+            logger.info("Database Error occured when execute query " + stat, sqle);
+            return false;
+        }
+        catch(Exception e){         
+            logger.info("Exception="+e);
+            return false;
+        }
+        logger.info(" result="+rs);
+        return (rs==-1)? false : true;
+    }
+    /**
+     * Used by checking the existence of table
+     * Same as the method 'execute' except throws SQLException
+     */
+    public void check4Table(String stat) throws SQLException {
+        statement.executeQuery(stat);
+    }
 
     public void closeConnection(String databasepath){