001: /*
002: * JDBCMySQLUserDAO.java
003: *
004: * Created on 28 de marzo de 2005, 13:46
005: */
006:
007: package org.manentia.kasai.user;
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:
016: import javax.xml.parsers.FactoryConfigurationError;
017: import javax.xml.parsers.ParserConfigurationException;
018:
019: import org.apache.commons.lang.ArrayUtils;
020: import org.apache.commons.lang.StringEscapeUtils;
021: import org.apache.commons.lang.StringUtils;
022: import org.manentia.kasai.Constants;
023: import org.manentia.kasai.User;
024: import org.manentia.kasai.exceptions.AlreadyExistsException;
025: import org.manentia.kasai.exceptions.DataAccessException;
026: import org.manentia.kasai.util.CacheUsers;
027: import org.xml.sax.SAXException;
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 JDBCANSISQLUserDAO implements UserDAO {
038:
039: /** Creates a new instance of JDBCMySQLUserDAO */
040: public JDBCANSISQLUserDAO() {
041: }
042:
043: public boolean checkOperative(String login, String operative,
044: String object) {
045: Connection con = null;
046: String sql;
047: ResultSet rs = null;
048: boolean result = false;
049: try {
050: org.manentia.kasai.User u = this .read(login, true);
051: if (u == null) {
052: return false;
053: }
054: if (u.getBlocked()) {
055: return false;
056: }
057: if (u.getSuperUser()) {
058: return true;
059: }
060:
061: sql = "select distinct(ARO.id_operative) as operative from kasai_roles_operatives ARO, "
062: + "kasai_users_groups AUG,kasai_objects_groups_roles AOGR,kasai_groups AG where "
063: + "AOGR.id_object='"
064: + org.apache.commons.lang.StringEscapeUtils
065: .escapeSql(object)
066: + "' and AOGR.id_group=AUG.id_group "
067: + "and AUG.id_user='"
068: + org.apache.commons.lang.StringEscapeUtils
069: .escapeSql(login)
070: + "' and ARO.id_role=AOGR.id_role and AG.id=AUG.id_group and AG.blocked=0";
071:
072: con = DBUtil.getConnection(Constants.DATABASE_SOURCE,
073: Constants.CONFIG_PROPERTY_FILE);
074: rs = con.createStatement().executeQuery(sql);
075: while (rs.next() && (!result)) {
076: result = (operative.startsWith(rs
077: .getString("operative")));
078: }
079:
080: if (!result) {
081: sql = "select distinct(ARO.id_operative) as operative from kasai_roles_operatives ARO, "
082: + "kasai_objects_users_roles AOUR where AOUR.id_user='"
083: + org.apache.commons.lang.StringEscapeUtils
084: .escapeSql(login)
085: + "' and "
086: + "AOUR.id_object='"
087: + org.apache.commons.lang.StringEscapeUtils
088: .escapeSql(object)
089: + "' and ARO.id_role=AOUR.id_role";
090: rs = con.createStatement().executeQuery(sql);
091: while (rs.next() && (!result)) {
092: result = (operative.startsWith(rs
093: .getString("operative")));
094: }
095: }
096:
097: return result;
098: } catch (DataAccessException dae) {
099: return false;
100: } catch (SQLException sqlE) {
101: Log.write("SQL Error", sqlE, Log.ERROR, "checkOperative",
102: JDBCANSISQLUserDAO.class);
103:
104: return false;
105: } catch (XMLException e) {
106: return false;
107: } finally {
108: try {
109: rs.close();
110: } catch (Exception e) {
111: }
112: try {
113: con.close();
114: } catch (Exception e) {
115: }
116: }
117: }
118:
119: public void create(User user)
120: throws org.manentia.kasai.exceptions.InvalidAttributesException,
121: org.manentia.kasai.exceptions.AlreadyExistsException,
122: DataAccessException, XMLException {
123:
124: Connection con = null;
125: String sql;
126: try {
127: user.validate();
128:
129: if (this .read(user.getLogin(), true) != null) {
130: Log.write("Login already exist", Log.WARN, "create",
131: JDBCANSISQLUserDAO.class);
132:
133: throw new AlreadyExistsException(this .getClass()
134: .getName()
135: + ".userAlreadyExist");
136: }
137: sql = "INSERT INTO kasai_users (id, first_name, last_name, email,blocked,description,data,super_user) VALUES (?,?,?,?,?,?,?,?)";
138: con = DBUtil.getConnection(Constants.DATABASE_SOURCE,
139: Constants.CONFIG_PROPERTY_FILE);
140: PreparedStatement stm = con.prepareStatement(sql);
141: stm.setString(1, user.getLogin());
142: stm.setString(2, user.getFirstName());
143: stm.setString(3, user.getLastName());
144: stm.setString(4, user.getEmail());
145: if (user.getBlocked()) {
146: stm.setInt(5, 1);
147: } else {
148: stm.setInt(5, 0);
149: }
150: stm.setString(6, user.getDescription());
151: stm.setString(7, user.getAttributesXML());
152: if (user.getSuperUser()) {
153: stm.setInt(8, 1);
154: } else {
155: stm.setInt(8, 0);
156: }
157: stm.executeUpdate();
158: } catch (SQLException sqle) {
159: Log.write("SQL Error", sqle, Log.ERROR, "create",
160: JDBCANSISQLUserDAO.class);
161:
162: throw new DataAccessException(sqle);
163: } catch (ParserConfigurationException e) {
164: Log.write("Error saving attributes XML", e, Log.ERROR,
165: "create", JDBCANSISQLUserDAO.class);
166:
167: throw new XMLException(e);
168: } catch (FactoryConfigurationError e) {
169: Log.write("Error saving attributes XML", e, Log.ERROR,
170: "create", JDBCANSISQLUserDAO.class);
171:
172: throw new XMLException(e);
173: } finally {
174: try {
175: con.close();
176: } catch (Exception e) {
177: }
178: }
179: }
180:
181: public void delete(String login) throws DataAccessException {
182: Connection con = null;
183: String sql;
184: try {
185: if (StringUtils.isNotEmpty(login)) {
186: sql = "DELETE FROM kasai_users WHERE id='"
187: + org.apache.commons.lang.StringEscapeUtils
188: .escapeSql(login) + "'";
189: con = DBUtil.getConnection(Constants.DATABASE_SOURCE,
190: Constants.CONFIG_PROPERTY_FILE);
191: con.createStatement().executeUpdate(sql);
192: CacheUsers.addUser(login, null);
193: }
194: } catch (SQLException sqle) {
195: Log.write("SQL Error", sqle, Log.ERROR, "delete",
196: JDBCANSISQLUserDAO.class);
197:
198: throw new DataAccessException(sqle);
199: } finally {
200: try {
201: con.close();
202: } catch (Exception e) {
203: }
204: }
205: }
206:
207: public java.util.List list(String login, String firstName,
208: String lastName, String email, int blocked,
209: String description, String group)
210: throws DataAccessException, XMLException {
211: Connection con = null;
212: String sql;
213: ResultSet rs = null;
214: User u = null;
215: ArrayList users = new ArrayList();
216: try {
217: sql = "SELECT AU.* FROM kasai_users AU";
218: if (StringUtils.isNotEmpty(group)) {
219: sql += ", kasai_users_groups AUG WHERE AUG.id_user=AU.id AND AUG.id_group='"
220: + org.apache.commons.lang.StringEscapeUtils
221: .escapeSql(group) + "'";
222: } else {
223: sql += " WHERE AU.id <> ''";
224: }
225: if (StringUtils.isNotEmpty(login)) {
226: sql += " AND AU.id LIKE '%"
227: + org.apache.commons.lang.StringEscapeUtils
228: .escapeSql(login) + "%'";
229: }
230: if (StringUtils.isNotEmpty(firstName)) {
231: sql += " AND AU.first_name LIKE '%"
232: + org.apache.commons.lang.StringEscapeUtils
233: .escapeSql(firstName) + "%'";
234: }
235: if (StringUtils.isNotEmpty(lastName)) {
236: sql += " AND AU.last_name LIKE '%"
237: + org.apache.commons.lang.StringEscapeUtils
238: .escapeSql(lastName) + "%'";
239: }
240: if (StringUtils.isNotEmpty(email)) {
241: sql += " AND AU.email LIKE '%"
242: + org.apache.commons.lang.StringEscapeUtils
243: .escapeSql(email) + "%'";
244: }
245: if (blocked != -1) {
246: sql += " AND AU.blocked = " + blocked;
247: }
248: if (StringUtils.isNotEmpty(description)) {
249: sql += " AND AU.description LIKE '%"
250: + org.apache.commons.lang.StringEscapeUtils
251: .escapeSql(description) + "%'";
252: }
253: sql += " order by AU.last_name, AU.first_name, AU.id ";
254: con = DBUtil.getConnection(Constants.DATABASE_SOURCE,
255: Constants.CONFIG_PROPERTY_FILE);
256: rs = con.createStatement().executeQuery(sql);
257: while (rs.next()) {
258: u = new User(rs);
259: users.add(u);
260: }
261: return users;
262: } catch (SQLException sqle) {
263: Log.write("SQL Error", sqle, Log.ERROR, "list",
264: JDBCANSISQLUserDAO.class);
265:
266: throw new DataAccessException(sqle);
267: } catch (SAXException e) {
268: Log.write("Error attributes XML document", e, Log.ERROR,
269: "list", JDBCANSISQLUserDAO.class);
270:
271: throw new XMLException(e);
272: } catch (IOException e) {
273: Log.write("Error attributes XML document", e, Log.ERROR,
274: "list", JDBCANSISQLUserDAO.class);
275:
276: throw new XMLException(e);
277: } catch (ParserConfigurationException e) {
278: Log.write("Error attributes XML document", e, Log.ERROR,
279: "list", JDBCANSISQLUserDAO.class);
280:
281: throw new XMLException(e);
282: } catch (FactoryConfigurationError e) {
283: Log.write("Error attributes XML document", e, Log.ERROR,
284: "list", JDBCANSISQLUserDAO.class);
285:
286: throw new XMLException(e);
287: } finally {
288: try {
289: rs.close();
290: } catch (Exception e) {
291: }
292: try {
293: con.close();
294: } catch (Exception e) {
295: }
296: }
297: }
298:
299: public org.manentia.kasai.User read(String login, boolean cache)
300: throws DataAccessException, XMLException {
301: Connection con = null;
302: String sql;
303: ResultSet rs = null;
304: User u = null;
305: try {
306: if (StringUtils.isNotEmpty(login)) {
307: if (cache) {
308: u = CacheUsers.getUser(login);
309: }
310: if (u == null) {
311: sql = "SELECT * FROM kasai_users WHERE id='"
312: + org.apache.commons.lang.StringEscapeUtils
313: .escapeSql(login) + "'";
314: con = DBUtil.getConnection(
315: Constants.DATABASE_SOURCE,
316: Constants.CONFIG_PROPERTY_FILE);
317: rs = con.createStatement().executeQuery(sql);
318: if (rs.next() && (rs.getString("id").equals(login))) {
319: u = new User(rs);
320: }
321: }
322: }
323: return u;
324: } catch (SQLException sqle) {
325: Log.write("SQL Error", sqle, Log.ERROR, "read",
326: JDBCANSISQLUserDAO.class);
327:
328: throw new DataAccessException(sqle);
329: } catch (SAXException e) {
330: Log.write("Error attributes XML document", e, Log.ERROR,
331: "read", JDBCANSISQLUserDAO.class);
332:
333: throw new XMLException(e);
334: } catch (IOException e) {
335: Log.write("Error attributes XML document", e, Log.ERROR,
336: "read", JDBCANSISQLUserDAO.class);
337:
338: throw new XMLException(e);
339: } catch (ParserConfigurationException e) {
340: Log.write("Error attributes XML document", e, Log.ERROR,
341: "read", JDBCANSISQLUserDAO.class);
342:
343: throw new XMLException(e);
344: } catch (FactoryConfigurationError e) {
345: Log.write("Error attributes XML document", e, Log.ERROR,
346: "read", JDBCANSISQLUserDAO.class);
347:
348: throw new XMLException(e);
349: } finally {
350: try {
351: rs.close();
352: } catch (Exception e) {
353: }
354: try {
355: con.close();
356: } catch (Exception e) {
357: }
358: }
359: }
360:
361: public void update(User user)
362: throws org.manentia.kasai.exceptions.InvalidAttributesException,
363: DataAccessException, XMLException {
364: Connection con = null;
365: String sql;
366: try {
367: user.validate();
368:
369: sql = "UPDATE kasai_users set first_name=?, last_name=?, email=?, blocked=?, description=?, data=?, super_user=? where id=?";
370: con = DBUtil.getConnection(Constants.DATABASE_SOURCE,
371: Constants.CONFIG_PROPERTY_FILE);
372: PreparedStatement stm = con.prepareStatement(sql);
373:
374: stm.setString(1, user.getFirstName());
375: stm.setString(2, user.getLastName());
376: stm.setString(3, user.getEmail());
377: if (user.getBlocked()) {
378: stm.setInt(4, 1);
379: } else {
380: stm.setInt(4, 0);
381: }
382: stm.setString(5, user.getDescription());
383: stm.setString(6, user.getAttributesXML());
384: if (user.getSuperUser()) {
385: stm.setInt(7, 1);
386: } else {
387: stm.setInt(7, 0);
388: }
389: stm.setString(8, user.getLogin());
390: stm.executeUpdate();
391: user = this .read(user.getLogin(), false);
392: CacheUsers.addUser(user.getLogin(), user);
393:
394: } catch (SQLException sqle) {
395: Log.write("SQL Error", sqle, Log.ERROR, "update",
396: JDBCANSISQLUserDAO.class);
397:
398: throw new DataAccessException(sqle);
399: } catch (ParserConfigurationException e) {
400: Log.write("Error saving attributes XML", e, Log.ERROR,
401: "update", JDBCANSISQLUserDAO.class);
402:
403: throw new XMLException(e);
404: } catch (FactoryConfigurationError e) {
405: Log.write("Error saving attributes XML", e, Log.ERROR,
406: "update", JDBCANSISQLUserDAO.class);
407:
408: throw new XMLException(e);
409: } catch (DataAccessException e) {
410: Log.write("Error saving attributes XML", e, Log.ERROR,
411: "update", JDBCANSISQLUserDAO.class);
412:
413: throw new XMLException(e);
414: } finally {
415: try {
416: con.close();
417: } catch (Exception e) {
418: }
419: }
420: }
421:
422: public String[] listUsernames() throws DataAccessException {
423: Connection con = null;
424: String sql;
425: ResultSet rs = null;
426: ArrayList<String> usernames = new ArrayList<String>();
427: try {
428: sql = "SELECT AU.id FROM kasai_users AU";
429: sql += " order by AU.id ";
430: con = DBUtil.getConnection(Constants.DATABASE_SOURCE,
431: Constants.CONFIG_PROPERTY_FILE);
432: rs = con.createStatement().executeQuery(sql);
433: while (rs.next()) {
434: usernames.add(rs.getString(1));
435: }
436: return usernames.toArray(new String[0]);
437: } catch (SQLException sqle) {
438: Log.write("SQL Error", sqle, Log.ERROR, "list",
439: JDBCANSISQLUserDAO.class);
440:
441: throw new DataAccessException(sqle);
442: } finally {
443: try {
444: rs.close();
445: } catch (Exception e) {
446: }
447: try {
448: con.close();
449: } catch (Exception e) {
450: }
451: }
452: }
453:
454: public String[] listUsernames(String groupId)
455: throws DataAccessException {
456: Connection con = null;
457: String sql;
458: ResultSet rs = null;
459: ArrayList<String> usernames = new ArrayList<String>();
460:
461: if (StringUtils.isEmpty(groupId)) {
462: return listUsernames();
463: } else {
464: try {
465: sql = "SELECT UG.id_user FROM kasai_users_groups UG";
466: sql += " WHERE UG.id_group='"
467: + StringEscapeUtils.escapeSql(groupId) + "'";
468: sql += " order by UG.id_user ";
469: con = DBUtil.getConnection(Constants.DATABASE_SOURCE,
470: Constants.CONFIG_PROPERTY_FILE);
471: rs = con.createStatement().executeQuery(sql);
472: while (rs.next()) {
473: usernames.add(rs.getString(1));
474: }
475: return usernames.toArray(new String[0]);
476: } catch (SQLException sqle) {
477: Log.write("SQL Error", sqle, Log.ERROR,
478: "listUsernames", JDBCANSISQLUserDAO.class);
479:
480: throw new DataAccessException(sqle);
481: } finally {
482: try {
483: rs.close();
484: } catch (Exception e) {
485: }
486: try {
487: con.close();
488: } catch (Exception e) {
489: }
490: }
491: }
492: }
493:
494: }
|