CustomerDB


1   package sellwin.db;
2   
3   import sellwin.domain.*;
4   import sellwin.utils.*;
5   
6   import java.util.*;
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  /**
15   * This  class implements the DBInterface for
16   * the Customer class objects which are stored
17   * in the customer database table.
18   */
19  public class CustomerDB extends DBType implements DBInterface {
20  
21      private Connection con;
22  
23      private final static String   selectNamesQuery = 
24              "SELECT " +
25                  "name " +
26              "FROM customer " +
27              "ORDER BY name";
28  
29      private final static String   selectByNameQuery = 
30              "SELECT " +
31                  "pk, ship_address_pk, bill_address_pk, " +
32                  "annual_sales, industry, channel ," +
33                  "contact_pk, modified_by, modified_date " +
34              "FROM customer " +
35              "WHERE name=";
36  
37      private final static String   selectQuery = 
38              "SELECT " +
39                  "name, ship_address_pk, bill_address_pk, " +
40                  "annual_sales, industry, channel ," +
41                  "contact_pk, modified_by, modified_date " +
42              "FROM customer " +
43              "WHERE pk=";
44  
45      private final static String   selectAllCustsQuery = 
46              "SELECT " +
47                  "pk, name, ship_address_pk, bill_address_pk, " +
48                  "annual_sales, industry, channel ," +
49                  "contact_pk, modified_by, modified_date " +
50              "FROM customer " ;
51  
52      private final static String   updateQuery = 
53              "UPDATE customer " +
54              "SET " ;
55  
56      private final static String   insertQuery = 
57              "INSERT INTO customer VALUES ( ";
58  
59      private final static String   deleteQuery = 
60              "DELETE FROM customer WHERE pk =";
61  
62      /**
63       * a do-nothing constructor but necessary to
64       * do the operations offered by this class
65       *
66       */
67      public CustomerDB() {
68      }
69  
70      /**
71       * construct specifying a particular db type
72       * @param dbType db type to assume
73       */
74      public CustomerDB(int dbType) {
75          DB_TYPE = dbType;
76      }
77  
78      /**
79       * a constructor that accepts an existing Connection
80       * to use for future operations
81       *
82       * @param con the Connection to use
83       */
84      public CustomerDB(Connection con) {
85          this.con = con;
86      }
87  
88      /**
89       * get the Connection in use
90       *
91       * @return the Connection in use
92       */
93      public Connection getConnection() {
94          return this.con;
95      }
96  
97      /**
98       * set the Connection to use 
99       *
100      * @param con the Connection to use for any future IO's
101      */
102     public void setConnection(Connection con) 
103         throws SQLException {
104 
105         this.con = con;
106     }
107 
108     /**
109      * select a single address row using the passed
110      * primary key
111      * 
112      * @param pk the primary key to search with
113      * @return the row that was selected
114      * @exception java.sql.SQLException 
115      */
116     public final Object   selectRow(Object   pk) 
117         throws SQLException {
118 
119         Customer cust = new Customer();
120         long pkValue = ((Long  )pk).longValue();
121         cust.setPK(pkValue);
122         Statement stmt = null;
123         ResultSet rs = null;
124         String   query = selectQuery + pkValue;
125 
126         try {
127             stmt = con.createStatement();
128             if (Prefs.DEBUG) LogWrite.write(query);
129             rs = stmt.executeQuery(query);
130 
131             int i;
132             long shipAddressPK, billAddressPK, contactAddressPK;
133 
134             AddressDB addrDB = new AddressDB(DB_TYPE);
135             addrDB.setConnection(getConnection());
136             Address shipAddress, billAddress, contactAddress;
137 
138             while (rs.next()) {
139                 i=1;
140                 cust.setName(rs.getString(i)); i++;
141                 shipAddressPK = rs.getLong(i); i++; //ship address
142                 shipAddress = (Address)addrDB.selectRow(new Long  (shipAddressPK));
143                 cust.setShipAddress(shipAddress);
144                 billAddressPK = rs.getLong(i); i++; //bill address
145                 billAddress = (Address)addrDB.selectRow(new Long  (billAddressPK));
146                 cust.setBillAddress(billAddress);
147                 cust.setAnnualSales(rs.getInt(i)); i++;
148                 cust.setIndustry(rs.getString(i)); i++;
149                 cust.setChannel(rs.getString(i)); i++;
150                 contactAddressPK = rs.getLong(i); i++; //contact address
151                 contactAddress = (Address)addrDB.selectRow(new Long  (contactAddressPK));
152                 cust.setContact(contactAddress);
153                 cust.setModifiedBy(rs.getString(i)); i++;
154                 cust.setModifiedDate(rs.getDate(i)); 
155             }
156         } catch (SQLException e) {
157             throw e;
158         } finally {
159             try {
160                 if (rs != null) rs.close();
161             } catch (SQLException x) { throw x; }
162             try {
163                 if (stmt != null) stmt.close();
164             } catch (SQLException x) { throw x; }
165         }
166 
167         return cust;
168     }
169 
170     /**
171      * select all customer rows 
172      * 
173      * @param lastSyncDate a user's last sync date used to limit
174      * the query or null if no limit
175      * @return the Customer(s) that were selected
176      * @exception java.sql.SQLException 
177      */
178     public final ArrayList selectAllRows(java.util.Date   lastSyncDate) 
179         throws SQLException {
180 
181         ArrayList custs = new ArrayList();
182         Customer cust = new Customer();
183         Statement stmt = null;
184         ResultSet rs = null;
185         StringBuffer   query = new StringBuffer  ();
186         query.append(selectAllCustsQuery);
187 
188         if (lastSyncDate != null) {
189             query.append(" WHERE customer.modified_date > ");
190             query.append(DateUtils.formatDateTime(DB_TYPE, lastSyncDate));
191         }
192 
193         query.append(" ORDER BY name");
194 
195         try {
196             stmt = con.createStatement();
197             if (Prefs.DEBUG) LogWrite.write(query.toString());
198             rs = stmt.executeQuery(query.toString());
199 
200             int i;
201             long shipAddressPK, billAddressPK, contactAddressPK;
202 
203             AddressDB addrDB = new AddressDB(DB_TYPE);
204             addrDB.setConnection(getConnection());
205             Address shipAddress, billAddress, contactAddress;
206 
207             while (rs.next()) {
208                 i=1;
209                 cust = new Customer();
210                 cust.setPK(rs.getLong(i)); i++;
211                 cust.setName(rs.getString(i)); i++;
212                 shipAddressPK = rs.getLong(i); i++; //ship address
213                 shipAddress = (Address)addrDB.selectRow(new Long  (shipAddressPK));
214                 cust.setShipAddress(shipAddress);
215                 billAddressPK = rs.getLong(i); i++; //bill address
216                 billAddress = (Address)addrDB.selectRow(new Long  (billAddressPK));
217                 cust.setBillAddress(billAddress);
218                 cust.setAnnualSales(rs.getInt(i)); i++;
219                 cust.setIndustry(rs.getString(i)); i++;
220                 cust.setChannel(rs.getString(i)); i++;
221                 contactAddressPK = rs.getLong(i); i++; //contact address
222                 contactAddress = (Address)addrDB.selectRow(new Long  (contactAddressPK));
223                 cust.setContact(contactAddress);
224                 cust.setModifiedBy(rs.getString(i)); i++;
225                 cust.setModifiedDate(rs.getDate(i)); 
226 
227                 custs.add(cust);
228             }
229         } catch (SQLException e) {
230             throw e;
231         } finally {
232             try {
233                 if (rs != null) rs.close();
234             } catch (SQLException x) { throw x; }
235             try {
236                 if (stmt != null) stmt.close();
237             } catch (SQLException x) { throw x; }
238         }
239 
240         return custs;
241     }
242 
243 
244     /**
245      * update a single address row using the passed
246      * Address object's attributes.  All columns
247      * get updated by this routine regardless of whether
248      * an attribute was modified or not.
249      *
250      * @param obj the object to update with
251      * @exception java.sql.SQLException 
252      */
253     public void updateRow(Object   obj) 
254         throws SQLException {
255 
256         Customer cust = (Customer)obj;
257 
258         Statement stmt = null;
259 
260         try {
261 
262             //update all the children hanging off this customer first
263             AddressDB addrDB = new AddressDB(DB_TYPE);
264             addrDB.setConnection(getConnection());
265 
266             Address contactAddress = cust.getContact();
267             if (contactAddress.isModified())
268                 addrDB.updateRow(contactAddress);
269             Address shipAddress = cust.getShipAddress();
270             if (shipAddress.isModified())
271                 addrDB.updateRow(shipAddress);
272             Address billAddress = cust.getBillAddress();
273             if (billAddress.isModified())
274                 addrDB.updateRow(billAddress);
275 
276             //update the customer's inventory
277             CustomerInventoryDB custInventoryDB = new CustomerInventoryDB(DB_TYPE);
278             custInventoryDB.setConnection(getConnection());
279             ArrayList inventory = cust.getInventory();
280             CustomerInventory ci;
281             for (int i=0;i<inventory.size();i++) {
282                 ci = (CustomerInventory)inventory.get(i);
283                 if (ci.getPK() < 1) {
284                     custInventoryDB.insertRow(ci, false);   
285                 } 
286             }
287 
288             //now preceed to update the customer itself
289             StringBuffer   query = new StringBuffer  (updateQuery);
290 
291             stmt = con.createStatement();
292 
293             query.append("name=");
294             query.append(JDBC.quoteMore(cust.getName()));
295             query.append("annual_sales=");
296             query.append(cust.getAnnualSales()).append(",");
297             query.append("industry=");
298             query.append(JDBC.quoteMore(cust.getIndustry()));
299             query.append("channel=");
300             query.append(JDBC.quote(cust.getChannel()));
301             query.append(" WHERE pk=");
302             query.append(cust.getPK()); 
303 
304             if (Prefs.DEBUG) LogWrite.write(query.toString());
305             int updatedRows = stmt.executeUpdate(query.toString());
306         } catch (SQLException e) {
307             throw e;
308         } finally {
309             try { if (stmt != null) stmt.close(); 
310             } catch (SQLException x) { }
311         }
312     }
313 
314     /**
315      * insert a new customer row using the passed
316      * Customer object as the column values.
317      * 
318      * @param obj the object we are going to insert
319      * @param load true if the row is not new, false if the row
320      * is new
321      * @return the newly assigned primary key of the new row
322      * @exception java.sql.SQLException 
323      */
324     public long insertRow(Object   obj, boolean load) 
325         throws SQLException {
326 
327         Customer cust = (Customer)obj;
328 
329         if (!load)
330             cust.setPK(DBUtils.generatePK());
331 
332         Statement stmt = null;
333 
334         try {
335             AddressDB addrDB = new AddressDB(DB_TYPE);
336             addrDB.setConnection(getConnection());
337             cust.getShipAddress().setPK(addrDB.insertRow(cust.getShipAddress(), load));
338             LogWrite.write("here pk=" + cust.getShipAddress().getPK());
339 
340             cust.getBillAddress().setPK(cust.getShipAddress().getPK() + 1);
341 
342             cust.getBillAddress().setPK(addrDB.insertRow(cust.getBillAddress(), true));
343             LogWrite.write("here2 pk=" + cust.getBillAddress().getPK());
344 
345             cust.getContact().setPK(cust.getBillAddress().getPK() + 1);
346 
347             long newkey = addrDB.insertRow(cust.getContact(), true);
348 
349             cust.getContact().setPK(newkey);
350             LogWrite.write("here3 pk=" + cust.getContact().getPK());
351 
352             StringBuffer   query = new StringBuffer  (insertQuery);
353             stmt = con.createStatement();
354 
355             query.append(cust.getPK()).append(",");
356             query.append(JDBC.quoteMore(cust.getName()));
357             query.append(cust.getShipAddress().getPK()).append(",");
358             query.append(cust.getBillAddress().getPK()).append(",");
359             query.append(cust.getAnnualSales()).append(",");
360             query.append(JDBC.quoteMore(cust.getIndustry()));
361             query.append(JDBC.quoteMore(cust.getChannel()));
362             query.append(cust.getContact().getPK()).append(",");
363             query.append(JDBC.quoteMore(cust.getModifiedBy()));
364             query.append(JDBC.quote(DateUtils.format(DB_TYPE, cust.getModifiedDate())));
365             query.append(")");
366 
367             if (Prefs.DEBUG) LogWrite.write(query.toString());
368             int rc = stmt.executeUpdate(query.toString());
369         } catch (SQLException e) {
370             throw e;
371         } finally {
372             try { if (stmt != null) stmt.close();
373             } catch (SQLException x) { }
374         }
375 
376         return cust.getPK();
377     }
378 
379     /**
380      * delete a single address row using the passed
381      * primary key value
382      *
383      * @param ojb Customer to delete
384      * @exception java.sql.SQLException 
385      */
386     public final void deleteRow(Object   obj) 
387         throws SQLException {
388 
389         Customer cust = (Customer)obj;
390 
391         String   query = deleteQuery + cust.getPK();
392     
393         Statement stmt = null;
394         try {
395             stmt = con.createStatement();
396             if (Prefs.DEBUG) LogWrite.write(query);
397             stmt.executeUpdate(query);
398 
399             //delete the children 
400             AddressDB addr = new AddressDB(DB_TYPE);
401             addr.setConnection(getConnection());
402             addr.deleteRow(new Long  (cust.getContact().getPK()));
403             addr.deleteRow(new Long  (cust.getShipAddress().getPK()));
404             addr.deleteRow(new Long  (cust.getBillAddress().getPK()));
405         } catch (SQLException e) {
406             throw e;
407         } finally {
408             try { if (stmt != null) stmt.close();
409             } catch (SQLException x) { }        
410         }
411     }
412 
413     /**
414      * delete a single address row using the passed
415      * name key
416      *
417      * @param name the customer name used as a key
418      * @exception java.sql.SQLException 
419      */
420     public final void deleteByNameRow(String   name) 
421         throws SQLException {
422 
423         Statement stmt = null;
424 
425         try {
426             Customer cust = selectByNameRow(name);
427 
428             deleteRow(new Long  (cust.getPK()));
429         } catch (SQLException e) {
430             throw e;
431         } finally {
432             try { if (stmt != null) stmt.close();
433             } catch (SQLException x) { }        
434         }
435     }
436 
437     /**
438      * select a single address row using the passed
439      * name as the key
440      * 
441      * @param name the name to search with
442      * @return the Customer that was selected
443      * @exception java.sql.SQLException 
444      */
445     public final Customer selectByNameRow(String   name) 
446         throws SQLException {
447 
448         Customer cust = new Customer();
449         cust.setName(name);
450         Statement stmt = null;
451         ResultSet rs = null;
452         String   query = selectByNameQuery + "'" + name + "'";
453 
454         try {
455             stmt = con.createStatement();
456             if (Prefs.DEBUG) LogWrite.write(query);
457             rs = stmt.executeQuery(query);
458 
459             int i;
460             long shipAddressPK, billAddressPK, contactAddressPK;
461 
462             AddressDB addrDB = new AddressDB(DB_TYPE);
463             addrDB.setConnection(getConnection());
464             Address shipAddress, billAddress, contactAddress;
465 
466             while (rs.next()) {
467                 i=1;
468                 cust.setPK(rs.getLong(i)); i++;
469                 shipAddressPK = rs.getLong(i); i++; //ship address
470                 shipAddress = (Address)addrDB.selectRow(new Long  (shipAddressPK));
471                 cust.setShipAddress(shipAddress);
472                 billAddressPK = rs.getLong(i); i++; //bill address
473                 billAddress = (Address)addrDB.selectRow(new Long  (billAddressPK));
474                 cust.setBillAddress(billAddress);
475                 cust.setAnnualSales(rs.getInt(i)); i++;
476                 cust.setIndustry(rs.getString(i)); i++;
477                 cust.setChannel(rs.getString(i)); i++;
478                 contactAddressPK = rs.getLong(i); i++; //contact address
479                 contactAddress = (Address)addrDB.selectRow(new Long  (contactAddressPK));
480                 cust.setContact(contactAddress);
481                 cust.setModifiedBy(rs.getString(i)); i++;
482                 cust.setModifiedDate(rs.getDate(i)); 
483             }
484         } catch (SQLException e) {
485             throw e;
486         } finally {
487             try {
488                 if (rs != null) rs.close();
489             } catch (SQLException x) { throw x; }
490             try {
491                 if (stmt != null) stmt.close();
492             } catch (SQLException x) { throw x; }
493         }
494 
495         return cust;
496     }
497 
498     /**
499      * select all the customer names
500      * 
501      * @return the names(s) that were selected
502      * @exception java.sql.SQLException 
503      */
504     public final TreeMap selectAllNames() 
505         throws SQLException {
506 
507         TreeMap names = new TreeMap();
508         Statement stmt = null;
509         ResultSet rs = null;
510         String   query = selectNamesQuery;
511 
512         try {
513             stmt = con.createStatement();
514             if (Prefs.DEBUG) LogWrite.write(query);
515             rs = stmt.executeQuery(query);
516 
517             while (rs.next()) {
518                 names.put(rs.getString(1), rs.getString(1)); 
519             }
520         } catch (SQLException e) {
521             throw e;
522         } finally {
523             try {
524                 if (rs != null) rs.close();
525             } catch (SQLException x) { throw x; }
526             try {
527                 if (stmt != null) stmt.close();
528             } catch (SQLException x) { throw x; }
529         }
530 
531         return names;
532     }
533     /**
534      * truncate the whole table
535      *
536      * @exception java.sql.SQLException
537      */
538     public final void truncate()
539         throws SQLException {
540 
541         String   query = "truncate table customer";
542    
543         Statement stmt = null;
544         try {
545             stmt = con.createStatement();
546             if (Prefs.DEBUG) LogWrite.write(query);
547             stmt.executeUpdate(query);
548         } catch (SQLException e) {
549             throw e;
550         } finally {
551             try { if (stmt != null) stmt.close();
552             } catch (SQLException x) { }
553         }
554     }
555 
556 }
557
A to Z: JavaDoc & Examples Daily Java News & Articles Open Source Projects Open Source Codes Free Computer Books Remove Frame
Popular Tags