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 18 public class UserGroupMemberDB extends DBType implements DBInterface { 19 private Connection con; 20 21 private final static String deleteUserQuery = 22 "DELETE FROM user_group_member WHERE user_pk = "; 23 24 private final static String deleteQuery = 25 "DELETE FROM user_group_member WHERE pk = "; 26 27 private final static String deleteGroupQuery = 28 "DELETE FROM user_group_member WHERE user_group_pk="; 29 30 private final static String insertQuery = 31 "INSERT INTO user_group_member " + 32 "VALUES ("; 33 34 private final static String selectQuery = 35 "SELECT user_group_pk, user_pk, "+ 36 "modified_by, modified_date " + 37 "FROM user_group_member "+ 38 "WHERE pk = "; 39 40 private final static String selectUsersQuery = 41 "SELECT user_pk, "+ 42 "FROM user_group_member "+ 43 "WHERE user_group_pk = "; 44 45 private final static String selectGroupNamesQuery = 46 "SELECT user_group.name " + 47 "FROM user_group, user_group_member "+ 48 "WHERE user_group_member.user_group_pk = user_group.pk AND "+ 49 "user_group_member.user_pk = "; 50 51 private final static String selectGroupsForUserQuery = 52 "SELECT user_group.pk " + 53 "FROM user_group, user_group_member "+ 54 "WHERE user_group_member.user_group_pk = user_group.pk AND "+ 55 "user_group_member.user_pk = "; 56 57 private final static String selectAllQuery = 58 "SELECT pk, user_group_pk, user_pk, "+ 59 "modified_by, modified_date FROM user_group_member "; 60 61 62 66 public UserGroupMemberDB() { 67 } 68 69 73 public UserGroupMemberDB(int dbType) { 74 DB_TYPE = dbType; 75 } 76 77 83 public UserGroupMemberDB(Connection con) { 84 this.con = con; 85 } 86 87 92 public Connection getConnection() { 93 return this.con; 94 } 95 96 101 public final void setConnection(Connection con) 102 throws SQLException { 103 104 this.con = con; 105 } 106 107 115 public final Object selectRow(Object pk) 116 throws SQLException { 117 118 UserGroupMember ug = new UserGroupMember(); 119 ug.setPK(((Long )pk).longValue()); 120 121 Statement stmt = null; 122 ResultSet rs = null; 123 String query = selectQuery + ug.getPK(); 124 125 try { 126 stmt = con.createStatement(); 127 if (Prefs.DEBUG) LogWrite.write(query); 128 rs = stmt.executeQuery(query); 129 130 int i; 131 132 while (rs.next()) { 133 i=1; 134 ug.setUserGroupPK(rs.getLong(i)); i++; 135 ug.setUserPK(rs.getLong(i)); i++; 136 SalesPersonDB spd = new SalesPersonDB(DB_TYPE); 137 spd.setConnection(getConnection()); 138 SalesPerson sp = (SalesPerson)spd.selectRow(new Long (ug.getUserPK())); 139 ug.setUser(sp); 140 ug.setModifiedBy(rs.getString(i)); i++; 141 ug.setModifiedDate(rs.getDate(i)); 142 } 143 144 } catch (SQLException e) { 145 throw e; 146 } finally { 147 try { 148 if (rs != null) rs.close(); 149 } catch (SQLException x) { throw x; } 150 try { 151 if (stmt != null) stmt.close(); 152 } catch (SQLException x) { throw x; } 153 } 154 155 return ug; 156 } 157 158 159 165 public final void updateRow(Object obj) 166 throws SQLException { 167 168 } 170 171 180 public final long insertRow(Object obj, boolean load) 181 throws SQLException { 182 183 UserGroupMember user_group_member = (UserGroupMember)obj; 184 185 if (!load) 186 user_group_member.setPK(DBUtils.generatePK()); 187 188 StringBuffer query = new StringBuffer (insertQuery); 189 Statement stmt = con.createStatement(); 190 191 query.append(user_group_member.getPK()).append(","); 192 query.append(user_group_member.getUserGroupPK()).append(","); 193 query.append(user_group_member.getUserPK()).append(","); 194 query.append(JDBC.quoteMore(user_group_member.getModifiedBy())); 195 if (DB_TYPE == Prefs.MYSQL) 196 query.append("CURRENT_DATE"); 197 else 198 query.append("SYSDATE"); 199 query.append(")"); 200 201 if (Prefs.DEBUG) LogWrite.write(query.toString()); 202 int rc = stmt.executeUpdate(query.toString()); 203 204 return user_group_member.getPK(); 205 } 206 207 214 public final void deleteRow(Object obj) 215 throws SQLException { 216 217 long pkValue = ((Long )obj).longValue(); 218 219 String query = deleteQuery + pkValue; 220 221 Statement stmt = null; 222 223 try { 224 stmt = con.createStatement(); 225 if (Prefs.DEBUG) LogWrite.write(query); 226 stmt.executeUpdate(query); 227 } catch (SQLException e) { 228 throw e; 229 } finally { 230 try { 231 if (stmt != null) stmt.close(); 232 } catch (SQLException x) { } 233 } 234 } 235 236 243 public final void deleteByUserRow(long u_pk) 244 throws SQLException { 245 246 String query = deleteUserQuery + u_pk; 247 248 Statement stmt = null; 249 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 { 258 if (stmt != null) stmt.close(); 259 } catch (SQLException x) { } 260 } 261 } 262 263 271 public final ArrayList selectUsersInGroup(long ug_pk) 272 throws SQLException { 273 274 ArrayList users = new ArrayList (); 275 Statement stmt = null; 276 ResultSet rs = null; 277 String query = selectUsersQuery + ug_pk; 278 279 try { 280 stmt = con.createStatement(); 281 if (Prefs.DEBUG) LogWrite.write(query); 282 rs = stmt.executeQuery(query); 283 SalesPersonDB spDB = new SalesPersonDB(DB_TYPE); 284 spDB.setConnection(getConnection()); 285 long sp_pk; 286 SalesPerson sp; 287 288 while (rs.next()) { 289 sp_pk = rs.getLong(1); 290 sp = (SalesPerson)(spDB.selectRow(new Long (sp_pk))); 291 users.add(sp); 292 } 293 } catch (SQLException e) { 294 throw e; 295 } finally { 296 try { 297 if (rs != null) rs.close(); 298 } catch (SQLException x) { throw x; } 299 try { 300 if (stmt != null) stmt.close(); 301 } catch (SQLException x) { throw x; } 302 } 303 304 return users; 305 } 306 307 314 public final ArrayList selectGroupNamesForUser(long user_pk) 315 throws SQLException { 316 317 ArrayList groupNames = new ArrayList (); 318 Statement stmt = null; 319 ResultSet rs = null; 320 String query = selectGroupNamesQuery + user_pk + 321 " ORDER BY user_group.name "; 322 323 try { 324 stmt = con.createStatement(); 325 if (Prefs.DEBUG) LogWrite.write(query); 326 rs = stmt.executeQuery(query); 327 String groupName; 328 329 while (rs.next()) { 330 groupName = rs.getString(1); 331 groupNames.add(groupName); 332 } 333 } catch (SQLException e) { 334 throw e; 335 } finally { 336 try { 337 if (rs != null) rs.close(); 338 } catch (SQLException x) { throw x; } 339 try { 340 if (stmt != null) stmt.close(); 341 } catch (SQLException x) { throw x; } 342 } 343 344 return groupNames; 345 } 346 347 354 public final ArrayList selectGroupsForUser(long user_pk) 355 throws SQLException { 356 357 ArrayList groups = new ArrayList (); 358 Statement stmt = null; 359 ResultSet rs = null; 360 String query = selectGroupsForUserQuery + user_pk ; 361 362 try { 363 stmt = con.createStatement(); 364 if (Prefs.DEBUG) LogWrite.write(query); 365 rs = stmt.executeQuery(query); 366 UserGroup user_group; 367 long group_pk; 368 UserGroupDB userGroupDB = new UserGroupDB(DB_TYPE); 369 userGroupDB.setConnection(getConnection()); 370 371 while (rs.next()) { 372 group_pk = rs.getLong(1); 373 user_group = (UserGroup)userGroupDB.selectRow(new Long (group_pk)); 374 groups.add(user_group); 375 } 376 } catch (SQLException e) { 377 throw e; 378 } finally { 379 try { 380 if (rs != null) rs.close(); 381 } catch (SQLException x) { throw x; } 382 try { 383 if (stmt != null) stmt.close(); 384 } catch (SQLException x) { throw x; } 385 } 386 387 return groups; 388 } 389 394 public final void truncate() 395 throws SQLException { 396 397 String query = "truncate table user_group_member"; 398 399 Statement stmt = null; 400 try { 401 stmt = con.createStatement(); 402 if (Prefs.DEBUG) LogWrite.write(query); 403 stmt.executeUpdate(query); 404 } catch (SQLException e) { 405 throw e; 406 } finally { 407 try { if (stmt != null) stmt.close(); 408 } catch (SQLException x) { } 409 } 410 } 411 412 420 public final ArrayList selectAllRows(java.util.Date lastSyncDate) 421 throws SQLException { 422 423 ArrayList rows = new ArrayList (); 424 Statement stmt = null; 425 ResultSet rs = null; 426 StringBuffer query = new StringBuffer (); 427 query.append(selectAllQuery); 428 if (lastSyncDate != null) { 429 query.append(" WHERE user_group_member.modified_date > "); 430 query.append(DateUtils.formatDateTime(DB_TYPE, lastSyncDate)); 431 } 432 433 try { 434 stmt = con.createStatement(); 435 if (Prefs.DEBUG) LogWrite.write(query.toString()); 436 rs = stmt.executeQuery(query.toString()); 437 438 UserGroupMember ugm; 439 440 while (rs.next()) { 441 ugm = new UserGroupMember(); 442 443 ugm.setPK(rs.getLong(1)); 444 ugm.setUserGroupPK(rs.getLong(2)); 445 ugm.setUserPK(rs.getLong(3)); 446 ugm.setModifiedBy(rs.getString(4)); 447 ugm.setModifiedDate(rs.getDate(5)); 448 449 rows.add(ugm); 450 } 451 } catch (SQLException e) { 452 throw e; 453 } finally { 454 try { 455 if (rs != null) rs.close(); 456 } catch (SQLException x) { throw x; } 457 try { 458 if (stmt != null) stmt.close(); 459 } catch (SQLException x) { throw x; } 460 } 461 462 return rows; 463 } 464 465 } 466 | Popular Tags |