Examples with EntityConditionParam org.apache.ofbiz.entity.condition.EntityConditionParam used on opensource projects

Example 1 with EntityConditionParam

use of org.apache.ofbiz.entity.condition.EntityConditionParam in project ofbiz-framework by apache.

the class GenericDAO method updateByCondition.

public int updateByCondition(ModelEntity modelEntity, Map<String, ? extends Object> fieldsToSet, EntityCondition condition, SQLProcessor sqlP) throws GenericEntityException {
    if (modelEntity == null || fieldsToSet == null || condition == null)
        return 0;
    if (modelEntity instanceof ModelViewEntity) {
        throw new org.apache.ofbiz.entity.GenericNotImplementedException("Operation updateByCondition not supported yet for view entities");
    }
    StringBuilder sql = new StringBuilder("UPDATE ").append(modelEntity.getTableName(datasource));
    sql.append(" SET ");
    List<EntityConditionParam> params = new LinkedList<EntityConditionParam>();
    for (Map.Entry<String, ? extends Object> entry : fieldsToSet.entrySet()) {
        String name = entry.getKey();
        ModelField field = modelEntity.getField(name);
        if (field != null) {
            if (!params.isEmpty()) {
                sql.append(", ");
            }
            sql.append(field.getColName()).append(" = ?");
            params.add(new EntityConditionParam(field, entry.getValue()));
        }
    }
    sql.append(" WHERE ").append(condition.makeWhereString(modelEntity, params, this.datasource));
    sqlP.prepareStatement(sql.toString());
    for (EntityConditionParam param : params) {
        SqlJdbcUtil.setValue(sqlP, param.getModelField(), modelEntity.getEntityName(), param.getFieldValue(), modelFieldTypeReader);
    }
    return sqlP.executeUpdate();
}

Example 2 with EntityConditionParam

use of org.apache.ofbiz.entity.condition.EntityConditionParam 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 3 with EntityConditionParam

use of org.apache.ofbiz.entity.condition.EntityConditionParam in project ofbiz-framework by apache.

the class GenericDAO method selectListIteratorByCondition.

/* ====================================================================== */
/* ====================================================================== */
/**
 * Finds GenericValues by the conditions specified in the EntityCondition object, the the EntityCondition javadoc for more details.
 *@param modelEntity The ModelEntity of the Entity as defined in the entity XML file
 *@param whereEntityCondition The EntityCondition object that specifies how to constrain this query before any groupings are done (if this is a view entity with group-by aliases)
 *@param havingEntityCondition The EntityCondition object that specifies how to constrain this query after any groupings are done (if this is a view entity with group-by aliases)
 *@param fieldsToSelect The fields of the named entity to get from the database; if empty or null all fields will be retreived
 *@param orderBy The fields of the named entity to order the query by; optionally add a " ASC" for ascending or " DESC" for descending
 *@param findOptions An instance of EntityFindOptions that specifies advanced query options. See the EntityFindOptions JavaDoc for more details.
 *@return EntityListIterator representing the result of the query: NOTE THAT THIS MUST BE CLOSED WHEN YOU ARE
 *      DONE WITH IT (preferably in a finally block),
 *      AND DON'T LEAVE IT OPEN TOO LONG BECAUSE IT WILL MAINTAIN A DATABASE CONNECTION.
 */
