KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > sellwin > db > CustomerInventoryDB


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 /**
15  * This class implements the DBInterface for
16  * the CustomerInventory class objects which are stored
17  * in the customer_inventory database table.
18  */
19 public class CustomerInventoryDB extends DBType implements DBInterface {
20     private Connection con;
21
22     private final static String JavaDoc deleteQuery =
23         "DELETE FROM customer_inventory WHERE pk = ";
24
25     private final static String JavaDoc insertQuery =
26         "INSERT INTO customer_inventory " +
27         " (PK,CUSTOMER_PK,PRODUCT_PK,CNT,MODIFIED_BY,MODIFIED_DATE) " +
28         "VALUES (";
29
30     private final static String JavaDoc updateQuery =
31             "UPDATE customer_inventory " +
32             "SET ";
33
34     private final static String JavaDoc selectQuery =
35             "SELECT customer_pk, product_pk, cnt, " +
36             "modified_by, modified_date " +
37             "FROM customer_inventory "+
38             "WHERE pk = ";
39     private final static String JavaDoc selectAllQuery =
40             "SELECT customer_pk, product_pk, cnt, " +
41             "modified_by, modified_date " +
42             "FROM customer_inventory "+
43             "WHERE customer_pk = ";
44
45     /**
46      * a do-nothing constructor but necessary to
47      * do the operations offered by this class
48      *
49      */
50     public CustomerInventoryDB() {
51     }
52
53     /**
54      * construct using a db type
55      * @param dbType the db type to assume
56      */
57     public CustomerInventoryDB(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 CustomerInventoryDB(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 customer_inventory row using the passed
93      * primary key
94      *
95      * @param pk the primary key to search with
96      * @return the CustomerInventory 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         CustomerInventory sp = new CustomerInventory();
103         sp.setPK(((Long JavaDoc)pk).longValue());
104
105         Statement stmt = null;
106         ResultSet rs = null;
107         String JavaDoc query = selectQuery + sp.getPK();
108
109         try {
110             stmt = con.createStatement();
111             if (Prefs.DEBUG) LogWrite.write(query);
112             rs = stmt.executeQuery(query);
113
114             int i;
115             long prodPK=0;
116
117             while (rs.next()) {
118                 i=1;
119                 sp.setCustomerPK(rs.getLong(i)); i++;
120
121                 prodPK = rs.getLong(i); i++;
122                 ProductDB pdb = new ProductDB(DB_TYPE);
123                 pdb.setConnection(getConnection());
124                 sp.setProduct((Product)pdb.selectRow(new Long JavaDoc(prodPK)));
125
126                 sp.setCount(new Integer JavaDoc(rs.getInt(i))); i++;
127                 sp.setModifiedBy(rs.getString(i)); i++;
128                 sp.setModifiedDate(rs.getDate(i));
129             }
130         } catch (SQLException e) {
131             throw e;
132         } finally {
133             try {
134                 if (rs != null) rs.close();
135             } catch (SQLException x) { throw x; }
136             try {
137                 if (stmt != null) stmt.close();
138             } catch (SQLException x) { throw x; }
139         }
140
141         return sp;
142     }
143
144
145     /**
146      * update a single customer_inventory row using the passed
147      * CustomerInventory object's attributes. All columns
148      * get updated by this routine regardless of whether
149      * an attribute was modified or not.
150      *
151      * @param obj the object we are updating
152      * @exception java.sql.SQLException
153      */
154     public final void updateRow(Object JavaDoc obj)
155         throws SQLException {
156
157         CustomerInventory customer_inventory = (CustomerInventory)obj;
158
159         StringBuffer JavaDoc query = new StringBuffer JavaDoc(updateQuery);
160         Statement stmt = con.createStatement();
161
162         query.append("cnt=");
163         query.append(customer_inventory.getCount().intValue()).append(",");
164         query.append(" WHERE pk=");
165         query.append(customer_inventory.getPK());
166
167         if (Prefs.DEBUG) LogWrite.write(query.toString());
168         int updatedRows = stmt.executeUpdate(query.toString());
169     }
170
171     /**
172      * insert a new customer_inventory row using the passed
173      * CustomerInventory object as the column values.
174      *
175      * @param obj the object we are inserting
176      * @param load true if to be loaded, false if to be added
177      * @return the newly assigned primary key of the new row
178      * @exception java.sql.SQLException
179      */
180     public final long insertRow(Object JavaDoc obj, boolean load)
181         throws SQLException {
182
183         CustomerInventory customer_inventory = (CustomerInventory)obj;
184
185         //we assume the customer has already been added
186 //we assume the product has already been added
187 //insert the customer_inventory
188 if (!load)
189             customer_inventory.setPK(DBUtils.generatePK());
190
191         Date modified_date = new Date(customer_inventory.getModifiedDate().getTime());
192
193         StringBuffer JavaDoc query = new StringBuffer JavaDoc(insertQuery);
194         Statement stmt = con.createStatement();
195
196         query.append(customer_inventory.getPK()).append(",");
197         query.append(customer_inventory.getCustomerPK()).append(",");
198         query.append(customer_inventory.getProduct().getPK()).append(",");
199         query.append(customer_inventory.getCount().intValue()).append(",");
200         query.append(JDBC.quoteMore(customer_inventory.getModifiedBy()));
201         query.append(JDBC.quote(DateUtils.format(DB_TYPE, customer_inventory.getModifiedDate())));
202         query.append(")");
203
204         if (Prefs.DEBUG) LogWrite.write(query.toString());
205         int rc = stmt.executeUpdate(query.toString());
206
207         return customer_inventory.getPK();
208     }
209
210     /**
211      * delete a single customer_inventory row using the passed
212      * primary key value
213      *
214      * @param ojb primary key stored in a Long
215      * @exception java.sql.SQLException
216      */
217     public final void deleteRow(Object JavaDoc obj)
218         throws SQLException {
219
220         long pkValue = ((Long JavaDoc)obj).longValue();
221
222         String JavaDoc query = deleteQuery + pkValue;
223
224         Statement stmt = null;
225
226         try {
227             stmt = con.createStatement();
228             if (Prefs.DEBUG) LogWrite.write(query);
229             stmt.executeUpdate(query);
230         } catch (SQLException e) {
231             throw e;
232         } finally {
233             try {
234                 if (stmt != null) stmt.close();
235             } catch (SQLException x) { }
236         }
237     }
238
239     /**
240      * select all customer_inventory rows using the passed
241      * customer primary key
242      *
243      * @param custpk the customer primary key to search with
244      * @return an ArrayList of CustomerInventory row(s) that were selected
245      * @exception java.sql.SQLException
246      */
247     public final ArrayList JavaDoc selectAllRows(long custpk)
248         throws SQLException {
249
250         CustomerInventory ci=null;
251         ArrayList JavaDoc rows = new ArrayList JavaDoc();
252
253         Statement stmt = null;
254         ResultSet rs = null;
255         String JavaDoc query = selectAllQuery + custpk;
256
257         try {
258             stmt = con.createStatement();
259             if (Prefs.DEBUG) LogWrite.write(query);
260             rs = stmt.executeQuery(query);
261
262             int i;
263             long prodPK=0;
264
265             while (rs.next()) {
266                 i=1;
267                 ci = new CustomerInventory();
268                 ci.setCustomerPK(rs.getLong(i)); i++;
269
270                 prodPK = rs.getLong(i); i++;
271                 ProductDB pdb = new ProductDB(DB_TYPE);
272                 pdb.setConnection(getConnection());
273                 ci.setProduct((Product)pdb.selectRow(new Long JavaDoc(prodPK)));
274
275                 ci.setCount(new Integer JavaDoc(rs.getInt(i))); i++;
276                 ci.setModifiedBy(rs.getString(i)); i++;
277                 ci.setModifiedDate(rs.getDate(i));
278                 rows.add(ci);
279             }
280         } catch (SQLException e) {
281             throw e;
282         } finally {
283             try {
284                 if (rs != null) rs.close();
285             } catch (SQLException x) { throw x; }
286             try {
287                 if (stmt != null) stmt.close();
288             } catch (SQLException x) { throw x; }
289         }
290
291         return rows;
292     }
293     /**
294      * truncate the whole table
295      *
296      * @exception java.sql.SQLException
297      */
298     public final void truncate()
299         throws SQLException {
300
301         String JavaDoc query = "truncate table customer_inventory";
302    
303         Statement stmt = null;
304         try {
305             stmt = con.createStatement();
306             if (Prefs.DEBUG) LogWrite.write(query);
307             stmt.executeUpdate(query);
308         } catch (SQLException e) {
309             throw e;
310         } finally {
311             try { if (stmt != null) stmt.close();
312             } catch (SQLException x) { }
313         }
314     }
315
316 }
317
Popular Tags