- Timestamp:
- 2012-01-26T11:17:01+13:00 (12 years ago)
- File:
-
- 1 edited
Legend:
- Unmodified
- Added
- Removed
-
main/trunk/greenstone3/src/java/org/greenstone/gsdl3/util/DerbyWrapper.java
r16869 r24983 31 31 public class DerbyWrapper 32 32 { 33 static final String PROTOCOL = "jdbc:derby:"; 34 static final String DRIVER = "org.apache.derby.jdbc.EmbeddedDriver"; 35 static final String USERSDB = "usersDB"; 36 static final String USERS = "users"; 37 private Connection conn=null; 38 private Statement state=null; 39 private String protocol_str; 40 41 public DerbyWrapper(){} 42 public DerbyWrapper(String dbpath){ 43 connectDatabase(dbpath,false); 44 } 45 46 public void connectDatabase(String dbpath, boolean create_database){ 47 try{ 48 Class.forName(DRIVER).newInstance(); 49 //System.out.println("Loaded the embedded driver."); 50 protocol_str = PROTOCOL + dbpath; 51 if (create_database){ 52 conn = DriverManager.getConnection(protocol_str + ";create=true"); 53 }else{ 54 conn = DriverManager.getConnection(protocol_str); 55 } 56 state = conn.createStatement(); 57 }catch (Throwable e){ 58 System.out.println("exception thrown:"); 59 if (e instanceof SQLException){ 60 printSQLError((SQLException) e); 61 }else{ 62 e.printStackTrace(); 63 } 64 } 65 } 66 67 public void closeDatabase() { 68 state = null; 69 conn = null; 70 boolean gotSQLExc = false; 71 try{ 72 DriverManager.getConnection(protocol_str + ";shutdown=true"); 73 }catch (SQLException se){ 74 gotSQLExc = true; 75 } 76 if (!gotSQLExc){ 77 System.out.println("Database did not shut down normally"); 78 } 79 } 80 81 public void createDatabase()throws SQLException{ 82 conn.setAutoCommit(false); 83 state.execute("create table users (username varchar(40) not null, password varchar(40) not null, groups varchar(500), accountstatus varchar(10), comment varchar(100), primary key(username))"); 84 //ystem.out.println("table users created successfully!"); 85 state.execute("insert into "+USERS+" values ('admin', 'admin', 'administrator,all-collections-editor', 'true', 'change the password for this account as soon as possible')"); 86 conn.commit(); 87 } 88 89 public UserQueryResult listAllUser()throws SQLException{ 90 UserQueryResult userQueryResult=new UserQueryResult(); 91 String sql_list_all_user="select username, password, groups, accountstatus, comment from " + USERS ; 92 93 ResultSet rs = state.executeQuery(sql_list_all_user); 94 while (rs.next()){ 95 String returned_username= rs.getString("username"); 96 String returned_password=rs.getString("password"); 97 String returned_groups=rs.getString("groups"); 98 String returned_accountstatus=rs.getString("accountstatus"); 99 String returned_comment=rs.getString("comment"); 100 userQueryResult.addUserTerm(returned_username,returned_password,returned_groups,returned_accountstatus,returned_comment); 101 } 102 if (userQueryResult.getSize()==0){ 103 System.out.println("couldn't find any users"); 104 return null; 105 }else{ 106 return userQueryResult; 107 } 108 } 109 110 public String addUser(String username, String password, String groups, String accountstatus, String comment)throws SQLException{ 111 conn.setAutoCommit(false); 112 String sql_insert_user="insert into " + USERS + " values ('"+username +"', '"+password+"', '"+groups+"', '"+accountstatus+"', '"+comment+"')"; 113 try{ 114 state.execute(sql_insert_user); 115 conn.commit(); 116 }catch(Throwable e){ 117 System.out.println("exception thrown:"); 118 if (e instanceof SQLException){ 119 printSQLError((SQLException) e); 120 }else{ 121 e.printStackTrace(); 122 } 123 closeDatabase(); 124 System.out.println("Error:"+e.getMessage()); 125 return "Error:"+e.getMessage(); 126 } 127 return "succeed"; 128 } 129 130 public String deleteUser(String del_username)throws SQLException{ 131 conn.setAutoCommit(false); 132 String sql_delete_user="delete from " + USERS + " where username='"+del_username+"'"; 133 try{ 134 state.execute(sql_delete_user); 135 conn.commit(); 136 }catch(Throwable e){ 137 System.out.println("exception thrown:"); 138 if (e instanceof SQLException){ 139 printSQLError((SQLException) e); 140 }else{ 141 e.printStackTrace(); 142 } 143 closeDatabase(); 144 return "Error:"+e.getMessage(); 145 } 146 return "succeed"; 147 } 148 149 public boolean deleteAllUser() throws SQLException{ 150 conn.setAutoCommit(false); 151 try{ 152 state.execute("delete from " + USERS); 153 conn.commit(); 154 }catch(Throwable e){ 155 System.out.println("exception thrown:"); 156 if (e instanceof SQLException){ 157 printSQLError((SQLException) e); 158 }else{ 159 e.printStackTrace(); 160 } 161 closeDatabase(); 162 return false; 163 } 164 return true; 165 } 166 167 public UserQueryResult findUser(String username, String password)throws SQLException{ 168 UserQueryResult userQueryResult=new UserQueryResult(); 169 170 conn.setAutoCommit(false); 171 String sql_find_user="SELECT username, password, groups, accountstatus, comment FROM "+USERS; 172 String append_sql=""; 173 174 if (username!=null){ 175 append_sql=" WHERE username = '" + username+ "'"; 176 } 177 if (password!=null){ 178 if (append_sql.equals("")){ 179 append_sql=" WHERE password = '"+password+"'"; 180 }else{ 181 append_sql+=" and password = '"+password+"'"; 182 } 183 } 184 if (!append_sql.equals("")){ 185 sql_find_user+=append_sql; 186 } 187 ResultSet rs = state.executeQuery(sql_find_user); 188 while (rs.next()){ 189 String returned_username= rs.getString("username"); 190 //System.out.println("returned_username :" + returned_username); 191 String returned_password=rs.getString("password"); 192 //System.out.println("returned_password :" + returned_password); 193 String returned_groups=rs.getString("groups"); 194 //System.out.println("returned_groups :" + returned_groups); 195 String returned_accountstatus=rs.getString("accountstatus"); 196 //System.out.println("returned_accountstatus :" + returned_accountstatus); 197 String returned_comment=rs.getString("comment"); 198 //System.out.println("returned_comment :" + returned_comment); 199 userQueryResult.addUserTerm(returned_username,returned_password,returned_groups,returned_accountstatus,returned_comment); 200 //System.out.println(userQueryResult.toString()); 201 } 202 conn.commit(); 203 if (userQueryResult.getSize()>0){ 204 return userQueryResult; 205 }else{ 206 System.out.println("couldn't find the user"); 207 return null; 208 } 209 } 210 211 public String modifyUserInfo(String username, String new_password,String groups, String accountstatus, String comment)throws SQLException{ 212 conn.setAutoCommit(false); 213 String sql_modify_user_info="update " + USERS +" set "; 214 if (new_password!=null && !new_password.equals("")){ 215 sql_modify_user_info += "password='" + new_password +"'"; 216 } 217 218 if (groups!=null && accountstatus!=null && comment!=null){ 219 sql_modify_user_info += ", groups='" + groups + "'" + ", accountstatus='" + accountstatus + "'" + ", comment='" + comment + "'"; 220 } 221 sql_modify_user_info +=" where username='" + username+"'"; 222 223 try{ 224 System.out.println(sql_modify_user_info); 225 state.execute(sql_modify_user_info); 226 conn.commit(); 227 }catch(Throwable e){ 228 System.out.println("exception thrown:"); 229 if (e instanceof SQLException){ 230 printSQLError((SQLException) e); 231 }else{ 232 e.printStackTrace(); 233 } 234 closeDatabase(); 235 return "Error:"+e.getMessage(); 236 } 237 return "succeed"; 238 } 239 240 public void db2txt() throws SQLException{ 241 UserQueryResult userQueryResult=new UserQueryResult(); 242 String sql_list_all_user="select username, password, groups, accountstatus, comment from " + USERS ; 243 ResultSet rs = state.executeQuery(sql_list_all_user); 244 245 while (rs.next()){ 246 String returned_username= rs.getString("username"); 247 System.out.println("[" + returned_username+"]"); 248 String returned_comment=rs.getString("comment"); 249 System.out.println("<comment>" + returned_comment); 250 String returned_accountstatus=rs.getString("accountstatus"); 251 System.out.println("<enabled>" + returned_accountstatus); 252 String returned_groups=rs.getString("groups"); 253 System.out.println("<groups>" + returned_groups); 254 String returned_password=rot13(rs.getString("password")); 255 System.out.println("<password>" + returned_password); 256 System.out.println("<username>" + returned_username); 257 System.out.println(""); 258 System.out.println("----------------------------------------------------------------------"); 259 } 260 conn.commit(); 261 closeDatabase(); 262 } 263 264 static void printSQLError(SQLException e) 265 { 266 while (e != null) 267 { 268 System.out.println(e.toString()); 269 e = e.getNextException(); 270 } 271 } 272 273 //Simply use rot-13 to encrypt and decrypt the password 274 public String rot13(String password){ 275 String out_password=""; 276 for (int i = 0; i < password.length(); i++) { 277 char c = password.charAt(i); 278 if (c >= 'a' && c <= 'm') c += 13; 279 else if (c >= 'n' && c <= 'z') c -= 13; 280 else if (c >= 'A' && c <= 'M') c += 13; 281 else if (c >= 'A' && c <= 'Z') c -= 13; 282 out_password+=c; 283 } 284 return out_password; 285 } 33 static final String PROTOCOL = "jdbc:derby:"; 34 static final String DRIVER = "org.apache.derby.jdbc.EmbeddedDriver"; 35 static final String USERSDB = "usersDB"; 36 static final String USERS = "users"; 37 private Connection conn = null; 38 private Statement state = null; 39 private String protocol_str; 40 41 public DerbyWrapper() 42 { 43 } 44 45 public DerbyWrapper(String dbpath) 46 { 47 connectDatabase(dbpath, false); 48 } 49 50 public void connectDatabase(String dbpath, boolean create_database) 51 { 52 try 53 { 54 Class.forName(DRIVER).newInstance(); 55 //System.out.println("Loaded the embedded driver."); 56 protocol_str = PROTOCOL + dbpath; 57 if (create_database) 58 { 59 conn = DriverManager.getConnection(protocol_str + ";create=true"); 60 } 61 else 62 { 63 conn = DriverManager.getConnection(protocol_str); 64 } 65 state = conn.createStatement(); 66 } 67 catch (Throwable e) 68 { 69 System.out.println("exception thrown:"); 70 if (e instanceof SQLException) 71 { 72 printSQLError((SQLException) e); 73 } 74 else 75 { 76 e.printStackTrace(); 77 } 78 } 79 } 80 81 public void closeDatabase() 82 { 83 state = null; 84 conn = null; 85 boolean gotSQLExc = false; 86 try 87 { 88 DriverManager.getConnection(protocol_str + ";shutdown=true"); 89 } 90 catch (SQLException se) 91 { 92 gotSQLExc = true; 93 } 94 if (!gotSQLExc) 95 { 96 System.out.println("Database did not shut down normally"); 97 } 98 } 99 100 public void createDatabase() throws SQLException 101 { 102 conn.setAutoCommit(false); 103 state.execute("create table users (username varchar(40) not null, password varchar(40) not null, groups varchar(500), accountstatus varchar(10), comment varchar(100), primary key(username))"); 104 //ystem.out.println("table users created successfully!"); 105 state.execute("insert into " + USERS + " values ('admin', 'admin', 'administrator,all-collections-editor', 'true', 'change the password for this account as soon as possible')"); 106 conn.commit(); 107 } 108 109 public UserQueryResult listAllUser() throws SQLException 110 { 111 UserQueryResult userQueryResult = new UserQueryResult(); 112 String sql_list_all_user = "select username, password, groups, accountstatus, comment from " + USERS; 113 114 ResultSet rs = state.executeQuery(sql_list_all_user); 115 while (rs.next()) 116 { 117 String returned_username = rs.getString("username"); 118 String returned_password = rs.getString("password"); 119 String returned_groups = rs.getString("groups"); 120 String returned_accountstatus = rs.getString("accountstatus"); 121 String returned_comment = rs.getString("comment"); 122 userQueryResult.addUserTerm(returned_username, returned_password, returned_groups, returned_accountstatus, returned_comment); 123 } 124 if (userQueryResult.getSize() == 0) 125 { 126 System.out.println("couldn't find any users"); 127 return null; 128 } 129 else 130 { 131 return userQueryResult; 132 } 133 } 134 135 public String addUser(String username, String password, String groups, String accountstatus, String comment) throws SQLException 136 { 137 conn.setAutoCommit(false); 138 String sql_insert_user = "insert into " + USERS + " values ('" + username + "', '" + password + "', '" + groups + "', '" + accountstatus + "', '" + comment + "')"; 139 try 140 { 141 state.execute(sql_insert_user); 142 conn.commit(); 143 } 144 catch (Throwable e) 145 { 146 System.out.println("exception thrown:"); 147 if (e instanceof SQLException) 148 { 149 printSQLError((SQLException) e); 150 } 151 else 152 { 153 e.printStackTrace(); 154 } 155 closeDatabase(); 156 System.out.println("Error:" + e.getMessage()); 157 return "Error:" + e.getMessage(); 158 } 159 return "succeed"; 160 } 161 162 public String deleteUser(String del_username) throws SQLException 163 { 164 conn.setAutoCommit(false); 165 String sql_delete_user = "delete from " + USERS + " where username='" + del_username + "'"; 166 try 167 { 168 state.execute(sql_delete_user); 169 conn.commit(); 170 } 171 catch (Throwable e) 172 { 173 System.out.println("exception thrown:"); 174 if (e instanceof SQLException) 175 { 176 printSQLError((SQLException) e); 177 } 178 else 179 { 180 e.printStackTrace(); 181 } 182 closeDatabase(); 183 return "Error:" + e.getMessage(); 184 } 185 return "succeed"; 186 } 187 188 public boolean deleteAllUser() throws SQLException 189 { 190 conn.setAutoCommit(false); 191 try 192 { 193 state.execute("delete from " + USERS); 194 conn.commit(); 195 } 196 catch (Throwable e) 197 { 198 System.out.println("exception thrown:"); 199 if (e instanceof SQLException) 200 { 201 printSQLError((SQLException) e); 202 } 203 else 204 { 205 e.printStackTrace(); 206 } 207 closeDatabase(); 208 return false; 209 } 210 return true; 211 } 212 213 public UserQueryResult findUser(String username, String password) throws SQLException 214 { 215 UserQueryResult userQueryResult = new UserQueryResult(); 216 217 conn.setAutoCommit(false); 218 String sql_find_user = "SELECT username, password, groups, accountstatus, comment FROM " + USERS; 219 String append_sql = ""; 220 221 if (username != null) 222 { 223 append_sql = " WHERE username = '" + username + "'"; 224 } 225 if (password != null) 226 { 227 if (append_sql.equals("")) 228 { 229 append_sql = " WHERE password = '" + password + "'"; 230 } 231 else 232 { 233 append_sql += " and password = '" + password + "'"; 234 } 235 } 236 if (!append_sql.equals("")) 237 { 238 sql_find_user += append_sql; 239 } 240 ResultSet rs = state.executeQuery(sql_find_user); 241 while (rs.next()) 242 { 243 String returned_username = rs.getString("username"); 244 //System.out.println("returned_username :" + returned_username); 245 String returned_password = rs.getString("password"); 246 //System.out.println("returned_password :" + returned_password); 247 String returned_groups = rs.getString("groups"); 248 //System.out.println("returned_groups :" + returned_groups); 249 String returned_accountstatus = rs.getString("accountstatus"); 250 //System.out.println("returned_accountstatus :" + returned_accountstatus); 251 String returned_comment = rs.getString("comment"); 252 //System.out.println("returned_comment :" + returned_comment); 253 userQueryResult.addUserTerm(returned_username, returned_password, returned_groups, returned_accountstatus, returned_comment); 254 //System.out.println(userQueryResult.toString()); 255 } 256 conn.commit(); 257 if (userQueryResult.getSize() > 0) 258 { 259 return userQueryResult; 260 } 261 else 262 { 263 System.out.println("couldn't find the user"); 264 return null; 265 } 266 } 267 268 public String modifyUserInfo(String username, String new_password, String groups, String accountstatus, String comment) throws SQLException 269 { 270 conn.setAutoCommit(false); 271 String sql_modify_user_info = "update " + USERS + " set "; 272 if (new_password != null && !new_password.equals("")) 273 { 274 sql_modify_user_info += "password='" + new_password + "'"; 275 } 276 277 if (groups != null && accountstatus != null && comment != null) 278 { 279 sql_modify_user_info += ", groups='" + groups + "'" + ", accountstatus='" + accountstatus + "'" + ", comment='" + comment + "'"; 280 } 281 sql_modify_user_info += " where username='" + username + "'"; 282 283 try 284 { 285 System.out.println(sql_modify_user_info); 286 state.execute(sql_modify_user_info); 287 conn.commit(); 288 } 289 catch (Throwable e) 290 { 291 System.out.println("exception thrown:"); 292 if (e instanceof SQLException) 293 { 294 printSQLError((SQLException) e); 295 } 296 else 297 { 298 e.printStackTrace(); 299 } 300 closeDatabase(); 301 return "Error:" + e.getMessage(); 302 } 303 return "succeed"; 304 } 305 306 public void db2txt() throws SQLException 307 { 308 UserQueryResult userQueryResult = new UserQueryResult(); 309 String sql_list_all_user = "select username, password, groups, accountstatus, comment from " + USERS; 310 ResultSet rs = state.executeQuery(sql_list_all_user); 311 312 while (rs.next()) 313 { 314 String returned_username = rs.getString("username"); 315 System.out.println("[" + returned_username + "]"); 316 String returned_comment = rs.getString("comment"); 317 System.out.println("<comment>" + returned_comment); 318 String returned_accountstatus = rs.getString("accountstatus"); 319 System.out.println("<enabled>" + returned_accountstatus); 320 String returned_groups = rs.getString("groups"); 321 System.out.println("<groups>" + returned_groups); 322 String returned_password = rot13(rs.getString("password")); 323 System.out.println("<password>" + returned_password); 324 System.out.println("<username>" + returned_username); 325 System.out.println(""); 326 System.out.println("----------------------------------------------------------------------"); 327 } 328 conn.commit(); 329 closeDatabase(); 330 } 331 332 static void printSQLError(SQLException e) 333 { 334 while (e != null) 335 { 336 System.out.println(e.toString()); 337 e = e.getNextException(); 338 } 339 } 340 341 //Simply use rot-13 to encrypt and decrypt the password 342 public String rot13(String password) 343 { 344 String out_password = ""; 345 for (int i = 0; i < password.length(); i++) 346 { 347 char c = password.charAt(i); 348 if (c >= 'a' && c <= 'm') 349 c += 13; 350 else if (c >= 'n' && c <= 'z') 351 c -= 13; 352 else if (c >= 'A' && c <= 'M') 353 c += 13; 354 else if (c >= 'A' && c <= 'Z') 355 c -= 13; 356 out_password += c; 357 } 358 return out_password; 359 } 286 360 }
Note:
See TracChangeset
for help on using the changeset viewer.