Example usage for javax.persistence Query setFirstResult

List of usage examples for javax.persistence Query setFirstResult

  1. HOME
  2. Java
  3. javax
  4. javax.persistence.*
  5. Query
  6. setFirstResult

Introduction

In this page you can find the example usage for javax.persistence Query setFirstResult.

Prototype

Query setFirstResult(int startPosition);

Source Link

Document

Set the position of the first result to retrieve.

Usage

From source file:org.apache.roller.weblogger.business.jpa.JPAUserManagerImpl.java

public List getUsers(Weblog weblog, Boolean enabled, Date startDate, Date endDate, int offset, int length)
        throws WebloggerException {
    Query query = null;
    List results = null;/*w  w w  .  j a  v a 2s .  c  o  m*/

    // if we are doing date range then we must have an end date
    if (startDate != null && endDate == null) {
        endDate = new Date();
    }

    List params = new ArrayList();
    int size = 0;
    StringBuffer queryString = new StringBuffer();
    StringBuffer whereClause = new StringBuffer();

    if (weblog != null) {
        queryString.append("SELECT u FROM User u JOIN u.permissions p ");
        params.add(size++, weblog);
        whereClause.append(" WHERE p.website = ?" + size);
    } else {
        queryString.append("SELECT u FROM User u ");
    }

    if (enabled != null) {
        if (whereClause.length() == 0) {
            whereClause.append(" WHERE ");
        } else {
            whereClause.append(" AND ");
        }
        params.add(size++, enabled);
        whereClause.append("u.enabled = ?" + size);
    }

    if (startDate != null) {
        if (whereClause.length() == 0) {
            whereClause.append(" WHERE ");
        } else {
            whereClause.append(" AND ");
        }

        // if we are doing date range then we must have an end date
        if (endDate == null) {
            endDate = new Date();
        }
        Timestamp start = new Timestamp(startDate.getTime());
        Timestamp end = new Timestamp(endDate.getTime());
        params.add(size++, start);
        whereClause.append("u.dateCreated > ?" + size);
        params.add(size++, end);
        whereClause.append(" AND u.dateCreated < ?" + size);
    }
    whereClause.append(" ORDER BY u.dateCreated DESC");
    query = strategy.getDynamicQuery(queryString.toString() + whereClause.toString());

    if (offset != 0) {
        query.setFirstResult(offset);
    }
    if (length != -1) {
        query.setMaxResults(length);
    }
    for (int i = 0; i < params.size(); i++) {
        query.setParameter(i + 1, params.get(i));
    }
    return query.getResultList();
}

From source file:org.apache.roller.weblogger.business.jpa.JPAWeblogEntryManagerImpl.java

/**
 * @inheritDoc//from w ww .j a v a  2 s.co  m
 */
public List getPopularTags(Weblog website, Date startDate, int offset, int limit) throws WebloggerException {
    Query query = null;
    List queryResults = null;

    if (website != null) {
        if (startDate != null) {
            Timestamp start = new Timestamp(startDate.getTime());
            query = strategy.getNamedQuery("WeblogEntryTagAggregate.getPopularTagsByWebsite&StartDate");
            query.setParameter(1, website);
            query.setParameter(2, start);
        } else {
            query = strategy.getNamedQuery("WeblogEntryTagAggregate.getPopularTagsByWebsite");
            query.setParameter(1, website);
        }
    } else {
        if (startDate != null) {
            Timestamp start = new Timestamp(startDate.getTime());
            query = strategy.getNamedQuery("WeblogEntryTagAggregate.getPopularTagsByWebsiteNull&StartDate");
            query.setParameter(1, start);
        } else {
            query = strategy.getNamedQuery("WeblogEntryTagAggregate.getPopularTagsByWebsiteNull");
        }
    }
    if (offset != 0) {
        query.setFirstResult(offset);
    }
    if (limit != -1) {
        query.setMaxResults(limit);
    }
    queryResults = query.getResultList();

    double min = Integer.MAX_VALUE;
    double max = Integer.MIN_VALUE;

    List results = new ArrayList(limit);

    for (Iterator iter = queryResults.iterator(); iter.hasNext();) {
        Object[] row = (Object[]) iter.next();
        TagStat t = new TagStat();
        t.setName((String) row[0]);
        t.setCount(((Number) row[1]).intValue());

        min = Math.min(min, t.getCount());
        max = Math.max(max, t.getCount());
        results.add(t);
    }

    min = Math.log(1 + min);
    max = Math.log(1 + max);

    double range = Math.max(.01, max - min) * 1.0001;

    for (Iterator iter = results.iterator(); iter.hasNext();) {
        TagStat t = (TagStat) iter.next();
        t.setIntensity((int) (1 + Math.floor(5 * (Math.log(1 + t.getCount()) - min) / range)));
    }

    // sort results by name, because query had to sort by total
    Collections.sort(results, tagStatNameComparator);

    return results;
}

