001: /**
002: * Copyright (c) 2000-2008 Liferay, Inc. All rights reserved.
003: *
004: * Permission is hereby granted, free of charge, to any person obtaining a copy
005: * of this software and associated documentation files (the "Software"), to deal
006: * in the Software without restriction, including without limitation the rights
007: * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
008: * copies of the Software, and to permit persons to whom the Software is
009: * furnished to do so, subject to the following conditions:
010: *
011: * The above copyright notice and this permission notice shall be included in
012: * all copies or substantial portions of the Software.
013: *
014: * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
015: * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
016: * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
017: * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
018: * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
019: * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
020: * SOFTWARE.
021: */package com.liferay.portlet.shopping.service.persistence;
022:
023: import com.liferay.portal.SystemException;
024: import com.liferay.portal.kernel.util.StringMaker;
025: import com.liferay.portal.kernel.util.StringUtil;
026: import com.liferay.portal.spring.hibernate.CustomSQLUtil;
027: import com.liferay.portal.spring.hibernate.HibernateUtil;
028: import com.liferay.portlet.shopping.model.impl.ShoppingItemImpl;
029: import com.liferay.util.dao.hibernate.QueryPos;
030: import com.liferay.util.dao.hibernate.QueryUtil;
031:
032: import java.util.Iterator;
033: import java.util.List;
034:
035: import org.hibernate.Hibernate;
036: import org.hibernate.SQLQuery;
037: import org.hibernate.Session;
038:
039: /**
040: * <a href="ShoppingItemFinderImpl.java.html"><b><i>View Source</i></b></a>
041: *
042: * @author Brian Wing Shun Chan
043: *
044: */
045: public class ShoppingItemFinderImpl implements ShoppingItemFinder {
046:
047: public static String COUNT_BY_CATEGORY_IDS = ShoppingItemFinder.class
048: .getName()
049: + ".countByCategoryIds";
050:
051: public int countByCategoryIds(List categoryIds)
052: throws SystemException {
053: Session session = null;
054:
055: try {
056: session = HibernateUtil.openSession();
057:
058: String sql = CustomSQLUtil.get(COUNT_BY_CATEGORY_IDS);
059:
060: sql = StringUtil.replace(sql, "[$CATEGORY_ID$]",
061: getCategoryIds(categoryIds));
062:
063: SQLQuery q = session.createSQLQuery(sql);
064:
065: q.addScalar(HibernateUtil.getCountColumnName(),
066: Hibernate.LONG);
067:
068: QueryPos qPos = QueryPos.getInstance(q);
069:
070: for (int i = 0; i < categoryIds.size(); i++) {
071: Long categoryId = (Long) categoryIds.get(i);
072:
073: qPos.add(categoryId);
074: }
075:
076: Iterator itr = q.list().iterator();
077:
078: if (itr.hasNext()) {
079: Long count = (Long) itr.next();
080:
081: if (count != null) {
082: return count.intValue();
083: }
084: }
085:
086: return 0;
087: } catch (Exception e) {
088: throw new SystemException(e);
089: } finally {
090: HibernateUtil.closeSession(session);
091: }
092: }
093:
094: public int countByFeatured(long groupId, long[] categoryIds)
095: throws SystemException {
096:
097: Session session = null;
098:
099: try {
100: session = HibernateUtil.openSession();
101:
102: StringMaker query = new StringMaker();
103:
104: query
105: .append("SELECT COUNT(*) AS COUNT_VALUE FROM ShoppingItem ");
106: query.append("INNER JOIN ShoppingCategory ON ");
107: query.append("ShoppingCategory.categoryId = ");
108: query.append("ShoppingItem.categoryId ");
109: query.append("WHERE ");
110: query.append("ShoppingCategory.groupId = ? AND (");
111:
112: if ((categoryIds != null) && (categoryIds.length > 0)) {
113: query.append("(");
114:
115: for (int i = 0; i < categoryIds.length; i++) {
116: query.append("ShoppingItem.categoryId = ? ");
117:
118: if (i + 1 < categoryIds.length) {
119: query.append("OR ");
120: }
121: }
122:
123: query.append(") AND ");
124: }
125:
126: query.append("ShoppingItem.featured = ? AND ");
127: query.append("ShoppingItem.smallImage = ?");
128:
129: SQLQuery q = session.createSQLQuery(query.toString());
130:
131: q.addScalar(HibernateUtil.getCountColumnName(),
132: Hibernate.LONG);
133:
134: QueryPos qPos = QueryPos.getInstance(q);
135:
136: qPos.add(groupId);
137:
138: for (int i = 0; i < categoryIds.length; i++) {
139: qPos.add(categoryIds[i]);
140: }
141:
142: qPos.add(true);
143: qPos.add(true);
144:
145: Iterator itr = q.list().iterator();
146:
147: if (itr.hasNext()) {
148: Long count = (Long) itr.next();
149:
150: if (count != null) {
151: return count.intValue();
152: }
153: }
154:
155: return 0;
156: } catch (Exception e) {
157: throw new SystemException(e);
158: } finally {
159: HibernateUtil.closeSession(session);
160: }
161: }
162:
163: public int countByKeywords(long groupId, long[] categoryIds,
164: String keywords) throws SystemException {
165:
166: Session session = null;
167:
168: try {
169: session = HibernateUtil.openSession();
170:
171: StringMaker query = new StringMaker();
172:
173: query
174: .append("SELECT COUNT(*) AS COUNT_VALUE FROM ShoppingItem ");
175: query.append("INNER JOIN ShoppingCategory ON ");
176: query.append("ShoppingCategory.categoryId = ");
177: query.append("ShoppingItem.categoryId ");
178: query.append("WHERE ");
179: query.append("ShoppingCategory.groupId = ? AND (");
180:
181: if ((categoryIds != null) && (categoryIds.length > 0)) {
182: query.append("(");
183:
184: for (int i = 0; i < categoryIds.length; i++) {
185: query.append("ShoppingItem.categoryId = ? ");
186:
187: if (i + 1 < categoryIds.length) {
188: query.append("OR ");
189: }
190: }
191:
192: query.append(") AND ");
193: }
194:
195: query.append("(ShoppingItem.name LIKE ? OR ");
196: query.append("ShoppingItem.description LIKE ? OR ");
197: query.append("ShoppingItem.properties LIKE ?))");
198:
199: keywords = '%' + keywords + '%';
200:
201: SQLQuery q = session.createSQLQuery(query.toString());
202:
203: q.addScalar(HibernateUtil.getCountColumnName(),
204: Hibernate.LONG);
205:
206: QueryPos qPos = QueryPos.getInstance(q);
207:
208: qPos.add(groupId);
209:
210: for (int i = 0; i < categoryIds.length; i++) {
211: qPos.add(categoryIds[i]);
212: }
213:
214: qPos.add(keywords);
215: qPos.add(keywords);
216: qPos.add(keywords);
217:
218: Iterator itr = q.list().iterator();
219:
220: if (itr.hasNext()) {
221: Long count = (Long) itr.next();
222:
223: if (count != null) {
224: return count.intValue();
225: }
226: }
227:
228: return 0;
229: } catch (Exception e) {
230: throw new SystemException(e);
231: } finally {
232: HibernateUtil.closeSession(session);
233: }
234: }
235:
236: public int countBySale(long groupId, long[] categoryIds)
237: throws SystemException {
238:
239: Session session = null;
240:
241: try {
242: session = HibernateUtil.openSession();
243:
244: StringMaker query = new StringMaker();
245:
246: query
247: .append("SELECT COUNT(*) AS COUNT_VALUE FROM ShoppingItem ");
248: query.append("INNER JOIN ShoppingCategory ON ");
249: query.append("ShoppingCategory.categoryId = ");
250: query.append("ShoppingItem.categoryId ");
251: query.append("WHERE ");
252: query.append("ShoppingCategory.groupId = ? AND (");
253:
254: if ((categoryIds != null) && (categoryIds.length > 0)) {
255: query.append("(");
256:
257: for (int i = 0; i < categoryIds.length; i++) {
258: query.append("ShoppingItem.categoryId = ? ");
259:
260: if (i + 1 < categoryIds.length) {
261: query.append("OR ");
262: }
263: }
264:
265: query.append(") AND ");
266: }
267:
268: query.append("ShoppingItem.sale = ? AND ");
269: query.append("ShoppingItem.smallImage = ?");
270:
271: SQLQuery q = session.createSQLQuery(query.toString());
272:
273: q.addScalar(HibernateUtil.getCountColumnName(),
274: Hibernate.LONG);
275:
276: QueryPos qPos = QueryPos.getInstance(q);
277:
278: qPos.add(groupId);
279:
280: for (int i = 0; i < categoryIds.length; i++) {
281: qPos.add(categoryIds[i]);
282: }
283:
284: qPos.add(true);
285: qPos.add(true);
286:
287: Iterator itr = q.list().iterator();
288:
289: if (itr.hasNext()) {
290: Long count = (Long) itr.next();
291:
292: if (count != null) {
293: return count.intValue();
294: }
295: }
296:
297: return 0;
298: } catch (Exception e) {
299: throw new SystemException(e);
300: } finally {
301: HibernateUtil.closeSession(session);
302: }
303: }
304:
305: public List findByFeatured(long groupId, long[] categoryIds,
306: int numOfItems) throws SystemException {
307:
308: int countByFeatured = countByFeatured(groupId, categoryIds);
309:
310: Session session = null;
311:
312: try {
313: session = HibernateUtil.openSession();
314:
315: StringMaker query = new StringMaker();
316:
317: query.append("SELECT {ShoppingItem.*} FROM ShoppingItem ");
318: query.append("INNER JOIN ShoppingCategory ON ");
319: query.append("ShoppingCategory.categoryId = ");
320: query.append("ShoppingItem.categoryId ");
321: query.append("WHERE ");
322: query.append("ShoppingCategory.groupId = ? AND (");
323:
324: if ((categoryIds != null) && (categoryIds.length > 0)) {
325: query.append("(");
326:
327: for (int i = 0; i < categoryIds.length; i++) {
328: query.append("ShoppingItem.categoryId = ? ");
329:
330: if (i + 1 < categoryIds.length) {
331: query.append("OR ");
332: }
333: }
334:
335: query.append(") AND ");
336: }
337:
338: query.append("ShoppingItem.featured = ? AND ");
339: query.append("ShoppingItem.smallImage = ?");
340:
341: SQLQuery q = session.createSQLQuery(query.toString());
342:
343: q.addEntity("ShoppingItem", ShoppingItemImpl.class);
344:
345: QueryPos qPos = QueryPos.getInstance(q);
346:
347: qPos.add(groupId);
348:
349: for (int i = 0; i < categoryIds.length; i++) {
350: qPos.add(categoryIds[i]);
351: }
352:
353: qPos.add(true);
354: qPos.add(true);
355:
356: return QueryUtil.randomList(q, HibernateUtil.getDialect(),
357: countByFeatured, numOfItems);
358: } catch (Exception e) {
359: throw new SystemException(e);
360: } finally {
361: HibernateUtil.closeSession(session);
362: }
363: }
364:
365: public List findByKeywords(long groupId, long[] categoryIds,
366: String keywords, int begin, int end) throws SystemException {
367:
368: Session session = null;
369:
370: try {
371: session = HibernateUtil.openSession();
372:
373: StringMaker query = new StringMaker();
374:
375: query.append("SELECT {ShoppingItem.*} FROM ShoppingItem ");
376: query.append("INNER JOIN ShoppingCategory ON ");
377: query.append("ShoppingCategory.categoryId = ");
378: query.append("ShoppingItem.categoryId ");
379: query.append("WHERE ");
380: query.append("ShoppingCategory.groupId = ? AND (");
381:
382: if ((categoryIds != null) && (categoryIds.length > 0)) {
383: query.append("(");
384:
385: for (int i = 0; i < categoryIds.length; i++) {
386: query.append("ShoppingItem.categoryId = ? ");
387:
388: if (i + 1 < categoryIds.length) {
389: query.append("OR ");
390: }
391: }
392:
393: query.append(") AND ");
394: }
395:
396: query.append("(ShoppingItem.name LIKE ? OR ");
397: query.append("ShoppingItem.description LIKE ? OR ");
398: query.append("ShoppingItem.properties LIKE ?))");
399:
400: keywords = '%' + keywords + '%';
401:
402: SQLQuery q = session.createSQLQuery(query.toString());
403:
404: q.addEntity("ShoppingItem", ShoppingItemImpl.class);
405:
406: QueryPos qPos = QueryPos.getInstance(q);
407:
408: qPos.add(groupId);
409:
410: for (int i = 0; i < categoryIds.length; i++) {
411: qPos.add(categoryIds[i]);
412: }
413:
414: qPos.add(keywords);
415: qPos.add(keywords);
416: qPos.add(keywords);
417:
418: return QueryUtil.list(q, HibernateUtil.getDialect(), begin,
419: end);
420: } catch (Exception e) {
421: throw new SystemException(e);
422: } finally {
423: HibernateUtil.closeSession(session);
424: }
425: }
426:
427: public List findBySale(long groupId, long[] categoryIds,
428: int numOfItems) throws SystemException {
429:
430: int countBySale = countBySale(groupId, categoryIds);
431:
432: Session session = null;
433:
434: try {
435: session = HibernateUtil.openSession();
436:
437: StringMaker query = new StringMaker();
438:
439: query.append("SELECT {ShoppingItem.*} FROM ShoppingItem ");
440: query.append("INNER JOIN ShoppingCategory ON ");
441: query.append("ShoppingCategory.categoryId = ");
442: query.append("ShoppingItem.categoryId ");
443: query.append("WHERE ");
444: query.append("ShoppingCategory.groupId = ? AND (");
445:
446: if ((categoryIds != null) && (categoryIds.length > 0)) {
447: query.append("(");
448:
449: for (int i = 0; i < categoryIds.length; i++) {
450: query.append("ShoppingItem.categoryId = ? ");
451:
452: if (i + 1 < categoryIds.length) {
453: query.append("OR ");
454: }
455: }
456:
457: query.append(") AND ");
458: }
459:
460: query.append("ShoppingItem.sale = ? AND ");
461: query.append("ShoppingItem.smallImage = ?");
462:
463: SQLQuery q = session.createSQLQuery(query.toString());
464:
465: q.addEntity("ShoppingItem", ShoppingItemImpl.class);
466:
467: QueryPos qPos = QueryPos.getInstance(q);
468:
469: qPos.add(groupId);
470:
471: for (int i = 0; i < categoryIds.length; i++) {
472: qPos.add(categoryIds[i]);
473: }
474:
475: qPos.add(true);
476: qPos.add(true);
477:
478: return QueryUtil.randomList(q, HibernateUtil.getDialect(),
479: countBySale, numOfItems);
480: } catch (Exception e) {
481: throw new SystemException(e);
482: } finally {
483: HibernateUtil.closeSession(session);
484: }
485: }
486:
487: protected String getCategoryIds(List categoryIds) {
488: StringMaker sm = new StringMaker();
489:
490: for (int i = 0; i < categoryIds.size(); i++) {
491: sm.append("categoryId = ? ");
492:
493: if ((i + 1) != categoryIds.size()) {
494: sm.append("OR ");
495: }
496: }
497:
498: return sm.toString();
499: }
500:
501: }
|