public EntityListIterator selectListIteratorByCondition(Delegator delegator, ModelEntity modelEntity, EntityCondition whereEntityCondition, EntityCondition havingEntityCondition, Collection<String> fieldsToSelect, List<String> orderBy, EntityFindOptions findOptions) throws GenericEntityException {
    if (modelEntity == null) {
        return null;
    }
    ModelViewEntity modelViewEntity = null;
    if (modelEntity instanceof ModelViewEntity) {
        modelViewEntity = (ModelViewEntity) modelEntity;
    }
    // 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);
    }
    // make two ArrayLists of fields, one for fields to select and the other for where clause fields (to find by)
    List<ModelField> selectFields = new LinkedList<ModelField>();
    if (UtilValidate.isNotEmpty(fieldsToSelect)) {
        Set<String> tempKeys = new HashSet<String>();
        tempKeys.addAll(fieldsToSelect);
        Set<String> fieldSetsToInclude = new HashSet<String>();
        Set<String> addedFields = new HashSet<String>();
        for (String fieldToSelect : fieldsToSelect) {
            if (tempKeys.contains(fieldToSelect)) {
                ModelField curField = modelEntity.getField(fieldToSelect);
                if (curField != null) {
                    fieldSetsToInclude.add(curField.getFieldSet());
                    selectFields.add(curField);
                    tempKeys.remove(fieldToSelect);
                    addedFields.add(fieldToSelect);
                }
            }
        }
        if (tempKeys.size() > 0) {
            throw new GenericModelException("In selectListIteratorByCondition invalid field names specified: " + tempKeys.toString());
        }
        fieldSetsToInclude.remove("");
        if (verboseOn) {
            Debug.logInfo("[" + modelEntity.getEntityName() + "]: field-sets to include: " + fieldSetsToInclude, module);
        }
        if (UtilValidate.isNotEmpty(fieldSetsToInclude)) {
            Iterator<ModelField> fieldIter = modelEntity.getFieldsIterator();
            Set<String> extraFields = new HashSet<String>();
            Set<String> reasonSets = new HashSet<String>();
            while (fieldIter.hasNext()) {
                ModelField curField = fieldIter.next();
                String fieldSet = curField.getFieldSet();
                if (UtilValidate.isEmpty(fieldSet)) {
                    continue;
                }
                if (!fieldSetsToInclude.contains(fieldSet)) {
                    continue;
                }
                String fieldName = curField.getName();
                if (addedFields.contains(fieldName)) {
                    continue;
                }
                reasonSets.add(fieldSet);
                extraFields.add(fieldName);
                addedFields.add(fieldName);
                selectFields.add(curField);
            }
            if (verboseOn) {
                Debug.logInfo("[" + modelEntity.getEntityName() + "]: auto-added select fields: " + extraFields, module);
                Debug.logInfo("[" + modelEntity.getEntityName() + "]: auto-added field-sets: " + reasonSets, module);
            }
        }
    } else {
        selectFields = modelEntity.getFieldsUnmodifiable();
    }
    StringBuilder sqlBuffer = new StringBuilder("SELECT ");
    if (findOptions.getDistinct()) {
        sqlBuffer.append("DISTINCT ");
    }
    if (selectFields.size() > 0) {
        modelEntity.colNameString(selectFields, sqlBuffer, "", ", ", "", datasource.getAliasViewColumns());
    } else {
        sqlBuffer.append("*");
    }
    // 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 (modelViewEntity != null) {
        modelViewEntity.colNameString(modelViewEntity.getGroupBysCopy(selectFields), sqlBuffer, " GROUP BY ", ", ", "", false);
    }
    // HAVING clause
    List<EntityConditionParam> havingEntityConditionParams = new LinkedList<EntityConditionParam>();
    makeConditionHavingString(sqlBuffer, " HAVING ", modelEntity, havingEntityCondition, viewHavingConditions, havingEntityConditionParams);
    // ORDER BY clause
    List<String> orderByExpanded = new LinkedList<String>();
    // add the manually specified ones, then the ones in the view entity's entity-condition
    if (orderBy != null) {
        orderByExpanded.addAll(orderBy);
    }
    if (viewOrderByList != null) {
        // add to end of other order by so that those in method call will override those in view
        orderByExpanded.addAll(viewOrderByList);
    }
    sqlBuffer.append(SqlJdbcUtil.makeOrderByClause(modelEntity, orderByExpanded, datasource));
    // OFFSET clause
    makeOffsetString(sqlBuffer, findOptions);
    // make the final SQL String
    String sql = sqlBuffer.toString();
    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);
    }
    long queryStartTime = 0;
    if (Debug.timingOn()) {
        queryStartTime = System.currentTimeMillis();
    }
    sqlP.executeQuery();
    if (Debug.timingOn()) {
        long queryEndTime = System.currentTimeMillis();
        long queryTotalTime = queryEndTime - queryStartTime;
        if (queryTotalTime > 150) {
            Debug.logTiming("Ran query in " + queryTotalTime + " milli-seconds: " + " EntityName: " + modelEntity.getEntityName() + " Sql: " + sql + " where clause:" + whereEntityConditionParams, module);
        }
    }
    return new EntityListIterator(sqlP, modelEntity, selectFields, modelFieldTypeReader, this, whereEntityCondition, havingEntityCondition, findOptions.getDistinct());
}