List of usage examples for javax.persistence TypedQuery setFirstResult
TypedQuery<X> setFirstResult(int startPosition);
From source file:org.seedstack.showcase.infrastructure.finders.JpaCategoryfinder.java
@Override protected List<CategoryRepresentation> computeResultList(Range range, Map<String, Object> criteria) { TypedQuery<CategoryRepresentation> query = entityManager.createQuery( "select new " + CategoryRepresentation.class.getName() + " (c.categoryId, c.name,c.urlImg) from " + " Category c" + whereCategoryClause("c", criteria) + " order by c.categoryId", CategoryRepresentation.class); query.setFirstResult((int) range.getOffset()); query.setMaxResults(range.getSize()); return query.getResultList(); }
From source file:org.seedstack.showcase.infrastructure.finders.JpaProductRepresentationFinder.java
@Override protected List<ProductRepresentation> computeResultList(Range range, Map<String, Object> criteria) { TypedQuery<ProductRepresentation> query = entityManager.createQuery("select new " + ProductRepresentation.class.getName() + "(p.entityId, p.designation, p.summary, p.details, p.picture, p.price,p.categoryId,cat.name)" + " from Product p,Category cat where p.categoryId=cat.categoryId " + getWhereClauseEnd() + " order by p.categoryId, p.entityId", ProductRepresentation.class); query.setFirstResult((int) range.getOffset()); query.setMaxResults(range.getSize()); return query.getResultList(); }
From source file:org.sofun.core.kup.KupServiceImpl.java
@Override public KupSearchResults search(Map<String, String> params) throws CoreException { List<Kup> results = new ArrayList<Kup>(); long count = 0; List<KupImpl> kups = null; int offset = 0; final String offsetStr = params.get("offset"); if (offsetStr != null) { offset = Integer.valueOf(offsetStr); }//from w ww. ja v a2 s .c o m int batchSize = 10; // default batch size final String batchSizeStr = params.get("batchSize"); if (batchSizeStr != null) { batchSize = Integer.valueOf(batchSizeStr); } String queryStr = ""; TypedQuery<KupImpl> query = null; Query countQuery = null; List<Byte> kupStatus = new ArrayList<Byte>(); final String kupStatusParam = params.get("status"); if (kupStatusParam == null) { kupStatus = null; } else if (kupStatusParam.equals("ALL")) { kupStatus = null; } else if (kupStatusParam.equals("OPENED")) { kupStatus.add(Integer.valueOf(1).byteValue()); // CREATED } else if (kupStatusParam.equals("ON_GOING")) { kupStatus.add(Integer.valueOf(2).byteValue()); // ON_GOING } else if (kupStatusParam.equals("ALL_OPENED")) { kupStatus.add(Integer.valueOf(1).byteValue()); // CREATED kupStatus.add(Integer.valueOf(2).byteValue()); // ON_GOING } else if (kupStatusParam.equals("ALL_CLOSED")) { kupStatus.add(Integer.valueOf(3).byteValue()); // CLOSED kupStatus.add(Integer.valueOf(4).byteValue()); // SETTLED kupStatus.add(Integer.valueOf(5).byteValue()); // PAID OUT kupStatus.add(Integer.valueOf(4).byteValue()); // SETTLED kupStatus.add(Integer.valueOf(-1).byteValue()); // CANCELED } final String email = params.get("email"); if (email != null) { Member member = members.getMember(email); if (member != null) { String template = params.get("template"); if (template != null && template.equals("all")) { queryStr = "SELECT k FROM " + KupImpl.class.getSimpleName() + " k JOIN k.members m WHERE m.id=:member_id"; if (kupStatus != null) { queryStr += " AND k.status IN (:status)"; } queryStr += " ORDER BY k.created"; query = em.createQuery(queryStr, KupImpl.class); countQuery = em.createQuery( "SELECT count(*) " + queryStr.substring(queryStr.indexOf("FROM"), queryStr.indexOf("ORDER BY")), Long.class); query.setParameter("member_id", member.getId()); countQuery.setParameter("member_id", member.getId()); if (kupStatus != null) { query.setParameter("status", kupStatus); countQuery.setParameter("status", kupStatus); } } else { queryStr = "SELECT k FROM " + KupImpl.class.getSimpleName() + " k JOIN k.members m WHERE m.id=:member_id AND k.isTemplate=:isTemplate"; if (kupStatus != null && kupStatus.size() > 0) { queryStr += " AND k.status IN (:status)"; } queryStr += " ORDER BY k.created"; query = em.createQuery(queryStr, KupImpl.class); countQuery = em.createQuery( "SELECT count(*) " + queryStr.substring(queryStr.indexOf("FROM"), queryStr.indexOf("ORDER BY")), Long.class); query.setParameter("member_id", member.getId()); countQuery.setParameter("member_id", member.getId()); query.setParameter("isTemplate", true); countQuery.setParameter("isTemplate", true); if (kupStatus != null && kupStatus.size() > 0) { query.setParameter("status", kupStatus); countQuery.setParameter("status", kupStatus); } } // pagination query.setFirstResult(offset); query.setMaxResults(batchSize); kups = query.getResultList(); count = (Long) countQuery.getSingleResult(); if (kups != null) { results.addAll(kups); } } } else { if (kupStatusParam == null || kupStatusParam.isEmpty()) { return null; } boolean includeRoomKups = false; final String withRoomKups = params.get("withRoomKups"); if (withRoomKups != null && "1".equals(withRoomKups)) { includeRoomKups = true; } if (!includeRoomKups) { queryStr = "from " + KupImpl.class.getSimpleName() + " k where k.isTemplate=:isTemplate"; } else { queryStr = "from " + KupImpl.class.getSimpleName() + " k where k.team.privacy IN (:teamPrivacy)"; } if (kupStatus != null) { queryStr += " AND k.status IN (:status)"; } final String name = params.get("name"); if (name != null && !"".equals(name)) { queryStr += " AND k.name IN (:name)"; } boolean isTemplateParam = true; final String isTemplate = params.get("isTemplate"); if (isTemplate != null && !"".equals(isTemplate)) { if (!isTemplate.equals("1")) { isTemplateParam = false; } } final String kupStakeParam = params.get("stake"); if (kupStakeParam != null) { if ("FREE_FREEROLL".equals(kupStakeParam)) { queryStr += " AND k.stake=0"; } else if ("FREEROLL".equals(kupStakeParam)) { queryStr += " AND k.stake=0 AND k.type='GAMBLING_FR'"; } else if ("GAMBLING".equals(kupStakeParam)) { queryStr += " AND k.type='GAMBLING_FR' AND k.stake>0"; } else if ("FREE".equals(kupStakeParam)) { queryStr += " AND k.type='FREE'"; } else if ("ALL_GAMBLING".equals(kupStakeParam)) { queryStr += " AND k.type='GAMBLING_FR'"; } else if (kupStakeParam.isEmpty()) { return null; } } final String kupSportsParam = params.get("sports"); List<String> sportsNameParams = null; if (kupSportsParam != null) { if (kupSportsParam.isEmpty()) { return null; } List<String> sparams = Arrays.asList(kupSportsParam.split("#")); if (!sparams.contains("ALL")) { queryStr += " AND UPPER(k.sport.name) IN (:sports)"; sportsNameParams = sparams; } } // Remove kups where player is a participant final String removeValidatedFor = params.get("removeValidatedfor"); Member removeValidatedMember = null; if (removeValidatedFor != null && !removeValidatedFor.isEmpty()) { queryStr += " AND :member NOT MEMBER OF k.participants"; removeValidatedMember = members.getMember(removeValidatedFor); } // Do not show up with no participants if (kupStatusParam != null && "ALL_CLOSED".equals(kupStatusParam)) { queryStr += " AND k.nbParticipants > 0"; } // Sorting final String sortParams = params.get("sort"); if (sortParams != null) { if (sortParams.isEmpty()) { return null; } queryStr += " ORDER BY"; List<String> sparams = Arrays.asList(sortParams.split("#")); boolean initialized = false; if (sparams.contains("START_DATE")) { if ("ALL_CLOSED".equals(kupStatusParam)) { queryStr += " k.endDate DESC"; } else { queryStr += " k.status ASC, k.startDate ASC"; } initialized = true; } if (sparams.contains("JACKPOT")) { if (initialized) { queryStr += " ,"; } queryStr += " k.guaranteedPrice DESC"; } if (sparams.contains("PARTICIPANTS")) { if (initialized) { queryStr += " ,"; } queryStr += " k.nbParticipants DESC"; } if (sparams.contains("KUP_DURATION")) { if (initialized) { queryStr += " ,"; } queryStr += " k.duration DESC"; } } else { if (kupStatusParam != null && "ALL_CLOSED".equals(kupStatusParam)) { queryStr += " ORDER BY k.endDate DESC"; } else { queryStr += " ORDER BY k.status ASC, k.startDate ASC"; } } query = em.createQuery(queryStr, KupImpl.class); countQuery = em.createQuery("SELECT count(*) " + queryStr.substring(0, queryStr.indexOf("ORDER BY")), Long.class); if (!includeRoomKups) { query.setParameter("isTemplate", isTemplateParam); countQuery.setParameter("isTemplate", isTemplateParam); } else { String[] teamPrivacy = new String[] { TeamPrivacy.PUBLIC, TeamPrivacy.PUBLIC_GAMBLING_FR }; query.setParameter("teamPrivacy", Arrays.asList(teamPrivacy)); countQuery.setParameter("teamPrivacy", Arrays.asList(teamPrivacy)); } if (kupStatus != null) { query.setParameter("status", kupStatus); countQuery.setParameter("status", kupStatus); } if (sportsNameParams != null) { query.setParameter("sports", sportsNameParams); countQuery.setParameter("sports", sportsNameParams); } if (name != null && !"".equals(name)) { final String[] names = name.split(","); query.setParameter("name", Arrays.asList(names)); countQuery.setParameter("name", Arrays.asList(names)); } if (removeValidatedFor != null && !removeValidatedFor.isEmpty()) { query.setParameter("member", removeValidatedMember); countQuery.setParameter("member", removeValidatedMember); } // pagination query.setFirstResult(offset); query.setMaxResults(batchSize); kups = query.getResultList(); count = (Long) countQuery.getSingleResult(); if (kups != null) { results.addAll(kups); } } return new KupSearchResultsImpl(offset, batchSize, count, results); }
From source file:org.sparkcommerce.core.catalog.dao.ProductDaoImpl.java
public List<Product> readActiveProductsByCategoryInternal(Long categoryId, Date currentDate, int limit, int offset) { TypedQuery<Product> query = em.createNamedQuery("BC_READ_ACTIVE_PRODUCTS_BY_CATEGORY", Product.class); query.setParameter("categoryId", sandBoxHelper.mergeCloneIds(em, CategoryImpl.class, categoryId)); query.setParameter("currentDate", currentDate); query.setFirstResult(offset); query.setMaxResults(limit);//from ww w. jav a2s .c om query.setHint(QueryHints.HINT_CACHEABLE, true); query.setHint(QueryHints.HINT_CACHE_REGION, "query.Catalog"); return query.getResultList(); }
From source file:org.sparkcommerce.core.catalog.dao.ProductDaoImpl.java
@Override public List<Product> readProductsByCategory(Long categoryId, int limit, int offset) { TypedQuery<Product> query = em.createNamedQuery("BC_READ_PRODUCTS_BY_CATEGORY", Product.class); query.setParameter("categoryId", sandBoxHelper.mergeCloneIds(em, CategoryImpl.class, categoryId)); query.setFirstResult(offset); query.setMaxResults(limit);/*from ww w.j a v a 2 s. co m*/ query.setHint(QueryHints.HINT_CACHEABLE, true); query.setHint(QueryHints.HINT_CACHE_REGION, "query.Catalog"); return query.getResultList(); }
From source file:org.springframework.data.jpa.repository.support.SimpleJpaRepository.java
/** * Reads the given {@link TypedQuery} into a {@link Page} applying the given {@link Pageable} and * {@link Specification}.//from w ww .ja va2s . c o m * * @param query must not be {@literal null}. * @param spec can be {@literal null}. * @param pageable can be {@literal null}. * @return */ protected Page<T> readPage(TypedQuery<T> query, Pageable pageable, Specification<T> spec) { query.setFirstResult(pageable.getOffset()); query.setMaxResults(pageable.getPageSize()); Long total = QueryUtils.executeCountQuery(getCountQuery(spec)); List<T> content = total > pageable.getOffset() ? query.getResultList() : Collections.<T>emptyList(); return new PageImpl<T>(content, pageable, total); }
From source file:org.tightblog.service.WeblogEntryManager.java
/** * Get WeblogEntries by offset/length as list in reverse chronological order. * The range offset and list arguments enable paging through query results. * * @param criteria WeblogEntrySearchCriteria object listing desired search parameters * @return List of WeblogEntry objects in order specified by search criteria *///from www . j a va 2 s .com public List<WeblogEntry> getWeblogEntries(WeblogEntrySearchCriteria criteria) { QueryData qd = createEntryQueryString(criteria); TypedQuery<WeblogEntry> query = entityManager.createQuery(qd.queryString, WeblogEntry.class); for (int i = 0; i < qd.params.size(); i++) { query.setParameter(i + 1, qd.params.get(i)); } if (criteria.getOffset() != 0) { query.setFirstResult(criteria.getOffset()); } if (criteria.getMaxResults() != -1) { query.setMaxResults(criteria.getMaxResults()); } List<WeblogEntry> results = query.getResultList(); if (criteria.isCalculatePermalinks()) { results = results.stream().peek(we -> we.setPermalink(urlService.getWeblogEntryURL(we))) .collect(Collectors.toList()); } return results; }
From source file:org.tightblog.service.WeblogEntryManager.java
/** * Generic comments query method.//from w w w. j a v a 2s .co m * * @param csc CommentSearchCriteria object with fields indicating search criteria * @return list of comments fitting search criteria */ public List<WeblogEntryComment> getComments(CommentSearchCriteria csc) { QueryData cqd = createCommentQueryString(csc); TypedQuery<WeblogEntryComment> query = entityManager.createQuery(cqd.queryString, WeblogEntryComment.class); if (csc.getOffset() != 0) { query.setFirstResult(csc.getOffset()); } if (csc.getMaxResults() != -1) { query.setMaxResults(csc.getMaxResults()); } for (int i = 0; i < cqd.params.size(); i++) { query.setParameter(i + 1, cqd.params.get(i)); } return query.getResultList(); }
From source file:org.tightblog.service.WeblogManager.java
/** * Get list of WeblogEntryTagAggregate objects for the tags comprising a weblog. * * @param weblog Weblog or null to get for all weblogs. * @param sortBy Sort by either 'name' or 'count' (null for name) * @param startsWith Prefix for tags to be returned (null or a string of length > 0) * @param offset 0-based index into returns * @param limit Max objects to return (or -1 for no limit) * @return List of tags matching the criteria. */// w ww . j av a2 s.c o m public List<WeblogEntryTagAggregate> getTags(Weblog weblog, String sortBy, String startsWith, int offset, int limit) { boolean sortByName = !"count".equals(sortBy); List<Object> params = new ArrayList<>(); int size = 0; StringBuilder queryString = new StringBuilder(); queryString.append("SELECT wtag.name, COUNT(wtag), MIN(we.pubTime), MAX(we.pubTime) " + "FROM WeblogEntryTag wtag, WeblogEntry we WHERE wtag.weblogEntry.id = we.id"); if (weblog != null) { params.add(size++, weblog.getId()); queryString.append(" AND wtag.weblog.id = ?").append(size); } if (startsWith != null && startsWith.length() > 0) { params.add(size++, startsWith + '%'); queryString.append(" AND wtag.name LIKE ?").append(size); } if (sortByName) { sortBy = "wtag.name"; } else { sortBy = "COUNT(wtag) DESC"; } queryString.append(" GROUP BY wtag.name ORDER BY ").append(sortBy); TypedQuery<WeblogEntryTagAggregate> query = entityManager.createQuery(queryString.toString(), WeblogEntryTagAggregate.class); for (int i = 0; i < params.size(); i++) { query.setParameter(i + 1, params.get(i)); } if (offset != 0) { query.setFirstResult(offset); } if (limit != -1) { query.setMaxResults(limit); } List queryResults = query.getResultList(); List<WeblogEntryTagAggregate> results = new ArrayList<>(); if (queryResults != null) { for (Object obj : queryResults) { Object[] row = (Object[]) obj; WeblogEntryTagAggregate ce = new WeblogEntryTagAggregate(); ce.setName((String) row[0]); // The JPA query retrieves SUM(w.total) always as long ce.setTotal(((Long) row[1]).intValue()); if (weblog != null) { ce.setFirstEntry(((Instant) row[2]).atZone(weblog.getZoneId()).toLocalDate()); ce.setLastEntry(((Instant) row[3]).atZone(weblog.getZoneId()).toLocalDate()); } results.add(ce); } } if (sortByName) { results.sort(WeblogEntryTagAggregate.NAME_COMPARATOR); } else { results.sort(WeblogEntryTagAggregate.COUNT_COMPARATOR); } return results; }
From source file:ru.codeinside.adm.AdminServiceImpl.java
private List<Group> selectGroupsBySocial(int startIndex, int count, String[] order, boolean[] asc, AdvancedFilterableSupport newSender, boolean social) { StringBuilder q = new StringBuilder("SELECT g FROM Group g where g.social = :social"); if (newSender != null) { for (Container.Filter f : newSender.getFilters()) { String field = ((SimpleStringFilter) f).getPropertyId().toString(); q.append(" and lower(g.").append(field).append(") LIKE lower(:").append(field).append(")"); }/* w w w . j av a2 s .co m*/ } for (int i = 0; i < order.length; i++) { if (i == 0) { q.append(" order by "); } else { q.append(", "); } q.append("g.").append(order[i]).append(asc[i] ? " asc" : " desc"); } TypedQuery<Group> query = em.createQuery(q.toString(), Group.class).setParameter("social", social); if (newSender != null) { for (Container.Filter f : newSender.getFilters()) { String field = ((SimpleStringFilter) f).getPropertyId().toString(); String value = ((SimpleStringFilter) f).getFilterString(); query.setParameter(field, "%" + value + "%"); } } return query.setFirstResult(startIndex).setMaxResults(count).getResultList(); }