1   /**
2    * Copyright (c) 2000-2007 Liferay, Inc. All rights reserved.
3    *
4    * Permission is hereby granted, free of charge, to any person obtaining a copy
5    * of this software and associated documentation files (the "Software"), to deal
6    * in the Software without restriction, including without limitation the rights
7    * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
8    * copies of the Software, and to permit persons to whom the Software is
9    * furnished to do so, subject to the following conditions:
10   *
11   * The above copyright notice and this permission notice shall be included in
12   * all copies or substantial portions of the Software.
13   *
14   * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
15   * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
16   * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
17   * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
18   * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
19   * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
20   * SOFTWARE.
21   */
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.spring.hibernate.CustomSQLUtil;
30  import com.liferay.portal.spring.hibernate.HibernateUtil;
31  import com.liferay.portlet.tags.model.impl.TagsAssetImpl;
32  import com.liferay.util.cal.CalendarUtil;
33  import com.liferay.util.dao.hibernate.QueryPos;
34  import com.liferay.util.dao.hibernate.QueryUtil;
35  
36  import java.sql.Timestamp;
37  
38  import java.util.ArrayList;
39  import java.util.Date;
40  import java.util.Iterator;
41  import java.util.List;
42  
43  import org.hibernate.Hibernate;
44  import org.hibernate.SQLQuery;
45  import org.hibernate.Session;
46  
47  /**
48   * <a href="TagsAssetFinder.java.html"><b><i>View Source</i></b></a>
49   *
50   * @author Brian Wing Shun Chan
51   *
52   */
53  public class TagsAssetFinder {
54  
55      public static String COUNT_BY_AND_ENTRY_IDS =
56          TagsAssetFinder.class.getName() + ".countByAndEntryIds";
57  
58      public static String COUNT_BY_OR_ENTRY_IDS =
59          TagsAssetFinder.class.getName() + ".countByOrEntryIds";
60  
61      public static String FIND_BY_AND_ENTRY_IDS =
62          TagsAssetFinder.class.getName() + ".findByAndEntryIds";
63  
64      public static String FIND_BY_OR_ENTRY_IDS =
65          TagsAssetFinder.class.getName() + ".findByOrEntryIds";
66  
67      public static int countByAndEntryIds(
68              long[] entryIds, long[] notEntryIds, Date publishDate,
69              Date expirationDate)
70          throws SystemException {
71  
72          if (entryIds.length == 0) {
73              return 0;
74          }
75  
76          Session session = null;
77  
78          try {
79              session = HibernateUtil.openSession();
80  
81              StringMaker sm = new StringMaker();
82  
83              sm.append("SELECT COUNT(DISTINCT assetId) AS COUNT_VALUE ");
84              sm.append("FROM TagsAsset WHERE TagsAsset.assetId IN (");
85  
86              for (int i = 0; i < entryIds.length; i++) {
87                  sm.append(CustomSQLUtil.get(FIND_BY_AND_ENTRY_IDS));
88  
89                  if ((i + 1) < entryIds.length) {
90                      sm.append(" AND TagsAsset.assetId IN (");
91                  }
92              }
93  
94              for (int i = 0; i < entryIds.length; i++) {
95                  if ((i + 1) < entryIds.length) {
96                      sm.append(StringPool.CLOSE_PARENTHESIS);
97                  }
98              }
99  
100             sm.append(StringPool.CLOSE_PARENTHESIS);
101 
102             if (notEntryIds.length > 0) {
103                 sm.append(" AND (");
104 
105                 for (int i = 0; i < notEntryIds.length; i++) {
106                     sm.append("TagsAsset.assetId NOT IN (");
107                     sm.append(CustomSQLUtil.get(FIND_BY_AND_ENTRY_IDS));
108                     sm.append(StringPool.CLOSE_PARENTHESIS);
109 
110                     if ((i + 1) < notEntryIds.length) {
111                         sm.append(" OR ");
112                     }
113                 }
114 
115                 sm.append(StringPool.CLOSE_PARENTHESIS);
116             }
117 
118             sm.append("[$DATES$]");
119 
120             String sql = sm.toString();
121 
122             sql = _getDates(sql, publishDate, expirationDate);
123 
124             SQLQuery q = session.createSQLQuery(sql);
125 
126             q.addScalar(HibernateUtil.getCountColumnName(), Hibernate.LONG);
127 
128             QueryPos qPos = QueryPos.getInstance(q);
129 
130             _setEntryIds(qPos, entryIds);
131             _setEntryIds(qPos, notEntryIds);
132             _setDates(qPos, publishDate, expirationDate);
133 
134             Iterator itr = q.list().iterator();
135 
136             if (itr.hasNext()) {
137                 Long count = (Long)itr.next();
138 
139                 if (count != null) {
140                     return count.intValue();
141                 }
142             }
143 
144             return 0;
145         }
146         catch (Exception e) {
147             throw new SystemException(e);
148         }
149         finally {
150             HibernateUtil.closeSession(session);
151         }
152     }
153 
154     public static int countByOrEntryIds(
155             long[] entryIds, long[] notEntryIds, Date publishDate,
156             Date expirationDate )
157         throws SystemException {
158 
159         if (entryIds.length == 0) {
160             return 0;
161         }
162 
163         Session session = null;
164 
165         try {
166             session = HibernateUtil.openSession();
167 
168             String sql = CustomSQLUtil.get(COUNT_BY_OR_ENTRY_IDS);
169 
170             sql = StringUtil.replace(
171                 sql, "[$ENTRY_ID$]", _getEntryIds(entryIds, StringPool.EQUAL));
172 
173             if (notEntryIds.length > 0) {
174                 StringMaker sm = new StringMaker();
175 
176                 sm.append(" AND (");
177 
178                 for (int i = 0; i < notEntryIds.length; i++) {
179                     sm.append("TagsAsset.assetId NOT IN (");
180                     sm.append(CustomSQLUtil.get(FIND_BY_AND_ENTRY_IDS));
181                     sm.append(StringPool.CLOSE_PARENTHESIS);
182 
183                     if ((i + 1) < notEntryIds.length) {
184                         sm.append(" AND ");
185                     }
186                 }
187 
188                 sm.append(StringPool.CLOSE_PARENTHESIS);
189 
190                 sql = StringUtil.replace(
191                     sql, "[$NOT_ENTRY_ID$]", sm.toString());
192             }
193             else {
194                 sql = StringUtil.replace(
195                     sql, "[$NOT_ENTRY_ID$]", StringPool.BLANK);
196             }
197 
198             sql = _getDates(sql, publishDate, expirationDate);
199 
200             SQLQuery q = session.createSQLQuery(sql);
201 
202             q.addScalar(HibernateUtil.getCountColumnName(), Hibernate.LONG);
203 
204             QueryPos qPos = QueryPos.getInstance(q);
205 
206             _setEntryIds(qPos, entryIds);
207             _setEntryIds(qPos, notEntryIds);
208             _setDates(qPos, publishDate, expirationDate);
209 
210             Iterator itr = q.list().iterator();
211 
212             if (itr.hasNext()) {
213                 Long count = (Long)itr.next();
214 
215                 if (count != null) {
216                     return count.intValue();
217                 }
218             }
219 
220             return 0;
221         }
222         catch (Exception e) {
223             throw new SystemException(e);
224         }
225         finally {
226             HibernateUtil.closeSession(session);
227         }
228     }
229 
230     public static List findByAndEntryIds(
231             long[] entryIds, long[] notEntryIds, Date publishDate,
232             Date expirationDate, int begin, int end)
233         throws SystemException {
234 
235         if (entryIds.length == 0) {
236             return new ArrayList();
237         }
238 
239         Session session = null;
240 
241         try {
242             session = HibernateUtil.openSession();
243 
244             StringMaker sm = new StringMaker();
245 
246             sm.append("SELECT DISTINCT {TagsAsset.*} ");
247             sm.append("FROM TagsAsset WHERE TagsAsset.assetId IN (");
248 
249             for (int i = 0; i < entryIds.length; i++) {
250                 sm.append(CustomSQLUtil.get(FIND_BY_AND_ENTRY_IDS));
251 
252                 if ((i + 1) < entryIds.length) {
253                     sm.append(" AND TagsAsset.assetId IN (");
254                 }
255             }
256 
257             for (int i = 0; i < entryIds.length; i++) {
258                 if ((i + 1) < entryIds.length) {
259                     sm.append(StringPool.CLOSE_PARENTHESIS);
260                 }
261             }
262 
263             sm.append(StringPool.CLOSE_PARENTHESIS);
264 
265             if (notEntryIds.length > 0) {
266                 sm.append(" AND (");
267 
268                 for (int i = 0; i < notEntryIds.length; i++) {
269                     sm.append("TagsAsset.assetId NOT IN (");
270                     sm.append(CustomSQLUtil.get(FIND_BY_AND_ENTRY_IDS));
271                     sm.append(StringPool.CLOSE_PARENTHESIS);
272 
273                     if ((i + 1) < notEntryIds.length) {
274                         sm.append(" OR ");
275                     }
276                 }
277 
278                 sm.append(StringPool.CLOSE_PARENTHESIS);
279             }
280 
281             sm.append("[$DATES$] ORDER BY TagsAsset.modifiedDate DESC");
282 
283             String sql = sm.toString();
284 
285             sql = _getDates(sql, publishDate, expirationDate);
286 
287             SQLQuery q = session.createSQLQuery(sql);
288 
289             q.addEntity("TagsAsset", TagsAssetImpl.class);
290 
291             QueryPos qPos = QueryPos.getInstance(q);
292 
293             _setEntryIds(qPos, entryIds);
294             _setEntryIds(qPos, notEntryIds);
295             _setDates(qPos, publishDate, expirationDate);
296 
297             return QueryUtil.list(q, HibernateUtil.getDialect(), begin, end);
298         }
299         catch (Exception e) {
300             throw new SystemException(e);
301         }
302         finally {
303             HibernateUtil.closeSession(session);
304         }
305     }
306 
307     public static List findByOrEntryIds(long[] entryIds, long[] notEntryIds,
308             Date publishDate, Date expirationDate )
309         throws SystemException {
310 
311         return findByOrEntryIds(
312             entryIds, notEntryIds, publishDate, expirationDate,
313             QueryUtil.ALL_POS, QueryUtil.ALL_POS);
314     }
315 
316     public static List findByOrEntryIds(
317             long[] entryIds, long[] notEntryIds, Date publishDate,
318             Date expirationDate, int begin, int end)
319         throws SystemException {
320 
321         if (entryIds.length == 0) {
322             return new ArrayList();
323         }
324 
325         Session session = null;
326 
327         try {
328             session = HibernateUtil.openSession();
329 
330             String sql = CustomSQLUtil.get(FIND_BY_OR_ENTRY_IDS);
331 
332             sql = StringUtil.replace(
333                 sql, "[$ENTRY_ID$]", _getEntryIds(entryIds, StringPool.EQUAL));
334 
335             if (notEntryIds.length > 0) {
336                 StringMaker sm = new StringMaker();
337 
338                 sm.append(" AND (");
339 
340                 for (int i = 0; i < notEntryIds.length; i++) {
341                     sm.append("TagsAsset.assetId NOT IN (");
342                     sm.append(CustomSQLUtil.get(FIND_BY_AND_ENTRY_IDS));
343                     sm.append(StringPool.CLOSE_PARENTHESIS);
344 
345                     if ((i + 1) < notEntryIds.length) {
346                         sm.append(" AND ");
347                     }
348                 }
349 
350                 sm.append(StringPool.CLOSE_PARENTHESIS);
351 
352                 sql = StringUtil.replace(
353                     sql, "[$NOT_ENTRY_ID$]", sm.toString());
354             }
355             else {
356                 sql = StringUtil.replace(
357                     sql, "[$NOT_ENTRY_ID$]", StringPool.BLANK);
358             }
359 
360             sql = _getDates(sql, publishDate, expirationDate);
361 
362             SQLQuery q = session.createSQLQuery(sql);
363 
364             q.addEntity("TagsAsset", TagsAssetImpl.class);
365 
366             QueryPos qPos = QueryPos.getInstance(q);
367 
368             _setEntryIds(qPos, entryIds);
369             _setEntryIds(qPos, notEntryIds);
370             _setDates(qPos, publishDate, expirationDate);
371 
372             return QueryUtil.list(q, HibernateUtil.getDialect(), begin, end);
373         }
374         catch (Exception e) {
375             throw new SystemException(e);
376         }
377         finally {
378             HibernateUtil.closeSession(session);
379         }
380     }
381 
382     private static String _getDates(
383         String sql, Date publishDate, Date expirationDate) {
384 
385         StringMaker sm = new StringMaker();
386 
387         if (publishDate != null) {
388             sm.append(" AND (publishDate IS NULL OR publishDate < ?)");
389         }
390 
391         if (expirationDate != null) {
392             sm.append(" AND (expirationDate IS NULL OR expirationDate > ?)");
393         }
394 
395         sql = StringUtil.replace(sql, "[$DATES$]", sm.toString());
396 
397         return sql;
398     }
399 
400     private static String _getEntryIds(long[] entryIds, String operator) {
401         StringMaker sm = new StringMaker();
402 
403         for (int i = 0; i < entryIds.length; i++) {
404             sm.append("TagsEntry.entryId ");
405             sm.append(operator);
406             sm.append(" ? ");
407 
408             if ((i + 1) != entryIds.length) {
409                 sm.append("OR ");
410             }
411         }
412 
413         return sm.toString();
414     }
415 
416     private static void _setEntryIds(QueryPos qPos, long[] entryIds) {
417         for (int i = 0; i < entryIds.length; i++) {
418             qPos.add(entryIds[i]);
419         }
420     }
421 
422     private static void _setDates(
423         QueryPos qPos, Date publishDate, Date expirationDate) {
424 
425         if (publishDate != null) {
426             Timestamp publishDate_TS = CalendarUtil.getTimestamp(publishDate);
427 
428             qPos.add(publishDate_TS);
429         }
430 
431         if (expirationDate != null) {
432             Timestamp expirationDate_TS =
433                 CalendarUtil.getTimestamp(expirationDate);
434 
435             qPos.add(expirationDate_TS);
436         }
437     }
438 
439 }