Java > Open Source Codes > com > quikj > application > communicator > applications > webtalk > model > UserTable


1 /*
2  * UserTable.java
3  *
4  * Created on May 14, 2003, 11:47 AM
5  */
6
7 package com.quikj.application.communicator.applications.webtalk.model;
8
9 import java.sql.*;
10 import java.util.*;
11
12 /**
13  *
14  * @author bhm
15  */
16 public class UserTable
17 {
18     // database user table name constant
19 public static final String USER_TABLE_NAME = "user_tbl";
20     
21     // database user table column name constants
22 public static final String USERNAME = "userid";
23     public static final String PASSWORD = "password";
24     public static final String DOMAIN = "domain";
25     public static final String FLAGS = "flags";
26     public static final String FULLNAME = "fullname";
27     public static final String ADDRESS = "address";
28     public static final String ADDITIONAL_INFO = "addnl_info";
29     public static final String UNAVAIL_XFER = "unavail_xferto";
30     public static final String GATEKEEPER = "gatekeeper";
31     
32     /** Holds value of property connection. */
33     private Connection connection;
34     
35     /** Holds value of property errorMessage. */
36     private String errorMessage;
37     
38     /** Creates a new instance of UserTable */
39     public UserTable()
40     {
41     }
42     
43     /** Setter for property connection.
44      * @param connection New value of property connection.
45      *
46      */
47     public void setConnection(Connection connection)
48     {
49         this.connection = connection;
50     }
51     
52     /** Getter for property errorMessage.
53      * @return Value of property errorMessage.
54      *
55      */
56     public String getErrorMessage()
57     {
58         return this.errorMessage;
59     }
60     
61     public UserElement query(String username)
62     {
63         return query(username, null);
64     }
65     
66     public UserElement query(String username, String domain_constraint)
67     {
68         
69         StringBuffer cmd = new StringBuffer ("select "
70         + FULLNAME
71         + ", "
72         + ADDRESS
73         + ", "
74         + ADDITIONAL_INFO
75         + ", "
76         + UNAVAIL_XFER
77         + ", "
78         + GATEKEEPER
79         + ", "
80         + DOMAIN
81         + " from "
82         + USER_TABLE_NAME
83         + " where ");
84         
85         if (domain_constraint != null)
86         {
87             cmd.append(DOMAIN + "= ? and ");
88         }
89         
90         cmd.append(USERNAME
91         + " = ?");
92         
93         try
94         {
95             Statement stmt = connection.createStatement();
96             stmt.executeUpdate("use webtalk");
97             
98             PreparedStatement pstmt = connection.prepareStatement(cmd.toString());
99             int index = 1;
100             if (domain_constraint != null)
101             {
102                 pstmt.setString(index++, domain_constraint);
103             }
104             pstmt.setString(index++, username);
105             
106             ResultSet rs = pstmt.executeQuery();
107             if (!rs.first())
108             {
109                 // Doesn't exist
110 errorMessage = null;
111                 return null;
112             }
113             
114             UserElement userdata = new UserElement();
115             
116             userdata.setFullName(rs.getString(1));
117             userdata.setAddress(rs.getString(2));
118             userdata.setAdditionalInfo(rs.getString(3));
119             userdata.setUnavailXferTo(rs.getString(4));
120             userdata.setGatekeeper(rs.getString(5));
121             userdata.setDomain(rs.getString(6));
122             userdata.setName(username);
123             
124             // query names of groups owned by this user
125 cmd = new StringBuffer ("select "
126             + GroupTable.GROUPNAME
127             + " from "
128             + GroupTable.GROUP_OWNER_TABLE_NAME
129             + " where "
130             + GroupTable.USERNAME
131             + " = ?");
132             pstmt = connection.prepareStatement(cmd.toString());
133             pstmt.setString(1, username);
134             rs = pstmt.executeQuery();
135             
136             while (rs.next() == true)
137             {
138                 String groupname = rs.getString(1);
139                 userdata.addOwnsGroup(groupname);
140             }
141             
142             // query names of groups that this user belongs to
143 cmd = new StringBuffer ("select "
144             + GroupTable.GROUPNAME
145             + " from "
146             + GroupTable.GROUP_MEMBER_TABLE_NAME
147             + " where "
148             + GroupTable.USERNAME
149             + " = ?");
150             
151             pstmt = connection.prepareStatement(cmd.toString());
152             pstmt.setString(1, username);
153             rs = pstmt.executeQuery();
154             
155             while (rs.next() == true)
156             {
157                 String groupname = rs.getString(1);
158                 userdata.addBelongsToGroup(groupname);
159             }
160             
161             return userdata;
162         }
163         catch (SQLException ex)
164         {
165             errorMessage = "SQLException: " + ex.getMessage() + ", SQL command: " + cmd;
166             return null;
167         }
168     }
169     
170     public boolean create(UserElement user)
171     {
172         StringBuffer buffer = new StringBuffer ("insert into "
173         + USER_TABLE_NAME
174         + " values (?, password(?), ?, ?, ?, ?, ?, ?, ?)");
175         
176         String cmd = buffer.toString();
177         
178         try
179         {
180             Statement stmt = connection.createStatement();
181             stmt.executeUpdate("use webtalk");
182             
183             PreparedStatement pstmt = connection.prepareStatement(cmd);
184             pstmt.setString(1, user.getName());
185             pstmt.setString(2, user.getPassword());
186             pstmt.setString(3, user.getDomain() == null ? "" : user.getDomain());
187             pstmt.setInt(4, 0);
188             pstmt.setString(5, user.getFullName() == null ? "" : user.getFullName());
189             pstmt.setString(6, user.getAddress() == null ? "" : user.getAddress());
190             pstmt.setString(7, user.getAdditionalInfo() == null ? "" : user.getAdditionalInfo());
191             pstmt.setString(8, user.getUnavailXferTo() == null ? "" : user.getUnavailXferTo());
192             pstmt.setString(9, user.getGatekeeper() == null ? "": user.getGatekeeper());
193             
194             int count = pstmt.executeUpdate();
195             if (count == 0)
196             {
197                 errorMessage = "User create failed: no rows affected, SQL command: " + cmd;
198                 return false;
199             }
200             
201             String username = user.getName();
202             
203             String [] groups = user.getOwnsGroups();
204             if ((groups != null) && (groups.length > 0))
205             {
206                 buffer = new StringBuffer ("replace into "
207                 + GroupTable.GROUP_OWNER_TABLE_NAME
208                 + " values ");
209                 
210                 for (int i = 0; i < groups.length; i++)
211                 {
212                     if (i > 0)
213                     {
214                         buffer.append(',');
215                     }
216                     
217                     buffer.append("(?, ?)");
218                 }
219                 
220                 cmd = buffer.toString();
221                 pstmt = connection.prepareStatement(cmd);
222                 int index = 1;
223                 for (int i = 0; i < groups.length; i++)
224                 {
225                     pstmt.setString(index++, username);
226                     pstmt.setString(index++, groups[i]);
227                 }
228                 pstmt.executeUpdate(); // need innoDB here
229 }
230             
231             groups = user.getBelongsToGroups();
232             if ((groups != null) && (groups.length > 0))
233             {
234                 buffer = new StringBuffer ("insert into "
235                 + GroupTable.GROUP_MEMBER_TABLE_NAME
236                 + " values ");
237                 
238                 for (int i = 0; i < groups.length; i++)
239                 {
240                     if (i > 0)
241                     {
242                         buffer.append(',');
243                     }
244                     
245                     buffer.append("(?, ?)");
246                 }
247                 
248                 cmd = buffer.toString();
249                 pstmt = connection.prepareStatement(cmd);
250                 int index = 1;
251                 for (int i = 0; i < groups.length; i++)
252                 {
253                     pstmt.setString(index++, username);
254                     pstmt.setString(index++, groups[i]);
255                 }
256                 pstmt.executeUpdate(); // need innoDB here
257 }
258         }
259         catch (SQLException ex)
260         {
261             errorMessage = "SQLException: " + ex.getMessage() + ", SQL command: " + cmd;
262             return false;
263         }
264         
265         return true;
266     }
267     
268     public boolean modify(UserElement user)
269     {
270         return modify(user, null);
271     }
272     
273     public boolean modify(UserElement user, String domain_constraint)
274     {
275         // This is a multi-part operation, need to check owned-groups manipulation
276 // first find out currently owned groups
277
278         String cmd = "select "
279         + GroupTable.GROUPNAME
280         + " from "
281         + GroupTable.GROUP_OWNER_TABLE_NAME
282         + " where "
283         + GroupTable.USERNAME
284         + " = ?";
285         
286         try
287         {
288             Statement stmt = connection.createStatement();
289             stmt.executeUpdate("use webtalk");
290             
291             PreparedStatement pstmt = connection.prepareStatement(cmd);
292             pstmt.setString(1, user.getName());
293             ResultSet rs = pstmt.executeQuery();
294             
295             ArrayList group_list = new ArrayList();
296             while (rs.next() == true)
297             {
298                 group_list.add(rs.getString(1));
299             }
300             
301             String [] newly_owned_groups = null;
302             
303             if (group_list.size() == 0)
304             {
305                 newly_owned_groups = user.getOwnsGroups();
306             }
307             else if (user.getOwnsGroups() == null)
308             {
309                 errorMessage = null;
310                 return false;
311             }
312             else
313             {
314                 // first check against new owns groups list, don't allow orphaned group
315 String [] new_owns_list = user.getOwnsGroups();
316                 int owns_num = group_list.size();
317                 for (int i = 0; i < owns_num; i++)
318                 {
319                     boolean found_in_new_list = false;
320                     String owned_group = (String )group_list.get(i);
321                     for (int j = 0; j < new_owns_list.length; j++)
322                     {
323                         if (new_owns_list[j].equals(owned_group) == true)
324                         {
325                             found_in_new_list = true;
326                             break;
327                         }
328                     }
329                     
330                     if (found_in_new_list == false)
331                     {
332                         errorMessage = null;
333                         return false;
334                     }
335                 }
336                 
337                 // now determine what's been added
338 int num_added = new_owns_list.length - owns_num;
339                 if (num_added > 0)
340                 {
341                     newly_owned_groups = new String [num_added];
342                     int index = 0;
343                     checking_new: for (int i = 0; i < new_owns_list.length; i++)
344                     {
345                         String owns_group = new_owns_list[i];
346                         for (int j = 0; j < owns_num; j++)
347                         {
348                             if (((String )group_list.get(j)).equals(owns_group) == true)
349                             {
350                                 continue checking_new;
351                             }
352                         }
353                         
354                         newly_owned_groups[index++] = owns_group;
355                     }
356                 }
357             }
358             
359             // all OK, first update the user data in the user table
360 StringBuffer buffer = new StringBuffer ("update "
361             + USER_TABLE_NAME
362             + " set ");
363             
364             if (user.getPassword() != null)
365             {
366                 buffer.append(PASSWORD + "=password(?), ");
367             }
368             
369             buffer.append(DOMAIN
370             + "= ?, "
371             + FULLNAME
372             + "= ?, "
373             + ADDRESS
374             + "= ?, "
375             + ADDITIONAL_INFO
376             + "= ?, "
377             + UNAVAIL_XFER
378             + "= ?, "
379             + GATEKEEPER
380             + "= ? "
381             + " where ");
382             
383             if (domain_constraint != null)
384             {
385                 buffer.append(DOMAIN + "= ? and ");
386             }
387             
388             buffer.append(USERNAME
389             + " = ?");
390             
391             pstmt = connection.prepareStatement(buffer.toString());
392             int index = 1;
393             if (user.getPassword() != null)
394             {
395                 pstmt.setString(index++, user.getPassword());
396             }
397             pstmt.setString(index++, user.getDomain() == null ? "" : user.getDomain());
398             pstmt.setString(index++, user.getFullName() == null ? "" : user.getFullName());
399             pstmt.setString(index++, user.getAddress() == null ? "" : user.getAddress());
400             pstmt.setString(index++, user.getAdditionalInfo() == null ? "" : user.getAdditionalInfo());
401             pstmt.setString(index++, user.getUnavailXferTo() == null ? "" : user.getUnavailXferTo());
402             pstmt.setString(index++, user.getGatekeeper() == null ? "" : user.getGatekeeper());
403             if (domain_constraint != null)
404             {
405                 pstmt.setString(index++, domain_constraint);
406             }
407             
408             pstmt.setString(index++, user.getName());
409             
410             int count = pstmt.executeUpdate();
411             if (count == 0)
412             {
413                 errorMessage = null;
414                 return false;
415             }
416             
417             // update group owner table
418 String username = user.getName();
419             
420             if ((newly_owned_groups != null) && (newly_owned_groups.length > 0))
421             {
422                 // delta list of newly assigned owned groups
423 buffer = new StringBuffer ("replace into "
424                 + GroupTable.GROUP_OWNER_TABLE_NAME
425                 + " values ");
426                 
427                 for (int i = 0; i < newly_owned_groups.length; i++)
428                 {
429                     if (i > 0)
430                     {
431                         buffer.append(',');
432                     }
433                     
434                     buffer.append("(?, ?)");
435                 }
436                 
437                 cmd = buffer.toString();
438                 pstmt = connection.prepareStatement(cmd);
439                 index = 1;
440                 for (int i = 0; i < newly_owned_groups.length; i++)
441                 {
442                     pstmt.setString(index++, username);
443                     pstmt.setString(index++, newly_owned_groups[i]);
444                 }
445                 
446                 pstmt.executeUpdate();
447             }
448             
449             // update group member table
450 cmd = "delete from "
451             + GroupTable.GROUP_MEMBER_TABLE_NAME
452             + " where "
453             + GroupTable.USERNAME
454             + " = ?";
455             
456             pstmt = connection.prepareStatement(cmd);
457             pstmt.setString(1, username);
458             pstmt.executeUpdate();
459             
460             String [] groups = user.getBelongsToGroups();
461             if ((groups != null) && (groups.length > 0))
462             {
463                 buffer = new StringBuffer ("insert into "
464                 + GroupTable.GROUP_MEMBER_TABLE_NAME
465                 + " values ");
466                 
467                 for (int i = 0; i < groups.length; i++)
468                 {
469                     if (i > 0)
470                     {
471                         buffer.append(',');
472                     }
473                     
474                     buffer.append("(?, ?)");
475                 }
476                 
477                 cmd = buffer.toString();
478                 pstmt = connection.prepareStatement(cmd);
479                 index = 1;
480                 for (int i = 0; i < groups.length; i++)
481                 {
482                     pstmt.setString(index++, username);
483                     pstmt.setString(index++, groups[i]);
484                 }
485                 
486                 pstmt.executeUpdate();
487             }
488         }
489         catch (SQLException ex)
490         {
491             errorMessage = "SQLException: " + ex.getMessage() + ", SQL command: " + cmd;
492             return false;
493         }
494         
495         return true;
496     }
497     
498     public boolean delete(String username)
499     {
500         return delete(username, null);
501     }
502     
503     public boolean delete(String username, String domain_constraint)
504     {
505         StringBuffer buffer = new StringBuffer ("delete from "
506         + USER_TABLE_NAME
507         + " where ");
508         
509         if (domain_constraint != null)
510         {
511             buffer.append(DOMAIN + "= ? and ");;
512         }
513         
514         buffer.append(USERNAME
515         + " = ?");
516         
517         String cmd = buffer.toString();
518         try
519         {
520             Statement stmt = connection.createStatement();
521             stmt.executeUpdate("use webtalk");
522             
523             PreparedStatement pstmt = connection.prepareStatement(cmd);
524             int index = 1;
525             
526             if (domain_constraint != null)
527             {
528                 pstmt.setString(index++, domain_constraint);
529             }
530             pstmt.setString(index++, username);
531             int count = pstmt.executeUpdate();
532             if (count == 0)
533             {
534                 errorMessage = null;
535                 return false;
536             }
537             
538             //handle owned groups
539 cmd = "select "
540             + GroupTable.GROUPNAME
541             + " from "
542             + GroupTable.GROUP_OWNER_TABLE_NAME
543             + " where "
544             + GroupTable.USERNAME
545             + " = ?";
546             
547             // when upgrade to MySQL 4.0, use multi-table delete for this
548 // (ex: delete membertbl, grouptbl, ownertbl from membertbl,grouptbl,ownertbl
549 // where membertbl.groupid=grouptbl.groupid and ((grouptbl.groupid =
550 // ownertbl.groupid) and (ownertbl.userid='username'))
551 pstmt = connection.prepareStatement(cmd);
552             pstmt.setString(1, username);
553             ResultSet rs = pstmt.executeQuery();
554             
555             ArrayList group_list = new ArrayList();
556             while (rs.next() == true)
557             {
558                 group_list.add(rs.getString(1));
559             }
560             
561             int num_groups = group_list.size();
562             
563             for (int i = 0; i < num_groups; i++)
564             {
565                 cmd = "delete from "
566                 + GroupTable.GROUP_MEMBER_TABLE_NAME
567                 + " where "
568                 + GroupTable.GROUPNAME
569                 + " = ?";
570                 pstmt = connection.prepareStatement(cmd);
571                 pstmt.setString(1, (String )group_list.get(i));
572                 
573                 pstmt.executeUpdate();
574                 
575                 cmd = "delete from "
576                 + GroupTable.GROUP_TABLE_NAME
577                 + " where "
578                 + GroupTable.GROUPNAME
579                 + " = ?";
580                 pstmt = connection.prepareStatement(cmd);
581                 pstmt.setString(1, (String )group_list.get(i));
582                 
583                 pstmt.executeUpdate();
584                 
585                 cmd = "delete from "
586                 + CannedMessageTable.TABLE_NAME
587                 + " where "
588                 + CannedMessageTable.GROUP
589                 + " = ?";
590                 
591                 pstmt = connection.prepareStatement(cmd);
592                 pstmt.setString(1, (String )group_list.get(i));
593                 
594                 pstmt.executeUpdate();
595                 
596             }
597             
598             cmd = "delete from "
599             + GroupTable.GROUP_OWNER_TABLE_NAME
600             + " where "
601             + GroupTable.USERNAME
602             + " = ?";
603             
604             pstmt = connection.prepareStatement(cmd);
605             pstmt.setString(1, username);
606             pstmt.executeUpdate();
607             
608             cmd = "delete from "
609             + GroupTable.GROUP_MEMBER_TABLE_NAME
610             + " where "
611             + GroupTable.USERNAME
612             + " = ?";
613             
614             pstmt = connection.prepareStatement(cmd);
615             pstmt.setString(1, username);
616             pstmt.executeUpdate();
617         }
618         catch (SQLException ex)
619         {
620             errorMessage = "SQLException: " + ex.getMessage() + ", SQL command: " + cmd;
621             return false;
622         }
623         
624         return true;
625     }
626     
627     public ArrayList list()
628     {
629         return list(null);
630     }
631     
632     public ArrayList list(String domain_constraint)
633     // returns list of 0 or more elements, or null if error encountered
634 {
635         StringBuffer cmd = new StringBuffer ("select "
636         + USERNAME
637         + " from "
638         + USER_TABLE_NAME);
639         
640         if (domain_constraint != null)
641         {
642             cmd.append(" where " + DOMAIN + "= ?");
643         }
644         
645         cmd.append(" order by "
646         + USERNAME);
647         
648         try
649         {
650             Statement stmt = connection.createStatement();
651             stmt.executeUpdate("use webtalk");
652             
653             PreparedStatement pstmt = connection.prepareStatement(cmd.toString());
654             if (domain_constraint != null)
655             {
656                 pstmt.setString(1, domain_constraint);
657             }
658             
659             ResultSet rs = pstmt.executeQuery();
660             
661             ArrayList list = new ArrayList();
662             while (rs.next() == true)
663             {
664                 list.add(rs.getString(1));
665             }
666             
667             return list;
668         }
669         catch (SQLException ex)
670         {
671             errorMessage = "SQLException: " + ex.getMessage() + ", SQL command: " + cmd;
672             return null;
673         }
674     }
675     
676     public ArrayList search(UserElement user)
677     {
678         return search(user, null);
679     }
680     
681     public ArrayList search(UserElement user, String domain_constraint)
682     // returns list of 0 or more elements, or null if error encountered
683 {
684         PreparedStatement pstmt = null;
685         
686         try
687         {
688             Statement stmt = connection.createStatement();
689             stmt.executeUpdate("use webtalk");
690             
691             String [] owns_groups = user.getOwnsGroups();
692             if ((owns_groups == null) || (owns_groups.length == 0))
693             {
694                 pstmt = searchNotOwnsGroup(user, domain_constraint);
695             }
696             else
697             {
698                 pstmt = searchOwnsGroup(user, domain_constraint, owns_groups);
699             }
700             
701             ResultSet rs = pstmt.executeQuery();
702             
703             ArrayList list = new ArrayList();
704             while (rs.next() == true)
705             {
706                 list.add(rs.getString(1));
707             }
708             
709             return list;
710         }
711         catch (SQLException ex)
712         {
713             errorMessage = "SQLException: " + ex.getMessage();
714             return null;
715         }
716     }
717     
718     private PreparedStatement searchNotOwnsGroup(UserElement user,
719     String domain_constraint)
720     throws SQLException
721     {
722         StringBuffer buffer = new StringBuffer ("select distinct t1."
723         + USERNAME
724         + " from ("
725         + USER_TABLE_NAME
726         + " as t1 left join "
727         + GroupTable.GROUP_MEMBER_TABLE_NAME
728         + " as t3 using ("
729         + USERNAME
730         + ")) where ");
731         
732         if (domain_constraint != null)
733         {
734             buffer.append("t1." + DOMAIN + "= ? and ");
735         }
736         
737         buffer.append(" t1."
738         + USERNAME
739         + " like ? ");
740         
741         String fullname = user.getFullName();
742         if ((fullname != null) && (fullname.length() > 0))
743         {
744             buffer.append("and t1."
745             + FULLNAME
746             + " like ? ");
747         }
748         
749         String address = user.getAddress();
750         if ((address != null) && (address.length() > 0))
751         {
752             buffer.append("and t1."
753             + ADDRESS
754             + " like ? ");
755         }
756         
757         String addnl_info = user.getAdditionalInfo();
758         if ((addnl_info != null) && (addnl_info.length() > 0))
759         {
760             buffer.append("and t1."
761             + ADDITIONAL_INFO
762             + " like ? ");
763         }
764         
765         String unavail = user.getUnavailXferTo();
766         if ((unavail != null) && (unavail.length() > 0))
767         {
768             buffer.append("and t1."
769             + UNAVAIL_XFER
770             + " like ? ");
771         }
772         
773         String gk = user.getGatekeeper();
774         if ((gk != null) && (gk.length() > 0))
775         {
776             buffer.append("and t1."
777             + GATEKEEPER
778             + " like ? ");
779         }
780         
781         String [] belongs_to_groups = user.getBelongsToGroups();
782         if ((belongs_to_groups != null) && (belongs_to_groups.length > 0))
783         {
784             for (int i = 0; i < belongs_to_groups.length; i++)
785             {
786                 if (i == 0)
787                 {
788                     buffer.append("and (");
789                 }
790                 else
791                 {
792                     buffer.append("or ");
793                 }
794                 
795                 buffer.append("t3."
796                 + GroupTable.GROUPNAME
797                 + " = ? ");
798                 
799                 if (i == (belongs_to_groups.length - 1))
800                 {
801                     buffer.append(") ");
802                 }
803             }
804         }
805         
806         buffer.append(" order by 1");
807         
808         PreparedStatement pstmt = connection.prepareStatement(buffer.toString());
809         int index = 1;
810         if (domain_constraint != null)
811         {
812             pstmt.setString(index++, domain_constraint);
813         }
814         
815         pstmt.setString(index++, user.getName());
816         
817         if ((fullname != null) && (fullname.length() > 0))
818         {
819             pstmt.setString(index++, fullname);
820         }
821         
822         if ((address != null) && (address.length() > 0))
823         {
824             pstmt.setString(index++, address);
825         }
826         
827         if ((addnl_info != null) && (addnl_info.length() > 0))
828         {
829             pstmt.setString(index++, addnl_info);
830         }
831         
832         if ((unavail != null) && (unavail.length() > 0))
833         {
834             pstmt.setString(index++, unavail);
835         }
836         
837         if ((gk != null) && (gk.length() > 0))
838         {
839             pstmt.setString(index++, gk);
840         }
841         
842         if ((belongs_to_groups != null) && (belongs_to_groups.length > 0))
843         {
844             for (int i = 0; i < belongs_to_groups.length; i++)
845             {
846                 pstmt.setString(index++, belongs_to_groups[i]);
847             }
848         }
849         
850         return pstmt;
851     }
852     
853     private PreparedStatement searchOwnsGroup(UserElement user,
854     String domain_constraint,
855     String [] owns_groups)
856     throws SQLException
857     {
858         StringBuffer buffer = new StringBuffer ("select distinct t1."
859         + USERNAME
860         + " from (("
861         + USER_TABLE_NAME
862         + " as t1 left join "
863         + GroupTable.GROUP_OWNER_TABLE_NAME
864         + " as t2 using ("
865         + USERNAME
866         + ")) left join "
867         + GroupTable.GROUP_MEMBER_TABLE_NAME
868         + " as t3 using ("
869         + USERNAME
870         + ")) where ");
871         
872         if (domain_constraint != null)
873         {
874             buffer.append("t1." + DOMAIN + "= ? and ");
875         }
876         
877         buffer.append(" t1."
878         + USERNAME
879         + " like ? ");
880         
881         String fullname = user.getFullName();
882         if ((fullname != null) && (fullname.length() > 0))
883         {
884             buffer.append("and t1."
885             + FULLNAME
886             + " like ? ");
887         }
888         
889         String address = user.getAddress();
890         if ((address != null) && (address.length() > 0))
891         {
892             buffer.append("and t1."
893             + ADDRESS
894             + " like ? ");
895         }
896         
897         String addnl_info = user.getAdditionalInfo();
898         if ((addnl_info != null) && (addnl_info.length() > 0))
899         {
900             buffer.append("and t1."
901             + ADDITIONAL_INFO
902             + " like ? ");
903         }
904         
905         String unavail = user.getUnavailXferTo();
906         if ((unavail != null) && (unavail.length() > 0))
907         {
908             buffer.append("and t1."
909             + UNAVAIL_XFER
910             + " like ? ");
911         }
912         
913         String gk = user.getGatekeeper();
914         if ((gk != null) && (gk.length() > 0))
915         {
916             buffer.append("and t1."
917             + GATEKEEPER
918             + " like ? ");
919         }
920         
921         for (int i = 0; i < owns_groups.length; i++)
922         {
923             if (i == 0)
924             {
925                 buffer.append("and (");
926             }
927             else
928             {
929                 buffer.append("or ");
930             }
931             
932             buffer.append("t2."
933             + GroupTable.GROUPNAME
934             + " = ? ");
935             
936             if (i == (owns_groups.length - 1))
937             {
938                 buffer.append(") ");
939             }
940         }
941         
942         String [] belongs_to_groups = user.getBelongsToGroups();
943         if ((belongs_to_groups != null) && (belongs_to_groups.length > 0))
944         {
945             for (int i = 0; i < belongs_to_groups.length; i++)
946             {
947                 if (i == 0)
948                 {
949                     buffer.append("and (");
950                 }
951                 else
952                 {
953                     buffer.append("or ");
954                 }
955                 
956                 buffer.append("t3."
957                 + GroupTable.GROUPNAME
958                 + " = ? ");
959                 
960                 if (i == (belongs_to_groups.length - 1))
961                 {
962                     buffer.append(") ");
963                 }
964             }
965         }
966         
967         buffer.append(" order by 1");
968         
969         PreparedStatement pstmt = connection.prepareStatement(buffer.toString());
970         int index = 1;
971         if (domain_constraint != null)
972         {
973             pstmt.setString(index++, domain_constraint);
974         }
975         
976         pstmt.setString(index++, user.getName());
977         
978         if ((fullname != null) && (fullname.length() > 0))
979         {
980             pstmt.setString(index++, fullname);
981         }
982         
983         if ((address != null) && (address.length() > 0))
984         {
985             pstmt.setString(index++, address);
986         }
987         
988         if ((addnl_info != null) && (addnl_info.length() > 0))
989         {
990             pstmt.setString(index++, addnl_info);
991         }
992         
993         if ((unavail != null) && (unavail.length() > 0))
994         {
995             pstmt.setString(index++, unavail);
996         }
997         
998         if ((gk != null) && (gk.length() > 0))
999         {
1000            pstmt.setString(index++, gk);
1001        }
1002        
1003        for (int i = 0; i < owns_groups.length; i++)
1004        {
1005            pstmt.setString(index++, owns_groups[i]);
1006        }
1007        
1008        if ((belongs_to_groups != null) && (belongs_to_groups.length > 0))
1009        {
1010            for (int i = 0; i < belongs_to_groups.length; i++)
1011            {
1012                pstmt.setString(index++, belongs_to_groups[i]);
1013            }
1014        }
1015        
1016        return pstmt;
1017    }
1018    
1019    public ArrayList findMembersOnlyByGroupDomain(String domain_constraint)
1020    // returns list of 0 or more elements, or null if error encountered
1021 {
1022        try
1023        {
1024            Statement stmt = connection.createStatement();
1025            stmt.executeUpdate("use webtalk");
1026            
1027            StringBuffer buffer = new StringBuffer ("select distinct m."
1028            + GroupTable.USERNAME
1029            + " from "
1030            + GroupTable.GROUP_TABLE_NAME
1031            + " as g,"
1032            + GroupTable.GROUP_MEMBER_TABLE_NAME
1033            + " as m left join "
1034            + GroupTable.GROUP_OWNER_TABLE_NAME
1035            + " as o on m."
1036            + GroupTable.USERNAME
1037            + "=o."
1038            + GroupTable.USERNAME
1039            + " where o."
1040            + GroupTable.USERNAME
1041            + " is null and g."
1042            + GroupTable.DOMAIN
1043            + " = ? and m."
1044            + GroupTable.GROUPNAME
1045            + "=g."
1046            + GroupTable.GROUPNAME
1047            + " order by 1");
1048            
1049            PreparedStatement pstmt = connection.prepareStatement(buffer.toString());
1050            
1051            pstmt.setString(1, domain_constraint);
1052            
1053            ResultSet rs = pstmt.executeQuery();
1054            
1055            ArrayList list = new ArrayList();
1056            while (rs.next() == true)
1057            {
1058                list.add(rs.getString(1));
1059            }
1060            
1061            return list;
1062        }
1063        catch (SQLException ex)
1064        {
1065            errorMessage = "SQLException: " + ex.getMessage();
1066            return null;
1067        }
1068    }
1069    
1070    public ArrayList findOwnersByGroupDomain(String domain_constraint)
1071    // returns list of 0 or more elements, or null if error encountered
1072 {
1073        try
1074        {
1075            Statement stmt = connection.createStatement();
1076            stmt.executeUpdate("use webtalk");
1077            
1078            StringBuffer buffer = new StringBuffer ("select distinct o."
1079            + GroupTable.USERNAME
1080            + " from "
1081            + GroupTable.GROUP_TABLE_NAME
1082            + " as g,"
1083            + GroupTable.GROUP_OWNER_TABLE_NAME
1084            + " as o where g."
1085            + GroupTable.DOMAIN
1086            + " = ? and o."
1087            + GroupTable.GROUPNAME
1088            + "=g."
1089            + GroupTable.GROUPNAME
1090            + " order by 1");
1091            
1092            PreparedStatement pstmt = connection.prepareStatement(buffer.toString());
1093            
1094            pstmt.setString(1, domain_constraint);
1095            
1096            ResultSet rs = pstmt.executeQuery();
1097            
1098            ArrayList list = new ArrayList();
1099            while (rs.next() == true)
1100            {
1101                list.add(rs.getString(1));
1102            }
1103            
1104            return list;
1105        }
1106        catch (SQLException ex)
1107        {
1108            errorMessage = "SQLException: " + ex.getMessage();
1109            return null;
1110        }
1111    }
1112    
1113    // public ArrayList search(UserElement user, String domain_constraint)
1114 // // returns list of 0 or more elements, or null if error encountered
1115 // {
1116 // String constraint = "";
1117 // if (domain_constraint != null)
1118 // {
1119 // constraint = "t1." + DOMAIN + "='" + domain_constraint + "' and ";
1120 // }
1121 //
1122 // String cmd = null;
1123 //
1124 // StringBuffer constraints = new StringBuffer(" t1."
1125 // + USERNAME
1126 // + " like '"
1127 // + user.getName()
1128 // + "' ");
1129 //
1130 // String fullname = user.getFullName();
1131 // if ((fullname != null) && (fullname.length() > 0))
1132 // {
1133 // constraints.append("and t1."
1134 // + FULLNAME
1135 // + " like '"
1136 // + fullname
1137 // + "' ");
1138 // }
1139 //
1140 // String address = user.getAddress();
1141 // if ((address != null) && (address.length() > 0))
1142 // {
1143 // constraints.append("and t1."
1144 // + ADDRESS
1145 // + " like '"
1146 // + address
1147 // + "' ");
1148 // }
1149 //
1150 // String addnl_info = user.getAdditionalInfo();
1151 // if ((addnl_info != null) && (addnl_info.length() > 0))
1152 // {
1153 // constraints.append("and t1."
1154 // + ADDITIONAL_INFO
1155 // + " like '"
1156 // + addnl_info
1157 // + "' ");
1158 // }
1159 //
1160 // String unavail = user.getUnavailXferTo();
1161 // if ((unavail != null) && (unavail.length() > 0))
1162 // {
1163 // constraints.append("and t1."
1164 // + UNAVAIL_XFER
1165 // + " like '"
1166 // + unavail
1167 // + "' ");
1168 // }
1169 //
1170 // String belong_str = "";
1171 // String[] belongs_to_groups = user.getBelongsToGroups();
1172 // if ((belongs_to_groups != null) && (belongs_to_groups.length > 0))
1173 // {
1174 // StringBuffer buf = new StringBuffer();
1175 // for (int i = 0; i < belongs_to_groups.length; i++)
1176 // {
1177 // if (i == 0)
1178 // {
1179 // buf.append("and (");
1180 // }
1181 // else
1182 // {
1183 // buf.append("or ");
1184 // }
1185 //
1186 // buf.append("t3."
1187 // + GroupTable.GROUPNAME
1188 // + " = '"
1189 // + belongs_to_groups[i]
1190 // + "' ");
1191 //
1192 // if (i == (belongs_to_groups.length - 1))
1193 // {
1194 // buf.append(") ");
1195 // belong_str = buf.toString();
1196 // }
1197 // }
1198 // }
1199 //
1200 // String[] owns_groups = user.getOwnsGroups();
1201 // if ((owns_groups == null) || (owns_groups.length == 0))
1202 // {
1203 // // just join user table with group member table, doesn't matter
1204 // // if group membership specified or not
1205 // // (The full query below this works for all cases except when an eligible user
1206 // // doesn't own any groups)
1207 //
1208 // constraints.append(belong_str);
1209 //
1210 // cmd = "select distinct t1."
1211 // + USERNAME
1212 // + " from ("
1213 // + USER_TABLE_NAME
1214 // + " as t1 left join "
1215 // + GroupTable.GROUP_MEMBER_TABLE_NAME
1216 // + " as t3 using ("
1217 // + USERNAME
1218 // + ")) where "
1219 // + constraint
1220 // + constraints.toString()
1221 // + " order by 1";
1222 // }
1223 // else
1224 // {
1225 // // group ownership was specified - use the full query - join all 3 tables
1226 //
1227 // // owns_group != null
1228 // for (int i = 0; i < owns_groups.length; i++)
1229 // {
1230 // if (i == 0)
1231 // {
1232 // constraints.append("and (");
1233 // }
1234 // else
1235 // {
1236 // constraints.append("or ");
1237 // }
1238 //
1239 // constraints.append("t2."
1240 // + GroupTable.GROUPNAME
1241 // + " = '"
1242 // + owns_groups[i]
1243 // + "' ");
1244 //
1245 // if (i == (owns_groups.length - 1))
1246 // {
1247 // constraints.append(") ");
1248 // }
1249 // }
1250 //
1251 // constraints.append(belong_str);
1252 //
1253 // cmd = "select distinct t1."
1254 // + USERNAME
1255 // + " from (("
1256 // + USER_TABLE_NAME
1257 // + " as t1 left join "
1258 // + GroupTable.GROUP_OWNER_TABLE_NAME
1259 // + " as t2 using ("
1260 // + USERNAME
1261 // + ")) left join "
1262 // + GroupTable.GROUP_MEMBER_TABLE_NAME
1263 // + " as t3 using ("
1264 // + USERNAME
1265 // + ")) where "
1266 // + constraint
1267 // + constraints.toString()
1268 // + " order by 1";
1269 // }
1270 //
1271 // try
1272 // {
1273 // Statement stmt = connection.createStatement();
1274 // stmt.executeUpdate("use webtalk");
1275 //
1276 // ResultSet rs = stmt.executeQuery(cmd);
1277 //
1278 // ArrayList list = new ArrayList();
1279 // while (rs.next() == true)
1280 // {
1281 // list.add(rs.getString(1));
1282 // }
1283 //
1284 // return list;
1285 // }
1286 // catch (SQLException ex)
1287 // {
1288 // errorMessage = "SQLException: " + ex.getMessage() + ", SQL command: " + cmd;
1289 // return null;
1290 // }
1291 // }
1292
1293}
1294

A to Z: JavaDoc & Examples

---

Daily Java News & Articles

---

Open Source Projects

---

Open Source Codes

---

Free Computer Books

---

Remove Frame

---

Popular Tags