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