|
1
6
7 package com.quikj.application.communicator.admin.model;
8
9 import java.sql.*;
10 import java.util.*;
11
12
16 public class AccountsTable
17 {
18
19
20 public AccountsTable(String  database)
21 {
22 this.database = database;
23 }
24
25
29 public void setConnection(java.sql.Connection connection)
30 {
31 this.connection = connection;
32 }
33
34 public AccountElement authenticate(String user, String password)
35 {
36 try
37 {
38 PreparedStatement cmd = connection.prepareStatement("select "
39 + DOMAIN
40 + ", "
41 + LEVEL
42 + ", "
43 + ADDITIONAL_INFO
44 + ", "
45 + FEATURES
46 + " from "
47 + ACCOUNT_TABLE_NAME
48 + " where "
49 + NAME
50 + " = ? and "
51 + PASSWORD
52 + " = password(?)");
53
54 cmd.setString(1, user);
55 cmd.setString(2, password);
56
57 ResultSet rs = cmd.executeQuery();
58 if (!rs.first())
59 {
60 errorMessage = "Authentication Failure";
62 return null;
63 }
64
65 AccountElement userinfo = new AccountElement();
66 userinfo.setName(user);
67 userinfo.setDomain(rs.getString(1));
68 userinfo.setLevel(rs.getInt(2));
69 userinfo.setAdditionalInfo(rs.getString(3));
70
71 Blob features = rs.getBlob(4);
72 int len = (int) features.length();
73 userinfo.stringToFeatureList(new String (features.getBytes(1, len)).trim());
74
75 return userinfo;
76 }
77 catch (SQLException ex)
78 {
79 errorMessage = "SQLException: " + ex.getMessage();
80 return null;
81 }
82 }
83
84 public AccountElement query(String username)
85 {
86 return query(username, null);
87 }
88
89 public AccountElement query(String username, String domain_constraint)
90 {
91 String constraint = "";
92 if (domain_constraint != null)
93 {
94 constraint = DOMAIN + "='" + domain_constraint + "' and ";
95 }
96
97 try
98 {
99 PreparedStatement cmd = connection.prepareStatement(
100 "select "
101 + DOMAIN
102 + ", "
103 + LEVEL
104 + ", "
105 + ADDITIONAL_INFO
106 + ", "
107 + FEATURES
108 + " from "
109 + ACCOUNT_TABLE_NAME
110 + " where "
111 + constraint
112 + NAME
113 + " = ?");
114
115 cmd.setString(1, username);
116
117 Statement stmt = connection.createStatement();
118 stmt.executeUpdate("use " + database);
119
120 ResultSet rs = cmd.executeQuery();
121 if (!rs.first())
122 {
123 errorMessage = null;
125 return null;
126 }
127
128 AccountElement userinfo = new AccountElement();
129 userinfo.setName(username);
130 userinfo.setDomain(rs.getString(1));
131 userinfo.setLevel(rs.getInt(2));
132 userinfo.setAdditionalInfo(rs.getString(3));
133
134 Blob features = rs.getBlob(4);
135 int len = (int) features.length();
136 userinfo.stringToFeatureList(new String (features.getBytes(1, len)).trim());
137
138 return userinfo;
139 }
140 catch (SQLException ex)
141 {
142 errorMessage = "SQLException: " + ex.getMessage();
143 return null;
144 }
145 }
146
147 public ArrayList list()
148 {
149 return list(null);
150 }
151
152 public ArrayList list(String domain_constraint)
153 {
154 String constraint = "";
155 if (domain_constraint != null)
156 {
157 constraint = " where " + DOMAIN + "='" + domain_constraint + "' ";
158 }
159
160 String cmd = "select "
161 + NAME
162 + " from "
163 + ACCOUNT_TABLE_NAME
164 + constraint
165 + " order by "
166 + NAME;
167
168 try
169 {
170 Statement stmt = connection.createStatement();
171 stmt.executeUpdate("use " + database);
172
173 ResultSet rs = stmt.executeQuery(cmd);
174
175 ArrayList list = new ArrayList();
176 while (rs.next() == true)
177 {
178 list.add(rs.getString(1));
179 }
180
181 return list;
182 }
183 catch (SQLException ex)
184 {
185 errorMessage = "SQLException: " + ex.getMessage() + ", SQL command: " + cmd;
186 return null;
187 }
188 }
189
190 public boolean create(AccountElement user)
191 {
192 StringBuffer buffer = new StringBuffer ("insert into "
193 + ACCOUNT_TABLE_NAME
194 + " values (?,password(?),");
195
196 if (user.getDomain() != null)
197 {
198 buffer.append("?,");
199 }
200 else
201 {
202 buffer.append("NULL,");
203 }
204
205 buffer.append("0,");
207 buffer.append(user.getLevel() + ",");
208
209 if (user.getAdditionalInfo() != null)
210 {
211 buffer.append("?,");
212 }
213 else
214 {
215 buffer.append("NULL,");
216 }
217
218 buffer.append("'" + user.featureListToString() + "')");
219
220 try
221 {
222 PreparedStatement cmd = connection.prepareStatement(buffer.toString());
223
224 cmd.setString(1, user.getName());
225 cmd.setString(2, user.getPassword());
226
227 if (user.getDomain() != null)
228 {
229 cmd.setString(3, user.getDomain());
230 if (user.getAdditionalInfo() != null)
231 {
232 cmd.setString(4, user.getAdditionalInfo());
233 }
234 }
235 else if (user.getAdditionalInfo() != null)
236 {
237 cmd.setString(3, user.getAdditionalInfo());
238 }
239
240 Statement stmt = connection.createStatement();
241 stmt.executeUpdate("use " + database);
242
243 int count = cmd.executeUpdate();
244 if (count == 0)
245 {
246 errorMessage = "Account create failed: no rows affected, SQL command: " + cmd;
247 return false;
248 }
249 }
250 catch (SQLException ex)
251 {
252 errorMessage = "SQLException: " + ex.getMessage();
253 return false;
254 }
255
256 return true;
257 }
258
259 public boolean modify(AccountElement user)
260 {
261 return modify(user, null);
262 }
263
264 public boolean modify(AccountElement user, String domain_constraint)
265 {
266 String constraint = "";
267 if (domain_constraint != null)
268 {
269 constraint = DOMAIN + "='" + domain_constraint + "' and ";
270 }
271
272 String password_string = "";
273 if (user.getPassword() != null)
274 {
275 password_string = PASSWORD + "=password(?), ";
276 }
277
278 try
279 {
280 PreparedStatement cmd = connection.prepareStatement("update "
281 + ACCOUNT_TABLE_NAME
282 + " set "
283 + password_string
284 + DOMAIN
285 + "='"
286 + (user.getDomain() == null ? "" : user.getDomain())
287 + "',"
288 + LEVEL
289 + "="
290 + user.getLevel()
291 + ","
292 + ADDITIONAL_INFO
293 + "= ?,"
294 + FEATURES
295 + "='"
296 + user.featureListToString()
297 + "'"
298 + " where "
299 + constraint
300 + NAME
301 + " = ?");
302
303 if (user.getPassword() != null)
304 {
305 cmd.setString(1, user.getPassword());
306
307 if (user.getAdditionalInfo() != null)
308 {
309 cmd.setString(2, user.getAdditionalInfo());
310 }
311 else
312 {
313 cmd.setString(2, "NULL");
314 }
315
316 cmd.setString(3, user.getName());
317 }
318 else
319 {
320 if (user.getAdditionalInfo() != null)
321 {
322 cmd.setString(1, user.getAdditionalInfo());
323 }
324 else
325 {
326 cmd.setString(1, "NULL");
327 }
328
329 cmd.setString(2, user.getName());
330 }
331
332 Statement stmt = connection.createStatement();
333 stmt.executeUpdate("use " + database);
334
335 int count = cmd.executeUpdate();
336 if (count == 0)
337 {
338 errorMessage = "Database not updated - the user was not found";
339 return false;
340 }
341 }
342 catch (SQLException ex)
343 {
344 errorMessage = "SQLException: " + ex.getMessage();
345 return false;
346 }
347
348 return true;
349 }
350
351 public boolean delete(String username)
352 {
353 return delete(username, null);
354 }
355
356 public boolean delete(String username, String domain_constraint)
357 {
358 String constraint = "";
359 if (domain_constraint != null)
360 {
361 constraint = DOMAIN + "='" + domain_constraint + "' and ";
362 }
363
364 try
365 {
366 PreparedStatement cmd = connection.prepareStatement("delete from "
367 + ACCOUNT_TABLE_NAME
368 + " where "
369 + constraint
370 + NAME
371 + " = ?");
372
373 cmd.setString(1, username);
374
375 Statement stmt = connection.createStatement();
376 stmt.executeUpdate("use " + database);
377
378 int count = cmd.executeUpdate();
379 if (count == 0)
380 {
381 errorMessage = null;
382 return false;
383 }
384 }
385 catch (SQLException ex)
386 {
387 errorMessage = "SQLException: " + ex.getMessage();
388 return false;
389 }
390
391 return true;
392 }
393
394 public boolean changePassword(String username, String old_password, String new_password)
395 {
396 try
397 {
398 PreparedStatement cmd = connection.prepareStatement("update "
399 + ACCOUNT_TABLE_NAME
400 + " set "
401 + PASSWORD
402 + "=password(?)"
403 + " where "
404 + NAME
405 + " = ? and "
406 + PASSWORD
407 + "=password(?)");
408
409 cmd.setString(1, new_password);
410 cmd.setString(2, username);
411 cmd.setString(3, old_password);
412
413 Statement stmt = connection.createStatement();
414 stmt.executeUpdate("use " + database);
415
416 int count = cmd.executeUpdate();
417 if (count == 0)
418 {
419 errorMessage = "Password change failed - database not updated - the user was not found or passwords did not match, user = "
420 + username;
421 return false;
422 }
423 }
424 catch (SQLException ex)
425 {
426 errorMessage = "SQLException: " + ex.getMessage();
427 return false;
428 }
429
430 return true;
431 }
432
433
434
438 public java.lang.String getErrorMessage()
439 {
440 return errorMessage;
441 }
442
443
444 private static final String ACCOUNT_TABLE_NAME = "account_tbl";
446
447 private static final String NAME = "userid";
449 private static final String PASSWORD = "password";
450 private static final String DOMAIN = "domain";
451 private static final String FLAGS = "flags";
452 private static final String LEVEL = "level";
453 private static final String ADDITIONAL_INFO = "addnl_info";
454 private static final String FEATURES = "features";
455
456 private Connection connection;
457
458 private String errorMessage;
459 private String database;
460 }
461 |
Popular Tags |
Java API By Example, From Geeks To Geeks.