Example usage for org.hibernate ScrollableResults close

List of usage examples for org.hibernate ScrollableResults close

Introduction

In this page you can find the example usage for org.hibernate ScrollableResults close.

Prototype

void close();

Source Link

Document

Release resources immediately.

Usage

From source file:org.openbravo.service.datasource.HQLDataSourceService.java

License:Open Source License

protected int getGroupedCount(Query countQuery) {
    int nRows = -1;
    ScrollableResults scrollableResults = countQuery.scroll();
    if (scrollableResults.last()) {
        nRows = scrollableResults.getRowNumber();
    }//from   w w w . j a  v a2 s.  c o  m
    scrollableResults.close();
    return nRows + 1;
}

From source file:org.openbravo.service.datasource.LinkToParentTreeDatasourceService.java

License:Open Source License

/**
 * /*from   w  ww. j a  va2s  . c om*/
 * @param parameters
 * @param parentId
 *          id of the node whose children are to be retrieved
 * @param hqlWhereClause
 *          hql where clase of the tab/selector
 * @param hqlWhereClauseRootNodes
 *          hql where clause that define what nodes are roots
 * @throws JSONException
 * @throws TooManyTreeNodesException
 *           if the number of returned nodes were to be too high
 */
@Override
protected JSONArray fetchNodeChildren(Map<String, String> parameters, Map<String, Object> datasourceParameters,
        String parentId, String hqlWhereClause, String hqlWhereClauseRootNodes)
        throws JSONException, TooManyTreeNodesException {

    boolean fetchRoot = ROOT_NODE_CLIENT.equals(parentId);
    String tabId = parameters.get("tabId");
    String treeReferenceId = parameters.get("treeReferenceId");
    Tab tab = null;
    Table table = null;
    TableTree tableTree = null;
    if (tabId != null) {
        tab = OBDal.getInstance().get(Tab.class, tabId);
        table = tab.getTable();
        tableTree = tab.getTableTree();
    } else if (treeReferenceId != null) {
        ReferencedTree treeReference = OBDal.getInstance().get(ReferencedTree.class, treeReferenceId);
        table = treeReference.getTable();
        tableTree = treeReference.getTableTreeCategory();
    } else {
        logger.error(
                "A request to the TreeDatasourceService must include the tabId or the treeReferenceId parameter");
        return new JSONArray();
    }
    Entity entity = ModelProvider.getInstance().getEntityByTableId(table.getId());
    Property linkToParentProperty = getLinkToParentProperty(tableTree);
    Property nodeIdProperty = getNodeIdProperty(tableTree);
    boolean isMultiParentTree = tableTree.isHasMultiparentNodes();

    StringBuilder whereClause = new StringBuilder();
    final List<Object> queryParameters = new ArrayList<Object>();
    whereClause.append(" as e where ");
    String actualParentId = new String(parentId);
    if (isMultiParentTree) {
        // The ids of multi parent trees are formed by the concatenation of the ids of its parents,
        // beginning with its root
        // node
        if (parentId.contains(ID_SEPARATOR)) {
            actualParentId = parentId.substring(parentId.lastIndexOf(ID_SEPARATOR) + 1);
        }
    }
    // check if we can avoid to apply the where clause when fetching the child nodes
    boolean allowNotApplyingWhereClauseToChildren = !tableTree.isApplyWhereClauseToChildNodes();
    if ((fetchRoot || !allowNotApplyingWhereClauseToChildren) && hqlWhereClause != null) {
        // Include the hql where clause for all root nodes and for child nodes only if it is required
        whereClause.append(hqlWhereClause + " and ");
    }

    if (hqlWhereClauseRootNodes != null && fetchRoot) {
        // If we are fetching the root nodes and there is a defined hqlWhereClauseRootNodes, apply it
        whereClause.append(" " + hqlWhereClauseRootNodes + " ");
    } else {
        whereClause.append(" e." + linkToParentProperty.getName());
        if (fetchRoot) {
            whereClause.append(" is null ");
        } else {
            if (!linkToParentProperty.isPrimitive()) {
                whereClause.append(".id");
            }
            whereClause.append(" = ? ");
            queryParameters.add(actualParentId);
        }
        if (tab != null && tab.getTabLevel() > 0) {
            // only try to add the parent tab criteria when the tab is not the header
            addParentTabCriteria(whereClause, tab, parameters, queryParameters);
        }
    }
    final OBQuery<BaseOBObject> query = OBDal.getInstance().createQuery(entity.getName(),
            whereClause.toString());

    query.setParameters(queryParameters);

    final DataToJsonConverter toJsonConverter = OBProvider.getInstance().get(DataToJsonConverter.class);

    JSONArray responseData = new JSONArray();

    // Check if the number of results to be returned is not higher than the defined limit
    int nResults = query.count();
    OBContext context = OBContext.getOBContext();
    int nMaxResults = -1;
    try {
        nMaxResults = Integer.parseInt(
                Preferences.getPreferenceValue("TreeDatasourceFetchLimit", false, context.getCurrentClient(),
                        context.getCurrentOrganization(), context.getUser(), context.getRole(), null));
    } catch (Exception e) {
        nMaxResults = 1000;
    }
    if (nResults > nMaxResults) {
        throw new TooManyTreeNodesException();
    }
    int count = 0;
    final ScrollableResults scrollableResults = query.scroll(ScrollMode.FORWARD_ONLY);
    try {
        while (scrollableResults.next()) {
            BaseOBObject bob = (BaseOBObject) scrollableResults.get()[0];
            final JSONObject json = toJsonConverter.toJsonObject(bob, DataResolvingMode.FULL);
            if (fetchRoot) {
                json.put("parentId", ROOT_NODE_CLIENT);
            } else {
                json.put("parentId", parentId);
            }
            Object nodeId = bob.get(nodeIdProperty.getName());
            String nodeIdStr = null;
            if (nodeId instanceof String) {
                nodeIdStr = (String) nodeId;
            } else if (nodeId instanceof BaseOBObject) {
                nodeIdStr = ((BaseOBObject) nodeId).getId().toString();
            }

            Object parentNodeId = bob.get(linkToParentProperty.getName());
            String parentNodeIdStr = null;
            if (parentNodeId instanceof String) {
                parentNodeIdStr = (String) parentNodeId;
            } else if (parentNodeId instanceof BaseOBObject) {
                parentNodeIdStr = ((BaseOBObject) parentNodeId).getId().toString();
            }

            if (isMultiParentTree) {
                json.put("nodeId", parentNodeIdStr + ID_SEPARATOR + nodeIdStr);
            } else {
                json.put("nodeId", nodeIdStr);
            }
            addNodeCommonAttributes(entity, bob, json);
            json.put("_hasChildren",
                    (this.nodeHasChildren(entity, linkToParentProperty, nodeIdProperty, bob, hqlWhereClause))
                            ? true
                            : false);
            responseData.put(json);
            count++;
            if (count % 100 == 0) {
                OBDal.getInstance().getSession().clear();
            }

        }
    } finally {
        scrollableResults.close();
    }
    return responseData;
}

