KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > sellwin > db > AddressDB


1 package sellwin.db;
2
3 import sellwin.domain.*;
4 import sellwin.utils.*;
5
6 import java.sql.*;
7
8 // SellWin http://sourceforge.net/projects/sellwincrm
9
//Contact support@open-app.com for commercial help with SellWin
10
//This software is provided "AS IS", without a warranty of any kind.
11

12
13 /**
14  * This class implements the DBInterface for
15  * the Address class objects which are stored
16  * in the address database table.
17  */

18 public class AddressDB extends DBType implements DBInterface {
19
20     private Connection con;
21
22     private final static String JavaDoc selectQuery =
23             "SELECT " +
24                 "first_name, last_name, middle_initial, " +
25                 "address_line1, address_line2, title ," +
26                 "phone, fax, pager, cell, email, " +
27                 "city, state, country, zip, zip4, " +
28                 "modified_by, modified_date " +
29             "FROM address " +
30             "WHERE pk=";
31
32     private final static String JavaDoc updateQuery =
33             "UPDATE address " +
34             "SET ";
35
36     private final static String JavaDoc insertQuery =
37             "INSERT INTO address VALUES ( ";
38
39     private final static String JavaDoc deleteQuery =
40             "DELETE FROM address WHERE pk =";
41
42     /**
43      * a do-nothing constructor but necessary to
44      * do the operations offered by this class
45      */

46     public AddressDB() {
47     }
48
49     /**
50      * construct using a db type
51      * @param dbType the database type
52      */

53     public AddressDB(int dbType) {
54         DB_TYPE=dbType;
55     }
56
57     /**
58      * a constructor that accepts an existing Connection
59      * to use for future operations
60      *
61      * @param con the Connection to use
62      */

63     public AddressDB(Connection con) {
64         this.con = con;
65     }
66
67     /**
68      * get the Connection in use
69      *
70      * @return the Connection in use
71      */

72     public Connection getConnection() {
73         return this.con;
74     }
75
76     /**
77      * set the Connection to use
78      *
79      * @param con the Connection to use for any future IO's
80      */

81     public void setConnection(Connection con)
82         throws SQLException {
83
84         this.con = con;
85     }
86
87     /**
88      * select a single address row using the passed
89      * primary key
90      *
91      * @param pk the primary key we are searching with
92      * @return the Address(s) that were selected
93      * @exception java.sql.SQLException
94      */

95     public final Object JavaDoc selectRow(Object JavaDoc pk)
96         throws SQLException {
97
98         Address address = new Address();
99         long pkValue = ((Long JavaDoc)pk).longValue();
100         address.setPK(pkValue);
101         Statement stmt = null;
102         ResultSet rs = null;
103         String JavaDoc query = selectQuery + pkValue;
104
105         try {
106             stmt = con.createStatement();
107             if (Prefs.DEBUG) LogWrite.write(query);
108             rs = stmt.executeQuery(query);
109
110             int i;
111             while (rs.next()) {
112                 i=1;
113                 address.setFirstName(rs.getString(i)); i++;
114                 address.setLastName(rs.getString(i)); i++;
115                 address.setMiddleInitial(rs.getString(i)); i++;
116                 address.setAddressLine1(rs.getString(i)); i++;
117                 address.setAddressLine2(rs.getString(i)); i++;
118                 address.setTitle(rs.getString(i)); i++;
119                 address.setPhone(rs.getString(i)); i++;
120                 address.setFax(rs.getString(i)); i++;
121                 address.setPager(rs.getString(i)); i++;
122                 address.setCell(rs.getString(i)); i++;
123                 address.setEmail(rs.getString(i)); i++;
124                 address.setCity(rs.getString(i)); i++;
125                 address.setState(rs.getString(i)); i++;
126                 address.setCountry(rs.getString(i)); i++;
127                 address.setZip(rs.getString(i)); i++;
128                 address.setZip4(rs.getString(i)); i++;
129                 address.setModifiedBy(rs.getString(i)); i++;
130                 address.setModifiedDate(rs.getDate(i));
131             }
132         } catch (SQLException e) {
133             throw e;
134         } finally {
135             try {
136                 if (rs != null) rs.close();
137             } catch (SQLException x) { throw x; }
138             try {
139                 if (stmt != null) stmt.close();
140             } catch (SQLException x) { throw x; }
141         }
142
143         return address;
144     }
145
146
147     /**
148      * update a single address row using the passed
149      * Address object's attributes. All columns
150      * get updated by this routine regardless of whether
151      * an attribute was modified or not.
152      *
153      * @param obj the object we want to update with
154      * @exception java.sql.SQLException
155      */

156     public void updateRow(Object JavaDoc obj)
157         throws SQLException {
158
159         Address address = (Address)obj;
160         long pk = address.getPK();
161
162         Statement stmt = null;
163         StringBuffer JavaDoc query = new StringBuffer JavaDoc(updateQuery);
164
165         try {
166             stmt = con.createStatement();
167
168             query.append("first_name=");
169             query.append(JDBC.quoteMore(address.getFirstName()));
170             query.append("last_name=");
171             query.append(JDBC.quoteMore(address.getLastName()));
172             query.append("middle_initial=");
173             query.append(JDBC.quoteMore(address.getMiddleInitial()));
174             query.append("address_line1=");
175             query.append(JDBC.quoteMore(address.getAddressLine1()));
176             query.append("address_line2=");
177             query.append(JDBC.quoteMore(address.getAddressLine2()));
178             query.append("title=");
179             query.append(JDBC.quoteMore(address.getTitle()));
180             query.append("phone=");
181             query.append(JDBC.quoteMore(address.getPhone()));
182             query.append("fax=");
183             query.append(JDBC.quoteMore(address.getFax()));
184             query.append("pager=");
185             query.append(JDBC.quoteMore(address.getPager()));
186             query.append("cell=");
187             query.append(JDBC.quoteMore(address.getCell()));
188             query.append("email=");
189             query.append(JDBC.quoteMore(address.getEmail()));
190             query.append("city=");
191             query.append(JDBC.quoteMore(address.getCity()));
192             query.append("state=");
193             query.append(JDBC.quoteMore(address.getState()));
194             query.append("country=");
195             query.append(JDBC.quoteMore(address.getCountry()));
196             query.append("zip=");
197             query.append(JDBC.quoteMore(address.getZip()));
198             query.append("zip4=");
199             query.append(JDBC.quote(address.getZip4()));
200             query.append(" WHERE pk=");
201             query.append(address.getPK());
202
203             if (Prefs.DEBUG) LogWrite.write(query.toString());
204             int updatedRows = stmt.executeUpdate(query.toString());
205         } catch (SQLException e) {
206             throw e;
207         } finally {
208             try { if (stmt != null) stmt.close();
209             } catch (SQLException x) { }
210         }
211     }
212
213     /**
214      * insert a new address row using the passed
215      * Address object as the column values.
216      *
217      * @param obj the object we are inserting
218      * @param load true if the row is to be loaded, false if the
219      * row is to be added for the first time
220      * @return the newly assigned primary key of the new row
221      * @exception java.sql.SQLException
222      */

223     public long insertRow(Object JavaDoc obj, boolean load)
224         throws SQLException {
225
226         Address address = (Address)obj;
227         if (!load) {
228             address.setPK(DBUtils.generatePK());
229             LogWrite.write("generated address pk = " + address.getPK());
230             address.setPK(address.getPK() + 1);
231         }
232
233         Statement stmt = null;
234         StringBuffer JavaDoc query = new StringBuffer JavaDoc(insertQuery);
235
236         try {
237             stmt = con.createStatement();
238
239             query.append(address.getPK()).append(",");
240             query.append(JDBC.quoteMore(address.getFirstName()));
241             query.append(JDBC.quoteMore(address.getLastName()));
242             query.append(JDBC.quoteMore(address.getMiddleInitial()));
243             query.append(JDBC.quoteMore(address.getAddressLine1()));
244             query.append(JDBC.quoteMore(address.getAddressLine2()));
245             query.append(JDBC.quoteMore(address.getTitle()));
246             query.append(JDBC.quoteMore(address.getPhone()));
247             query.append(JDBC.quoteMore(address.getFax()));
248             query.append(JDBC.quoteMore(address.getPager()));
249             query.append(JDBC.quoteMore(address.getCell()));
250             query.append(JDBC.quoteMore(address.getEmail()));
251             query.append(JDBC.quoteMore(address.getCity()));
252             query.append(JDBC.quoteMore(address.getState()));
253             query.append(JDBC.quoteMore(address.getCountry()));
254             query.append(JDBC.quoteMore(address.getZip()));
255             query.append(JDBC.quoteMore(address.getZip4()));
256             query.append(JDBC.quoteMore(address.getModifiedBy()));
257             query.append(JDBC.quote(DateUtils.format(DB_TYPE, address.getModifiedDate())));
258             query.append(")");
259
260             if (Prefs.DEBUG) LogWrite.write(query.toString());
261             int rc = stmt.executeUpdate(query.toString());
262         } catch (SQLException e) {
263             throw e;
264         } finally {
265             try { if (stmt != null) stmt.close();
266             } catch (SQLException x) { }
267         }
268
269         return address.getPK();
270     }
271
272     /**
273      * delete a single address row using the passed
274      * primary key value
275      *
276      * @param ojb primary key stored in a Long
277      * @exception java.sql.SQLException
278      */

279     public final void deleteRow(Object JavaDoc obj)
280         throws SQLException {
281
282         long pkValue = ((Long JavaDoc)obj).longValue();
283         String JavaDoc query = deleteQuery + pkValue;
284     
285         Statement stmt = null;
286         try {
287             stmt = con.createStatement();
288             if (Prefs.DEBUG) LogWrite.write(query);
289             stmt.executeUpdate(query);
290         } catch (SQLException e) {
291             throw e;
292         } finally {
293             try { if (stmt != null) stmt.close();
294             } catch (SQLException x) { }
295         }
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 address";
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 }
322
Popular Tags