1
22
23 package com.liferay.portlet.tags.service.persistence;
24
25 import com.liferay.portal.SystemException;
26 import com.liferay.portal.kernel.util.StringMaker;
27 import com.liferay.portal.kernel.util.StringPool;
28 import com.liferay.portal.kernel.util.StringUtil;
29 import com.liferay.portal.kernel.util.Validator;
30 import com.liferay.portal.spring.hibernate.CustomSQLUtil;
31 import com.liferay.portal.spring.hibernate.HibernateUtil;
32 import com.liferay.portlet.tags.model.TagsAsset;
33 import com.liferay.portlet.tags.model.impl.TagsAssetImpl;
34 import com.liferay.util.cal.CalendarUtil;
35 import com.liferay.util.dao.hibernate.QueryPos;
36 import com.liferay.util.dao.hibernate.QueryUtil;
37
38 import java.sql.Timestamp;
39
40 import java.util.Date;
41 import java.util.Iterator;
42 import java.util.List;
43
44 import org.hibernate.Hibernate;
45 import org.hibernate.SQLQuery;
46 import org.hibernate.Session;
47
48
54 public class TagsAssetFinderImpl implements TagsAssetFinder {
55
56 public static String COUNT_BY_AND_ENTRY_IDS =
57 TagsAssetFinder.class.getName() + ".countByAndEntryIds";
58
59 public static String COUNT_BY_OR_ENTRY_IDS =
60 TagsAssetFinder.class.getName() + ".countByOrEntryIds";
61
62 public static String FIND_BY_AND_ENTRY_IDS =
63 TagsAssetFinder.class.getName() + ".findByAndEntryIds";
64
65 public static String FIND_BY_OR_ENTRY_IDS =
66 TagsAssetFinder.class.getName() + ".findByOrEntryIds";
67
68 public static String FIND_BY_VIEW_COUNT =
69 TagsAssetFinder.class.getName() + ".findByViewCount";
70
71 public static String[] ORDER_BY_COLUMNS = new String[] {
72 "title", "createDate", "modifiedDate", "publishDate", "expirationDate",
73 "priority", "viewCount"
74 };
75
76 public static String[] ORDER_BY_TYPE = new String[] {
77 "ASC", "DESC"
78 };
79
80 public int countAssets(
81 long groupId, long[] classNameIds, boolean excludeZeroViewCount,
82 Date publishDate, Date expirationDate)
83 throws SystemException {
84
85 Session session = null;
86
87 try {
88 session = HibernateUtil.openSession();
89
90 StringMaker sm = new StringMaker();
91
92 sm.append("SELECT COUNT(assetId) AS COUNT_VALUE ");
93 sm.append("FROM TagsAsset WHERE");
94 sm.append(" (1 = 1)");
95
96 if (excludeZeroViewCount) {
97 sm.append(" AND (TagsAsset.viewCount > 0)");
98 }
99
100 sm.append("[$DATES$]");
101
102 if (groupId > 0) {
103 sm.append(" AND (TagsAsset.groupId = ?)");
104 }
105
106 sm.append(getClassNameIds(classNameIds));
107
108 String sql = sm.toString();
109
110 sql = getDates(sql, publishDate, expirationDate);
111
112 SQLQuery q = session.createSQLQuery(sql);
113
114 q.addScalar(HibernateUtil.getCountColumnName(), Hibernate.LONG);
115
116 QueryPos qPos = QueryPos.getInstance(q);
117
118 setDates(qPos, publishDate, expirationDate);
119
120 if (groupId > 0) {
121 setGroupId(qPos, groupId);
122 }
123
124 setClassNamedIds(qPos, classNameIds);
125
126 Iterator<Long> itr = q.list().iterator();
127
128 if (itr.hasNext()) {
129 Long count = itr.next();
130
131 if (count != null) {
132 return count.intValue();
133 }
134 }
135
136 return 0;
137 }
138 catch (Exception e) {
139 throw new SystemException(e);
140 }
141 finally {
142 HibernateUtil.closeSession(session);
143 }
144 }
145
146 public int countByAndEntryIds(
147 long groupId, long[] classNameIds, long[] entryIds,
148 long[] notEntryIds, boolean excludeZeroViewCount, Date publishDate,
149 Date expirationDate)
150 throws SystemException {
151
152 Session session = null;
153
154 try {
155 session = HibernateUtil.openSession();
156
157 StringMaker sm = new StringMaker();
158
159 sm.append("SELECT COUNT(DISTINCT assetId) AS COUNT_VALUE ");
160 sm.append("FROM TagsAsset WHERE");
161
162 if (entryIds.length > 0) {
163 sm.append(" TagsAsset.assetId IN (");
164
165 for (int i = 0; i < entryIds.length; i++) {
166 sm.append(CustomSQLUtil.get(FIND_BY_AND_ENTRY_IDS));
167
168 if ((i + 1) < entryIds.length) {
169 sm.append(" AND TagsAsset.assetId IN (");
170 }
171 }
172
173 for (int i = 0; i < entryIds.length; i++) {
174 if ((i + 1) < entryIds.length) {
175 sm.append(StringPool.CLOSE_PARENTHESIS);
176 }
177 }
178
179 if (excludeZeroViewCount) {
180 sm.append(" AND (TagsAsset.viewCount > 0)");
181 }
182
183 sm.append(StringPool.CLOSE_PARENTHESIS);
184 }
185 else {
186 sm.append(" (1 = 1)");
187 }
188
189 if (notEntryIds.length > 0) {
190 sm.append(" AND (");
191
192 for (int i = 0; i < notEntryIds.length; i++) {
193 sm.append("TagsAsset.assetId NOT IN (");
194 sm.append(CustomSQLUtil.get(FIND_BY_AND_ENTRY_IDS));
195 sm.append(StringPool.CLOSE_PARENTHESIS);
196
197 if ((i + 1) < notEntryIds.length) {
198 sm.append(" OR ");
199 }
200 }
201
202 sm.append(StringPool.CLOSE_PARENTHESIS);
203 }
204
205 sm.append("[$DATES$]");
206
207 if (groupId > 0) {
208 sm.append(" AND (TagsAsset.groupId = ?)");
209 }
210
211 sm.append(getClassNameIds(classNameIds));
212
213 String sql = sm.toString();
214
215 sql = getDates(sql, publishDate, expirationDate);
216
217 SQLQuery q = session.createSQLQuery(sql);
218
219 q.addScalar(HibernateUtil.getCountColumnName(), Hibernate.LONG);
220
221 QueryPos qPos = QueryPos.getInstance(q);
222
223 setEntryIds(qPos, entryIds);
224 setEntryIds(qPos, notEntryIds);
225 setDates(qPos, publishDate, expirationDate);
226
227 if (groupId > 0) {
228 setGroupId(qPos, groupId);
229 }
230
231 setClassNamedIds(qPos, classNameIds);
232
233 Iterator<Long> itr = q.list().iterator();
234
235 if (itr.hasNext()) {
236 Long count = itr.next();
237
238 if (count != null) {
239 return count.intValue();
240 }
241 }
242
243 return 0;
244 }
245 catch (Exception e) {
246 throw new SystemException(e);
247 }
248 finally {
249 HibernateUtil.closeSession(session);
250 }
251 }
252
253 public int countByOrEntryIds(
254 long groupId, long[] classNameIds, long[] entryIds,
255 long[] notEntryIds, boolean excludeZeroViewCount, Date publishDate,
256 Date expirationDate)
257 throws SystemException {
258
259 Session session = null;
260
261 try {
262 session = HibernateUtil.openSession();
263
264 String sql = CustomSQLUtil.get(COUNT_BY_OR_ENTRY_IDS);
265
266 sql = StringUtil.replace(
267 sql, "[$ENTRY_ID$]", getEntryIds(entryIds, StringPool.EQUAL));
268
269 if (notEntryIds.length > 0) {
270 StringMaker sm = new StringMaker();
271
272 sm.append(" AND (");
273
274 for (int i = 0; i < notEntryIds.length; i++) {
275 sm.append("TagsAsset.assetId NOT IN (");
276 sm.append(CustomSQLUtil.get(FIND_BY_AND_ENTRY_IDS));
277 sm.append(StringPool.CLOSE_PARENTHESIS);
278
279 if ((i + 1) < notEntryIds.length) {
280 sm.append(" AND ");
281 }
282 }
283
284 sm.append(StringPool.CLOSE_PARENTHESIS);
285
286 sql = StringUtil.replace(
287 sql, "[$NOT_ENTRY_ID$]", sm.toString());
288 }
289 else {
290 sql = StringUtil.replace(
291 sql, "[$NOT_ENTRY_ID$]", StringPool.BLANK);
292 }
293
294 sql = getDates(sql, publishDate, expirationDate);
295
296 if (groupId > 0) {
297 sql += " AND (TagsAsset.groupId = ?)";
298 }
299
300 sql += getClassNameIds(classNameIds);
301
302 if (excludeZeroViewCount) {
303 sql += " AND (TagsAsset.viewCount > 0)";
304 }
305
306 SQLQuery q = session.createSQLQuery(sql);
307
308 q.addScalar(HibernateUtil.getCountColumnName(), Hibernate.LONG);
309
310 QueryPos qPos = QueryPos.getInstance(q);
311
312 setEntryIds(qPos, entryIds);
313 setEntryIds(qPos, notEntryIds);
314 setDates(qPos, publishDate, expirationDate);
315
316 if (groupId > 0) {
317 setGroupId(qPos, groupId);
318 }
319
320 setClassNamedIds(qPos, classNameIds);
321
322 Iterator<Long> itr = q.list().iterator();
323
324 if (itr.hasNext()) {
325 Long count = itr.next();
326
327 if (count != null) {
328 return count.intValue();
329 }
330 }
331
332 return 0;
333 }
334 catch (Exception e) {
335 throw new SystemException(e);
336 }
337 finally {
338 HibernateUtil.closeSession(session);
339 }
340 }
341
342 public List<TagsAsset> findAssets(
343 long groupId, long[] classNameIds, String orderByCol1,
344 String orderByCol2, String orderByType1, String orderByType2,
345 boolean excludeZeroViewCount, Date publishDate, Date expirationDate,
346 int begin, int end)
347 throws SystemException {
348
349 orderByCol1 = checkOrderByCol(orderByCol1);
350 orderByCol2 = checkOrderByCol(orderByCol2);
351 orderByType1 = checkOrderByType(orderByType1);
352 orderByType2 = checkOrderByType(orderByType2);
353
354 Session session = null;
355
356 try {
357 session = HibernateUtil.openSession();
358
359 StringMaker sm = new StringMaker();
360
361 sm.append("SELECT {TagsAsset.*} ");
362 sm.append("FROM TagsAsset WHERE");
363 sm.append(" (1 = 1)");
364
365 if (excludeZeroViewCount) {
366 sm.append(" AND (TagsAsset.viewCount > 0)");
367 }
368
369 sm.append("[$DATES$]");
370
371 if (groupId > 0) {
372 sm.append(" AND (TagsAsset.groupId = ?)");
373 }
374
375 sm.append(getClassNameIds(classNameIds));
376
377 sm.append(" ORDER BY TagsAsset.");
378 sm.append(orderByCol1);
379 sm.append(StringPool.SPACE);
380 sm.append(orderByType1);
381
382 if (Validator.isNotNull(orderByCol2) &&
383 !orderByCol1.equals(orderByCol2)) {
384
385 sm.append(", TagsAsset.");
386 sm.append(orderByCol2);
387 sm.append(StringPool.SPACE);
388 sm.append(orderByType2);
389 }
390
391 String sql = sm.toString();
392
393 sql = getDates(sql, publishDate, expirationDate);
394
395 SQLQuery q = session.createSQLQuery(sql);
396
397 q.addEntity("TagsAsset", TagsAssetImpl.class);
398
399 QueryPos qPos = QueryPos.getInstance(q);
400
401 setDates(qPos, publishDate, expirationDate);
402
403 if (groupId > 0) {
404 setGroupId(qPos, groupId);
405 }
406
407 setClassNamedIds(qPos, classNameIds);
408
409 return (List<TagsAsset>)QueryUtil.list(
410 q, HibernateUtil.getDialect(), begin, end);
411 }
412 catch (Exception e) {
413 throw new SystemException(e);
414 }
415 finally {
416 HibernateUtil.closeSession(session);
417 }
418 }
419
420 public List<TagsAsset> findByAndEntryIds(
421 long groupId, long[] classNameIds, long[] entryIds,
422 long[] notEntryIds, String orderByCol1, String orderByCol2,
423 String orderByType1, String orderByType2,
424 boolean excludeZeroViewCount, Date publishDate, Date expirationDate,
425 int begin, int end)
426 throws SystemException {
427
428 orderByCol1 = checkOrderByCol(orderByCol1);
429 orderByCol2 = checkOrderByCol(orderByCol2);
430 orderByType1 = checkOrderByType(orderByType1);
431 orderByType2 = checkOrderByType(orderByType2);
432
433 Session session = null;
434
435 try {
436 session = HibernateUtil.openSession();
437
438 StringMaker sm = new StringMaker();
439
440 sm.append("SELECT DISTINCT {TagsAsset.*} ");
441 sm.append("FROM TagsAsset WHERE");
442
443 if (entryIds.length > 0) {
444 sm.append(" TagsAsset.assetId IN (");
445
446 for (int i = 0; i < entryIds.length; i++) {
447 sm.append(CustomSQLUtil.get(FIND_BY_AND_ENTRY_IDS));
448
449 if ((i + 1) < entryIds.length) {
450 sm.append(" AND TagsAsset.assetId IN (");
451 }
452 }
453
454 for (int i = 0; i < entryIds.length; i++) {
455 if ((i + 1) < entryIds.length) {
456 sm.append(StringPool.CLOSE_PARENTHESIS);
457 }
458 }
459
460 if (excludeZeroViewCount) {
461 sm.append(" AND (TagsAsset.viewCount > 0)");
462 }
463
464 sm.append(StringPool.CLOSE_PARENTHESIS);
465 }
466 else {
467 sm.append(" (1 = 1)");
468 }
469
470 if (notEntryIds.length > 0) {
471 sm.append(" AND (");
472
473 for (int i = 0; i < notEntryIds.length; i++) {
474 sm.append("TagsAsset.assetId NOT IN (");
475 sm.append(CustomSQLUtil.get(FIND_BY_AND_ENTRY_IDS));
476 sm.append(StringPool.CLOSE_PARENTHESIS);
477
478 if ((i + 1) < notEntryIds.length) {
479 sm.append(" OR ");
480 }
481 }
482
483 sm.append(StringPool.CLOSE_PARENTHESIS);
484 }
485
486 sm.append("[$DATES$]");
487
488 if (groupId > 0) {
489 sm.append(" AND (TagsAsset.groupId = ?)");
490 }
491
492 sm.append(getClassNameIds(classNameIds));
493
494 sm.append(" ORDER BY TagsAsset.");
495 sm.append(orderByCol1);
496 sm.append(StringPool.SPACE);
497 sm.append(orderByType1);
498
499 if (Validator.isNotNull(orderByCol2) &&
500 !orderByCol1.equals(orderByCol2)) {
501
502 sm.append(", TagsAsset.");
503 sm.append(orderByCol2);
504 sm.append(StringPool.SPACE);
505 sm.append(orderByType2);
506 }
507
508 String sql = sm.toString();
509
510 sql = getDates(sql, publishDate, expirationDate);
511
512 SQLQuery q = session.createSQLQuery(sql);
513
514 q.addEntity("TagsAsset", TagsAssetImpl.class);
515
516 QueryPos qPos = QueryPos.getInstance(q);
517
518 setEntryIds(qPos, entryIds);
519 setEntryIds(qPos, notEntryIds);
520 setDates(qPos, publishDate, expirationDate);
521
522 if (groupId > 0) {
523 setGroupId(qPos, groupId);
524 }
525
526 setClassNamedIds(qPos, classNameIds);
527
528 return (List<TagsAsset>)QueryUtil.list(
529 q, HibernateUtil.getDialect(), begin, end);
530 }
531 catch (Exception e) {
532 throw new SystemException(e);
533 }
534 finally {
535 HibernateUtil.closeSession(session);
536 }
537 }
538
539 public List<TagsAsset> findByOrEntryIds(
540 long groupId, long[] classNameIds, long[] entryIds,
541 long[] notEntryIds, Date publishDate, Date expirationDate )
542 throws SystemException {
543
544 return findByOrEntryIds(
545 groupId, classNameIds, entryIds, notEntryIds, null, null, null,
546 null, false, publishDate, expirationDate, QueryUtil.ALL_POS,
547 QueryUtil.ALL_POS);
548 }
549
550 public List<TagsAsset> findByOrEntryIds(
551 long groupId, long[] classNameIds, long[] entryIds,
552 long[] notEntryIds, String orderByCol1, String orderByCol2,
553 String orderByType1, String orderByType2,
554 boolean excludeZeroViewCount, Date publishDate, Date expirationDate,
555 int begin, int end)
556 throws SystemException {
557
558 orderByCol1 = checkOrderByCol(orderByCol1);
559 orderByCol2 = checkOrderByCol(orderByCol2);
560 orderByType1 = checkOrderByType(orderByType1);
561 orderByType2 = checkOrderByType(orderByType2);
562
563 Session session = null;
564
565 try {
566 session = HibernateUtil.openSession();
567
568 String sql = CustomSQLUtil.get(FIND_BY_OR_ENTRY_IDS);
569
570 sql = StringUtil.replace(
571 sql, "[$ENTRY_ID$]", getEntryIds(entryIds, StringPool.EQUAL));
572
573 if (notEntryIds.length > 0) {
574 StringMaker sm = new StringMaker();
575
576 sm.append(" AND (");
577
578 for (int i = 0; i < notEntryIds.length; i++) {
579 sm.append("TagsAsset.assetId NOT IN (");
580 sm.append(CustomSQLUtil.get(FIND_BY_AND_ENTRY_IDS));
581 sm.append(StringPool.CLOSE_PARENTHESIS);
582
583 if ((i + 1) < notEntryIds.length) {
584 sm.append(" AND ");
585 }
586 }
587
588 sm.append(StringPool.CLOSE_PARENTHESIS);
589
590 sql = StringUtil.replace(
591 sql, "[$NOT_ENTRY_ID$]", sm.toString());
592 }
593 else {
594 sql = StringUtil.replace(
595 sql, "[$NOT_ENTRY_ID$]", StringPool.BLANK);
596 }
597
598 sql = getDates(sql, publishDate, expirationDate);
599
600 if (groupId > 0) {
601 sql += " AND (TagsAsset.groupId = ?)";
602 }
603
604 sql += getClassNameIds(classNameIds);
605
606 if (excludeZeroViewCount) {
607 sql += " AND (TagsAsset.viewCount > 0)";
608 }
609
610 StringMaker sm = new StringMaker();
611
612 sm.append(" ORDER BY TagsAsset.");
613 sm.append(orderByCol1);
614 sm.append(StringPool.SPACE);
615 sm.append(orderByType1);
616
617 if (Validator.isNotNull(orderByCol2) &&
618 !orderByCol1.equals(orderByCol2)) {
619
620 sm.append(", TagsAsset.");
621 sm.append(orderByCol2);
622 sm.append(StringPool.SPACE);
623 sm.append(orderByType2);
624 }
625
626 sql += sm.toString();
627
628 SQLQuery q = session.createSQLQuery(sql);
629
630 q.addEntity("TagsAsset", TagsAssetImpl.class);
631
632 QueryPos qPos = QueryPos.getInstance(q);
633
634 setEntryIds(qPos, entryIds);
635 setEntryIds(qPos, notEntryIds);
636 setDates(qPos, publishDate, expirationDate);
637
638 if (groupId > 0) {
639 setGroupId(qPos, groupId);
640 }
641
642 setClassNamedIds(qPos, classNameIds);
643
644 return (List<TagsAsset>)QueryUtil.list(
645 q, HibernateUtil.getDialect(), begin, end);
646 }
647 catch (Exception e) {
648 throw new SystemException(e);
649 }
650 finally {
651 HibernateUtil.closeSession(session);
652 }
653 }
654
655 public List<TagsAsset> findByViewCount(
656 long[] classNameId, boolean asc, int begin, int end)
657 throws SystemException {
658
659 Session session = null;
660
661 try {
662 session = HibernateUtil.openSession();
663
664 String sql = CustomSQLUtil.get(FIND_BY_VIEW_COUNT);
665
666 StringMaker sm = new StringMaker();
667
668 for (int i = 0; i < classNameId.length; i++) {
669 sm.append("(TagsAsset.classNameId = ?)");
670
671 if ((i+1) < classNameId.length) {
672 sm.append(" OR ");
673 }
674 }
675
676 sql = StringUtil.replace(
677 sql, "(TagsAsset.classNameId = ?)", sm.toString());
678
679 sm = new StringMaker();
680
681 sm.append(" ORDER BY TagsAsset.viewCount");
682
683 if (asc) {
684 sm.append(" ASC");
685 }
686 else {
687 sm.append(" DESC");
688 }
689
690 sql += sm.toString();
691
692 SQLQuery q = session.createSQLQuery(sql);
693
694 q.addEntity("TagsAsset", TagsAssetImpl.class);
695
696 QueryPos qPos = QueryPos.getInstance(q);
697
698 for (int i = 0; i < classNameId.length; i++) {
699 qPos.add(classNameId[i]);
700 }
701
702 return (List<TagsAsset>)QueryUtil.list(
703 q, HibernateUtil.getDialect(), begin, end);
704 }
705 catch (Exception e) {
706 throw new SystemException(e);
707 }
708 finally {
709 HibernateUtil.closeSession(session);
710 }
711 }
712
713 protected String checkOrderByCol(String orderByCol) {
714 if (orderByCol == null) {
715 return "modifiedDate";
716 }
717
718 for (int i = 0; i < ORDER_BY_COLUMNS.length; i++) {
719 if (orderByCol.equals(ORDER_BY_COLUMNS[i])) {
720 return orderByCol;
721 }
722 }
723
724 return "modifiedDate";
725 }
726
727 protected String checkOrderByType(String orderByType) {
728 if (orderByType == null) {
729 return "DESC";
730 }
731
732 for (int i = 0; i < ORDER_BY_TYPE.length; i++) {
733 if (orderByType.equals(ORDER_BY_TYPE[i])) {
734 return orderByType;
735 }
736 }
737
738 return "DESC";
739 }
740
741 protected String getClassNameIds(long[] classNameIds) {
742 StringMaker sm = new StringMaker();
743
744 if (classNameIds.length > 0) {
745 sm.append(" AND (");
746
747 for (int i = 0; i < classNameIds.length; i++) {
748 sm.append("classNameId = ?");
749
750 if (i > 0) {
751 sm.append(" AND ");
752 }
753 }
754
755 sm.append(") ");
756 }
757
758 return sm.toString();
759 }
760
761 protected String getDates(
762 String sql, Date publishDate, Date expirationDate) {
763
764 StringMaker sm = new StringMaker();
765
766 if (publishDate != null) {
767 sm.append(" AND (publishDate IS NULL OR publishDate < ?)");
768 }
769
770 if (expirationDate != null) {
771 sm.append(" AND (expirationDate IS NULL OR expirationDate > ?)");
772 }
773
774 sql = StringUtil.replace(sql, "[$DATES$]", sm.toString());
775
776 return sql;
777 }
778
779 protected String getEntryIds(long[] entryIds, String operator) {
780 StringMaker sm = new StringMaker();
781
782 for (int i = 0; i < entryIds.length; i++) {
783 sm.append("TagsEntry.entryId ");
784 sm.append(operator);
785 sm.append(" ? ");
786
787 if ((i + 1) != entryIds.length) {
788 sm.append("OR ");
789 }
790 }
791
792 if (sm.length() == 0) {
793 sm.append("(1 = 1)");
794 }
795
796 return sm.toString();
797 }
798
799 protected void setClassNamedIds(QueryPos qPos, long[] classNameIds) {
800 for (int i = 0; i < classNameIds.length; i++) {
801 qPos.add(classNameIds[i]);
802 }
803 }
804
805 protected void setDates(
806 QueryPos qPos, Date publishDate, Date expirationDate) {
807
808 if (publishDate != null) {
809 Timestamp publishDate_TS = CalendarUtil.getTimestamp(publishDate);
810
811 qPos.add(publishDate_TS);
812 }
813
814 if (expirationDate != null) {
815 Timestamp expirationDate_TS =
816 CalendarUtil.getTimestamp(expirationDate);
817
818 qPos.add(expirationDate_TS);
819 }
820 }
821
822 protected void setGroupId(QueryPos qPos, long groupId) {
823 qPos.add(groupId);
824 }
825
826 protected void setEntryIds(QueryPos qPos, long[] entryIds) {
827 for (int i = 0; i < entryIds.length; i++) {
828 qPos.add(entryIds[i]);
829 }
830 }
831
832 }