KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > sellwin > db > QuoteDB


1 package sellwin.db;
2
3 import sellwin.domain.*;
4 import sellwin.utils.*;
5
6 import java.sql.*;
7 import java.util.ArrayList JavaDoc;
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 Quote class objects which are stored
16  * in the quote database table.
17  */
18 public class QuoteDB extends DBType implements DBInterface {
19     private Connection con;
20
21     private final static String JavaDoc deleteOppQuery =
22         "DELETE FROM quote WHERE opp_pk=";
23     private final static String JavaDoc deleteQuery =
24         "DELETE FROM quote WHERE pk = ";
25     private final static String JavaDoc insertQuery =
26         "INSERT INTO quote " +
27         "VALUES (";
28     private final static String JavaDoc updateQuery =
29             "UPDATE quote " +
30             "SET ";
31     private final static String JavaDoc selectQuery =
32             "SELECT opp_pk, "+
33             "name, "+
34             "modified_by, modified_date " +
35             "FROM quote "+
36             "WHERE pk = ";
37     private final static String JavaDoc selectByOppQuery =
38             "SELECT pk, "+
39             "name, "+
40             "modified_by, modified_date " +
41             "FROM quote "+
42             "WHERE opp_pk = ";
43
44     /**
45      * a do-nothing constructor but necessary to
46      * do the operations offered by this class
47      */
48     public QuoteDB() {
49     }
50
51     /**
52      * construct, use a particular db type
53      * @param dbType the db type to use
54      */
55     public QuoteDB(int dbType) {
56         DB_TYPE = dbType;
57     }
58
59     /**
60      * a version of the constructor when a connection
61      * is already obtained from somewhere else
62      *
63      * @param con the Connection to use
64      */
65     public QuoteDB(Connection con) {
66         this.con = con;
67     }
68
69     /**
70      * return the Connection in use
71      *
72      * @return the Connection in use
73      */
74     public Connection getConnection() {
75         return this.con;
76     }
77
78     /**
79      * set the Connection to use
80      *
81      * @param con the Connection to use for any future IO's
82      */
83     public final void setConnection(Connection con)
84         throws SQLException {
85
86         this.con = con;
87     }
88
89     /**
90      * select a single quote row using the passed
91      * primary key
92      *
93      * @param name description
94      * @return the Quote row(s) that were selected
95      * @exception java.sql.SQLException
96      */
97     public final Object JavaDoc selectRow(Object JavaDoc pk)
98         throws SQLException {
99
100         Quote quote = new Quote();
101         quote.setPK(((Long JavaDoc)pk).longValue());
102
103         Statement stmt = null;
104         ResultSet rs = null;
105         String JavaDoc query = selectQuery + quote.getPK();
106
107         try {
108             stmt = con.createStatement();
109             if (Prefs.DEBUG) LogWrite.write(query);
110             rs = stmt.executeQuery(query);
111
112             int i;
113
114             while (rs.next()) {
115                 i=1;
116                 quote.setOppPK(rs.getLong(i)); i++;
117                 quote.setName(rs.getString(i)); i++;
118                 quote.setModifiedBy(rs.getString(i)); i++;
119                 quote.setModifiedDate(rs.getDate(i));
120
121                 QuoteLineDB quoteLineDB = new QuoteLineDB(DB_TYPE);
122                 quoteLineDB.setConnection(getConnection());
123                 quoteLineDB.selectByQuoteRow(quote.getPK(), quote.getLines());
124             }
125
126         } catch (SQLException e) {
127             throw e;
128         } finally {
129             try {
130                 if (rs != null) rs.close();
131             } catch (SQLException x) { throw x; }
132             try {
133                 if (stmt != null) stmt.close();
134             } catch (SQLException x) { throw x; }
135         }
136
137         return quote;
138     }
139
140
141     /**
142      * update a single quote row using the passed
143      * Quote object's attributes. All columns
144      * get updated by this routine regardless of whether
145      * an attribute was modified or not.
146      *
147      * @param name description
148      * @exception java.sql.SQLException
149      */
150     public final void updateRow(Object JavaDoc obj)
151         throws SQLException {
152
153         Quote quote = (Quote)obj;
154
155         StringBuffer JavaDoc query = new StringBuffer JavaDoc(updateQuery);
156         Statement stmt = con.createStatement();
157
158         query.append("name=");
159         query.append(JDBC.quoteMore(quote.getName()));
160         query.append("modified_by=");
161         query.append(JDBC.quoteMore(quote.getModifiedBy()));
162         query.append("modified_date=");
163         if (DB_TYPE == Prefs.MYSQL)
164             query.append("CURRENT_DATE");
165         else
166             query.append("SYSDATE");
167         query.append(" WHERE pk=");
168         query.append(quote.getPK());
169
170         if (Prefs.DEBUG) LogWrite.write(query.toString());
171         int updatedRows = stmt.executeUpdate(query.toString());
172     }
173
174     /**
175      * insert a new quote row using the passed
176      * Quote object as the column values.
177      *
178      * @param obj Quote we are loading or adding
179      * @param load true if we are to load, false if adding
180      * @return the newly assigned primary key of the new row
181      * @exception java.sql.SQLException
182      */
183     public final long insertRow(Object JavaDoc obj, boolean load)
184         throws SQLException {
185
186         Quote quote = (Quote)obj;
187
188         if (!load)
189             quote.setPK(DBUtils.generatePK());
190
191         StringBuffer JavaDoc query = new StringBuffer JavaDoc(insertQuery);
192         Statement stmt = con.createStatement();
193
194         query.append(quote.getPK()).append(",");
195         query.append(quote.getOppPK()).append(",");
196         query.append(JDBC.quoteMore(quote.getName()));
197         query.append(JDBC.quoteMore(quote.getModifiedBy()));
198         if (DB_TYPE == Prefs.MYSQL)
199             query.append("CURRENT_DATE");
200         else
201             query.append("SYSDATE");
202         query.append(")");
203
204         if (Prefs.DEBUG) LogWrite.write(query.toString());
205         int rc = stmt.executeUpdate(query.toString());
206
207         return quote.getPK();
208     }
209
210     /**
211      * delete a single quote 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
228             //delete the quote lines for this quote first
229 QuoteLineDB qlDB = new QuoteLineDB(DB_TYPE);
230             qlDB.setConnection(getConnection());
231             qlDB.deleteQuoteRows(pkValue);
232
233             stmt = con.createStatement();
234             if (Prefs.DEBUG) LogWrite.write(query);
235             stmt.executeUpdate(query);
236         } catch (SQLException e) {
237             LogWrite.write(e);
238             throw e;
239         } finally {
240             try {
241                 if (stmt != null) stmt.close();
242             } catch (SQLException x) { }
243         }
244     }
245
246     /**
247      * delete all quote rows using the passed
248      * opportunity primary key value
249      *
250      * @param ojb primary key stored in a Long
251      * @exception java.sql.SQLException
252      */
253     public final void deleteOpportunityRows(Object JavaDoc obj)
254         throws SQLException {
255
256         long oppPK = ((Long JavaDoc)obj).longValue();
257
258         String JavaDoc query = deleteOppQuery + oppPK;
259
260         Statement stmt = null;
261
262         try {
263             //delete the quote lines for this quote first
264 QuoteLineDB qlDB = new QuoteLineDB(DB_TYPE);
265             qlDB.setConnection(getConnection());
266             qlDB.deleteQuoteRowsByOpp(oppPK);
267
268             stmt = con.createStatement();
269             if (Prefs.DEBUG) LogWrite.write(query);
270             stmt.executeUpdate(query);
271         } catch (SQLException e) {
272             LogWrite.write(e);
273             throw e;
274         } finally {
275             try {
276                 if (stmt != null) stmt.close();
277             } catch (SQLException x) { }
278         }
279     }
280
281     /**
282      * select all quote rows using the passed
283      * opportunity primary key
284      *
285      * @param oppPK the opportunity pk we are querying with
286      * @param quoteList the list we are going to add the quotes to
287      * @exception java.sql.SQLException
288      */
289     public final void selectByOppRow(long opp_pk, ArrayList JavaDoc quotes)
290         throws SQLException {
291
292         Quote q = null;
293
294         Statement stmt = null;
295         ResultSet rs = null;
296         String JavaDoc query = selectByOppQuery + opp_pk;
297
298         if (Prefs.DEBUG) LogWrite.write(query);
299
300         try {
301             stmt = con.createStatement();
302             if (Prefs.DEBUG) LogWrite.write(query);
303             rs = stmt.executeQuery(query);
304
305             int i;
306             QuoteLineDB quoteLineDB = new QuoteLineDB(DB_TYPE);
307             quoteLineDB.setConnection(getConnection());
308
309             while (rs.next()) {
310                 i=1;
311                 q = new Quote();
312                 q.setPK(rs.getLong(i)); i++;
313                 q.setOppPK(opp_pk);
314                 q.setName(rs.getString(i)); i++;
315                 q.setModifiedBy(rs.getString(i)); i++;
316                 q.setModifiedDate(rs.getDate(i));
317                 quoteLineDB.selectByQuoteRow(q.getPK(), q.getLines());
318                 quotes.add(q);
319             }
320             if (Prefs.DEBUG) LogWrite.write("found " + quotes.size() + " for opppk=" + opp_pk);
321         } catch (SQLException e) {
322             throw e;
323         } finally {
324             try {
325                 if (rs != null) rs.close();
326             } catch (SQLException x) { throw x; }
327             try {
328                 if (stmt != null) stmt.close();
329             } catch (SQLException x) { throw x; }
330         }
331
332     }
333     /**
334      * truncate the whole table
335      *
336      * @exception java.sql.SQLException
337      */
338     public final void truncate()
339         throws SQLException {
340
341         String JavaDoc query = "truncate table quote";
342    
343         Statement stmt = null;
344         try {
345             stmt = con.createStatement();
346             if (Prefs.DEBUG) LogWrite.write(query);
347             stmt.executeUpdate(query);
348         } catch (SQLException e) {
349             throw e;
350         } finally {
351             try { if (stmt != null) stmt.close();
352             } catch (SQLException x) { }
353         }
354     }
355
356 }
357
Popular Tags