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