001: /*
002: * (C) Copyright 2000 - 2006 Nabh Information Systems, Inc.
003: *
004: * This program is free software; you can redistribute it and/or
005: * modify it under the terms of the GNU General Public License
006: * as published by the Free Software Foundation; either version 2
007: * of the License, or (at your option) any later version.
008: *
009: * This program is distributed in the hope that it will be useful,
010: * but WITHOUT ANY WARRANTY; without even the implied warranty of
011: * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
012: * GNU General Public License for more details.
013: *
014: * You should have received a copy of the GNU General Public License
015: * along with this program; if not, write to the Free Software
016: * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
017: *
018: */
019: package com.nabhinc.portal.spi.impl.db;
020:
021: import java.rmi.RemoteException;
022: import java.sql.Connection;
023: import java.sql.PreparedStatement;
024: import java.sql.ResultSet;
025: import java.sql.SQLException;
026: import java.util.ArrayList;
027: import java.util.List;
028: import java.util.Vector;
029:
030: import javax.naming.NamingException;
031:
032: import com.nabhinc.spi.EntityExistsException;
033: import com.nabhinc.spi.NoSuchEntityException;
034: import com.nabhinc.spi.RoleAdminService;
035: import com.nabhinc.spi.UserRole;
036: import com.nabhinc.spi.UserRoleImpl;
037: import com.nabhinc.util.db.DBUtil;
038:
039: /**
040: *
041: *
042: * @author Padmanabh Dabke
043: * (c) 2006 Nabh Information Systems, Inc. All Rights Reserved.
044: */
045: public class RoleAdminServiceDBImpl extends BaseDatabaseServiceImpl
046: implements RoleAdminService {
047:
048: private String uasiRoleMemberSQL = "SELECT SB_ROLES.roleid FROM SB_USER_ROLES, SB_USERS, SB_ROLES WHERE SB_USERS.userid = SB_USER_ROLES.userid AND SB_ROLES.roleid = SB_USER_ROLES.roleid AND SB_USERS.username = ? AND SB_ROLES.rolename = ?";
049: private String uasiRolesForUserNameSQL = "SELECT SB_ROLES.rolename FROM SB_USER_ROLES, SB_USERS, SB_ROLES WHERE SB_USERS.userid = SB_USER_ROLES.userid AND SB_ROLES.roleid = SB_USER_ROLES.roleid AND SB_USERS.username = ?";
050: private String uasiRolesForUserIDSQL = "SELECT SB_ROLES.rolename FROM SB_USER_ROLES, SB_ROLES WHERE SB_ROLES.roleid = SB_USER_ROLES.roleid AND SB_USER_ROLES.userid = ?";
051: private String uasiRoleIDsForUserIDSQL = "SELECT roleid FROM SB_USER_ROLES WHERE userid = ?";
052: private String uasiCreateRoleSQL = "INSERT INTO SB_ROLES (rolename, descr) VALUES (?, ?)";
053: private String uasiDeleteRoleSQL = "DELETE FROM SB_ROLES WHERE roleid = ?";
054: private String uasiUpdateRoleSQL = "UPDATE SB_ROLES SET rolename = ?, descr = ? WHERE roleid = ?";
055: private String uasiSelectRolesSQL = "SELECT roleid, rolename, descr FROM SB_ROLES";
056: private String uasiRoleExistsSQL = "SELECT rolename FROM SB_ROLES WHERE rolename = ? AND roleid != ?";
057: private String uasiRoleForRoleIDSQL = "SELECT rolename, descr FROM SB_ROLES WHERE roleid = ?";
058: private String uasiRoleCountSQL = "SELECT count(*) FROM SB_ROLES";
059:
060: public String getRolesForUserNameSQL() {
061: return uasiRolesForUserNameSQL;
062: }
063:
064: public void setRolesForUserNameSQL(String roleSQL) {
065: uasiRolesForUserNameSQL = roleSQL;
066: }
067:
068: public String getCreateRoleSQL() {
069: return uasiCreateRoleSQL;
070: }
071:
072: public void setCreateRoleSQL(String uasiCreateRoleSQL) {
073: this .uasiCreateRoleSQL = uasiCreateRoleSQL;
074: }
075:
076: public String getDeleteRoleSQL() {
077: return uasiDeleteRoleSQL;
078: }
079:
080: public void setDeleteRoleSQL(String uasiDeleteRoleSQL) {
081: this .uasiDeleteRoleSQL = uasiDeleteRoleSQL;
082: }
083:
084: public String getSelectRolesSQL() {
085: return uasiSelectRolesSQL;
086: }
087:
088: public void setSelectRolesSQL(String uasiSelectRolesSQL) {
089: this .uasiSelectRolesSQL = uasiSelectRolesSQL;
090: }
091:
092: public String getUpdateRoleSQL() {
093: return uasiUpdateRoleSQL;
094: }
095:
096: public void setUpdateRoleSQL(String uasiUpdateRoleSQL) {
097: this .uasiUpdateRoleSQL = uasiUpdateRoleSQL;
098: }
099:
100: public String getRoleCountSQL() {
101: return uasiRoleCountSQL;
102: }
103:
104: public void setRoleCountSQL(String sql) {
105: this .uasiRoleCountSQL = sql;
106: }
107:
108: @SuppressWarnings("unchecked")
109: public String[] getUserRoles(String userName)
110: throws RemoteException {
111: Connection conn = null;
112: ResultSet results = null;
113: PreparedStatement st = null;
114:
115: try {
116: conn = DBUtil.getConnection(bdsiDataSource);
117: st = conn.prepareStatement(uasiRolesForUserNameSQL);
118: st.setString(1, userName);
119: results = st.executeQuery();
120: Vector v = new Vector();
121: while (results.next()) {
122: v.add(results.getString(1));
123: }
124: String[] roles = new String[v.size()];
125: v.copyInto(roles);
126: return roles;
127:
128: } catch (NamingException ex) {
129: throw new RemoteException("Failed to look up data source: "
130: + bdsiDataSource, ex);
131: } catch (SQLException ex) {
132: throw new RemoteException("System exception.", ex);
133: } finally {
134: DBUtil.close(st);
135: DBUtil.close(results);
136: DBUtil.close(conn);
137: }
138: }
139:
140: @SuppressWarnings("unchecked")
141: public String[] getUserRoles(int userID) throws RemoteException {
142: Connection conn = null;
143: ResultSet results = null;
144: PreparedStatement st = null;
145:
146: try {
147: conn = DBUtil.getConnection(bdsiDataSource);
148: st = conn.prepareStatement(uasiRolesForUserIDSQL);
149: st.setInt(1, userID);
150: results = st.executeQuery();
151: Vector v = new Vector();
152: while (results.next()) {
153: v.add(results.getString(1));
154: }
155: String[] roles = new String[v.size()];
156: v.copyInto(roles);
157: return roles;
158:
159: } catch (NamingException ex) {
160: throw new RemoteException("Failed to look up data source: "
161: + bdsiDataSource, ex);
162: } catch (SQLException ex) {
163: throw new RemoteException("System exception.", ex);
164: } finally {
165: DBUtil.close(st);
166: DBUtil.close(results);
167: DBUtil.close(conn);
168: }
169: }
170:
171: @SuppressWarnings("unchecked")
172: public int[] getUserRoleIDs(int userID) throws RemoteException {
173: Connection conn = null;
174: ResultSet results = null;
175: PreparedStatement st = null;
176:
177: try {
178: conn = DBUtil.getConnection(bdsiDataSource);
179: st = conn.prepareStatement(uasiRoleIDsForUserIDSQL);
180: st.setInt(1, userID);
181: results = st.executeQuery();
182: Vector v = new Vector();
183: while (results.next()) {
184: v.add(new Integer(results.getInt(1)));
185: }
186: int[] roles = new int[v.size()];
187: for (int i = 0; i < v.size(); i++) {
188: roles[i] = ((Integer) v.elementAt(i)).intValue();
189: }
190: return roles;
191:
192: } catch (NamingException ex) {
193: throw new RemoteException("Failed to look up data source: "
194: + bdsiDataSource, ex);
195: } catch (SQLException ex) {
196: throw new RemoteException("System exception.", ex);
197: } finally {
198: DBUtil.close(st);
199: DBUtil.close(results);
200: DBUtil.close(conn);
201: }
202: }
203:
204: public boolean isUserInRole(String userName, String roleName)
205: throws RemoteException {
206: Connection conn = null;
207: PreparedStatement st = null;
208: ResultSet results = null;
209: try {
210: conn = DBUtil.getConnection(bdsiDataSource);
211: st = conn.prepareStatement(uasiRoleMemberSQL);
212: st.setString(1, userName);
213: st.setString(2, roleName);
214: results = st.executeQuery();
215: return results.next();
216:
217: } catch (NamingException ex) {
218: throw new RemoteException("Failed to look up data source: "
219: + bdsiDataSource, ex);
220: } catch (SQLException ex) {
221: throw new RemoteException("System exception.", ex);
222: } finally {
223: DBUtil.close(results);
224: DBUtil.close(st);
225: DBUtil.close(conn);
226: }
227:
228: }
229:
230: public void createUserRole(UserRole role)
231: throws EntityExistsException, RemoteException {
232: Connection conn = null;
233: PreparedStatement st = null;
234: ResultSet results = null;
235: try {
236: conn = DBUtil.getConnection(bdsiDataSource);
237:
238: // First check if a role with specified name already exists.
239: st = conn.prepareStatement(uasiRoleExistsSQL);
240: st.setString(1, role.getName());
241: st.setInt(2, -1);
242: results = st.executeQuery();
243: if (results.next())
244: throw new EntityExistsException();
245: results.close();
246: results = null;
247: st.close();
248: st = null;
249:
250: st = conn.prepareStatement(uasiCreateRoleSQL);
251: st.setString(1, role.getName());
252: st.setString(2, role.getDescription());
253: st.execute();
254:
255: } catch (NamingException ex) {
256: throw new RemoteException("Failed to look up data source: "
257: + bdsiDataSource, ex);
258: } catch (SQLException ex) {
259: throw new RemoteException("System exception.", ex);
260: } finally {
261: DBUtil.close(results);
262: DBUtil.close(st);
263: DBUtil.close(conn);
264: }
265:
266: }
267:
268: public void updateUserRole(UserRole role) throws RemoteException,
269: EntityExistsException {
270: Connection conn = null;
271: PreparedStatement st = null;
272: ResultSet results = null;
273: try {
274: conn = DBUtil.getConnection(bdsiDataSource);
275:
276: // First check if a role with specified name already exists.
277: st = conn.prepareStatement(uasiRoleExistsSQL);
278: st.setString(1, role.getName());
279: st.setInt(2, role.getId());
280: results = st.executeQuery();
281: if (results.next())
282: throw new EntityExistsException();
283: results.close();
284: results = null;
285: st.close();
286:
287: st = null;
288: st = conn.prepareStatement(uasiUpdateRoleSQL);
289: st.setString(1, role.getName());
290: st.setString(2, role.getDescription());
291: st.setInt(3, role.getId());
292: st.execute();
293:
294: } catch (NamingException ex) {
295: throw new RemoteException("Failed to look up data source: "
296: + bdsiDataSource, ex);
297: } catch (SQLException ex) {
298: throw new RemoteException("System exception.", ex);
299: } finally {
300: DBUtil.close(results);
301: DBUtil.close(st);
302: DBUtil.close(conn);
303: }
304:
305: }
306:
307: public void deleteUserRoles(int[] roleID) throws RemoteException {
308: Connection conn = null;
309: PreparedStatement st = null;
310:
311: try {
312: conn = DBUtil.getConnection(bdsiDataSource);
313: conn.setAutoCommit(false);
314: st = conn.prepareStatement(uasiDeleteRoleSQL);
315: for (int i = 0; i < roleID.length; i++) {
316: st.setInt(1, roleID[i]);
317: st.execute();
318: }
319:
320: conn.commit();
321: } catch (NamingException ex) {
322: try {
323: if (conn != null)
324: conn.rollback();
325: } catch (Exception e) { /* Ignore */
326: }
327: throw new RemoteException("Failed to look up data source: "
328: + bdsiDataSource, ex);
329: } catch (SQLException ex) {
330: try {
331: if (conn != null)
332: conn.rollback();
333: } catch (Exception e) { /* Ignore */
334: }
335: throw new RemoteException("System exception.", ex);
336: } finally {
337: DBUtil.close(st);
338: DBUtil.close(conn);
339: }
340:
341: }
342:
343: @SuppressWarnings("unchecked")
344: public List getUserRoles(int offset, int maxRoles, String orderby,
345: boolean isDescending) throws RemoteException {
346: Connection conn = null;
347: ResultSet results = null;
348: PreparedStatement st = null;
349:
350: try {
351: String selectSQL = uasiSelectRolesSQL;
352: if (orderby != null) {
353: selectSQL += " ORDER BY " + orderby;
354: if (isDescending)
355: selectSQL += " DESC";
356: }
357:
358: conn = DBUtil.getConnection(bdsiDataSource);
359: st = conn.prepareStatement(selectSQL);
360: results = st.executeQuery();
361: List v = new ArrayList();
362:
363: // Skip upto offset
364: for (int i = 0; i < offset; i++) {
365: if (!results.next())
366: return v;
367: }
368:
369: // Create maxRoles roles and add them to the vector.
370: for (int i = 0; i < maxRoles; i++) {
371: if (!results.next())
372: break;
373: int roleID = results.getInt(1);
374: String roleName = results.getString(2);
375: String roleDesc = results.getString(3);
376: UserRole role = new UserRoleImpl();
377: role.setId(roleID);
378: role.setName(roleName);
379: role.setDescription(roleDesc);
380: v.add(role);
381: }
382:
383: return v;
384:
385: } catch (NamingException ex) {
386: throw new RemoteException("Failed to look up data source: "
387: + bdsiDataSource, ex);
388: } catch (SQLException ex) {
389: throw new RemoteException("System exception.", ex);
390: } finally {
391: DBUtil.close(st);
392: DBUtil.close(results);
393: DBUtil.close(conn);
394: }
395: }
396:
397: public UserRole getUserRole(int roleID)
398: throws NoSuchEntityException, RemoteException {
399: Connection conn = null;
400: ResultSet results = null;
401: PreparedStatement st = null;
402:
403: try {
404: conn = DBUtil.getConnection(bdsiDataSource);
405: st = conn.prepareStatement(uasiRoleForRoleIDSQL);
406: st.setInt(1, roleID);
407: results = st.executeQuery();
408: if (!results.next())
409: throw new NoSuchEntityException();
410: UserRole role = new UserRoleImpl();
411: role.setId(roleID);
412: role.setName(results.getString(1));
413: role.setDescription(results.getString(2));
414: return role;
415: } catch (NamingException ex) {
416: throw new RemoteException("Failed to look up data source: "
417: + bdsiDataSource, ex);
418: } catch (SQLException ex) {
419: throw new RemoteException("System exception.", ex);
420: } finally {
421: DBUtil.close(st);
422: DBUtil.close(results);
423: DBUtil.close(conn);
424: }
425: }
426:
427: public int getUserRoleCount() throws RemoteException {
428: Connection conn = null;
429: ResultSet results = null;
430: PreparedStatement st = null;
431:
432: try {
433: conn = DBUtil.getConnection(bdsiDataSource);
434: st = conn.prepareStatement(uasiRoleCountSQL);
435: results = st.executeQuery();
436: results.next();
437: return results.getInt(1);
438:
439: } catch (NamingException ex) {
440: throw new RemoteException("Failed to look up data source: "
441: + bdsiDataSource, ex);
442: } catch (SQLException ex) {
443: throw new RemoteException("System exception.", ex);
444: } finally {
445: DBUtil.close(st);
446: DBUtil.close(results);
447: DBUtil.close(conn);
448: }
449: }
450:
451: }
|