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

Last change on this file was 38856, checked in by anupama, 5 weeks ago

Testing if usersDB exists should use the full path. There was an error message about no driver being found for the jdbc url going into the tomcat log that I hadn't noticed before. This is because createDatabaseIfNeeded tested if usersDB existed, and if not tried to create it. The problem was that the file usersDB that it tested for existence is now being created with the relative string of usersDB instead of the full path inside web/etc, so it couldn't locate that.
File size: 23.8 KB
Line 
1/*
2 * DerbyWrapper.java
3 * Copyright (C) 2008 New Zealand Digital Library, http://www.nzdl.org
4 *
5 * This program is free software; you can redistribute it and/or modify
6 * it under the terms of the GNU General Public License as published by
7 * the Free Software Foundation; either version 2 of the License, or
8 * (at your option) any later version.
9 *
10 * This program is distributed in the hope that it will be useful,
11 * but WITHOUT ANY WARRANTY; without even the implied warranty of
12 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
13 * GNU General Public License for more details.
14 *
15 * You should have received a copy of the GNU General Public License
16 * along with this program; if not, write to the Free Software
17 * Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
18 */
19package org.greenstone.gsdl3.util;
20
21import java.io.File;
22import java.sql.Connection;
23import java.sql.DriverManager;
24import java.sql.PreparedStatement;
25import java.sql.ResultSet;
26import java.sql.SQLException;
27import java.sql.Statement;
28import java.util.ArrayList;
29import java.util.HashMap;
30import java.util.HashSet;
31
32import org.greenstone.gsdl3.service.Authentication;
33import org.greenstone.util.GlobalProperties;
34
35import org.apache.log4j.*;
36/**
37 * The UserDB stores the groups in the "roles" table in expanded form, i.e. expandedGroups.
38 * This is what is retrieved as well. Call UserTermInfo.compactGroup(expandedGroups)
39 * to get the compacted form closer to what the user may have entered.
40*/
41public class DerbyWrapper
42{
43 static final String PORT;
44 static final String DERBYSERVER;
45 static final String PROTOCOL;
46 static Logger logger = Logger.getLogger(org.greenstone.gsdl3.util.DerbyWrapper.class.getName());
47 static final String DRIVER = "org.apache.derby.jdbc.ClientDriver"; //"org.apache.derby.jdbc.EmbeddedDriver";
48 static final String USERSDB = "usersDB";
49 public static final String USERSDB_DIR;
50
51 // static code block to initialise the above
52 static {
53 // GlobalProperties won't be loaded at this point if running ant config-admin or ant config-user
54 // from the command line (both of which call ant update-userdb which in turn calls ModifyUsersDB.java)
55 // In such a case, the ant command will have set the system property (-Dgsdl3_writablehome)
56 // and passed this to ModifyUsersDB.java. Use that to load the GlobalProperties at this point
57
58 if(GlobalProperties.getGSDL3Home() == null) { // testing whether GlobalProperties is already loaded
59 String gsdl3_writablehome = System.getProperty("gsdl3.writablehome"); // set by 'ant update-userdb' cmd
60
61 //System.err.println("@@@@@ writablehome: " + gsdl3_writablehome);
62 GlobalProperties.loadGlobalProperties(gsdl3_writablehome);
63 }
64
65 //System.err.println("@@@@@ GlobalProperties.getGSDL3Home(): " + GlobalProperties.getGSDL3Home()); //test
66
67 // No more fallback values, use exactly what's propagated into global.properties from build.properties
68 String port = GlobalProperties.getProperty("derby.server.port");//, "8327"); GS' default derby port.
69 if(port.indexOf("@") == -1) { // if there are still @placeholders@ in global.properties, then use fallback values
70 DERBYSERVER = GlobalProperties.getProperty("derby.server");//, "localhost");
71 } else {
72 port = "8327"; // This setting is used by installer. 8327 is GS default derby port. Note: Out-of-box derby defaults to port 1527
73 DERBYSERVER = "localhost";
74 }
75
76 // Check any -D JVM properties passed to GS3 jarfile and, iff set, use it in preference to any PORT already found
77 // See https://stackoverflow.com/questions/5045608/proper-usage-of-java-d-command-line-parameters
78 PORT = System.getProperty("derby.server.port", port);
79
80 PROTOCOL = "jdbc:derby://"+DERBYSERVER+":"+PORT+"/"; // "jdbc:derby://localhost:8327"; // by default
81 //System.out.println("@@@ PROTOCOL:" + PROTOCOL); //check in installer
82
83 // if embedded derby Driver (UNTESTED BRANCH), use the full path to usersDB,
84 // else if networked derby server then just append dbname (usersDB) to protocol
85 if(DRIVER.equals("org.apache.derby.jdbc.EmbeddedDriver")) {
86 USERSDB_DIR = GlobalProperties.getGSDL3Home() + File.separatorChar + "etc" + File.separatorChar + USERSDB;
87 } else { // derby network driver, just go direct to usersDB
88 USERSDB_DIR = USERSDB;
89 }
90 }
91
92 public static final String USERS = "users";
93 public static final String ROLES = "roles";
94 static final String DATA = "data";
95 private Connection conn = null;
96 private static String protocol_str;
97
98 public DerbyWrapper(String dbpath)
99 {
100 connectDatabase(dbpath, false);
101 }
102
103 public static void createDatabaseIfNeeded()
104 {
105 protocol_str = PROTOCOL + USERSDB_DIR;
106 File usersDB_file = new File(USERSDB_DIR);
107
108 // to test for file existence, ensure we have full path to it,
109 // as "usersDB" on its own may not exist at the level we're testing this from
110 if(!USERSDB_DIR.startsWith(GlobalProperties.getGSDL3Home())) {
111 usersDB_file = new File(GlobalProperties.getGSDL3Home() + File.separatorChar + "etc" + File.separatorChar + USERSDB_DIR);
112 }
113
114 if (!usersDB_file.exists())
115 {
116 String etc_dir = GlobalProperties.getGSDL3Home() + File.separatorChar + "etc";
117 File etc_file = new File(etc_dir);
118 if (!etc_file.exists())
119 {
120 boolean success = etc_file.mkdir();
121 if (!success)
122 {
123 System.err.println("Couldn't create the etc dir under " + GlobalProperties.getGSDL3Home() + ".");
124 }
125 }
126 try
127 {
128 //Class.forName(DRIVER).getDeclaredConstructor().newInstance();
129 DerbyWrapper.createDatabase(DriverManager.getConnection(protocol_str + ";create=true"));
130 }
131 catch (Exception ex)
132 {
133 ex.printStackTrace();
134 }
135 }
136 }
137
138 public void connectDatabase(String dbpath, boolean create_database)
139 {
140 try
141 {
142 if (conn != null)
143 {
144 System.err.println("Connection already established, close the database first");
145 return;
146 }
147
148 Class.forName(DRIVER).getDeclaredConstructor().newInstance();
149 protocol_str = PROTOCOL + dbpath;
150 if (create_database)
151 {
152 conn = DriverManager.getConnection(protocol_str + ";create=true");
153 }
154 else
155 {
156 conn = DriverManager.getConnection(protocol_str);
157 }
158 conn.setAutoCommit(false);
159 }
160 catch (Throwable e)
161 {
162 System.out.println("exception thrown:");
163 if (e instanceof SQLException)
164 {
165 printSQLError((SQLException) e);
166 }
167 else
168 {
169 e.printStackTrace();
170 }
171 }
172 }
173
174 public void closeDatabase()
175 {
176 try
177 {
178 conn.commit();
179 conn.close();
180 conn = null;
181 }
182 catch (SQLException e)
183 {
184 e.printStackTrace();
185 }
186 }
187
188 public static void shutdownDatabaseServer()
189 {
190
191 // shutdown the server if we're using an embedded derby
192 // if we're a derby client using the derby network server
193
194 if(!DRIVER.equals("org.apache.derby.jdbc.EmbeddedDriver")) {
195 return;
196 }
197
198 boolean gotSQLExc = false;
199 try
200 {
201 DriverManager.getConnection(PROTOCOL + ";shutdown=true");
202 }
203 catch (SQLException se)
204 {
205 // this is good (i.e. what Derby is designed to do on a successful shutdown)
206 gotSQLExc = true;
207 //System.out.println("Shutdown returned: " + se);
208 }
209 catch (Exception e)
210 {
211 e.printStackTrace();
212 }
213 if (!gotSQLExc)
214 {
215 System.err.println("Warning: Derby did not shut down normally");
216 }
217 }
218
219 public void clearUserData()
220 {
221 try
222 {
223 Statement state = conn.createStatement();
224 state.execute("drop table data");
225 state.execute("create table data (username varchar(40) not null, name varchar(128) not null, value clob, primary key (username, name))");
226 conn.commit();
227 state.close();
228 }
229 catch (SQLException e)
230 {
231 e.printStackTrace();
232 }
233 }
234
235 public void clearTrackerData()
236 {
237 try
238 {
239 Statement state = conn.createStatement();
240 state.execute("drop table usertracker");
241 state.execute("create table usertracker (username varchar(40) not null, collection varchar(128) not null, site varchar(128) not null, oid varchar(128) not null, time varchar(128) not null, action varchar(128) not null, primary key (username, time))");
242 conn.commit();
243 state.close();
244 }
245 catch (SQLException e)
246 {
247 e.printStackTrace();
248 }
249 }
250
251 public static void createDatabase(Connection conn)
252 {
253 try
254 {
255 Statement state = conn.createStatement();
256 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))");
257 state.execute("create table roles (username varchar(40) not null, role varchar(40) not null, primary key (username, role))");
258 state.execute("create table data (username varchar(40) not null, name varchar(128) not null, value clob, primary key (username, name))");
259 state.execute("create table usertracker (username varchar(40) not null, collection varchar(128) not null, site varchar(128) not null, oid varchar(128) not null, time varchar(128) not null, action varchar(128) not null, primary key (username, time))");
260
261 state.execute("insert into " + USERS + " values ('admin', '" + Authentication.hashPassword("admin") + "', 'true', 'change the password for this account as soon as possible', '')");
262 state.execute("insert into " + ROLES + " values ('admin', 'administrator')");
263 state.execute("insert into " + ROLES + " values ('admin', 'all-collections-editor')");
264 conn.commit();
265 state.close();
266 conn.close();
267 }
268 catch (Exception ex)
269 {
270 ex.printStackTrace();
271 }
272 }
273
274 public void addUserAction(String username, String site, String collection, String oid, String action)
275 {
276 try
277 {
278 Statement state = conn.createStatement();
279 state.execute("INSERT INTO usertracker VALUES ('" + username + "', '" + collection + "', '" + site + "', '" + oid + "', '" + System.currentTimeMillis() + "', '" + action + "')");
280 conn.commit();
281 state.close();
282 }
283 catch (Exception ex)
284 {
285 ex.printStackTrace();
286 }
287 }
288
289 public ArrayList<HashMap<String, String>> getMostRecentUserActions(String site, String collection, String oid)
290 {
291 ArrayList<HashMap<String, String>> actions = new ArrayList<HashMap<String, String>>();
292
293 try
294 {
295 String query = "SELECT username, action, time FROM usertracker WHERE site = '" + site + "' and collection = '" + collection + "' and oid = '" + oid + "' ORDER BY time";
296 Statement state = conn.createStatement();
297 ResultSet rs = state.executeQuery(query);
298 conn.commit();
299
300 HashSet<String> usernamesSeen = new HashSet<String>();
301 while (rs.next())
302 {
303 String timeStr = rs.getString("time");
304 long time = Long.parseLong(timeStr);
305
306 if (System.currentTimeMillis() - time > 6000)
307 {
308 continue;
309 }
310
311 HashMap<String, String> action = new HashMap<String, String>();
312 if (!usernamesSeen.contains(rs.getString("username")))
313 {
314 action.put("username", rs.getString("username"));
315 action.put("action", rs.getString("action"));
316 actions.add(action);
317
318 usernamesSeen.add(rs.getString("username"));
319 }
320 }
321 state.close();
322
323 clearOldUserActions();
324 }
325 catch (Exception ex)
326 {
327 ex.printStackTrace();
328 }
329 return actions;
330 }
331
332 public void clearOldUserActions()
333 {
334 try
335 {
336 Statement state = conn.createStatement();
337 state.execute("DELETE FROM usertracker WHERE (CAST (time AS BIGINT)) < " + (System.currentTimeMillis() - 20000));
338 conn.commit();
339 state.close();
340 }
341 catch (Exception ex)
342 {
343 ex.printStackTrace();
344 }
345 }
346
347 public UserQueryResult listAllUsers() throws SQLException
348 {
349 UserQueryResult userQueryResult = new UserQueryResult();
350 String sql_list_all_user = "SELECT username, password, accountstatus, email, comment FROM " + USERS;
351
352 ArrayList<HashMap<String, String>> users = new ArrayList<HashMap<String, String>>();
353 Statement state = conn.createStatement();
354 ResultSet rs = state.executeQuery(sql_list_all_user);
355 conn.commit();
356 state.close();
357
358 while (rs.next())
359 {
360 HashMap<String, String> user = new HashMap<String, String>();
361 user.put("username", rs.getString("username"));
362 user.put("password", rs.getString("password"));
363 user.put("as", rs.getString("accountstatus"));
364 user.put("comment", rs.getString("comment"));
365 user.put("email", rs.getString("email"));
366
367 users.add(user);
368 }
369
370 for (HashMap<String, String> user : users)
371 {
372 ResultSet gs = state.executeQuery("SELECT role FROM " + ROLES + " WHERE username = '" + user.get("username") + "'");
373 String group = "";
374 while (gs.next())
375 {
376 if (!group.equals(""))
377 {
378 group += ",";
379 }
380 group += gs.getString("role");
381 }
382 userQueryResult.addUserTerm(user.get("username"), user.get("password"), group, user.get("as"), user.get("comment"), user.get("email"));
383 }
384
385 if (userQueryResult.getSize() == 0)
386 {
387 System.out.println("couldn't find any users");
388 return null;
389 }
390 else
391 {
392 return userQueryResult;
393 }
394 }
395
396 public boolean addUserData(String username, String name, String value)
397 {
398 //Check if we already have a value under this name
399 boolean found = false;
400 try
401 {
402 Statement state = conn.createStatement();
403 ResultSet rs = state.executeQuery("SELECT * FROM " + DATA + " WHERE username='" + username + "' AND name='" + name + "'");
404 conn.commit();
405 if (rs.next())
406 {
407 found = true;
408 }
409 else
410 {
411 found = false;
412 }
413 state.close();
414 }
415 catch (Exception ex)
416 {
417 System.out.println("exception thrown:");
418 if (ex instanceof SQLException)
419 {
420 printSQLError((SQLException) ex);
421 ex.printStackTrace();
422 }
423 else
424 {
425 ex.printStackTrace();
426 }
427
428 System.out.println("Error:" + ex.getMessage());
429 return false;
430 }
431
432 try
433 {
434 PreparedStatement stmt = null;
435 if (!found)
436 {
437 stmt = conn.prepareStatement("INSERT INTO " + DATA + " VALUES (?, ?, ?)");
438 stmt.setString(1, username);
439 stmt.setString(2, name);
440 stmt.setString(3, value);
441 stmt.executeUpdate();
442 }
443 else
444 {
445 stmt = conn.prepareStatement("UPDATE " + DATA + " SET value=? WHERE username=? AND name=?");
446 stmt.setString(1, value);
447 stmt.setString(2, username);
448 stmt.setString(3, name);
449 stmt.executeUpdate();
450 }
451 conn.commit();
452 stmt.close();
453 }
454 catch (Exception ex)
455 {
456 System.out.println("exception thrown:");
457 if (ex instanceof SQLException)
458 {
459 printSQLError((SQLException) ex);
460 }
461 else
462 {
463 ex.printStackTrace();
464 }
465
466 System.out.println("Error:" + ex.getMessage());
467 return false;
468 }
469 return true;
470 }
471
472 public String getUserData(String username, String name)
473 {
474 try
475 {
476 Statement state = conn.createStatement();
477 ResultSet rs = state.executeQuery("SELECT * FROM " + DATA + " WHERE username='" + username + "' AND name='" + name + "'");
478 conn.commit();
479 if (rs.next())
480 {
481 return rs.getString("value");
482 }
483 state.close();
484 }
485 catch (Exception ex)
486 {
487 System.out.println("exception thrown:");
488 if (ex instanceof SQLException)
489 {
490 printSQLError((SQLException) ex);
491 }
492 else
493 {
494 ex.printStackTrace();
495 }
496
497 System.out.println("Error:" + ex.getMessage());
498 }
499 return null;
500 }
501
502 public boolean addUser(String username, String password, String expandedGroups, String accountstatus, String comment, String email)
503 {
504 try
505 {
506 Statement state = conn.createStatement();
507 String sql_insert_user = "insert into " + USERS + " values ('" + username + "', '" + password + "', '" + accountstatus + "', '" + comment + "', '" + email + "')";
508 state.execute(sql_insert_user);
509
510 String[] groupArray = expandedGroups.split(",");
511 for (String g : groupArray)
512 {
513 String sql_insert_group = "insert into " + ROLES + " values ('" + username + "', '" + g + "')";
514 state.execute(sql_insert_group);
515 }
516
517 conn.commit();
518 state.close();
519 }
520 catch (Throwable e)
521 {
522 System.out.println("exception thrown:");
523 if (e instanceof SQLException)
524 {
525 printSQLError((SQLException) e);
526 }
527 else
528 {
529 e.printStackTrace();
530 }
531
532 System.out.println("Error:" + e.getMessage());
533 return false;
534 }
535
536 return true;
537 }
538
539 public boolean deleteUser(String del_username)
540 {
541 try
542 {
543 String sql_delete_user = "delete from " + USERS + " where username='" + del_username + "'";
544 String sql_delete_groups = "delete from " + ROLES + " where username='" + del_username + "'";
545 Statement state = conn.createStatement();
546 state.execute(sql_delete_user);
547 state.execute(sql_delete_groups);
548 conn.commit();
549 state.close();
550 }
551 catch (Throwable e)
552 {
553 System.out.println("exception thrown:");
554 if (e instanceof SQLException)
555 {
556 printSQLError((SQLException) e);
557 }
558 else
559 {
560 e.printStackTrace();
561 }
562 return false;
563 }
564 return true;
565 }
566
567 public boolean deleteAllUser() throws SQLException
568 {
569 try
570 {
571 Statement state = conn.createStatement();
572 state.execute("delete from " + USERS);
573 state.execute("delete from " + ROLES);
574 conn.commit();
575 state.close();
576 }
577 catch (Throwable e)
578 {
579 System.out.println("exception thrown:");
580 if (e instanceof SQLException)
581 {
582 printSQLError((SQLException) e);
583 }
584 else
585 {
586 e.printStackTrace();
587 }
588
589 return false;
590 }
591 return true;
592 }
593
594 // single arg method for convenience
595 public UserQueryResult findUser(String username) {
596 return findUser(username, null);
597 }
598 public UserQueryResult findUser(String username, String password)
599 {
600 UserQueryResult userQueryResult = new UserQueryResult();
601
602 String sql_find_user = "SELECT username, password, accountstatus, comment, email FROM " + USERS;
603 String append_sql = "";
604
605 if (username != null)
606 {
607 append_sql = " WHERE username = '" + username + "'";
608 }
609 if (password != null)
610 {
611 if (append_sql.equals(""))
612 {
613 append_sql = " WHERE password = '" + password + "'";
614 }
615 else
616 {
617 append_sql += " and password = '" + password + "'";
618 }
619 }
620 if (!append_sql.equals(""))
621 {
622 sql_find_user += append_sql;
623 }
624
625 try
626 {
627 ArrayList<HashMap<String, String>> users = new ArrayList<HashMap<String, String>>();
628 Statement state = conn.createStatement();
629 ResultSet rs = state.executeQuery(sql_find_user);
630 while (rs.next())
631 {
632 HashMap<String, String> user = new HashMap<String, String>();
633 user.put("username", rs.getString("username"));
634 user.put("password", rs.getString("password"));
635 user.put("as", rs.getString("accountstatus"));
636 user.put("comment", rs.getString("comment"));
637 user.put("email", rs.getString("email"));
638
639 users.add(user);
640 }
641 conn.commit();
642
643 for (HashMap<String, String> user : users)
644 {
645 ResultSet gs = state.executeQuery("SELECT role FROM " + ROLES + " WHERE username = '" + user.get("username") + "'");
646 conn.commit();
647
648 String group = "";
649 while (gs.next())
650 {
651 if (!group.equals(""))
652 {
653 group += ",";
654 }
655 group += gs.getString("role");
656 }
657
658 userQueryResult.addUserTerm(user.get("username"), user.get("password"), group, user.get("as"), user.get("comment"), user.get("email"));
659 }
660 state.close();
661 }
662 catch (Exception ex)
663 {
664 ex.printStackTrace();
665 return null;
666 }
667
668 if (userQueryResult.getSize() > 0)
669 {
670 return userQueryResult;
671 }
672 else
673 {
674 return null;
675 }
676 }
677
678 public String modifyUserInfo(String username, String new_password, String expandedGroups, String accountstatus, String comment, String email)
679 {
680 try
681 {
682 String sql_modify_user_info = "update " + USERS + " set ";
683
684 boolean needComma = false;
685 if (new_password != null && !new_password.equals(""))
686 {
687 sql_modify_user_info += "password='" + new_password + "'";
688 needComma = true;
689 }
690
691 if (accountstatus != null) {
692 sql_modify_user_info += (needComma ? "," : "") + " accountstatus='" + accountstatus + "'";
693 needComma = true;
694 }
695 if (comment != null)
696 {
697 sql_modify_user_info += (needComma ? "," : "") + " comment='" + comment + "'";
698 needComma = true;
699 }
700
701 if (email != null)
702 {
703 sql_modify_user_info += (needComma ? "," : "") + " email='" + email + "'";
704 needComma = true;
705 }
706 Statement state = conn.createStatement();
707 if (needComma) {
708 // it is possible that we are only modifying groups, so use needComma to
709 // see if we actually need to run this step
710 sql_modify_user_info += " where username='" + username + "'";
711 //System.err.println("about to execute sql: "+sql_modify_user_info);
712
713 state.execute(sql_modify_user_info);
714 }
715 if (expandedGroups != null) {
716 // delete the groups we have currently
717
718 String sql_delete_groups = "delete from " + ROLES + " where username='" + username + "'";
719 state.execute(sql_delete_groups);
720
721 // add the new groups
722 String[] groupsArray = expandedGroups.split(",");
723 for (String g : groupsArray)
724 {
725 String sql_insert_group = "insert into " + ROLES + " values ('" + username + "', '" + g + "')";
726 state.execute(sql_insert_group);
727 }
728
729 conn.commit();
730 state.close();
731 }
732 }
733 catch (Throwable e)
734 {
735 System.out.println("exception thrown:");
736 if (e instanceof SQLException)
737 {
738 printSQLError((SQLException) e);
739 }
740 else
741 {
742 e.printStackTrace();
743 }
744
745 return "Error:" + e.getMessage();
746 }
747 return "succeed";
748 }
749
750 public void db2txt()
751 {
752 System.err.println(db2txtString());
753 }
754
755 public String db2txtString()
756 {
757 //String db2txt = "Error in converting db2txt string.";
758 String db2txt = "";
759 try
760 {
761 String sql_list_all_user = "select username, password, accountstatus, comment, email from " + USERS;
762
763 Statement state = conn.createStatement();
764 ResultSet rs = state.executeQuery(sql_list_all_user);
765
766 ArrayList<HashMap<String, String>> infoMap = new ArrayList<HashMap<String, String>>();
767
768 while (rs.next())
769 {
770 HashMap<String, String> userMap = new HashMap<String, String>();
771 userMap.put("username", rs.getString("username"));
772 userMap.put("password", rs.getString("password"));
773 userMap.put("status", rs.getString("accountstatus"));
774 userMap.put("comment", rs.getString("comment"));
775 userMap.put("email", rs.getString("email"));
776 infoMap.add(userMap);
777 }
778 conn.commit();
779
780 StringBuffer buffer = new StringBuffer();//("-------------------------------------");
781 for (HashMap<String, String> user : infoMap)
782 {
783 ResultSet groupsSet = state.executeQuery("SELECT role FROM " + ROLES + " WHERE username = '" + user.get("username") + "'");
784 String returnedGroups = "";
785 while (groupsSet.next())
786 {
787 if (!returnedGroups.equals(""))
788 {
789 returnedGroups += ",";
790 }
791 returnedGroups += groupsSet.getString("role");
792 }
793 conn.commit();
794
795 buffer.append("USERNAME = " + user.get("username"));
796 buffer.append("\nPASSWORD = " + user.get("password"));
797 buffer.append("\nGROUPS = " + returnedGroups);
798 buffer.append("\nSTATUS = " + user.get("status"));
799 buffer.append("\nCOMMENT = " + user.get("comment"));
800 buffer.append("\nEMAIL = " + user.get("email"));
801 buffer.append("\n-------------------------------------\n");
802 }
803 db2txt = buffer.toString();
804
805 conn.commit();
806 state.close();
807 }
808 catch (Exception ex)
809 {
810 ex.printStackTrace();
811 }
812 finally
813 {
814 return db2txt;
815 }
816 }
817
818 static void printSQLError(SQLException e)
819 {
820 while (e != null)
821 {
822 System.out.println(e.toString());
823 e = e.getNextException();
824 }
825 }
826
827 public void clearUserDataWithPrefix(String username, String prefix)
828 {
829 try
830 {
831 Statement state = conn.createStatement();
832 state.execute("DELETE FROM data WHERE username = '" + username + "' AND SUBSTR(name, 1, " + prefix.length() + ") = '" + prefix + "'");
833 conn.commit();
834 state.close();
835 }
836 catch (Exception ex)
837 {
838 ex.printStackTrace();
839 }
840 }
841}
Note: See TracBrowser for help on using the repository browser.