KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > com > quikj > application > communicator > applications > webtalk > model > GroupTable


1 /*
2  * GroupTable.java
3  *
4  * Created on May 13, 2003, 11:38 AM
5  */

6
7 package com.quikj.application.communicator.applications.webtalk.model;
8
9 import java.sql.*;
10 import java.util.*;
11
12 /**
13  *
14  * @author bhm
15  */

16 public class GroupTable
17 {
18     // database group table name constant
19
public static final String JavaDoc GROUP_TABLE_NAME = "group_tbl";
20     
21     // database group table column name constants
22
public static final String JavaDoc GROUPNAME = "groupid";
23     public static final String JavaDoc DOMAIN = "domain";
24     public static final String JavaDoc FLAGS = "flags";
25     public static final String JavaDoc MEMBERLOGIN_NOTIFICATION = "memberlogin_notif";
26     public static final String JavaDoc MEMBERBUSY_NOTIFICATION = "memberbusy_notif";
27     public static final String JavaDoc OWNERLOGIN_NOTIFICATION = "ownerlogin_notif";
28     public static final String JavaDoc OWNERBUSY_NOTIFICATION = "ownerbusy_notif";
29     
30     // database group owner table name constant
31
public static final String JavaDoc GROUP_OWNER_TABLE_NAME = "group_owner_tbl";
32     
33     // database group owner table column name constants
34
public static final String JavaDoc USERNAME = UserTable.USERNAME;
35     //public static final String GROUPNAME = "groupid";
36

37     // database group member table name constant
38
public static final String JavaDoc GROUP_MEMBER_TABLE_NAME = "group_member_tbl";
39     
40     // database group member table column name constants
41
//public static final String USERNAME = UserTable.USERNAME;
42
//public static final String GROUPNAME = "groupid";
43

44     /** Holds value of property connection. */
45     private Connection connection;
46     
47     /** Holds value of property errorMessage. */
48     private String JavaDoc errorMessage;
49     
50     /** Creates a new instance of GroupTable */
51     public GroupTable()
52     {
53     }
54     
55     /** Setter for property connection.
56      * @param connection New value of property connection.
57      *
58      */

59     public void setConnection(Connection connection)
60     {
61         this.connection = connection;
62     }
63     
64     /** Getter for property errorMessage.
65      * @return Value of property errorMessage.
66      *
67      */

68     public String JavaDoc getErrorMessage()
69     {
70         return this.errorMessage;
71     }
72     
73     public GroupElement query(String JavaDoc groupname)
74     {
75         return query(groupname, null);
76     }
77     
78     public GroupElement query(String JavaDoc groupname, String JavaDoc domain_constraint)
79     {
80         StringBuffer JavaDoc cmd = new StringBuffer JavaDoc("select "
81         + DOMAIN
82         + ", "
83         + MEMBERLOGIN_NOTIFICATION
84         + ", "
85         + MEMBERBUSY_NOTIFICATION
86         + ", "
87         + OWNERLOGIN_NOTIFICATION
88         + ", "
89         + OWNERBUSY_NOTIFICATION
90         + " from "
91         + GROUP_TABLE_NAME
92         + " where ");
93         
94         if (domain_constraint != null)
95         {
96             cmd.append(DOMAIN + "= ? and ");
97         }
98         
99         cmd.append(GROUPNAME
100         + " = ?");
101         
102         try
103         {
104             Statement stmt = connection.createStatement();
105             stmt.executeUpdate("use webtalk");
106             
107             PreparedStatement pstmt = connection.prepareStatement(cmd.toString());
108             int index = 1;
109             if (domain_constraint != null)
110             {
111                 pstmt.setString(index++, domain_constraint);
112             }
113             
114             pstmt.setString(index++, groupname);
115             ResultSet rs = pstmt.executeQuery();
116             if (!rs.first())
117             {
118                 // Doesn't exist
119
errorMessage = null;
120                 return null;
121             }
122             
123             GroupElement groupinfo = new GroupElement();
124             groupinfo.setDomain(rs.getString(1));
125             groupinfo.setMemberLoginNotificationControl(rs.getInt(2));
126             groupinfo.setMemberBusyNotificationControl(rs.getInt(3));
127             groupinfo.setOwnerLoginNotificationControl(rs.getInt(4));
128             groupinfo.setOwnerBusyNotificationControl(rs.getInt(5));
129             
130             groupinfo.setName(groupname);
131             
132             return groupinfo;
133         }
134         catch (SQLException ex)
135         {
136             errorMessage = "SQLException: " + ex.getMessage() + ", SQL command: " + cmd;
137             return null;
138         }
139     }
140     
141     public ArrayList list()
142     {
143         return list(null);
144     }
145     
146     public ArrayList list(String JavaDoc domain_constraint)
147     // returns list of 0 or more elements, or null if error encountered
148
{
149         StringBuffer JavaDoc cmd = new StringBuffer JavaDoc("select "
150         + GROUPNAME
151         + " from "
152         + GROUP_TABLE_NAME);
153         
154         if (domain_constraint != null)
155         {
156             cmd.append(" where " + DOMAIN + "= ?");
157         }
158         
159         cmd.append(" order by "
160         + GROUPNAME);
161         
162         try
163         {
164             Statement stmt = connection.createStatement();
165             stmt.executeUpdate("use webtalk");
166             
167             PreparedStatement pstmt = connection.prepareStatement(cmd.toString());
168             if (domain_constraint != null)
169             {
170                 pstmt.setString(1, domain_constraint);
171             }
172             
173             ResultSet rs = pstmt.executeQuery();
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 ArrayList listAllByDomain()
191     // returns list of 0 or more GroupElements (name, domain field only), or null if error encountered
192
{
193         String JavaDoc cmd = "select "
194         + GROUPNAME
195         + ','
196         + DOMAIN
197         + " from "
198         + GROUP_TABLE_NAME
199         + " order by 2, 1";
200         
201         try
202         {
203             Statement stmt = connection.createStatement();
204             stmt.executeUpdate("use webtalk");
205             
206             ResultSet rs = stmt.executeQuery(cmd);
207             
208             ArrayList list = new ArrayList();
209             while (rs.next() == true)
210             {
211                 GroupElement ele = new GroupElement();
212                 ele.setName(rs.getString(1));
213                 ele.setDomain(rs.getString(2));
214                 
215                 list.add(ele);
216             }
217             
218             return list;
219         }
220         catch (SQLException ex)
221         {
222             errorMessage = "SQLException: " + ex.getMessage() + ", SQL command: " + cmd;
223             return null;
224         }
225     }
226     
227     public ArrayList listDomains()
228     // returns list of 0 or more domain names, or null if error encountered
229
{
230         String JavaDoc cmd = "select distinct "
231         + DOMAIN
232         + " from "
233         + GROUP_TABLE_NAME
234         + " order by 1";
235         
236         try
237         {
238             Statement stmt = connection.createStatement();
239             stmt.executeUpdate("use webtalk");
240             
241             ResultSet rs = stmt.executeQuery(cmd);
242             
243             ArrayList list = new ArrayList();
244             while (rs.next() == true)
245             {
246                 list.add(rs.getString(1));
247             }
248             
249             return list;
250         }
251         catch (SQLException ex)
252         {
253             errorMessage = "SQLException: " + ex.getMessage() + ", SQL command: " + cmd;
254             return null;
255         }
256     }
257     
258     public boolean create(GroupElement group)
259     {
260         String JavaDoc cmd = "insert into "
261         + GROUP_TABLE_NAME
262         + " values (?, ?, ?, ?, ?, ?, ?)";
263         
264         try
265         {
266             Statement stmt = connection.createStatement();
267             stmt.executeUpdate("use webtalk");
268             
269             PreparedStatement pstmt = connection.prepareStatement(cmd);
270             pstmt.setString(1, group.getName());
271             pstmt.setString(2, group.getDomain() == null ? "" : group.getDomain());
272             pstmt.setString(3, "0");
273             pstmt.setInt(4, group.getMemberLoginNotificationControl());
274             pstmt.setInt(5, group.getMemberBusyNotificationControl());
275             pstmt.setInt(6, group.getOwnerLoginNotificationControl());
276             pstmt.setInt(7, group.getOwnerBusyNotificationControl());
277             
278             int count = pstmt.executeUpdate();
279             if (count == 0)
280             {
281                 errorMessage = "Group create failed: no rows affected, SQL command: " + cmd;
282                 return false;
283             }
284         }
285         catch (SQLException ex)
286         {
287             errorMessage = "SQLException: " + ex.getMessage() + ", SQL command: " + cmd;
288             return false;
289         }
290         
291         return true;
292     }
293     
294     public boolean modify(GroupElement group)
295     {
296         return modify(group, null);
297     }
298     
299     public boolean modify(GroupElement group, String JavaDoc domain_constraint)
300     {
301         StringBuffer JavaDoc cmd = new StringBuffer JavaDoc("update "
302         + GROUP_TABLE_NAME
303         + " set "
304         + DOMAIN
305         + "= ?, "
306         + MEMBERLOGIN_NOTIFICATION
307         + "= ?, "
308         + MEMBERBUSY_NOTIFICATION
309         + "= ?, "
310         + OWNERLOGIN_NOTIFICATION
311         + "= ?, "
312         + OWNERBUSY_NOTIFICATION
313         + "= ? "
314         + " where ");
315         
316         if (domain_constraint != null)
317         {
318             cmd.append(DOMAIN + "= ? and ");
319         }
320         
321         cmd.append(GROUPNAME
322         + " = ?");
323         try
324         {
325             Statement stmt = connection.createStatement();
326             stmt.executeUpdate("use webtalk");
327             
328             PreparedStatement pstmt = connection.prepareStatement(cmd.toString());
329             int index = 1;
330             
331             pstmt.setString(index++, group.getDomain());
332             pstmt.setInt(index++, group.getMemberLoginNotificationControl());
333             pstmt.setInt(index++, group.getMemberBusyNotificationControl());
334             pstmt.setInt(index++, group.getOwnerLoginNotificationControl());
335             pstmt.setInt(index++, group.getOwnerBusyNotificationControl());
336             
337             if (domain_constraint != null)
338             {
339                 pstmt.setString(index++, domain_constraint);
340             }
341             pstmt.setString(index++, group.getName());
342             int count = pstmt.executeUpdate();
343             if (count == 0)
344             {
345                 errorMessage = "Database not updated - the group was not found";
346                 return false;
347             }
348         }
349         catch (SQLException ex)
350         {
351             errorMessage = "SQLException: " + ex.getMessage() + ", SQL command: " + cmd;
352             return false;
353         }
354         
355         return true;
356     }
357     
358     public boolean delete(String JavaDoc groupname)
359     {
360         return delete(groupname, null);
361     }
362     
363     public boolean delete(String JavaDoc groupname, String JavaDoc domain_constraint)
364     {
365         StringBuffer JavaDoc cmd = new StringBuffer JavaDoc("delete from "
366         + GROUP_TABLE_NAME
367         + " where ");
368         
369         if (domain_constraint != null)
370         {
371             cmd.append(DOMAIN + "= ? and ");
372         }
373         
374         cmd.append(GROUPNAME
375         + " = ?");
376         
377         try
378         {
379             Statement stmt = connection.createStatement();
380             stmt.executeUpdate("use webtalk");
381             
382             PreparedStatement pstmt = connection.prepareStatement(cmd.toString());
383             int index = 1;
384             if (domain_constraint != null)
385             {
386                 pstmt.setString(index++, domain_constraint);
387             }
388             pstmt.setString(index++, groupname);
389             
390             int count = pstmt.executeUpdate();
391             if (count == 0)
392             {
393                 errorMessage = null;
394                 return false;
395             }
396             
397             String JavaDoc sql = "delete from "
398             + GROUP_OWNER_TABLE_NAME
399             + " where "
400             + GROUPNAME
401             + " = ?";
402             
403             pstmt = connection.prepareStatement(sql);
404             pstmt.setString(1, groupname);
405             pstmt.executeUpdate();
406             
407             sql = "delete from "
408             + GROUP_MEMBER_TABLE_NAME
409             + " where "
410             + GROUPNAME
411             + " = ?";
412             pstmt = connection.prepareStatement(sql);
413             pstmt.setString(1, groupname);
414             
415             pstmt.executeUpdate();
416             
417             sql = "delete from "
418             + CannedMessageTable.TABLE_NAME
419             + " where "
420             + CannedMessageTable.GROUP
421             + " = ?";
422             pstmt = connection.prepareStatement(sql);
423             pstmt.setString(1, groupname);
424             
425             pstmt.executeUpdate();
426             
427         }
428         catch (SQLException ex)
429         {
430             errorMessage = "SQLException: " + ex.getMessage() + ", SQL command: " + cmd;
431             return false;
432         }
433         
434         return true;
435     }
436     
437     
438 }
439
Popular Tags