From source file:org.openbravo.service.datasource.LinkToParentTreeDatasourceService.java

License:Open Source License

protected List<JSONObject> fetchParentsOfNode(Map<String, String> parameters, String parentId,
        String hqlWhereClause, String hqlWhereClauseRootNodes) throws TooManyTreeNodesException {
    List<JSONObject> parentList = new ArrayList<JSONObject>();
    String tabId = parameters.get("tabId");
    String treeReferenceId = parameters.get("treeReferenceId");
    Tab tab = null;/*from   w  ww  .  j a v a2  s.co  m*/
    Table table = null;
    TableTree tableTree = null;
    if (tabId != null) {
        tab = OBDal.getInstance().get(Tab.class, tabId);
        table = tab.getTable();
        tableTree = tab.getTableTree();
    } else if (treeReferenceId != null) {
        ReferencedTree treeReference = OBDal.getInstance().get(ReferencedTree.class, treeReferenceId);
        table = treeReference.getTable();
        tableTree = treeReference.getTableTreeCategory();
    } else {
        logger.error(
                "A request to the TreeDatasourceService must include the tabId or the treeReferenceId parameter");
        return new ArrayList<JSONObject>();
    }

    Entity entity = ModelProvider.getInstance().getEntityByTableId(table.getId());
    Property linkToParentProperty = getLinkToParentProperty(tableTree);
    Property nodeIdProperty = getNodeIdProperty(tableTree);

    StringBuilder whereClause = new StringBuilder();
    whereClause.append(" as e where ");
    whereClause.append(" e." + nodeIdProperty.getName());
    if (!nodeIdProperty.isPrimitive()) {
        whereClause.append(".id");
    }
    whereClause.append(" = ? ");

    final OBQuery<BaseOBObject> query = OBDal.getInstance().createQuery(entity.getName(),
            whereClause.toString());

    final List<Object> queryParameters = new ArrayList<Object>();
    queryParameters.add(parentId);
    query.setParameters(queryParameters);

    final DataToJsonConverter toJsonConverter = OBProvider.getInstance().get(DataToJsonConverter.class);

    // Check if the number of results to be returned is not higher than the defined limit
    int nResults = query.count();
    OBContext context = OBContext.getOBContext();
    int nMaxResults = -1;
    try {
        nMaxResults = Integer.parseInt(
                Preferences.getPreferenceValue("TreeDatasourceFetchLimit", false, context.getCurrentClient(),
                        context.getCurrentOrganization(), context.getUser(), context.getRole(), null));
    } catch (Exception e) {
        nMaxResults = 100;
    }
    if (nResults > nMaxResults) {
        throw new TooManyTreeNodesException();
    }
    int count = 0;
    final ScrollableResults scrollableResults = query.scroll(ScrollMode.FORWARD_ONLY);
    try {
        while (scrollableResults.next()) {
            BaseOBObject bob = (BaseOBObject) scrollableResults.get()[0];
            final JSONObject json = toJsonConverter.toJsonObject(bob, DataResolvingMode.FULL);

            Object nodeId = bob.get(nodeIdProperty.getName());
            String nodeIdStr = null;
            if (nodeId instanceof String) {
                nodeIdStr = (String) nodeId;
            } else if (nodeId instanceof BaseOBObject) {
                nodeIdStr = ((BaseOBObject) nodeId).getId().toString();
            }

            Object parentNodeId = bob.get(linkToParentProperty.getName());
            String parentNodeIdStr = null;
            if (parentNodeId instanceof String) {
                parentNodeIdStr = (String) parentNodeId;
            } else if (parentNodeId instanceof BaseOBObject) {
                parentNodeIdStr = ((BaseOBObject) parentNodeId).getId().toString();
            }
            try {
                json.put("nodeId", nodeIdStr);
                if (parentNodeIdStr == null) {
                    json.put("parentId", ROOT_NODE_CLIENT);
                } else {
                    json.put("parentId", parentNodeIdStr);
                }
            } catch (JSONException e) {
                logger.error("Error on tree datasource", e);
            }

            parentList.add(json);
            count++;
            if (count % 100 == 0) {
                OBDal.getInstance().getSession().clear();
            }

        }
    } finally {
        scrollableResults.close();
    }
    return parentList;
}

