/** * Creates a new instance.<p> */ public CmsSelectQuery() { // always use 1 = 1 as a condition so we don't have to worry about whether we need a "WHERE" keyword m_conditions.add(new CmsSimpleQueryFragment("1 = 1", Collections.<Object> emptyList())); m_conditions.setSeparator(" AND "); m_otherClauses.setSeparator("\n"); m_columns.setSeparator(", "); }
/** * Adds OU conditions to an SQL query.<p> * * @param select the query * @param users the user table alias * @param allowedOus the allowed ous */ protected void addAllowedOuCondition( CmsSelectQuery select, TableAlias users, List<CmsOrganizationalUnit> allowedOus) { if ((allowedOus != null) && !allowedOus.isEmpty()) { CmsCompositeQueryFragment ouCondition = new CmsCompositeQueryFragment(); ouCondition.setPrefix("("); ouCondition.setSuffix(")"); ouCondition.setSeparator(" OR "); for (CmsOrganizationalUnit ou : allowedOus) { String ouName = CmsStringUtil.joinPaths("/", ou.getName()); ouCondition.add(new CmsSimpleQueryFragment(users.column(colOu()) + " = ? ", ouName)); } select.addCondition(ouCondition); } }
/** * Adds an expression which should be added as a column in the result set.<p> * * @param node the expression which should be added as a column */ public void addColumn(I_CmsQueryFragment node) { m_columns.add(node); }
/** * @see org.opencms.db.I_CmsQueryFragment#visit(org.opencms.db.CmsStatementBuilder) */ public void visit(CmsStatementBuilder builder) { if (m_useWindowFunctions) { I_CmsQueryFragment order = m_select.getOrdering(); assert order != null; CmsCompositeQueryFragment rownumFragment = new CmsCompositeQueryFragment(); rownumFragment.add(new CmsSimpleQueryFragment("ROW_NUMBER() OVER (ORDER BY ")); rownumFragment.add(order); rownumFragment.add(new CmsSimpleQueryFragment(") AS rownumber")); m_select.addColumn(rownumFragment); builder.add("SELECT * FROM ( "); m_select.visit(builder); int start = 1 + (m_pageSize * (m_page - 1)); int end = (start + m_pageSize) - 1; builder.add(")"); if (m_nameSubquery) { builder.add(" AS rnsq "); } builder.add(" WHERE rownumber BETWEEN " + start + " AND " + end); } else { m_select.visit(builder); int offset = (m_page - 1) * m_pageSize; builder.add("\nLIMIT " + m_pageSize + " OFFSET " + offset); } } }
/** * @see org.opencms.db.I_CmsQueryFragment#visit(org.opencms.db.CmsStatementBuilder) */ public void visit(CmsStatementBuilder builder) { builder.add("SELECT "); Joiner commaJoin = Joiner.on(", "); m_columns.visit(builder); builder.add("\nFROM "); builder.add(commaJoin.join(m_tables)); builder.add("\nWHERE "); m_conditions.visit(builder); if (m_ordering != null) { builder.add("\nORDER BY "); m_ordering.visit(builder); } m_otherClauses.visit(builder); }
searchFilter = searchFilter.toLowerCase(); CmsCompositeQueryFragment searchCondition = new CmsCompositeQueryFragment(); searchCondition.setSeparator(" OR "); searchCondition.setPrefix("("); searchCondition.setSuffix(")"); String matchExpr = patternExpr + like; searchFilter = "%" + CmsEncoder.escapeSqlLikePattern(searchFilter, '!') + '%'; searchCondition.add(new CmsSimpleQueryFragment(matchExpr, searchFilter)); for (SearchKey key : searchParams.getSearchKeys()) { switch (key) { case email: searchCondition.add( new CmsSimpleQueryFragment( wrapLower(users.column(colEmail()), caseInsensitive) + like, break; case orgUnit: searchCondition.add(new CmsSimpleQueryFragment( wrapLower(users.column(colOu()), caseInsensitive) + like, searchFilter));
/** * Adds another clause to the query.<p> * * @param clause the clause to add */ public void addClause(I_CmsQueryFragment clause) { m_otherClauses.add(clause); }
CmsCompositeQueryFragment groupClause = new CmsCompositeQueryFragment(); groupClause.setSeparator(" OR "); for (CmsGroup grp : anyGroups) { groupClause.add( new CmsSimpleQueryFragment("GU." + colGroupUserGroupId() + " = ?", grp.getId().toString())); CmsCompositeQueryFragment existsClause = new CmsCompositeQueryFragment(); existsClause.add( new CmsSimpleQueryFragment( "EXISTS (SELECT " + users.column(colId()) + " AND ")); existsClause.add(groupClause); existsClause.add(new CmsSimpleQueryFragment(" ) ")); select.addCondition(existsClause); CmsCompositeQueryFragment groupClause = new CmsCompositeQueryFragment(); groupClause.setPrefix("("); groupClause.setSuffix(")"); groupClause.setSeparator(" OR "); for (CmsGroup grp : notAnyGroups) { groupClause.add( new CmsSimpleQueryFragment("GU." + colGroupUserGroupId() + " = ?", grp.getId().toString())); CmsCompositeQueryFragment notExistsClause = new CmsCompositeQueryFragment(); notExistsClause.add( new CmsSimpleQueryFragment( "NOT EXISTS (SELECT "
/** * Adds a new condition to the query.<p> * * @param node the condition to add to the query */ public void addCondition(I_CmsQueryFragment node) { m_conditions.add(node); }
/** * Adds an expression which should be added as a column in the result set.<p> * * @param column the expression which should be added as a column */ public void addColumn(String column) { m_columns.add(new CmsSimpleQueryFragment(column, Collections.<Object> emptyList())); }
/** * Adds a new condition to the query.<p> * * @param fragment the condition SQL * @param params the condition parameters */ public void addCondition(String fragment, Object... params) { m_conditions.add(new CmsSimpleQueryFragment(fragment, params)); }