Java > Open Source Codes > sellwin > db > ProductDB


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

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags