protected StringBuilder getPreparedStatementDatasetIDs( AbstractWhereBuilder builder ) { StringBuilder getDatasetIDs = new StringBuilder( 300 ); String rootTableAlias = builder.getAliasManager().getRootTableAlias(); getDatasetIDs.append( "SELECT DISTINCT " ); getDatasetIDs.append( rootTableAlias ); getDatasetIDs.append( '.' ); getDatasetIDs.append( idColumn ); // for SELECT DISTINCT, all ORDER BY columns have to be SELECTed as well if ( builder.getOrderBy() != null ) { // hack to transform the ORDER BY column list in select list String orderColList = builder.getOrderBy().getSQL().toString(); int i = 1; while ( orderColList.contains( " ASC" ) || orderColList.contains( "DESC" ) ) { orderColList = orderColList.replaceFirst( " ASC| DESC", " AS crit" + ( i++ ) ); } getDatasetIDs.append( ',' ); getDatasetIDs.append( orderColList ); } return getDatasetIDs; }
protected void getPSBody( AbstractWhereBuilder builder, StringBuilder getDatasetIDs ) { String rootTableAlias = builder.getAliasManager().getRootTableAlias(); getDatasetIDs.append( " FROM " ); getDatasetIDs.append( mainTable ); getDatasetIDs.append( " " ); getDatasetIDs.append( rootTableAlias ); for ( PropertyNameMapping mappedPropName : builder.getMappedPropertyNames() ) { for ( Join join : mappedPropName.getJoins() ) { getDatasetIDs.append( " LEFT OUTER JOIN " ); getDatasetIDs.append( join.getToTable() ); getDatasetIDs.append( ' ' ); getDatasetIDs.append( join.getToTableAlias() ); getDatasetIDs.append( " ON " ); getDatasetIDs.append( join.getSQLJoinCondition() ); } } if ( builder.getWhere() != null ) { getDatasetIDs.append( " WHERE " ); getDatasetIDs.append( builder.getWhere().getSQL() ); } }
protected SQLExpression toProtoSQL( Expression expr, boolean assertNotMultiValued ) throws UnmappableException, FilterEvaluationException { SQLExpression sql = toProtoSQL( expr ); if ( assertNotMultiValued ) { assertNotMultiValued( sql ); } return sql; }
String ftTableAlias = wb.getAliasManager().getRootTableAlias(); LOG.debug( "WHERE clause: " + wb.getWhere() ); LOG.debug( "ORDER BY clause: " + wb.getOrderBy() ); for ( PropertyNameMapping mappedPropName : wb.getMappedPropertyNames() ) { for ( Join join : mappedPropName.getJoins() ) { sql.append( " LEFT OUTER JOIN " ); if ( wb.getWhere() != null ) { if ( blobMapping != null ) { sql.append( " AND " ); sql.append( wb.getWhere().getSQL() ); if ( wb.getOrderBy() != null ) { sql.append( " ORDER BY " ); sql.append( wb.getOrderBy().getSQL() ); if ( wb.getWhere() != null ) { for ( SQLArgument o : wb.getWhere().getArguments() ) { o.setArgument( stmt, i++ ); if ( wb.getOrderBy() != null ) { for ( SQLArgument o : wb.getOrderBy().getArguments() ) { o.setArgument( stmt, i++ ); if ( wb.getPostFilter() != null ) { LOG.debug( "Applying in-memory post-filtering." ); result = new FilteredFeatureInputStream( result, wb.getPostFilter() );
AbstractWhereBuilder wb = getWhereBuilder( ft, filter, query.getSortProperties(), conn ); if ( wb.getPostFilter() != null ) { LOG.debug( "Filter not fully mappable to WHERE clause. Need to iterate over all features to determine count." ); hits = queryByOperatorFilter( query, ftName, filter ).count(); } else { StringBuilder sql = new StringBuilder( "SELECT " ); if ( wb.getWhere() == null ) { sql.append( "COUNT(*) FROM " ); sql.append( ftMapping.getFtTable() ); sql.append( "COUNT(*) FROM (SELECT DISTINCT " ); String ftTableAlias = wb.getAliasManager().getRootTableAlias(); for ( PropertyNameMapping mappedPropName : wb.getMappedPropertyNames() ) { for ( Join join : mappedPropName.getJoins() ) { sql.append( " LEFT OUTER JOIN " ); LOG.debug( "WHERE clause: " + wb.getWhere() ); if ( wb.getWhere() != null ) { sql.append( " WHERE " ); sql.append( wb.getWhere().getSQL() ); if ( wb.getWhere() != null ) { for ( SQLArgument o : wb.getWhere().getArguments() ) { o.setArgument( stmt, i++ );
if ( builder.getOrderBy() != null ) { idSelect.append( " ORDER BY " ); idSelect.append( builder.getOrderBy().getSQL() ); if ( builder.getOrderBy() != null ) { outerSelect.append( " ORDER BY " ); String sortCols = builder.getOrderBy().getSQL().toString(); String rootTableQualifier = builder.getAliasManager().getRootTableAlias() + "."; int columnCount = StringUtils.count( sortCols, "," ) + 1; int rootAliasCount = StringUtils.count( sortCols, rootTableQualifier ); String colRegEx = builder.getAliasManager().getRootTableAlias() + ".\\S+"; for ( int i = 1; i <= columnCount; i++ ) { sortCols = sortCols.replaceFirst( colRegEx, "crit" + i ); if ( builder.getWhere() != null ) { for ( SQLArgument o : builder.getWhere().getArguments() ) { o.setArgument( preparedStatement, i++ ); arguments.add( o.getValue() ); if ( builder.getOrderBy() != null ) { for ( SQLArgument o : builder.getOrderBy().getArguments() ) { o.setArgument( preparedStatement, i++ ); arguments.add( o.getValue() );
stmt = connection.prepareStatement( header.toString() ); int i = 1; if ( builder.getWhere() != null ) { for ( SQLArgument o : builder.getWhere().getArguments() ) { o.setArgument( stmt, i++ ); if ( builder.getOrderBy() != null ) { for ( SQLArgument o : builder.getOrderBy().getArguments() ) { o.setArgument( stmt, i++ );
getDatasetIDs.append( "SELECT " ); getDatasetIDs.append( "COUNT( DISTINCT(" ); getDatasetIDs.append( builder.getAliasManager().getRootTableAlias() ); getDatasetIDs.append( "." ); getDatasetIDs.append( idColumn ); if ( builder.getWhere() != null ) { for ( SQLArgument o : builder.getWhere().getArguments() ) { o.setArgument( preparedStatement, i++ ); arguments.add( o.getValue() );
public static String repairAliasesInWhereClause( AbstractWhereBuilder builder, List<Join> usedJoins, List<Join> redundantJoins ) { String whereClause = builder.getWhere().getSQL().toString(); for ( Join redundantJoin : redundantJoins ) { Join usedJoin = getEquivalentJoin( redundantJoin, usedJoins ); String usedAlias = usedJoin.getToTableAlias(); String redundantAlias = redundantJoin.getToTableAlias(); whereClause = whereClause.replace( redundantAlias, usedAlias ); } return whereClause; }
PropertyIsBetween propIsBetween = (PropertyIsBetween) op; SQLOperationBuilder builder = new SQLOperationBuilder( BOOLEAN ); SQLExpression lower = toProtoSQL( propIsBetween.getLowerBoundary(), true ); SQLExpression expr = toProtoSQL( propIsBetween.getExpression(), true ); SQLExpression upper = toProtoSQL( propIsBetween.getUpperBoundary(), true ); inferType( lower, expr, upper ); builder.add( "(" ); addExpression( builder, lower, op.isMatchCase() ); builder.add( " <= " ); addExpression( builder, expr, op.isMatchCase() ); builder.add( " AND " ); addExpression( builder, expr, op.isMatchCase() ); builder.add( " <= " ); addExpression( builder, upper, op.isMatchCase() ); builder.add( ")" ); sqlOper = builder.toOperation(); SQLExpression param1 = toProtoSQL( propIsEqualTo.getParameter1() ); SQLExpression param2 = toProtoSQL( propIsEqualTo.getParameter2() ); if ( !param1.isMultiValued() && !param2.isMultiValued() ) { inferType( param1, param2 ); SQLOperationBuilder builder = new SQLOperationBuilder( BOOLEAN ); addExpression( builder, param1, op.isMatchCase() ); builder.add( " = " ); addExpression( builder, param2, op.isMatchCase() ); sqlOper = builder.toOperation(); } else { Expression literal = propIsEqualTo.getParameter2();
boolean isConstant = appendParamsFromFunction( function, params ); SQLFunctionProvider sqlFunction = SQLFunctionManager.getFunctionProvider( function.getName() ); if ( sqlFunction != null ) { } else if ( isConstant ) { TypedObjectNode value = evaluateFunction( function, params ); if ( value instanceof Geometry ) { return new SQLArgument( (Geometry) value, null );
if ( wb.getWhere() != null ) { idSelect.append( " WHERE " ).append( wb.getWhere().getSQL() ); if ( wb.getOrderBy() != null ) { idSelect.append( " ORDER BY " ); idSelect.append( wb.getOrderBy().getSQL() ); if ( wb.getWhere() != null ) { for ( SQLArgument argument : wb.getWhere().getArguments() ) { argument.setArgument( stmt, i++ ); if ( wb.getOrderBy() != null ) { for ( SQLArgument argument : wb.getOrderBy().getArguments() ) { argument.setArgument( stmt, i++ );
OperatorFilter bboxFilter = new OperatorFilter( query.getPrefilterBBox() ); wb = getWhereBuilderBlob( bboxFilter, conn ); LOG.debug( "WHERE clause: " + wb.getWhere() ); String alias = wb != null ? wb.getAliasManager().getRootTableAlias() : "X1"; if ( wb != null ) { sql.append( " AND " ); sql.append( wb.getWhere().getSQL() ); stmt.setShort( i++, getSchema().getFtId( ftName ) ); if ( wb != null ) { for ( SQLArgument o : wb.getWhere().getArguments() ) { o.setArgument( stmt, i++ );
OperatorFilter bboxFilter = new OperatorFilter( query.getPrefilterBBox() ); wb = getWhereBuilderBlob( bboxFilter, conn ); LOG.debug( "WHERE clause: " + wb.getWhere() ); String alias = wb != null ? wb.getAliasManager().getRootTableAlias() : "X1"; for ( PropertyNameMapping mappedPropName : wb.getMappedPropertyNames() ) { for ( Join join : mappedPropName.getJoins() ) { sql.append( " LEFT OUTER JOIN " ); if ( wb != null ) { sql.append( " AND " ); sql.append( wb.getWhere().getSQL() ); for ( SQLArgument o : wb.getWhere().getArguments() ) { o.setArgument( stmt, i++ );