KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > sellwin > db > SalesPersonRoleDB


1 package sellwin.db;
2
3 import sellwin.domain.*;
4 import sellwin.utils.*;
5
6 import java.util.ArrayList JavaDoc;
7 import java.sql.*;
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 SalesPersonRole class objects which are stored
16  * in the sales_person_role database table.
17  */
18 public class SalesPersonRoleDB extends DBType implements DBInterface {
19
20     private Connection con;
21
22     private final static String JavaDoc deleteQuery =
23         "DELETE FROM sales_person_role WHERE pk = ";
24
25     private final static String JavaDoc deleteSalesPersonQuery=
26         "DELETE FROM sales_person_role WHERE sp_pk=";
27
28     private final static String JavaDoc insertQuery =
29         "INSERT INTO sales_person_role " +
30         "VALUES (";
31
32     private final static String JavaDoc selectQuery =
33             "SELECT sp_pk, user_role_pk, " +
34             "modified_by, modified_date " +
35             "FROM sales_person_role " +
36             "WHERE pk = ";
37
38     private final static String JavaDoc selectRolesForUserQuery =
39             "SELECT user_role.pk, " +
40             "user_role.name, user_role.modified_by, " +
41             "user_role.modified_date " +
42             "FROM sales_person_role, user_role " +
43             "WHERE user_role.pk = sales_person_role.user_role_pk AND " +
44             "sales_person_role.sp_pk = ";
45
46     /**
47      * a do-nothing constructor but necessary to
48      * do the operations offered by this class
49      */
50     public SalesPersonRoleDB() {
51     }
52
53     /**
54      * construct using a db type
55      * @param dbType the db type to assume
56      */
57     public SalesPersonRoleDB(int dbType) {
58         DB_TYPE = dbType;
59     }
60
61     /**
62      * a version of the constructor when a connection
63      * is already obtained from somewhere else
64      *
65      * @param con the Connection to use
66      */
67     public SalesPersonRoleDB(Connection con) {
68         this.con = con;
69     }
70
71     /**
72      * return the Connection in use
73      *
74      * @return the Connection in use
75      */
76     public Connection getConnection() {
77         return this.con;
78     }
79
80     /**
81      * set the Connection to use
82      *
83      * @param con the Connection to use for any future IO's
84      */
85     public final void setConnection(Connection con)
86         throws SQLException {
87
88         this.con = con;
89     }
90
91     /**
92      * select a single sales_person_role row using the passed
93      * primary key
94      *
95      * @param pk the primary key to search with
96      * @return the SalesPersonRole row(s) that were selected
97      * @exception java.sql.SQLException
98      */
99     public final Object JavaDoc selectRow(Object JavaDoc pk)
100         throws SQLException {
101
102         SalesPersonRole sp = new SalesPersonRole();
103         sp.setPK(((Long JavaDoc)(pk)).longValue());
104         Statement stmt = null;
105         ResultSet rs = null;
106         String JavaDoc query = selectQuery + sp.getPK();
107
108         try {
109             stmt = con.createStatement();
110             if (Prefs.DEBUG) LogWrite.write(query);
111             rs = stmt.executeQuery(query);
112
113             int i;
114
115             while (rs.next()) {
116                 i=1;
117                 sp.setSalesPersonPK(rs.getLong(i)); i++;
118                 sp.setUserRolePK(rs.getLong(i)); i++;
119                 UserRoleDB udb = new UserRoleDB(DB_TYPE);
120                 udb.setConnection(getConnection());
121                 UserRole u = (UserRole)udb.selectRow(new Long JavaDoc(sp.getUserRolePK()));
122                 sp.setUserRole(u);
123                 sp.setModifiedBy(rs.getString(i)); i++;
124                 sp.setModifiedDate(rs.getDate(i));
125             }
126
127         } catch (SQLException e) {
128             throw e;
129         } finally {
130             try {
131                 if (rs != null) rs.close();
132             } catch (SQLException x) { throw x; }
133             try {
134                 if (stmt != null) stmt.close();
135             } catch (SQLException x) { throw x; }
136         }
137
138         return sp;
139     }
140
141
142     /**
143      * currently not necessary for this table
144      *
145      * @param name description
146      * @exception java.sql.SQLException
147      */
148     public final void updateRow(Object JavaDoc obj)
149         throws SQLException {
150
151         //currently not necessary
152 }
153
154     /**
155      * insert a new sales_person_role row using the passed
156      * SalesPersonRole object as the column values.
157      *
158      * @param obj the object to insert
159      * @param load true if loading, false if adding
160      * @return the newly assigned primary key of the new row
161      * @exception java.sql.SQLException
162      */
163     public final long insertRow(Object JavaDoc obj, boolean load)
164         throws SQLException {
165
166         SalesPersonRole sales_person_role = (SalesPersonRole)obj;
167
168         if (!load)
169             sales_person_role.setPK(DBUtils.generatePK());
170
171         StringBuffer JavaDoc query = new StringBuffer JavaDoc(insertQuery);
172         Statement stmt = con.createStatement();
173
174         int i=1;
175         query.append(sales_person_role.getPK()).append(",");
176         query.append(sales_person_role.getSalesPersonPK()).append(",");
177         query.append(sales_person_role.getUserRolePK()).append(",");
178         query.append(JDBC.quoteMore(sales_person_role.getModifiedBy()));
179         if (DB_TYPE == Prefs.MYSQL)
180             query.append("CURRENT_DATE");
181         else
182             query.append("SYSDATE");
183         query.append(")");
184
185         if (Prefs.DEBUG) LogWrite.write(query.toString());
186         int rc = stmt.executeUpdate(query.toString());
187
188         return sales_person_role.getPK();
189     }
190
191     /**
192      * delete a single sales_person_role row using the passed
193      * primary key value
194      *
195      * @param ojb primary key stored in a Long
196      * @exception java.sql.SQLException
197      */
198     public final void deleteRow(Object JavaDoc obj)
199         throws SQLException {
200
201         long pkValue = ((Long JavaDoc)obj).longValue();
202
203         String JavaDoc query = deleteQuery + pkValue;
204
205         Statement stmt = null;
206
207         try {
208             stmt = con.createStatement();
209             if (Prefs.DEBUG) LogWrite.write(query);
210             stmt.executeUpdate(query);
211         } catch (SQLException e) {
212             throw e;
213         } finally {
214             try {
215                 if (stmt != null) stmt.close();
216             } catch (SQLException x) { }
217         }
218     }
219
220     /**
221      * delete all sales_person_role rows using the passed
222      * sales person primary key value
223      *
224      * @param ojb primary key stored in a Long
225      * @exception java.sql.SQLException
226      */
227     public final void deleteSalesPersonRows(Object JavaDoc obj)
228         throws SQLException {
229
230         long pkValue = ((Long JavaDoc)obj).longValue();
231
232         String JavaDoc query = deleteSalesPersonQuery + pkValue;
233
234         Statement stmt = null;
235
236         try {
237             stmt = con.createStatement();
238             if (Prefs.DEBUG) LogWrite.write(query);
239             stmt.executeUpdate(query);
240         } catch (SQLException e) {
241             throw e;
242         } finally {
243             try {
244                 if (stmt != null) stmt.close();
245             } catch (SQLException x) { }
246         }
247     }
248
249     /**
250      * select all user_role rows using the passed
251      * sales person primary key...this requires a join
252      * of the user_role and sales_person_role tables so
253      * I placed the query here.
254      *
255      * @param spPK the sales person primary key to search with
256      * @param roles an array that gets the search results from
257      * this query
258      * @exception java.sql.SQLException
259      */
260     public final void selectRolesForUser(long spPK, ArrayList JavaDoc roles)
261         throws SQLException {
262
263         Statement stmt = null;
264         ResultSet rs = null;
265         String JavaDoc query = selectRolesForUserQuery + spPK;
266
267         try {
268             stmt = con.createStatement();
269             if (Prefs.DEBUG) LogWrite.write(query);
270             rs = stmt.executeQuery(query);
271
272             int i;
273             UserRole ur;
274             RolePermissionDB rolePermDB = new RolePermissionDB();
275             rolePermDB.setConnection(getConnection());
276
277             while (rs.next()) {
278                 i=1;
279                 ur = new UserRole();
280                 ur.setPK(rs.getLong(i)); i++;
281                 ur.setName(rs.getString(i)); i++;
282                 ur.setModifiedBy(rs.getString(i)); i++;
283                 ur.setModifiedDate(rs.getDate(i));
284
285                 //get the permissions for the new row
286 rolePermDB.selectRows(ur.getPK(), ur.getPermissions());
287                 roles.add(ur);
288             }
289         } catch (SQLException e) {
290             throw e;
291         } finally {
292             try {
293                 if (rs != null) rs.close();
294             } catch (SQLException x) { throw x; }
295             try {
296                 if (stmt != null) stmt.close();
297             } catch (SQLException x) { throw x; }
298         }
299     }
300     /**
301      * truncate the whole table
302      *
303      * @exception java.sql.SQLException
304      */
305     public final void truncate()
306         throws SQLException {
307
308         String JavaDoc query = "truncate table sales_person_role";
309    
310         Statement stmt = null;
311         try {
312             stmt = con.createStatement();
313             if (Prefs.DEBUG) LogWrite.write(query);
314             stmt.executeUpdate(query);
315         } catch (SQLException e) {
316             throw e;
317         } finally {
318             try { if (stmt != null) stmt.close();
319             } catch (SQLException x) { }
320         }
321     }
322
323 }
324
Popular Tags