Examples with QueryStringWithParameters com.manydesigns.elements.text.QueryStringWithParameters used on opensource projects

Example 1 with QueryStringWithParameters

use of com.manydesigns.elements.text.QueryStringWithParameters in project Portofino by ManyDesigns.

the class CrudAction method calculateTotalSearchRecords.

protected long calculateTotalSearchRecords() {
    TableCriteria criteria = new TableCriteria(baseTable);
    if (searchForm != null) {
        searchForm.configureCriteria(criteria);
    }
    QueryStringWithParameters query = QueryUtils.mergeQuery(getBaseQuery(), criteria, this);
    String queryString = query.getQueryString();
    String totalRecordsQueryString;
    try {
        totalRecordsQueryString = generateCountQuery(queryString);
    } catch (JSQLParserException e) {
        throw new Error(e);
    }
    // TODO gestire count non disponibile (totalRecordsQueryString == null)
    List<Object> result = QueryUtils.runHqlQuery(session, totalRecordsQueryString, query.getParameters());
    return totalSearchRecords = ((Number) result.get(0)).longValue();
}

Example 2 with QueryStringWithParameters

use of com.manydesigns.elements.text.QueryStringWithParameters in project Portofino by ManyDesigns.

the class ModelSelectionProviderSupport method createHQLOptionProvider.

protected OptionProvider createHQLOptionProvider(DatabaseSelectionProvider selectionProvider, String name, String databaseName, String hql) {
    Database database = DatabaseLogic.findDatabaseByName(persistence.getModel(), databaseName);
    Table table = QueryUtils.getTableFromQueryString(database, hql);
    if (table == null) {
        logger.error("Selection provider {} has a HQL query that " + "refers to an entity that does not exist ({})", name, hql);
        return null;
    }
    return new MemoizingOptionProvider(() -> {
        String entityName = table.getActualEntityName();
        Session session = persistence.getSession(databaseName);
        QueryStringWithParameters queryWithParameters = QueryUtils.mergeQuery(hql, null, this);
        Collection<Object> objects = getFromQueryCache(selectionProvider, queryWithParameters);
        if (objects == null) {
            String queryString = queryWithParameters.getQueryString();
            Object[] parameters = queryWithParameters.getParameters();
            logger.debug("Query not in cache: {}", queryString);
            try {
                objects = QueryUtils.runHqlQuery(session, queryString, parameters);
            } catch (Exception e) {
                logger.error("Exception in populating selection provider " + name, e);
                return null;
            }
            putInQueryCache(selectionProvider, queryWithParameters, objects);
        }
        TableAccessor tableAccessor = persistence.getTableAccessor(databaseName, entityName);
        ShortName shortNameAnnotation = tableAccessor.getAnnotation(ShortName.class);
        TextFormat[] textFormats = null;
        // L'ordinamento e' usato solo in caso di chiave singola
        if (shortNameAnnotation != null && tableAccessor.getKeyProperties().length == 1) {
            textFormats = new TextFormat[] { OgnlTextFormat.create(shortNameAnnotation.value()) };
        }
        final TextFormat[] actualTextFormats = textFormats;
        Stream<OptionProvider.Option> optionStream = objects.stream().map(o -> SelectionProviderLogic.getOption(name, tableAccessor.getKeyProperties(), actualTextFormats, o));
        if (selectionProvider instanceof ForeignKey) {
            optionStream = optionStream.sorted(DefaultSelectionProvider.OPTION_COMPARATOR_BY_LABEL);
        }
        return optionStream.collect(Collectors.toList());
    });
}

Example 3 with QueryStringWithParameters

use of com.manydesigns.elements.text.QueryStringWithParameters in project Portofino by ManyDesigns.

the class QueryUtils method mergeQuery.

/**
 * Merges a HQL query string with a {@link TableCriteria} object representing a search. The query string
 * is processed with an {@link OgnlSqlFormat}, so it can access values from the OGNL context, as well as
 * from an (optional) rootFactory object.
 * @param queryString the base query
 * @param criteria the criteria to merge with the query
 * @param rootObject the OGNL rootFactory object (can be null)
 * @return the merged query
 */
