KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > sellwin > db > UserGroupMemberDB


1 package sellwin.db;
2
3 import sellwin.domain.*;
4 import sellwin.utils.*;
5
6 import java.sql.*;
7 import java.util.ArrayList JavaDoc;
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 UserGroupMember class objects which are stored
16  * in the user_group_member database table.
17  */

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

66     public UserGroupMemberDB() {
67     }
68
69     /**
70      * construct using a db type
71      * @param dbType the db type to assume
72      */

73     public UserGroupMemberDB(int dbType) {
74         DB_TYPE = dbType;
75     }
76
77     /**
78      * a version of the constructor when a connection
79      * is already obtained from somewhere else
80      *
81      * @param con the Connection to use
82      */

83     public UserGroupMemberDB(Connection con) {
84         this.con = con;
85     }
86
87     /**
88      * return the Connection in use
89      *
90      * @return the Connection in use
91      */

92     public Connection getConnection() {
93         return this.con;
94     }
95
96     /**
97      * set the Connection to use
98      *
99      * @param con the Connection to use for any future IO's
100      */

101     public final void setConnection(Connection con)
102         throws SQLException {
103
104         this.con = con;
105     }
106
107     /**
108      * select a single user_group_member row using the passed
109      * primary key
110      *
111      * @param pk the primary key we are searching with
112      * @return the UserGroupMember row(s) that were selected
113      * @exception java.sql.SQLException
114      */

115     public final Object JavaDoc selectRow(Object JavaDoc pk)
116         throws SQLException {
117
118         UserGroupMember ug = new UserGroupMember();
119         ug.setPK(((Long JavaDoc)pk).longValue());
120
121         Statement stmt = null;
122         ResultSet rs = null;
123         String JavaDoc query = selectQuery + ug.getPK();
124
125         try {
126             stmt = con.createStatement();
127             if (Prefs.DEBUG) LogWrite.write(query);
128             rs = stmt.executeQuery(query);
129
130             int i;
131
132             while (rs.next()) {
133                 i=1;
134                 ug.setUserGroupPK(rs.getLong(i)); i++;
135                 ug.setUserPK(rs.getLong(i)); i++;
136                 SalesPersonDB spd = new SalesPersonDB(DB_TYPE);
137                 spd.setConnection(getConnection());
138                 SalesPerson sp = (SalesPerson)spd.selectRow(new Long JavaDoc(ug.getUserPK()));
139                 ug.setUser(sp);
140                 ug.setModifiedBy(rs.getString(i)); i++;
141                 ug.setModifiedDate(rs.getDate(i));
142             }
143
144         } catch (SQLException e) {
145             throw e;
146         } finally {
147             try {
148                 if (rs != null) rs.close();
149             } catch (SQLException x) { throw x; }
150             try {
151                 if (stmt != null) stmt.close();
152             } catch (SQLException x) { throw x; }
153         }
154
155         return ug;
156     }
157
158
159     /**
160      * currently not necessary
161      *
162      * @param name description
163      * @exception java.sql.SQLException
164      */

165     public final void updateRow(Object JavaDoc obj)
166         throws SQLException {
167
168         //currently not needed
169
}
170
171     /**
172      * insert a new user_group_member row using the passed
173      * UserGroupMember object as the column values.
174      *
175      * @param obj the object we are inserting
176      * @param load true if loading, false if adding
177      * @return the newly assigned primary key of the new row
178      * @exception java.sql.SQLException
179      */

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

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

243     public final void deleteByUserRow(long u_pk)
244         throws SQLException {
245
246         String JavaDoc query = deleteUserQuery + u_pk;
247
248         Statement stmt = null;
249
250         try {
251             stmt = con.createStatement();
252             if (Prefs.DEBUG) LogWrite.write(query);
253             stmt.executeUpdate(query);
254         } catch (SQLException e) {
255             throw e;
256         } finally {
257             try {
258                 if (stmt != null) stmt.close();
259             } catch (SQLException x) { }
260         }
261     }
262
263     /**
264      * select all sales persons in a group using the passed
265      * group key
266      *
267      * @param ug_pk the UserGroup primary key we are searching with
268      * @return the UserGroupMember row(s) that were selected
269      * @exception java.sql.SQLException
270      */

271     public final ArrayList JavaDoc selectUsersInGroup(long ug_pk)
272         throws SQLException {
273
274         ArrayList JavaDoc users = new ArrayList JavaDoc();
275         Statement stmt = null;
276         ResultSet rs = null;
277         String JavaDoc query = selectUsersQuery + ug_pk;
278
279         try {
280             stmt = con.createStatement();
281             if (Prefs.DEBUG) LogWrite.write(query);
282             rs = stmt.executeQuery(query);
283             SalesPersonDB spDB = new SalesPersonDB(DB_TYPE);
284             spDB.setConnection(getConnection());
285             long sp_pk;
286             SalesPerson sp;
287
288             while (rs.next()) {
289                 sp_pk = rs.getLong(1);
290                 sp = (SalesPerson)(spDB.selectRow(new Long JavaDoc(sp_pk)));
291                 users.add(sp);
292             }
293         } catch (SQLException e) {
294             throw e;
295         } finally {
296             try {
297                 if (rs != null) rs.close();
298             } catch (SQLException x) { throw x; }
299             try {
300                 if (stmt != null) stmt.close();
301             } catch (SQLException x) { throw x; }
302         }
303
304         return users;
305     }
306
307     /**
308      * select all group names for a given user
309      *
310      * @param user_pk the User primary key we are searching with
311      * @return the UserGroupMember row(s) that were selected
312      * @exception java.sql.SQLException
313      */

