KickJava   Java API By Example, From Geeks To Geeks.

Java > Open Source Codes > org > manentia > kasai > group > JDBCMySQLGroupDAO


1 /*
2  * JDBCMySQLUserDAO.java
3  *
4  * Created on 28 de marzo de 2005, 13:46
5  */

6
7 package org.manentia.kasai.group;
8
9 import com.koala.commons.log.Log;
10 import com.koala.commons.persistence.DBUtil;
11 import java.sql.*;
12 import java.util.ArrayList JavaDoc;
13 import java.util.Arrays JavaDoc;
14 import java.util.Collection JavaDoc;
15 import java.util.logging.Level JavaDoc;
16 import org.apache.commons.lang.StringUtils;
17 import org.apache.commons.lang.exception.ExceptionUtils;
18 import org.manentia.kasai.Group;
19 import org.manentia.kasai.Operative;
20 import org.manentia.kasai.Role;
21 import org.manentia.kasai.User;
22 import org.manentia.kasai.exceptions.AlreadyExistsException;
23 import org.manentia.kasai.exceptions.DataAccessException;
24 import org.manentia.kasai.exceptions.DoesntExistsException;
25 import org.manentia.kasai.exceptions.InvalidAttributesException;
26 import org.manentia.kasai.user.UserHandler;
27 import org.manentia.kasai.util.Constants;
28
29 /**
30  *
31  * @author rzuasti
32  */

