List of usage examples for org.hibernate ScrollableResults close
void close();
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(); } } }