Examples with SetCondition com.servoy.j2db.query.SetCondition used on opensource projects

Example 11 with SetCondition

use of com.servoy.j2db.query.SetCondition in project servoy-client by Servoy.

the class SQLGenerator method createTableFiltercondition.

public static QueryFilter createTableFiltercondition(BaseQueryTable qTable, Table table, DataproviderTableFilterdefinition filterdefinition) {
    Column c = table.getColumn(filterdefinition.getDataprovider());
    if (c == null) {
        // $NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$
        Debug.error("Could not apply filter " + filterdefinition + " on table " + table + " : column not found:" + filterdefinition.getDataprovider());
        return null;
    }
    int op = filterdefinition.getOperator();
    int maskedOp = op & IBaseSQLCondition.OPERATOR_MASK;
    Object value = filterdefinition.getValue();
    QueryColumn qColumn = c.queryColumn(qTable);
    ISQLCondition filterWhere;
    if (maskedOp == IBaseSQLCondition.EQUALS_OPERATOR || maskedOp == IBaseSQLCondition.NOT_OPERATOR || maskedOp == IBaseSQLCondition.IN_OPERATOR || maskedOp == IBaseSQLCondition.NOT_IN_OPERATOR || maskedOp == IBaseSQLCondition.GT_OPERATOR || maskedOp == IBaseSQLCondition.LT_OPERATOR || maskedOp == IBaseSQLCondition.GTE_OPERATOR || maskedOp == IBaseSQLCondition.LTE_OPERATOR) {
        Object inValues;
        boolean andCondition = true;
        if (value instanceof List<?>) {
            inValues = new Object[][] { ((List<?>) value).toArray() };
            andCondition = maskedOp != IBaseSQLCondition.NOT_OPERATOR && maskedOp != IBaseSQLCondition.NOT_IN_OPERATOR;
        } else if (value != null && value.getClass().isArray()) {
            inValues = new Object[][] { (Object[]) value };
            andCondition = maskedOp != IBaseSQLCondition.NOT_OPERATOR && maskedOp != IBaseSQLCondition.NOT_IN_OPERATOR;
        } else {
            if (value != null && isSelectQuery(value.toString())) {
                if ((op & IBaseSQLCondition.IS_SQL_MODIFIER) == 0) {
                    Debug.warn("Filter is created using a custom query without using the sql-modifier, this will be removed in a future version of servoy, please use operator '" + RelationItem.getOperatorAsString(op | IBaseSQLCondition.IS_SQL_MODIFIER) + "'");
                }
                // add as subquery
                inValues = new QueryCustomSelect(value.toString(), null);
            } else {
                if (value != null && (op & IBaseSQLCondition.IS_SQL_MODIFIER) != 0) {
                    Debug.warn("Filter has the sql-modifier, but the value is not valid sql for filters: " + SQL_QUERY_VALIDATION_MESSAGE + ":" + value);
                }
                inValues = new Object[][] { new Object[] { value } };
            }
        }
        // replace values with column type value
        if (inValues instanceof Object[][]) {
            Object[][] array = (Object[][]) inValues;
            for (int i = 0; i < array.length; i++) {
                for (int j = 0; array[i] != null && j < array[i].length; j++) {
                    Object v = c.getAsRightType(array[i][j]);
                    if (v == null)
                        v = ValueFactory.createNullValue(c.getType());
                    array[i][j] = v;
                }
            }
        }
        filterWhere = new SetCondition(op, new IQuerySelectValue[] { qColumn }, inValues, andCondition);
    } else if (maskedOp == IBaseSQLCondition.BETWEEN_OPERATOR || maskedOp == IBaseSQLCondition.NOT_BETWEEN_OPERATOR) {
        Object op1 = null;
        Object op2 = null;
        if (value instanceof List<?> && ((List<?>) value).size() > 1) {
            op1 = ((List<?>) value).get(0);
            op2 = ((List<?>) value).get(1);
        } else if (value != null && value.getClass().isArray() && ((Object[]) value).length > 1) {
            op1 = ((Object[]) value)[0];
            op2 = ((Object[]) value)[1];
        }
        op1 = c.getAsRightType(op1);
        if (op1 == null)
            op1 = ValueFactory.createNullValue(c.getType());
        op2 = c.getAsRightType(op2);
        if (op2 == null)
            op2 = ValueFactory.createNullValue(c.getType());
        filterWhere = new CompareCondition(op, qColumn, new Object[] { op1, op2 });
    } else {
        Object operand;
        if (maskedOp == IBaseSQLCondition.LIKE_OPERATOR || maskedOp == IBaseSQLCondition.NOT_LIKE_OPERATOR) {
            operand = value;
        } else {
            operand = c.getAsRightType(value);
            if (operand == null)
                operand = ValueFactory.createNullValue(c.getType());
        }
        filterWhere = new CompareCondition(op, qColumn, operand);
    }
    return new QueryFilter(filterWhere);
}

Example 12 with SetCondition

use of com.servoy.j2db.query.SetCondition in project servoy-client by Servoy.

the class Messages method loadMessagesFromDatabaseRepositorySinglefilter.

private static void loadMessagesFromDatabaseRepositorySinglefilter(IServer server, Table table, String clientId, IDataServer dataServer, Properties properties, Properties localeProperties, Locale language, int loadingType, String searchKey, String searchText, Column filterColumn, String singleColumnValueFilter, IFoundSetManagerInternal fm) throws RemoteException, ServoyException {
    // $NON-NLS-1$//$NON-NLS-2$//$NON-NLS-3$
    Debug.trace("Loading messages from DB: Server: " + server.getName() + " Table: " + table.getName() + " Language: " + language);
    if (loadingType == ALL_LOCALES || loadingType == DEFAULT_LOCALE) {
        QueryTable messagesTable = new QueryTable(table.getSQLName(), table.getDataSource(), table.getCatalog(), table.getSchema());
        QuerySelect sql = new QuerySelect(messagesTable);
        // $NON-NLS-1$
        QueryColumn msgKey = new QueryColumn(messagesTable, -1, "message_key", Types.VARCHAR, 150, 0, null, 0);
        // $NON-NLS-1$
        QueryColumn msgVal = new QueryColumn(messagesTable, -1, "message_value", Types.VARCHAR, 2000, 0, null, 0);
        // $NON-NLS-1$
        QueryColumn msgLang = new QueryColumn(messagesTable, -1, "message_language", Types.VARCHAR, 150, 0, null, 0);
        sql.addColumn(msgKey);
        sql.addColumn(msgVal);
        // $NON-NLS-1$
        String condMessages = "MESSAGES";
        sql.addCondition(condMessages, new CompareCondition(IBaseSQLCondition.EQUALS_OPERATOR | IBaseSQLCondition.ORNULL_MODIFIER, msgLang, new QueryColumnValue("", null)));
        if (filterColumn != null) {
            QueryColumn columnFilter = filterColumn.queryColumn(messagesTable);
            CompareCondition cc = new CompareCondition(IBaseSQLCondition.EQUALS_OPERATOR, columnFilter, new QueryColumnValue(singleColumnValueFilter, null));
            sql.addCondition(condMessages, cc);
        }
        // Filter to only include records with the default (null) value for columns flagged as Tenant column
        for (Column column : table.getTenantColumns()) {
            CompareCondition cc = new CompareCondition(IBaseSQLCondition.ISNULL_OPERATOR, column.queryColumn(messagesTable), null);
            sql.addCondition("_svy_tenant_id_filter_" + column.getName(), cc);
        }
        if (searchKey != null || searchText != null) {
            QueryTable subselectTable = new QueryTable(table.getSQLName(), table.getDataSource(), table.getCatalog(), table.getSchema());
            QuerySelect subselect = new QuerySelect(subselectTable);
            // $NON-NLS-1$
            QueryColumn msgKeySub = new QueryColumn(subselectTable, -1, "message_key", Types.VARCHAR, 150, 0, null, 0);
            // $NON-NLS-1$
            QueryColumn msgValueSub = new QueryColumn(subselectTable, -1, "message_value", Types.VARCHAR, 2000, 0, null, 0);
            // $NON-NLS-1$
            QueryColumn msgLangSub = new QueryColumn(subselectTable, -1, "message_language", Types.VARCHAR, 150, 0, null, 0);
            subselect.addColumn(msgKeySub);
            // $NON-NLS-1$
            String condSearch = "SEARCH";
            if (searchKey != null) {
                subselect.addCondition(condSearch, new CompareCondition(IBaseSQLCondition.LIKE_OPERATOR, msgKeySub, new QueryColumnValue('%' + searchKey + '%', null)));
            }
            if (searchText != null) {
                subselect.addConditionOr(condSearch, new CompareCondition(IBaseSQLCondition.LIKE_OPERATOR, msgValueSub, new QueryColumnValue('%' + searchText + '%', null)));
            }
            // $NON-NLS-1$
            String condLang = "LANGUAGE";
            subselect.addCondition(condLang, new CompareCondition(IBaseSQLCondition.EQUALS_OPERATOR, msgLangSub, new QueryColumnValue(localeToString(language), null)));
            subselect.addConditionOr(condLang, // $NON-NLS-1$
            new CompareCondition(IBaseSQLCondition.EQUALS_OPERATOR | IBaseSQLCondition.ORNULL_MODIFIER, msgLangSub, new QueryColumnValue("", null)));
            sql.addCondition(condMessages, new SetCondition(IBaseSQLCondition.EQUALS_OPERATOR, new QueryColumn[] { msgKey }, subselect, true));
        }
        // $NON-NLS-1$
        if (Debug.tracing())
            Debug.trace("Loading messages from DB: SQL: " + sql);
        IDataSet set = dataServer.performQuery(clientId, server.getName(), null, sql, null, fm != null ? fm.getTableFilterParams(server.getName(), sql) : null, false, 0, Integer.MAX_VALUE, IDataServer.MESSAGES_QUERY);
        for (int i = 0; i < set.getRowCount(); i++) {
            Object[] row = set.getRow(i);
            if (row[0] != null && row[1] != null) {
                properties.setProperty((String) row[0], (String) row[1]);
            }
        }
    }
    if (loadingType == ALL_LOCALES || loadingType == SPECIFIED_LANGUAGE) {
        fillLocaleMessages(clientId, dataServer, table, server.getName(), filterColumn, singleColumnValueFilter, searchKey, searchText, language, localeProperties != null ? localeProperties : properties, SPECIFIED_LANGUAGE, fm);
    }
    if (loadingType == ALL_LOCALES || loadingType == SPECIFIED_LOCALE) {
        fillLocaleMessages(clientId, dataServer, table, server.getName(), filterColumn, singleColumnValueFilter, searchKey, searchText, language, localeProperties != null ? localeProperties : properties, SPECIFIED_LOCALE, fm);
    }
}

