|
1
18
19 package sync4j.exchange.items.common.dao;
20
21 import java.io.IOException  ;
22
23 import java.sql.Connection ;
24 import java.sql.PreparedStatement ;
25 import java.sql.ResultSet ;
26 import java.sql.ResultSetMetaData ;
27 import java.sql.SQLException ;
28 import java.sql.Timestamp ;
29
30 import java.text.MessageFormat ;
31 import java.text.ParseException ;
32 import java.text.SimpleDateFormat ;
33
34 import java.util.ArrayList ;
35 import java.util.Date ;
36
37 import sync4j.exchange.httptransport.WebDavHttpTransport;
38 import sync4j.exchange.items.common.model.Item;
39 import sync4j.exchange.AuthenticationException;
40 import sync4j.exchange.ExchangeAccessException;
41 import sync4j.exchange.xml.XmlParseException;
42 import sync4j.exchange.xml.XmlParser;
43
44 import sync4j.exchange.util.DataAccess;
45 import sync4j.exchange.DataAccessException;
46
47 import sync4j.framework.server.store.NotFoundException;
48
49
57 public class ItemDAO {
58
59
61 protected static final String FILE_ENCODING = "UTF-8";
62
63 protected static final int MAX_YEAR = 4000;
64
65 protected static final String WEBDAV_HEADER_PROPPATCH =
66 "PROPPATCH {0}/{1} HTTP/1.0 \r\n";
67
68 protected static final String WEBDAV_HEADER_REMOVE =
69 "DELETE {0}/{1} HTTP/1.0\r\n";
70
71 protected static final String WEBDAV_HEADER_SELECT =
72 "SEARCH {0} HTTP/1.0\r\n" +
73 "Depth: 1,noroot\r\n";
74
75 private static final String WEBDAV_HEADER_PROPFIND =
76 "PROPFIND {0} HTTP/1.0\r\n" +
77 "Depth: 1,noroot\r\n";
78
79 private static final String WEBDAV_MSG_ACCESS_STATUS =
80 "<?xml version=\"1.0\" encoding=\"utf-8\" ?>\n" +
81 "<D:propfind xmlns:D=\"DAV:\">\n" +
82 "<D:allprop/>\n" +
83 "</D:propfind>\n" ;
84
85 private static final String WEBDAV_MSG_SELECT_ITEM =
86 "<?xml version=\"1.0\" encoding=\"utf-8\" ?>\n" +
87 "<D:searchrequest xmlns:D =\"DAV:\">\n" +
88 "<D:sql>\n" +
89 "Select " +
90 "\"http://schemas.microsoft.com/repl/repl-uid\" AS repluid, " +
91 "\"DAV:getlastmodified\" AS getlastmodified, " +
92 "\"DAV:creationdate\" AS creationdate, " +
93 "\"DAV:href\" AS href, " +
94 "\"DAV:isfolder\" AS isfolder " +
95 "FROM \"/{0}/{1}/{2}\" " +
96 "{3}" +
97 "</D:sql>\n" +
98 "</D:searchrequest>" ;
99
100 private static final String WEBDAV_MSG_PROPFIND_SELECT_ITEM =
101 "<?xml version=\"1.0\" encoding=\"utf-8\" ?>\n" +
102 "<D:propfind " +
103 "xmlns:D=\"DAV:\" " +
104 "xmlns:EN=\"http://schemas.microsoft.com/repl/\"" +
105 ">\n" +
106 "<D:prop><D:href/></D:prop>\n" +
107 "<D:prop><D:creationdate/></D:prop>\n" +
108 "<D:prop><D:getlastmodified/></D:prop>\n" +
109 "<D:prop><EN:repl-uid/></D:prop>\n" +
110 "</D:propfind>\n" ;
111
112 private static final String CLAUSE_REPLID_FIELD
113 = "\"http://schemas.microsoft.com/repl/repl-uid\"" ;
114
115 private static final String CONTENT_REPLID_START = "rid:" ;
116
117
118 private static final String SQL_SELECT_ITEMS_BY_PRINCIPAL_BY_SOURCE =
119 "select id, last_modified " +
120 "from ss_exch_item_status " +
121 "where " +
122 "uri = ? and principal = ?" ;
123
124 private static final String SQL_ADD_LOCAL_ITEM =
125 "insert into ss_exch_item_status " +
126 "(id, last_modified, uri, principal) " +
127 "values(?, ?, ?, ?)" ;
128
129 private static final String SQL_DELETE_LOCAL_ITEMS =
130 "delete from ss_exch_item_status " +
131 "where uri = ? and principal = ? " ;
132
133 private static final String TAG_HREF = "href" ;
134 private static final String TAG_CREATION_DATE = "creationdate" ;
135 private static final String TAG_REPLUID = "repluid" ;
136 private static final String TAG_LAST_MODIFIED = "getlastmodified" ;
137 protected
138 static final String TAG_IS_FOLDER = "isfolder" ;
139 private static final String TAG_PROP = "a:prop" ;
140
141 protected
142 static final String PROP_NO_FOLDER = "0" ;
143
144 protected static final String NEW_LINE = "\r\n" ;
145
146 private static final int
147 STATUS_ACCESS_DANIED = 401 ;
148 private static final int
149 STATUS_OK_LOAD_CHANGE = 207 ;
150 private static final int
151 STATUS_OK_DELETE = 200 ;
152 private static final int
153 STATUS_NOT_FOUND = 404 ;
154
155 private static final String DATE_FORMAT_WEBDAV =
156 "yyyy-MM-dd'T'HH:mm:ss'.000Z'" ;
157
158 private static final String DATE_FORMAT_PDI =
159 "yyyyMMdd'T'HHmmss'Z'" ;
160
161
163 private DataAccess da = null ;
164 private WebDavHttpTransport webDavHttp = null ;
165
166
168 public ItemDAO ()
169 throws DataAccessException {
170
171 this.da = new DataAccess () ;
172
173 }
174
175 public ItemDAO (String host ,
176 int port )
177 throws DataAccessException {
178
179 this.da = new DataAccess () ;
180 this.webDavHttp = new WebDavHttpTransport(host, port);
181
182 }
183
184
186
196 public Item[] getLocalItems (String sourceURI, String principal)
197 throws DataAccessException {
198
199 Connection connection = null ;
200 PreparedStatement ps = null ;
201 ResultSet rs = null ;
202
203 ArrayList rows = null ;
204
205 Item[] items = null ;
206 Item item = null ;
207
208 int count = 0 ;
209
210 try {
211
212 connection = this.da.getConnection();
213
214 rows = new ArrayList ();
215
216 ps = connection.prepareStatement(
217 SQL_SELECT_ITEMS_BY_PRINCIPAL_BY_SOURCE);
218
219 ps.setString (1, sourceURI ) ;
220 ps.setString (2, principal ) ;
221
222 rs = ps.executeQuery();
223
224 while(rs.next()) {
225 rows.add(new Item(rs.getString(1),
226 new Date (rs.getTimestamp(2).getTime())));
227 ++count;
228 }
229
230 items = (Item[]) rows.toArray(new Item[count]);
231
232 } catch (Exception e) {
233 throw new DataAccessException("Error getting local items", e);
234 } finally {
235 try {
236 this.da.cleanUp(connection, ps, rs);
237 } catch (Exception e) {
238 throw new DataAccessException("Error getting local items", e);
239 }
240 }
241
242 return items;
243
244 }
245
246
255 public void addLocalItems(Item[] items ,
256 String sourceURI ,
257 String principal
258 )
259 throws DataAccessException {
260
261 Connection connection = null ;
262 PreparedStatement ps = null ;
263
264 String id = null;
265 Date lastModified = null;
266
267 try {
268
269 connection = this.da.getConnection();
270
271 for (int i=0, l = items.length; i < l; i++) {
272
273 ps = connection.prepareStatement(SQL_ADD_LOCAL_ITEM);
274
275 id = items[i].getId() ;
276 lastModified = items[i].getLastModified() ;
277
278 ps.setString (1, id ) ;
279 ps.setTimestamp (2, new Timestamp (lastModified.getTime()) ) ;
280 ps.setString (3, sourceURI ) ;
281 ps.setString (4, principal ) ;
282
283 ps.executeUpdate();
284
285 ps.close() ;
286 ps = null ;
287
288 }
289
290
291 } catch (Exception e) {
292 throw new DataAccessException("Error adding local items", e);
293 } finally {
294 try {
295 this.da.cleanUp(connection, ps, null);
296 } catch (Exception e) {
297 throw new DataAccessException("Error adding local items", e);
298 }
299 }
300
301 }
302
303
311 public void deleteLocalItems(String sourceURI, String principal)
312 throws DataAccessException {
313
314 Connection connection = null ;
315 PreparedStatement ps = null ;
316
317 try {
318
319 connection = this.da.getConnection();
320
321 ps = connection.prepareStatement(SQL_DELETE_LOCAL_ITEMS);
322
323 ps.setString (1, sourceURI ) ;
324 ps.setString (2, principal ) ;
325
326 ps.executeUpdate();
327
328 } catch (Exception e) {
329 throw new DataAccessException("Error deleting local items", e);
330 } finally {
331 try {
332 this.da.cleanUp(connection, ps, null);
333 } catch (Exception e) {
334 throw new DataAccessException("Error deleting local items", e);
335 }
336 }
337
338 }
339
340
351 public Item[] getAllExchangeItems(String username ,
352 String credentials ,
353 String exchangeFolder )
354 throws DataAccessException {
355
356 ArrayList items = null ;
357 Item item = null ;
358
359 String [] props = null ;
360 String prop = null ;
361 String [] resp = null ;
362
363 String replUid = null ;
364 String uri = null ;
365 String creationDate = null ;
366 String lastUpdate = null ;
367
368 String isFolder = null ;
369
370 String response = null ;
371
372 String webDavBodyMsg = null ;
373
374 String webDavHeaderMsg = null ;
375
376 String server = null ;
377 String resource = null ;
378
379 int count = 0 ;
380
381 String clause = null ;
382
383 server = getServerFromExchangeFolder (exchangeFolder );
384 resource = getResourceFromExchangeFolder (exchangeFolder );
385
386 if ("calendar".equals(resource.toLowerCase())) {
387 clause = " WHERE not (\"http://schemas.microsoft.com/mapi/id/{00062002-0000-0000-C000-000000000046}/0x8231\" != cast(\"0\" as int) " +
388 "and (\"urn:schemas:calendar:instancetype\" != 1 and \"urn:schemas:calendar:instancetype\" != 0)) " ;
389 } else {
390 clause = "";
391 }
392
393 webDavBodyMsg =
394 MessageFormat.format(WEBDAV_MSG_SELECT_ITEM,
395 new Object [] {server, username, resource, clause});
396
397 webDavHeaderMsg =
398 MessageFormat.format(WEBDAV_HEADER_SELECT,
399
400 new Object [] {
401 "/" +
402 server +
403 "/" +
404 username +
405 "/" +
406 resource
407 }
408 );
409
410
411 try {
412 response = this.webDavHttp.sendRequest(webDavHeaderMsg ,
413 credentials ,
414 webDavBodyMsg ,
415 FILE_ENCODING );
416 } catch (IOException e) {
417 throw new DataAccessException("Error getting Exchange server response",
418 e);
419 }
420
421 try {
422 int s = getStatusFromResponse(response);
423 checkResponseStatus(s);
424 } catch (Exception e) {
425
426 throw new DataAccessException("USER " +
427 username +
428 " URI " +
429 exchangeFolder +
430 " reading exchange items" ,
431 e) ;
432 }
433
434 resp = new String [] {response};
435
436 try {
437 props = XmlParser.getXMLTag(resp, TAG_PROP);
438 } catch (XmlParseException e) {
439 throw new DataAccessException("Error parsing item", e);
440 }
441
442 items = new ArrayList ();
443
444 for (int i=0, l = props.length; i < l; i++) {
445
446 try {
447
448 prop = props[i];
449
450 isFolder = XmlParser.getXMLInitTagValue
451 (prop, TAG_IS_FOLDER) ;
452
453 replUid = XmlParser.getXMLTagValue
454 (prop, TAG_REPLUID) ;
455
456 uri = XmlParser.getXMLTagValue
457 (prop, TAG_HREF) ;
458
459 creationDate = XmlParser.getXMLInitTagValue
460 (prop, TAG_CREATION_DATE) ;
461
462 lastUpdate = XmlParser.getXMLInitTagValue
463 (prop, TAG_LAST_MODIFIED) ;
464
465 item = new Item(getIdFromReplUid(replUid) ,
466 XmlParser.getHrefFromUri(uri) ,
467 XmlParser.webDavTagToDate(creationDate) ,
468 XmlParser.webDavTagToDate(lastUpdate));
469
470 if(PROP_NO_FOLDER.equals(isFolder)) {
471 items.add(item);
472 count ++;
473 }
474
475 } catch (XmlParseException e) {
476 throw new DataAccessException("Error parsing item", e);
477 }
478
479 }
480
481 return (Item[])items.toArray(new Item[count]);
482 }
483
484
495 public int getExchangeAccessStatus(String exchangeServerName ,
496 String userName ,
497 String credentials )
498 throws DataAccessException {
499
500
501 String webDavHeaderMsg = null ;
502 String msg = null ;
503
504 int status = 0 ;
505
506 webDavHeaderMsg
507 = MessageFormat.format(ItemDAO.WEBDAV_HEADER_PROPFIND,
508 new Object [] {
509 "/" +
510 exchangeServerName +
511 "/" +
512 userName +
513 "/"
514 }
515 );
516
517 try {
518 msg = this.webDavHttp.sendRequest (webDavHeaderMsg,
519 credentials,
520 WEBDAV_MSG_ACCESS_STATUS,
521 FILE_ENCODING);
522 } catch (IOException e) {
523 throw new DataAccessException("Error getting Exchange server response",
524 e);
525 }
526
527
528 try {
529 status = getStatusFromResponse(msg);
530 } catch (Exception e) {
531
532 throw new DataAccessException("USER " +
533 userName +
534 " access exchange items" ,
535 e) ;
536 }
537
538 return status;
539
540 }
541
542
550 protected int getStatusFromResponse(String msg)
551 throws ExchangeAccessException
552 {
553
554 String header = null;
555 String value = null;
556 int status = 0 ;
557
558 char[] newLine = {13, 10};
559
560 if (msg == null && msg.length() < 30) {
561 throw new ExchangeAccessException
562 ("Exchange server, wrong response: " + msg);
563 }
564
565 header = msg.substring(0, msg.indexOf(new String (newLine)));
566
567 value = header.substring(header.indexOf(" ") + 1);
568 value = value.substring(0, value.indexOf(" "));
569
570 try {
571 status = Integer.parseInt(value);
572 } catch (Exception e) {
573 throw new ExchangeAccessException
574 ("Exchange server, wrong status: " + value);
575 }
576
577 return status;
578
579 }
580
581
582
592 protected void checkResponseStatus(int status)
593 throws AuthenticationException ,
594 ExchangeAccessException ,
595 NotFoundException
596 {
597
598 if (status == STATUS_ACCESS_DANIED) {
599 throw new AuthenticationException
600 ("Exchange server, access denied");
601 }
602
603 if (!(status == STATUS_OK_LOAD_CHANGE ) &&
604 !(status == STATUS_OK_DELETE ) &&
605 !(status == STATUS_NOT_FOUND ) ) {
606 throw new ExchangeAccessException
607 ("Exchange server, error accessing resource");
608 }
609
610 if (status == STATUS_NOT_FOUND) {
611 throw new NotFoundException
612 ("Exchange server, resource not found");
613 }
614
615 }
616
617
625 public static String getClause (String [] ids) {
626
627 String clause = null;
628
629 int l = ids.length;
630
631 if (l == 0) {
632 return "";
633 }
634
635 clause = "where (" +
636 CLAUSE_REPLID_FIELD +
637 " = '" +
638 CONTENT_REPLID_START +
639 ids[0] +
640 "'" ;
641
642 if (l > 1) {
643
644 for (int i = 1; i < l; i++) {
645 clause = clause +
646 " OR " +
647 CLAUSE_REPLID_FIELD +
648 " = '" +
649 CONTENT_REPLID_START +
650 ids[i] +
651 "'" ;
652 }
653
654 }
655
656 clause = clause + ")\r\n";
657
658 return clause;
659
660 }
661
662
669 public static String getIdFromReplUid(String replUid)
670 throws XmlParseException {
671
672 int start = 0;
673
674 start = replUid.indexOf(CONTENT_REPLID_START);
675
676 if (start == -1) {
677 throw new XmlParseException("Error in replUid-TAG, value: " + replUid);
678 }
679
680 return replUid.substring(start + CONTENT_REPLID_START.length());
681 }
682
683
690 public static String dateToWebDavTag(String date)
691 throws Exception {
692
693 SimpleDateFormat formatter = null ;
694 String webDavDate = null ;
695 Date dt = null ;
696
697 if (date != null) {
698
699 formatter = new SimpleDateFormat (DATE_FORMAT_PDI);
700
701 dt = formatter.parse(date);
702
703 webDavDate = dateToWebDavTag(dt);
704
705 } else {
706
707 webDavDate = "";
708
709 }
710
711 return webDavDate;
712 }
713
714
721 public static String dateToWebDavTag(Date date) {
722
723 SimpleDateFormat formatter = null;
724 String webDavDate = null;
725
726 if (date != null) {
727
728 formatter = new SimpleDateFormat (DATE_FORMAT_WEBDAV);
729
730 webDavDate = formatter.format(date);
731
732 if (webDavDate.length() > 4) {
733 String year = webDavDate.substring(0, 4);
734 if (Integer.parseInt(year) > MAX_YEAR) {
735 webDavDate = "";
736 }
737 }
738 } else {
739
740 webDavDate = "";
741
742 }
743
744 return webDavDate;
745 }
746
747
749 protected String getServerFromExchangeFolder(String uri) {
750 return uri.substring(0, uri.indexOf("/"));
751 }
752
753 protected String getResourceFromExchangeFolder(String uri) {
754 return uri.substring(uri.indexOf("/") + 1);
755 }
756
757 }
758 |
Popular Tags |
Java API By Example, From Geeks To Geeks.