1 6 7 package com.quikj.application.communicator.applications.webtalk.model; 8 9 import java.sql.*; 10 import java.util.*; 11 12 16 public class GroupTable 17 { 18 public static final String GROUP_TABLE_NAME = "group_tbl"; 20 21 public static final String GROUPNAME = "groupid"; 23 public static final String DOMAIN = "domain"; 24 public static final String FLAGS = "flags"; 25 public static final String MEMBERLOGIN_NOTIFICATION = "memberlogin_notif"; 26 public static final String MEMBERBUSY_NOTIFICATION = "memberbusy_notif"; 27 public static final String OWNERLOGIN_NOTIFICATION = "ownerlogin_notif"; 28 public static final String OWNERBUSY_NOTIFICATION = "ownerbusy_notif"; 29 30 public static final String GROUP_OWNER_TABLE_NAME = "group_owner_tbl"; 32 33 public static final String USERNAME = UserTable.USERNAME; 35 37 public static final String GROUP_MEMBER_TABLE_NAME = "group_member_tbl"; 39 40 44 45 private Connection connection; 46 47 48 private String errorMessage; 49 50 51 public GroupTable() 52 { 53 } 54 55 59 public void setConnection(Connection connection) 60 { 61 this.connection = connection; 62 } 63 64 68 public String getErrorMessage() 69 { 70 return this.errorMessage; 71 } 72 73 public GroupElement query(String groupname) 74 { 75 return query(groupname, null); 76 } 77 78 public GroupElement query(String groupname, String domain_constraint) 79 { 80 StringBuffer cmd = new StringBuffer ("select " 81 + DOMAIN 82 + ", " 83 + MEMBERLOGIN_NOTIFICATION 84 + ", " 85 + MEMBERBUSY_NOTIFICATION 86 + ", " 87 + OWNERLOGIN_NOTIFICATION 88 + ", " 89 + OWNERBUSY_NOTIFICATION 90 + " from " 91 + GROUP_TABLE_NAME 92 + " where "); 93 94 if (domain_constraint != null) 95 { 96 cmd.append(DOMAIN + "= ? and "); 97 } 98 99 cmd.append(GROUPNAME 100 + " = ?"); 101 102 try 103 { 104 Statement stmt = connection.createStatement(); 105 stmt.executeUpdate("use webtalk"); 106 107 PreparedStatement pstmt = connection.prepareStatement(cmd.toString()); 108 int index = 1; 109 if (domain_constraint != null) 110 { 111 pstmt.setString(index++, domain_constraint); 112 } 113 114 pstmt.setString(index++, groupname); 115 ResultSet rs = pstmt.executeQuery(); 116 if (!rs.first()) 117 { 118 errorMessage = null; 120 return null; 121 } 122 123 GroupElement groupinfo = new GroupElement(); 124 groupinfo.setDomain(rs.getString(1)); 125 groupinfo.setMemberLoginNotificationControl(rs.getInt(2)); 126 groupinfo.setMemberBusyNotificationControl(rs.getInt(3)); 127 groupinfo.setOwnerLoginNotificationControl(rs.getInt(4)); 128 groupinfo.setOwnerBusyNotificationControl(rs.getInt(5)); 129 130 groupinfo.setName(groupname); 131 132 return groupinfo; 133 } 134 catch (SQLException ex) 135 { 136 errorMessage = "SQLException: " + ex.getMessage() + ", SQL command: " + cmd; 137 return null; 138 } 139 } 140 141 public ArrayList list() 142 { 143 return list(null); 144 } 145 146 public ArrayList list(String domain_constraint) 147 { 149 StringBuffer cmd = new StringBuffer ("select " 150 + GROUPNAME 151 + " from " 152 + GROUP_TABLE_NAME); 153 154 if (domain_constraint != null) 155 { 156 cmd.append(" where " + DOMAIN + "= ?"); 157 } 158 159 cmd.append(" order by " 160 + GROUPNAME); 161 162 try 163 { 164 Statement stmt = connection.createStatement(); 165 stmt.executeUpdate("use webtalk"); 166 167 PreparedStatement pstmt = connection.prepareStatement(cmd.toString()); 168 if (domain_constraint != null) 169 { 170 pstmt.setString(1, domain_constraint); 171 } 172 173 ResultSet rs = pstmt.executeQuery(); 174 175 ArrayList list = new ArrayList(); 176 while (rs.next() == true) 177 { 178 list.add(rs.getString(1)); 179 } 180 181 return list; 182 } 183 catch (SQLException ex) 184 { 185 errorMessage = "SQLException: " + ex.getMessage() + ", SQL command: " + cmd; 186 return null; 187 } 188 } 189 190 public ArrayList listAllByDomain() 191 { 193 String cmd = "select " 194 + GROUPNAME 195 + ',' 196 + DOMAIN 197 + " from " 198 + GROUP_TABLE_NAME 199 + " order by 2, 1"; 200 201 try 202 { 203 Statement stmt = connection.createStatement(); 204 stmt.executeUpdate("use webtalk"); 205 206 ResultSet rs = stmt.executeQuery(cmd); 207 208 ArrayList list = new ArrayList(); 209 while (rs.next() == true) 210 { 211 GroupElement ele = new GroupElement(); 212 ele.setName(rs.getString(1)); 213 ele.setDomain(rs.getString(2)); 214 215 list.add(ele); 216 } 217 218 return list; 219 } 220 catch (SQLException ex) 221 { 222 errorMessage = "SQLException: " + ex.getMessage() + ", SQL command: " + cmd; 223 return null; 224 } 225 } 226 227 public ArrayList listDomains() 228 { 230 String cmd = "select distinct " 231 + DOMAIN 232 + " from " 233 + GROUP_TABLE_NAME 234 + " order by 1"; 235 236 try 237 { 238 Statement stmt = connection.createStatement(); 239 stmt.executeUpdate("use webtalk"); 240 241 ResultSet rs = stmt.executeQuery(cmd); 242 243 ArrayList list = new ArrayList(); 244 while (rs.next() == true) 245 { 246 list.add(rs.getString(1)); 247 } 248 249 return list; 250 } 251 catch (SQLException ex) 252 { 253 errorMessage = "SQLException: " + ex.getMessage() + ", SQL command: " + cmd; 254 return null; 255 } 256 } 257 258 public boolean create(GroupElement group) 259 { 260 String cmd = "insert into " 261 + GROUP_TABLE_NAME 262 + " values (?, ?, ?, ?, ?, ?, ?)"; 263 264 try 265 { 266 Statement stmt = connection.createStatement(); 267 stmt.executeUpdate("use webtalk"); 268 269 PreparedStatement pstmt = connection.prepareStatement(cmd); 270 pstmt.setString(1, group.getName()); 271 pstmt.setString(2, group.getDomain() == null ? "" : group.getDomain()); 272 pstmt.setString(3, "0"); 273 pstmt.setInt(4, group.getMemberLoginNotificationControl()); 274 pstmt.setInt(5, group.getMemberBusyNotificationControl()); 275 pstmt.setInt(6, group.getOwnerLoginNotificationControl()); 276 pstmt.setInt(7, group.getOwnerBusyNotificationControl()); 277 278 int count = pstmt.executeUpdate(); 279 if (count == 0) 280 { 281 errorMessage = "Group create failed: no rows affected, SQL command: " + cmd; 282 return false; 283 } 284 } 285 catch (SQLException ex) 286 { 287 errorMessage = "SQLException: " + ex.getMessage() + ", SQL command: " + cmd; 288 return false; 289 } 290 291 return true; 292 } 293 294 public boolean modify(GroupElement group) 295 { 296 return modify(group, null); 297 } 298 299 public boolean modify(GroupElement group, String domain_constraint) 300 { 301 StringBuffer cmd = new StringBuffer ("update " 302 + GROUP_TABLE_NAME 303 + " set " 304 + DOMAIN 305 + "= ?, " 306 + MEMBERLOGIN_NOTIFICATION 307 + "= ?, " 308 + MEMBERBUSY_NOTIFICATION 309 + "= ?, " 310 + OWNERLOGIN_NOTIFICATION 311 + "= ?, " 312 + OWNERBUSY_NOTIFICATION 313 + "= ? " 314 + " where "); 315 316 if (domain_constraint != null) 317 { 318 cmd.append(DOMAIN + "= ? and "); 319 } 320 321 cmd.append(GROUPNAME 322 + " = ?"); 323 try 324 { 325 Statement stmt = connection.createStatement(); 326 stmt.executeUpdate("use webtalk"); 327 328 PreparedStatement pstmt = connection.prepareStatement(cmd.toString()); 329 int index = 1; 330 331 pstmt.setString(index++, group.getDomain()); 332 pstmt.setInt(index++, group.getMemberLoginNotificationControl()); 333 pstmt.setInt(index++, group.getMemberBusyNotificationControl()); 334 pstmt.setInt(index++, group.getOwnerLoginNotificationControl()); 335 pstmt.setInt(index++, group.getOwnerBusyNotificationControl()); 336 337 if (domain_constraint != null) 338 { 339 pstmt.setString(index++, domain_constraint); 340 } 341 pstmt.setString(index++, group.getName()); 342 int count = pstmt.executeUpdate(); 343 if (count == 0) 344 { 345 errorMessage = "Database not updated - the group was not found"; 346 return false; 347 } 348 } 349 catch (SQLException ex) 350 { 351 errorMessage = "SQLException: " + ex.getMessage() + ", SQL command: " + cmd; 352 return false; 353 } 354 355 return true; 356 } 357 358 public boolean delete(String groupname) 359 { 360 return delete(groupname, null); 361 } 362 363 public boolean delete(String groupname, String domain_constraint) 364 { 365 StringBuffer cmd = new StringBuffer ("delete from " 366 + GROUP_TABLE_NAME 367 + " where "); 368 369 if (domain_constraint != null) 370 { 371 cmd.append(DOMAIN + "= ? and "); 372 } 373 374 cmd.append(GROUPNAME 375 + " = ?"); 376 377 try 378 { 379 Statement stmt = connection.createStatement(); 380 stmt.executeUpdate("use webtalk"); 381 382 PreparedStatement pstmt = connection.prepareStatement(cmd.toString()); 383 int index = 1; 384 if (domain_constraint != null) 385 { 386 pstmt.setString(index++, domain_constraint); 387 } 388 pstmt.setString(index++, groupname); 389 390 int count = pstmt.executeUpdate(); 391 if (count == 0) 392 { 393 errorMessage = null; 394 return false; 395 } 396 397 String sql = "delete from " 398 + GROUP_OWNER_TABLE_NAME 399 + " where " 400 + GROUPNAME 401 + " = ?"; 402 403 pstmt = connection.prepareStatement(sql); 404 pstmt.setString(1, groupname); 405 pstmt.executeUpdate(); 406 407 sql = "delete from " 408 + GROUP_MEMBER_TABLE_NAME 409 + " where " 410 + GROUPNAME 411 + " = ?"; 412 pstmt = connection.prepareStatement(sql); 413 pstmt.setString(1, groupname); 414 415 pstmt.executeUpdate(); 416 417 sql = "delete from " 418 + CannedMessageTable.TABLE_NAME 419 + " where " 420 + CannedMessageTable.GROUP 421 + " = ?"; 422 pstmt = connection.prepareStatement(sql); 423 pstmt.setString(1, groupname); 424 425 pstmt.executeUpdate(); 426 427 } 428 catch (SQLException ex) 429 { 430 errorMessage = "SQLException: " + ex.getMessage() + ", SQL command: " + cmd; 431 return false; 432 } 433 434 return true; 435 } 436 437 438 } 439 | Popular Tags |