001: /*
002: * JDBCMySQLUserDAO.java
003: *
004: * Created on 28 de marzo de 2005, 13:46
005: */
006:
007: package org.manentia.kasai.group;
008:
009: import java.io.IOException;
010: import java.sql.Connection;
011: import java.sql.PreparedStatement;
012: import java.sql.ResultSet;
013: import java.sql.SQLException;
014: import java.util.ArrayList;
015: import java.util.Arrays;
016: import java.util.Collection;
017: import java.util.List;
018:
019: import javax.xml.parsers.FactoryConfigurationError;
020: import javax.xml.parsers.ParserConfigurationException;
021:
022: import org.apache.commons.lang.StringUtils;
023: import org.manentia.kasai.Constants;
024: import org.manentia.kasai.Group;
025: import org.manentia.kasai.User;
026: import org.manentia.kasai.exceptions.AlreadyExistsException;
027: import org.manentia.kasai.exceptions.DataAccessException;
028: import org.manentia.kasai.exceptions.DoesntExistsException;
029: import org.manentia.kasai.exceptions.InvalidAttributesException;
030: import org.manentia.kasai.user.UserHandler;
031: import org.xml.sax.SAXException;
032:
033: import com.manentia.commons.log.Log;
034: import com.manentia.commons.persistence.DBUtil;
035: import com.manentia.commons.xml.XMLException;
036:
037: /**
038: *
039: * @author rzuasti
040: */
041: public class JDBCANSISQLGroupDAO implements GroupDAO {
042:
043: /** Creates a new instance of JDBCMySQLUserDAO */
044: public JDBCANSISQLGroupDAO() {
045: }
046:
047: public void addUserToGroup(String login, String group)
048: throws DoesntExistsException, DataAccessException,
049: XMLException {
050: Connection con = null;
051: if (UserHandler.getInstance().read(login, true) == null) {
052: Log.write("User doesn't exist", Log.WARN, "addUserToGroup",
053: JDBCANSISQLGroupDAO.class);
054:
055: throw new DoesntExistsException(User.class.getName()
056: + ".userDoesntExist");
057: }
058: if (this .read(group) == null) {
059: Log.write("Group doesn't exist", Log.WARN,
060: "addUserToGroup", JDBCANSISQLGroupDAO.class);
061:
062: throw new DoesntExistsException(Group.class.getName()
063: + ".groupDoesntExist");
064: }
065: if (UserHandler.getInstance().list(login, null, null, null, -1,
066: null, group).size() == 0) {
067: try {
068: String sql = "insert into kasai_users_groups (id_user,id_group) values ('"
069: + org.apache.commons.lang.StringEscapeUtils
070: .escapeSql(login)
071: + "','"
072: + org.apache.commons.lang.StringEscapeUtils
073: .escapeSql(group) + "')";
074: con = DBUtil.getConnection(Constants.DATABASE_SOURCE,
075: Constants.CONFIG_PROPERTY_FILE);
076: con.createStatement().executeUpdate(sql);
077: } catch (SQLException sqle) {
078: Log.write("SQL Error", sqle, Log.ERROR,
079: "addUserToGroup", JDBCANSISQLGroupDAO.class);
080:
081: throw new DataAccessException(sqle);
082: } finally {
083: try {
084: con.close();
085: } catch (Exception e) {
086: }
087: }
088: }
089: }
090:
091: public boolean checkUserBelongsToGroup(String user, String group)
092: throws DataAccessException {
093: Connection con = null;
094: String sql;
095: ResultSet rs = null;
096: try {
097: sql = "SELECT AU.* FROM kasai_users AU";
098: sql += ", kasai_users_groups AUG WHERE AUG.id_user=AU.id AND AUG.id_group='"
099: + org.apache.commons.lang.StringEscapeUtils
100: .escapeSql(group) + "'";
101: sql += " AND AU.id = '"
102: + org.apache.commons.lang.StringEscapeUtils
103: .escapeSql(user) + "'";
104: con = DBUtil.getConnection(Constants.DATABASE_SOURCE,
105: Constants.CONFIG_PROPERTY_FILE);
106: rs = con.createStatement().executeQuery(sql);
107: if (rs.next()) {
108: return true;
109: } else {
110: return false;
111: }
112:
113: } catch (SQLException sqle) {
114: Log.write("SQL Error", sqle, Log.ERROR,
115: "checkUserBelongsToGroup",
116: JDBCANSISQLGroupDAO.class);
117:
118: throw new DataAccessException(sqle);
119: } finally {
120: try {
121: rs.close();
122: } catch (Exception e) {
123: }
124: try {
125: con.close();
126: } catch (Exception e) {
127: }
128: }
129:
130: }
131:
132: public void create(Group group) throws InvalidAttributesException,
133: AlreadyExistsException, DataAccessException, XMLException {
134:
135: Connection con = null;
136: String sql;
137: try {
138:
139: group.validate();
140:
141: if (this .read(group.getId()) != null) {
142: Log.write("Group name already exist", Log.WARN,
143: "create", JDBCANSISQLGroupDAO.class);
144:
145: throw new AlreadyExistsException(this .getClass()
146: .getName()
147: + ".groupAlreadyExist");
148: }
149: sql = "INSERT INTO kasai_groups (id, blocked, description, data) VALUES (?,?,?,?)";
150: con = DBUtil.getConnection(Constants.DATABASE_SOURCE,
151: Constants.CONFIG_PROPERTY_FILE);
152: PreparedStatement stm = con.prepareStatement(sql);
153: stm.setString(1, group.getId());
154: if (group.getBlocked()) {
155: stm.setInt(2, 1);
156: } else {
157: stm.setInt(2, 0);
158: }
159: stm.setString(3, group.getDescription());
160: stm.setString(4, group.getAttributesXML());
161: stm.executeUpdate();
162: } catch (SQLException sqle) {
163: Log.write("SQL Error", sqle, Log.ERROR, "create",
164: JDBCANSISQLGroupDAO.class);
165:
166: throw new DataAccessException(sqle);
167: } catch (ParserConfigurationException e) {
168: Log.write("XML error saving group", e, Log.ERROR, "create",
169: JDBCANSISQLGroupDAO.class);
170:
171: throw new XMLException(e);
172: } catch (FactoryConfigurationError e) {
173: Log.write("XML error saving group", e, Log.ERROR, "create",
174: JDBCANSISQLGroupDAO.class);
175:
176: throw new XMLException(e);
177: } finally {
178: try {
179:
180: con.close();
181: } catch (Exception e) {
182: }
183: }
184: }
185:
186: public void delete(String group) throws DataAccessException {
187: Connection con = null;
188: String sql;
189: try {
190: if (StringUtils.isNotEmpty(group)) {
191: sql = "DELETE FROM kasai_groups WHERE id='"
192: + org.apache.commons.lang.StringEscapeUtils
193: .escapeSql(group) + "'";
194: con = DBUtil.getConnection(Constants.DATABASE_SOURCE,
195: Constants.CONFIG_PROPERTY_FILE);
196: con.createStatement().executeUpdate(sql);
197: }
198: } catch (SQLException sqle) {
199: Log.write("SQL Error", sqle, Log.ERROR, "delete",
200: JDBCANSISQLGroupDAO.class);
201:
202: throw new DataAccessException(sqle);
203: } finally {
204: try {
205: con.close();
206: } catch (Exception e) {
207: }
208: }
209:
210: }
211:
212: public void deleteUserFromGroup(String login, String group)
213: throws DataAccessException {
214: Connection con = null;
215: if (StringUtils.isNotEmpty(login)
216: && StringUtils.isNotEmpty(group)) {
217: try {
218: String sql = "delete from kasai_users_groups where id_user='"
219: + org.apache.commons.lang.StringEscapeUtils
220: .escapeSql(login)
221: + "' and id_group='"
222: + org.apache.commons.lang.StringEscapeUtils
223: .escapeSql(group) + "'";
224: con = DBUtil.getConnection(Constants.DATABASE_SOURCE,
225: Constants.CONFIG_PROPERTY_FILE);
226: con.createStatement().executeUpdate(sql);
227: } catch (SQLException sqle) {
228: Log.write("SQL Error", sqle, Log.ERROR,
229: "deleteUserFromGroup",
230: JDBCANSISQLGroupDAO.class);
231:
232: throw new DataAccessException(sqle);
233: } finally {
234: try {
235: con.close();
236: } catch (Exception e) {
237: }
238: }
239: }
240: }
241:
242: public List list(String idGroup, String description, int blocked,
243: int system, String login) throws DataAccessException,
244: XMLException {
245: Connection con = null;
246: String sql;
247: ResultSet rs = null;
248: Group g = null;
249: ArrayList groups = new ArrayList();
250: try {
251: sql = "SELECT AG.* FROM kasai_groups AG ";
252: if (StringUtils.isNotEmpty(login)) {
253: sql += ", kasai_users_groups AUG WHERE AUG.id_group=AG.id AND AUG.id_user='"
254: + org.apache.commons.lang.StringEscapeUtils
255: .escapeSql(login) + "'";
256: } else {
257: sql += " WHERE AG.id <> ''";
258: }
259: if (StringUtils.isNotEmpty(idGroup)) {
260: sql += " AND AG.id LIKE '%"
261: + org.apache.commons.lang.StringEscapeUtils
262: .escapeSql(idGroup) + "%'";
263: }
264: if (blocked != -1) {
265: sql += " AND AG.blocked = " + blocked;
266: }
267: if (system != -1) {
268: sql += " AND AG.system = " + system;
269: }
270: if (StringUtils.isNotEmpty(description)) {
271: sql += " AND AG.description LIKE '%"
272: + org.apache.commons.lang.StringEscapeUtils
273: .escapeSql(description) + "%'";
274: }
275: sql += " order by AG.id ";
276:
277: con = DBUtil.getConnection(Constants.DATABASE_SOURCE,
278: Constants.CONFIG_PROPERTY_FILE);
279: rs = con.createStatement().executeQuery(sql);
280: while (rs.next()) {
281: g = new Group(rs);
282: groups.add(g);
283: }
284: return groups;
285: } catch (SQLException sqle) {
286: Log.write("SQL Error", sqle, Log.ERROR, "list",
287: JDBCANSISQLGroupDAO.class);
288:
289: throw new DataAccessException(sqle);
290: } catch (SAXException e) {
291: Log.write("XML error reading group attributes", e,
292: Log.ERROR, "list", JDBCANSISQLGroupDAO.class);
293:
294: throw new XMLException(e);
295: } catch (IOException e) {
296: Log.write("XML error reading group attributes", e,
297: Log.ERROR, "list", JDBCANSISQLGroupDAO.class);
298:
299: throw new XMLException(e);
300: } catch (ParserConfigurationException e) {
301: Log.write("XML error reading group attributes", e,
302: Log.ERROR, "list", JDBCANSISQLGroupDAO.class);
303:
304: throw new XMLException(e);
305: } catch (FactoryConfigurationError e) {
306: Log.write("XML error reading group attributes", e,
307: Log.ERROR, "list", JDBCANSISQLGroupDAO.class);
308:
309: throw new XMLException(e);
310: } finally {
311: try {
312: rs.close();
313: } catch (Exception e) {
314: }
315: try {
316: con.close();
317: } catch (Exception e) {
318: }
319: }
320:
321: }
322:
323: public Collection listUsersNotInGroup(String group)
324: throws DataAccessException, XMLException {
325:
326: Connection con = null;
327: String sql;
328: ResultSet rs = null;
329: User u = null;
330: ArrayList members = new ArrayList();
331: ArrayList users = new ArrayList();
332:
333: try {
334: sql = "SELECT AU.* FROM kasai_users AU,kasai_users_groups AUG WHERE AUG.id_user=AU.id AND AUG.id_group='"
335: + org.apache.commons.lang.StringEscapeUtils
336: .escapeSql(group) + "'";
337: sql += " order by AU.last_name, AU.first_name, AU.id ";
338: con = DBUtil.getConnection(Constants.DATABASE_SOURCE,
339: Constants.CONFIG_PROPERTY_FILE);
340: rs = con.createStatement().executeQuery(sql);
341:
342: while (rs.next()) {
343:
344: u = new User(rs);
345: members.add(u);
346: }
347:
348: sql = "SELECT * FROM kasai_users";
349: sql += " order by last_name, first_name, id ";
350: con = DBUtil.getConnection(Constants.DATABASE_SOURCE,
351: Constants.CONFIG_PROPERTY_FILE);
352: rs = con.createStatement().executeQuery(sql);
353:
354: while (rs.next()) {
355: u = new User(rs);
356: if (!members.contains(u)) {
357: users.add(u);
358: }
359: }
360:
361: return users;
362: } catch (SQLException sqle) {
363: Log.write("SQL Error", sqle, Log.ERROR,
364: "listUsersNotInGroup", JDBCANSISQLGroupDAO.class);
365:
366: throw new DataAccessException(sqle);
367: } catch (SAXException e) {
368: Log.write("XML error reading users", e, Log.ERROR,
369: "listUsersNotInGroup", JDBCANSISQLGroupDAO.class);
370:
371: throw new XMLException(e);
372: } catch (IOException e) {
373: Log.write("XML error reading users", e, Log.ERROR,
374: "listUsersNotInGroup", JDBCANSISQLGroupDAO.class);
375:
376: throw new XMLException(e);
377: } catch (ParserConfigurationException e) {
378: Log.write("XML error reading users", e, Log.ERROR,
379: "listUsersNotInGroup", JDBCANSISQLGroupDAO.class);
380:
381: throw new XMLException(e);
382: } catch (FactoryConfigurationError e) {
383: Log.write("XML error reading users", e, Log.ERROR,
384: "listUsersNotInGroup", JDBCANSISQLGroupDAO.class);
385:
386: throw new XMLException(e);
387: } finally {
388:
389: try {
390:
391: rs.close();
392: } catch (Exception e) {
393: }
394:
395: try {
396:
397: con.close();
398: } catch (Exception e) {
399: }
400: }
401: }
402:
403: public Group read(String group) throws DataAccessException,
404: XMLException {
405: Connection con = null;
406: String sql;
407: ResultSet rs = null;
408: Group g = null;
409: try {
410: if (StringUtils.isNotEmpty(group)) {
411: sql = "SELECT * FROM kasai_groups WHERE id='"
412: + org.apache.commons.lang.StringEscapeUtils
413: .escapeSql(group) + "'";
414: con = DBUtil.getConnection(Constants.DATABASE_SOURCE,
415: Constants.CONFIG_PROPERTY_FILE);
416: rs = con.createStatement().executeQuery(sql);
417: if (rs.next()) {
418: g = new Group(rs);
419: }
420: }
421: return g;
422: } catch (SQLException sqle) {
423: Log.write("SQL Error", sqle, Log.ERROR, "read",
424: JDBCANSISQLGroupDAO.class);
425:
426: throw new DataAccessException(sqle);
427: } catch (SAXException e) {
428: Log.write("XML error reading group attributes", e,
429: Log.ERROR, "read", JDBCANSISQLGroupDAO.class);
430:
431: throw new XMLException(e);
432: } catch (IOException e) {
433: Log.write("XML error reading group attributes", e,
434: Log.ERROR, "read", JDBCANSISQLGroupDAO.class);
435:
436: throw new XMLException(e);
437: } catch (ParserConfigurationException e) {
438: Log.write("XML error reading group attributes", e,
439: Log.ERROR, "read", JDBCANSISQLGroupDAO.class);
440:
441: throw new XMLException(e);
442: } catch (FactoryConfigurationError e) {
443: Log.write("XML error reading group attributes", e,
444: Log.ERROR, "read", JDBCANSISQLGroupDAO.class);
445:
446: throw new XMLException(e);
447: } finally {
448: try {
449: rs.close();
450: } catch (Exception e) {
451: }
452: try {
453: con.close();
454: } catch (Exception e) {
455: }
456: }
457:
458: }
459:
460: public void update(Group group) throws InvalidAttributesException,
461: DataAccessException, XMLException {
462:
463: Connection con = null;
464: String sql;
465: try {
466:
467: group.validate();
468:
469: sql = "UPDATE kasai_groups set blocked=?, description=?, data=? where id=?";
470: con = DBUtil.getConnection(Constants.DATABASE_SOURCE,
471: Constants.CONFIG_PROPERTY_FILE);
472:
473: PreparedStatement stm = con.prepareStatement(sql);
474:
475: if (group.getBlocked()) {
476:
477: stm.setInt(1, 1);
478: } else {
479:
480: stm.setInt(1, 0);
481: }
482:
483: stm.setString(2, group.getDescription());
484: stm.setString(3, group.getAttributesXML());
485: stm.setString(4, group.getId());
486: stm.executeUpdate();
487: } catch (SQLException sqle) {
488: Log.write("SQL Error", sqle, Log.ERROR, "update",
489: JDBCANSISQLGroupDAO.class);
490:
491: throw new DataAccessException(sqle);
492: } catch (ParserConfigurationException e) {
493: Log.write("XML Error saving group", e, Log.ERROR, "update",
494: JDBCANSISQLGroupDAO.class);
495:
496: throw new XMLException(e);
497: } catch (FactoryConfigurationError e) {
498: Log.write("XML Error saving group", e, Log.ERROR, "update",
499: JDBCANSISQLGroupDAO.class);
500:
501: throw new XMLException(e);
502: } finally {
503:
504: try {
505:
506: con.close();
507: } catch (Exception e) {
508: }
509: }
510: }
511:
512: public void update(Group group, String[] members)
513: throws InvalidAttributesException, DataAccessException,
514: XMLException {
515:
516: Connection con = null;
517: ResultSet rs = null;
518: String sql;
519:
520: try {
521:
522: group.validate();
523:
524: sql = "UPDATE kasai_groups set blocked=?, description=?, data=? where id=?";
525: con = DBUtil.getConnection(Constants.DATABASE_SOURCE,
526: Constants.CONFIG_PROPERTY_FILE);
527:
528: PreparedStatement stm = con.prepareStatement(sql);
529:
530: if (group.getBlocked()) {
531:
532: stm.setInt(1, 1);
533: } else {
534:
535: stm.setInt(1, 0);
536: }
537:
538: stm.setString(2, group.getDescription());
539: stm.setString(3, group.getAttributesXML());
540: stm.setString(4, group.getId());
541: stm.executeUpdate();
542:
543: if (members != null) {
544:
545: String login = null;
546:
547: ArrayList newMembers = new ArrayList(Arrays
548: .asList(members));
549:
550: ArrayList currentMembers = new ArrayList();
551: sql = "SELECT id_user FROM kasai_users_groups WHERE id_group='"
552: + org.apache.commons.lang.StringEscapeUtils
553: .escapeSql(group.getId()) + "'";
554: rs = con.createStatement().executeQuery(sql);
555:
556: while (rs.next()) {
557:
558: currentMembers.add(StringUtils.defaultString(rs
559: .getString("id_user")));
560: }
561:
562: for (int i = 0; i < members.length; i++) {
563:
564: login = members[i];
565:
566: if (!currentMembers.contains(login)) {
567:
568: sql = "INSERT INTO kasai_users_groups (id_user,id_group) VALUES ('"
569: + org.apache.commons.lang.StringEscapeUtils
570: .escapeSql(login)
571: + "','"
572: + org.apache.commons.lang.StringEscapeUtils
573: .escapeSql(group.getId())
574: + "')";
575: con = DBUtil.getConnection(
576: Constants.DATABASE_SOURCE,
577: Constants.CONFIG_PROPERTY_FILE);
578: con.createStatement().executeUpdate(sql);
579: }
580: }
581:
582: for (int i = 0; i < currentMembers.size(); i++) {
583:
584: login = (String) currentMembers.get(i);
585:
586: if (!newMembers.contains(login)) {
587:
588: sql = "DELETE FROM kasai_users_groups WHERE id_user='"
589: + org.apache.commons.lang.StringEscapeUtils
590: .escapeSql(login)
591: + "' AND id_group='"
592: + org.apache.commons.lang.StringEscapeUtils
593: .escapeSql(group.getId()) + "'";
594: con = DBUtil.getConnection(
595: Constants.DATABASE_SOURCE,
596: Constants.CONFIG_PROPERTY_FILE);
597: con.createStatement().executeUpdate(sql);
598: }
599: }
600: }
601: } catch (SQLException sqle) {
602: Log.write("SQL Error", sqle, Log.ERROR, "update",
603: JDBCANSISQLGroupDAO.class);
604:
605: throw new DataAccessException(sqle);
606: } catch (ParserConfigurationException e) {
607: Log.write("XML Error saving group", e, Log.ERROR, "update",
608: JDBCANSISQLGroupDAO.class);
609:
610: throw new XMLException(e);
611: } catch (FactoryConfigurationError e) {
612: Log.write("XML Error saving group", e, Log.ERROR, "update",
613: JDBCANSISQLGroupDAO.class);
614:
615: throw new XMLException(e);
616: } finally {
617:
618: try {
619:
620: con.close();
621: } catch (Exception e) {
622: }
623: }
624: }
625: }
|