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

Last change on this file since 30240 was 30240, checked in by ak19, 9 years ago

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