1
22
23 package com.liferay.portlet.shopping.service.persistence;
24
25 import com.liferay.portal.SystemException;
26 import com.liferay.portal.kernel.util.StringMaker;
27 import com.liferay.portal.kernel.util.StringUtil;
28 import com.liferay.portal.spring.hibernate.CustomSQLUtil;
29 import com.liferay.portal.spring.hibernate.HibernateUtil;
30 import com.liferay.portlet.shopping.model.ShoppingItem;
31 import com.liferay.portlet.shopping.model.impl.ShoppingItemImpl;
32 import com.liferay.util.dao.hibernate.QueryPos;
33 import com.liferay.util.dao.hibernate.QueryUtil;
34
35 import java.util.Iterator;
36 import java.util.List;
37
38 import org.hibernate.Hibernate;
39 import org.hibernate.SQLQuery;
40 import org.hibernate.Session;
41
42
48 public class ShoppingItemFinderImpl implements ShoppingItemFinder {
49
50 public static String COUNT_BY_CATEGORY_IDS =
51 ShoppingItemFinder.class.getName() + ".countByCategoryIds";
52
53 public int countByCategoryIds(List<Long> categoryIds)
54 throws SystemException {
55
56 Session session = null;
57
58 try {
59 session = HibernateUtil.openSession();
60
61 String sql = CustomSQLUtil.get(COUNT_BY_CATEGORY_IDS);
62
63 sql = StringUtil.replace(
64 sql, "[$CATEGORY_ID$]", getCategoryIds(categoryIds));
65
66 SQLQuery q = session.createSQLQuery(sql);
67
68 q.addScalar(HibernateUtil.getCountColumnName(), Hibernate.LONG);
69
70 QueryPos qPos = QueryPos.getInstance(q);
71
72 for (int i = 0; i < categoryIds.size(); i++) {
73 Long categoryId = categoryIds.get(i);
74
75 qPos.add(categoryId);
76 }
77
78 Iterator<Long> itr = q.list().iterator();
79
80 if (itr.hasNext()) {
81 Long count = itr.next();
82
83 if (count != null) {
84 return count.intValue();
85 }
86 }
87
88 return 0;
89 }
90 catch (Exception e) {
91 throw new SystemException(e);
92 }
93 finally {
94 HibernateUtil.closeSession(session);
95 }
96 }
97
98 public int countByFeatured(long groupId, long[] categoryIds)
99 throws SystemException {
100
101 Session session = null;
102
103 try {
104 session = HibernateUtil.openSession();
105
106 StringMaker query = new StringMaker();
107
108 query.append("SELECT COUNT(*) AS COUNT_VALUE FROM ShoppingItem ");
109 query.append("INNER JOIN ShoppingCategory ON ");
110 query.append("ShoppingCategory.categoryId = ");
111 query.append("ShoppingItem.categoryId ");
112 query.append("WHERE ");
113 query.append("ShoppingCategory.groupId = ? AND (");
114
115 if ((categoryIds != null) && (categoryIds.length > 0)) {
116 query.append("(");
117
118 for (int i = 0; i < categoryIds.length; i++) {
119 query.append("ShoppingItem.categoryId = ? ");
120
121 if (i + 1 < categoryIds.length) {
122 query.append("OR ");
123 }
124 }
125
126 query.append(") AND ");
127 }
128
129 query.append("ShoppingItem.featured = ? AND ");
130 query.append("ShoppingItem.smallImage = ?");
131
132 SQLQuery q = session.createSQLQuery(query.toString());
133
134 q.addScalar(HibernateUtil.getCountColumnName(), Hibernate.LONG);
135
136 QueryPos qPos = QueryPos.getInstance(q);
137
138 qPos.add(groupId);
139
140 for (int i = 0; i < categoryIds.length; i++) {
141 qPos.add(categoryIds[i]);
142 }
143
144 qPos.add(true);
145 qPos.add(true);
146
147 Iterator<Long> itr = q.list().iterator();
148
149 if (itr.hasNext()) {
150 Long count = itr.next();
151
152 if (count != null) {
153 return count.intValue();
154 }
155 }
156
157 return 0;
158 }
159 catch (Exception e) {
160 throw new SystemException(e);
161 }
162 finally {
163 HibernateUtil.closeSession(session);
164 }
165 }
166
167 public int countByKeywords(
168 long groupId, long[] categoryIds, String keywords)
169 throws SystemException {
170
171 Session session = null;
172
173 try {
174 session = HibernateUtil.openSession();
175
176 StringMaker query = new StringMaker();
177
178 query.append("SELECT COUNT(*) AS COUNT_VALUE FROM ShoppingItem ");
179 query.append("INNER JOIN ShoppingCategory ON ");
180 query.append("ShoppingCategory.categoryId = ");
181 query.append("ShoppingItem.categoryId ");
182 query.append("WHERE ");
183 query.append("ShoppingCategory.groupId = ? AND (");
184
185 if ((categoryIds != null) && (categoryIds.length > 0)) {
186 query.append("(");
187
188 for (int i = 0; i < categoryIds.length; i++) {
189 query.append("ShoppingItem.categoryId = ? ");
190
191 if (i + 1 < categoryIds.length) {
192 query.append("OR ");
193 }
194 }
195
196 query.append(") AND ");
197 }
198
199 query.append("(ShoppingItem.name LIKE ? OR ");
200 query.append("ShoppingItem.description LIKE ? OR ");
201 query.append("ShoppingItem.properties LIKE ?))");
202
203 keywords = '%' + keywords + '%';
204
205 SQLQuery q = session.createSQLQuery(query.toString());
206
207 q.addScalar(HibernateUtil.getCountColumnName(), Hibernate.LONG);
208
209 QueryPos qPos = QueryPos.getInstance(q);
210
211 qPos.add(groupId);
212
213 for (int i = 0; i < categoryIds.length; i++) {
214 qPos.add(categoryIds[i]);
215 }
216
217 qPos.add(keywords);
218 qPos.add(keywords);
219 qPos.add(keywords);
220
221 Iterator<Long> itr = q.list().iterator();
222
223 if (itr.hasNext()) {
224 Long count = itr.next();
225
226 if (count != null) {
227 return count.intValue();
228 }
229 }
230
231 return 0;
232 }
233 catch (Exception e) {
234 throw new SystemException(e);
235 }
236 finally {
237 HibernateUtil.closeSession(session);
238 }
239 }
240
241 public int countBySale(long groupId, long[] categoryIds)
242 throws SystemException {
243
244 Session session = null;
245
246 try {
247 session = HibernateUtil.openSession();
248
249 StringMaker query = new StringMaker();
250
251 query.append("SELECT COUNT(*) AS COUNT_VALUE FROM ShoppingItem ");
252 query.append("INNER JOIN ShoppingCategory ON ");
253 query.append("ShoppingCategory.categoryId = ");
254 query.append("ShoppingItem.categoryId ");
255 query.append("WHERE ");
256 query.append("ShoppingCategory.groupId = ? AND (");
257
258 if ((categoryIds != null) && (categoryIds.length > 0)) {
259 query.append("(");
260
261 for (int i = 0; i < categoryIds.length; i++) {
262 query.append("ShoppingItem.categoryId = ? ");
263
264 if (i + 1 < categoryIds.length) {
265 query.append("OR ");
266 }
267 }
268
269 query.append(") AND ");
270 }
271
272 query.append("ShoppingItem.sale = ? AND ");
273 query.append("ShoppingItem.smallImage = ?");
274
275 SQLQuery q = session.createSQLQuery(query.toString());
276
277 q.addScalar(HibernateUtil.getCountColumnName(), Hibernate.LONG);
278
279 QueryPos qPos = QueryPos.getInstance(q);
280
281 qPos.add(groupId);
282
283 for (int i = 0; i < categoryIds.length; i++) {
284 qPos.add(categoryIds[i]);
285 }
286
287 qPos.add(true);
288 qPos.add(true);
289
290 Iterator<Long> itr = q.list().iterator();
291
292 if (itr.hasNext()) {
293 Long count = itr.next();
294
295 if (count != null) {
296 return count.intValue();
297 }
298 }
299
300 return 0;
301 }
302 catch (Exception e) {
303 throw new SystemException(e);
304 }
305 finally {
306 HibernateUtil.closeSession(session);
307 }
308 }
309
310 public List<ShoppingItem> findByFeatured(
311 long groupId, long[] categoryIds, int numOfItems)
312 throws SystemException {
313
314 int countByFeatured = countByFeatured(groupId, categoryIds);
315
316 Session session = null;
317
318 try {
319 session = HibernateUtil.openSession();
320
321 StringMaker query = new StringMaker();
322
323 query.append("SELECT {ShoppingItem.*} FROM ShoppingItem ");
324 query.append("INNER JOIN ShoppingCategory ON ");
325 query.append("ShoppingCategory.categoryId = ");
326 query.append("ShoppingItem.categoryId ");
327 query.append("WHERE ");
328 query.append("ShoppingCategory.groupId = ? AND (");
329
330 if ((categoryIds != null) && (categoryIds.length > 0)) {
331 query.append("(");
332
333 for (int i = 0; i < categoryIds.length; i++) {
334 query.append("ShoppingItem.categoryId = ? ");
335
336 if (i + 1 < categoryIds.length) {
337 query.append("OR ");
338 }
339 }
340
341 query.append(") AND ");
342 }
343
344 query.append("ShoppingItem.featured = ? AND ");
345 query.append("ShoppingItem.smallImage = ?");
346
347 SQLQuery q = session.createSQLQuery(query.toString());
348
349 q.addEntity("ShoppingItem", ShoppingItemImpl.class);
350
351 QueryPos qPos = QueryPos.getInstance(q);
352
353 qPos.add(groupId);
354
355 for (int i = 0; i < categoryIds.length; i++) {
356 qPos.add(categoryIds[i]);
357 }
358
359 qPos.add(true);
360 qPos.add(true);
361
362 return (List<ShoppingItem>)QueryUtil.randomList(
363 q, HibernateUtil.getDialect(), countByFeatured, numOfItems);
364 }
365 catch (Exception e) {
366 throw new SystemException(e);
367 }
368 finally {
369 HibernateUtil.closeSession(session);
370 }
371 }
372
373 public List<ShoppingItem> findByKeywords(
374 long groupId, long[] categoryIds, String keywords, int begin,
375 int end)
376 throws SystemException {
377
378 Session session = null;
379
380 try {
381 session = HibernateUtil.openSession();
382
383 StringMaker query = new StringMaker();
384
385 query.append("SELECT {ShoppingItem.*} FROM ShoppingItem ");
386 query.append("INNER JOIN ShoppingCategory ON ");
387 query.append("ShoppingCategory.categoryId = ");
388 query.append("ShoppingItem.categoryId ");
389 query.append("WHERE ");
390 query.append("ShoppingCategory.groupId = ? AND (");
391
392 if ((categoryIds != null) && (categoryIds.length > 0)) {
393 query.append("(");
394
395 for (int i = 0; i < categoryIds.length; i++) {
396 query.append("ShoppingItem.categoryId = ? ");
397
398 if (i + 1 < categoryIds.length) {
399 query.append("OR ");
400 }
401 }
402
403 query.append(") AND ");
404 }
405
406 query.append("(ShoppingItem.name LIKE ? OR ");
407 query.append("ShoppingItem.description LIKE ? OR ");
408 query.append("ShoppingItem.properties LIKE ?))");
409
410 keywords = '%' + keywords + '%';
411
412 SQLQuery q = session.createSQLQuery(query.toString());
413
414 q.addEntity("ShoppingItem", ShoppingItemImpl.class);
415
416 QueryPos qPos = QueryPos.getInstance(q);
417
418 qPos.add(groupId);
419
420 for (int i = 0; i < categoryIds.length; i++) {
421 qPos.add(categoryIds[i]);
422 }
423
424 qPos.add(keywords);
425 qPos.add(keywords);
426 qPos.add(keywords);
427
428 return (List<ShoppingItem>)QueryUtil.list(
429 q, HibernateUtil.getDialect(), begin, end);
430 }
431 catch (Exception e) {
432 throw new SystemException(e);
433 }
434 finally {
435 HibernateUtil.closeSession(session);
436 }
437 }
438
439 public List<ShoppingItem> findBySale(
440 long groupId, long[] categoryIds, int numOfItems)
441 throws SystemException {
442
443 int countBySale = countBySale(groupId, categoryIds);
444
445 Session session = null;
446
447 try {
448 session = HibernateUtil.openSession();
449
450 StringMaker query = new StringMaker();
451
452 query.append("SELECT {ShoppingItem.*} FROM ShoppingItem ");
453 query.append("INNER JOIN ShoppingCategory ON ");
454 query.append("ShoppingCategory.categoryId = ");
455 query.append("ShoppingItem.categoryId ");
456 query.append("WHERE ");
457 query.append("ShoppingCategory.groupId = ? AND (");
458
459 if ((categoryIds != null) && (categoryIds.length > 0)) {
460 query.append("(");
461
462 for (int i = 0; i < categoryIds.length; i++) {
463 query.append("ShoppingItem.categoryId = ? ");
464
465 if (i + 1 < categoryIds.length) {
466 query.append("OR ");
467 }
468 }
469
470 query.append(") AND ");
471 }
472
473 query.append("ShoppingItem.sale = ? AND ");
474 query.append("ShoppingItem.smallImage = ?");
475
476 SQLQuery q = session.createSQLQuery(query.toString());
477
478 q.addEntity("ShoppingItem", ShoppingItemImpl.class);
479
480 QueryPos qPos = QueryPos.getInstance(q);
481
482 qPos.add(groupId);
483
484 for (int i = 0; i < categoryIds.length; i++) {
485 qPos.add(categoryIds[i]);
486 }
487
488 qPos.add(true);
489 qPos.add(true);
490
491 return (List<ShoppingItem>)QueryUtil.randomList(
492 q, HibernateUtil.getDialect(), countBySale, numOfItems);
493 }
494 catch (Exception e) {
495 throw new SystemException(e);
496 }
497 finally {
498 HibernateUtil.closeSession(session);
499 }
500 }
501
502 protected String getCategoryIds(List<Long> categoryIds) {
503 StringMaker sm = new StringMaker();
504
505 for (int i = 0; i < categoryIds.size(); i++) {
506 sm.append("categoryId = ? ");
507
508 if ((i + 1) != categoryIds.size()) {
509 sm.append("OR ");
510 }
511 }
512
513 return sm.toString();
514 }
515
516 }