1 package sellwin.db; 2 3 import sellwin.domain.*; 4 import sellwin.utils.*; 5 6 import java.sql.*; 7 import java.util.*; 8 9 13 18 public class UserGroupDB extends DBType implements DBInterface { 19 private Connection con; 20 21 private final static String deleteAllQuery = 22 "DELETE FROM user_group"; 23 24 private final static String deleteByNameQuery = 25 "DELETE FROM user_group WHERE name = "; 26 27 private final static String deleteQuery = 28 "DELETE FROM user_group WHERE pk = "; 29 30 private final static String insertQuery = 31 "INSERT INTO user_group " + 32 "VALUES ("; 33 private final static String updateQuery = 34 "UPDATE user_group " + 35 "SET "; 36 private final static String selectQuery = 37 "SELECT name, "+ 38 "modified_by, modified_date " + 39 "FROM user_group "+ 40 "WHERE pk = "; 41 42 private final static String selectAllQuery = 43 "SELECT pk, name, "+ 44 "modified_by, modified_date " + 45 "FROM user_group "; 46 47 private final static String selectUsersInGroupQuery = 48 "SELECT user_group_member.user_pk " + 49 "FROM user_group, user_group_member " + 50 "WHERE " + 51 "user_group.pk = user_group_member.user_group_pk AND "+ 52 "user_group.name = "; 53 54 58 public UserGroupDB() { 59 } 60 61 65 public UserGroupDB(int dbType) { 66 DB_TYPE = dbType; 67 } 68 69 75 public UserGroupDB(Connection con) { 76 this.con = con; 77 } 78 79 84 public Connection getConnection() { 85 return this.con; 86 } 87 88 93 public final void setConnection(Connection con) 94 throws SQLException { 95 96 this.con = con; 97 } 98 99 107 public final Object selectRow(Object pk) 108 throws SQLException { 109 110 UserGroup ug = new UserGroup(""); 111 ug.setPK(((Long )pk).longValue()); 112 113 Statement stmt = null; 114 ResultSet rs = null; 115 String query = selectQuery + ug.getPK(); 116 117 try { 118 stmt = con.createStatement(); 119 if (Prefs.DEBUG) LogWrite.write(query); 120 rs = stmt.executeQuery(query); 121 122 int i; 123 124 while (rs.next()) { 125 i=1; 126 ug.setName(rs.getString(i)); i++; 127 ug.setModifiedBy(rs.getString(i)); i++; 128 ug.setModifiedDate(rs.getDate(i)); 129 } 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 ug; 143 } 144 145 146 155 public final void updateRow(Object obj) 156 throws SQLException { 157 158 UserGroup user_group = (UserGroup)obj; 159 160 StringBuffer query = new StringBuffer (updateQuery); 161 Statement stmt = con.createStatement(); 162 163 query.append("name="); 164 query.append(JDBC.quoteMore(user_group.getName())); 165 query.append("modified_by="); 166 query.append(JDBC.quoteMore(user_group.getModifiedBy())); 167 query.append("modified_date="); 168 if (DB_TYPE == Prefs.MYSQL) 169 query.append("CURRENT_DATE"); 170 else 171 query.append("SYSDATE"); 172 query.append(" WHERE pk=").append(user_group.getPK()); 173 174 if (Prefs.DEBUG) LogWrite.write(query.toString()); 175 int updatedRows = stmt.executeUpdate(query.toString()); 176 } 177 178 187 public final long insertRow(Object obj, boolean load) 188 throws SQLException { 189 190 UserGroup user_group = (UserGroup)obj; 191 192 if (!load) 193 user_group.setPK(DBUtils.generatePK()); 194 195 StringBuffer query = new StringBuffer (insertQuery); 196 Statement stmt = con.createStatement(); 197 198 query.append(user_group.getPK()).append(","); 199 query.append(JDBC.quoteMore(user_group.getName())); 200 query.append(JDBC.quoteMore(user_group.getModifiedBy())); 201 if (DB_TYPE == Prefs.MYSQL) 202 query.append("CURRENT_DATE"); 203 else 204 query.append("SYSDATE"); 205 query.append(")"); 206 207 if (Prefs.DEBUG) LogWrite.write(query.toString()); 208 int rc = stmt.executeUpdate(query.toString()); 209 210 return user_group.getPK(); 211 } 212 213 220 public final void deleteRow(Object obj) 221 throws SQLException { 222 223 long pkValue = ((Long )obj).longValue(); 224 225 String query = deleteQuery + pkValue; 226 227 Statement stmt = null; 228 229 try { 230 stmt = con.createStatement(); 231 if (Prefs.DEBUG) LogWrite.write(query); 232 stmt.executeUpdate(query); 233 } catch (SQLException e) { 234 throw e; 235 } finally { 236 try { 237 if (stmt != null) stmt.close(); 238 } catch (SQLException x) { } 239 } 240 } 241 242 249 public final void deleteByNameRow(String name) 250 throws SQLException { 251 252 String query = deleteByNameQuery + "'" + name + "'"; 253 254 Statement stmt = null; 255 256 try { 257 stmt = con.createStatement(); 258 if (Prefs.DEBUG) LogWrite.write(query); 259 stmt.executeUpdate(query); 260 } catch (SQLException e) { 261 throw e; 262 } finally { 263 try { 264 if (stmt != null) stmt.close(); 265 } catch (SQLException x) { } 266 } 267 } 268 269 276 public final TreeMap selectUsersInGroup(String groupName) 277 throws SQLException { 278 279 TreeMap users = new TreeMap(); 280 Statement stmt = null; 281 ResultSet rs = null; 282 String query = selectUsersInGroupQuery + "'" + groupName + "'"; 283 284 try { 285 stmt = con.createStatement(); 286 if (Prefs.DEBUG) LogWrite.write(query); 287 rs = stmt.executeQuery(query); 288 289 int i; 290 long user_pk; 291 SalesPerson sp=null; 292 SalesPersonDB salesPersonDB = new SalesPersonDB(DB_TYPE); 293 salesPersonDB.setConnection(getConnection()); 294 295 while (rs.next()) { 296 i=1; 297 user_pk = rs.getLong(i); i++; 298 sp = (SalesPerson)(salesPersonDB.selectRow(new Long (user_pk))); 299 users.put(sp.getID(), sp); 300 } 301 } catch (SQLException e) { 302 throw e; 303 } finally { 304 try { 305 if (rs != null) rs.close(); 306 } catch (SQLException x) { throw x; } 307 try { 308 if (stmt != null) stmt.close(); 309 } catch (SQLException x) { throw x; } 310 } 311 312 return users; 313 } 314 315 321 public final ArrayList selectUserPKsInGroup(String groupName) 322 throws SQLException { 323 324 ArrayList list = new ArrayList(); 325 Statement stmt = null; 326 ResultSet rs = null; 327 String query = selectUsersInGroupQuery + "'" + groupName + "'"; 328 329 try { 330 stmt = con.createStatement(); 331 if (Prefs.DEBUG) LogWrite.write(query); 332 rs = stmt.executeQuery(query); 333 334 int i; 335 long user_pk; 336 337 while (rs.next()) { 338 i=1; 339 user_pk = rs.getLong(i); i++; 340 list.add(new Long (user_pk)); 341 } 342 } catch (SQLException e) { 343 throw e; 344 } finally { 345 try { 346 if (rs != null) rs.close(); 347 } catch (SQLException x) { throw x; } 348 try { 349 if (stmt != null) stmt.close(); 350 } catch (SQLException x) { throw x; } 351 } 352 353 return list; 354 } 355 356 364 public final TreeMap selectAllRows(java.util.Date lastSyncDate) 365 throws SQLException { 366 367 UserGroup ug = null; 368 TreeMap list = new TreeMap(); 369 Statement stmt = null; 370 ResultSet rs = null; 371 StringBuffer query = new StringBuffer (); 372 query.append(selectAllQuery); 373 if (lastSyncDate != null) { 374 query.append(" WHERE user_group.modified_date > "); 375 query.append(DateUtils.formatDateTime(DB_TYPE, lastSyncDate)); 376 } 377 query.append(" ORDER BY name"); 378 379 try { 380 stmt = con.createStatement(); 381 if (Prefs.DEBUG) LogWrite.write(query.toString()); 382 rs = stmt.executeQuery(query.toString()); 383 384 int i,j; 385 ArrayList users; 386 SalesPerson sp; 387 388 while (rs.next()) { 389 i=1; 390 ug = new UserGroup(); 391 ug.setPK(rs.getLong(i)); i++; 392 ug.setName(rs.getString(i)); i++; 393 ug.setModifiedBy(rs.getString(i)); i++; 394 ug.setModifiedDate(rs.getDate(i)); 395 396 users = selectUserPKsInGroup(ug.getName()); 397 for (j=0;j<users.size();j++) { 398 ug.addUser((Long )users.get(j)); 399 } 400 401 list.put(ug.getName(), ug); 402 } 403 404 } catch (SQLException e) { 405 throw e; 406 } finally { 407 try { 408 if (rs != null) rs.close(); 409 } catch (SQLException x) { throw x; } 410 try { 411 if (stmt != null) stmt.close(); 412 } catch (SQLException x) { throw x; } 413 } 414 415 return list; 416 } 417 418 423 public final void deleteAllRows() 424 throws SQLException { 425 426 String query = deleteAllQuery; 427 428 Statement stmt = null; 429 430 try { 431 stmt = con.createStatement(); 432 if (Prefs.DEBUG) LogWrite.write(query); 433 stmt.executeUpdate(query); 434 } catch (SQLException e) { 435 throw e; 436 } finally { 437 try { 438 if (stmt != null) stmt.close(); 439 } catch (SQLException x) { } 440 } 441 } 442 447 public final void truncate() 448 throws SQLException { 449 450 String query = "truncate table user_group"; 451 452 Statement stmt = null; 453 try { 454 stmt = con.createStatement(); 455 if (Prefs.DEBUG) LogWrite.write(query); 456 stmt.executeUpdate(query); 457 } catch (SQLException e) { 458 throw e; 459 } finally { 460 try { if (stmt != null) stmt.close(); 461 } catch (SQLException x) { } 462 } 463 } 464 465 } 466 | Popular Tags |