|
1
6
7 package com.quikj.application.communicator.applications.webtalk.model;
8
9 import java.sql.*;
10 import java.util.*;
11
12
16 public class FeatureTable
17 {
18 public static final String  FEATURE_TABLE_NAME = "feature_tbl";
20 public static final String PARAMS_TABLE_NAME = "feature_params_tbl";
21
22 public static final String FEATURE_ID = "id";
24 public static final String FEATURE_NAME = "fname";
25 public static final String FEATURE_DOMAIN = "domain";
26 public static final String FEATURE_CLASS = "class";
27 public static final String FEATURE_ACTIVE = "active";
28
29 public static final String PARAM_FEATURE_ID = "feature_id";
31 public static final String PARAM_NAME = "pname";
32 public static final String PARAM_VALUE = "pvalue";
33
34
35 private Connection connection;
36
37
38 private String errorMessage;
39
40
41 public FeatureTable()
42 {
43 }
44
45
49 public void setConnection(Connection connection)
50 {
51 this.connection = connection;
52 }
53
54
58 public String getErrorMessage()
59 {
60 return this.errorMessage;
61 }
62
63 public FeatureTableElement query(String featurename)
64 {
65 return query(featurename, null);
66 }
67
68 public FeatureTableElement query(String featurename, String domain_constraint)
69 {
70 String constraint = "";
71 if (domain_constraint != null)
72 {
73 constraint = FEATURE_DOMAIN + "='" + domain_constraint + "' and ";
74 }
75
76 String cmd = "select "
77 + FEATURE_DOMAIN
78 + ", "
79 + FEATURE_CLASS
80 + ", "
81 + FEATURE_ACTIVE
82 + " from "
83 + FEATURE_TABLE_NAME
84 + " where "
85 + constraint
86 + FEATURE_NAME
87 + " = ?";
88
89 try
90 {
91 Statement stmt = connection.createStatement();
92 stmt.executeUpdate("use webtalk");
93
94 PreparedStatement pstmt = connection.prepareStatement(cmd);
95 pstmt.setString(1, featurename);
96 ResultSet rs = pstmt.executeQuery();
97 if (!rs.first())
98 {
99 errorMessage = null;
101 return null;
102 }
103
104 FeatureTableElement data = new FeatureTableElement();
105
106 data.setDomain(rs.getString(1));
107 data.setClassName(rs.getString(2));
108 data.setActive(rs.getBoolean(3));
109 data.setName(featurename);
110
111 cmd = "select "
113 + PARAM_NAME
114 + ','
115 + PARAM_VALUE
116 + " from "
117 + PARAMS_TABLE_NAME
118 + ','
119 + FEATURE_TABLE_NAME
120 + " where "
121 + PARAM_FEATURE_ID + '=' + FEATURE_ID + " and "
122 + FEATURE_NAME + " = ?";
123
124 pstmt = connection.prepareStatement(cmd);
125 pstmt.setString(1, featurename);
126 rs = pstmt.executeQuery();
127 HashMap map = new HashMap();
128
129 while (rs.next() == true)
130 {
131 map.put(rs.getString(1), rs.getString(2));
132 }
133
134 data.setParams(map);
135
136 return data;
137 }
138 catch (SQLException ex)
139 {
140 errorMessage = "SQLException: " + ex.getMessage() + ", SQL command: " + cmd;
141 return null;
142 }
143 }
144
145 public ArrayList queryAll()
146 {
149 String cmd = "select "
151 + PARAM_FEATURE_ID
152 + ','
153 + PARAM_NAME
154 + ','
155 + PARAM_VALUE
156 + " from "
157 + PARAMS_TABLE_NAME;
158
159 try
160 {
161 Statement stmt = connection.createStatement();
162 stmt.executeUpdate("use webtalk");
163
164 ResultSet rs = stmt.executeQuery(cmd);
165 HashMap feature_map = new HashMap();
167 while (rs.next() == true)
168 {
169 Integer feature_id = new Integer (rs.getInt(1));
170
171 Map param_map = (Map) feature_map.get(feature_id);
172 if (param_map == null)
173 {
174 param_map = new HashMap();
175 feature_map.put(feature_id, param_map);
176 }
177
178 param_map.put(rs.getString(2), rs.getString(3));
179 }
180
181 cmd = "select "
183 + FEATURE_ID
184 + ", "
185 + FEATURE_NAME
186 + ", "
187 + FEATURE_DOMAIN
188 + ", "
189 + FEATURE_CLASS
190 + ", "
191 + FEATURE_ACTIVE
192 + " from "
193 + FEATURE_TABLE_NAME;
194
195 rs = stmt.executeQuery(cmd);
196
197 ArrayList list = new ArrayList();
198
199 while (rs.next() == true)
200 {
201 Integer feature_id = new Integer (rs.getInt(1));
202 FeatureTableElement data = new FeatureTableElement();
203
204 data.setName(rs.getString(2));
205 data.setDomain(rs.getString(3));
206 data.setClassName(rs.getString(4));
207 data.setActive(rs.getBoolean(5));
208
209 data.setParams((HashMap) feature_map.get(feature_id));
210
211 list.add(data);
212 }
213
214 return list;
215 }
216 catch (SQLException ex)
217 {
218 errorMessage = "SQLException: " + ex.getMessage() + ", SQL command: " + cmd;
219 return null;
220 }
221 }
222
223 public ArrayList list()
224 {
225 return list(null);
226 }
227
228 public ArrayList list(String domain_constraint)
229 {
231 StringBuffer cmd = new StringBuffer ("select "
232 + FEATURE_NAME
233 + ", "
234 + FEATURE_DOMAIN
235 + ", "
236 + FEATURE_CLASS
237 + ", "
238 + FEATURE_ACTIVE
239 + " from "
240 + FEATURE_TABLE_NAME);
241
242 if (domain_constraint != null)
243 {
244 cmd.append(" where " + FEATURE_DOMAIN + "= ?");
245 }
246
247 cmd.append(" order by "
248 + FEATURE_NAME);
249
250 try
251 {
252 Statement stmt = connection.createStatement();
253 stmt.executeUpdate("use webtalk");
254
255 PreparedStatement pstmt = connection.prepareStatement(cmd.toString());
256 if (domain_constraint != null)
257 {
258 pstmt.setString(1, domain_constraint);
259 }
260 ResultSet rs = pstmt.executeQuery();
261
262 ArrayList list = new ArrayList();
263 while (rs.next() == true)
264 {
265 FeatureTableElement ele = new FeatureTableElement();
266
267 ele.setName(rs.getString(1));
268 ele.setDomain(rs.getString(2));
269 ele.setClassName(rs.getString(3));
270 ele.setActive(rs.getBoolean(4));
271
272 list.add(ele);
273 }
274
275 return list;
276 }
277 catch (SQLException ex)
278 {
279 errorMessage = "SQLException: " + ex.getMessage() + ", SQL command: " + cmd;
280 return null;
281 }
282 }
283
284
285 public boolean create(FeatureTableElement ele)
286 {
287 String cmd = "insert into "
288 + FEATURE_TABLE_NAME
289 + " values (NULL, ?, ?, ?, ?)";
290 try
291 {
292 Statement stmt = connection.createStatement();
293 stmt.executeUpdate("use webtalk");
294
295 PreparedStatement pstmt = connection.prepareStatement(cmd);
296 pstmt.setString(1, ele.getName());
297 pstmt.setString(2, ele.getDomain() == null ? "" : ele.getDomain());
298 pstmt.setString(3, ele.getClassName());
299 pstmt.setBoolean(4, ele.isActive());
300
301 int count = pstmt.executeUpdate();
302 if (count == 0)
303 {
304 errorMessage = "Feature create failed: no rows affected, SQL command: " + cmd;
305 return false;
306 }
307
308 Map params = ele.getParams();
309 if ((params != null) && (params.size() > 0))
310 {
311 StringBuffer buffer = new StringBuffer ("insert into "
312 + PARAMS_TABLE_NAME
313 + " values ");
314
315
316 int size = params.size();
317 for (int i = 0; i < size; i++)
318 {
319 if (i > 0)
320 {
321 buffer.append(", ");
322 }
323 buffer.append("(LAST_INSERT_ID(), ?, ?)");
324 }
325 cmd = buffer.toString();
326
327 pstmt = connection.prepareStatement(cmd);
328 int index = 1;
329 Set key_set = params.keySet();
330
331 for (Iterator i = key_set.iterator(); i.hasNext();)
332 {
333 String key = (String ) i.next();
334 pstmt.setString(index++, key);
335 String value = (String ) params.get(key);
336 pstmt.setString(index++, value);
337 }
338
339 pstmt.executeUpdate(); }
341 }
342 catch (SQLException ex)
343 {
344 errorMessage = "SQLException: " + ex.getMessage() + ", SQL command: " + cmd;
345 return false;
346 }
347
348 return true;
349 }
350
351 public boolean modify(FeatureTableElement ele)
352 {
353 return modify(ele, null);
354 }
355
356 public boolean modify(FeatureTableElement ele, String domain_constraint)
357 {
358 StringBuffer cmd = new StringBuffer ("select "
359 + FEATURE_ID
360 + " from "
361 + FEATURE_TABLE_NAME
362 + " where ");
363
364 if (domain_constraint != null)
365 {
366 cmd.append(FEATURE_DOMAIN + "= ? and ");;
367 }
368
369 cmd.append(FEATURE_NAME
370 + " = ?");
371
372 try
373 {
374 Statement stmt = connection.createStatement();
375 stmt.executeUpdate("use webtalk");
376
377 PreparedStatement pstmt = connection.prepareStatement(cmd.toString());
378 int index = 1;
379 if (domain_constraint != null)
380 {
381 pstmt.setString(index++, domain_constraint);
382 }
383 pstmt.setString(index++, ele.getName());
384
385 ResultSet rs = pstmt.executeQuery();
386 if (!rs.first())
387 {
388 errorMessage = null;
390 return false;
391 }
392
393 int feature_id = rs.getInt(1);
394
395 String sql = "update "
396 + FEATURE_TABLE_NAME
397 + " set "
398 + FEATURE_DOMAIN
399 + "= ?, "
400 + FEATURE_CLASS
401 + "= ? where "
402 + FEATURE_ID
403 + "= ? ";
404
405 pstmt = connection.prepareStatement(sql);
406 pstmt.setString(1, ele.getDomain() == null ? "" : ele.getDomain());
407 pstmt.setString(2, ele.getClassName() == null ? FEATURE_CLASS : ele.getClassName());
408 pstmt.setInt(3, feature_id);
409 int count = pstmt.executeUpdate();
410 if (count == 0)
411 {
412 errorMessage = "Feature modify not performed: no rows affected";
413 return false;
414 }
415
416 sql = "delete from "
418 + PARAMS_TABLE_NAME
419 + " where "
420 + PARAM_FEATURE_ID
421 + '='
422 + feature_id;
423
424 stmt.executeUpdate(sql);
425
426 Map params = ele.getParams();
427 if ((params != null) && (params.size() > 0))
428 {
429 StringBuffer buffer = new StringBuffer ("insert into "
430 + PARAMS_TABLE_NAME
431 + " values ");
432
433 int size = params.size();
434 for (int i = 0; i < size; i++)
435 {
436 if (i > 0)
437 {
438 buffer.append(", ");
439 }
440 buffer.append("(?, ?, ?)");
441 }
442
443
444 sql = buffer.toString();
445 pstmt = connection.prepareStatement(sql);
446 index = 1;
447 Set key_set = params.keySet();
448 for (Iterator iter = key_set.iterator(); iter.hasNext() == true;)
449 {
450 pstmt.setInt(index++, feature_id);
451 String key = (String ) iter.next();
452 pstmt.setString(index++, key);
453 String value = (String ) params.get(key);
454 pstmt.setString(index++, value);
455 }
456
457 pstmt.executeUpdate(); }
459
460 }
461 catch (SQLException ex)
462 {
463 errorMessage = "SQLException: " + ex.getMessage() + ", SQL command: " + cmd;
464 return false;
465 }
466
467 return true;
468 }
469
470 public boolean delete(String name)
471 {
472 return delete(name, null);
473 }
474
475 public boolean delete(String name, String domain_constraint)
476 {
477 StringBuffer cmd = new StringBuffer ("select "
478 + FEATURE_ID
479 + " from "
480 + FEATURE_TABLE_NAME
481 + " where ");
482
483 if (domain_constraint != null)
484 {
485 cmd.append(FEATURE_DOMAIN + "= ? and ");
486 }
487
488 cmd.append(FEATURE_NAME + " = ?");
489
490 try
491 {
492 Statement stmt = connection.createStatement();
493 stmt.executeUpdate("use webtalk");
494
495 PreparedStatement pstmt = connection.prepareStatement(cmd.toString());
496 int index = 1;
497 if (domain_constraint != null)
498 {
499 pstmt.setString(index++, domain_constraint);
500 }
501 pstmt.setString(index++, name);
502
503 ResultSet rs = pstmt.executeQuery();
504 if (!rs.first())
505 {
506 errorMessage = null;
508 return false;
509 }
510
511 int feature_id = rs.getInt(1);
512
513 String sql = "delete from "
514 + PARAMS_TABLE_NAME
515 + " where "
516 + PARAM_FEATURE_ID
517 + '='
518 + feature_id;
519
520 stmt.executeUpdate(sql);
521
522 sql = "delete from "
523 + FEATURE_TABLE_NAME
524 + " where "
525 + FEATURE_ID
526 + '='
527 + feature_id;
528
529 int count = stmt.executeUpdate(sql);
530 if (count == 0)
531 {
532 errorMessage = "Feature delete failed: no rows affected, SQL command: " + sql;
533 return false;
534 }
535
536 }
537 catch (SQLException ex)
538 {
539 errorMessage = "SQLException: " + ex.getMessage() + ", SQL command: " + cmd;
540 return false;
541 }
542
543 return true;
544 }
545
546 public boolean activate(String name)
547 {
548 String cmd = "update "
549 + FEATURE_TABLE_NAME
550 + " set "
551 + FEATURE_ACTIVE
552 + " = 1 where "
553 + FEATURE_NAME
554 + " = ?";
555
556 try
557 {
558 Statement stmt = connection.createStatement();
559 stmt.executeUpdate("use webtalk");
560
561 PreparedStatement pstmt = connection.prepareStatement(cmd);
562 pstmt.setString(1, name);
563 int count = pstmt.executeUpdate();
564 if (count == 0)
565 {
566 errorMessage = "Feature activate not performed: no rows affected";
567 return false;
568 }
569 }
570 catch (SQLException ex)
571 {
572 errorMessage = "SQLException: " + ex.getMessage() + ", SQL command: " + cmd;
573 return false;
574 }
575
576 return true;
577 }
578
579 public boolean deactivate(String name)
580 {
581 String cmd = "update "
582 + FEATURE_TABLE_NAME
583 + " set "
584 + FEATURE_ACTIVE
585 + " = 0 where "
586 + FEATURE_NAME
587 + " = ?";
588
589 try
590 {
591 Statement stmt = connection.createStatement();
592 stmt.executeUpdate("use webtalk");
593 PreparedStatement pstmt = connection.prepareStatement(cmd);
594 pstmt.setString(1, name);
595 int count = pstmt.executeUpdate();
596 if (count == 0)
597 {
598 errorMessage = "Feature deactivate not performed: no rows affected";
599 return false;
600 }
601 }
602 catch (SQLException ex)
603 {
604 errorMessage = "SQLException: " + ex.getMessage() + ", SQL command: " + cmd;
605 return false;
606 }
607
608 return true;
609 }
610
611 public boolean isFeatureActive(String name)
612 {
613 String cmd = "select "
614 + FEATURE_ACTIVE
615 + " from "
616 + FEATURE_TABLE_NAME
617 + " where "
618 + FEATURE_NAME
619 + " = ?";
620
621 try
622 {
623 Statement stmt = connection.createStatement();
624 stmt.executeUpdate("use webtalk");
625 PreparedStatement pstmt = connection.prepareStatement(cmd);
626 pstmt.setString(1, name);
627 ResultSet rs = pstmt.executeQuery();
628 if (!rs.first())
629 {
630 return false;
631 }
632
633 return rs.getBoolean(1);
634 }
635 catch (SQLException ex)
636 {
637 return false;
638 }
639 }
640 }
641 |
Popular Tags |
Java API By Example, From Geeks To Geeks.