Example 13 with SetCondition

use of com.servoy.j2db.query.SetCondition in project servoy-client by Servoy.

the class RowManager method getBlob.

/**
 * @param row
 * @return
 */
Blob getBlob(Row row, int columnIndex) throws Exception {
    QuerySelect blobSelect = new QuerySelect(new QueryTable(sheet.getTable().getSQLName(), sheet.getTable().getDataSource(), sheet.getTable().getCatalog(), sheet.getTable().getSchema()));
    String blobColumnName = sheet.getColumnNames()[columnIndex];
    Column blobColumn = sheet.getTable().getColumn(blobColumnName);
    blobSelect.addColumn(new QueryColumn(blobSelect.getTable(), blobColumn.getID(), blobColumn.getSQLName(), blobColumn.getType(), blobColumn.getLength(), blobColumn.getScale(), blobColumn.getNativeTypename(), blobColumn.getFlags(), false));
    String[] pkColumnNames = sheet.getPKColumnDataProvidersAsArray();
    IQuerySelectValue[] pkQuerycolumns = new IQuerySelectValue[pkColumnNames.length];
    Object[][] pkValues = new Object[pkColumnNames.length][];
    Object[] pk = row.getPK();
    for (int k = 0; k < pkValues.length; k++) {
        Column pkcolumn = sheet.getTable().getColumn(pkColumnNames[k]);
        pkQuerycolumns[k] = new QueryColumn(blobSelect.getTable(), pkcolumn.getID(), pkcolumn.getSQLName(), pkcolumn.getType(), pkcolumn.getLength(), pkcolumn.getScale(), pkcolumn.getNativeTypename(), pkcolumn.getFlags(), false);
        pkValues[k] = new Object[] { pk[k] };
    }
    // $NON-NLS-1$
    blobSelect.addCondition("blobselect", new SetCondition(IBaseSQLCondition.EQUALS_OPERATOR, pkQuerycolumns, pkValues, true));
    String serverName = sheet.getServerName();
    String transaction_id = null;
    GlobalTransaction gt = fsm.getGlobalTransaction();
    if (gt != null) {
        transaction_id = gt.getTransactionID(sheet.getServerName());
    }
    return fsm.getApplication().getDataServer().getBlob(fsm.getApplication().getClientID(), serverName, blobSelect, fsm.getTableFilterParams(sheet.getServerName(), blobSelect), transaction_id);
}