From source file:gov.medicaid.services.impl.ProviderEnrollmentServiceBean.java

/**
 * This method gets all the providers that meet the search criteria. If none available, the search result will be
 * empty./*from   w  w  w  . jav  a  2 s . c  om*/
 * 
 * @param user the user performing the action
 * @param criteria - the search criteria
 * @return - the applicable providers
 * 
 * @throws IllegalArgumentException if any argument is null, or the page size and page number settings are invalid
 * @throws PortalServiceException for any errors encountered
 */
@SuppressWarnings("unchecked")
public SearchResult<UserRequest> searchTickets(CMSUser user, ProviderSearchCriteria criteria)
        throws PortalServiceException {
    if (criteria == null) {
        throw new IllegalArgumentException("Criteria cannot be null.");
    }
    if (user == null || user.getRole() == null) {
        throw new IllegalArgumentException("User and the corresponding role cannot be null.");
    }
    if (Util.isNotBlank(criteria.getEnrollmentNumber()) && !Util.isDigits(criteria.getEnrollmentNumber())) {
        throw new IllegalArgumentException("Invalid number format for enrollment number.");
    }

    checkSortColumn(criteria, TICKET_COL_CNT);

    SearchResult<UserRequest> results = new SearchResult<UserRequest>();
    results.setPageNumber(criteria.getPageNumber());
    results.setPageSize(criteria.getPageSize());

    String fromClause = "FROM ProviderProfile p LEFT JOIN p.riskLevel rl, Enrollment t LEFT JOIN t.requestType rt "
            + "LEFT JOIN t.status ts, Entity e LEFT JOIN e.providerType pt WHERE p.ticketId = t.ticketId "
            + "AND e.ticketId = p.ticketId AND p.profileId = e.profileId AND p.ticketId > 0";

    StringBuilder countQuery = new StringBuilder("SELECT count(*) " + fromClause);
    appendCriteria(countQuery, user, criteria);

    Query count = getEm().createQuery(countQuery.toString());
    bindParameters(count, user, criteria);
    results.setTotal(((Number) count.getSingleResult()).intValue());

    StringBuilder fetchQuery = new StringBuilder(
            "SELECT NEW gov.medicaid.entities.UserRequest(t.ticketId, e.npi, t.submissionDate, "
                    + "rt.description, ts.description, t.statusDate, rl.description, pt.description, "
                    + "e.name, t.createdOn, rl.sortIndex, t.processInstanceId, t.profileReferenceId) "
                    + fromClause);

    appendCriteria(fetchQuery, user, criteria);
    appendSorting(fetchQuery, criteria);

    Query items = getEm().createQuery(fetchQuery.toString());
    bindParameters(items, user, criteria);
    if (criteria.getPageSize() > 0) {
        int offset = (criteria.getPageNumber() - 1) * criteria.getPageSize();
        items.setFirstResult(offset);
        items.setMaxResults(criteria.getPageSize());
    }

    results.setItems(items.getResultList());
    return results;
}

From source file:gov.medicaid.services.impl.ProviderEnrollmentServiceBean.java

/**
 * Search for practice./*from   w  ww .  ja  v  a  2  s. c o  m*/
 * 
 * @param user the user performing the search
 * @param criteria the criteria filter
 * @return the matching practice results
 * @throws PortalServiceException for any errors encountered
 */
