[14289] | 1 | package org.greenstone.gsdl3.util;
|
---|
| 2 |
|
---|
| 3 | import javax.swing.*;
|
---|
| 4 | import java.io.File;
|
---|
| 5 | import java.sql.Connection;
|
---|
| 6 | import java.sql.DriverManager;
|
---|
| 7 | import java.sql.ResultSet;
|
---|
| 8 | import java.sql.SQLException;
|
---|
| 9 | import java.sql.SQLWarning;
|
---|
| 10 | import java.sql.Statement;
|
---|
| 11 | import java.util.Properties;
|
---|
| 12 |
|
---|
| 13 | public class DerbyWrapper
|
---|
| 14 | {
|
---|
| 15 | static final String PROTOCOL = "jdbc:derby:";
|
---|
| 16 | static final String DRIVER = "org.apache.derby.jdbc.EmbeddedDriver";
|
---|
| 17 | static final String USERSDB = "usersDB";
|
---|
| 18 | static final String USERS = "users";
|
---|
| 19 | private Connection conn=null;
|
---|
| 20 | private Statement state=null;
|
---|
| 21 | private String protocol_str;
|
---|
| 22 |
|
---|
| 23 | public DerbyWrapper(){}
|
---|
| 24 | public DerbyWrapper(String dbpath){
|
---|
| 25 | connectDatabase(dbpath,false);
|
---|
| 26 | }
|
---|
| 27 |
|
---|
| 28 | public void connectDatabase(String dbpath, boolean create_database){
|
---|
| 29 | try{
|
---|
| 30 | Class.forName(DRIVER).newInstance();
|
---|
| 31 | //System.out.println("Loaded the embedded driver.");
|
---|
| 32 | protocol_str = PROTOCOL + dbpath;
|
---|
| 33 | if (create_database){
|
---|
| 34 | conn = DriverManager.getConnection(protocol_str + ";create=true");
|
---|
| 35 | }else{
|
---|
| 36 | conn = DriverManager.getConnection(protocol_str);
|
---|
| 37 | }
|
---|
| 38 | state = conn.createStatement();
|
---|
| 39 | }catch (Throwable e){
|
---|
| 40 | System.out.println("exception thrown:");
|
---|
| 41 | if (e instanceof SQLException){
|
---|
| 42 | printSQLError((SQLException) e);
|
---|
| 43 | }else{
|
---|
| 44 | e.printStackTrace();
|
---|
| 45 | }
|
---|
| 46 | }
|
---|
| 47 | }
|
---|
| 48 |
|
---|
| 49 | public void closeDatabase() {
|
---|
| 50 | state = null;
|
---|
| 51 | conn = null;
|
---|
| 52 | boolean gotSQLExc = false;
|
---|
| 53 | try{
|
---|
| 54 | DriverManager.getConnection(protocol_str + ";shutdown=true");
|
---|
| 55 | }catch (SQLException se){
|
---|
| 56 | gotSQLExc = true;
|
---|
| 57 | }
|
---|
| 58 | if (!gotSQLExc){
|
---|
| 59 | System.out.println("Database did not shut down normally");
|
---|
| 60 | }
|
---|
| 61 | }
|
---|
| 62 |
|
---|
| 63 | public void createDatabase()throws SQLException{
|
---|
| 64 | conn.setAutoCommit(false);
|
---|
| 65 | 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))");
|
---|
| 66 | //ystem.out.println("table users created successfully!");
|
---|
| 67 | state.execute("insert into "+USERS+" values ('admin', 'admin', 'administrator,all-collections-editor', 'true', 'change the password for this account as soon as possible')");
|
---|
| 68 | conn.commit();
|
---|
| 69 | }
|
---|
| 70 |
|
---|
| 71 | public UserQueryResult listAllUser()throws SQLException{
|
---|
| 72 | UserQueryResult userQueryResult=new UserQueryResult();
|
---|
| 73 | String sql_list_all_user="select username, password, groups, accountstatus, comment from " + USERS ;
|
---|
| 74 |
|
---|
| 75 | ResultSet rs = state.executeQuery(sql_list_all_user);
|
---|
| 76 | while (rs.next()){
|
---|
| 77 | String returned_username= rs.getString("username");
|
---|
| 78 | String returned_password=rs.getString("password");
|
---|
| 79 | String returned_groups=rs.getString("groups");
|
---|
| 80 | String returned_accountstatus=rs.getString("accountstatus");
|
---|
| 81 | String returned_comment=rs.getString("comment");
|
---|
| 82 | userQueryResult.addUserTerm(returned_username,returned_password,returned_groups,returned_accountstatus,returned_comment);
|
---|
| 83 | }
|
---|
| 84 | if (userQueryResult.getSize()==0){
|
---|
| 85 | System.out.println("couldn't find any users");
|
---|
| 86 | return null;
|
---|
| 87 | }else{
|
---|
| 88 | return userQueryResult;
|
---|
| 89 | }
|
---|
| 90 | }
|
---|
| 91 |
|
---|
| 92 | public String addUser(String username, String password, String groups, String accountstatus, String comment)throws SQLException{
|
---|
| 93 | conn.setAutoCommit(false);
|
---|
| 94 | String sql_insert_user="insert into " + USERS + " values ('"+username +"', '"+password+"', '"+groups+"', '"+accountstatus+"', '"+comment+"')";
|
---|
| 95 | try{
|
---|
| 96 | state.execute(sql_insert_user);
|
---|
| 97 | conn.commit();
|
---|
| 98 | }catch(Throwable e){
|
---|
| 99 | System.out.println("exception thrown:");
|
---|
| 100 | if (e instanceof SQLException){
|
---|
| 101 | printSQLError((SQLException) e);
|
---|
| 102 | }else{
|
---|
| 103 | e.printStackTrace();
|
---|
| 104 | }
|
---|
| 105 | closeDatabase();
|
---|
| 106 | System.out.println("Error:"+e.getMessage());
|
---|
| 107 | return "Error:"+e.getMessage();
|
---|
| 108 | }
|
---|
| 109 | return "succeed";
|
---|
| 110 | }
|
---|
| 111 |
|
---|
| 112 | public String deleteUser(String del_username)throws SQLException{
|
---|
| 113 | conn.setAutoCommit(false);
|
---|
| 114 | String sql_delete_user="delete from " + USERS + " where username='"+del_username+"'";
|
---|
| 115 | try{
|
---|
| 116 | state.execute(sql_delete_user);
|
---|
| 117 | conn.commit();
|
---|
| 118 | }catch(Throwable e){
|
---|
| 119 | System.out.println("exception thrown:");
|
---|
| 120 | if (e instanceof SQLException){
|
---|
| 121 | printSQLError((SQLException) e);
|
---|
| 122 | }else{
|
---|
| 123 | e.printStackTrace();
|
---|
| 124 | }
|
---|
| 125 | closeDatabase();
|
---|
| 126 | return "Error:"+e.getMessage();
|
---|
| 127 | }
|
---|
| 128 | return "succeed";
|
---|
| 129 | }
|
---|
[14335] | 130 |
|
---|
| 131 | public boolean deleteAllUser() throws SQLException{
|
---|
| 132 | conn.setAutoCommit(false);
|
---|
| 133 | try{
|
---|
| 134 | state.execute("delete from " + USERS);
|
---|
| 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 false;
|
---|
| 145 | }
|
---|
| 146 | return true;
|
---|
| 147 | }
|
---|
| 148 |
|
---|
[14289] | 149 | public UserQueryResult findUser(String username, String password)throws SQLException{
|
---|
| 150 | UserQueryResult userQueryResult=new UserQueryResult();
|
---|
| 151 |
|
---|
| 152 | conn.setAutoCommit(false);
|
---|
| 153 | String sql_find_user="SELECT username, password, groups, accountstatus, comment FROM "+USERS;
|
---|
| 154 | String append_sql="";
|
---|
| 155 |
|
---|
| 156 | if (username!=null){
|
---|
| 157 | append_sql=" WHERE username = '" + username+ "'";
|
---|
| 158 | }
|
---|
| 159 | if (password!=null){
|
---|
| 160 | if (append_sql.equals("")){
|
---|
| 161 | append_sql=" WHERE password = '"+password+"'";
|
---|
| 162 | }else{
|
---|
| 163 | append_sql+=" and password = '"+password+"'";
|
---|
| 164 | }
|
---|
| 165 | }
|
---|
| 166 | if (!append_sql.equals("")){
|
---|
| 167 | sql_find_user+=append_sql;
|
---|
| 168 | }
|
---|
| 169 | ResultSet rs = state.executeQuery(sql_find_user);
|
---|
| 170 | while (rs.next()){
|
---|
| 171 | String returned_username= rs.getString("username");
|
---|
| 172 | System.out.println("returned_username :" + returned_username);
|
---|
| 173 | String returned_password=rs.getString("password");
|
---|
| 174 | System.out.println("returned_password :" + returned_password);
|
---|
| 175 | String returned_groups=rs.getString("groups");
|
---|
| 176 | System.out.println("returned_groups :" + returned_groups);
|
---|
| 177 | String returned_accountstatus=rs.getString("accountstatus");
|
---|
| 178 | System.out.println("returned_accountstatus :" + returned_accountstatus);
|
---|
| 179 | String returned_comment=rs.getString("comment");
|
---|
| 180 | System.out.println("returned_comment :" + returned_comment);
|
---|
| 181 | userQueryResult.addUserTerm(returned_username,returned_password,returned_groups,returned_accountstatus,returned_comment);
|
---|
| 182 | System.out.println(userQueryResult.toString());
|
---|
| 183 | }
|
---|
| 184 | conn.commit();
|
---|
[14338] | 185 | if (userQueryResult.getSize()>0){
|
---|
| 186 | return userQueryResult;
|
---|
| 187 | }else{
|
---|
[14289] | 188 | System.out.println("couldn't find the user");
|
---|
| 189 | return null;
|
---|
| 190 | }
|
---|
| 191 | }
|
---|
| 192 |
|
---|
| 193 | public String modifyUserInfo(String username, String new_password,String groups, String accountstatus, String comment)throws SQLException{
|
---|
| 194 | conn.setAutoCommit(false);
|
---|
| 195 | String sql_modify_user_info="update " + USERS +" set ";
|
---|
| 196 | if (new_password!=null && !new_password.equals("")){
|
---|
[14341] | 197 | sql_modify_user_info += "password='" + new_password +"'";
|
---|
[14289] | 198 | }
|
---|
[14341] | 199 |
|
---|
| 200 | if (groups!=null && accountstatus!=null && comment!=null){
|
---|
| 201 | sql_modify_user_info += ", groups='" + groups + "'" + ", accountstatus='" + accountstatus + "'" + ", comment='" + comment + "'";
|
---|
| 202 | }
|
---|
| 203 | sql_modify_user_info +=" where username='" + username+"'";
|
---|
[14289] | 204 |
|
---|
| 205 | try{
|
---|
| 206 | System.out.println(sql_modify_user_info);
|
---|
| 207 | state.execute(sql_modify_user_info);
|
---|
| 208 | conn.commit();
|
---|
| 209 | }catch(Throwable e){
|
---|
| 210 | System.out.println("exception thrown:");
|
---|
| 211 | if (e instanceof SQLException){
|
---|
| 212 | printSQLError((SQLException) e);
|
---|
| 213 | }else{
|
---|
| 214 | e.printStackTrace();
|
---|
| 215 | }
|
---|
| 216 | closeDatabase();
|
---|
| 217 | return "Error:"+e.getMessage();
|
---|
| 218 | }
|
---|
| 219 | return "succeed";
|
---|
| 220 | }
|
---|
| 221 |
|
---|
[14327] | 222 | public void db2txt() throws SQLException{
|
---|
[14289] | 223 | UserQueryResult userQueryResult=new UserQueryResult();
|
---|
| 224 | String sql_list_all_user="select username, password, groups, accountstatus, comment from " + USERS ;
|
---|
| 225 | ResultSet rs = state.executeQuery(sql_list_all_user);
|
---|
| 226 |
|
---|
| 227 | while (rs.next()){
|
---|
| 228 | String returned_username= rs.getString("username");
|
---|
| 229 | System.out.println("[" + returned_username+"]");
|
---|
| 230 | String returned_comment=rs.getString("comment");
|
---|
| 231 | System.out.println("<comment>" + returned_comment);
|
---|
| 232 | String returned_accountstatus=rs.getString("accountstatus");
|
---|
| 233 | System.out.println("<enabled>" + returned_accountstatus);
|
---|
| 234 | String returned_groups=rs.getString("groups");
|
---|
| 235 | System.out.println("<groups>" + returned_groups);
|
---|
| 236 | String returned_password=rs.getString("password");
|
---|
| 237 | System.out.println("<password>" + returned_password);
|
---|
| 238 | System.out.println("<username>" + returned_username);
|
---|
| 239 | System.out.println("");
|
---|
| 240 | System.out.println("----------------------------------------------------------------------");
|
---|
| 241 | }
|
---|
| 242 | conn.commit();
|
---|
| 243 | closeDatabase();
|
---|
| 244 | }
|
---|
| 245 |
|
---|
| 246 | static void printSQLError(SQLException e)
|
---|
| 247 | {
|
---|
| 248 | while (e != null)
|
---|
| 249 | {
|
---|
| 250 | System.out.println(e.toString());
|
---|
| 251 | e = e.getNextException();
|
---|
| 252 | }
|
---|
| 253 | }
|
---|
| 254 | }
|
---|