|
1
25 package org.jresearch.gossip.dao;
26
27 import java.lang.reflect.InvocationTargetException  ;
28 import java.security.SecureRandom ;
29 import java.sql.Connection ;
30 import java.sql.PreparedStatement ;
31 import java.sql.ResultSet ;
32 import java.sql.SQLException ;
33 import java.util.ArrayList ;
34 import java.util.Calendar ;
35
36 import org.jresearch.gossip.IConst;
37 import org.jresearch.gossip.am.ban.BanMap;
38 import org.jresearch.gossip.beans.Ban;
39 import org.jresearch.gossip.beans.NamedValue;
40 import org.jresearch.gossip.beans.user.Sender;
41 import org.jresearch.gossip.beans.user.User;
42 import org.jresearch.gossip.beans.user.UserInfo;
43 import org.jresearch.gossip.beans.user.UserSettings;
44 import org.jresearch.gossip.dao.drivers.DbDriver;
45 import org.jresearch.gossip.dao.drivers.keygen.IKeyGenConst;
46 import org.jresearch.gossip.dao.drivers.keygen.KeyGenerator;
47 import org.jresearch.gossip.dao.drivers.keygen.KeyGeneratorFactory;
48 import org.jresearch.gossip.forms.ListForm;
49 import org.jresearch.gossip.forms.ProfileForm;
50 import org.jresearch.gossip.list.Mapping;
51 import org.jresearch.gossip.list.RecordsData;
52 import org.jresearch.gossip.util.MD5Digest;
53
54
59 public class UserDAO extends DAO {
60
61 private static UserDAO instance;
62
63 private static Object lock = new Object ();
64
65 private final DbDriver dbDriver;
66
67 private final KeyGenerator keyGen;
68
69 private UserDAO() {
70 try {
71 this.dbDriver = DbDriver.getInstance();
72 this.keyGen = KeyGeneratorFactory
73 .getKeyGenerator(IKeyGenConst.DEFAULT_KEYGEN);
74 } catch (SQLException ex) {
75 throw new RuntimeException ("UserDAO not initialized", ex);
76 }
77 }
78
79
84 public static UserDAO getInstance() {
85 if (instance == null) {
86 synchronized (lock) {
87 if (instance == null) {
88 instance = new UserDAO();
89 }
90 }
91 }
92 return instance;
93 }
94
95
106 public boolean addUser(ProfileForm profile) throws SQLException {
107 Connection connection = this.dataSource.getConnection();
108 PreparedStatement st = null;
109 ResultSet rs = null;
110 boolean notExist = !isUserExist(profile.getLogin());
111
112 try {
113 if (notExist) {
114 int uid = ((Integer ) keyGen.generateKey(
115 IKeyGenConst.KEY_NAMES[IKeyGenConst.KEY_USER],
116 connection)).intValue();
117 Calendar cl = Calendar.getInstance();
118 cl.set(Integer.parseInt(profile.getDOB_year()), Integer
119 .parseInt(profile.getDOB_month()), Integer
120 .parseInt(profile.getDOB_day()));
121 st = connection.prepareStatement(dbDriver.getQueries()
122 .getUserQueries().getSql_ADD_USER());
123 st.setString(1, profile.getLogin());
124 st.setString(2, MD5Digest.digest(profile.getLogin(), profile
125 .getPassword2()));
126 st.setString(3, profile.getEmail());
127 st.setString(4, profile.getHomePage());
128 st.setString(5, profile.getIcq());
129 st.setDate(6, new java.sql.Date (cl.getTime().getTime()));
130 st.setString(7, profile.getPlace());
131 st.setString(8, profile.getOccupation());
132 st.setString(9, profile.getSignature());
133 st.setInt(10, Integer.parseInt(profile.getMessPerPage()));
134 st.setInt(11, Integer.parseInt(profile.getAutoLogin()));
135 st.setInt(12, Integer.parseInt(profile.getShowEmail()));
136 st.setDate(13, new java.sql.Date (0L));
137 st.setInt(14, uid);
138
139 st.executeUpdate();
140 }
141 } finally {
142 if (rs != null) {
143 rs.close();
144 }
145
146 if (st != null) {
147 st.close();
148 }
149
150 connection.close();
151 }
152
153 return notExist;
154 }
155
156
164 public boolean addPendingUser(String login, String email, String confirmcode)
165 throws SQLException {
166 Connection connection = this.dataSource.getConnection();
167 PreparedStatement st = null;
168 ResultSet rs = null;
169 boolean notExist = !isUserExist(login)
170 && !checkPendingUser(login, confirmcode);
171
172 try {
173 if (notExist) {
174 st = connection.prepareStatement(dbDriver.getQueries()
175 .getUserQueries().getSql_ADD_PENDING_USER());
176 st.setString(1, login);
177 st.setString(2, email);
178 st.setString(3, confirmcode);
179 st.setTimestamp(4, new java.sql.Timestamp (
180 (new java.util.Date ()).getTime()));
181
182 st.executeUpdate();
183 }
184 } finally {
185 if (rs != null) {
186 rs.close();
187 }
188
189 if (st != null) {
190 st.close();
191 }
192
193 connection.close();
194 }
195
196 return notExist;
197 }
198
199
210 public void changePassword(String pass, String login) throws SQLException {
211 Connection connection = this.dataSource.getConnection();
212 PreparedStatement st = connection.prepareStatement(dbDriver
213 .getQueries().getUserQueries().getSql_CHANGE_PASSWORD());
214
215 try {
216 st.setString(1, MD5Digest.digest(login, pass));
217 st.setString(2, login);
218 st.execute();
219 } finally {
220 st.close();
221 connection.close();
222 }
223 }
224
225
232 public int countUsers() throws SQLException {
233 Connection connection = this.dataSource.getConnection();
234 PreparedStatement st = connection.prepareStatement(dbDriver
235 .getQueries().getUserQueries().getSql_COUNT_USERS());
236 ResultSet rs = null;
237 int count = 0;
238
239 try {
240 rs = (ResultSet ) st.executeQuery();
241
242 if (rs.next()) {
243 count = rs.getInt(1);
244 }
245 } finally {
246 if (rs != null) {
247 rs.close();
248 }
249
250 st.close();
251 connection.close();
252 }
253
254 return count;
255 }
256
257
263 public boolean checkPendingUser(String login, String confirmcode)
264 throws SQLException {
265 Connection connection = this.dataSource.getConnection();
266 PreparedStatement st = connection.prepareStatement(dbDriver
267 .getQueries().getUserQueries().getSql_CHECK_PENDING_USER());
268 ResultSet rs = null;
269 boolean success = false;
270
271 try {
272 st.setString(1, login);
273 st.setString(2, confirmcode);
274 rs = (ResultSet ) st.executeQuery();
275
276 if (rs.next()) {
277 success = true;
278 }
279 } finally {
280 if (rs != null) {
281 rs.close();
282 }
283
284 st.close();
285 connection.close();
286 }
287
288 return success;
289 }
290
291
299 public void deleteUser(String uid) throws SQLException {
300 Connection connection = this.dataSource.getConnection();
301 PreparedStatement st = null;
302
303 try {
304 st = connection.prepareStatement(dbDriver.getQueries()
305 .getUserQueries().getSql_GET_USER_BY_ID());
306 st.setInt(1, Integer.parseInt(uid));
307
308 User user = new User();
309 fillUser(st, user, false);
310 st = connection.prepareStatement(dbDriver.getQueries()
311 .getUserQueries().getSql_DELETE_USER());
312 st.setInt(1, Integer.parseInt(uid));
313 st.execute();
314 st = connection.prepareStatement(dbDriver.getQueries()
315 .getForumQueries().getSql_CLEAN_USER_MOD());
316 st.setString(1, user.getName());
317 st.execute();
318 st = connection.prepareStatement(dbDriver.getQueries()
319 .getForumQueries().getSql_CLEAN_USER_SUBSCR());
320 st.setString(1, user.getName());
321 st.execute();
322 st = connection.prepareStatement(dbDriver.getQueries()
323 .getForumQueries().getSql_MARK_USER_MESS());
324 st.setString(1, "<" + user.getName() + ">");
325 st.setString(2, user.getName());
326 st.execute();
327 } finally {
328 st.close();
329 connection.close();
330 }
331 }
332
333
338 public boolean deletePendingUser(String login) throws SQLException {
339 Connection connection = this.dataSource.getConnection();
340 PreparedStatement st = null;
341
342 try {
343 st = connection.prepareStatement(dbDriver.getQueries()
344 .getUserQueries().getSql_DELETE_PENDING_USER());
345 st.setString(1, login);
346 st.execute();
347 return true;
348 } finally {
349 st.close();
350 connection.close();
351 }
352 }
353
354
359 public boolean deletePendingUser(long expirationTimeout)
360 throws SQLException {
361 Connection connection = this.dataSource.getConnection();
362 PreparedStatement st = null;
363
364 try {
365 st = connection.prepareStatement(dbDriver.getQueries()
366 .getUserQueries().getSql_DELETE_EXPIRED_PENDING_USER());
367 st.setTimestamp(1, new java.sql.Timestamp (expirationTimeout));
368 st.execute();
369 return true;
370 } finally {
371 st.close();
372 connection.close();
373 }
374 }
375
376
387 public void fillUser(PreparedStatement st, User bean) throws SQLException {
388 fillUser(st, bean, true);
389 }
390
391
404 public void fillUser(PreparedStatement st, User bean, boolean fullinfo)
405 throws SQLException {
406 ResultSet rs = null;
407
408 try {
409 rs = (ResultSet ) st.executeQuery();
410
411 if (rs.next()) {
412 UserInfo info = new UserInfo();
413 UserSettings settings = new UserSettings();
414 bean.setId(rs.getInt("id"));
415 bean.setName(rs.getString("user_name"));
416 info.setEmail(rs.getString("user_mail"));
417 bean.setStatus(rs.getInt("user_status"));
418 bean.setIntime(rs.getTimestamp("last_intime"));
419
420 if (fullinfo) {
421 settings.setAutologin(rs.getBoolean("auto_login"));
422 bean.setPassword(rs.getString("user_pass"));
423 settings.setMes_per_page(rs.getInt("mes_per_page"));
424 }
425
426 info.setCity(rs.getString("user_city"));
427 info.setIcq(rs.getString("user_icq"));
428 info.setBirthday(rs.getDate("user_dob"));
429 info.setHomepage(rs.getString("user_hp"));
430 info.setOccupation(rs.getString("user_occupation"));
431 settings.setShow_user_mail(rs.getBoolean("show_user_mail"));
432 settings.setSignature(rs.getString("user_signature"));
433 bean.setInfo(info);
434 bean.setSettings(settings);
435 }
436 } finally {
437 if (rs != null) {
438 rs.close();
439 }
440 }
441 }
442
443
462 public void fillUserList(User user, RecordsData recordsData, ListForm lform)
463 throws InstantiationException , IllegalAccessException ,
464 InvocationTargetException , NoSuchMethodException , SQLException {
465 Connection connection = this.dataSource.getConnection();
466 PreparedStatement st = connection.prepareStatement(dbDriver
467 .getQueries().getUserQueries().getSql_GET_USERS());
468 ResultSet rs = null;
469
470 try {
471 int blokSize = user.getSettings().getMes_per_page() * 2;
472 int currBlok = Integer.parseInt(lform.getBlock());
473 st.setInt(1, currBlok);
474 st.setInt(2, dbDriver.getLastRowIdx(currBlok, blokSize));
475 rs = (ResultSet ) st.executeQuery();
476 recordsData.fillRecords(rs, Mapping.getInstance().UserMapping,
477 User.class);
478 recordsData.setBlockSize(blokSize);
479 recordsData.setCurrBlock(currBlok);
480 recordsData.setRecordsCount(countUsers());
481 } finally {
482 if (rs != null) {
483 rs.close();
484 }
485
486 st.close();
487 connection.close();
488 }
489 }
490
491 public String generatePassword() {
492 SecureRandom random = new SecureRandom ();
493 String charset = IConst.VALUES.PASSWORD_DICTIONARY;
494 int length = charset.length();
495 StringBuffer newpass = new StringBuffer ();
496
497 for (int i = 0; i < IConst.VALUES.DEFAULT_PASSWORD_LENGTH; i++) {
498 newpass.append(charset.charAt(random.nextInt(length)));
499 }
500
501 return newpass.toString();
502 }
503
504
517 public User getUser(String username, String password) throws SQLException {
518 Connection connection = this.dataSource.getConnection();
519 PreparedStatement st = connection.prepareStatement(dbDriver
520 .getQueries().getUserQueries().getSql_GET_USER());
521 User bean = new User();
522 st.setString(1, username);
523 st.setString(2, MD5Digest.digest(username, password));
524
525 try {
526 fillUser(st, bean);
527 } finally {
528 st.close();
529 connection.close();
530 }
531
532 return bean;
533 }
534
535
543 public ArrayList getUserEmails() throws SQLException {
544 Connection connection = this.dataSource.getConnection();
545 PreparedStatement st = connection.prepareStatement(dbDriver
546 .getQueries().getUserQueries().getSql_GET_USER_EMAILS());
547 ResultSet rs = null;
548 ArrayList list = new ArrayList ();
549
550 try {
551 rs = (ResultSet ) st.executeQuery();
552
553 while (rs.next()) {
554 NamedValue nv = new NamedValue();
555 nv.setName(rs.getString("user_name"));
556 nv.setValue(rs.getString("user_mail"));
557 list.add(nv);
558 }
559 } finally {
560 if (rs != null) {
561 rs.close();
562 }
563
564 st.close();
565 connection.close();
566 }
567
568 return list;
569 }
570
571
584 public User getUserEncoded(String username, String password)
585 throws SQLException {
586 Connection connection = this.dataSource.getConnection();
587 PreparedStatement st = connection.prepareStatement(dbDriver
588 .getQueries().getUserQueries().getSql_GET_USER_ENCODED());
589 User bean = new User();
590 st.setString(1, username);
591 st.setString(2, password);
592
593 try {
594 fillUser(st, bean);
595 } finally {
596 st.close();
597 connection.close();
598 }
599
600 return bean;
601 }
602
603
608 public User getUser(String username) throws SQLException {
609 Connection connection = this.dataSource.getConnection();
610 PreparedStatement st = connection.prepareStatement(dbDriver
611 .getQueries().getUserQueries().getSql_GET_USER_INFO_FULL());
612 User bean = new User();
613
614 try {
615 st.setString(1, username);
616 fillUser(st, bean);
617 } finally {
618 st.close();
619 connection.close();
620 }
621
622 return bean;
623 }
624
625
636 public User getUserInfo(String username) throws SQLException {
637 Connection connection = this.dataSource.getConnection();
638 PreparedStatement st = connection.prepareStatement(dbDriver
639 .getQueries().getUserQueries().getSql_GET_USER_INFO_FULL());
640 User bean = new User();
641
642 try {
643 st.setString(1, username);
644 fillUser(st, bean, false);
645 } finally {
646 st.close();
647 connection.close();
648 }
649
650 return bean;
651 }
652
653
663 public Sender getSenderInfo(String username) throws SQLException {
664 Connection connection = this.dataSource.getConnection();
665 PreparedStatement st = connection.prepareStatement(dbDriver
666 .getQueries().getUserQueries().getSql_GET_USER_INFO());
667 ResultSet rs = null;
668 Sender bean = new Sender();
669
670 try {
671 st.setString(1, username);
672 rs = (ResultSet ) st.executeQuery();
673
674 if (rs.next()) {
675 UserInfo info = new UserInfo();
676 UserSettings settings = new UserSettings();
677 bean.setName(rs.getString("user_name"));
678 info.setCity(rs.getString("user_city"));
679 settings.setSignature(rs.getString("user_signature"));
680 bean.setTotalMess(rs.getInt("tot_mes"));
681 bean.setStatus(rs.getInt("user_status"));
682 bean.setSettings(settings);
683 bean.setInfo(info);
684 }
685 } finally {
686 if (rs != null) {
687 rs.close();
688 }
689
690 st.close();
691 connection.close();
692 }
693
694 return bean;
695 }
696
697
708 public String getUserName(int id) throws SQLException {
709 Connection connection = this.dataSource.getConnection();
710 PreparedStatement st = connection.prepareStatement(dbDriver
711 .getQueries().getUserQueries().getSql_GET_USER_INFO_BY_ID());
712 ResultSet rs = null;
713 String name = null;
714
715 try {
716 st.setInt(1, id);
717 rs = (ResultSet ) st.executeQuery();
718
719 if (rs.next()) {
720 name = rs.getString("user_name");
721 }
722 } finally {
723 if (rs != null) {
724 rs.close();
725 }
726
727 st.close();
728 connection.close();
729 }
730
731 return name;
732 }
733
734
743 public boolean isUserExist(String login) throws SQLException {
744 Connection connection = this.dataSource.getConnection();
745 PreparedStatement st = connection.prepareStatement(dbDriver
746 .getQueries().getUserQueries().getSql_CHECK_USER());
747 ResultSet rs = null;
748 boolean success = false;
749
750 try {
751 st.setString(1, login);
752 rs = (ResultSet ) st.executeQuery();
753
754 if (rs.next()) {
755 success = true;
756 }
757 } finally {
758 if (rs != null) {
759 rs.close();
760 }
761
762 st.close();
763 connection.close();
764 }
765
766 return success;
767 }
768
769
780 public User setNewPassword(String email, String login) throws SQLException {
781 Connection connection = this.dataSource.getConnection();
782 PreparedStatement st = connection.prepareStatement(dbDriver
783 .getQueries().getUserQueries().getSql_CHECK_USER_WITH_EMAIL());
784 ResultSet rs = null;
785 String newpass = null;
786 User user = new User();
787
788 try {
789 st.setString(1, email);
790 st.setString(2, login);
791 rs = (ResultSet ) st.executeQuery();
792 fillUser(st, user, false);
793
794 if (user.getStatus() > 0) {
795 newpass = generatePassword();
796 st = connection.prepareStatement(dbDriver.getQueries()
797 .getUserQueries().getSql_CHANGE_PASSWORD());
798 st.setString(1, MD5Digest.digest(login, newpass));
799 st.setString(2, login);
800 st.execute();
801 user.setPassword(newpass);
802 }
803 } finally {
804 if (rs != null) {
805 rs.close();
806 }
807
808 st.close();
809 connection.close();
810 }
811
812 return user;
813 }
814
815
824 public void setUserStatus(String login, int status) throws SQLException {
825 Connection connection = this.dataSource.getConnection();
826
827 PreparedStatement st = connection.prepareStatement(dbDriver
828 .getQueries().getUserQueries().getSql_UPDATE_USER_STATUS());
829
830 try {
831 st.setInt(1, status);
832 st.setString(2, login);
833 st.execute();
834
835 if (status < 7) {
836 st = connection.prepareStatement(dbDriver.getQueries()
837 .getForumQueries().getSql_CLEAN_USER_MOD());
838 st.setString(1, login);
839 st.execute();
840 }
841 } finally {
842 st.close();
843 connection.close();
844 }
845 }
846
847
858 public User getUserInfoShort(int uid) throws SQLException {
859 Connection connection = this.dataSource.getConnection();
860 PreparedStatement st = connection.prepareStatement(dbDriver
861 .getQueries().getUserQueries().getSql_GET_USER_INFO_BY_ID());
862 ResultSet rs = null;
863 User bean = new User();
864
865 try {
866 st.setInt(1, uid);
867 rs = st.executeQuery();
868
869 if (rs.next()) {
870 bean.setName(rs.getString("user_name"));
871 bean.setStatus(rs.getInt("user_status"));
872 }
873 } finally {
874 if (rs != null) {
875 rs.close();
876 }
877
878 st.close();
879 connection.close();
880 }
881
882 return bean;
883 }
884
885
898 public boolean updateUser(ProfileForm profile, String login)
899 throws SQLException {
900 Connection connection = this.dataSource.getConnection();
901 PreparedStatement st = connection.prepareStatement(dbDriver
902 .getQueries().getUserQueries().getSql_CHECK_USER());
903 ResultSet rs = null;
904
905 try {
906 st.setString(1, profile.getLogin());
907 rs = (ResultSet ) st.executeQuery();
908
909 if (rs.next()) {
910 return false;
911 } else {
912 Calendar cl = Calendar.getInstance();
913 cl.set(Integer.parseInt(profile.getDOB_year()), Integer
914 .parseInt(profile.getDOB_month()), Integer
915 .parseInt(profile.getDOB_day()));
916 st = connection.prepareStatement(dbDriver.getQueries()
917 .getUserQueries().getSql_UPDATE_USER());
918
919 st.setString(1, profile.getEmail());
920 st.setString(2, profile.getHomePage());
921 st.setString(3, profile.getIcq());
922 st.setDate(4, new java.sql.Date (cl.getTime().getTime()));
923 st.setString(5, profile.getPlace());
924 st.setString(6, profile.getOccupation());
925 st.setString(7, profile.getSignature());
926 st.setInt(8, Integer.parseInt(profile.getMessPerPage()));
927 st.setInt(9, Integer.parseInt(profile.getAutoLogin()));
928 st.setInt(10, Integer.parseInt(profile.getShowEmail()));
929 st.setString(11, login);
930
931 st.execute();
932 st = connection.prepareStatement(dbDriver.getQueries()
933 .getForumQueries().getSql_UPDATE_SUBSCRIBE());
934 st.setString(1, profile.getEmail());
935 st.setString(2, login);
936 st.execute();
937
938 return true;
939 }
940 } finally {
941 if (rs != null) {
942 rs.close();
943 }
944
945 st.close();
946 connection.close();
947 }
948 }
949
950
958 public void updateIntime(String login) throws SQLException {
959 Connection connection = this.dataSource.getConnection();
960 PreparedStatement st = connection.prepareStatement(dbDriver
961 .getQueries().getUserQueries().getSql_SET_LAST_INTIME());
962
963 try {
964 st.setString(1, login);
965 st.execute();
966 } finally {
967 st.close();
968 connection.close();
969 }
970 }
971
972
976 public void fillBanMap(BanMap banMap) throws SQLException {
977 Connection connection = this.dataSource.getConnection();
978 PreparedStatement st = connection.prepareStatement(dbDriver
979 .getQueries().getUserQueries().getSql_GET_BAN_MAP());
980 ResultSet rs = null;
981 try {
982 rs = (ResultSet ) st.executeQuery();
983
984 while (rs.next()) {
985 banMap.add(rs.getString("ban_mask"), rs.getInt("type_id"));
986 }
987 } finally {
988 if (rs != null) {
989 rs.close();
990 }
991
992 st.close();
993 connection.close();
994 }
995 }
996
997
1000 public void deleteBan(Ban ban) throws SQLException {
1001 Connection connection = this.dataSource.getConnection();
1002 PreparedStatement st = connection.prepareStatement(dbDriver
1003 .getQueries().getUserQueries().getSql_DELETE_BAN());
1004
1005 try {
1006 st.setInt(1, ban.getType());
1007 st.setString(2, ban.getMask());
1008 st.execute();
1009 } finally {
1010 st.close();
1011 connection.close();
1012 }
1013
1014 }
1015
1016
1019 public void addBan(Ban ban) throws SQLException {
1020 Connection connection = this.dataSource.getConnection();
1021 PreparedStatement st = connection.prepareStatement(dbDriver
1022 .getQueries().getUserQueries().getADD_BAN());
1023
1024 try {
1025 st.setInt(1, ban.getType());
1026 st.setString(2, ban.getMask());
1027 st.execute();
1028 } finally {
1029 st.close();
1030 connection.close();
1031 }
1032 }
1033} |
Popular Tags |
Java API By Example, From Geeks To Geeks.