ContactDB


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  /**
15   * This class implements the DBInterface for
16   * the Contact class objects which are stored
17   * in the contact database table.
18   */
19  public class ContactDB extends DBType implements DBInterface {
20      private Connection con;
21  
22      private final static String   deleteQuery =
23          "DELETE FROM contact WHERE pk = ";
24  
25      private final static String   deleteOppRowsQuery =
26          "DELETE FROM contact WHERE opp_pk = ";
27  
28      private final static String   insertQuery =
29          "INSERT INTO contact " +
30          "VALUES (";
31  
32      private final static String   selectQuery =
33              "SELECT "+
34              "opp_pk," +
35              "address_pk," +
36              "modified_by," +
37              "modified_date " +
38              "FROM contact "+
39              "WHERE pk = ";
40  
41      private final static String   selectForOppQuery =
42              "SELECT "+
43              "pk," +
44              "opp_pk," +
45              "address_pk," +
46              "modified_by, modified_date " +
47              "FROM contact "+
48              "WHERE opp_pk = ";
49  
50      /**
51       * a do-nothing constructor but necessary to
52       * do the operations offered by this class
53       */
54      public ContactDB() {
55      }
56  
57      /**
58       * construct using a particular db type
59       * @param dbType the db type to assume
60       */
61      public ContactDB(int dbType) {
62          DB_TYPE=dbType;
63      }
64  
65      /**
66       * a version of the constructor when a connection
67       * is already obtained from somewhere else
68       *
69       * @param con the Connection to use 
70       */
71      public ContactDB(Connection con) {
72          this.con = con;
73      }
74  
75      /**
76       * return the Connection in use
77       *
78       * @return the Connection in use
79       */
80      public Connection getConnection() {
81          return this.con;
82      }
83  
84      /**
85       * set the Connection to use 
86       *
87       * @param con the Connection to use for any future IO's
88       */
89      public final void setConnection(Connection con) 
90          throws SQLException {
91  
92          this.con = con;
93      }
94  
95      /**
96       * select a single contact row using the passed
97       * primary key
98       * 
99       * @param pk the primary key we search with
100      * @return the Contact row(s) that were selected
101      * @exception java.sql.SQLException 
102      */
103     public final Object   selectRow(Object   pk) 
104         throws SQLException {
105 
106         Contact contact = new Contact();
107         contact.setPK(((Long  )pk).longValue());
108 
109         Statement stmt = null;
110         ResultSet rs = null;
111         String   query = selectQuery + contact.getPK();
112 
113         try {
114             stmt = con.createStatement();
115             if (Prefs.DEBUG) LogWrite.write(query);
116             rs = stmt.executeQuery(query);
117 
118             int i;
119 
120             while (rs.next()) {
121                 i=1;
122                 contact.setOppKey(rs.getLong(i)); i++;
123                 contact.setAddressKey(rs.getLong(i)); i++;
124                 contact.setModifiedBy(rs.getString(i)); i++;
125                 contact.setModifiedDate(rs.getDate(i)); 
126             }
127 
128             AddressDB addressDB = new AddressDB(DB_TYPE);
129             addressDB.setConnection(getConnection());
130             Address a = (Address)addressDB.selectRow(new Long  (contact.getAddressKey()));
131             contact.setAddress(a);
132 
133         } catch (SQLException e) {
134             throw e;
135         } finally {
136             try {
137                 if (rs != null) rs.close();
138             } catch (SQLException x) { throw x; }
139             try {
140                 if (stmt != null) stmt.close();
141             } catch (SQLException x) { throw x; }
142         }
143 
144         return contact;
145     }
146 
147 
148     /**
149      * not yet coded
150      * @param name description
151      * @exception java.sql.SQLException 
152      */
153     public final void updateRow(Object   obj) 
154         throws SQLException {
155 
156         Contact contact = (Contact)obj;
157 
158         //just update the contact's address, the other attributes
159         //of contact are not worth updating.
160 
161         AddressDB addressDB = new AddressDB(DB_TYPE);
162         addressDB.setConnection(getConnection());
163         addressDB.updateRow(contact.getAddress());
164     }
165 
166     /**
167      * insert a new forecast row using the passed
168      * Contact object as the column values.
169      * 
170      * @param obj the object we are going to insert
171      * @param load true if the row is to be loaded, false if the
172      * row is to be added for the first time
173      * @return the newly assigned primary key of the new row
174      * @exception java.sql.SQLException 
175      */
176     public final long insertRow(Object   obj, boolean load) 
177         throws SQLException {
178 
179         Contact contact = (Contact)obj;
180 
181         if (!load)
182             contact.setPK(DBUtils.generatePK());
183 
184         //insert the contact's address first
185         AddressDB addressDB = new AddressDB(DB_TYPE);
186         addressDB.setConnection(getConnection());
187         contact.getAddress().setModifiedBy(contact.getModifiedBy());
188         contact.getAddress().setPK(addressDB.insertRow(contact.getAddress(), load));
189 
190         StringBuffer   query = new StringBuffer  (insertQuery);
191         Statement stmt = con.createStatement();
192 
193         query.append(contact.getPK()).append(",");
194         query.append(contact.getOppKey()).append(",");
195         query.append(contact.getAddress().getPK()).append(",");
196         query.append(JDBC.quoteMore(contact.getModifiedBy()));
197         query.append(JDBC.quote(DateUtils.format(DB_TYPE, contact.getModifiedDate())));
198         query.append(")");
199 
200         if (Prefs.DEBUG) LogWrite.write(query.toString());
201         int rc = stmt.executeUpdate(query.toString());
202 
203         return contact.getPK();
204     }
205 
206     /**
207      * delete a single forecast row using the passed
208      * primary key value
209      *
210      * @param ojb primary key stored in a Long
211      * @exception java.sql.SQLException 
212      */
213     public final void deleteRow(Object   obj) 
214         throws SQLException {
215 
216         long pkValue = ((Long  )obj).longValue();
217 
218 
219         String   query = deleteQuery + pkValue;
220 
221         Statement stmt = null;
222 
223         try {
224             Contact c = (Contact)selectRow(new Long  (pkValue));
225             stmt = con.createStatement();
226             if (Prefs.DEBUG) LogWrite.write(query);
227             stmt.executeUpdate(query);
228 
229             //delete the child address 
230             AddressDB addressDB = new AddressDB(DB_TYPE);
231             addressDB.setConnection(getConnection());
232             addressDB.deleteRow(new Long  (c.getAddress().getPK()));
233         } catch (SQLException e) {
234             LogWrite.write(e);
235             throw e;
236         } finally {
237             try {
238                 if (stmt != null) stmt.close();
239             } catch (SQLException x) { }
240         }
241     }
242 
243 
244     /**
245      * delete all contact rows using the passed
246      * opportunity primary key value
247      *
248      * @param ojb primary key stored in a Long
249      * @exception java.sql.SQLException 
250      */
251     public final void deleteOpportunityRows(Object   obj) 
252         throws SQLException {
253 
254         long oppPK = ((Long  )obj).longValue();
255 
256         String   query = deleteOppRowsQuery + oppPK;
257 
258         Statement stmt = null;
259 
260         try {
261             //first, go get all the contacts for this opp
262             ArrayList   contacts = new ArrayList  ();
263             selectByOppRow(oppPK, contacts);
264 
265             //second, delete all the contact's Address children
266             AddressDB addressDB = new AddressDB(DB_TYPE);
267             addressDB.setConnection(getConnection());
268             Contact  contact;
269             for (int i=0;i<contacts.size();i++) {
270                 contact = (Contact)contacts.get(i);
271                 addressDB.deleteRow(new Long  (contact.getAddress().getPK()));
272             }
273 
274             //lastly, delete all the contacts for this Opp
275             stmt = con.createStatement();
276             if (Prefs.DEBUG) LogWrite.write(query);
277             stmt.executeUpdate(query);
278         } catch (SQLException e) {
279             LogWrite.write(e);
280             throw e;
281         } finally {
282             try {
283                 if (stmt != null) stmt.close();
284             } catch (SQLException x) { }
285         }
286     }
287 
288     /**
289      * select all forecast rows using the passed
290      * opportunity primary key
291      * 
292      * @param opp_pk the primary key value to search with
293      * @param contacts a list that gets populated with objects found
294      * in the search
295      * @exception java.sql.SQLException 
296      */
297     public final void selectByOppRow(long opp_pk, ArrayList   contacts) 
298         throws SQLException {
299 
300         Contact contact = null;
301 
302         Statement stmt = null;
303         ResultSet rs = null;
304         String   query = selectForOppQuery + opp_pk;
305 
306         try {
307             stmt = con.createStatement();
308 
309             if (Prefs.DEBUG) LogWrite.write(query);
310             rs = stmt.executeQuery(query);
311 
312             int i;
313             long addressPK;
314             AddressDB addressDB = new AddressDB(DB_TYPE);
315             addressDB.setConnection(getConnection());
316 
317             while (rs.next()) {
318                 i=1;
319                 contact = new Contact();
320                 contact.setPK(rs.getLong(i)); i++;
321                 contact.setOppKey(rs.getLong(i)); i++;
322                 addressPK = rs.getLong(i); i++;
323                 contact.setAddress((Address)addressDB.selectRow(new Long  (addressPK)));
324 
325                 contact.setModifiedBy(rs.getString(i)); i++;
326                 contact.setModifiedDate(rs.getDate(i)); 
327                 contacts.add(contact);
328             }
329         } catch (SQLException e) {
330             throw e;
331         } finally {
332             try {
333                 if (stmt != null) stmt.close();
334             } catch (SQLException x) { }
335         }
336     }
337     /**
338      * truncate the whole table
339      *
340      * @exception java.sql.SQLException
341      */
342     public final void truncate()
343         throws SQLException {
344 
345         String   query = "truncate table contact";
346    
347         Statement stmt = null;
348         try {
349             stmt = con.createStatement();
350             if (Prefs.DEBUG) LogWrite.write(query);
351             stmt.executeUpdate(query);
352         } catch (SQLException e) {
353             throw e;
354         } finally {
355             try { if (stmt != null) stmt.close();
356             } catch (SQLException x) { }
357         }
358     }
359 
360 
361 }
362
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags