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.impl.TagsAssetImpl;
33 import com.liferay.util.cal.CalendarUtil;
34 import com.liferay.util.dao.hibernate.QueryPos;
35 import com.liferay.util.dao.hibernate.QueryUtil;
36
37 import java.sql.Timestamp;
38
39 import java.util.ArrayList;
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 countByAndEntryIds(
81 long groupId, long[] classNameIds, long[] entryIds,
82 long[] notEntryIds, boolean excludeZeroViewCount, Date publishDate,
83 Date expirationDate)
84 throws SystemException {
85
86 if (entryIds.length == 0) {
87 return 0;
88 }
89
90 Session session = null;
91
92 try {
93 session = HibernateUtil.openSession();
94
95 StringMaker sm = new StringMaker();
96
97 sm.append("SELECT COUNT(DISTINCT assetId) AS COUNT_VALUE ");
98 sm.append("FROM TagsAsset WHERE TagsAsset.assetId IN (");
99
100 for (int i = 0; i < entryIds.length; i++) {
101 sm.append(CustomSQLUtil.get(FIND_BY_AND_ENTRY_IDS));
102
103 if ((i + 1) < entryIds.length) {
104 sm.append(" AND TagsAsset.assetId IN (");
105 }
106 }
107
108 for (int i = 0; i < entryIds.length; i++) {
109 if ((i + 1) < entryIds.length) {
110 sm.append(StringPool.CLOSE_PARENTHESIS);
111 }
112 }
113
114 if (excludeZeroViewCount) {
115 sm.append(" AND (TagsAsset.viewCount > 0)");
116 }
117
118 sm.append(StringPool.CLOSE_PARENTHESIS);
119
120 if (notEntryIds.length > 0) {
121 sm.append(" AND (");
122
123 for (int i = 0; i < notEntryIds.length; i++) {
124 sm.append("TagsAsset.assetId NOT IN (");
125 sm.append(CustomSQLUtil.get(FIND_BY_AND_ENTRY_IDS));
126 sm.append(StringPool.CLOSE_PARENTHESIS);
127
128 if ((i + 1) < notEntryIds.length) {
129 sm.append(" OR ");
130 }
131 }
132
133 sm.append(StringPool.CLOSE_PARENTHESIS);
134 }
135
136 sm.append("[$DATES$]");
137
138 if (groupId > 0) {
139 sm.append(" AND (TagsAsset.groupId = ?)");
140 }
141
142 sm.append(getClassNameIds(classNameIds));
143
144 String sql = sm.toString();
145
146 sql = getDates(sql, publishDate, expirationDate);
147
148 SQLQuery q = session.createSQLQuery(sql);
149
150 q.addScalar(HibernateUtil.getCountColumnName(), Hibernate.LONG);
151
152 QueryPos qPos = QueryPos.getInstance(q);
153
154 setEntryIds(qPos, entryIds);
155 setEntryIds(qPos, notEntryIds);
156 setDates(qPos, publishDate, expirationDate);
157
158 if (groupId > 0) {
159 setGroupId(qPos, groupId);
160 }
161
162 setClassNamedIds(qPos, classNameIds);
163
164 Iterator itr = q.list().iterator();
165
166 if (itr.hasNext()) {
167 Long count = (Long)itr.next();
168
169 if (count != null) {
170 return count.intValue();
171 }
172 }
173
174 return 0;
175 }
176 catch (Exception e) {
177 throw new SystemException(e);
178 }
179 finally {
180 HibernateUtil.closeSession(session);
181 }
182 }
183
184 public int countByOrEntryIds(
185 long groupId, long[] classNameIds, long[] entryIds,
186 long[] notEntryIds, boolean excludeZeroViewCount, Date publishDate,
187 Date expirationDate)
188 throws SystemException {
189
190 if (entryIds.length == 0) {
191 return 0;
192 }
193
194 Session session = null;
195
196 try {
197 session = HibernateUtil.openSession();
198
199 String sql = CustomSQLUtil.get(COUNT_BY_OR_ENTRY_IDS);
200
201 sql = StringUtil.replace(
202 sql, "[$ENTRY_ID$]", getEntryIds(entryIds, StringPool.EQUAL));
203
204 if (notEntryIds.length > 0) {
205 StringMaker sm = new StringMaker();
206
207 sm.append(" AND (");
208
209 for (int i = 0; i < notEntryIds.length; i++) {
210 sm.append("TagsAsset.assetId NOT IN (");
211 sm.append(CustomSQLUtil.get(FIND_BY_AND_ENTRY_IDS));
212 sm.append(StringPool.CLOSE_PARENTHESIS);
213
214 if ((i + 1) < notEntryIds.length) {
215 sm.append(" AND ");
216 }
217 }
218
219 sm.append(StringPool.CLOSE_PARENTHESIS);
220
221 sql = StringUtil.replace(
222 sql, "[$NOT_ENTRY_ID$]", sm.toString());
223 }
224 else {
225 sql = StringUtil.replace(
226 sql, "[$NOT_ENTRY_ID$]", StringPool.BLANK);
227 }
228
229 sql = getDates(sql, publishDate, expirationDate);
230
231 if (groupId > 0) {
232 sql += " AND (TagsAsset.groupId = ?)";
233 }
234
235 sql += getClassNameIds(classNameIds);
236
237 if (excludeZeroViewCount) {
238 sql += " AND (TagsAsset.viewCount > 0)";
239 }
240
241 SQLQuery q = session.createSQLQuery(sql);
242
243 q.addScalar(HibernateUtil.getCountColumnName(), Hibernate.LONG);
244
245 QueryPos qPos = QueryPos.getInstance(q);
246
247 setEntryIds(qPos, entryIds);
248 setEntryIds(qPos, notEntryIds);
249 setDates(qPos, publishDate, expirationDate);
250
251 if (groupId > 0) {
252 setGroupId(qPos, groupId);
253 }
254
255 setClassNamedIds(qPos, classNameIds);
256
257 Iterator itr = q.list().iterator();
258
259 if (itr.hasNext()) {
260 Long count = (Long)itr.next();
261
262 if (count != null) {
263 return count.intValue();
264 }
265 }
266
267 return 0;
268 }
269 catch (Exception e) {
270 throw new SystemException(e);
271 }
272 finally {
273 HibernateUtil.closeSession(session);
274 }
275 }
276
277 public List findByAndEntryIds(
278 long groupId, long[] classNameIds, long[] entryIds,
279 long[] notEntryIds, String orderByCol1, String orderByCol2,
280 String orderByType1, String orderByType2,
281 boolean excludeZeroViewCount, Date publishDate, Date expirationDate,
282 int begin, int end)
283 throws SystemException {
284
285 if (entryIds.length == 0) {
286 return new ArrayList();
287 }
288
289 orderByCol1 = checkOrderByCol(orderByCol1);
290 orderByCol2 = checkOrderByCol(orderByCol2);
291 orderByType1 = checkOrderByType(orderByType1);
292 orderByType2 = checkOrderByType(orderByType2);
293
294 Session session = null;
295
296 try {
297 session = HibernateUtil.openSession();
298
299 StringMaker sm = new StringMaker();
300
301 sm.append("SELECT DISTINCT {TagsAsset.*} ");
302 sm.append("FROM TagsAsset WHERE TagsAsset.assetId IN (");
303
304 for (int i = 0; i < entryIds.length; i++) {
305 sm.append(CustomSQLUtil.get(FIND_BY_AND_ENTRY_IDS));
306
307 if ((i + 1) < entryIds.length) {
308 sm.append(" AND TagsAsset.assetId IN (");
309 }
310 }
311
312 for (int i = 0; i < entryIds.length; i++) {
313 if ((i + 1) < entryIds.length) {
314 sm.append(StringPool.CLOSE_PARENTHESIS);
315 }
316 }
317
318 if (excludeZeroViewCount) {
319 sm.append(" AND (TagsAsset.viewCount > 0)");
320 }
321
322 sm.append(StringPool.CLOSE_PARENTHESIS);
323
324 if (notEntryIds.length > 0) {
325 sm.append(" AND (");
326
327 for (int i = 0; i < notEntryIds.length; i++) {
328 sm.append("TagsAsset.assetId NOT IN (");
329 sm.append(CustomSQLUtil.get(FIND_BY_AND_ENTRY_IDS));
330 sm.append(StringPool.CLOSE_PARENTHESIS);
331
332 if ((i + 1) < notEntryIds.length) {
333 sm.append(" OR ");
334 }
335 }
336
337 sm.append(StringPool.CLOSE_PARENTHESIS);
338 }
339
340 sm.append("[$DATES$]");
341
342 if (groupId > 0) {
343 sm.append(" AND (TagsAsset.groupId = ?)");
344 }
345
346 sm.append(getClassNameIds(classNameIds));
347
348 sm.append(" ORDER BY TagsAsset.");
349 sm.append(orderByCol1);
350 sm.append(StringPool.SPACE);
351 sm.append(orderByType1);
352
353 if (Validator.isNotNull(orderByCol2) &&
354 !orderByCol1.equals(orderByCol2)) {
355
356 sm.append(", TagsAsset.");
357 sm.append(orderByCol2);
358 sm.append(StringPool.SPACE);
359 sm.append(orderByType2);
360 }
361
362 String sql = sm.toString();
363
364 sql = getDates(sql, publishDate, expirationDate);
365
366 SQLQuery q = session.createSQLQuery(sql);
367
368 q.addEntity("TagsAsset", TagsAssetImpl.class);
369
370 QueryPos qPos = QueryPos.getInstance(q);
371
372 setEntryIds(qPos, entryIds);
373 setEntryIds(qPos, notEntryIds);
374 setDates(qPos, publishDate, expirationDate);
375
376 if (groupId > 0) {
377 setGroupId(qPos, groupId);
378 }
379
380 setClassNamedIds(qPos, classNameIds);
381
382 return QueryUtil.list(q, HibernateUtil.getDialect(), begin, end);
383 }
384 catch (Exception e) {
385 throw new SystemException(e);
386 }
387 finally {
388 HibernateUtil.closeSession(session);
389 }
390 }
391
392 public List findByOrEntryIds(
393 long groupId, long[] classNameIds, long[] entryIds,
394 long[] notEntryIds, Date publishDate, Date expirationDate )
395 throws SystemException {
396
397 return findByOrEntryIds(
398 groupId, classNameIds, entryIds, notEntryIds, null, null, null,
399 null, false, publishDate, expirationDate, QueryUtil.ALL_POS,
400 QueryUtil.ALL_POS);
401 }
402
403 public List findByOrEntryIds(
404 long groupId, long[] classNameIds, long[] entryIds,
405 long[] notEntryIds, String orderByCol1, String orderByCol2,
406 String orderByType1, String orderByType2,
407 boolean excludeZeroViewCount, Date publishDate, Date expirationDate,
408 int begin, int end)
409 throws SystemException {
410
411 if (entryIds.length == 0) {
412 return new ArrayList();
413 }
414
415 orderByCol1 = checkOrderByCol(orderByCol1);
416 orderByCol2 = checkOrderByCol(orderByCol2);
417 orderByType1 = checkOrderByType(orderByType1);
418 orderByType2 = checkOrderByType(orderByType2);
419
420 Session session = null;
421
422 try {
423 session = HibernateUtil.openSession();
424
425 String sql = CustomSQLUtil.get(FIND_BY_OR_ENTRY_IDS);
426
427 sql = StringUtil.replace(
428 sql, "[$ENTRY_ID$]", getEntryIds(entryIds, StringPool.EQUAL));
429
430 if (notEntryIds.length > 0) {
431 StringMaker sm = new StringMaker();
432
433 sm.append(" AND (");
434
435 for (int i = 0; i < notEntryIds.length; i++) {
436 sm.append("TagsAsset.assetId NOT IN (");
437 sm.append(CustomSQLUtil.get(FIND_BY_AND_ENTRY_IDS));
438 sm.append(StringPool.CLOSE_PARENTHESIS);
439
440 if ((i + 1) < notEntryIds.length) {
441 sm.append(" AND ");
442 }
443 }
444
445 sm.append(StringPool.CLOSE_PARENTHESIS);
446
447 sql = StringUtil.replace(
448 sql, "[$NOT_ENTRY_ID$]", sm.toString());
449 }
450 else {
451 sql = StringUtil.replace(
452 sql, "[$NOT_ENTRY_ID$]", StringPool.BLANK);
453 }
454
455 sql = getDates(sql, publishDate, expirationDate);
456
457 if (groupId > 0) {
458 sql += " AND (TagsAsset.groupId = ?)";
459 }
460
461 sql += getClassNameIds(classNameIds);
462
463 if (excludeZeroViewCount) {
464 sql += " AND (TagsAsset.viewCount > 0)";
465 }
466
467 StringMaker sm = new StringMaker();
468
469 sm.append(" ORDER BY TagsAsset.");
470 sm.append(orderByCol1);
471 sm.append(StringPool.SPACE);
472 sm.append(orderByType1);
473
474 if (Validator.isNotNull(orderByCol2) &&
475 !orderByCol1.equals(orderByCol2)) {
476
477 sm.append(", TagsAsset.");
478 sm.append(orderByCol2);
479 sm.append(StringPool.SPACE);
480 sm.append(orderByType2);
481 }
482
483 sql += sm.toString();
484
485 SQLQuery q = session.createSQLQuery(sql);
486
487 q.addEntity("TagsAsset", TagsAssetImpl.class);
488
489 QueryPos qPos = QueryPos.getInstance(q);
490
491 setEntryIds(qPos, entryIds);
492 setEntryIds(qPos, notEntryIds);
493 setDates(qPos, publishDate, expirationDate);
494
495 if (groupId > 0) {
496 setGroupId(qPos, groupId);
497 }
498
499 setClassNamedIds(qPos, classNameIds);
500
501 return QueryUtil.list(q, HibernateUtil.getDialect(), begin, end);
502 }
503 catch (Exception e) {
504 throw new SystemException(e);
505 }
506 finally {
507 HibernateUtil.closeSession(session);
508 }
509 }
510
511 public List findByViewCount(
512 long[] classNameId, boolean asc, int begin, int end)
513 throws SystemException {
514
515 Session session = null;
516
517 try {
518 session = HibernateUtil.openSession();
519
520 String sql = CustomSQLUtil.get(FIND_BY_VIEW_COUNT);
521
522 StringMaker sm = new StringMaker();
523
524 for (int i = 0; i < classNameId.length; i++) {
525 sm.append("(TagsAsset.classNameId = ?)");
526
527 if ((i+1) < classNameId.length) {
528 sm.append(" OR ");
529 }
530 }
531
532 sql = StringUtil.replace(
533 sql, "(TagsAsset.classNameId = ?)", sm.toString());
534
535 sm = new StringMaker();
536
537 sm.append(" ORDER BY TagsAsset.viewCount");
538
539 if (asc) {
540 sm.append(" ASC");
541 }
542 else {
543 sm.append(" DESC");
544 }
545
546 sql += sm.toString();
547
548 SQLQuery q = session.createSQLQuery(sql);
549
550 q.addEntity("TagsAsset", TagsAssetImpl.class);
551
552 QueryPos qPos = QueryPos.getInstance(q);
553
554 for (int i = 0; i < classNameId.length; i++) {
555 qPos.add(classNameId[i]);
556 }
557
558 return QueryUtil.list(q, HibernateUtil.getDialect(), begin, end);
559 }
560 catch (Exception e) {
561 throw new SystemException(e);
562 }
563 finally {
564 HibernateUtil.closeSession(session);
565 }
566 }
567
568 protected String checkOrderByCol(String orderByCol) {
569 if (orderByCol == null) {
570 return "modifiedDate";
571 }
572
573 for (int i = 0; i < ORDER_BY_COLUMNS.length; i++) {
574 if (orderByCol.equals(ORDER_BY_COLUMNS[i])) {
575 return orderByCol;
576 }
577 }
578
579 return "modifiedDate";
580 }
581
582 protected String checkOrderByType(String orderByType) {
583 if (orderByType == null) {
584 return "DESC";
585 }
586
587 for (int i = 0; i < ORDER_BY_TYPE.length; i++) {
588 if (orderByType.equals(ORDER_BY_TYPE[i])) {
589 return orderByType;
590 }
591 }
592
593 return "DESC";
594 }
595
596 protected String getClassNameIds(long[] classNameIds) {
597 StringMaker sm = new StringMaker();
598
599 if (classNameIds.length > 0) {
600 sm.append(" AND (");
601
602 for (int i = 0; i < classNameIds.length; i++) {
603 sm.append("classNameId = ?");
604
605 if (i > 0) {
606 sm.append(" AND ");
607 }
608 }
609
610 sm.append(") ");
611 }
612
613 return sm.toString();
614 }
615
616 protected String getDates(
617 String sql, Date publishDate, Date expirationDate) {
618
619 StringMaker sm = new StringMaker();
620
621 if (publishDate != null) {
622 sm.append(" AND (publishDate IS NULL OR publishDate < ?)");
623 }
624
625 if (expirationDate != null) {
626 sm.append(" AND (expirationDate IS NULL OR expirationDate > ?)");
627 }
628
629 sql = StringUtil.replace(sql, "[$DATES$]", sm.toString());
630
631 return sql;
632 }
633
634 protected String getEntryIds(long[] entryIds, String operator) {
635 StringMaker sm = new StringMaker();
636
637 for (int i = 0; i < entryIds.length; i++) {
638 sm.append("TagsEntry.entryId ");
639 sm.append(operator);
640 sm.append(" ? ");
641
642 if ((i + 1) != entryIds.length) {
643 sm.append("OR ");
644 }
645 }
646
647 return sm.toString();
648 }
649
650 protected void setClassNamedIds(QueryPos qPos, long[] classNameIds) {
651 for (int i = 0; i < classNameIds.length; i++) {
652 qPos.add(classNameIds[i]);
653 }
654 }
655
656 protected void setDates(
657 QueryPos qPos, Date publishDate, Date expirationDate) {
658
659 if (publishDate != null) {
660 Timestamp publishDate_TS = CalendarUtil.getTimestamp(publishDate);
661
662 qPos.add(publishDate_TS);
663 }
664
665 if (expirationDate != null) {
666 Timestamp expirationDate_TS =
667 CalendarUtil.getTimestamp(expirationDate);
668
669 qPos.add(expirationDate_TS);
670 }
671 }
672
673 protected void setGroupId(QueryPos qPos, long groupId) {
674 qPos.add(groupId);
675 }
676
677 protected void setEntryIds(QueryPos qPos, long[] entryIds) {
678 for (int i = 0; i < entryIds.length; i++) {
679 qPos.add(entryIds[i]);
680 }
681 }
682
683 }