Examples with QBSelect com.servoy.j2db.querybuilder.impl.QBSelect used on opensource projects

Example 6 with QBSelect

use of com.servoy.j2db.querybuilder.impl.QBSelect in project servoy-client by Servoy.

the class FoundSet method getFoundSetFilterParams.

public Object[][] getFoundSetFilterParams(String filterName) {
    List<Object[]> result = new ArrayList<Object[]>();
    for (TableFilter f : iterate(foundSetFilters)) {
        if (filterName == null || filterName.equals(f.getName())) {
            if (f.getTableFilterdefinition() instanceof DataproviderTableFilterdefinition) {
                DataproviderTableFilterdefinition tableFilterdefinition = (DataproviderTableFilterdefinition) f.getTableFilterdefinition();
                result.add(new Object[] { f.getTableName(), tableFilterdefinition.getDataprovider(), RelationItem.getOperatorAsString(tableFilterdefinition.getOperator()), tableFilterdefinition.getValue(), f.getName() });
            }
            if (f.getTableFilterdefinition() instanceof QueryTableFilterdefinition) {
                QuerySelect querySelect = ((QueryTableFilterdefinition) f.getTableFilterdefinition()).getQuerySelect();
                result.add(new Object[] { new QBSelect(fsm, fsm.getScopesScopeProvider(), fsm.getApplication().getFlattenedSolution(), fsm.getApplication().getScriptEngine().getSolutionScope(), querySelect.getTable().getDataSource(), null, AbstractBaseQuery.deepClone(querySelect, true)), f.getName() });
            }
        }
    }
    return result.toArray(new Object[result.size()][]);
}

Example 7 with QBSelect

use of com.servoy.j2db.querybuilder.impl.QBSelect in project servoy-client by Servoy.

the class FoundSet method loadByQuery.

public boolean loadByQuery(IQueryBuilder query) throws ServoyException {
    // check if this query is on our base table
    if (!Utils.stringSafeEquals(getDataSource(), query.getDataSource())) {
        throw new RepositoryException(// $NON-NLS-1$//$NON-NLS-2$
        "Cannot load foundset with query based on another table (" + getDataSource() + " != " + query.getDataSource() + ')').setContext(this.toString());
    }
    // makes a clone
    QuerySelect sqlSelect = ((QBSelect) query).build();
    if (sqlSelect.getColumns() == null) {
        // no columns, add pk
        // note that QBSelect.build() already returns a clone
        Iterator<Column> pkIt = ((Table) getTable()).getRowIdentColumns().iterator();
        if (!pkIt.hasNext()) {
            throw new RepositoryException(ServoyException.InternalCodes.PRIMARY_KEY_NOT_FOUND, new Object[] { getTable().getName() }).setContext(this.toString());
        }
        while (pkIt.hasNext()) {
            Column c = pkIt.next();
            sqlSelect.addColumn(c.queryColumn(sqlSelect.getTable()));
        }
    }
    Placeholder dynamicPKplaceholder = sqlSelect.getPlaceholder(new TablePlaceholderKey(sqlSelect.getTable(), SQLGenerator.PLACEHOLDER_FOUNDSET_PKS));
    if (dynamicPKplaceholder != null && dynamicPKplaceholder.isSet() && dynamicPKplaceholder.getValue() instanceof Object[]) {
        // loading from saved query, dynamic pk was replaced by array in serialization, make dynamic again
        dynamicPKplaceholder.setValue(new DynamicPkValuesArray(getSQLSheet().getTable().getRowIdentColumns(), SQLGenerator.createPKValuesDataSet(getSQLSheet().getTable().getRowIdentColumns(), (Object[][]) dynamicPKplaceholder.getValue())));
    }
    if (sqlSelect.getSorts() == null) {
        // query does not define sort, use last sorts
        fsm.getSQLGenerator().addSorts(sqlSelect, sqlSelect.getTable(), this, sheet.getTable(), lastSortColumns == null ? defaultSort : lastSortColumns, true, false);
    } else {
        // try to determine the SortColumns from the query-sort
        lastSortColumns = determineSortColumns(sqlSelect);
    }
    return loadByQuery(addFilterconditions(sqlSelect, foundSetFilters));
}

Example 8 with QBSelect

use of com.servoy.j2db.querybuilder.impl.QBSelect in project servoy-client by Servoy.

the class FoundSetManager method getFoundSet.

public IFoundSet getFoundSet(IQueryBuilder query) throws ServoyException {
    QBSelect select = (QBSelect) query;
    IFoundSet fs = getNewFoundSet(select.getDataSource(), select.getQuery(), null);
    fs.loadAllRecords();
    return fs;
}

