1 package sellwin.db; 2 3 import sellwin.domain.*; 4 import sellwin.utils.*; 5 6 import java.sql.*; 7 import java.util.ArrayList ; 8 9 13 14 19 public class CampaignDB extends DBType implements DBInterface { 20 private Connection con; 21 22 private final static String 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 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 updateQuery = 38 "UPDATE campaign " + 39 "SET " ; 40 41 private final static String insertQuery = 42 "INSERT INTO campaign VALUES ( "; 43 44 private final static String deleteQuery = 45 "DELETE FROM campaign WHERE pk ="; 46 47 private final static String deleteAllQuery = 48 "DELETE FROM campaign "; 49 50 51 56 public CampaignDB() { 57 } 58 59 63 public CampaignDB(int dbType) { 64 DB_TYPE=dbType; 65 } 66 67 73 public CampaignDB(Connection con) { 74 this.con = con; 75 } 76 77 82 public Connection getConnection() { 83 return this.con; 84 } 85 86 91 public void setConnection(Connection con) 92 throws SQLException { 93 94 this.con = con; 95 } 96 97 105 public final Object selectRow(Object pk) 106 throws SQLException { 107 108 Campaign campaign = new Campaign(); 109 long pkValue = ((Long )pk).longValue(); 110 campaign.setPK(pkValue); 111 Statement stmt = null; 112 ResultSet rs = null; 113 String 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 (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 155 public void updateRow(Object obj) 156 throws SQLException { 157 158 Campaign campaign = (Campaign)obj; 159 long pk = campaign.getPK(); 160 161 Statement stmt = null; 162 StringBuffer query = new StringBuffer (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 200 public long insertRow(Object 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 query = new StringBuffer (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 243 public final void deleteRow(Object obj) 244 throws SQLException { 245 246 long pkValue = ((Long )obj).longValue(); 247 String 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 270 public final ArrayList selectAllRows(java.util.Date lastSyncDate) 271 throws SQLException { 272 273 ArrayList campaigns = new ArrayList (); 274 Statement stmt = null; 275 ResultSet rs = null; 276 StringBuffer query = new StringBuffer (); 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 (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 323 public final void deleteAllRows() 324 throws SQLException { 325 326 String 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 345 public final void truncate() 346 throws SQLException { 347 348 String 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 |