Changeset 27617 for main/trunk/greenstone3/src/java/org/greenstone/gsdl3/util/DerbyWrapper.java

Timestamp:

2013-06-13T13:13:46+12:00 (11 years ago)

Author:

sjm84

Message:

Various improvements and fixes mostly to do with adding depositor functionality

File:

• main/trunk/greenstone3/src/java/org/greenstone/gsdl3/util/DerbyWrapper.java (modified) (5 diffs)

main/trunk/greenstone3/src/java/org/greenstone/gsdl3/util/DerbyWrapper.java

@@ -21 +21 @@
 import java.sql.Connection;
 import java.sql.DriverManager;
+import java.sql.PreparedStatement;
 import java.sql.ResultSet;
 import java.sql.SQLException;
@@ -36 +37 @@
     static final String USERS = "users";
     static final String ROLES = "roles";
+    static final String DATA = "data";
     private Connection conn = null;
     private Statement state = null;
@@ -142 +144 @@
             state.execute("create table users (username varchar(40) not null, password varchar(40) not null, accountstatus varchar(10), comment varchar(100), email varchar(40), primary key(username))");
             state.execute("create table roles (username varchar(40) not null, role varchar(40) not null, primary key (username, role))");
+            state.execute("create table data (username varchar(40) not null, name varchar(128) not null, value clob, primary key (username, name))");
             state.execute("insert into " + USERS + " values ('admin', '" + Authentication.hashPassword("admin") + "', 'true', 'change the password for this account as soon as possible', '')");
             state.execute("insert into " + ROLES + " values ('admin', 'administrator')");
@@ -174 +177 @@
         for (HashMap<String, String> user : users)
         {
-            ResultSet gs = state.executeQuery("SELECT role FROM roles WHERE username = '" + user.get("username") + "'");
+            ResultSet gs = state.executeQuery("SELECT role FROM " + ROLES + " WHERE username = '" + user.get("username") + "'");
             String group = "";
             while (gs.next())
@@ -196 +199 @@
             return userQueryResult;
         }
+    }
+
+    public boolean addUserData(String username, String name, String value)
+    {
+        //Check if we already have a value under this name
+        boolean found = false;
+        try
+        {
+            ResultSet rs = state.executeQuery("SELECT * FROM " + DATA + " WHERE username='" + username + "' AND name='" + name + "'");
+            if (rs.next())
+            {
+                found = true;
+            }
+            else
+            {
+                found = false;
+            }
+        }
+        catch (Exception ex)
+        {
+            System.out.println("exception thrown:");
+            if (ex instanceof SQLException)
+            {
+                printSQLError((SQLException) ex);
+            }
+            else
+            {
+                ex.printStackTrace();
+            }
+            closeDatabase();
+            System.out.println("Error:" + ex.getMessage());
+            return false;
+        }
+
+        try
+        {
+            if (!found)
+            {
+                PreparedStatement stmt = null;
+                stmt = conn.prepareStatement("INSERT INTO " + DATA + " VALUES (?, ?, ?)");
+                stmt.setString(1, username);
+                stmt.setString(2, name);
+                stmt.setString(3, value);
+                stmt.executeUpdate();
+            }
+            else
+            {
+                PreparedStatement stmt = null;
+                stmt = conn.prepareStatement("UPDATE " + DATA + " SET value=? WHERE username=? AND name=?");
+                stmt.setString(1, value);
+                stmt.setString(2, username);
+                stmt.setString(3, name);
+                stmt.executeUpdate();
+            }
+        }
+        catch (Exception ex)
+        {
+            System.out.println("exception thrown:");
+            if (ex instanceof SQLException)
+            {
+                printSQLError((SQLException) ex);
+            }
+            else
+            {
+                ex.printStackTrace();
+            }
+            closeDatabase();
+            System.out.println("Error:" + ex.getMessage());
+            return false;
+        }
+        return true;
+    }
+
+    public String getUserData(String username, String name)
+    {
+        try
+        {
+            ResultSet rs = state.executeQuery("SELECT * FROM " + DATA + " WHERE username='" + username + "' AND name='" + name + "'");
+            if (rs.next())
+            {
+                return rs.getString("value");
+            }
+        }
+        catch (Exception ex)
+        {
+            System.out.println("exception thrown:");
+            if (ex instanceof SQLException)
+            {
+                printSQLError((SQLException) ex);
+            }
+            else
+            {
+                ex.printStackTrace();
+            }
+            closeDatabase();
+            System.out.println("Error:" + ex.getMessage());
+        }
+        return null;
     }