Example 9 with QBSelect

use of com.servoy.j2db.querybuilder.impl.QBSelect in project servoy-client by Servoy.

the class FoundSetManager method getTableFilterParams.

public Object[][] getTableFilterParams(String serverName, String filterName) {
    List<Object[]> result = new ArrayList<Object[]>();
    for (TableFilter f : iterate(tableFilterParams.get(serverName))) {
        if (filterName == null || filterName.equals(f.getName())) {
            if (f.getTableFilterdefinition() instanceof DataproviderTableFilterdefinition) {
                DataproviderTableFilterdefinition tableFilterdefinition = (DataproviderTableFilterdefinition) f.getTableFilterdefinition();
                result.add(new Object[] { f.getTableName(), tableFilterdefinition.getDataprovider(), RelationItem.getOperatorAsString(tableFilterdefinition.getOperator()), tableFilterdefinition.getValue(), f.getName() });
            }
            if (f.getTableFilterdefinition() instanceof QueryTableFilterdefinition) {
                QuerySelect querySelect = ((QueryTableFilterdefinition) f.getTableFilterdefinition()).getQuerySelect();
                result.add(new Object[] { new QBSelect(this, getScopesScopeProvider(), getApplication().getFlattenedSolution(), getApplication().getScriptEngine().getSolutionScope(), querySelect.getTable().getDataSource(), null, AbstractBaseQuery.deepClone(querySelect, true)), f.getName() });
            }
        }
    }
    return result.toArray(new Object[result.size()][]);
}

Example 10 with QBSelect

use of com.servoy.j2db.querybuilder.impl.QBSelect in project servoy-client by Servoy.

the class JSDatabaseManager method js_getSQL.

/**
 * Returns the internal SQL which defines the specified (related)foundset.
 * Optionally, the foundset and table filter params can be excluded in the sql (includeFilters=false).
 * Make sure to set the applicable filters when the sql is used in a loadRecords() call.
 * When the founset is in find mode, the find conditions are included in the resulting query.
 *
 * @sample var sql = databaseManager.getSQL(foundset)
 *
 * @param foundsetOrQBSelect The JSFoundset or QBSelect to get the sql for.
 * @param includeFilters include the foundset and table filters.
 *
 * @return String representing the sql of the JSFoundset.
 */
public String js_getSQL(Object foundsetOrQBSelect, boolean includeFilters) throws ServoyException {
    checkAuthorized();
    if (foundsetOrQBSelect instanceof IFoundSetInternal && ((IFoundSetInternal) foundsetOrQBSelect).getTable() != null) {
        try {
            QuerySet querySet = getQuerySet(((IFoundSetInternal) foundsetOrQBSelect).getCurrentStateQuery(true, false), includeFilters);
            StringBuilder sql = new StringBuilder();
            QueryString[] prepares = querySet.getPrepares();
            for (int i = 0; prepares != null && i < prepares.length; i++) {
            // TODO parameters from updates and cleanups
            // sql.append(updates[i].getSql());
            // sql.append("\n"); //$NON-NLS-1$
            }
            sql.append(querySet.getSelect().getSql());
            QueryString[] cleanups = querySet.getCleanups();
            for (int i = 0; cleanups != null && i < cleanups.length; i++) {
            // TODO parameters from updates and cleanups
            // sql.append("\n"); //$NON-NLS-1$
            // sql.append(cleanups[i].getSql());
            }
            return sql.toString();
        } catch (Exception e) {
            Debug.error(e);
        }
    } else if (foundsetOrQBSelect instanceof QBSelect) {
        try {
            QuerySelect select = ((QBSelect) foundsetOrQBSelect).build();
            if (select.getColumns() == null) {
                // no columns, add pk
                // note that QBSelect.build() already returns a clone
                ITable table = application.getFoundSetManager().getTable(select.getTable().getDataSource());
                Iterator<Column> pkIt = ((Table) table).getRowIdentColumns().iterator();
                if (!pkIt.hasNext()) {
                    throw new RepositoryException(ServoyException.InternalCodes.PRIMARY_KEY_NOT_FOUND, new Object[] { table.getName() });
                }
                while (pkIt.hasNext()) {
                    Column c = pkIt.next();
                    select.addColumn(c.queryColumn(select.getTable()));
                }
            }
            QuerySet querySet = getQuerySet(select, includeFilters);
            return querySet.getSelect().getSql();
        } catch (RemoteException e) {
            Debug.error(e);
        }
    }
    return null;
}