From source file:org.openbravo.service.json.DefaultJsonDataService.java

License:Open Source License

public void fetch(Map<String, String> parameters, QueryResultWriter writer) {
    long t = System.currentTimeMillis();

    doPreAction(parameters, "", DataSourceAction.FETCH);
    final DataEntityQueryService queryService = createSetQueryService(parameters, false);

    String selectedProperties = parameters.get(JsonConstants.SELECTEDPROPERTIES_PARAMETER);

    final DataToJsonConverter toJsonConverter = OBProvider.getInstance().get(DataToJsonConverter.class);
    toJsonConverter.setAdditionalProperties(JsonUtils.getAdditionalProperties(parameters));
    // Convert to Json only the properties specified in the request. If no properties are specified,
    // all of them will be converted to Json
    toJsonConverter.setSelectedProperties(selectedProperties);

    final ScrollableResults scrollableResults = queryService.scroll();
    try {/*from   ww  w  .  j  a va  2 s.  com*/
        int i = 0;
        while (scrollableResults.next()) {
            final Object result = scrollableResults.get()[0];
            final JSONObject json = toJsonConverter.toJsonObject((BaseOBObject) result, DataResolvingMode.FULL);

            try {
                doPostFetch(parameters, json);
            } catch (JSONException e) {
                throw new OBException(e);
            }

            writer.write(json);

            i++;
            // Clear session every 1000 records to prevent huge memory consumption in case of big loops
            if (i % 1000 == 0) {
                OBDal.getInstance().getSession().clear();
                log.debug("clearing in record " + i + " elapsed time " + (System.currentTimeMillis() - t));
            }
        }
    } finally {
        scrollableResults.close();
    }
    log.debug("Fetch took " + (System.currentTimeMillis() - t) + " ms");
}