@SuppressWarnings("unchecked")
public SearchResult<PracticeLookup> searchPractice(CMSUser user, PracticeSearchCriteria criteria)
        throws PortalServiceException {
    if (criteria == null) {
        throw new IllegalArgumentException("Criteria cannot be null.");
    }
    if (user == null || user.getRole() == null) {
        throw new IllegalArgumentException("User and the corresponding role cannot be null.");
    }
    if (criteria.getPageNumber() < 0 || (criteria.getPageNumber() > 0 && criteria.getPageSize() <= 0)) {
        throw new IllegalArgumentException("Invalid page number/page size combination.");
    }

    checkSortColumn(criteria, PRACTICE_COL_CNT);

    SearchResult<PracticeLookup> results = new SearchResult<PracticeLookup>();
    results.setPageNumber(criteria.getPageNumber());
    results.setPageSize(criteria.getPageSize());

    String fromClause = "FROM Organization e LEFT JOIN e.contactInformation ci LEFT JOIN ci.address a "
            + "WHERE e.enrolled = 'Y'";

    StringBuilder countQuery = new StringBuilder("SELECT count(*) " + fromClause);
    appendCriteria(countQuery, user, criteria);

    Query count = getEm().createQuery(countQuery.toString());
    bindParameters(count, user, criteria);
    results.setTotal(((Number) count.getSingleResult()).intValue());

    StringBuilder fetchQuery = new StringBuilder(
            "SELECT NEW gov.medicaid.entities.PracticeLookup(e.profileId, e.npi, e.name, "
                    + "ci.address.line1, ci.address.line2, ci.address.city, ci.address.state, "
                    + "ci.address.zipcode, ci.address.county, ci.phoneNumber, ci.faxNumber, "
                    + "e.backgroundStudyId, e.backgroundClearanceDate, e.agencyId) " + fromClause);

    appendCriteria(fetchQuery, user, criteria);
    appendSorting(fetchQuery, criteria);

    Query items = getEm().createQuery(fetchQuery.toString());
    bindParameters(items, user, criteria);
    if (criteria.getPageNumber() > 0) {
        int offset = (criteria.getPageNumber() - 1) * criteria.getPageSize();
        items.setFirstResult(offset);
        items.setMaxResults(criteria.getPageSize());
    }

    results.setItems(items.getResultList());

    if (criteria.isAgency()) {
        populateContact(items.getResultList());
    }
    return results;
}

From source file:org.opencms.db.jpa.CmsUserDriver.java

/**
 * @see org.opencms.db.I_CmsUserDriver#searchUsers(org.opencms.db.CmsDbContext, org.opencms.file.CmsUserSearchParameters)
 *///from w  w  w. ja  va 2 s  .c  o m
public List<CmsUser> searchUsers(CmsDbContext dbc, CmsUserSearchParameters searchParams)
        throws CmsDataAccessException {

    List<CmsUser> users = new ArrayList<CmsUser>();
    try {
        CmsUserQueryBuilder builder = new CmsJpaUserQueryBuilder();
        CmsPair<String, List<Object>> queryData = builder.createUserQuery(searchParams, false);
        Query q = m_sqlManager.createQueryWithParametersFromJPQL(dbc, queryData.getFirst(),
                queryData.getSecond());

        // paging is done here, not in the query string 
        q.setMaxResults(searchParams.getPageSize());
        q.setFirstResult(searchParams.getPageSize() * (searchParams.getPage() - 1));

        List<?> res = q.getResultList();
        // create new Cms group objects
        for (Object singleRes : res) {
            CmsDAOUsers daoUser = null;
            if (singleRes instanceof Object[]) {
                Object[] resArray = (Object[]) singleRes;
                daoUser = (CmsDAOUsers) resArray[0];
            } else {
                daoUser = (CmsDAOUsers) singleRes;
            }
            users.add(internalCreateUser(dbc, daoUser));
        }
    } catch (PersistenceException e) {
        throw new CmsDataAccessException(Messages.get().container(Messages.ERR_JPA_PERSITENCE, e), e);
    }

    return users;
}

From source file:org.apache.roller.weblogger.business.jpa.JPAWeblogEntryManagerImpl.java

/**
 * @inheritDoc/*from w w  w.  j  a  v a2 s  .  c  o m*/
 */