33 public class JDBCMySQLGroupDAO implements GroupDAO {
34     
35     /** Creates a new instance of JDBCMySQLUserDAO */
36     public JDBCMySQLGroupDAO() {
37     }
38    
39     public void addUserToGroup(String JavaDoc login, String JavaDoc group) throws DoesntExistsException,DataAccessException{
40         Connection con = null;
41         if (UserHandler.getInstance().read(login,true) == null){
42             Log.getInstance(Constants.PROPERTY_FILE).write (this.getClass().getName(), "apply", "User doesn't exist",Level.WARNING);
43             throw new DoesntExistsException(User.class.getName() + ".userDoesntExist");
44         }
45         if (this.read(group) == null){
46             Log.getInstance(Constants.PROPERTY_FILE).write (this.getClass().getName(), "apply", "Group doesn't exist",Level.WARNING);
47             throw new DoesntExistsException(Group.class.getName() + ".groupDoesntExist");
48         }
49         if (UserHandler.getInstance().list(login,null,null,null,-1,null,group).size()==0){
50             try{
51                 String JavaDoc sql = "insert into kasai_users_groups (id_user,id_group) values ('" + org.apache.commons.lang.StringEscapeUtils.escapeSql(login) + "','" + org.apache.commons.lang.StringEscapeUtils.escapeSql(group) + "')";
52                 con = DBUtil.getConnection(Constants.DATABASE_SOURCE, Constants.PROPERTY_FILE);
53                 con.createStatement().executeUpdate(sql);
54             }catch (SQLException sqle){
55                 Log.getInstance(Constants.PROPERTY_FILE).write (this.getClass().getName(), "addUserToGroup", ExceptionUtils.getStackTrace(sqle),Level.SEVERE);
56                 throw new DataAccessException(sqle);
57             }finally{
58                 try{
59                     con.close();
60                 }catch(Exception JavaDoc e){}
61             }
62         }
63     }
64     
65     public boolean checkUserBelongsToGroup(String JavaDoc user, String JavaDoc group) throws DataAccessException{
66         Connection con = null;
67         String JavaDoc sql;
68         ResultSet rs = null;
69         try{
70             sql = "SELECT AU.* FROM kasai_users AU";
71             sql += ", kasai_users_groups AUG WHERE AUG.id_user=AU.id AND AUG.id_group='" + org.apache.commons.lang.StringEscapeUtils.escapeSql(group) +"'";
72             sql += " AND AU.id = '" + org.apache.commons.lang.StringEscapeUtils.escapeSql(user) +"'";
73             con = DBUtil.getConnection(Constants.DATABASE_SOURCE, Constants.PROPERTY_FILE);
74             rs = con.createStatement().executeQuery(sql);
75             if (rs.next()){
76                 return true;
77             }
78             else{
79                 return false;
80             }
81                 
82         }catch (SQLException sqle){
83             Log.getInstance(Constants.PROPERTY_FILE).write (this.getClass().getName(), "checkUserBelongsToGroup", ExceptionUtils.getStackTrace(sqle),Level.SEVERE);
84             throw new DataAccessException(sqle);
85         }finally{
86             try{
87                 rs.close();
88             }catch(Exception JavaDoc e){}
89             try{
90                 con.close();
91             }catch(Exception JavaDoc e){}
92         }
93         
94     }
95     
96     public void create(String JavaDoc id, String JavaDoc description, boolean blocked) throws InvalidAttributesException, AlreadyExistsException, DataAccessException{
97             
98         Connection con = null;
99         String JavaDoc sql;
100         Group g = null;
101         try{
102             
103             g = new Group();
104             g.setBlocked(blocked);
105             g.setDescription(description);
106             g.setId(StringUtils.defaultString(id).trim());
107             g.validate();
108             
109             g = this.read(id);
110             if (g != null){
111                 Log.getInstance(Constants.PROPERTY_FILE).write (this.getClass().getName(),"create","Group name already exist",Level.WARNING);
112                 throw new AlreadyExistsException(this.getClass().getName() + ".groupAlreadyExist");
113             }
114             sql = "INSERT INTO kasai_groups (id, blocked, description) VALUES (?,?,?)";
115             con = DBUtil.getConnection(Constants.DATABASE_SOURCE, Constants.PROPERTY_FILE);
116             PreparedStatement stm = con.prepareStatement(sql);
117             stm.setString(1,id);
118             if (blocked){
119                 stm.setInt(2, 1);
120             }else{
121                 stm.setInt(2, 0);
122             }
123             stm.setString(3, description);
124             stm.executeUpdate();
125         }catch (SQLException sqle){
126             Log.getInstance(Constants.PROPERTY_FILE).write (this.getClass().getName(), "create", ExceptionUtils.getStackTrace(sqle),Level.SEVERE);
127             throw new DataAccessException(sqle);
128         }finally{
129             try{
130                 
131                 con.close();
132             }catch(Exception JavaDoc e){}
133         }
134     }
135     
136     public void delete(String JavaDoc group) throws DataAccessException{
137         Connection con = null;
138         String JavaDoc sql;
139         try{
140             if (StringUtils.isNotEmpty(group)){
141                 sql = "DELETE FROM kasai_groups WHERE id='" + org.apache.commons.lang.StringEscapeUtils.escapeSql(group) + "'";
142                 con = DBUtil.getConnection(Constants.DATABASE_SOURCE, Constants.PROPERTY_FILE);
143                 con.createStatement().executeUpdate(sql);
144             }
145         }catch (SQLException sqle){
146             Log.getInstance(Constants.PROPERTY_FILE).write (this.getClass().getName(), "delete", ExceptionUtils.getStackTrace(sqle),Level.SEVERE);
147             throw new DataAccessException(sqle);
148         }finally{
149             try{
150                 con.close();
151             }catch(Exception JavaDoc e){}
152         }
153         
154     }
155     
156     public void deleteUserFromGroup(String JavaDoc login, String JavaDoc group) throws DataAccessException{
157         Connection con = null;
158         if (StringUtils.isNotEmpty(login) && StringUtils.isNotEmpty(group)){
159             try{
160                 String JavaDoc sql = "delete from kasai_users_groups where id_user='" + org.apache.commons.lang.StringEscapeUtils.escapeSql(login) + "' and id_group='" + org.apache.commons.lang.StringEscapeUtils.escapeSql(group) + "'";
161                 con = DBUtil.getConnection(Constants.DATABASE_SOURCE, Constants.PROPERTY_FILE);
162                 con.createStatement().executeUpdate(sql);
163             }catch (SQLException sqle){
164                 Log.getInstance(Constants.PROPERTY_FILE).write (this.getClass().getName(), "deleteUserFromGroup", ExceptionUtils.getStackTrace(sqle),Level.SEVERE);
165                 throw new DataAccessException(sqle);
166             }finally {
167                 try{
168                     con.close();
169                 }catch(Exception JavaDoc e){}
170             }
171         }
172     }
173     
174     public Collection JavaDoc list(String JavaDoc idGroup, String JavaDoc description, int blocked, int system, String JavaDoc login) throws DataAccessException{
175         Connection con = null;
176         String JavaDoc sql;
177         ResultSet rs = null;
178         Group g = null;
179         ArrayList JavaDoc groups = new ArrayList JavaDoc();
180         try{
181             sql = "SELECT AG.* FROM kasai_groups AG ";
182             if (StringUtils.isNotEmpty(login)){
183                 sql += ", kasai_users_groups AUG WHERE AUG.id_group=AG.id AND AUG.id_user='" + org.apache.commons.lang.StringEscapeUtils.escapeSql(login) +"'";
184             }
185             else{
186                 sql += " WHERE AG.id <> ''";
187             }
188             if (StringUtils.isNotEmpty(idGroup)){
189                 sql += " AND AG.id LIKE '%" + org.apache.commons.lang.StringEscapeUtils.escapeSql(idGroup) +"%'";
190             }
191             if (blocked != -1){
192                 sql += " AND AG.blocked = " + blocked;
193             }
194             if (system != -1){
195                 sql += " AND AG.system = " + system;
196             }
197             if (StringUtils.isNotEmpty(description)){
198                 sql += " AND AG.description LIKE '%" + org.apache.commons.lang.StringEscapeUtils.escapeSql(description) +"%'";
199             }
200             sql += " order by AG.id ";
201             
202             con = DBUtil.getConnection(Constants.DATABASE_SOURCE, Constants.PROPERTY_FILE);
203             rs = con.createStatement().executeQuery(sql);
204             while (rs.next()){
205                 g = new Group (rs);
206                 groups.add(g);
207             }
208             return groups;
209         }catch (SQLException sqle){
210             Log.getInstance(Constants.PROPERTY_FILE).write (this.getClass().getName(), "list", ExceptionUtils.getStackTrace(sqle),Level.SEVERE);
211             throw new DataAccessException(sqle);
212         }finally{
213             try{
214                 rs.close();
215             }catch(Exception JavaDoc e){}
216             try{
217                 con.close();
218             }catch(Exception JavaDoc e){}
219         }
220         
221     }
222     
223     public Collection JavaDoc listUsersNotInGroup(String JavaDoc group) throws DataAccessException {
224
225         Connection con = null;
226         String JavaDoc sql;
227         ResultSet rs = null;
228         User u = null;
229         ArrayList JavaDoc members = new ArrayList JavaDoc();
230         ArrayList JavaDoc users = new ArrayList JavaDoc();
231
232         try {
233             sql = "SELECT AU.* FROM kasai_users AU,kasai_users_groups AUG WHERE AUG.id_user=AU.id AND AUG.id_group='" + org.apache.commons.lang.StringEscapeUtils.escapeSql(group) + "'";
234             sql += " order by AU.last_name, AU.first_name, AU.id ";
235             con = DBUtil.getConnection(Constants.DATABASE_SOURCE, Constants.PROPERTY_FILE);
236             rs = con.createStatement().executeQuery(sql);
237
238             while (rs.next()) {
239
240                 u = new User(rs);
241                 members.add(u);
242             }
243
244             sql = "SELECT * FROM kasai_users";
245             sql += " order by last_name, first_name, id ";
246             con = DBUtil.getConnection(Constants.DATABASE_SOURCE, Constants.PROPERTY_FILE);
247             rs = con.createStatement().executeQuery(sql);
248
249             while (rs.next()) {
250                 u = new User(rs);
251                 if (!members.contains(u)) {
252                     users.add(u);
253                 }
254             }
255             
256             return users;
257         }catch (SQLException sqle){
258             Log.getInstance(Constants.PROPERTY_FILE).write (this.getClass().getName(), "listUsersNotInGroup", ExceptionUtils.getStackTrace(sqle),Level.SEVERE);
259             throw new DataAccessException(sqle);
260         } finally {
261
262             try {
263
264                 rs.close();
265             } catch (Exception JavaDoc e) {}
266
267             try {
268
269                 con.close();
270             } catch (Exception JavaDoc e) {}
271         }
272     }
273     
274     public Group read(String JavaDoc group) throws DataAccessException{
275         Connection con = null;
276         String JavaDoc sql;
277         ResultSet rs = null;
278         Group g = null;
279         try{
280             if (StringUtils.isNotEmpty(group)){
281                 sql = "SELECT * FROM kasai_groups WHERE id='" + org.apache.commons.lang.StringEscapeUtils.escapeSql(group) + "'";
282                 con = DBUtil.getConnection(Constants.DATABASE_SOURCE, Constants.PROPERTY_FILE);
283                 rs = con.createStatement().executeQuery(sql);
284                 if (rs.next()){
285                     g = new Group (rs);
286                 }
287             }
288             return g;
289         }catch (SQLException sqle){
290             Log.getInstance(Constants.PROPERTY_FILE).write (this.getClass().getName(), "read", ExceptionUtils.getStackTrace(sqle),Level.SEVERE);
291             throw new DataAccessException(sqle);
292         }finally{
293             try{
294                 rs.close();
295             }catch(Exception JavaDoc e){}
296             try{
297                 con.close();
298             }catch(Exception JavaDoc e){}
299         }
300         
301     }
302     
303     public void update(
304         String JavaDoc id,
305         boolean blocked,
306         String JavaDoc description
307     ) throws InvalidAttributesException, DataAccessException {
308
309         Connection con = null;
310         String JavaDoc sql;
311         Group g = null;
312
313         try {
314
315             g = new Group();
316             g.setBlocked(blocked);
317             g.setDescription(description);
318             g.setId(id);
319             g.validate();
320
321             sql = "UPDATE kasai_groups set blocked=?, description=? where id=?";
322             con = DBUtil.getConnection(Constants.DATABASE_SOURCE, Constants.PROPERTY_FILE);
323
324             PreparedStatement stm = con.prepareStatement(sql);
325
326             if (blocked) {
327
328                 stm.setInt(1, 1);
329             } else {
330
331                 stm.setInt(1, 0);
332             }
333
334             stm.setString(2, description);
335             stm.setString(3, id);
336             stm.executeUpdate();
337         }catch (SQLException sqle){
338             Log.getInstance(Constants.PROPERTY_FILE).write (this.getClass().getName(), "update", ExceptionUtils.getStackTrace(sqle),Level.SEVERE);
339             throw new DataAccessException(sqle);
340         } finally {
341
342             try {
343
344                 con.close();
345             } catch (Exception JavaDoc e) {}
346         }
347     }
348
349     public void update(
350         String JavaDoc id,
351         boolean blocked,
352         String JavaDoc description,
353         String JavaDoc[] members
354     ) throws InvalidAttributesException, DataAccessException {
355
356         Connection con = null;
357         Group g = null;
358         ResultSet rs = null;
359         String JavaDoc sql;
360
361         try {
362
363             g = new Group();
364             g.setBlocked(blocked);
365             g.setDescription(description);
366             g.setId(id);
367             g.validate();
368
369             sql = "UPDATE kasai_groups set blocked=?, description=? where id=?";
370             con = DBUtil.getConnection(Constants.DATABASE_SOURCE, Constants.PROPERTY_FILE);
371
372             PreparedStatement stm = con.prepareStatement(sql);
373
374             if (blocked) {
375
376                 stm.setInt(1, 1);
377             } else {
378
379                 stm.setInt(1, 0);
380             }
381
382             stm.setString(2, description);
383             stm.setString(3, id);
384             stm.executeUpdate();
385
386             if (members != null) {
387
388                 String JavaDoc login = null;
389
390                 ArrayList JavaDoc newMembers = new ArrayList JavaDoc(Arrays.asList(members));
391
392                 ArrayList JavaDoc currentMembers = new ArrayList JavaDoc();
393                 sql = "SELECT id_user FROM kasai_users_groups WHERE id_group='" + org.apache.commons.lang.StringEscapeUtils.escapeSql(id) + "'";
394                 rs = con.createStatement().executeQuery(sql);
395
396                 while (rs.next()) {
397
398                     currentMembers.add(StringUtils.defaultString(rs.getString("id_user")));
399                 }
400
401                 for (int i = 0; i < members.length; i++) {
402
403                     login = members[i];
404
405                     if (!currentMembers.contains(login)) {
406
407                         sql = "INSERT INTO kasai_users_groups (id_user,id_group) VALUES ('" + org.apache.commons.lang.StringEscapeUtils.escapeSql(login) + "','" + org.apache.commons.lang.StringEscapeUtils.escapeSql(id) +
408                             "')";
409                         con = DBUtil.getConnection(Constants.DATABASE_SOURCE, Constants.PROPERTY_FILE);
410                         con.createStatement().executeUpdate(sql);
411                     }
412                 }
413
414                 for (int i = 0; i < currentMembers.size(); i++) {
415
416                     login = (String JavaDoc) currentMembers.get(i);
417
418                     if (!newMembers.contains(login)) {
419
420                         sql = "DELETE FROM kasai_users_groups WHERE id_user='" + org.apache.commons.lang.StringEscapeUtils.escapeSql(login) + "' AND id_group='" + org.apache.commons.lang.StringEscapeUtils.escapeSql(id) +
421                             "'";
422                         con = DBUtil.getConnection(Constants.DATABASE_SOURCE, Constants.PROPERTY_FILE);
423                         con.createStatement().executeUpdate(sql);
424                     }
425                 }
426             }
427         }catch (SQLException sqle){
428             Log.getInstance(Constants.PROPERTY_FILE).write (this.getClass().getName(), "update", ExceptionUtils.getStackTrace(sqle),Level.SEVERE);
429             throw new DataAccessException(sqle);
430         } finally {
431
432             try {
433
434                 con.close();
435             } catch (Exception JavaDoc e) {}
436         }
437     }
438 }
439
Popular Tags