public static QueryStringWithParameters mergeQuery(String queryString, @Nullable TableCriteria criteria, Object rootObject) {
    OgnlHqlFormat hqlFormat = OgnlHqlFormat.create(queryString);
    String formatString = hqlFormat.getFormatString();
    Object[] parameters = hqlFormat.evaluateOgnlExpressions(rootObject);
    CCJSqlParserManager parserManager = new CCJSqlParserManager();
    PlainSelect parsedQueryString;
    PlainSelect parsedCriteriaQuery;
    try {
        parsedQueryString = parseQuery(parserManager, formatString);
    } catch (JSQLParserException e) {
        throw new RuntimeException("Couldn't merge query", e);
    }
    Alias mainEntityAlias = null;
    if (criteria != null) {
        mainEntityAlias = getEntityAlias(criteria.getTable().getActualEntityName(), parsedQueryString);
    }
    QueryStringWithParameters criteriaQuery = getQueryStringWithParametersForCriteria(criteria, mainEntityAlias != null ? mainEntityAlias.getName() : null, parameters.length + 1);
    String criteriaQueryString = criteriaQuery.getQueryString();
    Object[] criteriaParameters = criteriaQuery.getParameters();
    try {
        if (StringUtils.isEmpty(criteriaQueryString)) {
            parsedCriteriaQuery = new PlainSelect();
        } else {
            parsedCriteriaQuery = parseQuery(parserManager, criteriaQueryString);
        }
    } catch (JSQLParserException e) {
        throw new RuntimeException("Couldn't merge query", e);
    }
    Expression whereExpression;
    if (parsedQueryString.getWhere() != null) {
        if (parsedCriteriaQuery.getWhere() != null) {
            whereExpression = parsedQueryString.getWhere();
            if (!(whereExpression instanceof Parenthesis)) {
                whereExpression = new Parenthesis(whereExpression);
            }
            whereExpression = new AndExpression(whereExpression, parsedCriteriaQuery.getWhere());
        } else {
            whereExpression = parsedQueryString.getWhere();
        }
    } else {
        whereExpression = parsedCriteriaQuery.getWhere();
    }
    parsedQueryString.setWhere(whereExpression);
    if (criteria != null && criteria.getOrderBy() != null) {
        List orderByElements = new ArrayList();
        OrderByElement orderByElement = new OrderByElement();
        orderByElement.setAsc(criteria.getOrderBy().isAsc());
        String propertyName = criteria.getOrderBy().getPropertyAccessor().getName();
        if (mainEntityAlias != null) {
            propertyName = mainEntityAlias.getName() + "." + propertyName;
        }
        orderByElement.setExpression(new net.sf.jsqlparser.schema.Column(new net.sf.jsqlparser.schema.Table(), propertyName));
        orderByElements.add(orderByElement);
        if (parsedQueryString.getOrderByElements() != null) {
            for (Object el : parsedQueryString.getOrderByElements()) {
                OrderByElement toAdd = (OrderByElement) el;
                if (toAdd.getExpression() instanceof net.sf.jsqlparser.schema.Column) {
                    net.sf.jsqlparser.schema.Column column = (net.sf.jsqlparser.schema.Column) toAdd.getExpression();
                    if (StringUtils.isEmpty(column.getTable().getName()) && propertyName.equals(column.getColumnName())) {
                        // do not add
                        continue;
                    }
                }
                orderByElements.add(toAdd);
            }
        }
        parsedQueryString.setOrderByElements(orderByElements);
    }
    String fullQueryString = parsedQueryString.toString();
    if (fullQueryString.toLowerCase().startsWith(FAKE_SELECT_PREFIX)) {
        fullQueryString = fullQueryString.substring(FAKE_SELECT_PREFIX.length());
    }
    // merge the parameters
    ArrayList<Object> mergedParametersList = new ArrayList<Object>();
    mergedParametersList.addAll(Arrays.asList(parameters));
    mergedParametersList.addAll(Arrays.asList(criteriaParameters));
    Object[] mergedParameters = new Object[mergedParametersList.size()];
    mergedParametersList.toArray(mergedParameters);
    return new QueryStringWithParameters(fullQueryString, mergedParameters);
}

Example 4 with QueryStringWithParameters

use of com.manydesigns.elements.text.QueryStringWithParameters in project Portofino by ManyDesigns.

the class QueryUtilsTest method testMergeQueryWithAlias.

