001: /*
002: * JDBCMySQLUserDAO.java
003: *
004: * Created on 28 de marzo de 2005, 13:46
005: */
006:
007: package org.manentia.kasai.operative;
008:
009: import java.io.IOException;
010: import java.sql.Connection;
011: import java.sql.ResultSet;
012: import java.sql.SQLException;
013: import java.util.ArrayList;
014: import java.util.Collection;
015:
016: import javax.xml.parsers.FactoryConfigurationError;
017: import javax.xml.parsers.ParserConfigurationException;
018:
019: import org.apache.commons.lang.StringUtils;
020: import org.manentia.kasai.Constants;
021: import org.manentia.kasai.Group;
022: import org.manentia.kasai.Operative;
023: import org.manentia.kasai.User;
024: import org.manentia.kasai.exceptions.DataAccessException;
025: import org.xml.sax.SAXException;
026:
027: import com.manentia.commons.log.Log;
028: import com.manentia.commons.persistence.DBUtil;
029: import com.manentia.commons.xml.XMLException;
030:
031: /**
032: *
033: * @author rzuasti
034: */
035: public class JDBCANSISQLOperativeDAO implements OperativeDAO {
036:
037: /** Creates a new instance of JDBCMySQLUserDAO */
038: public JDBCANSISQLOperativeDAO() {
039: }
040:
041: public Collection listGroupsOperative(String operative,
042: String object) throws DataAccessException, XMLException {
043: Connection con = null;
044: String sql;
045: ResultSet rs = null;
046: Group g = null;
047: ArrayList groups = new ArrayList();
048: try {
049:
050: int index = 0;
051: con = DBUtil.getConnection(Constants.DATABASE_SOURCE,
052: Constants.CONFIG_PROPERTY_FILE);
053: while (index != -1) {
054:
055: sql = "select distinct AG.* from kasai_roles_operatives ARO, "
056: + "kasai_users_groups AUG,kasai_objects_groups_roles AOGR,kasai_groups AG where "
057: + "ARO.id_operative='"
058: + org.apache.commons.lang.StringEscapeUtils
059: .escapeSql(operative)
060: + "' and AOGR.id_object='"
061: + org.apache.commons.lang.StringEscapeUtils
062: .escapeSql(object)
063: + "' and AOGR.id_group=AUG.id_group "
064: + "and ARO.id_role=AOGR.id_role and AG.id=AUG.id_group "
065: + "and AG.blocked=0";
066:
067: rs = con.createStatement().executeQuery(sql);
068: while (rs.next()) {
069: g = new Group(rs);
070: if (!groups.contains(g))
071: groups.add(g);
072: }
073:
074: index = operative.lastIndexOf('.');
075: if (index >= 0) {
076: operative = operative.substring(0, index);
077: }
078:
079: }
080:
081: return groups;
082: } catch (SQLException sqle) {
083: Log.write("SQL Error", sqle, Log.ERROR,
084: "listGroupsOperative",
085: JDBCANSISQLOperativeDAO.class);
086:
087: throw new DataAccessException(sqle);
088: } catch (SAXException e) {
089: Log.write("XML Error reading group", e, Log.ERROR,
090: "listGroupsOperative",
091: JDBCANSISQLOperativeDAO.class);
092:
093: throw new XMLException(e);
094: } catch (IOException e) {
095: Log.write("XML Error reading group", e, Log.ERROR,
096: "listGroupsOperative",
097: JDBCANSISQLOperativeDAO.class);
098:
099: throw new XMLException(e);
100: } catch (ParserConfigurationException e) {
101: Log.write("XML Error reading group", e, Log.ERROR,
102: "listGroupsOperative",
103: JDBCANSISQLOperativeDAO.class);
104:
105: throw new XMLException(e);
106: } catch (FactoryConfigurationError e) {
107: Log.write("XML Error reading group", e, Log.ERROR,
108: "listGroupsOperative",
109: JDBCANSISQLOperativeDAO.class);
110:
111: throw new XMLException(e);
112: } finally {
113: try {
114: rs.close();
115: } catch (Exception e) {
116: }
117: try {
118: con.close();
119: } catch (Exception e) {
120: }
121: }
122:
123: }
124:
125: public Collection list(String idOperative)
126: throws DataAccessException {
127: Connection con = null;
128: String sql;
129: ResultSet rs = null;
130: Operative o = null;
131: ArrayList operatives = new ArrayList();
132: try {
133: sql = "SELECT * FROM kasai_operatives ";
134: if (StringUtils.isNotEmpty(idOperative)) {
135: sql += " where id='"
136: + org.apache.commons.lang.StringEscapeUtils
137: .escapeSql(idOperative) + "'";
138: }
139: sql += " order by sequence ";
140: con = DBUtil.getConnection(Constants.DATABASE_SOURCE,
141: Constants.CONFIG_PROPERTY_FILE);
142: rs = con.createStatement().executeQuery(sql);
143: while (rs.next()) {
144: o = new Operative(rs);
145: operatives.add(o);
146: }
147: return operatives;
148: } catch (SQLException sqle) {
149: Log.write("SQL Error", sqle, Log.ERROR, "list",
150: JDBCANSISQLOperativeDAO.class);
151:
152: throw new DataAccessException(sqle);
153: } finally {
154: try {
155: rs.close();
156: } catch (Exception e) {
157: }
158: try {
159: con.close();
160: } catch (Exception e) {
161: }
162: }
163:
164: }
165:
166: public Collection listUsersOperative(String operative, String object)
167: throws DataAccessException, XMLException {
168: Connection con = null;
169: String sql;
170: ResultSet rs = null;
171: User u = null;
172: ArrayList users = new ArrayList();
173: try {
174:
175: sql = "SELECT AU.* FROM kasai_users AU WHERE AU.super_user=1 AND AU.blocked=0";
176: con = DBUtil.getConnection(Constants.DATABASE_SOURCE,
177: Constants.CONFIG_PROPERTY_FILE);
178: rs = con.createStatement().executeQuery(sql);
179: while (rs.next()) {
180: u = new User(rs);
181: users.add(u);
182: }
183:
184: int index = 0;
185: while (index != -1) {
186:
187: sql = "select distinct AU.* from kasai_roles_operatives ARO, "
188: + "kasai_users_groups AUG,kasai_users AU,kasai_objects_groups_roles AOGR,kasai_groups AG where "
189: + "ARO.id_operative='"
190: + org.apache.commons.lang.StringEscapeUtils
191: .escapeSql(operative)
192: + "' and AOGR.id_object='"
193: + org.apache.commons.lang.StringEscapeUtils
194: .escapeSql(object)
195: + "' and AOGR.id_group=AUG.id_group "
196: + "and ARO.id_role=AOGR.id_role and AG.id=AUG.id_group "
197: + "and AU.id=AUG.id_user"
198: + " and AG.blocked=0"
199: + " and AU.blocked=0";
200:
201: rs = con.createStatement().executeQuery(sql);
202: while (rs.next()) {
203: u = new User(rs);
204: if (!users.contains(u))
205: users.add(u);
206: }
207:
208: sql = "select distinct AU.* from kasai_roles_operatives ARO,kasai_users AU,kasai_objects_users_roles AOUR "
209: + "where AOUR.id_user=AU.id and "
210: + "ARO.id_operative='"
211: + org.apache.commons.lang.StringEscapeUtils
212: .escapeSql(operative)
213: + "' and AOUR.id_object='"
214: + org.apache.commons.lang.StringEscapeUtils
215: .escapeSql(object)
216: + "' and ARO.id_role=AOUR.id_role"
217: + " and AU.blocked=0";
218:
219: rs = con.createStatement().executeQuery(sql);
220: while (rs.next()) {
221: u = new User(rs);
222: if (!users.contains(u))
223: users.add(u);
224: }
225:
226: index = operative.lastIndexOf('.');
227: if (index >= 0) {
228: operative = operative.substring(0, index);
229: }
230:
231: }
232: return users;
233: } catch (SQLException sqle) {
234: Log
235: .write("SQL Error", sqle, Log.ERROR,
236: "listUsersOperative",
237: JDBCANSISQLOperativeDAO.class);
238:
239: throw new DataAccessException(sqle);
240: } catch (SAXException e) {
241: Log
242: .write("XML error reading user", e, Log.ERROR,
243: "listUsersOperative",
244: JDBCANSISQLOperativeDAO.class);
245:
246: throw new XMLException(e);
247: } catch (IOException e) {
248: Log
249: .write("XML error reading user", e, Log.ERROR,
250: "listUsersOperative",
251: JDBCANSISQLOperativeDAO.class);
252:
253: throw new XMLException(e);
254: } catch (ParserConfigurationException e) {
255: Log
256: .write("XML error reading user", e, Log.ERROR,
257: "listUsersOperative",
258: JDBCANSISQLOperativeDAO.class);
259:
260: throw new XMLException(e);
261: } catch (FactoryConfigurationError e) {
262: Log
263: .write("XML error reading user", e, Log.ERROR,
264: "listUsersOperative",
265: JDBCANSISQLOperativeDAO.class);
266:
267: throw new XMLException(e);
268: } finally {
269: try {
270: rs.close();
271: } catch (Exception e) {
272: }
273: try {
274: con.close();
275: } catch (Exception e) {
276: }
277: }
278:
279: }
280: }
|