KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > sellwin > db > CampaignDB


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 /**
15  * This class implements the DBInterface for
16  * the Campaign class objects which are stored
17  * in the campaign database table.
18  */

19 public class CampaignDB extends DBType implements DBInterface {
20     private Connection con;
21
22     private final static String JavaDoc selectAllQuery =
23             "SELECT " +
24                 "pk, name, type, product_group, " +
25                 "product_line, lead_count," +
26                 "modified_by, modified_date " +
27             "FROM campaign";
28
29     private final static String JavaDoc selectQuery =
30             "SELECT " +
31                 "name, type, product_group, " +
32                 "product_line, lead_count," +
33                 "modified_by, modified_date " +
34             "FROM campaign " +
35             "WHERE pk=";
36
37     private final static String JavaDoc updateQuery =
38             "UPDATE campaign " +
39             "SET " ;
40
41     private final static String JavaDoc insertQuery =
42             "INSERT INTO campaign VALUES ( ";
43
44     private final static String JavaDoc deleteQuery =
45             "DELETE FROM campaign WHERE pk =";
46
47     private final static String JavaDoc deleteAllQuery =
48             "DELETE FROM campaign ";
49
50
51     /**
52      * a do-nothing constructor but necessary to
53      * do the operations offered by this class
54      *
55      */

56     public CampaignDB() {
57     }
58
59     /**
60      * construct using a particular db
61      * @param dbType the database type
62      */

63     public CampaignDB(int dbType) {
64         DB_TYPE=dbType;
65     }
66
67     /**
68      * a constructor that accepts an existing Connection
69      * to use for future operations
70      *
71      * @param con the Connection to use
72      */

73     public CampaignDB(Connection con) {
74         this.con = con;
75     }
76
77     /**
78      * get the Connection in use
79      *
80      * @return the Connection in use
81      */

82     public Connection getConnection() {
83         return this.con;
84     }
85
86     /**
87      * set the Connection to use
88      *
89      * @param con the Connection to use for any future IO's
90      */

91     public void setConnection(Connection con)
92         throws SQLException {
93
94         this.con = con;
95     }
96
97     /**
98      * select a single campaign row using the passed
99      * primary key
100      *
101      * @param pk the primary key we are searching with
102      * @return the row(s) that were selected
103      * @exception java.sql.SQLException
104      */

105     public final Object JavaDoc selectRow(Object JavaDoc pk)
106         throws SQLException {
107
108         Campaign campaign = new Campaign();
109         long pkValue = ((Long JavaDoc)pk).longValue();
110         campaign.setPK(pkValue);
111         Statement stmt = null;
112         ResultSet rs = null;
113         String JavaDoc query = selectQuery + pkValue;
114
115         try {
116             stmt = con.createStatement();
117             if (Prefs.DEBUG) LogWrite.write(query);
118             rs = stmt.executeQuery(query);
119
120             int i;
121             while (rs.next()) {
122                 i=1;
123                 campaign.setName(rs.getString(i)); i++;
124                 campaign.setType(rs.getString(i)); i++;
125                 campaign.setProductGroup(rs.getString(i)); i++;
126                 campaign.setProductLine(rs.getString(i)); i++;
127                 campaign.setLeadCount(new Integer JavaDoc(rs.getInt(i))); i++;
128                 campaign.setModifiedBy(rs.getString(i)); i++;
129                 campaign.setModifiedDate(rs.getDate(i));
130             }
131         } catch (SQLException e) {
132             throw e;
133         } finally {
134             try {
135                 if (rs != null) rs.close();
136             } catch (SQLException x) { throw x; }
137             try {
138                 if (stmt != null) stmt.close();
139             } catch (SQLException x) { throw x; }
140         }
141
142         return campaign;
143     }
144
145
146     /**
147      * update a single campaign row using the passed
148      * Campaign object's attributes. All columns
149      * get updated by this routine regardless of whether
150      * an attribute was modified or not.
151      *
152      * @param obj the object we are going to update with
153      * @exception java.sql.SQLException
154      */

155     public void updateRow(Object JavaDoc obj)
156         throws SQLException {
157
158         Campaign campaign = (Campaign)obj;
159         long pk = campaign.getPK();
160
161         Statement stmt = null;
162         StringBuffer JavaDoc query = new StringBuffer JavaDoc(updateQuery);
163
164         try {
165             stmt = con.createStatement();
166
167             query.append("name=");
168             query.append(JDBC.quoteMore(campaign.getName()));
169             query.append("type=");
170             query.append(JDBC.quoteMore(campaign.getType()));
171             query.append("product_group=");
172             query.append(JDBC.quoteMore(campaign.getProductGroup()));
173             query.append("product_line=");
174             query.append(JDBC.quoteMore(campaign.getProductLine()));
175             query.append("lead_count=");
176             query.append(campaign.getLeadCount().intValue());
177             query.append("WHERE pk = ");
178             query.append(campaign.getPK());
179
180             if (Prefs.DEBUG) LogWrite.write(query.toString());
181             int updatedRows = stmt.executeUpdate(query.toString());
182         } catch (SQLException e) {
183             throw e;
184         } finally {
185             try { if (stmt != null) stmt.close();
186             } catch (SQLException x) { }
187         }
188     }
189
190     /**
191      * insert a new campaign row using the passed
192      * Campaign object as the column values.
193      *
194      * @param obj the object we are going to insert
195      * @param load true if this row is to be loaded, false if
196      * the row is to be added for the first time
197      * @return the newly assigned primary key of the new row
198      * @exception java.sql.SQLException
199      */

200     public long insertRow(Object JavaDoc obj, boolean load)
201         throws SQLException {
202
203         Campaign campaign = (Campaign)obj;
204
205         if (!load)
206             campaign.setPK(DBUtils.generatePK());
207
208         Statement stmt = null;
209         StringBuffer JavaDoc query = new StringBuffer JavaDoc(insertQuery);
210
211         try {
212             stmt = con.createStatement();
213
214             query.append(campaign.getPK()).append(",");
215             query.append(JDBC.quoteMore(campaign.getName()));
216             query.append(JDBC.quoteMore(campaign.getType()));
217             query.append(JDBC.quoteMore(campaign.getProductGroup()));
218             query.append(JDBC.quoteMore(campaign.getProductLine()));
219             query.append(campaign.getLeadCount().intValue()).append(",");
220             query.append(JDBC.quoteMore(campaign.getModifiedBy()));
221             query.append(JDBC.quote(DateUtils.format(DB_TYPE,campaign.getModifiedDate())));
222             query.append(")");
223
224             if (Prefs.DEBUG) LogWrite.write(query.toString());
225             int rc = stmt.executeUpdate(query.toString());
226         } catch (SQLException e) {
227             throw e;
228         } finally {
229             try { if (stmt != null) stmt.close();
230             } catch (SQLException x) { }
231         }
232
233         return campaign.getPK();
234     }
235
236     /**
237      * delete a single campaign row using the passed
238      * primary key value
239      *
240      * @param ojb primary key stored in a Long we use to delete with
241      * @exception java.sql.SQLException
242      */

243     public final void deleteRow(Object JavaDoc obj)
244         throws SQLException {
245
246         long pkValue = ((Long JavaDoc)obj).longValue();
247         String JavaDoc query = deleteQuery + pkValue;
248     
249         Statement stmt = null;
250         try {
251             stmt = con.createStatement();
252             if (Prefs.DEBUG) LogWrite.write(query);
253             stmt.executeUpdate(query);
254         } catch (SQLException e) {
255             throw e;
256         } finally {
257             try { if (stmt != null) stmt.close();
258             } catch (SQLException x) { }
259         }
260     }
261
262     /**
263      * select all campaign rows
264      *
265      * @param lastSyncDate a user's last sync date used to limit the query
266      * or null if no limit
267      * @return the Campaign(s) that were selected
268      * @exception java.sql.SQLException
269      */

270     public final ArrayList JavaDoc selectAllRows(java.util.Date JavaDoc lastSyncDate)
271         throws SQLException {
272
273         ArrayList JavaDoc campaigns = new ArrayList JavaDoc();
274         Statement stmt = null;
275         ResultSet rs = null;
276         StringBuffer JavaDoc query = new StringBuffer JavaDoc();
277         query.append(selectAllQuery);
278         if (lastSyncDate != null) {
279             query.append(" WHERE campaign.modified_date > ");
280             query.append(DateUtils.formatDateTime(DB_TYPE, lastSyncDate));
281         }
282
283         try {
284             stmt = con.createStatement();
285             if (Prefs.DEBUG) LogWrite.write(query.toString());
286             rs = stmt.executeQuery(query.toString());
287
288             int i;
289             Campaign campaign;
290
291             while (rs.next()) {
292                 i=1;
293                 campaign = new Campaign();
294                 campaign.setPK(rs.getLong(i)); i++;
295                 campaign.setName(rs.getString(i)); i++;
296                 campaign.setType(rs.getString(i)); i++;
297                 campaign.setProductGroup(rs.getString(i)); i++;
298                 campaign.setProductLine(rs.getString(i)); i++;
299                 campaign.setLeadCount(new Integer JavaDoc(rs.getInt(i))); i++;
300                 campaign.setModifiedBy(rs.getString(i)); i++;
301                 campaign.setModifiedDate(rs.getDate(i));
302                 campaigns.add(campaign);
303             }
304         } catch (SQLException e) {
305             throw e;
306         } finally {
307             try {
308                 if (rs != null) rs.close();
309             } catch (SQLException x) { throw x; }
310             try {
311                 if (stmt != null) stmt.close();
312             } catch (SQLException x) { throw x; }
313         }
314
315         return campaigns;
316     }
317
318     /**
319      * delete all campaign rows
320      *
321      * @exception java.sql.SQLException
322      */

323     public final void deleteAllRows()
324         throws SQLException {
325
326         String JavaDoc query = deleteAllQuery;
327     
328         Statement stmt = null;
329         try {
330             stmt = con.createStatement();
331             if (Prefs.DEBUG) LogWrite.write(query);
332             stmt.executeUpdate(query);
333         } catch (SQLException e) {
334             throw e;
335         } finally {
336             try { if (stmt != null) stmt.close();
337             } catch (SQLException x) { }
338         }
339     }
340     /**
341      * truncate the whole table
342      *
343      * @exception java.sql.SQLException
344      */

345     public final void truncate()
346         throws SQLException {
347
348         String JavaDoc query = "truncate table campaign";
349    
350         Statement stmt = null;
351         try {
352             stmt = con.createStatement();
353             if (Prefs.DEBUG) LogWrite.write(query);
354             stmt.executeUpdate(query);
355         } catch (SQLException e) {
356             throw e;
357         } finally {
358             try { if (stmt != null) stmt.close();
359             } catch (SQLException x) { }
360         }
361     }
362
363 }
364
Popular Tags