KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > sellwin > db > StateTaxDB


1 package sellwin.db;
2
3 import java.sql.*;
4 import java.util.*;
5
6 import sellwin.domain.*;
7 import sellwin.utils.*;
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 StateTax class objects which are stored
16  * in the address database table.
17  */
18 public class StateTaxDB extends DBType implements DBInterface {
19
20     private Connection con;
21
22     private final static String JavaDoc selectQuery =
23             "SELECT " +
24                 "STATE_CODE, STATE_FULL_NAME, TAX_PCT, " +
25                 "modified_by, modified_date " +
26             "FROM state_tax WHERE STATE_CODE=";
27
28     private final static String JavaDoc selectAllQuery =
29             "SELECT " +
30                 "STATE_CODE, STATE_FULL_NAME, TAX_PCT, " +
31                 "modified_by, modified_date " +
32             "FROM state_tax ";
33
34     private final static String JavaDoc updateQuery =
35             "UPDATE state_tax " +
36             "SET ";
37
38     private final static String JavaDoc insertQuery =
39         "INSERT INTO state_tax VALUES ( ";
40
41     private final static String JavaDoc deleteAllQuery =
42         "DELETE FROM state_tax";
43
44
45     /**
46      * a do-nothing constructor but necessary to
47      * do the operations offered by this class
48      */
49     public StateTaxDB() {
50     }
51
52     /**
53      * construct using a db type
54      * @param dbType the database type to use
55      */
56     public StateTaxDB(int dbType) {
57         DB_TYPE = dbType;
58     }
59
60     /**
61      * a constructor that accepts an existing Connection
62      * to use for future operations
63      *
64      * @param con the Connection to use
65      */
66     public StateTaxDB(Connection con) {
67         this.con = con;
68     }
69
70     /**
71      * get the Connection in use
72      *
73      * @return the Connection in use
74      */
75     public Connection getConnection() {
76         return this.con;
77     }
78
79     /**
80      * set the Connection to use
81      *
82      * @param con the Connection to use for any future IO's
83      */
84     public void setConnection(Connection con)
85         throws SQLException {
86
87         this.con = con;
88     }
89
90     /**
91      * select a single address row using the passed
92      * primary key
93      *
94      * @param pk the primary key we are searching with
95      * @return the StateTax(s) that were selected
96      * @exception java.sql.SQLException
97      */
98     public final Object JavaDoc selectRow(Object JavaDoc pk)
99         throws SQLException {
100
101         StateTax tax = new StateTax();
102         tax.setCode((String JavaDoc)pk);
103         Statement stmt = null;
104         ResultSet rs = null;
105         String JavaDoc query = selectQuery + "'" + tax.getCode() + "'";
106
107         try {
108             stmt = con.createStatement();
109             if (Prefs.DEBUG) LogWrite.write(query);
110             rs = stmt.executeQuery(query);
111
112             int i;
113             while (rs.next()) {
114                 i=1;
115                 tax.setFullName(rs.getString(i)); i++;
116                 tax.setRate(rs.getDouble(i)); i++;
117                 tax.setModifiedBy(rs.getString(i)); i++;
118                 tax.setModifiedDate(rs.getDate(i));
119             }
120         } catch (SQLException e) {
121             throw e;
122         } finally {
123             try {
124                 if (rs != null) rs.close();
125             } catch (SQLException x) { throw x; }
126             try {
127                 if (stmt != null) stmt.close();
128             } catch (SQLException x) { throw x; }
129         }
130
131         return tax;
132     }
133
134
135     /**
136      * update a single state_tax row using the passed
137      * StateTax object's attributes. All columns
138      * get updated by this routine regardless of whether
139      * an attribute was modified or not.
140      *
141      * @param obj the object we want to update with
142      * @exception java.sql.SQLException
143      */
144     public void updateRow(Object JavaDoc obj)
145         throws SQLException {
146
147         StateTax tax = (StateTax)obj;
148
149         Statement stmt = null;
150         StringBuffer JavaDoc query = new StringBuffer JavaDoc(updateQuery);
151
152         try {
153             stmt = con.createStatement();
154
155             query.append("tax_pct=");
156             query.append(tax.getRate());
157             query.append(",modified_by='");
158             query.append(tax.getModifiedBy()).append("'");
159             query.append(",modified_date=");
160             if (DB_TYPE == Prefs.MYSQL)
161                 query.append("CURRENT_DATE ");
162             else
163                 query.append("SYSDATE ");
164             query.append(" WHERE state_code='");
165             query.append(tax.getCode()).append("'");
166
167             if (Prefs.DEBUG) LogWrite.write(query.toString());
168             int updatedRows = stmt.executeUpdate(query.toString());
169         } catch (SQLException e) {
170             throw e;
171         } finally {
172             try { if (stmt != null) stmt.close();
173             } catch (SQLException x) { }
174         }
175     }
176
177     /**
178      * not implemented since we don't need insert
179      * functionality for this table
180      *
181      * @param obj the object we are inserting
182      * @param load true if loading, false if adding
183      * @return the newly assigned primary key of the new row
184      * @exception java.sql.SQLException
185      */
186     public long insertRow(Object JavaDoc obj, boolean load)
187         throws SQLException {
188
189         StateTax st = (StateTax)obj;
190         
191         Statement stmt = null;
192         StringBuffer JavaDoc query = new StringBuffer JavaDoc(insertQuery);
193
194         try {
195             stmt = con.createStatement();
196             query.append(JDBC.quoteMore(st.getCode()));
197             query.append(st.getRate()).append(",");
198             query.append(JDBC.quoteMore(st.getFullName()));
199             query.append(JDBC.quoteMore(st.getModifiedBy()));
200             query.append(JDBC.quote(DateUtils.format(DB_TYPE, st.getModifiedDate())));
201             query.append(")");
202             if (Prefs.DEBUG) LogWrite.write(query.toString());
203             int rc = stmt.executeUpdate(query.toString());
204         } catch (SQLException e) {
205             throw e;
206         } finally {
207             try { if (stmt != null) stmt.close();
208             } catch (SQLException x) {}
209         }
210
211         return 0L;
212     }
213
214     /**
215      * not needed since we don't ever delete from this table
216      *
217      * @param ojb primary key stored in a Long
218      * @exception java.sql.SQLException
219      */
220     public final void deleteRow(Object JavaDoc obj)
221         throws SQLException {
222
223     }
224
225     /**
226      * delete all rows
227      * @exception java.sql.SQLException
228      */
229     public final void deleteAllRows()
230         throws SQLException {
231
232         Statement stmt = null;
233         try {
234             stmt = con.createStatement();
235             if (Prefs.DEBUG) LogWrite.write(deleteAllQuery);
236             stmt.executeUpdate(deleteAllQuery);
237         } catch (SQLException e) {
238             throw e;
239         } finally {
240             try { if (stmt != null) stmt.close();
241             } catch (SQLException x) { }
242         }
243     }
244
245     /**
246      * select all state_tax rows
247      *
248      * @param lastSyncDate a user's last sync date which can
249      * limit the query results, if null, no limit
250      * @return the ArrayList of StateTax(s)
251      * @exception java.sql.SQLException
252      */
253     public final ArrayList selectAllRows(java.util.Date JavaDoc lastSyncDate)
254         throws SQLException {
255
256         ArrayList list = new ArrayList();
257         StateTax tax;
258         Statement stmt = null;
259         ResultSet rs = null;
260         StringBuffer JavaDoc query = new StringBuffer JavaDoc();
261         query.append(selectAllQuery);
262         if (lastSyncDate != null) {
263             query.append(" WHERE modified_date > ");
264             query.append(DateUtils.formatDateTime(DB_TYPE, lastSyncDate));
265         }
266         query.append(" ORDER BY STATE_CODE");
267
268         try {
269             stmt = con.createStatement();
270             if (Prefs.DEBUG) LogWrite.write(query.toString());
271             rs = stmt.executeQuery(query.toString());
272
273             int i;
274             while (rs.next()) {
275                 i=1;
276                 tax = new StateTax();
277                 tax.setCode(rs.getString(i)); i++;
278                 tax.setFullName(rs.getString(i)); i++;
279                 tax.setRate(rs.getDouble(i)); i++;
280                 tax.setModifiedBy(rs.getString(i)); i++;
281                 tax.setModifiedDate(rs.getDate(i));
282                 list.add(tax);
283             }
284         } catch (SQLException e) {
285             throw e;
286         } finally {
287             try {
288                 if (rs != null) rs.close();
289             } catch (SQLException x) { throw x; }
290             try {
291                 if (stmt != null) stmt.close();
292             } catch (SQLException x) { throw x; }
293         }
294
295         return list;
296     }
297     /**
298      * truncate the whole table
299      *
300      * @exception java.sql.SQLException
301      */
302     public final void truncate()
303         throws SQLException {
304
305         String JavaDoc query = "truncate table state_tax";
306    
307         Statement stmt = null;
308         try {
309             stmt = con.createStatement();
310             if (Prefs.DEBUG) LogWrite.write(query);
311             stmt.executeUpdate(query);
312         } catch (SQLException e) {
313             throw e;
314         } finally {
315             try { if (stmt != null) stmt.close();
316             } catch (SQLException x) { }
317         }
318     }
319
320 }
321
Popular Tags