1 6 7 package org.manentia.kasai.group; 8 9 import com.koala.commons.log.Log; 10 import com.koala.commons.persistence.DBUtil; 11 import java.sql.*; 12 import java.util.ArrayList ; 13 import java.util.Arrays ; 14 import java.util.Collection ; 15 import java.util.logging.Level ; 16 import org.apache.commons.lang.StringUtils; 17 import org.apache.commons.lang.exception.ExceptionUtils; 18 import org.manentia.kasai.Group; 19 import org.manentia.kasai.Operative; 20 import org.manentia.kasai.Role; 21 import org.manentia.kasai.User; 22 import org.manentia.kasai.exceptions.AlreadyExistsException; 23 import org.manentia.kasai.exceptions.DataAccessException; 24 import org.manentia.kasai.exceptions.DoesntExistsException; 25 import org.manentia.kasai.exceptions.InvalidAttributesException; 26 import org.manentia.kasai.user.UserHandler; 27 import org.manentia.kasai.util.Constants; 28 29 33 public class JDBCMySQLGroupDAO implements GroupDAO { 34 35 36 public JDBCMySQLGroupDAO() { 37 } 38 39 public void addUserToGroup(String login, String group) throws DoesntExistsException,DataAccessException{ 40 Connection con = null; 41 if (UserHandler.getInstance().read(login,true) == null){ 42 Log.getInstance(Constants.PROPERTY_FILE).write (this.getClass().getName(), "apply", "User doesn't exist",Level.WARNING); 43 throw new DoesntExistsException(User.class.getName() + ".userDoesntExist"); 44 } 45 if (this.read(group) == null){ 46 Log.getInstance(Constants.PROPERTY_FILE).write (this.getClass().getName(), "apply", "Group doesn't exist",Level.WARNING); 47 throw new DoesntExistsException(Group.class.getName() + ".groupDoesntExist"); 48 } 49 if (UserHandler.getInstance().list(login,null,null,null,-1,null,group).size()==0){ 50 try{ 51 String sql = "insert into kasai_users_groups (id_user,id_group) values ('" + org.apache.commons.lang.StringEscapeUtils.escapeSql(login) + "','" + org.apache.commons.lang.StringEscapeUtils.escapeSql(group) + "')"; 52 con = DBUtil.getConnection(Constants.DATABASE_SOURCE, Constants.PROPERTY_FILE); 53 con.createStatement().executeUpdate(sql); 54 }catch (SQLException sqle){ 55 Log.getInstance(Constants.PROPERTY_FILE).write (this.getClass().getName(), "addUserToGroup", ExceptionUtils.getStackTrace(sqle),Level.SEVERE); 56 throw new DataAccessException(sqle); 57 }finally{ 58 try{ 59 con.close(); 60 }catch(Exception e){} 61 } 62 } 63 } 64 65 public boolean checkUserBelongsToGroup(String user, String group) throws DataAccessException{ 66 Connection con = null; 67 String sql; 68 ResultSet rs = null; 69 try{ 70 sql = "SELECT AU.* FROM kasai_users AU"; 71 sql += ", kasai_users_groups AUG WHERE AUG.id_user=AU.id AND AUG.id_group='" + org.apache.commons.lang.StringEscapeUtils.escapeSql(group) +"'"; 72 sql += " AND AU.id = '" + org.apache.commons.lang.StringEscapeUtils.escapeSql(user) +"'"; 73 con = DBUtil.getConnection(Constants.DATABASE_SOURCE, Constants.PROPERTY_FILE); 74 rs = con.createStatement().executeQuery(sql); 75 if (rs.next()){ 76 return true; 77 } 78 else{ 79 return false; 80 } 81 82 }catch (SQLException sqle){ 83 Log.getInstance(Constants.PROPERTY_FILE).write (this.getClass().getName(), "checkUserBelongsToGroup", ExceptionUtils.getStackTrace(sqle),Level.SEVERE); 84 throw new DataAccessException(sqle); 85 }finally{ 86 try{ 87 rs.close(); 88 }catch(Exception e){} 89 try{ 90 con.close(); 91 }catch(Exception e){} 92 } 93 94 } 95 96 public void create(String id, String description, boolean blocked) throws InvalidAttributesException, AlreadyExistsException, DataAccessException{ 97 98 Connection con = null; 99 String sql; 100 Group g = null; 101 try{ 102 103 g = new Group(); 104 g.setBlocked(blocked); 105 g.setDescription(description); 106 g.setId(StringUtils.defaultString(id).trim()); 107 g.validate(); 108 109 g = this.read(id); 110 if (g != null){ 111 Log.getInstance(Constants.PROPERTY_FILE).write (this.getClass().getName(),"create","Group name already exist",Level.WARNING); 112 throw new AlreadyExistsException(this.getClass().getName() + ".groupAlreadyExist"); 113 } 114 sql = "INSERT INTO kasai_groups (id, blocked, description) VALUES (?,?,?)"; 115 con = DBUtil.getConnection(Constants.DATABASE_SOURCE, Constants.PROPERTY_FILE); 116 PreparedStatement stm = con.prepareStatement(sql); 117 stm.setString(1,id); 118 if (blocked){ 119 stm.setInt(2, 1); 120 }else{ 121 stm.setInt(2, 0); 122 } 123 stm.setString(3, description); 124 stm.executeUpdate(); 125 }catch (SQLException sqle){ 126 Log.getInstance(Constants.PROPERTY_FILE).write (this.getClass().getName(), "create", ExceptionUtils.getStackTrace(sqle),Level.SEVERE); 127 throw new DataAccessException(sqle); 128 }finally{ 129 try{ 130 131 con.close(); 132 }catch(Exception e){} 133 } 134 } 135 136 public void delete(String group) throws DataAccessException{ 137 Connection con = null; 138 String sql; 139 try{ 140 if (StringUtils.isNotEmpty(group)){ 141 sql = "DELETE FROM kasai_groups WHERE id='" + org.apache.commons.lang.StringEscapeUtils.escapeSql(group) + "'"; 142 con = DBUtil.getConnection(Constants.DATABASE_SOURCE, Constants.PROPERTY_FILE); 143 con.createStatement().executeUpdate(sql); 144 } 145 }catch (SQLException sqle){ 146 Log.getInstance(Constants.PROPERTY_FILE).write (this.getClass().getName(), "delete", ExceptionUtils.getStackTrace(sqle),Level.SEVERE); 147 throw new DataAccessException(sqle); 148 }finally{ 149 try{ 150 con.close(); 151 }catch(Exception e){} 152 } 153 154 } 155 156 public void deleteUserFromGroup(String login, String group) throws DataAccessException{ 157 Connection con = null; 158 if (StringUtils.isNotEmpty(login) && StringUtils.isNotEmpty(group)){ 159 try{ 160 String sql = "delete from kasai_users_groups where id_user='" + org.apache.commons.lang.StringEscapeUtils.escapeSql(login) + "' and id_group='" + org.apache.commons.lang.StringEscapeUtils.escapeSql(group) + "'"; 161 con = DBUtil.getConnection(Constants.DATABASE_SOURCE, Constants.PROPERTY_FILE); 162 con.createStatement().executeUpdate(sql); 163 }catch (SQLException sqle){ 164 Log.getInstance(Constants.PROPERTY_FILE).write (this.getClass().getName(), "deleteUserFromGroup", ExceptionUtils.getStackTrace(sqle),Level.SEVERE); 165 throw new DataAccessException(sqle); 166 }finally { 167 try{ 168 con.close(); 169 }catch(Exception e){} 170 } 171 } 172 } 173 174 public Collection list(String idGroup, String description, int blocked, int system, String login) throws DataAccessException{ 175 Connection con = null; 176 String sql; 177 ResultSet rs = null; 178 Group g = null; 179 ArrayList groups = new ArrayList (); 180 try{ 181 sql = "SELECT AG.* FROM kasai_groups AG "; 182 if (StringUtils.isNotEmpty(login)){ 183 sql += ", kasai_users_groups AUG WHERE AUG.id_group=AG.id AND AUG.id_user='" + org.apache.commons.lang.StringEscapeUtils.escapeSql(login) +"'"; 184 } 185 else{ 186 sql += " WHERE AG.id <> ''"; 187 } 188 if (StringUtils.isNotEmpty(idGroup)){ 189 sql += " AND AG.id LIKE '%" + org.apache.commons.lang.StringEscapeUtils.escapeSql(idGroup) +"%'"; 190 } 191 if (blocked != -1){ 192 sql += " AND AG.blocked = " + blocked; 193 } 194 if (system != -1){ 195 sql += " AND AG.system = " + system; 196 } 197 if (StringUtils.isNotEmpty(description)){ 198 sql += " AND AG.description LIKE '%" + org.apache.commons.lang.StringEscapeUtils.escapeSql(description) +"%'"; 199 } 200 sql += " order by AG.id "; 201 202 con = DBUtil.getConnection(Constants.DATABASE_SOURCE, Constants.PROPERTY_FILE); 203 rs = con.createStatement().executeQuery(sql); 204 while (rs.next()){ 205 g = new Group (rs); 206 groups.add(g); 207 } 208 return groups; 209 }catch (SQLException sqle){ 210 Log.getInstance(Constants.PROPERTY_FILE).write (this.getClass().getName(), "list", ExceptionUtils.getStackTrace(sqle),Level.SEVERE); 211 throw new DataAccessException(sqle); 212 }finally{ 213 try{ 214 rs.close(); 215 }catch(Exception e){} 216 try{ 217 con.close(); 218 }catch(Exception e){} 219 } 220 221 } 222 223 public Collection listUsersNotInGroup(String group) throws DataAccessException { 224 225 Connection con = null; 226 String sql; 227 ResultSet rs = null; 228 User u = null; 229 ArrayList members = new ArrayList (); 230 ArrayList users = new ArrayList (); 231 232 try { 233 sql = "SELECT AU.* FROM kasai_users AU,kasai_users_groups AUG WHERE AUG.id_user=AU.id AND AUG.id_group='" + org.apache.commons.lang.StringEscapeUtils.escapeSql(group) + "'"; 234 sql += " order by AU.last_name, AU.first_name, AU.id "; 235 con = DBUtil.getConnection(Constants.DATABASE_SOURCE, Constants.PROPERTY_FILE); 236 rs = con.createStatement().executeQuery(sql); 237 238 while (rs.next()) { 239 240 u = new User(rs); 241 members.add(u); 242 } 243 244 sql = "SELECT * FROM kasai_users"; 245 sql += " order by last_name, first_name, id "; 246 con = DBUtil.getConnection(Constants.DATABASE_SOURCE, Constants.PROPERTY_FILE); 247 rs = con.createStatement().executeQuery(sql); 248 249 while (rs.next()) { 250 u = new User(rs); 251 if (!members.contains(u)) { 252 users.add(u); 253 } 254 } 255 256 return users; 257 }catch (SQLException sqle){ 258 Log.getInstance(Constants.PROPERTY_FILE).write (this.getClass().getName(), "listUsersNotInGroup", ExceptionUtils.getStackTrace(sqle),Level.SEVERE); 259 throw new DataAccessException(sqle); 260 } finally { 261 262 try { 263 264 rs.close(); 265 } catch (Exception e) {} 266 267 try { 268 269 con.close(); 270 } catch (Exception e) {} 271 } 272 } 273 274 public Group read(String group) throws DataAccessException{ 275 Connection con = null; 276 String sql; 277 ResultSet rs = null; 278 Group g = null; 279 try{ 280 if (StringUtils.isNotEmpty(group)){ 281 sql = "SELECT * FROM kasai_groups WHERE id='" + org.apache.commons.lang.StringEscapeUtils.escapeSql(group) + "'"; 282 con = DBUtil.getConnection(Constants.DATABASE_SOURCE, Constants.PROPERTY_FILE); 283 rs = con.createStatement().executeQuery(sql); 284 if (rs.next()){ 285 g = new Group (rs); 286 } 287 } 288 return g; 289 }catch (SQLException sqle){ 290 Log.getInstance(Constants.PROPERTY_FILE).write (this.getClass().getName(), "read", ExceptionUtils.getStackTrace(sqle),Level.SEVERE); 291 throw new DataAccessException(sqle); 292 }finally{ 293 try{ 294 rs.close(); 295 }catch(Exception e){} 296 try{ 297 con.close(); 298 }catch(Exception e){} 299 } 300 301 } 302 303 public void update( 304 String id, 305 boolean blocked, 306 String description 307 ) throws InvalidAttributesException, DataAccessException { 308 309 Connection con = null; 310 String sql; 311 Group g = null; 312 313 try { 314 315 g = new Group(); 316 g.setBlocked(blocked); 317 g.setDescription(description); 318 g.setId(id); 319 g.validate(); 320 321 sql = "UPDATE kasai_groups set blocked=?, description=? where id=?"; 322 con = DBUtil.getConnection(Constants.DATABASE_SOURCE, Constants.PROPERTY_FILE); 323 324 PreparedStatement stm = con.prepareStatement(sql); 325 326 if (blocked) { 327 328 stm.setInt(1, 1); 329 } else { 330 331 stm.setInt(1, 0); 332 } 333 334 stm.setString(2, description); 335 stm.setString(3, id); 336 stm.executeUpdate(); 337 }catch (SQLException sqle){ 338 Log.getInstance(Constants.PROPERTY_FILE).write (this.getClass().getName(), "update", ExceptionUtils.getStackTrace(sqle),Level.SEVERE); 339 throw new DataAccessException(sqle); 340 } finally { 341 342 try { 343 344 con.close(); 345 } catch (Exception e) {} 346 } 347 } 348 349 public void update( 350 String id, 351 boolean blocked, 352 String description, 353 String [] members 354 ) throws InvalidAttributesException, DataAccessException { 355 356 Connection con = null; 357 Group g = null; 358 ResultSet rs = null; 359 String sql; 360 361 try { 362 363 g = new Group(); 364 g.setBlocked(blocked); 365 g.setDescription(description); 366 g.setId(id); 367 g.validate(); 368 369 sql = "UPDATE kasai_groups set blocked=?, description=? where id=?"; 370 con = DBUtil.getConnection(Constants.DATABASE_SOURCE, Constants.PROPERTY_FILE); 371 372 PreparedStatement stm = con.prepareStatement(sql); 373 374 if (blocked) { 375 376 stm.setInt(1, 1); 377 } else { 378 379 stm.setInt(1, 0); 380 } 381 382 stm.setString(2, description); 383 stm.setString(3, id); 384 stm.executeUpdate(); 385 386 if (members != null) { 387 388 String login = null; 389 390 ArrayList newMembers = new ArrayList (Arrays.asList(members)); 391 392 ArrayList currentMembers = new ArrayList (); 393 sql = "SELECT id_user FROM kasai_users_groups WHERE id_group='" + org.apache.commons.lang.StringEscapeUtils.escapeSql(id) + "'"; 394 rs = con.createStatement().executeQuery(sql); 395 396 while (rs.next()) { 397 398 currentMembers.add(StringUtils.defaultString(rs.getString("id_user"))); 399 } 400 401 for (int i = 0; i < members.length; i++) { 402 403 login = members[i]; 404 405 if (!currentMembers.contains(login)) { 406 407 sql = "INSERT INTO kasai_users_groups (id_user,id_group) VALUES ('" + org.apache.commons.lang.StringEscapeUtils.escapeSql(login) + "','" + org.apache.commons.lang.StringEscapeUtils.escapeSql(id) + 408 "')"; 409 con = DBUtil.getConnection(Constants.DATABASE_SOURCE, Constants.PROPERTY_FILE); 410 con.createStatement().executeUpdate(sql); 411 } 412 } 413 414 for (int i = 0; i < currentMembers.size(); i++) { 415 416 login = (String ) currentMembers.get(i); 417 418 if (!newMembers.contains(login)) { 419 420 sql = "DELETE FROM kasai_users_groups WHERE id_user='" + org.apache.commons.lang.StringEscapeUtils.escapeSql(login) + "' AND id_group='" + org.apache.commons.lang.StringEscapeUtils.escapeSql(id) + 421 "'"; 422 con = DBUtil.getConnection(Constants.DATABASE_SOURCE, Constants.PROPERTY_FILE); 423 con.createStatement().executeUpdate(sql); 424 } 425 } 426 } 427 }catch (SQLException sqle){ 428 Log.getInstance(Constants.PROPERTY_FILE).write (this.getClass().getName(), "update", ExceptionUtils.getStackTrace(sqle),Level.SEVERE); 429 throw new DataAccessException(sqle); 430 } finally { 431 432 try { 433 434 con.close(); 435 } catch (Exception e) {} 436 } 437 } 438 } 439 | Popular Tags |