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