|
1 package org.jahia.webapps.addressbook;
2
3 import java.io.*;
4 import java.util.*;
5 import java.sql.*;
6 import javax.servlet.*;
7 import javax.servlet.http.*;
8
9 import org.apache.ojb.broker.PersistenceBroker;
11 import org.apache.ojb.broker.*;
12 import org.apache.ojb.broker.query.*;
13 import org.apache.ojb.broker.accesslayer.*;
14
15 import org.jahia.tools.*;
16 import org.jahia.tools.db.*;
17 import org.jahia.webapps.*;
18
19
20
21
22
23
29 public class AdbApplicationDB {
30
31
32 private static PersistenceBroker broker = null;
33
34
39 public static void init(PersistenceBroker broker_) {
40 Tools.toConsole("AdbApplicationDB", "getting broker instance");
41 broker = broker_;
42 }
43
44
45
53 public static ResultSet executeQuery(String  sql) throws SQLException {
54
55 ResultSet rs = null;
56
57 try {
58 Connection conn = broker.serviceConnectionManager().getConnection();
59 Statement stmt = conn.createStatement();
60 rs = stmt.executeQuery(sql);
61 }
62 catch (LookupException e) {
63 Tools.toConsole("DBServices#executeQuery", e.toString());
64 }
65 return rs;
66 }
67
68
75 public static int getNextId(String column, String table) {
76 int new_id = 1;
77 ResultSet rs;
78
79 try {
80 rs = executeQuery("select max(" + column + ") from " + table);
81 if (rs.next()) {
82 new_id = rs.getInt(1) + 1;
83 }
84 else {
85 Tools.toConsole("DBServices - getNextId",
86 "problem reaching DB " + table);
87 }
88 }
89 catch (SQLException e) {
90 Tools.toConsole("DBServices - getNextId", e.toString());
91 }
92 return new_id;
93
94 }
95
96
102
103 public static AddressBook selectAddBook(int user) {
104 AddressBook book = null;
105
106 try {
107
108 Criteria crit1 = new Criteria();
109 crit1.addEqualTo("owner_id", new Integer (user));
110 Query q = QueryFactory.newQuery(AddressBook.class, crit1);
111 broker.clearCache();
112 Collection userColl = broker.getCollectionByQuery(q);
113 if (!userColl.isEmpty()) {
114 java.util.Iterator iter = userColl.iterator();
115 book = (AddressBook) iter.next();
116
117 }
118 else {
119 book = null;
120 }
121
122 }
123 catch (PersistenceBrokerException e) {
124 Tools.toConsole("DBServices - findBook", e.toString());
125 broker.abortTransaction();
126
127 }
128
129 return book;
130 }
131
132
139
140 public static AddressBook selectAddBookContext(int context, int userId) {
141 AddressBook book = null;
142
143 try {
144
145 Criteria crit1 = new Criteria();
146 crit1.addEqualTo("context_id", new Integer (context));
147 crit1.addEqualTo("owner_id", new Integer (userId));
148 Query q = QueryFactory.newQuery(AddressBook.class, crit1);
149 broker.clearCache();
150 Collection userColl = broker.getCollectionByQuery(q);
151 if (!userColl.isEmpty()) {
152 java.util.Iterator iter = userColl.iterator();
153 book = (AddressBook) iter.next();
154
155 }
156 else {
157 book = null;
158 }
159
160 }
161 catch (PersistenceBrokerException e) {
162 Tools.toConsole("DBServices - findBook", e.toString());
163 broker.abortTransaction();
164
165 }
166
167 return book;
168 }
169
170
177
178 public static User selectUser(String username, String password) {
179 User user = null;
180
181 try {
182
183 Criteria crit1 = new Criteria();
184 crit1.addEqualTo("login", username);
185 crit1.addEqualTo("password", password);
186 Query q = QueryFactory.newQuery(AddressBook.class, crit1);
187 broker.clearCache();
188 Collection userColl = broker.getCollectionByQuery(q);
189 if (!userColl.isEmpty()) {
190 java.util.Iterator iter = userColl.iterator();
191 user = (User) iter.next();
192
193 }
194 else {
195 user = null;
196 }
197
198 }
199 catch (PersistenceBrokerException e) {
200 Tools.toConsole("DBServices - findUser", e.toString());
201 broker.abortTransaction();
202
203 }
204
205 return user;
206 }
207
208 public static AddressBook selectAddBookContext(int context) {
209 AddressBook book = null;
210
211 try {
212
213 Criteria crit1 = new Criteria();
214 crit1.addEqualTo("context_id", new Integer (context));
215 Query q = QueryFactory.newQuery(AddressBook.class, crit1);
216 broker.clearCache();
217 Collection userColl = broker.getCollectionByQuery(q);
218 if (!userColl.isEmpty()) {
219 java.util.Iterator iter = userColl.iterator();
220 book = (AddressBook) iter.next();
221
222 }
223 else {
224 book = null;
225 }
226
227 }
228 catch (PersistenceBrokerException e) {
229 Tools.toConsole("DBServices - findBook", e.toString());
230 broker.abortTransaction();
231
232 }
233
234 return book;
235 }
236
237 public static int findUser(String login) {
238
239 int id = 0;
240 User user;
241
242 try {
243
244 Criteria crit1 = new Criteria();
245 crit1.addEqualTo("login", login);
246 Query q = QueryFactory.newQuery(User.class, crit1);
247 broker.clearCache();
248 Collection userColl = broker.getCollectionByQuery(q);
249 if (!userColl.isEmpty()) {
250
251 java.util.Iterator iter = userColl.iterator();
252 user = (User) iter.next();
253 id = user.getId();
254
255 }
256
257 else {
258 id = -1;
259
260 }
261
262 }
263 catch (PersistenceBrokerException e) {
264 Tools.toConsole("DBServices - inserUser", e.toString());
265 broker.abortTransaction();
266
267 }
268
269 return id;
270 }
271
272 public static boolean insertContact(Contact newContact) {
273 boolean ret = true;
274 int max = 0;
275
276 try {
277 max = AdbApplicationDB.getNextId("contact_id", "contact");
279 newContact.setId(max);
280
281 broker.beginTransaction();
282 broker.store(newContact);
283 broker.commitTransaction();
284 }
285 catch (PersistenceBrokerException e) {
286 Tools.toConsole("DBServices - inserUser", e.toString());
287 broker.abortTransaction();
288 ret = false;
289 }
290
291 return ret;
292 }
293
294 public static void insertUser(int id, String username) {
295 boolean ret = true;
296 int max = 0;
297 User user;
298
299 try {
300 user = new User(id, username, "", "");
301
302 broker.beginTransaction();
303 broker.store(user);
304 broker.commitTransaction();
305 }
306 catch (PersistenceBrokerException e) {
307 Tools.toConsole("DBServices - inserUser", e.toString());
308 broker.abortTransaction();
309 }
311
312 }
313
314
315 public static AddressBook insertAddBook(int nextId, int contextId, int userId) {
316 boolean ret = true;
317 int max = 0;
318 AddressBook book = null;
319
320 try {
321
323 book = new AddressBook(nextId, userId, contextId);
324 broker.beginTransaction();
325 broker.store(book);
326 broker.commitTransaction();
327 }
328 catch (PersistenceBrokerException e) {
329 Tools.toConsole("DBServices - inserAddressBook", e.toString());
330 broker.abortTransaction();
331 book = null;
332 }
333
334 return book;
335 }
336
337
338
339 public static Vector getSearchAll(String value, int addbookId) {
340 Vector contacts = new Vector();
341
342 try {
343 Criteria crit1 = new Criteria();
344 Criteria crit2 = new Criteria();
345 Criteria crit3 = new Criteria();
346 Criteria crit4 = new Criteria();
347 Criteria crit5 = new Criteria();
348 Criteria crit6 = new Criteria();
349 Criteria crit7 = new Criteria();
350 Criteria crit8 = new Criteria();
351 Criteria crit9 = new Criteria();
352 crit1.addEqualTo("first_name", value);
353 crit1.addEqualTo("addbook_id", new Integer (addbookId));
354 crit2.addEqualTo("prof_title", value);
355 crit2.addEqualTo("addbook_id", new Integer (addbookId));
356 crit3.addEqualTo("company", value);
357 crit3.addEqualTo("addbook_id", new Integer (addbookId));
358 crit4.addEqualTo("address1", value);
359 crit4.addEqualTo("addbook_id", new Integer (addbookId));
360 crit5.addEqualTo("address2", value);
361 crit5.addEqualTo("addbook_id", new Integer (addbookId));
362 crit6.addEqualTo("city", value);
363 crit6.addEqualTo("addbook_id", new Integer (addbookId));
364 crit7.addEqualTo("country", value);
365 crit7.addEqualTo("addbook_id", new Integer (addbookId));
366 crit8.addEqualTo("notes", value);
367 crit8.addEqualTo("addbook_id", new Integer (addbookId));
368 crit9.addEqualTo("last_name", value);
369 crit9.addEqualTo("addbook_id", new Integer (addbookId));
370 crit1.addOrCriteria(crit2);
371 crit1.addOrCriteria(crit3);
372 crit1.addOrCriteria(crit4);
373 crit1.addOrCriteria(crit5);
374 crit1.addOrCriteria(crit6);
375 crit1.addOrCriteria(crit7);
376 crit1.addOrCriteria(crit8);
377 crit1.addOrCriteria(crit9);
378 Query q = QueryFactory.newQuery(Contact.class, crit1);
379 broker.clearCache();
380 Collection userColl = broker.getCollectionByQuery(q);
381 if (!userColl.isEmpty()) {
382
383 java.util.Iterator iter = userColl.iterator();
384 while (iter.hasNext()) {
385 contacts.add(iter.next());
386 }
387 }
388 }
389 catch (Exception e) {
390 Tools.toConsole("DBServices - getDbUserDesc", e.toString());
391 }
392
393 return contacts;
394 }
395
396 public static Vector getSearchLast(String value, int bookId) {
397
398 Vector contacts = new Vector();
399
400 Contact description = null;
401
402 String firstname;
403 try {
404 Criteria crit1 = new Criteria();
405 crit1.addEqualTo("last_name", value);
406 crit1.addEqualTo("addbook_id", new Integer (bookId));
407 Query q = QueryFactory.newQuery(Contact.class, crit1);
408 broker.clearCache();
409 Collection userColl = broker.getCollectionByQuery(q);
410
411 if (!userColl.isEmpty()) {
412 java.util.Iterator iter = userColl.iterator();
413 while (iter.hasNext()) {
414 contacts.add(iter.next());
415
416 }
417 }
418 }
419 catch (Exception e) {
420 Tools.toConsole("ADBSApplication - getSearch", e.toString());
421 }
422
423 return contacts;
424 }
425
426 public static Vector getSearchFirst(String value, int bookId) {
427
428 Vector contacts = new Vector();
429
430 Contact description = null;
431
432 String firstname;
433 try {
434 Criteria crit1 = new Criteria();
435 crit1.addEqualTo("first_name", value);
436 crit1.addEqualTo("addbook_id", new Integer (bookId));
437 Query q = QueryFactory.newQuery(Contact.class, crit1);
438 broker.clearCache();
439 Collection userColl = broker.getCollectionByQuery(q);
440
441 if (!userColl.isEmpty()) {
442 java.util.Iterator iter = userColl.iterator();
443 while (iter.hasNext()) {
444 contacts.add(iter.next());
445
446 }
447 }
448 }
449 catch (Exception e) {
450 Tools.toConsole("ADBSApplication - getSearch", e.toString());
451 }
452
453 return contacts;
454 }
455
456 public static Vector loadCategories(int id) {
457
458 Vector categories = new Vector();
459 String firstname;
460 try {
461 Criteria crit1 = new Criteria();
462 crit1.addEqualTo("addbook_id", new Integer (id));
463 Query q = QueryFactory.newQuery(Category.class, crit1);
464 broker.clearCache();
465 Collection userColl = broker.getCollectionByQuery(q);
466
467 if (!userColl.isEmpty()) {
468 java.util.Iterator iter = userColl.iterator();
469 while (iter.hasNext()) {
470
471 categories.add(iter.next());
472
473 }
474 }
475 }
476 catch (Exception e) {
477 Tools.toConsole("ADBSApplication - getSearch", e.toString());
478 }
479
480 return categories;
481 }
482
483
484
485
486
491
492 public static Vector loadCommunications() {
493
494 Vector comms = new Vector();
495 try {
496 Criteria crit1 = new Criteria();
497 Query q = QueryFactory.newQuery(Communication.class, crit1);
498 broker.clearCache();
499 Collection userColl = broker.getCollectionByQuery(q);
500
501 if (!userColl.isEmpty()) {
502 java.util.Iterator iter = userColl.iterator();
503 while (iter.hasNext()) {
504 comms.add(iter.next());
505 }
506
507 }
508 }
509 catch (Exception e) {
510 Tools.toConsole("ADBSApplication - getSearch", e.toString());
511 }
512
513 return comms;
514 }
515
516
525 public static void deleteCategory(int cat_id) {
526 try {
527 Criteria crit = new Criteria();
528 crit.addEqualTo("category_id", new Integer (cat_id));
529 Query q = QueryFactory.newQuery(Category.class, crit);
530 broker.clearCache();
531 broker.beginTransaction();
532 broker.deleteByQuery(q);
533 Criteria crit2 = new Criteria();
534 crit2.addEqualTo("category", new Integer (cat_id));
535 Collection userColl = broker.getCollectionByQuery(q);
536 if (!userColl.isEmpty()) {
537 java.util.Iterator iter = userColl.iterator();
538 while (iter.hasNext()) {
539 Contact c = (Contact) iter.next();
540 c.setCategory( -1);
541 broker.store(c);
542 }
543 broker.commitTransaction();
544
545 }
546 }
547 catch (PersistenceBrokerException e) {
548 Tools.toConsole("DBServices - removeActivity", e.toString());
549 broker.abortTransaction();
550 }
551
552 }
553
554
560
561 public static void renameCategory(int id, String cat_name) {
562
563 try {
564 Criteria crit1 = new Criteria();
565 crit1.addEqualTo("category_id", new Integer (id));
566 Query q = QueryFactory.newQuery(Category.class, crit1);
567 broker.clearCache();
568 Collection userColl = broker.getCollectionByQuery(q);
569
570 if (!userColl.isEmpty()) {
571 java.util.Iterator iter = userColl.iterator();
572 Category c = (Category) iter.next();
573 c.setName(cat_name);
574
575 broker.beginTransaction();
576 broker.store(c);
577 broker.commitTransaction();
578 }
579 }
580 catch (PersistenceBrokerException e) {
581 Tools.toConsole("DBServices - renameCategory", e.toString());
582 broker.abortTransaction();
583
584 }
585
586 }
587
588
593
594 public static Vector loadContacts(int id) {
595
596 Vector contacts = new Vector();
597 String firstname;
598 try {
599 Criteria crit1 = new Criteria();
600 crit1.addEqualTo("addbook_id", new Integer (id));
601 QueryByCriteria q = QueryFactory.newQuery(Contact.class, crit1);
602 q.addOrderByAscending("last_name");
603 broker.clearCache();
604 Collection userColl = broker.getCollectionByQuery(q);
605
606 if (!userColl.isEmpty()) {
607 java.util.Iterator iter = userColl.iterator();
608 while (iter.hasNext()) {
609 contacts.add(iter.next());
610
611 }
612
613 for (int i = 0; i < contacts.size(); i++) {
614 Contact c = (Contact) contacts.get(i);
615 String res = c.getLastName();
616 }
617 }
618 }
619 catch (Exception e) {
620 Tools.toConsole("ADBSApplication - getSearch", e.toString());
621 }
622
623 return contacts;
624 }
625
626 public static Vector loadContactsCat(int id) {
627
628 Vector contacts = new Vector();
629 String firstname;
630 try {
631 Criteria crit1 = new Criteria();
632 crit1.addEqualTo("addbook_id", new Integer (id));
633 QueryByCriteria q = QueryFactory.newQuery(Contact.class, crit1);
634 q.addOrderByAscending("category");
635 broker.clearCache();
636 Collection userColl = broker.getCollectionByQuery(q);
637
638 if (!userColl.isEmpty()) {
639 java.util.Iterator iter = userColl.iterator();
640 while (iter.hasNext()) {
641 contacts.add(iter.next());
642 }
643 }
644 }
645 catch (Exception e) {
646 Tools.toConsole("ADBSApplication - getSearch", e.toString());
647 }
648
649 return contacts;
650 }
651
652 public static Vector readCompanies(int bookId) {
653
654 Vector contacts = new Vector();
655 String firstname;
656 try {
657 Criteria crit1 = new Criteria();
658 crit1.addEqualTo("addbook_id", new Integer (bookId));
659 crit1.addEqualTo("civil_title", "company");
660 Query q = QueryFactory.newQuery(Contact.class, crit1);
661 broker.clearCache();
662 Collection userColl = broker.getCollectionByQuery(q);
663
664 if (!userColl.isEmpty()) {
665 java.util.Iterator iter = userColl.iterator();
666 while (iter.hasNext()) {
667 contacts.add(iter.next());
668 }
669 }
670 }
671 catch (Exception e) {
672 Tools.toConsole("ADBSApplication - getSearch", e.toString());
673 }
674
675 return contacts;
676 }
677
678 public static Contact SearchContactById(int id) {
679
680 Contact contact = null;
681
682 Contact description = null;
683 try {
684 Criteria crit1 = new Criteria();
685 crit1.addEqualTo("contact_id", new Integer (id));
686 Query q = QueryFactory.newQuery(Contact.class, crit1);
687 broker.clearCache();
688 Collection userColl = broker.getCollectionByQuery(q);
689
690 if (!userColl.isEmpty()) {
691 Iterator iter = userColl.iterator();
692 contact = (Contact) iter.next();
693
694 }
695 else {
696 contact = null;
697 }
698 }
699 catch (PersistenceBrokerException e) {
700 Tools.toConsole("DBServices - getPeriod", e.toString());
701 }
702
703 return contact;
704 }
705
706 public static void updateCompany(int id, String company) {
707
708 Contact contact = null;
709
710 Contact description = null;
711 try {
712 Criteria crit1 = new Criteria();
713 crit1.addEqualTo("company", new Integer (id));
714 Query q = QueryFactory.newQuery(Contact.class, crit1);
715 broker.clearCache();
716 Collection userColl = broker.getCollectionByQuery(q);
717
718 if (!userColl.isEmpty()) {
719 Iterator iter = userColl.iterator();
720 contact = (Contact) iter.next();
721 contact.setCompany(company);
722 broker.beginTransaction();
723 broker.store(contact);
724 broker.commitTransaction();
725
726 }
727 else {
728 contact = null;
729 }
730 }
731 catch (PersistenceBrokerException e) {
732 Tools.toConsole("DBServices - getPeriod", e.toString());
733 }
734 }
735
736 public static void update(int id, Contact contact) {
737
738 Contact change;
739
740 try {
741 Criteria crit1 = new Criteria();
742 crit1.addEqualTo("contact_id", new Integer (id));
743 Query q = QueryFactory.newQuery(Contact.class, crit1);
744 broker.clearCache();
745 Collection userColl = broker.getCollectionByQuery(q);
746
747 if (!userColl.isEmpty()) {
748 Iterator iter = userColl.iterator();
749 change = (Contact) iter.next();
750 change.setAddBookId(contact.getAddBookId());
751 change.setAddress1(contact.getAddress1());
752 change.setAddress2(contact.getAddress2());
753 change.setCategory(contact.getCategory());
754 change.setCity(contact.getCity());
755 change.setCivilTitle(contact.getCivilTitle());
756 change.setCompany(contact.getCompany());
757 change.setCountry(contact.getCountry());
758 change.setFirstName(contact.getFirstName());
759 change.setLastName(contact.getLastName());
760 change.setNotes(contact.getNotes());
761 change.setProfTitle(contact.getProfTitle());
762 change.setState(contact.getState());
763 change.setZip(contact.getZip());
764 change.setNumbersIds(contact.getNumbersIds());
765 change.setNumbersValues(contact.getNumbersValues());
766
767 broker.beginTransaction();
768 broker.store(change);
769 broker.commitTransaction();
770
771 }
772 else {
773 contact = null;
774 }
775 }
776 catch (PersistenceBrokerException e) {
777 Tools.toConsole("DBServices - update", e.toString());
778 }
779
780 }
781
782 public static Vector getSearchAddress(String value, int bookId) {
783 Vector contacts = new Vector();
784
785 Contact description = null;
786
787 String firstname;
788 try {
789 Criteria crit1 = new Criteria();
790 Criteria crit2 = new Criteria();
791 crit1.addEqualTo("address1", value);
792 crit1.addEqualTo("addbook_id", new Integer (bookId));
793 crit2.addEqualTo("address2", value);
794 crit2.addEqualTo("addbook_id", new Integer (bookId));
795 crit1.addOrCriteria(crit2);
796 Query q = QueryFactory.newQuery(Contact.class, crit1);
797 broker.clearCache();
798 Collection userColl = broker.getCollectionByQuery(q);
799
800 if (!userColl.isEmpty()) {
801 java.util.Iterator iter = userColl.iterator();
802 while (iter.hasNext()) {
803 contacts.add(iter.next());
804 String lastname = ( (Contact) iter.next()).getLastName();
805
806 }
807 }
808 }
809 catch (Exception e) {
810 Tools.toConsole("ADBSApplication - getSearch", e.toString());
811 }
812
813 return contacts;
814 }
815
816 public static Vector getSearchCity(String value, int bookId) {
817
818 Vector contacts = new Vector();
819
820 Contact description = null;
821
822 String firstname;
823 try {
824 Criteria crit1 = new Criteria();
825 crit1.addEqualTo("city", value);
826 crit1.addEqualTo("addbook_id", new Integer (bookId));
827 Query q = QueryFactory.newQuery(Contact.class, crit1);
828 broker.clearCache();
829 Collection userColl = broker.getCollectionByQuery(q);
830
831 if (!userColl.isEmpty()) {
832 java.util.Iterator iter = userColl.iterator();
833 while (iter.hasNext()) {
834 contacts.add(iter.next());
835 String lastname = ( (Contact) iter.next()).getLastName();
836
837 }
838 }
839 }
840 catch (Exception e) {
841 Tools.toConsole("ADBSApplication - getSearch", e.toString());
842 }
843
844 return contacts;
845 }
846
847 public static Vector getSearchCompany(String value, int bookId) {
848
849 Vector contacts = new Vector();
850
851 Contact description = null;
852
853 String firstname;
854 try {
855 Criteria crit1 = new Criteria();
856 crit1.addEqualTo("company", value);
857 crit1.addEqualTo("addbook_id", new Integer (bookId));
858 Query q = QueryFactory.newQuery(Contact.class, crit1);
859 broker.clearCache();
860 Collection userColl = broker.getCollectionByQuery(q);
861
862 if (!userColl.isEmpty()) {
863 java.util.Iterator iter = userColl.iterator();
864 while (iter.hasNext()) {
865 contacts.add(iter.next());
866 String lastname = ( (Contact) iter.next()).getLastName();
867
868 }
869 }
870 }
871 catch (Exception e) {
872 Tools.toConsole("ADBSApplication - getSearch", e.toString());
873 }
874
875 return contacts;
876 }
877
878 public static void createCategory(String label, int addbookId) {
879 try {
880 Criteria crit = new Criteria();
881 Category cat = null;
882 Query q = QueryFactory.newQuery(Category.class, crit);
883 crit.addEqualTo("label", label);
884 crit.addEqualTo("addbook_id", new Integer (addbookId));
885 broker.clearCache();
886 broker.beginTransaction();
887 Collection allCategory = broker.getCollectionByQuery(q);
888 Category category = null;
889 if (allCategory.isEmpty()) {
891 int categoryId = getNextId("category_id", "category");
892 cat = new Category(categoryId, label);
893 cat.setAddbookId(addbookId);
894 }
895 else { java.util.Iterator iter = allCategory.iterator();
897
901 }
902 broker.store(cat);
903 broker.commitTransaction();
904 }
905 catch (PersistenceBrokerException e) {
906 Tools.toConsole("AdbApplicationDB - createCategory", e.toString());
907 broker.abortTransaction();
908 }
909 }
910
911 } |
Popular Tags |
Java API By Example, From Geeks To Geeks.