1 package sellwin.db; 2 3 import sellwin.domain.*; 4 import sellwin.utils.*; 5 6 import java.sql.*; 7 8 12 13 18 public class AddressDB extends DBType implements DBInterface { 19 20 private Connection con; 21 22 private final static String 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 updateQuery = 33 "UPDATE address " + 34 "SET "; 35 36 private final static String insertQuery = 37 "INSERT INTO address VALUES ( "; 38 39 private final static String deleteQuery = 40 "DELETE FROM address WHERE pk ="; 41 42 46 public AddressDB() { 47 } 48 49 53 public AddressDB(int dbType) { 54 DB_TYPE=dbType; 55 } 56 57 63 public AddressDB(Connection con) { 64 this.con = con; 65 } 66 67 72 public Connection getConnection() { 73 return this.con; 74 } 75 76 81 public void setConnection(Connection con) 82 throws SQLException { 83 84 this.con = con; 85 } 86 87 95 public final Object selectRow(Object pk) 96 throws SQLException { 97 98 Address address = new Address(); 99 long pkValue = ((Long )pk).longValue(); 100 address.setPK(pkValue); 101 Statement stmt = null; 102 ResultSet rs = null; 103 String 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 156 public void updateRow(Object obj) 157 throws SQLException { 158 159 Address address = (Address)obj; 160 long pk = address.getPK(); 161 162 Statement stmt = null; 163 StringBuffer query = new StringBuffer (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 223 public long insertRow(Object 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 query = new StringBuffer (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 279 public final void deleteRow(Object obj) 280 throws SQLException { 281 282 long pkValue = ((Long )obj).longValue(); 283 String 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 302 public final void truncate() 303 throws SQLException { 304 305 String 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 |