314     public final ArrayList JavaDoc selectGroupNamesForUser(long user_pk)
315         throws SQLException {
316
317         ArrayList JavaDoc groupNames = new ArrayList JavaDoc();
318         Statement stmt = null;
319         ResultSet rs = null;
320         String JavaDoc query = selectGroupNamesQuery + user_pk +
321         " ORDER BY user_group.name ";
322
323         try {
324             stmt = con.createStatement();
325             if (Prefs.DEBUG) LogWrite.write(query);
326             rs = stmt.executeQuery(query);
327             String JavaDoc groupName;
328
329             while (rs.next()) {
330                 groupName = rs.getString(1);
331                 groupNames.add(groupName);
332             }
333         } catch (SQLException e) {
334             throw e;
335         } finally {
336             try {
337                 if (rs != null) rs.close();
338             } catch (SQLException x) { throw x; }
339             try {
340                 if (stmt != null) stmt.close();
341             } catch (SQLException x) { throw x; }
342         }
343
344         return groupNames;
345     }
346
347     /**
348      * select all groups for a given user
349      *
350      * @param user_pk the User primary key we are searching with
351      * @return the UserGroup row(s) that were selected
352      * @exception java.sql.SQLException
353      */

354     public final ArrayList JavaDoc selectGroupsForUser(long user_pk)
355         throws SQLException {
356
357         ArrayList JavaDoc groups = new ArrayList JavaDoc();
358         Statement stmt = null;
359         ResultSet rs = null;
360         String JavaDoc query = selectGroupsForUserQuery + user_pk ;
361
362         try {
363             stmt = con.createStatement();
364             if (Prefs.DEBUG) LogWrite.write(query);
365             rs = stmt.executeQuery(query);
366             UserGroup user_group;
367             long group_pk;
368             UserGroupDB userGroupDB = new UserGroupDB(DB_TYPE);
369             userGroupDB.setConnection(getConnection());
370
371             while (rs.next()) {
372                 group_pk = rs.getLong(1);
373                 user_group = (UserGroup)userGroupDB.selectRow(new Long JavaDoc(group_pk));
374                 groups.add(user_group);
375             }
376         } catch (SQLException e) {
377             throw e;
378         } finally {
379             try {
380                 if (rs != null) rs.close();
381             } catch (SQLException x) { throw x; }
382             try {
383                 if (stmt != null) stmt.close();
384             } catch (SQLException x) { throw x; }
385         }
386
387         return groups;
388     }
389     /**
390      * truncate the whole table
391      *
392      * @exception java.sql.SQLException
393      */

394     public final void truncate()
395         throws SQLException {
396
397         String JavaDoc query = "truncate table user_group_member";
398    
399         Statement stmt = null;
400         try {
401             stmt = con.createStatement();
402             if (Prefs.DEBUG) LogWrite.write(query);
403             stmt.executeUpdate(query);
404         } catch (SQLException e) {
405             throw e;
406         } finally {
407             try { if (stmt != null) stmt.close();
408             } catch (SQLException x) { }
409         }
410     }
411
412     /**
413      * select all user group member rows
414      *
415      * @param lastSyncDate a user's last sync date used to limit
416      * the query, or null if no limit is to be applied
417      * @return the UserGroupMember row(s) that were selected
418      * @exception java.sql.SQLException
419      */

420     public final ArrayList JavaDoc selectAllRows(java.util.Date JavaDoc lastSyncDate)
421         throws SQLException {
422
423         ArrayList JavaDoc rows = new ArrayList JavaDoc();
424         Statement stmt = null;
425         ResultSet rs = null;
426         StringBuffer JavaDoc query = new StringBuffer JavaDoc();
427         query.append(selectAllQuery);
428         if (lastSyncDate != null) {
429             query.append(" WHERE user_group_member.modified_date > ");
430             query.append(DateUtils.formatDateTime(DB_TYPE, lastSyncDate));
431         }
432
433         try {
434             stmt = con.createStatement();
435             if (Prefs.DEBUG) LogWrite.write(query.toString());
436             rs = stmt.executeQuery(query.toString());
437
438             UserGroupMember ugm;
439
440             while (rs.next()) {
441                 ugm = new UserGroupMember();
442
443                 ugm.setPK(rs.getLong(1));
444                 ugm.setUserGroupPK(rs.getLong(2));
445                 ugm.setUserPK(rs.getLong(3));
446                 ugm.setModifiedBy(rs.getString(4));
447                 ugm.setModifiedDate(rs.getDate(5));
448
449                 rows.add(ugm);
450             }
451         } catch (SQLException e) {
452             throw e;
453         } finally {
454             try {
455                 if (rs != null) rs.close();
456             } catch (SQLException x) { throw x; }
457             try {
458                 if (stmt != null) stmt.close();
459             } catch (SQLException x) { throw x; }
460         }
461
462         return rows;
463     }
464
465 }
466
Popular Tags