public List getTags(Weblog website, String sortBy, String startsWith, int offset, int limit)
        throws WebloggerException {
    Query query = null;
    List queryResults = null;
    boolean sortByName = sortBy == null || !sortBy.equals("count");

    List params = new ArrayList();
    int size = 0;
    StringBuffer queryString = new StringBuffer();
    queryString.append("SELECT w.name, SUM(w.total) FROM WeblogEntryTagAggregate w WHERE ");

    if (website != null) {
        params.add(size++, website.getId());
        queryString.append(" w.weblog.id = ?").append(size);
    } else {
        queryString.append(" w.weblog IS NULL");
    }

    if (startsWith != null && startsWith.length() > 0) {
        params.add(size++, startsWith + '%');
        queryString.append(" AND w.name LIKE ?" + size);
    }

    if (sortBy != null && sortBy.equals("count")) {
        sortBy = "w.total DESC";
    } else {
        sortBy = "w.name";
    }
    queryString.append(" GROUP BY w.name, w.total ORDER BY " + sortBy);

    query = strategy.getDynamicQuery(queryString.toString());
    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);
    }
    queryResults = query.getResultList();

    List results = new ArrayList();
    for (Iterator iter = queryResults.iterator(); iter.hasNext();) {
        Object[] row = (Object[]) iter.next();
        TagStat ce = new TagStat();
        ce.setName((String) row[0]);
        // The JPA query retrieves SUM(w.total) always as long
        ce.setCount(((Long) row[1]).intValue());
        results.add(ce);
    }

    if (sortByName) {
        Collections.sort(results, tagStatNameComparator);
    } else {
        Collections.sort(results, tagStatCountReverseComparator);
    }

    return results;
}

From source file:com.tea.landlordapp.repository.mysql.ApplicationDaoImpl.java

@SuppressWarnings("unchecked")
private List<Application> findApplications(String startSql, Integer id, String condition,
        List<SearchTerm> searchTerms, Integer pageNo, Integer sortBy, String sortType, User user) {
    if (StringUtils.isBlank(condition)) {
        condition = "and";
    }//w  w  w . ja  v  a  2 s .  co m

    // String addlQuery = " ";
    final StringBuilder build = new StringBuilder(" ");
    if (!ObjectUtils.equals(searchTerms, null) && searchTerms.size() > 0) {
        try {
            int countSearchTerm = 0;
            for (final SearchTerm s : searchTerms) {
                if (++countSearchTerm == 1) {
                    build.append("AND");
                } else {
                    build.append(condition);
                }
                if (s.getCriteria().contains("].lastName")) {
                    final String encLN = s.getValueAsString();
                    build.append(
                            " a.id in (select ap.application.id from Applicant ap where upper(ap.lastName) "
                                    + s.getModifier() + " " + encLN + ") ");
                } else if (s.getCriteria().contains("Date")) {
                    build.append(" " + "date_format(" + s.getCriteria() + ", '%m/%d/%Y') " + s.getModifier()
                            + " " + s.getValueAsString() + " ");
                } else {
                    build.append(
                            " " + s.getCriteria() + " " + s.getModifier() + " " + s.getValueAsString() + " ");
                }
            }
        } catch (final Exception ex) {
            logger.error("Unable to create additional query...");
            build.append("");
        }
    }

    // Append Sort By
    if (sortBy == 3 || sortBy == 4 || sortBy == 5) {
        // sort by firstName, lastName, ssn
        // Note: order by a.applications[0].firstName is not supported.
        build.append(" AND app.application.id = a.id AND app.applicantType like 'Primary' order by "
                + getViewApplicationsSortOptions().get(sortBy) + " " + sortType);
    } else {
        build.append(" order by " + getViewApplicationsSortOptions().get(sortBy) + " " + sortType);
    }
    final String addlQuery = build.toString();
    logger.debug("findApplications query is.. " + startSql + addlQuery);
    final Query query = em.createQuery(startSql + addlQuery);
    query.setFirstResult(pageNo * 10);
    query.setMaxResults(10);
    query.setParameter("id", id);
    if (user != null)
        query.setParameter("user", user);

    return query.getResultList();
}

From source file:it.drwolf.ridire.session.JobManager.java