From source file:org.openmrs.module.amrsreports.db.hibernate.MohHibernateCoreDAO.java

License:Open Source License

/**
 * post-process a list of observations from a criteria; duplicate data FTL
 *
 * @param criteria            the object with data in it
 * @param mohFetchRestriction information for limiting fetch, specifically the size
 * @return a list of processed (cleaned) observations
 *///from  w  ww.  j  av  a2s.com
private List<Obs> processObs(Criteria criteria, MohFetchRestriction mohFetchRestriction) {
    List<Obs> observations = new ArrayList<Obs>();

    // TODO: further optimization would be adding start date and end date parameter in the obs restrictions
    ScrollableResults scrollableResults = criteria.scroll(ScrollMode.FORWARD_ONLY);

    Integer size = mohFetchRestriction.getSize();

    // scroll the results
    Obs processedObs = null;
    while (scrollableResults.next() && OpenmrsUtil.compareWithNullAsGreatest(observations.size(), size) == -1) {
        Obs obs = (Obs) scrollableResults.get(0);
        // TODO: thanks to Ampath for the duplicate data, we need to sanitize the query results here
        if (processedObs != null && !obs.isObsGrouping()) {
            if (DateUtils.isSameDay(processedObs.getObsDatetime(), obs.getObsDatetime())
                    && OpenmrsUtil.nullSafeEquals(processedObs.getConcept(), obs.getConcept())
                    && OpenmrsUtil.nullSafeEquals(processedObs.getValueCoded(), obs.getValueCoded())
                    && (OpenmrsUtil.nullSafeEquals(processedObs.getValueNumeric(), obs.getValueNumeric()))) {
                continue;
            }
        }
        processedObs = obs;
        observations.add(obs);
    }
    scrollableResults.close();
    return observations;
}

From source file:org.openmrs.module.amrsreports.db.hibernate.MohHibernateCoreDAO.java

License:Open Source License

/**
 * @see MohCoreDAO#getPatientEncounters(Integer, java.util.Map, org.openmrs.module.amrsreports.util.MohFetchRestriction, java.util.Date)
 *///from  www.  j  av a2 s. c  o  m
@Override
public List<Encounter> getPatientEncounters(final Integer patientId,
        final Map<String, Collection<OpenmrsObject>> restrictions,
        final MohFetchRestriction mohFetchRestriction, final Date evaluationDate) throws DAOException {
    // create a hibernate criteria on the encounter object
    Criteria criteria = sessionFactory.getCurrentSession().createCriteria(Encounter.class);
    // restrict the encounter that will be returned to specific patient only
    criteria.add(Restrictions.eq("patientId", patientId));

    // default ordering for the returned encounter is descending on encounter datetime
    Order order = Order.desc("encounterDatetime");
    // change the default ordering if the user pass the ascending ordering in the parameters
    if (OpenmrsUtil.nullSafeEquals(MohFetchOrdering.ORDER_ASCENDING, mohFetchRestriction.getFetchOrdering())) {
        order = Order.asc("encounterDatetime");
    }
    // add the ordering object to the criteria
    criteria.addOrder(order);

    // make sure we don't go past the evaluationDate
    criteria.add(Restrictions.le("encounterDatetime", evaluationDate));

    // always get from the first result
    criteria.setFirstResult(0);
    // set the first result to a number if the user pass any value on this parameter (currently not being used)
    if (mohFetchRestriction.getStart() != null) {
        criteria.setFirstResult(mohFetchRestriction.getStart());
    }

    // specify how many records should be returned
    if (mohFetchRestriction.getSize() != null) {
        criteria.setMaxResults(mohFetchRestriction.getSize());
    }

    // create a dummy encounter object
    Encounter encounter = new Encounter();
    // iterate over each property in the restriction map
    for (String property : restrictions.keySet()) {
        // get the actual object that will restrict the encounter. this will contains the list of encounter type or list of location
        // or list of provider (currently not being used) passed from caller
        Collection<OpenmrsObject> propertyValues = restrictions.get(property);
        // check to make sure the list is not empty and the property is readable. example of the property for encounter are
        // encounterType or location of the encounter
        if (CollectionUtils.isNotEmpty(propertyValues) && PropertyUtils.isReadable(encounter, property)) {
            // create a restriction on the property with the above list as the value
            criteria.add(Restrictions.in(property, propertyValues));
            // add ordering on that property to prevent slowness when ordering only on encounter datetime (1.6.x only)
            criteria.addOrder(Order.asc(property));
        }
    }

    // exclude all voided encounters
    criteria.add(Restrictions.eq("voided", Boolean.FALSE));

    List<Encounter> encounters = new ArrayList<Encounter>();

    // scroll the results and add them to the above list of encounter
    Integer counter = 0;
    ScrollableResults scrollableResults = criteria.scroll(ScrollMode.FORWARD_ONLY);
    while (scrollableResults.next()) {
        encounters.add((Encounter) scrollableResults.get(0));
    }
    scrollableResults.close();
    return encounters;
}

