|
1
6
7 package com.quikj.application.communicator.applications.webtalk.model;
8
9 import java.sql.*;
10 import java.util.*;
11 import java.net.*;
12
13
17 public class RestrictedAccessUserTable
18 {
19 public static final String  TABLE_NAME = "ace_restricted_access_user_tbl";
21
22 public static final String USERNAME = "user";
24
25 public static final String PASSWORD = "password";
26
27 public static final String FULLNAME = "fullname";
28
29 public static final String EMAIL = "email";
30
31 public static final String ADDITIONAL_INFO = "info";
32
33
34 private Connection connection;
35
36
37 private String errorMessage;
38
39
40 public RestrictedAccessUserTable()
41 {
42 }
43
44
51 public void setConnection(Connection connection)
52 {
53 this.connection = connection;
54 }
55
56
62 public String getErrorMessage()
63 {
64 return this.errorMessage;
65 }
66
67 public RestrictedAccessUserElement query(String username)
68 {
69 String cmd = "select " + FULLNAME + ", " + EMAIL + ", "
70 + ADDITIONAL_INFO + " from " + TABLE_NAME + " where "
71 + USERNAME + " = ?";
72
73 try
74 {
75 Statement stmt = connection.createStatement();
76 stmt.executeUpdate("use webtalk");
77
78 PreparedStatement pstmt = connection.prepareStatement(cmd);
79 pstmt.setString(1, username);
80 ResultSet rs = pstmt.executeQuery();
81 if (!rs.first())
82 {
83 errorMessage = null;
85 return null;
86 }
87
88 RestrictedAccessUserElement userdata = new RestrictedAccessUserElement();
89
90 userdata.setFullName(rs.getString(1));
91 userdata.setEmail(rs.getString(2));
92 userdata.setInfoParms(stringToInfoParms(rs.getString(3)));
93 userdata.setName(username);
94
95 return userdata;
96 }
97 catch (SQLException ex)
98 {
99 errorMessage = "SQLException: " + ex.getMessage()
100 + ", SQL command: " + cmd;
101 return null;
102 }
103 }
104
105 public boolean create(RestrictedAccessUserElement user)
106 {
107 String sql = "insert into " + TABLE_NAME
108 + " values (?, password(?), ?, ?, ?)";
109
110 try
111 {
112 Statement stmt = connection.createStatement();
113 stmt.executeUpdate("use webtalk");
114
115 PreparedStatement pstmt = connection.prepareStatement(sql);
116 pstmt.setString(1, user.getName());
117 pstmt.setString(2, user.getPassword());
118 pstmt.setString(3, user.getFullName() == null ? "" : user
119 .getFullName());
120 pstmt.setString(4, user.getEmail() == null ? "" : user.getEmail());
121 pstmt.setString(5, user.getInfoParms() == null ? ""
122 : infoParmsToString(user.getInfoParms()));
123
124 int count = pstmt.executeUpdate();
125 if (count == 0)
126 {
127 errorMessage = "User create failed: no rows affected, SQL command: "
128 + sql;
129 return false;
130 }
131 }
132 catch (SQLException ex)
133 {
134 errorMessage = "SQLException: " + ex.getMessage()
135 + ", SQL command: " + sql;
136 return false;
137 }
138
139 return true;
140 }
141
142 public boolean modify(RestrictedAccessUserElement user)
143 {
144 StringBuffer cmd = new StringBuffer ("update " + TABLE_NAME + " set "
145 + FULLNAME + "= ?, " + EMAIL + "= ?, " + ADDITIONAL_INFO
146 + "= ?");
147
148 if (user.getPassword() != null)
149 {
150 cmd.append(", " + PASSWORD + "= password(?)");
151 }
152
153 cmd.append(" where " + USERNAME + " = ?");
154
155 try
156 {
157 Statement stmt = connection.createStatement();
158 stmt.executeUpdate("use webtalk");
159
160 PreparedStatement pstmt = connection.prepareStatement(cmd
161 .toString());
162 int index = 1;
163 pstmt.setString(index++, user.getFullName() == null ? "" : user
164 .getFullName());
165 pstmt.setString(index++, user.getEmail() == null ? "" : user
166 .getEmail());
167 pstmt.setString(index++, user.getInfoParms() == null ? ""
168 : infoParmsToString(user.getInfoParms()));
169 if (user.getPassword() != null)
170 {
171 pstmt.setString(index++, user.getPassword());
172 }
173 pstmt.setString(index++, user.getName());
174
175 int count = pstmt.executeUpdate();
176 if (count == 0)
177 {
178 errorMessage = null;
179 return false;
180 }
181 }
182 catch (SQLException ex)
183 {
184 errorMessage = "SQLException: " + ex.getMessage()
185 + ", SQL command: " + cmd;
186 return false;
187 }
188
189 return true;
190 }
191
192 public boolean delete(String username)
193 {
194 String cmd = "delete from " + TABLE_NAME + " where " + USERNAME
195 + " = ?";
196 try
197 {
198 Statement stmt = connection.createStatement();
199 stmt.executeUpdate("use webtalk");
200
201 PreparedStatement pstmt = connection.prepareStatement(cmd);
202 pstmt.setString(1, username);
203 int count = pstmt.executeUpdate();
204 if (count == 0)
205 {
206 errorMessage = null;
207 return false;
208 }
209 }
210 catch (SQLException ex)
211 {
212 errorMessage = "SQLException: " + ex.getMessage()
213 + ", SQL command: " + cmd;
214 return false;
215 }
216
217 return true;
218 }
219
220 public ArrayList search(RestrictedAccessUserElement user)
221 {
223 String cmd = null;
224 boolean where = false;
225
226 StringBuffer constraints = new StringBuffer ("select " + USERNAME
227 + " from " + TABLE_NAME);
228
229 String name = user.getName();
230 if ((name != null) && (name.length() > 0))
231 {
232 if (where == false)
233 {
234 constraints.append(" where ");
235 where = true;
236 }
237 else
238 {
239 constraints.append(" and ");
240 }
241
242 constraints.append(USERNAME + " like ?");
243 }
244
245 String fullname = user.getFullName();
246 if ((fullname != null) && (fullname.length() > 0))
247 {
248 if (where == false)
249 {
250 constraints.append(" where ");
251 where = true;
252 }
253 else
254 {
255 constraints.append(" and ");
256 }
257
258 constraints.append(FULLNAME + " like ?");
259 }
260
261 String email = user.getEmail();
262 if ((email != null) && (email.length() > 0))
263 {
264 if (where == false)
265 {
266 constraints.append(" where ");
267 where = true;
268 }
269 else
270 {
271 constraints.append(" and ");
272 }
273
274 constraints.append(EMAIL + " like ?");
275 }
276
277 String addnl_info = user.getAdditionalInfo();
278 if ((addnl_info != null) && (addnl_info.length() > 0))
279 {
280 if (where == false)
281 {
282 constraints.append(" where ");
283 where = true;
284 }
285 else
286 {
287 constraints.append(" and ");
288 }
289
290 constraints.append(ADDITIONAL_INFO + " like ?");
291 }
292
293 constraints.append(" order by 1");
294 cmd = constraints.toString();
295
296 try
297 {
298 Statement stmt = connection.createStatement();
299 stmt.executeUpdate("use webtalk");
300
301 int index = 1;
302 PreparedStatement pstmt = connection.prepareStatement(cmd);
303 if ((name != null) && (name.length() > 0))
304 {
305 pstmt.setString(index++, name);
306 }
307
308 if ((fullname != null) && (fullname.length() > 0))
309 {
310 pstmt.setString(index++, fullname);
311 }
312
313 if ((email != null) && (email.length() > 0))
314 {
315 pstmt.setString(index++, email);
316 }
317
318 if ((addnl_info != null) && (addnl_info.length() > 0))
319 {
320 pstmt.setString(index++, addnl_info);
321 }
322
323 ResultSet rs = pstmt.executeQuery();
324
325 ArrayList list = new ArrayList();
326 while (rs.next() == true)
327 {
328 list.add(rs.getString(1));
329 }
330
331 return list;
332 }
333 catch (SQLException ex)
334 {
335 errorMessage = "SQLException: " + ex.getMessage()
336 + ", SQL command: " + cmd;
337 return null;
338 }
339 }
340
341 private HashMap stringToInfoParms(String parm_str)
342 {
343 if (parm_str.length() > 0)
344 {
345 HashMap parms = new HashMap();
346
347 StringTokenizer strtok = new StringTokenizer(parm_str, "&");
348 int num_tokens = strtok.countTokens();
349 for (int i = 0; i < num_tokens; i++)
350 {
351 String pair = strtok.nextToken();
352
353 StringTokenizer pairtok = new StringTokenizer(pair, "=");
354 int num_subparms = pairtok.countTokens();
355
356 if (num_subparms != 2)
357 {
358 continue;
359 }
360
361 try
362 {
363 String key = URLDecoder
364 .decode(pairtok.nextToken(), "UTF-8");
365 String value = URLDecoder.decode(pairtok.nextToken(),
366 "UTF-8");
367 parms.put(key, value);
368 }
369 catch (Exception e)
370 {
371 return null;
372 }
373
374 }
375
376 return parms;
377 }
378
379 return null;
380 }
381
382 private String infoParmsToString(HashMap parms)
383 {
384 if (parms != null)
385 {
386 Set key_set = parms.keySet();
387 StringBuffer buf = new StringBuffer ();
388
389 for (Iterator i = key_set.iterator(); i.hasNext();)
390 {
391 String key = (String ) i.next();
392 String value = (String ) parms.get(key);
393
394 try
395 {
396 buf.append(URLEncoder.encode(key, "UTF-8") + '='
397 + URLEncoder.encode(value, "UTF-8"));
398 }
399 catch (Exception e)
400 {
401 return null;
402 }
403
404 if (i.hasNext() == true)
405 {
406 buf.append('&');
407 }
408 }
409
410 return buf.toString();
411 }
412
413 return "";
414 }
415 } |
Popular Tags |
Java API By Example, From Geeks To Geeks.