UserRoleDB


1   package sellwin.db;
2   
3   import sellwin.domain.*;
4   import sellwin.utils.*;
5   
6   import java.sql.*;
7   import java.util.ArrayList  ;
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 UserRole class objects which are stored
16   * in the user_role database table.
17   */
18  public class UserRoleDB extends DBType implements DBInterface {
19      private Connection con;
20      private final static String   deleteByNameQuery =
21          "DELETE FROM user_role WHERE name = ";
22      private final static String   deleteQuery =
23          "DELETE FROM user_role WHERE pk = ";
24      private final static String   deleteAllQuery =
25          "DELETE FROM user_role";
26      private final static String   insertQuery =
27          "INSERT INTO user_role " +
28          "VALUES (";
29      private final static String   updateQuery = 
30              "UPDATE user_role " +
31              "SET ";
32      private final static String   selectAllQuery =
33              "SELECT pk, name, "+
34              "modified_by, modified_date " +
35              "FROM user_role ";
36      private final static String   selectQuery =
37              "SELECT name, "+
38              "modified_by, modified_date " +
39              "FROM user_role "+
40              "WHERE pk = ";
41  
42      private final static String   selectByNameQuery =
43              "SELECT pk, "+
44              "modified_by, modified_date " +
45              "FROM user_role "+
46              "WHERE name = ";
47  
48      /**
49       * a do-nothing constructor but necessary to
50       * do the operations offered by this class
51       *
52       */
53      public UserRoleDB() {
54      }
55  
56      /**
57       * construct, specifying a particular db type
58       */
59      public UserRoleDB(int dbType) {
60          DB_TYPE = dbType;
61      }
62      
63  
64      /**
65       * a version of the constructor when a connection
66       * is already obtained from somewhere else
67       *
68       * @param con the Connection to use 
69       */
70      public UserRoleDB(Connection con) {
71          this.con = con;
72      }
73  
74      /**
75       * return the Connection in use
76       *
77       * @return the Connection in use
78       */
79      public Connection getConnection() {
80          return this.con;
81      }
82  
83      /**
84       * set the Connection to use 
85       *
86       * @param con the Connection to use for any future IO's
87       */
88      public final void setConnection(Connection con) 
89          throws SQLException {
90  
91          this.con = con;
92      }
93  
94      /**
95       * select a single user_role row using the passed
96       * primary key
97       * 
98       * @param pk the primary key to search with
99       * @return the UserRole row(s) that were selected
100      * @exception java.sql.SQLException 
101      */
102     public final Object   selectRow(Object   pk) 
103         throws SQLException {
104 
105         UserRole role = new UserRole("");
106         role.setPK(((Long  )pk).longValue());
107 
108         Statement stmt = null;
109         ResultSet rs = null;
110         String   query = selectQuery + role.getPK();
111 
112         try {
113 
114             RolePermissionDB rolePermDB = new RolePermissionDB(DB_TYPE);
115             rolePermDB.setConnection(getConnection());
116 
117             stmt = con.createStatement();
118             if (Prefs.DEBUG) LogWrite.write(query);
119             rs = stmt.executeQuery(query);
120 
121             int i;
122 
123             while (rs.next()) {
124                 i=1;
125                 role.setName(rs.getString(i)); i++;
126                 role.setModifiedBy(rs.getString(i)); i++;
127                 role.setModifiedDate(rs.getDate(i)); 
128                 rolePermDB.selectRows(role.getPK(), role.getPermissions());
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 role;
143     }
144 
145 
146     /**
147      * update a single user_role row using the passed
148      * UserRole 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   obj) 
156         throws SQLException {
157 
158         UserRole user_role = (UserRole)obj;
159 
160         StringBuffer   query = new StringBuffer  (updateQuery);
161 
162         Statement stmt = con.createStatement();
163 
164         query.append("name=");
165         query.append(JDBC.quoteMore(user_role.getName()));
166         query.append("modified_by=");
167         query.append(JDBC.quoteMore(user_role.getModifiedBy()));
168         query.append("modified_date=");
169         if (DB_TYPE == Prefs.MYSQL)
170             query.append("CURRENT_DATE ");
171         else
172             query.append("SYSDATE ");
173         query.append("WHERE pk = ").append(user_role.getPK());
174 
175         if (Prefs.DEBUG) LogWrite.write(query.toString());
176         int updatedRows = stmt.executeUpdate(query.toString());
177     }
178 
179     /**
180      * insert a new user_role row using the passed
181      * UserRole object as the column values.
182      * 
183      * @param obj the object we are inserting
184      * @param load true if loading, false if adding
185      * @return the newly assigned primary key of the new row
186      * @exception java.sql.SQLException 
187      */
188     public final long insertRow(Object   obj, boolean load) 
189         throws SQLException {
190 
191         UserRole user_role = (UserRole)obj;
192 
193         if (!load)
194             user_role.setPK(DBUtils.generatePK());
195 
196         StringBuffer   query = new StringBuffer  (insertQuery);
197         Statement stmt = con.createStatement();
198 
199         query.append(user_role.getPK()).append(",");
200         query.append(JDBC.quoteMore(user_role.getName()));
201         query.append(JDBC.quoteMore(user_role.getModifiedBy()));
202         if (DB_TYPE == Prefs.MYSQL)
203             query.append("CURRENT_DATE");
204         else
205             query.append("SYSDATE");
206         query.append(")");
207 
208         if (Prefs.DEBUG) LogWrite.write(query.toString());
209         int rc = stmt.executeUpdate(query.toString());
210 
211         return user_role.getPK();
212     }
213 
214     /**
215      * delete a single user_role row using the passed
216      * primary key value
217      *
218      * @param ojb primary key stored in a Long
219      * @exception java.sql.SQLException 
220      */
221     public final void deleteRow(Object   obj) 
222         throws SQLException {
223 
224         long pkValue = ((Long  )obj).longValue();
225 
226         String   query = deleteQuery + pkValue;
227 
228         Statement stmt = null;
229 
230         try {
231             stmt = con.createStatement();
232             if (Prefs.DEBUG) LogWrite.write(query);
233             stmt.executeUpdate(query);
234         } catch (SQLException e) {
235             throw e;
236         } finally {
237             try {
238                 if (stmt != null) stmt.close();
239             } catch (SQLException x) { }
240         }
241     }
242 
243     /**
244      * select all user_role rows
245      * 
246      * @param lastSyncDate a user's last sync date to compare against,
247      * if value is null, then don't use it
248      * @return the UserRole row(s) that were selected
249      * @exception java.sql.SQLException 
250      */
251     public final ArrayList   selectAllRows(java.util.Date   lastSyncDate) 
252         throws SQLException {
253 
254         ArrayList   roles = new ArrayList  ();
255         Statement stmt = null;
256         ResultSet rs = null;
257         StringBuffer   query = new StringBuffer  ();
258         query.append(selectAllQuery);
259         if (lastSyncDate != null) {
260             query.append(" WHERE user_role.modified_date > ");
261             query.append(DateUtils.formatDateTime(DB_TYPE, lastSyncDate));
262         }
263 
264         query.append(" ORDER BY name");
265 
266         try {
267             stmt = con.createStatement();
268             if (Prefs.DEBUG) LogWrite.write(query.toString());
269             rs = stmt.executeQuery(query.toString());
270 
271             int i;
272             UserRole ur = null;
273 
274             while (rs.next()) {
275                 i=1;
276                 ur = new UserRole("");
277                 ur.setPK(rs.getLong(i)); i++;
278                 ur.setName(rs.getString(i)); i++;
279                 ur.setModifiedBy(rs.getString(i)); i++;
280                 ur.setModifiedDate(rs.getDate(i)); 
281                 roles.add(ur);
282             }
283 
284         } catch (SQLException e) {
285             throw e;
286         } finally {
287             try {
288                 if (rs != null) rs.close();
289             } catch (SQLException x) { throw x; }
290             try {
291                 if (stmt != null) stmt.close();
292             } catch (SQLException x) { throw x; }
293         }
294 
295         return roles;
296     }
297 
298     /**
299      * delete a single user_role row using the passed
300      * name key
301      *
302      * @param name the name of the UserRole we are deleting 
303      * @exception java.sql.SQLException 
304      */
305     public final void deleteRowByName(String   name) 
306         throws SQLException {
307 
308         String   query = deleteByNameQuery + "'" + name + "'";
309 
310         Statement stmt = null;
311 
312         try {
313             stmt = con.createStatement();
314             if (Prefs.DEBUG) LogWrite.write(query);
315             stmt.executeUpdate(query);
316         } catch (SQLException e) {
317             throw e;
318         } finally {
319             try {
320                 if (stmt != null) stmt.close();
321             } catch (SQLException x) { }
322         }
323     }
324 
325     /**
326      * select a single user_role row using the passed
327      * primary key
328      * 
329      * @param name the name of the UserRole we are selecting
330      * @return the UserRole row(s) that were selected
331      * @exception java.sql.SQLException 
332      */
333     public final UserRole selectRowByName(String   name) 
334         throws SQLException {
335 
336         UserRole ur = new UserRole("");
337         ur.setName(name);
338 
339         Statement stmt = null;
340         ResultSet rs = null;
341         String   query = selectByNameQuery + "'" + name + "'";
342 
343         try {
344             stmt = con.createStatement();
345             if (Prefs.DEBUG) LogWrite.write(query);
346             rs = stmt.executeQuery(query);
347 
348             int i;
349 
350             while (rs.next()) {
351                 i=1;
352                 ur.setPK(rs.getLong(i)); i++;
353                 ur.setModifiedBy(rs.getString(i)); i++;
354                 ur.setModifiedDate(rs.getDate(i)); 
355             }
356         } catch (SQLException e) {
357             throw e;
358         } finally {
359             try {
360                 if (rs != null) rs.close();
361             } catch (SQLException x) { throw x; }
362             try {
363                 if (stmt != null) stmt.close();
364             } catch (SQLException x) { throw x; }
365         }
366 
367         return ur;
368     }
369 
370     /**
371      * delete all user_role rows 
372      *
373      * @exception java.sql.SQLException 
374      */
375     public final void deleteAllRows() 
376         throws SQLException {
377 
378         String   query = deleteAllQuery;
379 
380         Statement stmt = null;
381 
382         try {
383             stmt = con.createStatement();
384             if (Prefs.DEBUG) LogWrite.write(query);
385             stmt.executeUpdate(query);
386         } catch (SQLException e) {
387             throw e;
388         } finally {
389             try {
390                 if (stmt != null) stmt.close();
391             } catch (SQLException x) { }
392         }
393     }
394     /**
395      * truncate the whole table
396      *
397      * @exception java.sql.SQLException
398      */
399     public final void truncate()
400         throws SQLException {
401 
402         String   query = "truncate table user_role";
403    
404         Statement stmt = null;
405         try {
406             stmt = con.createStatement();
407             if (Prefs.DEBUG) LogWrite.write(query);
408             stmt.executeUpdate(query);
409         } catch (SQLException e) {
410             throw e;
411         } finally {
412             try { if (stmt != null) stmt.close();
413             } catch (SQLException x) { }
414         }
415     }
416 
417 }
418
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags