Examples with EntityFindOptions org.apache.ofbiz.entity.util.EntityFindOptions used on opensource projects

Example 1 with EntityFindOptions

use of org.apache.ofbiz.entity.util.EntityFindOptions in project ofbiz-framework by apache.

the class GenericDAO method selectCountByCondition.

public long selectCountByCondition(Delegator delegator, ModelEntity modelEntity, EntityCondition whereEntityCondition, EntityCondition havingEntityCondition, List<ModelField> selectFields, EntityFindOptions findOptions) throws GenericEntityException {
    if (modelEntity == null) {
        return 0;
    }
    // if no find options passed, use default
    if (findOptions == null) {
        findOptions = new EntityFindOptions();
    }
    boolean verboseOn = Debug.verboseOn();
    if (verboseOn) {
        // put this inside an if statement so that we don't have to generate the string when not used...
        if (Debug.verboseOn())
            Debug.logVerbose("Doing selectListIteratorByCondition with whereEntityCondition: " + whereEntityCondition, module);
    }
    boolean isGroupBy = false;
    ModelViewEntity modelViewEntity = null;
    if (modelEntity instanceof ModelViewEntity) {
        modelViewEntity = (ModelViewEntity) modelEntity;
        isGroupBy = modelViewEntity.getGroupBysSize() > 0;
    }
    // To get a count of the rows that will be returned when there is a GROUP BY, must do something like:
    // SELECT COUNT(1) FROM (SELECT COUNT(1) FROM OFBIZ.POSTAL_ADDRESS PA GROUP BY PA.CITY) TEMP_NAME
    // instead of a simple:
    // SELECT COUNT(1) FROM OFBIZ.POSTAL_ADDRESS PA GROUP BY PA.CITY
    StringBuilder sqlBuffer = new StringBuilder("SELECT ");
    if (isGroupBy) {
        sqlBuffer.append("COUNT(1) FROM (SELECT ");
    }
    if (findOptions.getDistinct()) {
        /* DEJ20100304: the code below was causing problems so the line above may be used instead, but hopefully this is fixed now 
             * may need varying SQL for different databases, and also in view-entities in some cases it seems to 
             * cause the "COUNT(DISTINCT " to appear twice, causing an attempt to try to count a count (function="count-distinct", distinct=true in find options)
             */
        if (selectFields != null && selectFields.size() > 0) {
            ModelField firstSelectField = selectFields.get(0);
            ModelViewEntity.ModelAlias firstModelAlias = modelViewEntity != null ? modelViewEntity.getAlias(firstSelectField.getName()) : null;
            if (firstModelAlias != null && UtilValidate.isNotEmpty(firstModelAlias.getFunction())) {
                // if the field has a function already we don't want to count just it, would be meaningless
                sqlBuffer.append("COUNT(DISTINCT *) ");
            } else {
                sqlBuffer.append("COUNT(DISTINCT ");
                // this only seems to support a single column, which is not desirable but seems a lot better than no columns or in certain cases all columns
                sqlBuffer.append(firstSelectField.getColValue());
                // sqlBuffer.append(modelEntity.colNameString(selectFields, ", ", "", datasource.aliasViews));
                sqlBuffer.append(")");
            }
        } else {
            sqlBuffer.append("COUNT(DISTINCT *) ");
        }
    } else {
        // NOTE DEJ20080701 Changed from COUNT(*) to COUNT(1) to improve performance, and should get the same results at least when there is no DISTINCT
        sqlBuffer.append("COUNT(1) ");
    }
    // populate the info from entity-condition in the view-entity, if it is one and there is one
    List<EntityCondition> viewWhereConditions = null;
    List<EntityCondition> viewHavingConditions = null;
    List<String> viewOrderByList = null;
    if (modelViewEntity != null) {
        viewWhereConditions = new LinkedList<EntityCondition>();
        viewHavingConditions = new LinkedList<EntityCondition>();
        viewOrderByList = new LinkedList<String>();
        modelViewEntity.populateViewEntityConditionInformation(modelFieldTypeReader, viewWhereConditions, viewHavingConditions, viewOrderByList, null);
    }
    // FROM clause and when necessary the JOIN or LEFT JOIN clause(s) as well
    sqlBuffer.append(SqlJdbcUtil.makeFromClause(modelEntity, modelFieldTypeReader, datasource));
    // WHERE clause
    List<EntityConditionParam> whereEntityConditionParams = new LinkedList<EntityConditionParam>();
    makeConditionWhereString(sqlBuffer, " WHERE ", modelEntity, whereEntityCondition, viewWhereConditions, whereEntityConditionParams);
    // GROUP BY clause for view-entity
    if (isGroupBy) {
        modelViewEntity.colNameString(modelViewEntity.getGroupBysCopy(), sqlBuffer, " GROUP BY ", ", ", "", false);
    }
    // HAVING clause
    List<EntityConditionParam> havingEntityConditionParams = new LinkedList<EntityConditionParam>();
    makeConditionHavingString(sqlBuffer, " HAVING ", modelEntity, havingEntityCondition, viewHavingConditions, havingEntityConditionParams);
    if (isGroupBy) {
        sqlBuffer.append(") TEMP_NAME");
    }
    String sql = sqlBuffer.toString();
    if (Debug.verboseOn())
        Debug.logVerbose("Count select sql: " + sql, module);
    try (SQLProcessor sqlP = new SQLProcessor(delegator, helperInfo)) {
        sqlP.prepareStatement(sql, findOptions.getSpecifyTypeAndConcur(), findOptions.getResultSetType(), findOptions.getResultSetConcurrency(), findOptions.getFetchSize(), findOptions.getMaxRows());
        if (verboseOn) {
            // put this inside an if statement so that we don't have to generate the string when not used...
            if (Debug.verboseOn())
                Debug.logVerbose("Setting the whereEntityConditionParams: " + whereEntityConditionParams, module);
        }
        // set all of the values from the Where EntityCondition
        for (EntityConditionParam whereEntityConditionParam : whereEntityConditionParams) {
            SqlJdbcUtil.setValue(sqlP, whereEntityConditionParam.getModelField(), modelEntity.getEntityName(), whereEntityConditionParam.getFieldValue(), modelFieldTypeReader);
        }
        if (verboseOn) {
            // put this inside an if statement so that we don't have to generate the string when not used...
            if (Debug.verboseOn())
                Debug.logVerbose("Setting the havingEntityConditionParams: " + havingEntityConditionParams, module);
        }
        // set all of the values from the Having EntityCondition
        for (EntityConditionParam havingEntityConditionParam : havingEntityConditionParams) {
            SqlJdbcUtil.setValue(sqlP, havingEntityConditionParam.getModelField(), modelEntity.getEntityName(), havingEntityConditionParam.getFieldValue(), modelFieldTypeReader);
        }
        try {
            sqlP.executeQuery();
            long count = 0;
            ResultSet resultSet = sqlP.getResultSet();
            if (resultSet.next()) {
                count = resultSet.getLong(1);
            }
            return count;
        } catch (SQLException e) {
            throw new GenericDataSourceException("Error getting count value", e);
        }
    }
}

Example 2 with EntityFindOptions

use of org.apache.ofbiz.entity.util.EntityFindOptions in project ofbiz-framework by apache.

the class EntityQueryTestSuite method testCursorScrollInSensitive.

/*
     * cursorScrollInSensitive(): ResultSet object's cursor is scrollable but generally not sensitive to changes to the data that underlies the ResultSet.
     * assert: Compared first record found by both the iterators.
     */
public void testCursorScrollInSensitive() throws GenericEntityException {
    List<GenericValue> testingTypes = new LinkedList<>();
    testingTypes.add(delegator.makeValue("TestingType", "testingTypeId", "scrollInSensitive-1", "description", "cursorScrollInSensitive One"));
    testingTypes.add(delegator.makeValue("TestingType", "testingTypeId", "scrollInSensitive-2", "description", "cursorScrollInSensitive Two"));
    testingTypes.add(delegator.makeValue("TestingType", "testingTypeId", "scrollInSensitive-3", "description", "cursorScrollInSensitive Three"));
    delegator.storeAll(testingTypes);
    boolean transactionStarted = false;
    try {
        transactionStarted = TransactionUtil.begin();
        EntityListIterator eliByEntityEngine = null;
        EntityListIterator eliByEntityQuery = null;
        EntityFindOptions findOptions = new EntityFindOptions();
        findOptions.setResultSetType(EntityFindOptions.TYPE_SCROLL_INSENSITIVE);
        eliByEntityEngine = delegator.find("TestingType", null, null, null, null, findOptions);
        eliByEntityQuery = EntityQuery.use(delegator).from("TestingType").cursorScrollInsensitive().queryIterator();
        GenericValue nextRecordByDelegator = eliByEntityEngine.next();
        GenericValue nextRecordByEntityQuery = eliByEntityQuery.next();
        assertEquals("cursorScrollInSensitive(): Records by delegator method and by EntityQuery method matched", nextRecordByDelegator, nextRecordByEntityQuery);
        eliByEntityEngine.close();
        eliByEntityQuery.close();
        TransactionUtil.commit(transactionStarted);
    } catch (GenericEntityException e) {
        TransactionUtil.rollback(transactionStarted, "Transaction is Rolled Back", e);
    }
}

Example 3 with EntityFindOptions

use of org.apache.ofbiz.entity.util.EntityFindOptions in project ofbiz-framework by apache.

the class EntityQueryTestSuite method testCursorForwardOnly.

/*
     * cursorForwardOnly(): Indicate that the ResultSet object's cursor may move only forward
     * assert: Compared first record found by both the iterator.
     */
public void testCursorForwardOnly() throws GenericEntityException {
    List<GenericValue> testingTypes = new LinkedList<>();
    testingTypes.add(delegator.makeValue("TestingType", "testingTypeId", "cursorForwardOnly-1", "description", "cursorForwardOnly One"));
    testingTypes.add(delegator.makeValue("TestingType", "testingTypeId", "cursorForwardOnly-2", "description", "cursorForwardOnly Two"));
    testingTypes.add(delegator.makeValue("TestingType", "testingTypeId", "cursorForwardOnly-3", "description", "cursorForwardOnly Three"));
    delegator.storeAll(testingTypes);
    boolean transactionStarted = false;
    try {
        transactionStarted = TransactionUtil.begin();
        EntityListIterator eliByEntityEngine = null;
        EntityListIterator eliByEntityQuery = null;
        EntityFindOptions findOptions = new EntityFindOptions();
        findOptions.setResultSetType(EntityFindOptions.TYPE_FORWARD_ONLY);
        eliByEntityEngine = delegator.find("TestingType", null, null, null, null, findOptions);
        eliByEntityQuery = EntityQuery.use(delegator).from("TestingType").cursorForwardOnly().queryIterator();
        GenericValue nextRecordByEntityEngine = eliByEntityEngine.next();
        GenericValue nextRecordByEntityQuery = eliByEntityQuery.next();
        assertEquals("cursorForwardOnly(): Value of first record pointed by both iterators matched", nextRecordByEntityEngine, nextRecordByEntityQuery);
        eliByEntityEngine.close();
        eliByEntityQuery.close();
        TransactionUtil.commit(transactionStarted);
    } catch (GenericEntityException e) {
        TransactionUtil.rollback(transactionStarted, "Transaction is Rolled Back", e);
    }
}

Example 4 with EntityFindOptions

use of org.apache.ofbiz.entity.util.EntityFindOptions in project ofbiz-framework by apache.

the class EntityQueryTestSuite method testCursorScrollSensitive.

/*
     * cursorScrollSensitive(): ResultSet object's cursor is scrollable but generally sensitive to changes to the data that underlies the ResultSet.
     * assert: Compared first record found by both the iterators.
     */
public void testCursorScrollSensitive() throws GenericEntityException {
    List<GenericValue> testingTypes = new LinkedList<>();
    testingTypes.add(delegator.makeValue("TestingType", "testingTypeId", "scrollSensitive-1", "description", "cursorScrollSensitive One"));
    testingTypes.add(delegator.makeValue("TestingType", "testingTypeId", "scrollSensitive-2", "description", "cursorScrollSensitive Two"));
    testingTypes.add(delegator.makeValue("TestingType", "testingTypeId", "scrollSensitive-3", "description", "cursorScrollSensitive Three"));
    delegator.storeAll(testingTypes);
    boolean transactionStarted = false;
    try {
        transactionStarted = TransactionUtil.begin();
        EntityListIterator eliByEntityEngine = null;
        EntityListIterator eliByEntityQuery = null;
        EntityFindOptions findOptions = new EntityFindOptions();
        findOptions.setResultSetType(EntityFindOptions.TYPE_SCROLL_SENSITIVE);
        eliByEntityEngine = delegator.find("TestingType", null, null, null, null, findOptions);
        eliByEntityQuery = EntityQuery.use(delegator).from("TestingType").cursorScrollSensitive().queryIterator();
        GenericValue nextRecordByDelegator = eliByEntityEngine.next();
        GenericValue nextRecordByEntityQuery = eliByEntityQuery.next();
        assertEquals("cursorScrollSensitive(): Records by delegator method and by EntityQuery method matched", nextRecordByDelegator, nextRecordByEntityQuery);
        eliByEntityEngine.close();
        eliByEntityQuery.close();
        TransactionUtil.commit(transactionStarted);
    } catch (GenericEntityException e) {
        TransactionUtil.rollback(transactionStarted, "Transaction is Rolled Back", e);
    }
}

Example 5 with EntityFindOptions

use of org.apache.ofbiz.entity.util.EntityFindOptions in project ofbiz-framework by apache.

the class ListFinder method runFind.

@Override
public void runFind(Map<String, Object> context, Delegator delegator) throws GeneralException {
    String entityName = this.entityNameExdr.expandString(context);
    String useCacheStr = this.useCacheStrExdr.expandString(context);
    String filterByDateStr = this.filterByDateStrExdr.expandString(context);
    String distinctStr = this.distinctStrExdr.expandString(context);
    String delegatorName = this.delegatorNameExdr.expandString(context);
    ModelEntity modelEntity = delegator.getModelEntity(entityName);
    String resultSetTypeString = this.resultSetTypeExdr.expandString(context);
    if (modelEntity == null) {
        throw new IllegalArgumentException("In find entity by " + label + " could not find definition for entity with name [" + entityName + "].");
    }
    boolean useCache = "true".equals(useCacheStr);
    boolean filterByDate = "true".equals(filterByDateStr);
    boolean distinct = "true".equals(distinctStr);
    int resultSetType = ResultSet.TYPE_SCROLL_INSENSITIVE;
    if ("forward".equals(resultSetTypeString)) {
        resultSetType = ResultSet.TYPE_FORWARD_ONLY;
    }
    if (UtilValidate.isNotEmpty(delegatorName)) {
        delegator = DelegatorFactory.getDelegator(delegatorName);
    }
    EntityCondition whereEntityCondition = getWhereEntityCondition(context, modelEntity, delegator.getModelFieldTypeReader(modelEntity));
    EntityCondition havingEntityCondition = getHavingEntityCondition(context, modelEntity, delegator.getModelFieldTypeReader(modelEntity));
    if (useCache) {
        // if useCache == true && outputHandler instanceof UseIterator, throw exception; not a valid combination
        if (outputHandler instanceof UseIterator) {
            Debug.logWarning("In find entity by " + label + " cannot have use-cache set to true " + label + " select use-iterator for the output type. Using cache and ignoring use-iterator setting.", module);
            outputHandler = new GetAll();
        }
        if (distinct) {
            throw new IllegalArgumentException("In find entity by " + label + " cannot have use-cache set to true " + label + " set distinct to true.");
        }
        if (havingEntityCondition != null) {
            throw new IllegalArgumentException("In find entity by " + label + " cannot have use-cache set to true and specify a having-condition-list (can only use a where condition with condition-expr or condition-list).");
        }
    }
    // get the list of fieldsToSelect from selectFieldExpanderList
    Set<String> fieldsToSelect = EntityFinderUtil.makeFieldsToSelect(selectFieldExpanderList, context);
    // if fieldsToSelect != null and useCacheBool is true, throw an error
    if (fieldsToSelect != null && useCache) {
        throw new IllegalArgumentException("Error in entity query by " + label + " definition, cannot specify select-field elements when use-cache is set to true");
    }
    // get the list of orderByFields from orderByExpanderList
    List<String> orderByFields = EntityFinderUtil.makeOrderByFieldList(this.orderByExpanderList, context);
    try {
        // if filterByDate, do a date filter on the results based on the now-timestamp
        if (filterByDate && !useCache) {
            EntityCondition filterByDateCondition = EntityUtil.getFilterByDateExpr();
            if (whereEntityCondition != null) {
                whereEntityCondition = EntityCondition.makeCondition(UtilMisc.toList(whereEntityCondition, filterByDateCondition));
            } else {
                whereEntityCondition = filterByDateCondition;
            }
        }
        if (useCache) {
            List<GenericValue> results = delegator.findList(entityName, whereEntityCondition, fieldsToSelect, orderByFields, null, true);
            if (filterByDate) {
                results = EntityUtil.filterByDate(results);
            }
            this.outputHandler.handleOutput(results, context, listAcsr);
        } else {
            boolean useTransaction = true;
            if (this.outputHandler instanceof UseIterator && !TransactionUtil.isTransactionInPlace()) {
                Exception newE = new Exception("Stack Trace");
                Debug.logError(newE, "ERROR: Cannot do a by " + label + " find that returns an EntityListIterator with no transaction in place. Wrap this call in a transaction.", module);
                useTransaction = false;
            }
            EntityFindOptions options = new EntityFindOptions();
            options.setDistinct(distinct);
            options.setResultSetType(resultSetType);
            if (outputHandler instanceof LimitRange) {
                LimitRange limitRange = (LimitRange) outputHandler;
                int start = limitRange.getStart(context);
                int size = limitRange.getSize(context);
                options.setMaxRows(start + size);
            } else if (outputHandler instanceof LimitView) {
                LimitView limitView = (LimitView) outputHandler;
                int index = limitView.getIndex(context);
                int size = limitView.getSize(context);
                options.setMaxRows(size * (index + 1));
            }
            boolean beganTransaction = false;
            try {
                if (useTransaction) {
                    beganTransaction = TransactionUtil.begin();
                }
                EntityListIterator eli = delegator.find(entityName, whereEntityCondition, havingEntityCondition, fieldsToSelect, orderByFields, options);
                this.outputHandler.handleOutput(eli, context, listAcsr);
            // NOTE: the eli EntityListIterator is not closed here. It SHOULD be closed later after the returned list will be used (eg see EntityAnd.getChildren() in ModelTree.java)
            } catch (GenericEntityException e) {
                String errMsg = "Failure in by " + label + " find operation, rolling back transaction";
                Debug.logError(e, errMsg, module);
                try {
                    // only rollback the transaction if we started one...
                    TransactionUtil.rollback(beganTransaction, errMsg, e);
                } catch (GenericEntityException e2) {
                    Debug.logError(e2, "Could not rollback transaction: " + e2.toString(), module);
                }
                // after rolling back, rethrow the exception
                throw e;
            } finally {
                // only commit the transaction if we started one... this will throw an exception if it fails
                TransactionUtil.commit(beganTransaction);
            }
        }
    } catch (GenericEntityException e) {
        String errMsg = "Error doing find by " + label + ": " + e.toString();
        Debug.logError(e, module);
        throw new GeneralException(errMsg, e);
    }
}