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.Vector;
027:
028: import javax.naming.NamingException;
029:
030: import com.nabhinc.spi.EntityExistsException;
031: import com.nabhinc.spi.GroupAdminService;
032: import com.nabhinc.spi.UserGroup;
033: import com.nabhinc.util.db.DBUtil;
034: import com.nabhinc.ws.server.ServerObjectImpl;
035:
036: /**
037: *
038: *
039: * @author Padmanabh Dabke
040: * (c) 2006 Nabh Information Systems, Inc. All Rights Reserved.
041: */
042: public class GroupAdminServiceDBImpl extends ServerObjectImpl implements
043: GroupAdminService {
044: private String uasiDataSource = null;
045:
046: // Properties related to group administration
047: private String uasiGroupIDColumn = "groupid";
048: private String uasiGroupNameColumn = "groupname";
049: private String uasiGroupDescriptionColumn = "descr";
050: private String uasiGroupOwnerColumn = "groupowner";
051: private String uasiGroupMemberSQL = "SELECT SB_GROUPS.groupid FROM SB_USER_GROUPS, SB_USERS, SB_GROUPS WHERE SB_USERS.userid = SB_USER_GROUPS.userid AND SB_GROUPS.groupid = SB_USER_GROUPS.groupid AND SB_USERS.username = ? AND SB_GROUPS.groupname = ?";
052: private String uasiCreateGroupSQL = "INSERT INTO SB_GROUPS (groupname, descr, groupowner) VALUES (?, ?)";
053: private String uasiDeleteGroupSQL = "DELETE FROM SB_GROUPS WHERE groupid = ?";
054: private String uasiUpdateGroupSQL = "UPDATE SB_GROUPS SET groupname = ?, descr = ?, groupowner = ? WHERE groupid = ?";
055: private String uasiSelectGroupsSQL = "SELECT groupid, groupname, descr, groupowner FROM SB_GROUPS";
056: private String uasiGroupExistsSQL = "SELECT groupname FROM SB_GROUPS WHERE groupname = ? AND groupid != ?";
057: private String[] uasiGroupColumns = { uasiGroupIDColumn,
058: uasiGroupNameColumn, uasiGroupDescriptionColumn,
059: uasiGroupOwnerColumn };
060:
061: public String getDataSourceName() {
062: return uasiDataSource;
063: }
064:
065: public void setDataSourceName(String ds) {
066: uasiDataSource = ds;
067: }
068:
069: public String getGroupNameColumn() {
070: return uasiGroupNameColumn;
071: }
072:
073: public void setGroupNameColumn(String col) {
074: uasiGroupNameColumn = col;
075: }
076:
077: public String getGroupDescriptionColumn() {
078: return uasiGroupDescriptionColumn;
079: }
080:
081: public void setGroupDescriptionColumn(String col) {
082: uasiGroupDescriptionColumn = col;
083: }
084:
085: public String getCreateGroupSQL() {
086: return uasiCreateGroupSQL;
087: }
088:
089: public void setCreateGroupSQL(String uasiCreateGroupSQL) {
090: this .uasiCreateGroupSQL = uasiCreateGroupSQL;
091: }
092:
093: public String getDeleteGroupSQL() {
094: return uasiDeleteGroupSQL;
095: }
096:
097: public void setDeleteGroupSQL(String uasiDeleteGroupSQL) {
098: this .uasiDeleteGroupSQL = uasiDeleteGroupSQL;
099: }
100:
101: public String getSelectGroupsSQL() {
102: return uasiSelectGroupsSQL;
103: }
104:
105: public void setSelectGroupsSQL(String uasiSelectGroupsSQL) {
106: this .uasiSelectGroupsSQL = uasiSelectGroupsSQL;
107: }
108:
109: public String getUpdateGroupSQL() {
110: return uasiUpdateGroupSQL;
111: }
112:
113: public void setUpdateGroupSQL(String uasiUpdateGroupSQL) {
114: this .uasiUpdateGroupSQL = uasiUpdateGroupSQL;
115: }
116:
117: public String getGroupMemberSQL() {
118: return this .uasiGroupMemberSQL;
119: }
120:
121: public void setGroupMemberSQL(String sql) {
122: this .uasiGroupMemberSQL = sql;
123: }
124:
125: public boolean isUserInGroup(String userName, String groupName)
126: throws RemoteException {
127: Connection conn = null;
128: PreparedStatement st = null;
129: ResultSet results = null;
130: try {
131: conn = DBUtil.getConnection(uasiDataSource);
132: st = conn.prepareStatement(uasiGroupMemberSQL);
133: st.setString(1, userName);
134: st.setString(2, groupName);
135: results = st.executeQuery();
136: return results.next();
137:
138: } catch (NamingException ex) {
139: throw new RemoteException("Failed to look up data source: "
140: + uasiDataSource, ex);
141: } catch (SQLException ex) {
142: throw new RemoteException("System exception.", ex);
143: } finally {
144: DBUtil.close(results);
145: DBUtil.close(st);
146: DBUtil.close(conn);
147: }
148:
149: }
150:
151: public void createUserGroup(UserGroup group)
152: throws EntityExistsException, RemoteException {
153: Connection conn = null;
154: PreparedStatement st = null;
155: ResultSet results = null;
156: try {
157: conn = DBUtil.getConnection(uasiDataSource);
158:
159: // First check if a group with specified name already exists.
160: st = conn.prepareStatement(uasiGroupExistsSQL);
161: st.setString(1, group.getName());
162: st.setInt(2, -1);
163: results = st.executeQuery();
164: if (results.next())
165: throw new EntityExistsException();
166: results.close();
167: results = null;
168: st.close();
169: st = null;
170:
171: st = conn.prepareStatement(uasiCreateGroupSQL);
172: st.setString(1, group.getName());
173: st.setString(2, group.getDescription());
174: st.setInt(3, group.getOwnerID());
175: st.execute();
176:
177: } catch (NamingException ex) {
178: throw new RemoteException("Failed to look up data source: "
179: + uasiDataSource, ex);
180: } catch (SQLException ex) {
181: throw new RemoteException("System exception.", ex);
182: } finally {
183: DBUtil.close(results);
184: DBUtil.close(st);
185: DBUtil.close(conn);
186: }
187:
188: }
189:
190: public void updateUserGroup(UserGroup group)
191: throws EntityExistsException, RemoteException {
192: Connection conn = null;
193: PreparedStatement st = null;
194: ResultSet results = null;
195: try {
196: conn = DBUtil.getConnection(uasiDataSource);
197:
198: // First check if a group with specified name already exists.
199: st = conn.prepareStatement(uasiGroupExistsSQL);
200: st.setString(1, group.getName());
201: st.setInt(2, -1);
202: results = st.executeQuery();
203: if (results.next())
204: throw new EntityExistsException();
205: results.close();
206: results = null;
207: st.close();
208: st = null;
209: st = conn.prepareStatement(uasiUpdateGroupSQL);
210: st.setString(1, group.getName());
211: st.setString(2, group.getDescription());
212: st.setInt(3, group.getOwnerID());
213: st.setInt(4, group.getID());
214: st.execute();
215:
216: } catch (NamingException ex) {
217: throw new RemoteException("Failed to look up data source: "
218: + uasiDataSource, ex);
219: } catch (SQLException ex) {
220: throw new RemoteException("System exception.", ex);
221: } finally {
222: DBUtil.close(results);
223: DBUtil.close(st);
224: DBUtil.close(conn);
225: }
226:
227: }
228:
229: public void deleteUserGroup(int groupID) throws RemoteException {
230: Connection conn = null;
231: PreparedStatement st = null;
232:
233: try {
234: conn = DBUtil.getConnection(uasiDataSource);
235: st = conn.prepareStatement(uasiDeleteGroupSQL);
236: st.setInt(1, groupID);
237: st.execute();
238:
239: } catch (NamingException ex) {
240: throw new RemoteException("Failed to look up data source: "
241: + uasiDataSource, ex);
242: } catch (SQLException ex) {
243: throw new RemoteException("System exception.", ex);
244: } finally {
245: DBUtil.close(st);
246: DBUtil.close(conn);
247: }
248:
249: }
250:
251: @SuppressWarnings("unchecked")
252: public UserGroup[] getUserGroups(int offset, int maxGroups,
253: int orderby, boolean isDescending) throws RemoteException {
254: Connection conn = null;
255: ResultSet results = null;
256: PreparedStatement st = null;
257:
258: try {
259: String selectSQL = uasiSelectGroupsSQL;
260: if (orderby > -1 && orderby < MAX_GROUP_COLUMN_INDEX) {
261: selectSQL += " ORDER BY " + uasiGroupColumns[orderby];
262: if (isDescending)
263: selectSQL += " DESC";
264: }
265:
266: conn = DBUtil.getConnection(uasiDataSource);
267: st = conn.prepareStatement(selectSQL);
268: results = st.executeQuery();
269: Vector v = new Vector();
270:
271: // Skip upto offset
272: for (int i = 0; i < offset; i++) {
273: if (!results.next())
274: return new UserGroup[0];
275: }
276:
277: // Create maxGroups groups and add them to the vector.
278: for (int i = 0; i < maxGroups; i++) {
279: if (!results.next())
280: break;
281: int groupID = results.getInt(1);
282: String groupName = results.getString(2);
283: String groupDesc = results.getString(3);
284: int ownerID = results.getInt(4);
285: UserGroup group = new UserGroup();
286: group.setID(groupID);
287: group.setName(groupName);
288: group.setDescription(groupDesc);
289: group.setOwnerID(ownerID);
290: v.addElement(group);
291: }
292:
293: UserGroup[] groups = new UserGroup[v.size()];
294: v.copyInto(groups);
295: return groups;
296:
297: } catch (NamingException ex) {
298: throw new RemoteException("Failed to look up data source: "
299: + uasiDataSource, ex);
300: } catch (SQLException ex) {
301: throw new RemoteException("System exception.", ex);
302: } finally {
303: DBUtil.close(st);
304: DBUtil.close(results);
305: DBUtil.close(conn);
306: }
307: }
308:
309: }
|