public void testMergeQueryWithAlias() throws NoSuchFieldException {
    Model model = new Model();
    Database database = new Database();
    database.setDatabaseName("db");
    model.getDatabases().add(database);
    Schema schema = new Schema(database);
    schema.setSchemaName("schema");
    database.getSchemas().add(schema);
    Table table = new Table(schema);
    table.setTableName("test_table");
    schema.getTables().add(table);
    Column column = new Column(table);
    column.setColumnName("column1");
    column.setColumnType("varchar");
    column.setLength(10);
    column.setScale(0);
    table.getColumns().add(column);
    PrimaryKey primaryKey = new PrimaryKey(table);
    PrimaryKeyColumn pkColumn = new PrimaryKeyColumn(primaryKey);
    primaryKey.getPrimaryKeyColumns().add(pkColumn);
    pkColumn.setColumnName("column1");
    table.setPrimaryKey(primaryKey);
    model.init(new PropertiesConfiguration());
    TableAccessor tableAccessor = new TableAccessor(table);
    TableCriteria criteria = new TableCriteria(table);
    criteria.eq(tableAccessor.getProperty("column1"), "123");
    // W/o select
    QueryStringWithParameters queryStringWithParameters = QueryUtils.mergeQuery("from test_table t", criteria, null);
    assertEquals("FROM test_table t WHERE t.column1 = :p1", queryStringWithParameters.getQueryString());
    queryStringWithParameters = QueryUtils.mergeQuery("from test_table t where t.foo = 1", criteria, null);
    assertEquals("FROM test_table t WHERE (t.foo = 1) AND t.column1 = :p1", queryStringWithParameters.getQueryString());
    queryStringWithParameters = QueryUtils.mergeQuery("from test_table t, other where t.foo = other.bar", criteria, null);
    assertEquals("FROM test_table t, other WHERE (t.foo = other.bar) AND t.column1 = :p1", queryStringWithParameters.getQueryString());
    queryStringWithParameters = QueryUtils.mergeQuery("from test_table t, other x where t.foo = x.bar", criteria, null);
    assertEquals("FROM test_table t, other x WHERE (t.foo = x.bar) AND t.column1 = :p1", queryStringWithParameters.getQueryString());
    // W/select
    queryStringWithParameters = QueryUtils.mergeQuery("select t from test_table t", criteria, null);
    assertEquals("SELECT t FROM test_table t WHERE t.column1 = :p1", queryStringWithParameters.getQueryString());
    queryStringWithParameters = QueryUtils.mergeQuery("select t from test_table t where t.foo = 1", criteria, null);
    assertEquals("SELECT t FROM test_table t WHERE (t.foo = 1) AND t.column1 = :p1", queryStringWithParameters.getQueryString());
    queryStringWithParameters = QueryUtils.mergeQuery("select t from test_table t, other where t.foo = other.bar", criteria, null);
    assertEquals("SELECT t FROM test_table t, other WHERE (t.foo = other.bar) AND t.column1 = :p1", queryStringWithParameters.getQueryString());
    queryStringWithParameters = QueryUtils.mergeQuery("select t from test_table t, other x where t.foo = x.bar", criteria, null);
    assertEquals("SELECT t FROM test_table t, other x WHERE (t.foo = x.bar) AND t.column1 = :p1", queryStringWithParameters.getQueryString());
    // W/multiple select
    queryStringWithParameters = QueryUtils.mergeQuery("select t, u from test_table t", criteria, null);
    assertEquals("SELECT t, u FROM test_table t WHERE t.column1 = :p1", queryStringWithParameters.getQueryString());
    queryStringWithParameters = QueryUtils.mergeQuery("select t, u from test_table t where t.foo = 1", criteria, null);
    assertEquals("SELECT t, u FROM test_table t WHERE (t.foo = 1) AND t.column1 = :p1", queryStringWithParameters.getQueryString());
    queryStringWithParameters = QueryUtils.mergeQuery("select t, u from test_table t, other where t.foo = other.bar", criteria, null);
    assertEquals("SELECT t, u FROM test_table t, other WHERE (t.foo = other.bar) AND t.column1 = :p1", queryStringWithParameters.getQueryString());
    queryStringWithParameters = QueryUtils.mergeQuery("select t, u from test_table t, other x where t.foo = x.bar", criteria, null);
    assertEquals("SELECT t, u FROM test_table t, other x WHERE (t.foo = x.bar) AND t.column1 = :p1", queryStringWithParameters.getQueryString());
}

Example 5 with QueryStringWithParameters

use of com.manydesigns.elements.text.QueryStringWithParameters in project Portofino by ManyDesigns.

the class ModelSelectionProviderSupport method createSQLOptionProvider.

@NotNull
protected OptionProvider createSQLOptionProvider(DatabaseSelectionProvider selectionProvider, Class[] fieldTypes, String name, String databaseName, String sql) {
    return new MemoizingOptionProvider(() -> {
        Session session = persistence.getSession(databaseName);
        OgnlSqlFormat sqlFormat = OgnlSqlFormat.create(sql);
        String formatString = sqlFormat.getFormatString();
        Object[] parameters = sqlFormat.evaluateOgnlExpressions(this);
        QueryStringWithParameters cacheKey = new QueryStringWithParameters(formatString, parameters);
        Collection<Object[]> objects = getFromQueryCache(selectionProvider, cacheKey);
        if (objects == null) {
            logger.debug("Query not in cache: {}", formatString);
            try {
                objects = QueryUtils.runSql(session, formatString, parameters);
            } catch (Exception e) {
                logger.error("Exception in populating selection provider " + name, e);
                return null;
            }
            putInQueryCache(selectionProvider, cacheKey, objects);
        }
        return objects.stream().map(o -> SelectionProviderLogic.getOption(fieldTypes, o)).collect(Collectors.toList());
    });
}