@Factory("jobValidationResources")
public List<CrawledResource> getJobValidationResources() {
    String queryString = "select cr from CrawledResource cr left join cr.semanticMetadatum left join cr.functionalMetadatum where cr.validation <> :v and cr.job=:job";
    String queryCountString = "select count(*) from CrawledResource cr where cr.validation <> :v and cr.job=:job";
    String queryTotalCountString = queryCountString;
    if (this.getFilterURLValue() != null && this.getFilterURLValue().length() > 0) {
        queryString += " and cr.url like :url";
        queryCountString += " and cr.url like :url";
    }//from w w w. ja  v  a 2  s  .  co  m
    if (this.getFilterMimeTypeValue() != null && this.getFilterMimeTypeValue().length() > 0) {
        queryString += " and cr.contentType like :mimeType";
        queryCountString += " and cr.contentType like :mimeType";
    }
    if (this.isFilterValidated()) {
        queryString += " and cr.validation <> " + CrawledResource.VALIDATED + " ";
        queryCountString += " and cr.validation <> " + CrawledResource.VALIDATED + " ";
    }
    if (this.sortField != null && this.sortField.length() > 0) {
        queryString += " order by cr." + this.sortField;
        if (this.sortOrder != null) {
            if (this.sortOrder.equals("down")) {
                queryString += " desc ";
            }
        }
    }
    Query query = this.entityManager.createQuery(queryString).setParameter("job", this.job).setParameter("v",
            CrawledResource.NOT_CHOOSEN_FOR_VALIDATION);
    Query queryCount = this.entityManager.createQuery(queryCountString).setParameter("job", this.job)
            .setParameter("v", CrawledResource.NOT_CHOOSEN_FOR_VALIDATION);
    Query queryTotalCount = this.entityManager.createQuery(queryTotalCountString).setParameter("job", this.job)
            .setParameter("v", CrawledResource.NOT_CHOOSEN_FOR_VALIDATION);
    if (this.getFilterURLValue() != null && this.getFilterURLValue().length() > 0) {
        query.setParameter("url", "%" + this.getFilterURLValue() + "%");
        queryCount.setParameter("url", "%" + this.getFilterURLValue() + "%");
    }
    if (this.getFilterMimeTypeValue() != null && this.getFilterMimeTypeValue().length() > 0) {
        query.setParameter("mimeType", "%" + this.getFilterMimeTypeValue() + "%");
        queryCount.setParameter("mimeType", "%" + this.getFilterMimeTypeValue() + "%");
    }
    if (this.firstResult == null) {
        this.firstResult = 0;
    }
    // System.out.println("*****" + this.pageSize);
    this.results = query.setFirstResult(this.firstResult).setMaxResults(this.pageSize).getResultList();
    this.setTotalSelectedResourcesNumber((Long) queryTotalCount.getSingleResult());
    if (this.results != null) {
        this.setTotalResults((Long) queryCount.getSingleResult());
        if (this.getFirstResult() > this.getTotalResults()) {
            this.setFirstResult(0);

        }
    }
    this.entityManager.clear();
    return this.results;
}

From source file:org.apache.ranger.biz.UserMgr.java

/**
 * @param searchCriteria// ww w.  ja  v a  2 s .com
 * @return
 */
