0001: /*
0002: * (C) Copyright 2000 - 2006 Nabh Information Systems, Inc.
0003: *
0004: * This program is free software; you can redistribute it and/or
0005: * modify it under the terms of the GNU General Public License
0006: * as published by the Free Software Foundation; either version 2
0007: * of the License, or (at your option) any later version.
0008: *
0009: * This program is distributed in the hope that it will be useful,
0010: * but WITHOUT ANY WARRANTY; without even the implied warranty of
0011: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
0012: * GNU General Public License for more details.
0013: *
0014: * You should have received a copy of the GNU General Public License
0015: * along with this program; if not, write to the Free Software
0016: * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
0017: *
0018: */
0019: package com.nabhinc.portal.spi.impl.db;
0020:
0021: import java.rmi.RemoteException;
0022: import java.sql.Connection;
0023: import java.sql.PreparedStatement;
0024: import java.sql.ResultSet;
0025: import java.sql.SQLException;
0026: import java.sql.Types;
0027: import java.util.ArrayList;
0028: import java.util.Date;
0029: import java.util.HashMap;
0030: import java.util.List;
0031: import java.util.Map;
0032: import java.util.Vector;
0033:
0034: import javax.naming.NamingException;
0035:
0036: import com.nabhinc.portal.spi.BaseUserServiceImpl;
0037: import com.nabhinc.spi.EntityExistsException;
0038: import com.nabhinc.spi.EntityUniqueException;
0039: import com.nabhinc.spi.MissingRequiredAttributeException;
0040: import com.nabhinc.spi.NoSuchEntityException;
0041: import com.nabhinc.spi.User;
0042: import com.nabhinc.spi.UserAdminService;
0043: import com.nabhinc.spi.UserImpl;
0044: import com.nabhinc.util.StringUtil;
0045: import com.nabhinc.util.db.DBConfigUtil;
0046: import com.nabhinc.util.db.DBUtil;
0047: import com.nabhinc.ws.core.WebServiceException;
0048: import com.nabhinc.ws.server.ServerObjectConfig;
0049:
0050: /**
0051: *
0052: *
0053: * @author Padmanabh Dabke
0054: * (c) 2006 Nabh Information Systems, Inc. All Rights Reserved.
0055: */
0056: public class UserAdminServiceDBImpl extends BaseUserServiceImpl
0057: implements UserAdminService {
0058: protected String bdsiDataSource = null;
0059:
0060: public String getDataSourceName() {
0061: return bdsiDataSource;
0062: }
0063:
0064: public void setDataSourceName(String ds) {
0065: bdsiDataSource = ds;
0066: }
0067:
0068: private String uasiUserIDSQL = "SELECT userid FROM SB_USERS WHERE username = ?";
0069: private String uasiCreateUserSQL = "INSERT INTO SB_USERS (username, pass_word, utitle, fname, mname, lname, suffix, oemail, address1, address2, city, state, country, zipcode, ophone, hphone, cphone, hemail, ofax, hfax, pager, website, gender, bdate, sig, aim, yim, msnm, icq, showemail, showname) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
0070: private String uasiAssignRolesSQL = "INSERT INTO SB_USER_ROLES (userid, roleid) VALUES (?, ?)";
0071: private String uasiUnassignRolesSQL = "DELETE FROM SB_USER_ROLES WHERE userid = ?";
0072: private String uasiDeleteUserSQL = "DELETE FROM SB_USERS WHERE userid = ?";
0073: private String uasiDeleteUserWithUserNameSQL = "DELETE FROM SB_USERS WHERE username = ?";
0074: private String uasiUpdateUserSQL = "UPDATE SB_USERS SET username = ?, utitle = ?, fname = ?, mname = ?, lname = ?, suffix = ?, oemail = ?, address1 = ?, address2 = ?, city = ?, state = ?, country = ?, zipcode = ?, ophone = ?, hphone = ?, cphone = ?, hemail = ?, ofax = ?, hfax = ?, pager = ?, website = ?, gender = ?, bdate = ?, sig = ?, aim = ?, yim = ?, msnm = ?, icq = ?, showemail = ?, showname = ? WHERE userid = ?";
0075: private String uasiSelectUsersSQL = "SELECT userid, username, fname, lname, oemail, joined, lastlogin, mname, suffix, address1, address2, city, state, country, zipcode, ophone, hphone, cphone, hemail, ofax, hfax, pager, website, gender, bdate, sig, aim, yim, msnm, icq, showemail, showname, utitle FROM SB_USERS";
0076: private String uasiSelectUserWithUserIDSQL = "SELECT userid, username, fname, lname, oemail, joined, lastlogin, mname, suffix, address1, address2, city, state, country, zipcode, ophone, hphone, cphone, hemail, ofax, hfax, pager, website, gender, bdate, sig, aim, yim, msnm, icq, showemail, showname, utitle FROM SB_USERS WHERE userid = ?";
0077: private String uasiSelectUserWithUserNameSQL = "SELECT userid, username, fname, lname, oemail, joined, lastlogin, mname, suffix, address1, address2, city, state, country, zipcode, ophone, hphone, cphone, hemail, ofax, hfax, pager, website, gender, bdate, sig, aim, yim, msnm, icq, showemail, showname, utitle FROM SB_USERS WHERE upper(username) = upper(?)";
0078: private String uasiUserExistsSQL = "SELECT username FROM SB_USERS WHERE (upper(username) = upper(?) OR upper(oemail) = upper(?)) AND userid != ?";
0079: private String uasiUserNameExistsSQL = "SELECT userid FROM SB_USERS WHERE upper(username) = upper(?) AND userid != ?";
0080: private String uasiEmailExistsSQL = "SELECT userid FROM SB_USERS WHERE upper(oemail) = upper(?) AND userid != ?";
0081: private String uasiUserNameFromEmailSQL = "SELECT username FROM SB_USERS WHERE upper(oemail) = upper(?)";
0082: private String uasiLastLoginUpdateSQL = "UPDATE SB_USERS SET lastlogin=? WHERE username= ?";
0083: private String uasiPasswordUpdateSQL = "UPDATE SB_USERS SET pass_word = ? WHERE upper(username) = upper(?)";
0084: private String uasiUserCountSQL = "SELECT count(*) FROM SB_USERS";
0085:
0086: private String[] uasiStringUserAttributes = UserAdminService.STRING_USER_ATTRIBUTES;
0087: // private String[] uasiBooleanUserAttributes = UserAdminService.BOOLEAN_USER_ATTRIBUTES;
0088: private String[] uasiIntUserAttributes = UserAdminService.INTEGER_USER_ATTRIBUTES;
0089: private String[] uasiDateUserAttributes = UserAdminService.DATE_USER_ATTRIBUTES;
0090:
0091: private String uasiIconUpdateSQL = "UPDATE SB_USERS SET usericon = ? WHERE username = ?";
0092: private String uasiSelectIconSQL = "SELECT usericon FROM SB_USERS WHERE username = ?";
0093:
0094: /**
0095: * JSR 168 compliant names of user info attributes supported.
0096: */
0097: private String[] uasiUserInfoAttributes = null;
0098:
0099: /**
0100: * SQL for retrieving user info attributes.
0101: */
0102: private String uasiUserInfoSQL = "SELECT fname, lname, ophone, oemail, userid FROM SB_USERS WHERE username = ?";
0103:
0104: /**
0105: * A helper array passed in getRecord call made to retrieve
0106: * user info attributes.
0107: */
0108: private int[] uasiUserInfoAttributeTypes = null;
0109:
0110: /**
0111: * Caches server context and sets object properties.
0112: */
0113: public void init(ServerObjectConfig config)
0114: throws WebServiceException {
0115: super .init(config);
0116: if (uasiUserInfoAttributes == null) {
0117: uasiUserInfoAttributes = new String[] { "user.name.given",
0118: "user.name.family",
0119: "user.business-info.telecom.telephone.number",
0120: "user.business-info.online.email", "user.id" };
0121: }
0122: if (uasiUserInfoAttributeTypes == null) {
0123: uasiUserInfoAttributeTypes = new int[uasiUserInfoAttributes.length];
0124: for (int i = 0; i < uasiUserInfoAttributes.length; i++) {
0125: uasiUserInfoAttributeTypes[i] = Types.VARCHAR;
0126: }
0127: } else {
0128: if (uasiUserInfoAttributes.length != uasiUserInfoAttributeTypes.length) {
0129: throw new WebServiceException(
0130: "Number of user attributes must be equal to the number of attrib types.");
0131: }
0132: }
0133: }
0134:
0135: public String getUserInfoAttributes() {
0136: return StringUtil.join(uasiUserInfoAttributes, ",");
0137: }
0138:
0139: @SuppressWarnings("unchecked")
0140: public void setUserInfoAttributes(String uInfoAttribs) {
0141: Vector attribList = new Vector();
0142: String[] attribs = StringUtil.split(uInfoAttribs, ",");
0143: for (int i = 0; i < attribs.length; i++) {
0144: if (attribs[i] != null) {
0145: String attrib = attribs[i].trim();
0146: if (attrib.length() > 0)
0147: attribList.add(attrib);
0148: }
0149: }
0150: uasiUserInfoAttributes = new String[attribList.size()];
0151: attribList.copyInto(uasiUserInfoAttributes);
0152: }
0153:
0154: public void setUserInfoAttributeTypes(String attribTypes) {
0155: String[] attribTypeArray = StringUtil.split(attribTypes, ",");
0156: uasiUserInfoAttributeTypes = new int[attribTypeArray.length];
0157: for (int i = 0; i < attribTypeArray.length; i++) {
0158: uasiUserInfoAttributeTypes[i] = DBConfigUtil
0159: .getSQLType(attribTypeArray[i]);
0160: }
0161: }
0162:
0163: public String getUserInfoSQL() {
0164: return uasiUserInfoSQL;
0165: }
0166:
0167: public void setUserInfoSQL(String uasiUserInfoSQL) {
0168: this .uasiUserInfoSQL = uasiUserInfoSQL;
0169: }
0170:
0171: public String getAssignRolesSQL() {
0172: return uasiAssignRolesSQL;
0173: }
0174:
0175: public void setAssignRolesSQL(String uasiAssignRolesSQL) {
0176: this .uasiAssignRolesSQL = uasiAssignRolesSQL;
0177: }
0178:
0179: public String getCreateUserSQL() {
0180: return uasiCreateUserSQL;
0181: }
0182:
0183: public void setCreateUserSQL(String uasiCreateUserSQL) {
0184: this .uasiCreateUserSQL = uasiCreateUserSQL;
0185: }
0186:
0187: public String getDeleteUserSQL() {
0188: return uasiDeleteUserSQL;
0189: }
0190:
0191: public void setDeleteUserSQL(String uasiDeleteUserSQL) {
0192: this .uasiDeleteUserSQL = uasiDeleteUserSQL;
0193: }
0194:
0195: public String getDeleteUserWithUserNameSQL() {
0196: return uasiDeleteUserWithUserNameSQL;
0197: }
0198:
0199: public void setDeleteUserWithUserNameSQL(String deleteUserSQL) {
0200: uasiDeleteUserWithUserNameSQL = deleteUserSQL;
0201: }
0202:
0203: public String getIconUpdateSQL() {
0204: return uasiIconUpdateSQL;
0205: }
0206:
0207: public void setIconUpdateSQL(String uasiIconUpdateSQL) {
0208: this .uasiIconUpdateSQL = uasiIconUpdateSQL;
0209: }
0210:
0211: public String getLastLoginUpdateSQL() {
0212: return uasiLastLoginUpdateSQL;
0213: }
0214:
0215: public void setLastLoginUpdateSQL(String uasiLastLoginUpdateSQL) {
0216: this .uasiLastLoginUpdateSQL = uasiLastLoginUpdateSQL;
0217: }
0218:
0219: public String getPasswordUpdateSQL() {
0220: return uasiPasswordUpdateSQL;
0221: }
0222:
0223: public void setPasswordUpdateSQL(String uasiPasswordUpdateSQL) {
0224: this .uasiPasswordUpdateSQL = uasiPasswordUpdateSQL;
0225: }
0226:
0227: public String getSelectIconSQL() {
0228: return uasiSelectIconSQL;
0229: }
0230:
0231: public void setSelectIconSQL(String uasiSelectIconSQL) {
0232: this .uasiSelectIconSQL = uasiSelectIconSQL;
0233: }
0234:
0235: public String getSelectUsersSQL() {
0236: return uasiSelectUsersSQL;
0237: }
0238:
0239: public void setSelectUsersSQL(String uasiSelectUsersSQL) {
0240: this .uasiSelectUsersSQL = uasiSelectUsersSQL;
0241: }
0242:
0243: public String getSelectUserWithUserIDSQL() {
0244: return uasiSelectUserWithUserIDSQL;
0245: }
0246:
0247: public void setSelectUserWithUserIDSQL(
0248: String uasiSelectUserWithUserIDSQL) {
0249: this .uasiSelectUserWithUserIDSQL = uasiSelectUserWithUserIDSQL;
0250: }
0251:
0252: public String getSelectUserWithUserNameSQL() {
0253: return uasiSelectUserWithUserNameSQL;
0254: }
0255:
0256: public void setSelectUserWithUserNameSQL(String sql) {
0257: this .uasiSelectUserWithUserNameSQL = sql;
0258: }
0259:
0260: public String getUnassignRolesSQL() {
0261: return uasiUnassignRolesSQL;
0262: }
0263:
0264: public void setUnassignRolesSQL(String uasiUnassignRolesSQL) {
0265: this .uasiUnassignRolesSQL = uasiUnassignRolesSQL;
0266: }
0267:
0268: public String getUpdateUserSQL() {
0269: return uasiUpdateUserSQL;
0270: }
0271:
0272: public void setUpdateUserSQL(String uasiUpdateUserSQL) {
0273: this .uasiUpdateUserSQL = uasiUpdateUserSQL;
0274: }
0275:
0276: public String[] getStringUserAttributes() {
0277: return uasiStringUserAttributes;
0278: }
0279:
0280: public void setStringUserAttributes(String[] uasiUserAttributes) {
0281: this .uasiStringUserAttributes = uasiUserAttributes;
0282: }
0283:
0284: public String[] getIntUserAttributes() {
0285: return uasiIntUserAttributes;
0286: }
0287:
0288: public void setIntUserAttributes(String[] uasiUserAttributes) {
0289: this .uasiIntUserAttributes = uasiUserAttributes;
0290: }
0291:
0292: public String[] getDateUserAttributes() {
0293: return uasiDateUserAttributes;
0294: }
0295:
0296: public void setDateUserAttributes(String[] uasiUserAttributes) {
0297: this .uasiDateUserAttributes = uasiUserAttributes;
0298: }
0299:
0300: /*
0301: public String[] getBooleanUserAttributes() {
0302: return uasiBooleanUserAttributes;
0303: }
0304:
0305: public void setBooleanUserAttributes(String[] uasiUserAttributes) {
0306: this.uasiBooleanUserAttributes = uasiUserAttributes;
0307: }
0308: */
0309:
0310: public String getUserCountSQL() {
0311: return uasiUserCountSQL;
0312: }
0313:
0314: public void setUserCountSQL(String userCountSQL) {
0315: this .uasiUserCountSQL = userCountSQL;
0316: }
0317:
0318: public String getUserExistsSQL() {
0319: return uasiUserExistsSQL;
0320: }
0321:
0322: public void setUserExistsSQL(String userExistsSQL) {
0323: this .uasiUserExistsSQL = userExistsSQL;
0324: }
0325:
0326: public String getUserNameExistsSQL() {
0327: return uasiUserNameExistsSQL;
0328: }
0329:
0330: public void setUserNameExistsSQL(String userNameExistsSQL) {
0331: this .uasiUserNameExistsSQL = userNameExistsSQL;
0332: }
0333:
0334: public String getEmailExistsSQL() {
0335: return uasiEmailExistsSQL;
0336: }
0337:
0338: public void setEmailExistsSQL(String emailExistsSQL) {
0339: this .uasiEmailExistsSQL = emailExistsSQL;
0340: }
0341:
0342: public String getUserIDSQL() {
0343: return uasiUserIDSQL;
0344: }
0345:
0346: public void setUserIDSQL(String userIDSQL) {
0347: this .uasiUserIDSQL = userIDSQL;
0348: }
0349:
0350: public String getUserNameFromEmailSQL() {
0351: return uasiUserNameFromEmailSQL;
0352: }
0353:
0354: public void setUserNameFromEmailSQL(String userNameFromEmailSQL) {
0355: this .uasiUserNameFromEmailSQL = userNameFromEmailSQL;
0356: }
0357:
0358: public void updateLastLogin(String userName) throws RemoteException {
0359: Connection conn = null;
0360: PreparedStatement st = null;
0361:
0362: try {
0363: conn = DBUtil.getConnection(bdsiDataSource);
0364: st = conn.prepareStatement(uasiLastLoginUpdateSQL);
0365: st.setTimestamp(1, new java.sql.Timestamp(System
0366: .currentTimeMillis()));
0367: st.setString(2, userName);
0368: st.execute();
0369:
0370: } catch (NamingException ex) {
0371: throw new RemoteException("Failed to look up data source: "
0372: + bdsiDataSource, ex);
0373: } catch (SQLException ex) {
0374: throw new RemoteException("System exception.", ex);
0375: } finally {
0376: DBUtil.close(st);
0377: DBUtil.close(conn);
0378: }
0379:
0380: }
0381:
0382: public void setPassword(String userName, String password)
0383: throws RemoteException {
0384: Connection conn = null;
0385: PreparedStatement st = null;
0386:
0387: try {
0388: conn = DBUtil.getConnection(bdsiDataSource);
0389: st = conn.prepareStatement(uasiPasswordUpdateSQL);
0390: password = digest(password);
0391: st.setString(1, password);
0392: st.setString(2, userName);
0393: st.execute();
0394:
0395: } catch (NamingException ex) {
0396: throw new RemoteException("Failed to look up data source: "
0397: + bdsiDataSource, ex);
0398: } catch (SQLException ex) {
0399: throw new RemoteException("System exception.", ex);
0400: } finally {
0401: DBUtil.close(st);
0402: DBUtil.close(conn);
0403: }
0404:
0405: }
0406:
0407: public void createUser(User user, String password, int[] roleID)
0408: throws EntityExistsException, EntityUniqueException,
0409: RemoteException {
0410: Connection conn = null;
0411: PreparedStatement st = null;
0412: ResultSet results = null;
0413: try {
0414: conn = DBUtil.getConnection(bdsiDataSource);
0415: conn.setAutoCommit(false);
0416:
0417: // First check if a user with specified name or primary email already exists.
0418: /*
0419: st = conn.prepareStatement(uasiUserExistsSQL);
0420: st.setString(1, user.getUserName());
0421: st.setString(2, user.getPrimaryEmail());
0422: st.setInt(3, -1);
0423: */
0424: st = conn.prepareStatement(uasiUserNameExistsSQL);
0425: st.setString(1, user.getUserName());
0426: st.setInt(2, -1);
0427: results = st.executeQuery();
0428: if (results.next())
0429: throw new EntityExistsException();
0430: results.close();
0431: results = null;
0432: st.close();
0433: st = null;
0434:
0435: st = conn.prepareStatement(uasiEmailExistsSQL);
0436: st.setString(1, user.getPrimaryEmail());
0437: st.setInt(2, -1);
0438: results = st.executeQuery();
0439: if (results.next())
0440: throw new EntityUniqueException();
0441: results.close();
0442: results = null;
0443: st.close();
0444: st = null;
0445:
0446: // username, pass_word, fname, lname, oemail, joined, userprofile
0447: st = conn.prepareStatement(uasiCreateUserSQL);
0448: setUserAttributes(st, user, password, -1);
0449: st.execute();
0450: st.close();
0451: st = null;
0452:
0453: // Get the user id of the user we just inserted
0454: st = conn.prepareStatement(uasiUserIDSQL);
0455: st.setString(1, user.getUserName());
0456: results = st.executeQuery();
0457: results.next();
0458: user.setId(results.getInt(1));
0459: st.close();
0460: st = null;
0461:
0462: // Now insert user-role pairs
0463: st = conn.prepareStatement(uasiAssignRolesSQL);
0464: st.setInt(1, user.getId());
0465: for (int i = 0; i < roleID.length; i++) {
0466: st.setInt(2, roleID[i]);
0467: st.execute();
0468: }
0469:
0470: conn.commit();
0471: } catch (NamingException ex) {
0472: try {
0473: conn.rollback();
0474: } catch (Exception e) { /* ignore */
0475: }
0476: throw new RemoteException("Failed to look up data source: "
0477: + bdsiDataSource, ex);
0478: } catch (SQLException ex) {
0479: try {
0480: conn.rollback();
0481: } catch (Exception e) { /* ignore */
0482: }
0483: throw new RemoteException("System exception.", ex);
0484: } finally {
0485: DBUtil.close(results);
0486: DBUtil.close(st);
0487: DBUtil.close(conn);
0488: }
0489:
0490: }
0491:
0492: public void updateUser(User user, int[] roles)
0493: throws EntityExistsException, RemoteException,
0494: EntityUniqueException, MissingRequiredAttributeException {
0495: Connection conn = null;
0496: PreparedStatement st = null;
0497: ResultSet results = null;
0498:
0499: if (user.getUserName() == null
0500: || user.getUserName().trim().equals("")) {
0501: throw new MissingRequiredAttributeException(
0502: "User name must be specified.");
0503: }
0504: if (user.getPrimaryEmail() == null
0505: || user.getPrimaryEmail().trim().equals("")) {
0506: throw new MissingRequiredAttributeException(
0507: "Primary email must be specified.");
0508: }
0509: if (user.getFirstName() == null
0510: || user.getFirstName().trim().equals("")) {
0511: throw new MissingRequiredAttributeException(
0512: "First name must be specified.");
0513: }
0514: if (user.getLastName() == null
0515: || user.getLastName().trim().equals("")) {
0516: throw new MissingRequiredAttributeException(
0517: "Last name must be specified.");
0518: }
0519: try {
0520: conn = DBUtil.getConnection(bdsiDataSource);
0521: conn.setAutoCommit(false);
0522:
0523: // First check if a user with specified name or primary email already exists.
0524: /*
0525: st = conn.prepareStatement(uasiUserExistsSQL);
0526: st.setString(1, user.getUserName());
0527: st.setString(2, user.getPrimaryEmail());
0528: st.setInt(3, user.getId());
0529: results = st.executeQuery();
0530: if (results.next()) throw new EntityExistsException();
0531: results.close();
0532: results = null;
0533: st.close();
0534: st = null;
0535: */
0536:
0537: st = conn.prepareStatement(uasiUserNameExistsSQL);
0538: st.setString(1, user.getUserName());
0539: st.setInt(2, user.getId());
0540: results = st.executeQuery();
0541: if (results.next())
0542: throw new EntityExistsException();
0543: results.close();
0544: results = null;
0545: st.close();
0546: st = null;
0547:
0548: st = conn.prepareStatement(uasiEmailExistsSQL);
0549: st.setString(1, user.getPrimaryEmail());
0550: st.setInt(2, user.getId());
0551: results = st.executeQuery();
0552: if (results.next())
0553: throw new EntityUniqueException();
0554: results.close();
0555: results = null;
0556: st.close();
0557: st = null;
0558:
0559: st = conn.prepareStatement(uasiUpdateUserSQL);
0560: setUserAttributes(st, user, null, user.getId());
0561: st.execute();
0562:
0563: st.close();
0564: st = null;
0565:
0566: // Delete current user/role mapping
0567: st = conn.prepareStatement(uasiUnassignRolesSQL);
0568: st.setInt(1, user.getId());
0569: st.execute();
0570: st.close();
0571: st = null;
0572:
0573: // Now insert user-role pairs
0574: st = conn.prepareStatement(uasiAssignRolesSQL);
0575: st.setInt(1, user.getId());
0576: for (int i = 0; i < roles.length; i++) {
0577: st.setInt(2, roles[i]);
0578: st.execute();
0579: }
0580: conn.commit();
0581:
0582: } catch (NamingException ex) {
0583: try {
0584: conn.rollback();
0585: } catch (Exception e) { /* ignore */
0586: }
0587: throw new RemoteException("Failed to look up data source: "
0588: + bdsiDataSource, ex);
0589: } catch (SQLException ex) {
0590: try {
0591: conn.rollback();
0592: } catch (Exception e) { /* ignore */
0593: }
0594: throw new RemoteException("System exception.", ex);
0595: } finally {
0596: DBUtil.close(results);
0597: DBUtil.close(st);
0598: DBUtil.close(conn);
0599: }
0600:
0601: }
0602:
0603: public void updateUserProfile(User user)
0604: throws EntityExistsException, EntityUniqueException,
0605: MissingRequiredAttributeException, RemoteException {
0606: Connection conn = null;
0607: PreparedStatement st = null;
0608: ResultSet results = null;
0609:
0610: if (user.getUserName() == null
0611: || user.getUserName().trim().equals("")) {
0612: throw new MissingRequiredAttributeException(
0613: "User name must be specified.");
0614: }
0615: if (user.getPrimaryEmail() == null
0616: || user.getPrimaryEmail().trim().equals("")) {
0617: throw new MissingRequiredAttributeException(
0618: "Primary email must be specified.");
0619: }
0620: if (user.getFirstName() == null
0621: || user.getFirstName().trim().equals("")) {
0622: throw new MissingRequiredAttributeException(
0623: "First name must be specified.");
0624: }
0625: if (user.getLastName() == null
0626: || user.getLastName().trim().equals("")) {
0627: throw new MissingRequiredAttributeException(
0628: "Last name must be specified.");
0629: }
0630:
0631: try {
0632:
0633: conn = DBUtil.getConnection(bdsiDataSource);
0634: conn.setAutoCommit(false);
0635:
0636: // First check if a user with specified name or primary email already exists.
0637: /*
0638: st = conn.prepareStatement(uasiUserExistsSQL);
0639: st.setString(1, user.getUserName());
0640: st.setString(2, user.getPrimaryEmail());
0641: st.setInt(3, user.getId());
0642: results = st.executeQuery();
0643: if (results.next()) throw new EntityExistsException();
0644: results.close();
0645: results = null;
0646: st.close();
0647: st = null;
0648: */
0649: st = conn.prepareStatement(uasiUserNameExistsSQL);
0650: st.setString(1, user.getUserName());
0651: st.setInt(2, user.getId());
0652: results = st.executeQuery();
0653: if (results.next())
0654: throw new EntityExistsException();
0655: results.close();
0656: results = null;
0657: st.close();
0658: st = null;
0659:
0660: st = conn.prepareStatement(uasiEmailExistsSQL);
0661: st.setString(1, user.getPrimaryEmail());
0662: st.setInt(2, user.getId());
0663: results = st.executeQuery();
0664: if (results.next())
0665: throw new EntityUniqueException();
0666: results.close();
0667: results = null;
0668: st.close();
0669: st = null;
0670:
0671: st = conn.prepareStatement(uasiUpdateUserSQL);
0672: setUserAttributes(st, user, null, user.getId());
0673: st.execute();
0674:
0675: st.close();
0676: st = null;
0677:
0678: conn.commit();
0679:
0680: } catch (NamingException ex) {
0681: try {
0682: conn.rollback();
0683: } catch (Exception e) { /* ignore */
0684: }
0685: throw new RemoteException("Failed to look up data source: "
0686: + bdsiDataSource, ex);
0687: } catch (SQLException ex) {
0688: try {
0689: conn.rollback();
0690: } catch (Exception e) { /* ignore */
0691: }
0692: throw new RemoteException("System exception.", ex);
0693: } finally {
0694: DBUtil.close(results);
0695: DBUtil.close(st);
0696: DBUtil.close(conn);
0697: }
0698:
0699: }
0700:
0701: public void deleteUsers(int[] userID) throws RemoteException {
0702: Connection conn = null;
0703: PreparedStatement st = null;
0704:
0705: try {
0706: conn = DBUtil.getConnection(bdsiDataSource);
0707: conn.setAutoCommit(false);
0708: st = conn.prepareStatement(uasiDeleteUserSQL);
0709: for (int i = 0; i < userID.length; i++) {
0710: st.setInt(1, userID[i]);
0711: st.execute();
0712: }
0713: conn.commit();
0714: } catch (NamingException ex) {
0715: try {
0716: conn.rollback();
0717: } catch (Exception e) { /* ignore */
0718: }
0719: throw new RemoteException("Failed to look up data source: "
0720: + bdsiDataSource, ex);
0721: } catch (SQLException ex) {
0722: try {
0723: conn.rollback();
0724: } catch (Exception e) { /* ignore */
0725: }
0726: throw new RemoteException("System exception.", ex);
0727: } finally {
0728: DBUtil.close(st);
0729: DBUtil.close(conn);
0730: }
0731:
0732: }
0733:
0734: public void deleteUsers(String[] userNames) throws RemoteException {
0735: Connection conn = null;
0736: PreparedStatement st = null;
0737:
0738: try {
0739: conn = DBUtil.getConnection(bdsiDataSource);
0740: conn.setAutoCommit(false);
0741: st = conn.prepareStatement(uasiDeleteUserWithUserNameSQL);
0742: for (int i = 0; i < userNames.length; i++) {
0743: st.setString(1, userNames[i]);
0744: st.execute();
0745: }
0746: conn.commit();
0747: } catch (NamingException ex) {
0748: try {
0749: conn.rollback();
0750: } catch (Exception e) { /* ignore */
0751: }
0752: throw new RemoteException("Failed to look up data source: "
0753: + bdsiDataSource, ex);
0754: } catch (SQLException ex) {
0755: try {
0756: conn.rollback();
0757: } catch (Exception e) { /* ignore */
0758: }
0759: throw new RemoteException("System exception.", ex);
0760: } finally {
0761: DBUtil.close(st);
0762: DBUtil.close(conn);
0763: }
0764: }
0765:
0766: @SuppressWarnings("unchecked")
0767: public List getUsers(int offset, int maxUsers, String orderby,
0768: boolean isDescending) throws RemoteException {
0769: Connection conn = null;
0770: ResultSet results = null;
0771: PreparedStatement st = null;
0772:
0773: try {
0774: String selectSQL = uasiSelectUsersSQL;
0775: if (orderby != null) {
0776: selectSQL += " ORDER BY " + orderby;
0777: if (isDescending)
0778: selectSQL += " DESC";
0779: }
0780:
0781: conn = DBUtil.getConnection(bdsiDataSource);
0782: st = conn.prepareStatement(selectSQL);
0783: results = st.executeQuery();
0784: List userList = new ArrayList();
0785:
0786: // Skip upto offset
0787: for (int i = 0; i < offset; i++) {
0788: if (!results.next())
0789: return userList;
0790: }
0791:
0792: // Create maxRoles roles and add them to the vector.
0793: for (int i = 0; i < maxUsers; i++) {
0794: if (!results.next())
0795: break;
0796: userList.add(createUser(results));
0797: }
0798:
0799: return userList;
0800:
0801: } catch (NamingException ex) {
0802: throw new RemoteException("Failed to look up data source: "
0803: + bdsiDataSource, ex);
0804: } catch (SQLException ex) {
0805: throw new RemoteException("System exception.", ex);
0806: } finally {
0807: DBUtil.close(st);
0808: DBUtil.close(results);
0809: DBUtil.close(conn);
0810: }
0811: }
0812:
0813: public User getUser(int userID) throws NoSuchEntityException,
0814: RemoteException {
0815: Connection conn = null;
0816: ResultSet results = null;
0817: PreparedStatement st = null;
0818:
0819: try {
0820:
0821: conn = DBUtil.getConnection(bdsiDataSource);
0822: st = conn.prepareStatement(uasiSelectUserWithUserIDSQL);
0823: st.setInt(1, userID);
0824: results = st.executeQuery();
0825: if (!results.next())
0826: throw new NoSuchEntityException("User with id "
0827: + userID + " not found.");
0828: return createUser(results);
0829:
0830: } catch (NamingException ex) {
0831: throw new RemoteException("Failed to look up data source: "
0832: + bdsiDataSource, ex);
0833: } catch (SQLException ex) {
0834: throw new RemoteException("System exception.", ex);
0835: } finally {
0836: DBUtil.close(st);
0837: DBUtil.close(results);
0838: DBUtil.close(conn);
0839: }
0840: }
0841:
0842: public User getUser(String userName) throws NoSuchEntityException,
0843: RemoteException {
0844: Connection conn = null;
0845: ResultSet results = null;
0846: PreparedStatement st = null;
0847:
0848: try {
0849:
0850: conn = DBUtil.getConnection(bdsiDataSource);
0851: st = conn.prepareStatement(uasiSelectUserWithUserNameSQL);
0852: st.setString(1, userName);
0853: results = st.executeQuery();
0854: if (!results.next())
0855: throw new NoSuchEntityException("User with name "
0856: + userName + " not found.");
0857: return createUser(results);
0858:
0859: } catch (NamingException ex) {
0860: throw new RemoteException("Failed to look up data source: "
0861: + bdsiDataSource, ex);
0862: } catch (SQLException ex) {
0863: throw new RemoteException("System exception.", ex);
0864: } finally {
0865: DBUtil.close(st);
0866: DBUtil.close(results);
0867: DBUtil.close(conn);
0868: }
0869: }
0870:
0871: @SuppressWarnings("unchecked")
0872: private User createUser(ResultSet results) throws SQLException {
0873: User usr = new UserImpl();
0874: // userid, username, fname, lname, oemail, joined, lastlogin, mname, suffix, address1, address2, city, state, country, zipcode, ophone, hphone, cphone, hemail, ofax, hfax, pager, website, gender, bdate, sig, aim, yim, msnm, icq, showemail, showname FROM SB_USERS";
0875:
0876: usr.setId(results.getInt(1));
0877: usr.setUserName(results.getString(2));
0878: usr.setFirstName(results.getString(3));
0879: usr.setLastName(results.getString(4));
0880: usr.setPrimaryEmail(results.getString(5));
0881: usr.setUserSince(results.getTimestamp(6));
0882: // usr.setDisabled(results.getBoolean(7));
0883: usr.setLastLogin(results.getTimestamp(7));
0884:
0885: for (int j = 0; j < uasiStringUserAttributes.length; j++) {
0886: String attrValue = results
0887: .getString(uasiStringUserAttributes[j]);
0888: if (attrValue != null)
0889: usr.getProfile().put(uasiStringUserAttributes[j],
0890: attrValue);
0891: }
0892: /*
0893: for (int j=0; j<uasiBooleanUserAttributes.length; j++) {
0894: boolean attrValue = results.getBoolean(uasiBooleanUserAttributes[j]);
0895: if (attrValue) usr.getProfile().put(uasiBooleanUserAttributes[j], Boolean.TRUE);
0896: }
0897: */
0898: for (int j = 0; j < uasiIntUserAttributes.length; j++) {
0899: int attrValue = results.getInt(uasiIntUserAttributes[j]);
0900: if (attrValue != 0)
0901: usr.getProfile().put(uasiIntUserAttributes[j],
0902: new Integer(attrValue));
0903: }
0904:
0905: for (int j = 0; j < uasiDateUserAttributes.length; j++) {
0906: Date attrValue = results.getDate(uasiDateUserAttributes[j]);
0907: if (attrValue != null)
0908: usr.getProfile().put(uasiDateUserAttributes[j],
0909: attrValue);
0910: }
0911: return usr;
0912: }
0913:
0914: public void setIcon(String userName, byte[] icon)
0915: throws RemoteException {
0916: Connection conn = null;
0917: PreparedStatement st = null;
0918:
0919: try {
0920: conn = DBUtil.getConnection(bdsiDataSource);
0921: st = conn.prepareStatement(uasiIconUpdateSQL);
0922: st.setBytes(1, icon);
0923: st.setString(2, userName);
0924: st.execute();
0925:
0926: } catch (NamingException ex) {
0927: throw new RemoteException("Failed to look up data source: "
0928: + bdsiDataSource, ex);
0929: } catch (SQLException ex) {
0930: throw new RemoteException("System exception.", ex);
0931: } finally {
0932: DBUtil.close(st);
0933: DBUtil.close(conn);
0934: }
0935:
0936: }
0937:
0938: public byte[] getIcon(String userName) throws RemoteException {
0939: Connection conn = null;
0940: ResultSet results = null;
0941: PreparedStatement st = null;
0942:
0943: try {
0944: conn = DBUtil.getConnection(bdsiDataSource);
0945: st = conn.prepareStatement(uasiSelectIconSQL);
0946: results = st.executeQuery();
0947: if (results.next()) {
0948: return results.getBytes(1);
0949: } else {
0950: return null;
0951: }
0952:
0953: } catch (NamingException ex) {
0954: throw new RemoteException("Failed to look up data source: "
0955: + bdsiDataSource, ex);
0956: } catch (SQLException ex) {
0957: throw new RemoteException("System exception.", ex);
0958: } finally {
0959: DBUtil.close(st);
0960: DBUtil.close(results);
0961: DBUtil.close(conn);
0962: }
0963: }
0964:
0965: public String getUserNameFromEmail(String email)
0966: throws RemoteException, NoSuchEntityException {
0967: Connection conn = null;
0968: ResultSet results = null;
0969: PreparedStatement st = null;
0970:
0971: try {
0972: conn = DBUtil.getConnection(bdsiDataSource);
0973: st = conn.prepareStatement(uasiUserNameFromEmailSQL);
0974: st.setString(1, email);
0975: results = st.executeQuery();
0976: if (results.next()) {
0977: return results.getString(1);
0978: } else {
0979: throw new NoSuchEntityException();
0980: }
0981:
0982: } catch (NamingException ex) {
0983: throw new RemoteException("Failed to look up data source: "
0984: + bdsiDataSource, ex);
0985: } catch (SQLException ex) {
0986: throw new RemoteException("System exception.", ex);
0987: } finally {
0988: DBUtil.close(st);
0989: DBUtil.close(results);
0990: DBUtil.close(conn);
0991: }
0992: }
0993:
0994: public int getUserCount() throws RemoteException {
0995: Connection conn = null;
0996: ResultSet results = null;
0997: PreparedStatement st = null;
0998:
0999: try {
1000: conn = DBUtil.getConnection(bdsiDataSource);
1001: st = conn.prepareStatement(uasiUserCountSQL);
1002: results = st.executeQuery();
1003: results.next();
1004: return results.getInt(1);
1005:
1006: } catch (NamingException ex) {
1007: throw new RemoteException("Failed to look up data source: "
1008: + bdsiDataSource, ex);
1009: } catch (SQLException ex) {
1010: throw new RemoteException("System exception.", ex);
1011: } finally {
1012: DBUtil.close(st);
1013: DBUtil.close(results);
1014: DBUtil.close(conn);
1015: }
1016: }
1017:
1018: @SuppressWarnings("unchecked")
1019: public Map getUserInfo(String userName) throws RemoteException,
1020: NoSuchEntityException {
1021: Map userInfo = new HashMap();
1022:
1023: if (uasiUserInfoSQL != null) {
1024: try {
1025: String[] infoFields = DBUtil.getRecord(bdsiDataSource,
1026: uasiUserInfoSQL, uasiUserInfoAttributeTypes,
1027: null, new String[] { userName });
1028: if (infoFields != null) {
1029: if (infoFields.length != uasiUserInfoAttributes.length) {
1030: error("Invalid configuration: User info SQL does not return expected number of user fields.");
1031: throw new RuntimeException(
1032: "Invalid configuration.");
1033: } else {
1034: for (int i = 0; i < infoFields.length; i++) {
1035: if (infoFields[i] != null)
1036: userInfo.put(uasiUserInfoAttributes[i],
1037: infoFields[i]);
1038: }
1039: }
1040: }
1041: return userInfo;
1042: } catch (Exception ex) {
1043: error("Exception in retrieving user info attributes: ",
1044: ex);
1045: throw new RemoteException(
1046: "Failed to retrieve user info from the database.",
1047: ex);
1048: }
1049: } else {
1050: return null;
1051: }
1052:
1053: }
1054:
1055: private void setUserAttributes(PreparedStatement st, User usr,
1056: String passwd, int userID) throws SQLException {
1057: Map userInfo = usr.getProfile();
1058: int i = 1;
1059: // username
1060: st.setString(i++, usr.getUserName());
1061: // pass_word
1062: if (passwd != null) {
1063: /*
1064: if (Defaults.getEncryptionAlgorithm() != null) {
1065: passwd = EncryptionUtil.encrypt(passwd, Defaults.getEncryptionAlgorithm());
1066: }
1067: */
1068: passwd = digest(passwd);
1069: st.setString(i++, passwd);
1070: }
1071: // utitle
1072: st.setString(i++, (String) userInfo.get("utitle"));
1073: // fname
1074: st.setString(i++, usr.getFirstName());
1075: // mname
1076: st.setString(i++, (String) userInfo.get("mname"));
1077: // lname
1078: st.setString(i++, usr.getLastName());
1079: // suffix
1080: st.setString(i++, (String) userInfo.get("suffix"));
1081: // oemail
1082: st.setString(i++, usr.getPrimaryEmail());
1083: // address1
1084: st.setString(i++, (String) userInfo.get("address1"));
1085: // address2
1086: st.setString(i++, (String) userInfo.get("address2"));
1087: // city
1088: st.setString(i++, (String) userInfo.get("city"));
1089: // state
1090: st.setString(i++, (String) userInfo.get("state"));
1091: // country
1092: st.setString(i++, (String) userInfo.get("country"));
1093: // zipcode
1094: st.setString(i++, (String) userInfo.get("zipcode"));
1095: // ophone,
1096: st.setString(i++, (String) userInfo.get("ophone"));
1097: // hphone
1098: st.setString(i++, (String) userInfo.get("hphone"));
1099: // cphone
1100: st.setString(i++, (String) userInfo.get("cphone"));
1101: // hemail
1102: st.setString(i++, (String) userInfo.get("hemail"));
1103: // ofax
1104: st.setString(i++, (String) userInfo.get("ofax"));
1105: // hfax
1106: st.setString(i++, (String) userInfo.get("hfax"));
1107: // pager
1108: st.setString(i++, (String) userInfo.get("pager"));
1109: // website
1110: st.setString(i++, (String) userInfo.get("website"));
1111: // gender
1112: Integer genderInt = (Integer) userInfo.get("gender");
1113: if (genderInt == null)
1114: st.setString(i++, null);
1115: else
1116: st.setInt(i++, genderInt.intValue());
1117: // bdate
1118: Date bDate = (Date) userInfo.get("bdate");
1119: java.sql.Date sqlDate = bDate == null ? null
1120: : new java.sql.Date(bDate.getTime());
1121: st.setDate(i++, sqlDate);
1122: // sig
1123: st.setString(i++, (String) userInfo.get("sig"));
1124: // aim
1125: st.setString(i++, (String) userInfo.get("aim"));
1126: // yim
1127: st.setString(i++, (String) userInfo.get("yim"));
1128: // msnm
1129: st.setString(i++, (String) userInfo.get("msnm"));
1130: // icq
1131: st.setString(i++, (String) userInfo.get("icq"));
1132: // showemail
1133: Boolean showEmail = (Boolean) userInfo.get("showemail");
1134:
1135: if (showEmail == null || (!showEmail.booleanValue()))
1136: st.setBoolean(i++, false);
1137: else
1138: st.setBoolean(i++, true);
1139: // showname
1140: Boolean showName = (Boolean) userInfo.get("showname");
1141:
1142: if (showName == null || (!showName.booleanValue()))
1143: st.setBoolean(i++, false);
1144: else
1145: st.setBoolean(i++, true);
1146: // userid
1147: if (userID != -1)
1148: st.setInt(i++, userID);
1149: }
1150:
1151: }
|