001: /*
002: * JDBCMySQLUserDAO.java
003: *
004: * Created on 28 de marzo de 2005, 13:46
005: */
006:
007: package org.manentia.kasai.authobject;
008:
009: import java.sql.Connection;
010: import java.sql.ResultSet;
011: import java.sql.SQLException;
012: import java.util.ArrayList;
013: import java.util.Collection;
014:
015: import org.apache.commons.lang.StringUtils;
016: import org.manentia.kasai.AuthObject;
017: import org.manentia.kasai.Constants;
018: import org.manentia.kasai.Group;
019: import org.manentia.kasai.ObjectGroupRole;
020: import org.manentia.kasai.ObjectUserRole;
021: import org.manentia.kasai.Role;
022: import org.manentia.kasai.User;
023: import org.manentia.kasai.exceptions.DataAccessException;
024: import org.manentia.kasai.exceptions.DoesntExistsException;
025: import org.manentia.kasai.group.GroupHandler;
026: import org.manentia.kasai.role.RoleHandler;
027: import org.manentia.kasai.user.UserHandler;
028:
029: import com.manentia.commons.log.Log;
030: import com.manentia.commons.persistence.DBUtil;
031: import com.manentia.commons.xml.XMLException;
032:
033: /**
034: *
035: * @author rzuasti
036: */
037: public class JDBCANSISQLAuthObjectDAO implements AuthObjectDAO {
038:
039: /** Creates a new instance of JDBCMySQLUserDAO */
040: public JDBCANSISQLAuthObjectDAO() {
041: }
042:
043: public void copyPermissionsFromObject(String sourceObject,
044: String destinationObject) throws DoesntExistsException,
045: DataAccessException {
046:
047: Connection con = null;
048: String sql;
049:
050: try {
051: if ((StringUtils.isNotEmpty(sourceObject))
052: && (StringUtils.isNotEmpty(destinationObject))) {
053: if (this .read(sourceObject) == null) {
054: Log.write("Source object doesn't exist", Log.WARN,
055: "copyPermissionsFromObject",
056: JDBCANSISQLAuthObjectDAO.class);
057:
058: throw new DoesntExistsException(AuthObject.class
059: .getName()
060: + ".objectDoesntExist");
061: }
062:
063: if (this .read(destinationObject) == null) {
064: Log.write("Destination object doesn't exist",
065: Log.WARN, "copyPermissionsFromObject",
066: JDBCANSISQLAuthObjectDAO.class);
067:
068: throw new DoesntExistsException(AuthObject.class
069: .getName()
070: + ".objectDoesntExist");
071: }
072:
073: con = DBUtil.getConnection(Constants.DATABASE_SOURCE,
074: Constants.CONFIG_PROPERTY_FILE);
075:
076: sql = "INSERT INTO kasai_objects_users_roles KOUR1 (id_object,id_user,id_role) SELECT '"
077: + destinationObject
078: + "',KOUR2.id_user,KOUR2.id_role FROM kasai_objects_users_roles KOUR2 "
079: + "WHERE id_object='"
080: + sourceObject
081: + "' AND NOT EXISTS ( "
082: + " SELECT * FROM kasai_objects_users_roles KOUR3 "
083: + " WHERE KOUR3.id_object='"
084: + destinationObject
085: + "' AND KOUR3.id_user=KOUR2.id_user AND KOUR3.id_role=KOUR2.id_role "
086: + " )";
087: con.createStatement().executeUpdate(sql);
088:
089: sql = "REPLACE INTO kasai_objects_groups_roles (id_object,id_group,id_role) SELECT '"
090: + destinationObject
091: + "',id_group,id_role FROM kasai_objects_groups_roles KOGR2 "
092: + "WHERE id_object='"
093: + sourceObject
094: + "' AND NOT EXISTS ( "
095: + " SELECT * FROM kasai_objects_groups_roles KOGR3 "
096: + " WHERE KOGR3.id_object='"
097: + destinationObject
098: + "' AND KOGR3.id_group=KOGR2.id_group AND KOGR3.id_role=KOGR2.id_role "
099: + " )";
100: con.createStatement().executeUpdate(sql);
101:
102: }
103: } catch (SQLException sqle) {
104: Log.write("SQL Error", sqle, Log.ERROR,
105: "copyPermissionsFromObject",
106: JDBCANSISQLAuthObjectDAO.class);
107:
108: throw new DataAccessException(sqle);
109: } finally {
110: try {
111: con.close();
112: } catch (Exception e) {
113: }
114: }
115: }
116:
117: public void create(String object) throws DataAccessException {
118:
119: Connection con = null;
120: String sql;
121: if (StringUtils.isNotEmpty(object)) {
122: if (this .read(object) == null) {
123: try {
124: sql = "INSERT INTO kasai_objects (id) values ('"
125: + org.apache.commons.lang.StringEscapeUtils
126: .escapeSql(object) + "')";
127: con = DBUtil.getConnection(
128: Constants.DATABASE_SOURCE,
129: Constants.CONFIG_PROPERTY_FILE);
130: con.createStatement().executeUpdate(sql);
131: } catch (SQLException sqle) {
132: Log.write("SQL Error", sqle, Log.ERROR, "create",
133: JDBCANSISQLAuthObjectDAO.class);
134:
135: throw new DataAccessException(sqle);
136: } finally {
137: try {
138: con.close();
139: } catch (Exception e) {
140: }
141: }
142: }
143: }
144: }
145:
146: public void createObjectGroupRole(String object, String group,
147: int role) throws DoesntExistsException,
148: DataAccessException, XMLException {
149:
150: Connection con = null;
151: String sql;
152:
153: try {
154: if ((StringUtils.isNotEmpty(object))
155: && (StringUtils.isNotEmpty(group))) {
156: if (GroupHandler.getInstance().read(group) == null) {
157: Log.write("Group doesn't exist", Log.WARN,
158: "createObjectGroupRole",
159: JDBCANSISQLAuthObjectDAO.class);
160:
161: throw new DoesntExistsException(Group.class
162: .getName()
163: + ".groupDoesntExist");
164: }
165: if (RoleHandler.getInstance().read(role) == null) {
166: Log.write("Role doesn't exist", Log.WARN,
167: "createObjectGroupRole",
168: JDBCANSISQLAuthObjectDAO.class);
169:
170: throw new DoesntExistsException(Role.class
171: .getName()
172: + ".roleDoesntExist");
173: }
174: if (this .read(object) == null) {
175: Log.write("Object doesn't exist", Log.WARN,
176: "createObjectGroupRole",
177: JDBCANSISQLAuthObjectDAO.class);
178:
179: throw new DoesntExistsException(AuthObject.class
180: .getName()
181: + ".objectDoesntExist");
182: }
183:
184: con = DBUtil.getConnection(Constants.DATABASE_SOURCE,
185: Constants.CONFIG_PROPERTY_FILE);
186: sql = "SELECT id from kasai_objects_groups_roles WHERE id_object='"
187: + org.apache.commons.lang.StringEscapeUtils
188: .escapeSql(object)
189: + "' AND id_group='"
190: + org.apache.commons.lang.StringEscapeUtils
191: .escapeSql(group)
192: + "' AND id_role="
193: + role;
194: if (!con.createStatement().executeQuery(sql).next()) {
195: sql = "INSERT INTO kasai_objects_groups_roles (id_object,id_group,id_role) values ('"
196: + org.apache.commons.lang.StringEscapeUtils
197: .escapeSql(object)
198: + "','"
199: + org.apache.commons.lang.StringEscapeUtils
200: .escapeSql(group)
201: + "',"
202: + role
203: + ")";
204: con.createStatement().executeUpdate(sql);
205: }
206: }
207: } catch (SQLException sqle) {
208: Log.write("SQL Error", sqle, Log.ERROR,
209: "createObjectGroupRole",
210: JDBCANSISQLAuthObjectDAO.class);
211:
212: throw new DataAccessException(sqle);
213: } finally {
214: try {
215: con.close();
216: } catch (Exception e) {
217: }
218: }
219: }
220:
221: public void createObjectUserRole(String object, String login,
222: int role) throws DoesntExistsException,
223: DataAccessException, XMLException {
224:
225: Connection con = null;
226: String sql;
227:
228: try {
229: if ((StringUtils.isNotEmpty(object))
230: && (StringUtils.isNotEmpty(login))) {
231: if (UserHandler.getInstance().read(login, true) == null) {
232: Log.write("User doesn't exist", Log.WARN,
233: "createObjectUserRole",
234: JDBCANSISQLAuthObjectDAO.class);
235:
236: throw new DoesntExistsException(User.class
237: .getName()
238: + ".userDoesntExist");
239: }
240: if (RoleHandler.getInstance().read(role) == null) {
241: Log.write("Role doesn't exist", Log.WARN,
242: "createObjectUserRole",
243: JDBCANSISQLAuthObjectDAO.class);
244:
245: throw new DoesntExistsException(Role.class
246: .getName()
247: + ".roleDoesntExist");
248: }
249: if (this .read(object) == null) {
250: Log.write("Object doesn't exist", Log.WARN,
251: "createObjectUserRole",
252: JDBCANSISQLAuthObjectDAO.class);
253:
254: throw new DoesntExistsException(AuthObject.class
255: .getName()
256: + ".objectDoesntExist");
257: }
258: con = DBUtil.getConnection(Constants.DATABASE_SOURCE,
259: Constants.CONFIG_PROPERTY_FILE);
260: sql = "SELECT id from kasai_objects_users_roles WHERE id_object='"
261: + org.apache.commons.lang.StringEscapeUtils
262: .escapeSql(object)
263: + "' AND id_user='"
264: + org.apache.commons.lang.StringEscapeUtils
265: .escapeSql(login)
266: + "' AND id_role="
267: + role;
268: if (!con.createStatement().executeQuery(sql).next()) {
269: sql = "INSERT INTO kasai_objects_users_roles (id_object,id_user,id_role) values ('"
270: + org.apache.commons.lang.StringEscapeUtils
271: .escapeSql(object)
272: + "','"
273: + org.apache.commons.lang.StringEscapeUtils
274: .escapeSql(login)
275: + "',"
276: + role
277: + ")";
278: con.createStatement().executeUpdate(sql);
279: }
280: }
281: } catch (SQLException sqle) {
282: Log.write("SQL Error", sqle, Log.ERROR,
283: "createObjectUserRole",
284: JDBCANSISQLAuthObjectDAO.class);
285:
286: throw new DataAccessException(sqle);
287: } finally {
288: try {
289: con.close();
290: } catch (Exception e) {
291: }
292: }
293: }
294:
295: public void delete(String object) throws DataAccessException {
296: Connection con = null;
297: String sql;
298: try {
299: if (StringUtils.isNotEmpty(object)) {
300:
301: con = DBUtil.getConnection(Constants.DATABASE_SOURCE,
302: Constants.CONFIG_PROPERTY_FILE);
303:
304: sql = "DELETE FROM kasai_objects WHERE id='"
305: + org.apache.commons.lang.StringEscapeUtils
306: .escapeSql(object) + "'";
307: con.createStatement().executeUpdate(sql);
308: }
309: } catch (SQLException sqle) {
310: Log.write("SQL Error", sqle, Log.ERROR, "delete",
311: JDBCANSISQLAuthObjectDAO.class);
312:
313: throw new DataAccessException(sqle);
314: } finally {
315: try {
316: con.close();
317: } catch (Exception e) {
318: }
319: }
320:
321: }
322:
323: public void deleteObjectGroupRole(int id)
324: throws DataAccessException {
325: Connection con = null;
326: try {
327: String sql = "delete from kasai_objects_groups_roles where id="
328: + id;
329: con = DBUtil.getConnection(Constants.DATABASE_SOURCE,
330: Constants.CONFIG_PROPERTY_FILE);
331: con.createStatement().executeUpdate(sql);
332: } catch (SQLException sqle) {
333: Log.write("SQL Error", sqle, Log.ERROR,
334: "deleteObjectGroupRole",
335: JDBCANSISQLAuthObjectDAO.class);
336:
337: throw new DataAccessException(sqle);
338: } finally {
339: try {
340: con.close();
341: } catch (Exception e) {
342: }
343: }
344: }
345:
346: public void deleteObjectUserRole(int id) throws DataAccessException {
347: Connection con = null;
348: try {
349: String sql = "DELETE FROM kasai_objects_users_roles WHERE id="
350: + id;
351: con = DBUtil.getConnection(Constants.DATABASE_SOURCE,
352: Constants.CONFIG_PROPERTY_FILE);
353: con.createStatement().executeUpdate(sql);
354: } catch (SQLException sqle) {
355: Log.write("SQL Error", sqle, Log.ERROR,
356: "deleteObjectUserRole",
357: JDBCANSISQLAuthObjectDAO.class);
358:
359: throw new DataAccessException(sqle);
360: } finally {
361: try {
362: con.close();
363: } catch (Exception e) {
364: }
365: }
366: }
367:
368: public void deleteObjectUserRole(String login, String object,
369: int role) throws DataAccessException {
370: Connection con = null;
371: try {
372: String sql = "DELETE FROM kasai_objects_users_roles WHERE id_user='"
373: + org.apache.commons.lang.StringEscapeUtils
374: .escapeSql(login)
375: + "' AND id_object='"
376: + org.apache.commons.lang.StringEscapeUtils
377: .escapeSql(object)
378: + "' AND id_role="
379: + role;
380: con = DBUtil.getConnection(Constants.DATABASE_SOURCE,
381: Constants.CONFIG_PROPERTY_FILE);
382: con.createStatement().executeUpdate(sql);
383: } catch (SQLException sqle) {
384: Log.write("SQL Error", sqle, Log.ERROR,
385: "deleteObjectUserRole",
386: JDBCANSISQLAuthObjectDAO.class);
387:
388: throw new DataAccessException(sqle);
389: } finally {
390: try {
391: con.close();
392: } catch (Exception e) {
393: }
394: }
395: }
396:
397: public void deleteObjectUserRole(String login, String object)
398: throws DataAccessException {
399: Connection con = null;
400: try {
401: String sql = "DELETE FROM kasai_objects_users_roles WHERE id_user='"
402: + org.apache.commons.lang.StringEscapeUtils
403: .escapeSql(login)
404: + "' AND id_object='"
405: + org.apache.commons.lang.StringEscapeUtils
406: .escapeSql(object) + "'";
407: con = DBUtil.getConnection(Constants.DATABASE_SOURCE,
408: Constants.CONFIG_PROPERTY_FILE);
409: con.createStatement().executeUpdate(sql);
410: } catch (SQLException sqle) {
411: Log.write("SQL Error", sqle, Log.ERROR,
412: "deleteObjectUserRole",
413: JDBCANSISQLAuthObjectDAO.class);
414:
415: throw new DataAccessException(sqle);
416: } finally {
417: try {
418: con.close();
419: } catch (Exception e) {
420: }
421: }
422: }
423:
424: public Collection listObjectGroupsRoles(String object)
425: throws DataAccessException {
426: Connection con = null;
427: String sql;
428: ResultSet rs = null;
429: ObjectGroupRole ogr = null;
430: ArrayList ogrs = new ArrayList();
431: if (StringUtils.isNotEmpty(object)) {
432: try {
433:
434: sql = "SELECT AOGR.*,AR.name as role_name FROM kasai_objects_groups_roles AOGR,kasai_roles AR "
435: + "WHERE AOGR.id_object='"
436: + org.apache.commons.lang.StringEscapeUtils
437: .escapeSql(object)
438: + "' and AR.id=AOGR.id_role";
439:
440: sql += " order by AOGR.id_group ";
441: con = DBUtil.getConnection(Constants.DATABASE_SOURCE,
442: Constants.CONFIG_PROPERTY_FILE);
443: rs = con.createStatement().executeQuery(sql);
444: while (rs.next()) {
445: ogr = new ObjectGroupRole(rs);
446: ogrs.add(ogr);
447: }
448: } catch (SQLException sqle) {
449: Log.write("SQL Error", sqle, Log.ERROR,
450: "listObjectGroupsRoles",
451: JDBCANSISQLAuthObjectDAO.class);
452:
453: throw new DataAccessException(sqle);
454: } finally {
455: try {
456: rs.close();
457: } catch (Exception e) {
458: }
459: try {
460: con.close();
461: } catch (Exception e) {
462: }
463: }
464: }
465: return ogrs;
466: }
467:
468: public Collection listObjectUsersRoles(String object)
469: throws DataAccessException {
470: Connection con = null;
471: String sql;
472: ResultSet rs = null;
473: ObjectUserRole our = null;
474: ArrayList ours = new ArrayList();
475: if (StringUtils.isNotEmpty(object)) {
476: try {
477:
478: sql = "SELECT AOUR.*,AR.name as role_name FROM kasai_objects_users_roles AOUR,kasai_roles AR "
479: + "WHERE AOUR.id_object='"
480: + org.apache.commons.lang.StringEscapeUtils
481: .escapeSql(object)
482: + "' and AR.id=AOUR.id_role";
483:
484: sql += " order by AOUR.id_user ";
485: con = DBUtil.getConnection(Constants.DATABASE_SOURCE,
486: Constants.CONFIG_PROPERTY_FILE);
487: rs = con.createStatement().executeQuery(sql);
488: while (rs.next()) {
489: our = new ObjectUserRole(rs);
490: ours.add(our);
491: }
492: } catch (SQLException sqle) {
493: Log.write("SQL Error", sqle, Log.ERROR,
494: "listObjectUsersRoles",
495: JDBCANSISQLAuthObjectDAO.class);
496:
497: throw new DataAccessException(sqle);
498: } finally {
499: try {
500: rs.close();
501: } catch (Exception e) {
502: }
503: try {
504: con.close();
505: } catch (Exception e) {
506: }
507: }
508: }
509: return ours;
510: }
511:
512: public AuthObject read(String id) throws DataAccessException {
513: Connection con = null;
514: String sql;
515: ResultSet rs = null;
516: AuthObject o = null;
517: try {
518: if (StringUtils.isNotEmpty(id)) {
519: sql = "SELECT * FROM kasai_objects WHERE id='"
520: + org.apache.commons.lang.StringEscapeUtils
521: .escapeSql(id) + "'";
522: con = DBUtil.getConnection(Constants.DATABASE_SOURCE,
523: Constants.CONFIG_PROPERTY_FILE);
524: rs = con.createStatement().executeQuery(sql);
525: if (rs.next()) {
526: o = new AuthObject(rs);
527: }
528: }
529: return o;
530: } catch (SQLException sqle) {
531: Log.write("SQL Error", sqle, Log.ERROR, "read",
532: JDBCANSISQLAuthObjectDAO.class);
533:
534: throw new DataAccessException(sqle);
535: } finally {
536: try {
537: rs.close();
538: } catch (Exception e) {
539: }
540: try {
541: con.close();
542: } catch (Exception e) {
543: }
544: }
545:
546: }
547: }
|