From source file:org.openmrs.module.amrsreports.db.hibernate.MohHibernateCoreDAO.java

License:Open Source License

@Override
public List<Object> executeScrollingHqlQuery(String query, Map<String, Object> substitutions) {
    Query q = sessionFactory.getCurrentSession().createQuery(query);

    applySubstitutions(q, substitutions);

    // optimizations go here
    q.setFetchSize(Integer.MIN_VALUE);
    q.setReadOnly(true);//from ww w .j  a v a 2  s .  com

    List<Object> res = new ArrayList<Object>();

    long i = 0;

    ScrollableResults sr = q.scroll(ScrollMode.FORWARD_ONLY);
    while (sr.next()) {

        // rows always come back as an array
        Object[] o = sr.get();

        // but if there is only one object in the row, add it instead of the array
        if (o.length == 1)
            res.add(o[0]);
        else
            res.add(o);

        if (++i % 500 == 0)
            log.warn("processed #" + i);
    }

    log.warn("done. processed " + i + " total rows.");

    sr.close();

    return res;
}

From source file:org.openmrs.module.clinicalsummary.db.hibernate.HibernateCoreDAO.java

License:Open Source License

/**
 * @see CoreDAO#getPatientObservations(Integer, java.util.Map, org.openmrs.module.clinicalsummary.util.FetchRestriction)
 *//*from   w  ww. j ava 2 s  . c  om*/
@Override
public List<Obs> getPatientObservations(final Integer patientId,
        final Map<String, Collection<OpenmrsObject>> restrictions, final FetchRestriction fetchRestriction)
        throws DAOException {
    Criteria criteria = sessionFactory.getCurrentSession().createCriteria(Obs.class);
    criteria.createAlias("person", "person");
    criteria.add(Restrictions.eq("person.personId", patientId));

    criteria.setFirstResult(0);
    if (fetchRestriction.getStart() != null)
        criteria.setFirstResult(fetchRestriction.getStart());

    if (fetchRestriction.getSize() != null && fetchRestriction.getSize() == 1)
        criteria.setMaxResults(fetchRestriction.getSize());

    Order order = Order.desc("obsDatetime");
    if (OpenmrsUtil.nullSafeEquals(FetchOrdering.ORDER_ASCENDING, fetchRestriction.getFetchOrdering()))
        order = Order.asc("obsDatetime");

    criteria.addOrder(order);

    Obs observation = new Obs();
    for (String property : restrictions.keySet()) {
        Collection<OpenmrsObject> propertyValues = restrictions.get(property);
        if (CollectionUtils.isNotEmpty(propertyValues) && PropertyUtils.isReadable(observation, property)) {
            criteria.add(Restrictions.in(property, propertyValues));
            criteria.addOrder(Order.asc(property));
        }
    }

    criteria.add(Restrictions.eq("voided", Boolean.FALSE));

    List<Obs> observations = new ArrayList<Obs>();

    // TODO: further optimization would be adding start date and end date parameter in the obs restrictions
    ScrollableResults scrollableResults = criteria.scroll(ScrollMode.FORWARD_ONLY);

    Integer size = fetchRestriction.getSize();

    // scroll the results
    Obs processedObs = null;
    while (scrollableResults.next() && OpenmrsUtil.compareWithNullAsGreatest(observations.size(), size) == -1) {
        Obs obs = (Obs) scrollableResults.get(0);
        // TODO: thanks to Ampath for the duplicate data, we need to sanitize the query results here
        if (processedObs != null && !obs.isObsGrouping()) {
            if (DateUtils.isSameDay(processedObs.getObsDatetime(), obs.getObsDatetime())
                    && OpenmrsUtil.nullSafeEquals(processedObs.getConcept(), obs.getConcept())
                    && OpenmrsUtil.nullSafeEquals(processedObs.getValueCoded(), obs.getValueCoded())
                    && (OpenmrsUtil.nullSafeEquals(processedObs.getValueNumeric(), obs.getValueNumeric())))
                continue;
        }
        processedObs = obs;
        observations.add(obs);
    }
    scrollableResults.close();
    return observations;
}

