|
1
2 package com.quikj.application.communicator.applications.webtalk.model;
3
4 import java.sql.*;
5 import java.util.*;
6
7
11 public class CannedMessageTable
12 {
13 public static final String  TABLE_NAME = "canned_message_tbl";
15
16 public static final String ID = "id";
18 public static final String GROUP = "grp";
19 public static final String DESCRIPTION = "description";
20 public static final String MESSAGE = "message";
21
22
23
24 private Connection connection;
25
26
27 private String errorMessage;
28
29
30 public CannedMessageTable()
31 {
32 }
33
34
38 public void setConnection(Connection connection)
39 {
40 this.connection = connection;
41 }
42
43
47 public String getErrorMessage()
48 {
49 return this.errorMessage;
50 }
51
52 public CannedMessageElement query(String id, String domain_constraint)
53 {
54 PreparedStatement cmd = null;
55
56 try
57 {
58 if (domain_constraint != null)
59 {
60 cmd = connection.prepareStatement("select "
61 + GROUP
62 + ", "
63 + DESCRIPTION
64 + ", "
65 + MESSAGE
66 + " from "
67 + TABLE_NAME
68 + ','
69 + GroupTable.GROUP_TABLE_NAME
70 + " where "
71 + ID
72 + " = ? and "
73 + GroupTable.DOMAIN
74 + " = ? and "
75 + GroupTable.GROUPNAME
76 + '='
77 + GROUP);
78
79 cmd.setString(1, id);
80 cmd.setString(2, domain_constraint);
81 }
82 else
83 {
84 cmd = connection.prepareStatement("select "
85 + GROUP
86 + ", "
87 + DESCRIPTION
88 + ", "
89 + MESSAGE
90 + " from "
91 + TABLE_NAME
92 + " where "
93 + ID
94 + " = ?");
95
96 cmd.setString(1, id);
97 }
98
99 Statement stmt = connection.createStatement();
100 stmt.executeUpdate("use webtalk");
101
102 ResultSet rs = cmd.executeQuery();
103 if (!rs.first())
104 {
105 errorMessage = null;
107 return null;
108 }
109
110 CannedMessageElement element = new CannedMessageElement();
111
112 element.setGroup(rs.getString(1));
113 element.setDescription(rs.getString(2));
114 element.setMessage(rs.getString(3));
115 element.setId(id);
116
117 return element;
118 }
119 catch (SQLException ex)
120 {
121 errorMessage = "SQLException: " + ex.getMessage() + ", SQL command: " + cmd;
122 return null;
123 }
124 }
125
126 public boolean create(CannedMessageElement element)
127 {
128 PreparedStatement cmd = null;
129
130 try
131 {
132 cmd = connection.prepareStatement("insert into "
133 + TABLE_NAME
134 + " values (?,?,?,?)");
135
136 cmd.setString(1, element.getId());
137 cmd.setString(2, (element.getGroup() == null ? "" : element.getGroup()));
138 cmd.setString(3, (element.getDescription() == null ? "" : element.getDescription()));
139 cmd.setBytes(4, (new String (element.getMessage() == null ? "" : element.getMessage()).getBytes()));
140
141 Statement stmt = connection.createStatement();
142 stmt.executeUpdate("use webtalk");
143
144 int count = cmd.executeUpdate();
145 if (count == 0)
146 {
147 errorMessage = "Canned message create failed: no rows affected, SQL command: " + cmd;
148 return false;
149 }
150 }
151 catch (SQLException ex)
152 {
153 errorMessage = "SQLException: " + ex.getMessage() + ", SQL command: " + cmd;
154 return false;
155 }
156
157 return true;
158 }
159
160 public boolean modify(CannedMessageElement element, ArrayList group_constraint)
161 {
163
164 StringBuffer buf = new StringBuffer ("update "
165 + TABLE_NAME
166 + " set "
167 + GROUP
168 + " = ?, "
169 + DESCRIPTION
170 + " = ?, "
171 + MESSAGE
172 + " = ? where "
173 + ID
174 + " = ?");
175
176 if (group_constraint != null)
177 {
178 buf.append(" and (");
179 for (Iterator i = group_constraint.iterator(); i.hasNext();)
180 {
181 buf.append(GROUP
182 + " = ?");
183
184 i.next();
185
186 if (i.hasNext() == true)
187 {
188 buf.append(" or ");
189 }
190 }
191 buf.append(')');
192 }
193
194 PreparedStatement cmd = null;
195
196 try
197 {
198 cmd = connection.prepareStatement(buf.toString());
199
200 cmd.setString(1, (element.getGroup() == null ? "" : element.getGroup()));
201 cmd.setString(2, (element.getDescription() == null ? "" : element.getDescription()));
202 cmd.setBytes(3, (new String (element.getMessage() == null ? "" : element.getMessage()).getBytes()));
203 cmd.setString(4, element.getId());
204
205 int index = 5;
206 if (group_constraint != null)
207 {
208 for (Iterator i = group_constraint.iterator(); i.hasNext();)
209 {
210 cmd.setString(index++, (String )i.next());
211 }
212 }
213
214 Statement stmt = connection.createStatement();
215 stmt.executeUpdate("use webtalk");
216
217 int count = cmd.executeUpdate();
218 if (count == 0)
219 {
220 errorMessage = null;
221 return false;
222 }
223 }
224 catch (SQLException ex)
225 {
226 errorMessage = "SQLException: " + ex.getMessage() + ", SQL command: " + cmd;
227 return false;
228 }
229
230 return true;
231 }
232
233 public boolean delete(String id, ArrayList group_constraint)
234 {
236 PreparedStatement cmd = null;
237
238 try
239 {
240 if (group_constraint != null)
241 {
242 StringBuffer buf = new StringBuffer ("delete from "
243 + TABLE_NAME
244 + " where "
245 + ID
246 + " = ? and (");
247
248 for (Iterator i = group_constraint.iterator(); i.hasNext();)
249 {
250 buf.append(GROUP
251 + " = ?");
252
253 i.next();
254
255 if (i.hasNext() == true)
256 {
257 buf.append(" or ");
258 }
259 }
260
261 buf.append(')');
262
263 cmd = connection.prepareStatement(buf.toString());
264
265 cmd.setString(1, id);
266
267 int index = 2;
268
269 for (Iterator i = group_constraint.iterator(); i.hasNext();)
270 {
271 cmd.setString(index++, (String )i.next());
272 }
273 }
274 else
275 {
276 cmd = connection.prepareStatement("delete from "
277 + TABLE_NAME
278 + " where "
279 + ID
280 + " = ?");
281
282 cmd.setString(1, id);
283 }
284
285 Statement stmt = connection.createStatement();
286 stmt.executeUpdate("use webtalk");
287
288 int count = cmd.executeUpdate();
289 if (count == 0)
290 {
291 errorMessage = null;
292 return false;
293 }
294 }
295 catch (SQLException ex)
296 {
297 errorMessage = "SQLException: " + ex.getMessage() + ", SQL command: " + cmd;
298 return false;
299 }
300
301 return true;
302 }
303
304 public ArrayList search(CannedMessageElement element, String sortby, String domain_constraint)
305 {
307 boolean where = false;
308
309 StringBuffer constraints = new StringBuffer ("select "
310 + ID
311 + ','
312 + GROUP
313 + " from "
314 + TABLE_NAME);
315
316 if (domain_constraint != null)
317 {
318 constraints.append(',' + GroupTable.GROUP_TABLE_NAME);
319 }
320
321 String id = element.getId();
322 if ((id != null) && (id.length() > 0))
323 {
324 if (where == false)
325 {
326 constraints.append(" where ");
327 where = true;
328 }
329 else
330 {
331 constraints.append(" and ");
332 }
333
334 constraints.append(ID
335 + " like ?");
336 }
337
338 String group = element.getGroup();
339 if ((group != null) && (group.length() > 0))
340 {
341 if (where == false)
342 {
343 constraints.append(" where ");
344 where = true;
345 }
346 else
347 {
348 constraints.append(" and ");
349 }
350
351 constraints.append(GROUP
352 + " like ?");
353 }
354
355 String description = element.getDescription();
356 if ((description != null) && (description.length() > 0))
357 {
358 if (where == false)
359 {
360 constraints.append(" where ");
361 where = true;
362 }
363 else
364 {
365 constraints.append(" and ");
366 }
367
368 constraints.append(DESCRIPTION
369 + " like ?");
370 }
371
372 String message = element.getMessage();
373 if ((message != null) && (message.length() > 0))
374 {
375 if (where == false)
376 {
377 constraints.append(" where ");
378 where = true;
379 }
380 else
381 {
382 constraints.append(" and ");
383 }
384
385 constraints.append(MESSAGE
386 + " like ?");
387 }
388
389 if (domain_constraint != null)
390 {
391 if (where == false)
392 {
393 constraints.append(" where ");
394 where = true;
395 }
396 else
397 {
398 constraints.append(" and ");
399 }
400
401 constraints.append(GroupTable.DOMAIN
402 + " = ? and "
403 + GroupTable.GROUPNAME
404 + '='
405 + GROUP);
406 }
407
408 if ((sortby != null) && (sortby.equals("Group") == true))
409 {
410 constraints.append(" order by " + GROUP + ", 1");
411 }
412 else
413 {
414 constraints.append(" order by 1");
415 }
416
417
418 PreparedStatement cmd = null;
419
420 try
421 {
422 cmd = connection.prepareStatement(constraints.toString());
423
424 int index = 1;
425
426 if ((id != null) && (id.length() > 0))
427 {
428 cmd.setString(index++, id);
429 }
430
431 if ((group != null) && (group.length() > 0))
432 {
433 cmd.setString(index++, group);
434 }
435
436 if ((description != null) && (description.length() > 0))
437 {
438 cmd.setString(index++, description);
439 }
440
441 if ((message != null) && (message.length() > 0))
442 {
443 cmd.setString(index++, message);
444 }
445
446 if (domain_constraint != null)
447 {
448 cmd.setString(index++, domain_constraint);
449 }
450
451
452 Statement stmt = connection.createStatement();
453 stmt.executeUpdate("use webtalk");
454
455 ResultSet rs = cmd.executeQuery();
456
457 ArrayList list = new ArrayList();
458 while (rs.next() == true)
459 {
460 CannedMessageElement ele = new CannedMessageElement();
461 ele.setId(rs.getString(1));
462 ele.setGroup(rs.getString(2));
463
464 list.add(ele);
465 }
466
467 return list;
468 }
469 catch (SQLException ex)
470 {
471 errorMessage = "SQLException: " + ex.getMessage() + ", SQL command: " + cmd;
472 return null;
473 }
474 }
475
476
477 }
478 |
Popular Tags |
Java API By Example, From Geeks To Geeks.