public VXPortalUserList searchUsers(SearchCriteria searchCriteria) {

    VXPortalUserList returnList = new VXPortalUserList();
    ArrayList<VXPortalUser> objectList = new ArrayList<VXPortalUser>();
    String queryStr = "SELECT u FROM  XXPortalUser u ";
    String countQueryStr = "SELECT COUNT(u) FROM XXPortalUser u ";

    // Get total count first
    Query query = createUserSearchQuery(countQueryStr, null, searchCriteria);
    Long count = (Long) query.getSingleResult();
    int resultSize = count != null ? count.intValue() : 0;
    if (resultSize == 0) {
        return returnList;
    }

    // Get actual data

    // Add sort by
    String sortBy = searchCriteria.getSortBy();
    String querySortBy = "u.loginId";
    if (!stringUtil.isEmpty(sortBy)) {
        sortBy = sortBy.trim();
        if (sortBy.equalsIgnoreCase("userId")) {
            querySortBy = "u.id";
        } else if (sortBy.equalsIgnoreCase("loginId")) {
            querySortBy = "ua.loginId";
        } else if (sortBy.equalsIgnoreCase("emailAddress")) {
            querySortBy = "u.emailAddress";
        } else if (sortBy.equalsIgnoreCase("firstName")) {
            querySortBy = "u.firstName";
        } else if (sortBy.equalsIgnoreCase("lastName")) {
            querySortBy = "u.lastName";
        } else {
            sortBy = "loginId";
            logger.error("Invalid sortBy provided. sortBy=" + sortBy);
        }
    } else {
        sortBy = "loginId";
    }

    // Default sort field
    String sortClause = " order by " + querySortBy + " ";

    // Add sort type
    String sortType = searchCriteria.getSortType();
    String querySortType = "asc";
    if (sortType != null) {
        if (sortType.equalsIgnoreCase("asc") || sortType.equalsIgnoreCase("desc")) {
            querySortType = sortType;
        } else {
            logger.error("Invalid sortType. sortType=" + sortType);
        }
    }
    sortClause += querySortType;

    query = createUserSearchQuery(queryStr, sortClause, searchCriteria);

    // Set start index
    query.setFirstResult(searchCriteria.getStartIndex());

    searchUtil.updateQueryPageSize(query, searchCriteria);

    @SuppressWarnings("rawtypes")
    List resultList = query.getResultList();
    // Iterate over the result list and create the return list
    for (Object object : resultList) {
        XXPortalUser gjUser = (XXPortalUser) object;
        VXPortalUser userProfile = new VXPortalUser();
        gjUserToUserProfile(gjUser, userProfile);
        objectList.add(userProfile);
    }

    returnList.setResultSize(resultSize);
    returnList.setPageSize(query.getMaxResults());
    returnList.setSortBy(sortBy);
    returnList.setSortType(querySortType);
    returnList.setStartIndex(query.getFirstResult());
    returnList.setTotalCount(count.longValue());
    returnList.setVXPortalUsers(objectList);
    return returnList;
}

From source file:org.apache.roller.weblogger.business.jpa.JPAWeblogEntryManagerImpl.java

/**
 * @inheritDoc/*from  ww  w .java 2s . c om*/
 */
public List getMostCommentedWeblogEntries(Weblog website, Date startDate, Date endDate, int offset, int length)
        throws WebloggerException {
    Query query = null;
    List queryResults = null;
    if (endDate == null)
        endDate = new Date();

    if (website != null) {
        if (startDate != null) {
            Timestamp start = new Timestamp(startDate.getTime());
            Timestamp end = new Timestamp(endDate.getTime());
            query = strategy
                    .getNamedQuery("WeblogEntryComment.getMostCommentedWeblogEntryByWebsite&EndDate&StartDate");
            query.setParameter(1, website);
            query.setParameter(2, end);
            query.setParameter(3, start);
        } else {
            Timestamp end = new Timestamp(endDate.getTime());
            query = strategy.getNamedQuery("WeblogEntryComment.getMostCommentedWeblogEntryByWebsite&EndDate");
            query.setParameter(1, website);
            query.setParameter(2, end);
        }
    } else {
        if (startDate != null) {
            Timestamp start = new Timestamp(startDate.getTime());
            Timestamp end = new Timestamp(endDate.getTime());
            query = strategy.getNamedQuery("WeblogEntryComment.getMostCommentedWeblogEntryByEndDate&StartDate");
            query.setParameter(1, end);
            query.setParameter(2, start);
        } else {
            Timestamp end = new Timestamp(endDate.getTime());
            query = strategy.getNamedQuery("WeblogEntryComment.getMostCommentedWeblogEntryByEndDate");
            query.setParameter(1, end);
        }
    }
    if (offset != 0) {
        query.setFirstResult(offset);
    }
    if (length != -1) {
        query.setMaxResults(length);
    }
    queryResults = query.getResultList();
    List results = new ArrayList();
    for (Iterator iter = queryResults.iterator(); iter.hasNext();) {
        Object[] row = (Object[]) iter.next();
        StatCount sc = new StatCount((String) row[1], // weblog handle
                (String) row[2], // entry anchor
                (String) row[3], // entry title
                "statCount.weblogEntryCommentCountType", // stat desc
                ((Long) row[0]).longValue()); // count
        sc.setWeblogHandle((String) row[1]);
        results.add(sc);
    }
    // Original query ordered by desc count.
    // JPA QL doesn't allow queries to be ordered by agregates; do it in memory
    Collections.sort(results, statCountCountReverseComparator);

    return results;
}