From source file:org.openmrs.module.clinicalsummary.db.hibernate.HibernateCoreDAO.java

License:Open Source License

/**
 * @see CoreDAO#getPatientEncounters(Integer, java.util.Map, org.openmrs.module.clinicalsummary.util.FetchRestriction)
 *//*from w  w  w .j  a v  a  2 s  .c o m*/
@Override
public List<Encounter> getPatientEncounters(final Integer patientId,
        final Map<String, Collection<OpenmrsObject>> restrictions, final FetchRestriction fetchRestriction)
        throws DAOException {
    // create a hibernate criteria on the encounter object
    Criteria criteria = sessionFactory.getCurrentSession().createCriteria(Encounter.class);
    // restrict the encounter that will be returned to specific patient only
    criteria.createAlias("patient", "patient");
    criteria.add(Restrictions.eq("patient.patientId", patientId));

    // default ordering for the returned encounter is descending on encounter datetime
    Order order = Order.desc("encounterDatetime");
    // change the default ordering if the user pass the ascending ordering in the parameters
    if (OpenmrsUtil.nullSafeEquals(FetchOrdering.ORDER_ASCENDING, fetchRestriction.getFetchOrdering()))
        order = Order.asc("encounterDatetime");
    // add the ordering object to the criteria
    criteria.addOrder(order);

    // always get from the first result
    criteria.setFirstResult(0);
    // set the first result to a number if the user pass any value on this parameter (currently not being used)
    if (fetchRestriction.getStart() != null)
        criteria.setFirstResult(fetchRestriction.getStart());

    // specify how many records should be returned
    if (fetchRestriction.getSize() != null)
        criteria.setMaxResults(fetchRestriction.getSize());

    // create a dummy encounter object
    Encounter encounter = new Encounter();
    // iterate over each property in the restriction map
    for (String property : restrictions.keySet()) {
        // get the actual object that will restrict the encounter. this will contains the list of encounter type or list of location
        // or list of provider (currently not being used) passed from caller
        Collection<OpenmrsObject> propertyValues = restrictions.get(property);
        // check to make sure the list is not empty and the property is readable. example of the property for encounter are
        // encounterType or location of the encounter
        if (CollectionUtils.isNotEmpty(propertyValues) && PropertyUtils.isReadable(encounter, property)) {
            // create a restriction on the property with the above list as the value
            criteria.add(Restrictions.in(property, propertyValues));
            // add ordering on that property to prevent slowness when ordering only on encounter datetime (1.6.x only)
            criteria.addOrder(Order.asc(property));
        }
    }

    // exclude all voided encounters
    criteria.add(Restrictions.eq("voided", Boolean.FALSE));

    List<Encounter> encounters = new ArrayList<Encounter>();

    // scroll the results and add them to the above list of encounter
    Integer counter = 0;
    ScrollableResults scrollableResults = criteria.scroll(ScrollMode.FORWARD_ONLY);
    while (scrollableResults.next())
        encounters.add((Encounter) scrollableResults.get(0));
    scrollableResults.close();
    return encounters;
}

