/** * Creates an expression for matching a column with a constant pattern.<p> * * @param column the column name * @param str the pattern string * * @return the query expression */ public static CmsSimpleQueryFragment columnLike(String column, String str) { return new CmsSimpleQueryFragment(column + " LIKE ? ", str); } }
/** * Creates an expression for comparing a column with a constant.<p> * * @param column the column name * @param o the constant * * @return the query expression */ public static CmsSimpleQueryFragment columnEquals(String column, Object o) { return new CmsSimpleQueryFragment(column + " = ?", o); }
/** * Sets the SQL used for the ORDER BY clause.<p> * * @param ordering the SQL used for the ORDER BY clause */ public void setOrdering(String ordering) { if (ordering != null) { m_ordering = new CmsSimpleQueryFragment(ordering, Collections.<Object> emptyList()); } else { m_ordering = null; } }
/** * @see org.opencms.db.generic.CmsUserQueryBuilder#createFlagCondition(org.opencms.db.CmsSelectQuery.TableAlias, int) */ @Override protected I_CmsQueryFragment createFlagCondition(TableAlias users, int flags) { return new CmsSimpleQueryFragment( "BITAND(" + users.column("USER_FLAGS") + ", ?) = ? ", new Integer(flags), new Integer(flags)); }
/** * 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)); }
/** * @see org.opencms.db.generic.CmsUserQueryBuilder#createFlagCondition(org.opencms.db.CmsSelectQuery.TableAlias, int) */ @Override protected I_CmsQueryFragment createFlagCondition(TableAlias users, int flags) { return new CmsSimpleQueryFragment( "BITAND(" + users.column("USER_FLAGS") + ", ?) = ? ", new Integer(flags), new Integer(flags)); }
/** * @see org.opencms.db.generic.CmsUserQueryBuilder#createFlagCondition(org.opencms.db.CmsSelectQuery.TableAlias, int) */ @Override protected I_CmsQueryFragment createFlagCondition(TableAlias users, int flags) { return new CmsSimpleQueryFragment( "BITAND(" + users.column("USER_FLAGS") + ", ?) = ? ", new Integer(flags), new Integer(flags)); }
/** * Creates an SQL flag check condition.<p> * * @param users the user table alias * @param flags the flags to check * * @return the resulting SQL expression */ protected I_CmsQueryFragment createFlagCondition(TableAlias users, int flags) { return new CmsSimpleQueryFragment( users.column(colFlags()) + " & ? = ? ", new Integer(flags), new Integer(flags)); }
/** * Creates a core user check condition.<p> * * @param users the user table alias * * @return the resulting SQL expression */ protected I_CmsQueryFragment createCoreCondition(TableAlias users) { return new CmsSimpleQueryFragment(users.column(colFlags()) + " <= " + I_CmsPrincipal.FLAG_CORE_LIMIT); }
/** * @see org.opencms.db.generic.CmsUserQueryBuilder#createFlagCondition(org.opencms.db.CmsSelectQuery.TableAlias, int) */ @Override protected I_CmsQueryFragment createFlagCondition(TableAlias users, int flags) { String conditionStr = internalCreateFlagCondition(users.column(colFlags()), flags); return new CmsSimpleQueryFragment(conditionStr); }
/** * 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(", "); }
/** * @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); } } }
/** * 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); } }
TableAlias groupUsers = select.addTable(tabGroupUsers(), "groupusrs"); select.addCondition( new CmsSimpleQueryFragment(groupUsers.column(colGroupUserGroupId()) + " = ? ", groupId.toString())); select.addCondition( new CmsSimpleQueryFragment(groupUsers.column(colGroupUserUserId()) + " = " + users.column(colId()))); if (searchParams.isFilterByGroupOu()) { select.addCondition(new CmsSimpleQueryFragment(users.column(colOu()) + " = ? ", group.getOuFqn())); CmsSimpleQueryFragment notGroupCondition = new CmsSimpleQueryFragment( "NOT EXISTS (SELECT " + getGroupUserSubqueryColumns() for (CmsGroup grp : anyGroups) { groupClause.add( new CmsSimpleQueryFragment("GU." + colGroupUserGroupId() + " = ?", grp.getId().toString())); new CmsSimpleQueryFragment( "EXISTS (SELECT " + getGroupUserSubqueryColumns() + " AND ")); existsClause.add(groupClause); existsClause.add(new CmsSimpleQueryFragment(" ) ")); select.addCondition(existsClause); for (CmsGroup grp : notAnyGroups) { groupClause.add( new CmsSimpleQueryFragment("GU." + colGroupUserGroupId() + " = ?", grp.getId().toString()));
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, searchFilter)); break; case orgUnit: searchCondition.add(new CmsSimpleQueryFragment( wrapLower(users.column(colOu()), caseInsensitive) + like, searchFilter));