KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > sellwin > db > UserGroupDB


1 package sellwin.db;
2
3 import sellwin.domain.*;
4 import sellwin.utils.*;
5
6 import java.sql.*;
7 import java.util.*;
8
9 // SellWin http://sourceforge.net/projects/sellwincrm
10
//Contact support@open-app.com for commercial help with SellWin
11
//This software is provided "AS IS", without a warranty of any kind.
12

13 /**
14  * This class implements the DBInterface for
15  * the UserGroup class objects which are stored
16  * in the user_group database table.
17  */

18 public class UserGroupDB extends DBType implements DBInterface {
19     private Connection con;
20
21     private final static String JavaDoc deleteAllQuery =
22         "DELETE FROM user_group";
23
24     private final static String JavaDoc deleteByNameQuery =
25         "DELETE FROM user_group WHERE name = ";
26
27     private final static String JavaDoc deleteQuery =
28         "DELETE FROM user_group WHERE pk = ";
29
30     private final static String JavaDoc insertQuery =
31         "INSERT INTO user_group " +
32         "VALUES (";
33     private final static String JavaDoc updateQuery =
34             "UPDATE user_group " +
35             "SET ";
36     private final static String JavaDoc selectQuery =
37             "SELECT name, "+
38             "modified_by, modified_date " +
39             "FROM user_group "+
40             "WHERE pk = ";
41
42     private final static String JavaDoc selectAllQuery =
43             "SELECT pk, name, "+
44             "modified_by, modified_date " +
45             "FROM user_group ";
46
47     private final static String JavaDoc selectUsersInGroupQuery =
48             "SELECT user_group_member.user_pk " +
49             "FROM user_group, user_group_member " +
50             "WHERE " +
51             "user_group.pk = user_group_member.user_group_pk AND "+
52             "user_group.name = ";
53
54     /**
55      * a do-nothing constructor but necessary to
56      * do the operations offered by this class
57      */

58     public UserGroupDB() {
59     }
60
61     /**
62      * construct using a db type
63      * @param dbType the db type to assume
64      */

65     public UserGroupDB(int dbType) {
66         DB_TYPE = dbType;
67     }
68
69     /**
70      * a version of the constructor when a connection
71      * is already obtained from somewhere else
72      *
73      * @param con the Connection to use
74      */

75     public UserGroupDB(Connection con) {
76         this.con = con;
77     }
78
79     /**
80      * return the Connection in use
81      *
82      * @return the Connection in use
83      */

84     public Connection getConnection() {
85         return this.con;
86     }
87
88     /**
89      * set the Connection to use
90      *
91      * @param con the Connection to use for any future IO's
92      */

93     public final void setConnection(Connection con)
94         throws SQLException {
95
96         this.con = con;
97     }
98
99     /**
100      * select a single user_group row using the passed
101      * primary key
102      *
103      * @param pk the primary key we are searching with
104      * @return the UserGroup row(s) that were selected
105      * @exception java.sql.SQLException
106      */

107     public final Object JavaDoc selectRow(Object JavaDoc pk)
108         throws SQLException {
109
110         UserGroup ug = new UserGroup("");
111         ug.setPK(((Long JavaDoc)pk).longValue());
112
113         Statement stmt = null;
114         ResultSet rs = null;
115         String JavaDoc query = selectQuery + ug.getPK();
116
117         try {
118             stmt = con.createStatement();
119             if (Prefs.DEBUG) LogWrite.write(query);
120             rs = stmt.executeQuery(query);
121
122             int i;
123
124             while (rs.next()) {
125                 i=1;
126                 ug.setName(rs.getString(i)); i++;
127                 ug.setModifiedBy(rs.getString(i)); i++;
128                 ug.setModifiedDate(rs.getDate(i));
129             }
130
131         } catch (SQLException e) {
132             throw e;
133         } finally {
134             try {
135                 if (rs != null) rs.close();
136             } catch (SQLException x) { throw x; }
137             try {
138                 if (stmt != null) stmt.close();
139             } catch (SQLException x) { throw x; }
140         }
141
142         return ug;
143     }
144
145
146     /**
147      * update a single user_group row using the passed
148      * UserGroup object's attributes. All columns
149      * get updated by this routine regardless of whether
150      * an attribute was modified or not.
151      *
152      * @param obj the object we are updating
153      * @exception java.sql.SQLException
154      */

155     public final void updateRow(Object JavaDoc obj)
156         throws SQLException {
157
158         UserGroup user_group = (UserGroup)obj;
159
160         StringBuffer JavaDoc query = new StringBuffer JavaDoc(updateQuery);
161         Statement stmt = con.createStatement();
162
163         query.append("name=");
164         query.append(JDBC.quoteMore(user_group.getName()));
165         query.append("modified_by=");
166         query.append(JDBC.quoteMore(user_group.getModifiedBy()));
167         query.append("modified_date=");
168         if (DB_TYPE == Prefs.MYSQL)
169             query.append("CURRENT_DATE");
170         else
171             query.append("SYSDATE");
172         query.append(" WHERE pk=").append(user_group.getPK());
173
174         if (Prefs.DEBUG) LogWrite.write(query.toString());
175         int updatedRows = stmt.executeUpdate(query.toString());
176     }
177
178     /**
179      * insert a new user_group row using the passed
180      * UserGroup object as the column values.
181      *
182      * @param obj the object we are inserting
183      * @param load true if loading, false if adding
184      * @return the newly assigned primary key of the new row
185      * @exception java.sql.SQLException
186      */

187     public final long insertRow(Object JavaDoc obj, boolean load)
188         throws SQLException {
189
190         UserGroup user_group = (UserGroup)obj;
191
192         if (!load)
193             user_group.setPK(DBUtils.generatePK());
194
195         StringBuffer JavaDoc query = new StringBuffer JavaDoc(insertQuery);
196         Statement stmt = con.createStatement();
197
198         query.append(user_group.getPK()).append(",");
199         query.append(JDBC.quoteMore(user_group.getName()));
200         query.append(JDBC.quoteMore(user_group.getModifiedBy()));
201         if (DB_TYPE == Prefs.MYSQL)
202             query.append("CURRENT_DATE");
203         else
204             query.append("SYSDATE");
205         query.append(")");
206
207         if (Prefs.DEBUG) LogWrite.write(query.toString());
208         int rc = stmt.executeUpdate(query.toString());
209
210         return user_group.getPK();
211     }
212
213     /**
214      * delete a single user_group row using the passed
215      * primary key value
216      *
217      * @param ojb primary key stored in a Long
218      * @exception java.sql.SQLException
219      */

220     public final void deleteRow(Object JavaDoc obj)
221         throws SQLException {
222
223         long pkValue = ((Long JavaDoc)obj).longValue();
224
225         String JavaDoc query = deleteQuery + pkValue;
226
227         Statement stmt = null;
228
229         try {
230             stmt = con.createStatement();
231             if (Prefs.DEBUG) LogWrite.write(query);
232             stmt.executeUpdate(query);
233         } catch (SQLException e) {
234             throw e;
235         } finally {
236             try {
237                 if (stmt != null) stmt.close();
238             } catch (SQLException x) { }
239         }
240     }
241
242     /**
243      * delete a single user_group row using the passed
244      * group name
245      *
246      * @param ojb primary key stored in a Long
247      * @exception java.sql.SQLException
248      */

249     public final void deleteByNameRow(String JavaDoc name)
250         throws SQLException {
251
252         String JavaDoc query = deleteByNameQuery + "'" + name + "'";
253
254         Statement stmt = null;
255
256         try {
257             stmt = con.createStatement();
258             if (Prefs.DEBUG) LogWrite.write(query);
259             stmt.executeUpdate(query);
260         } catch (SQLException e) {
261             throw e;
262         } finally {
263             try {
264                 if (stmt != null) stmt.close();
265             } catch (SQLException x) { }
266         }
267     }
268
269     /**
270      * select all user names for a given user group
271      *
272      * @param groupName the group name we are searching with
273      * @return the SalesPerson row(s) that were selected
274      * @exception java.sql.SQLException
275      */

276     public final TreeMap selectUsersInGroup(String JavaDoc groupName)
277         throws SQLException {
278
279         TreeMap users = new TreeMap();
280         Statement stmt = null;
281         ResultSet rs = null;
282         String JavaDoc query = selectUsersInGroupQuery + "'" + groupName + "'";
283
284         try {
285             stmt = con.createStatement();
286             if (Prefs.DEBUG) LogWrite.write(query);
287             rs = stmt.executeQuery(query);
288
289             int i;
290             long user_pk;
291             SalesPerson sp=null;
292             SalesPersonDB salesPersonDB = new SalesPersonDB(DB_TYPE);
293             salesPersonDB.setConnection(getConnection());
294
295             while (rs.next()) {
296                 i=1;
297                 user_pk = rs.getLong(i); i++;
298                 sp = (SalesPerson)(salesPersonDB.selectRow(new Long JavaDoc(user_pk)));
299                 users.put(sp.getID(), sp);
300             }
301         } catch (SQLException e) {
302             throw e;
303         } finally {
304             try {
305                 if (rs != null) rs.close();
306             } catch (SQLException x) { throw x; }
307             try {
308                 if (stmt != null) stmt.close();
309             } catch (SQLException x) { throw x; }
310         }
311
312         return users;
313     }
314
315     /**
316      * select all user pks for a given user group
317      * @param groupName the group name we are searching with
318      * @return the primary key(s) that were selected
319      * @exception java.sql.SQLException
320      */

321     public final ArrayList selectUserPKsInGroup(String JavaDoc groupName)
322         throws SQLException {
323
324         ArrayList list = new ArrayList();
325         Statement stmt = null;
326         ResultSet rs = null;
327         String JavaDoc query = selectUsersInGroupQuery + "'" + groupName + "'";
328
329         try {
330             stmt = con.createStatement();
331             if (Prefs.DEBUG) LogWrite.write(query);
332             rs = stmt.executeQuery(query);
333
334             int i;
335             long user_pk;
336
337             while (rs.next()) {
338                 i=1;
339                 user_pk = rs.getLong(i); i++;
340                 list.add(new Long JavaDoc(user_pk));
341             }
342         } catch (SQLException e) {
343             throw e;
344         } finally {
345             try {
346                 if (rs != null) rs.close();
347             } catch (SQLException x) { throw x; }
348             try {
349                 if (stmt != null) stmt.close();
350             } catch (SQLException x) { throw x; }
351         }
352
353         return list;
354     }
355
356     /**
357      * select all user_group rows using the passed
358      *
359      * @param lastSyncDate a user's last sync date, if not null, is
360      * used to limit the query
361      * @return the UserGroup row(s) that were selected
362      * @exception java.sql.SQLException
363      */

364     public final TreeMap selectAllRows(java.util.Date JavaDoc lastSyncDate)
365         throws SQLException {
366
367         UserGroup ug = null;
368         TreeMap list = new TreeMap();
369         Statement stmt = null;
370         ResultSet rs = null;
371         StringBuffer JavaDoc query = new StringBuffer JavaDoc();
372         query.append(selectAllQuery);
373         if (lastSyncDate != null) {
374             query.append(" WHERE user_group.modified_date > ");
375             query.append(DateUtils.formatDateTime(DB_TYPE, lastSyncDate));
376         }
377         query.append(" ORDER BY name");
378
379         try {
380             stmt = con.createStatement();
381             if (Prefs.DEBUG) LogWrite.write(query.toString());
382             rs = stmt.executeQuery(query.toString());
383
384             int i,j;
385             ArrayList users;
386             SalesPerson sp;
387
388             while (rs.next()) {
389                 i=1;
390                 ug = new UserGroup();
391                 ug.setPK(rs.getLong(i)); i++;
392                 ug.setName(rs.getString(i)); i++;
393                 ug.setModifiedBy(rs.getString(i)); i++;
394                 ug.setModifiedDate(rs.getDate(i));
395     
396                 users = selectUserPKsInGroup(ug.getName());
397                 for (j=0;j<users.size();j++) {
398                     ug.addUser((Long JavaDoc)users.get(j));
399                 }
400
401                 list.put(ug.getName(), ug);
402             }
403
404         } catch (SQLException e) {
405             throw e;
406         } finally {
407             try {
408                 if (rs != null) rs.close();
409             } catch (SQLException x) { throw x; }
410             try {
411                 if (stmt != null) stmt.close();
412             } catch (SQLException x) { throw x; }
413         }
414
415         return list;
416     }
417
418     /**
419      * delete all user_group rows
420      *
421      * @exception java.sql.SQLException
422      */

423     public final void deleteAllRows()
424         throws SQLException {
425
426         String JavaDoc query = deleteAllQuery;
427
428         Statement stmt = null;
429
430         try {
431             stmt = con.createStatement();
432             if (Prefs.DEBUG) LogWrite.write(query);
433             stmt.executeUpdate(query);
434         } catch (SQLException e) {
435             throw e;
436         } finally {
437             try {
438                 if (stmt != null) stmt.close();
439             } catch (SQLException x) { }
440         }
441     }
442     /**
443      * truncate the whole table
444      *
445      * @exception java.sql.SQLException
446      */

447     public final void truncate()
448         throws SQLException {
449
450         String JavaDoc query = "truncate table user_group";
451    
452         Statement stmt = null;
453         try {
454             stmt = con.createStatement();
455             if (Prefs.DEBUG) LogWrite.write(query);
456             stmt.executeUpdate(query);
457         } catch (SQLException e) {
458             throw e;
459         } finally {
460             try { if (stmt != null) stmt.close();
461             } catch (SQLException x) { }
462         }
463     }
464
465 }
466
Popular Tags