From source file:org.opentaps.common.domain.order.PurchaseOrderLookupRepository.java

License:Open Source License

/** {@inheritDoc} */
public List<OrderViewForListing> findOrders() throws RepositoryException {

    // convert fromDateStr / thruDateStr into Timestamps if the string versions were given
    if (UtilValidate.isNotEmpty(fromDateStr)) {
        fromDate = UtilDate.toTimestamp(fromDateStr, timeZone, locale);
    }//from w  ww.java  2s. co m
    if (UtilValidate.isNotEmpty(thruDateStr)) {
        thruDate = UtilDate.toTimestamp(thruDateStr, timeZone, locale);
    }
    Session session = null;
    try {
        // get a hibernate session
        session = getInfrastructure().getSession();
        Criteria criteria = session.createCriteria(OrderHeader.class);

        // always filter by the current organization
        criteria.add(Restrictions.eq(OrderHeader.Fields.billToPartyId.name(), organizationPartyId));

        // filters by order type, we only want purchase order
        criteria.add(Restrictions.eq(OrderHeader.Fields.orderTypeId.name(), OrderTypeConstants.PURCHASE_ORDER));

        // set the from/thru date filter if they were given
        if (fromDate != null) {
            criteria.add(Restrictions.ge(OrderHeader.Fields.orderDate.name(), fromDate));
        }
        if (thruDate != null) {
            criteria.add(Restrictions.le(OrderHeader.Fields.orderDate.name(), thruDate));
        }

        // filter the role assoc, there is only one supplier role per order
        Criteria roleCriteria = criteria.createAlias("orderRoles", "or");
        roleCriteria.add(Restrictions.eq("or.id." + OrderRole.Fields.roleTypeId.name(),
                RoleTypeConstants.BILL_FROM_VENDOR));

        // filter by order status
        if (findDesiredOnly) {
            List<String> statuses = UtilMisc.toList(StatusItemConstants.OrderStatus.ORDER_APPROVED,
                    StatusItemConstants.OrderStatus.ORDER_CREATED, StatusItemConstants.OrderStatus.ORDER_HOLD);
            criteria.add(Restrictions.in(OrderHeader.Fields.statusId.name(), statuses));
        }

        // filter by the given orderId string
        if (UtilValidate.isNotEmpty(orderId)) {
            criteria.add(Restrictions.ilike(OrderHeader.Fields.orderId.name(), orderId, MatchMode.START));
        }

        // filter by exact matching status, if a statusId was given
        if (UtilValidate.isNotEmpty(statusId)) {
            criteria.add(Restrictions.eq(OrderHeader.Fields.statusId.name(), statusId));
        }

        // filter by the user who created the order if given
        if (UtilValidate.isNotEmpty(createdBy)) {
            criteria.add(Restrictions.eq(OrderHeader.Fields.createdBy.name(), createdBy));
        }

        // filter by the given orderName string
        if (UtilValidate.isNotEmpty(orderName)) {
            criteria.add(Restrictions.ilike(OrderHeader.Fields.orderName.name(), orderName, MatchMode.START));
        }

        // filter by the given supplierPartyId string, from the OrderRole entity
        if (UtilValidate.isNotEmpty(supplierPartyId)) {
            roleCriteria.add(Restrictions.ilike("or.id." + OrderRole.Fields.partyId.name(), supplierPartyId,
                    MatchMode.START));
        }

        // filter by product, if given
        criteria.createAlias("orderItems", "oi");
        if (UtilValidate.isNotEmpty(productPattern)) {
            try {
                // try to get product by using productPattern as productId
                Product product = getProductRepository().getProductById(productPattern);
                criteria.add(
                        Restrictions.eq("oi." + OrderItem.Fields.productId.name(), product.getProductId()));
            } catch (EntityNotFoundException e) {
                // could not get the product by using productPattern as productId
                // find all the products that may match
                String likePattern = "%" + productPattern + "%";
                EntityCondition conditionList = EntityCondition.makeCondition(EntityOperator.OR,
                        EntityCondition.makeCondition(ProductAndGoodIdentification.Fields.productId.getName(),
                                EntityOperator.LIKE, likePattern),
                        EntityCondition.makeCondition(
                                ProductAndGoodIdentification.Fields.internalName.getName(), EntityOperator.LIKE,
                                likePattern),
                        EntityCondition.makeCondition(ProductAndGoodIdentification.Fields.productName.getName(),
                                EntityOperator.LIKE, likePattern),
                        EntityCondition.makeCondition(ProductAndGoodIdentification.Fields.comments.getName(),
                                EntityOperator.LIKE, likePattern),
                        EntityCondition.makeCondition(ProductAndGoodIdentification.Fields.description.getName(),
                                EntityOperator.LIKE, likePattern),
                        EntityCondition.makeCondition(
                                ProductAndGoodIdentification.Fields.longDescription.getName(),
                                EntityOperator.LIKE, likePattern),
                        EntityCondition.makeCondition(ProductAndGoodIdentification.Fields.idValue.getName(),
                                EntityOperator.LIKE, likePattern));
                List<ProductAndGoodIdentification> products = findList(ProductAndGoodIdentification.class,
                        conditionList);
                if (products.size() > 0) {
                    criteria.add(Restrictions.in("oi." + OrderItem.Fields.productId.name(), Entity
                            .getDistinctFieldValues(products, ProductAndGoodIdentification.Fields.productId)));
                }
            }
        }

        // specify the fields to return
        criteria.setProjection(Projections.projectionList()
                .add(Projections.distinct(Projections.property(OrderHeader.Fields.orderId.name())))
                .add(Projections.property(OrderHeader.Fields.orderName.name()))
                .add(Projections.property(OrderHeader.Fields.statusId.name()))
                .add(Projections.property(OrderHeader.Fields.grandTotal.name()))
                .add(Projections.property(OrderHeader.Fields.orderDate.name()))
                .add(Projections.property(OrderHeader.Fields.currencyUom.name()))
                .add(Projections.property("or.id." + OrderRole.Fields.partyId.name())));

        // set the order by
        if (orderBy == null) {
            orderBy = Arrays.asList(OrderHeader.Fields.orderDate.desc());
        }
        // some substitution is needed to fit the hibernate field names
        // this also maps the calculated fields and indicates the non sortable fields
        Map<String, String> subs = new HashMap<String, String>();
        subs.put("partyId", "or.id.partyId");
        subs.put("partyName", "or.id.partyId");
        subs.put("orderDateString", "orderDate");
        subs.put("orderNameId", "orderId");
        subs.put("statusDescription", "statusId");
        HibernateUtil.setCriteriaOrder(criteria, orderBy, subs);

        ScrollableResults results = null;
        List<OrderViewForListing> results2 = new ArrayList<OrderViewForListing>();
        try {
            // fetch the paginated results
            results = criteria.scroll(ScrollMode.SCROLL_INSENSITIVE);
            if (usePagination()) {
                results.setRowNumber(getPageStart());
            } else {
                results.first();
            }

            // convert them into OrderViewForListing objects which will also calculate or format some fields for display
            Object[] o = results.get();
            int n = 0; // number of results actually read
            while (o != null) {
                OrderViewForListing r = new OrderViewForListing();
                r.initRepository(this);
                int i = 0;
                r.setOrderId((String) o[i++]);
                r.setOrderName((String) o[i++]);
                r.setStatusId((String) o[i++]);
                r.setGrandTotal((BigDecimal) o[i++]);
                r.setOrderDate((Timestamp) o[i++]);
                r.setCurrencyUom((String) o[i++]);
                r.setPartyId((String) o[i++]);
                r.calculateExtraFields(getDelegator(), timeZone, locale);
                results2.add(r);
                n++;

                if (!results.next()) {
                    break;
                }
                if (usePagination() && n >= getPageSize()) {
                    break;
                }
                o = results.get();
            }
            results.last();
            // note: row number starts at 0
            setResultSize(results.getRowNumber() + 1);
        } finally {
            results.close();
        }

        return results2;

    } catch (InfrastructureException e) {
        throw new RepositoryException(e);
    } finally {
        if (session != null) {
            session.close();
        }
    }
}