Package org.geotools.data.jdbc

Class FilterToSQL

Object
FilterToSQL
All Implemented Interfaces:
ExpressionVisitor, FilterVisitor
Direct Known Subclasses:
DuckDBFilterToSQL, H2FilterToSQL, H2GISFilterToSQL, InformixFilterToSQL, MySQLFilterToSQL, NestedFilterToSQL, PostgisFilterToSQL, PreparedFilterToSQL, SingleStoreFilterToSQL, SQLServerFilterToSQL
public class FilterToSQL extends Object implements FilterVisitor, ExpressionVisitor
Encodes a filter into a SQL WHERE statement. It should hopefully be generic enough that any SQL database will work with it. This generic SQL encoder should eventually be able to encode all filters except Geometry Filters. This is because the OGC's SFS for SQL document specifies two ways of doing SQL databases, one with native geometry types and one without. To implement an encoder for one of the two types simply subclass off of this encoder and put in the proper GeometryFilter visit method. Then add the filter types supported to the capabilities by overriding the {createFilterCapabilities() method.

The output Writer can be provided either through the constructor, or with a setWriter(Writer). It must be provided before a call to encode.

Note that the class isn't thread-safe and a new object should be created for each call to any of the encode functions.

Author:
originally by Chris Holmes, TOPP, ported by Saul Farber, MassGIS

  • Field Details

    • IO_ERROR

      protected static final String IO_ERROR
      error message for exceptions
      See Also:

    • filterFactory

      protected static FilterFactory filterFactory
      filter factory

    • capabilities

      protected FilterCapabilities capabilities
      The filter types that this class can encode

    • LOGGER

      protected static Logger LOGGER
      Standard java logger

    • sqlNameEscape

      protected String sqlNameEscape
      Character used to escape database schema, table and column names

    • out

      protected Writer out
      where to write the constructed string from visiting the filters.

    • primaryKey

      protected PrimaryKey primaryKey
      The primary key corresponding to the table the filter is being encoded against.

    • databaseSchema

      protected String databaseSchema
      The schema that contains the table the filter being encoded against.

    • featureType

      protected SimpleFeatureType featureType
      the schmema the encoder will be used to be encode sql for

    • encodingFunction

      protected boolean encodingFunction
      flag which indicates that the encoder is currently encoding a function

    • currentGeometry

      protected GeometryDescriptor currentGeometry
      the geometry descriptor corresponding to the current binary spatial filter being encoded

    • currentSRID

      protected Integer currentSRID
      the srid corresponding to the current binary spatial filter being encoded

    • currentDimension

      protected Integer currentDimension
      The dimension corresponding to the current binary spatial filter being encoded

    • inline

      protected boolean inline
      inline flag, controlling whether "WHERE" will prefix the SQL encoded filter

    • inEncodingEnabled

      protected boolean inEncodingEnabled
      Whether the encoder should try to encode "in" function into a SQL IN operator

    • escapeBackslash

      protected boolean escapeBackslash
      Whether to escape backslash characters in string literals

    • fieldEncoder

      protected FilterToSQL.FieldEncoder fieldEncoder
      Current field encoder

  • Constructor Details

    • FilterToSQL

      public FilterToSQL()
      Default constructor

    • FilterToSQL

      public FilterToSQL(Writer out)

  • Method Details

    • setWriter

      public void setWriter(Writer out)
      Sets the writer the encoder will write to.

    • setInline

      public void setInline(boolean inline)

    • isInEncodingEnabled

      public boolean isInEncodingEnabled()
      Whether "in" like functions are encoded in SQL or not
      Returns:
      a boolean value, true if the in linke functions are encoded

    • setInEncodingEnabled

      public void setInEncodingEnabled(boolean inEncodingEnabled)
      Makes the encoder translate in like functions into SQL (see also InFunction.isInFunction(Expression)
      Parameters:
      inEncodingEnabled - the new flag controlling in encoding

    • isEscapeBackslash

      public boolean isEscapeBackslash()
      Returns:
      whether to escape backslash characters in string literals

    • setEscapeBackslash

      public void setEscapeBackslash(boolean escapeBackslash)
      Parameters:
      escapeBackslash - whether to escape backslash characters in string literals

    • encode

      public void encode(Filter filter) throws FilterToSQLException
      Performs the encoding, sends the encoded sql to the writer passed in.
      Parameters:
      filter - the Filter to be encoded.
      Throws:
      FilterToSQLException - If filter type not supported, or if there were io problems.

    • encodeToString

      public String encodeToString(Filter filter) throws FilterToSQLException
      purely a convenience method.

      Equivalent to:

      StringWriter out = new StringWriter(); new FilterToSQL(out).encode(filter); out.getBuffer().toString();

      Returns:
      a string representing the filter encoded to SQL.
      Throws:
      FilterToSQLException

    • encode

      public void encode(Expression expression) throws FilterToSQLException
      Performs the encoding, sends the encoded sql to the writer passed in.
      Parameters:
      expression - the Expression to be encoded.
      Throws:
      FilterToSQLException - If filter type not supported, or if there were io problems.

    • encodeToString

      public String encodeToString(Expression expression) throws FilterToSQLException
      purely a convenience method.

      Equivalent to:

      StringWriter out = new StringWriter(); new FilterToSQL(out).encode(filter); out.getBuffer().toString();

      Returns:
      a string representing the filter encoded to SQL.
      Throws:
      FilterToSQLException

    • setFeatureType

      public void setFeatureType(SimpleFeatureType featureType)
      Sets the featuretype the encoder is encoding sql for.

      This is used for context for attribute expressions when encoding to sql.

    • getFeatureType

      public SimpleFeatureType getFeatureType()
      Returns the feature type set in this encoder, if any

    • getPrimaryKey

      public PrimaryKey getPrimaryKey()

    • setPrimaryKey

      public void setPrimaryKey(PrimaryKey primaryKey)

    • getDatabaseSchema

      public String getDatabaseSchema()

    • setDatabaseSchema

      public void setDatabaseSchema(String databaseSchema)

    • createFilterCapabilities

      protected FilterCapabilities createFilterCapabilities()
      Sets the capabilities of this filter.
      Returns:
      FilterCapabilities for this Filter

    • getCapabilities

      public final FilterCapabilities getCapabilities()
      Describes the capabilities of this encoder.

      Performs lazy creation of capabilities. If you're subclassing this class, override createFilterCapabilities to declare which filtercapabilities you support. Don't use this method.

      Returns:
      The capabilities supported by this encoder.

    • setCapabilities

      public void setCapabilities(FilterCapabilities capabilities)
      Sets the capabilities for the encoder.

    • visit

      public Object visit(ExcludeFilter filter, Object extraData)
      Description copied from interface: FilterVisitor
      Visit Filter.EXCLUDE (often used during data structure transformations).
      Specified by:
      visit in interface FilterVisitor
      Parameters:
      filter - the filter to be visited
      extraData - Value object provided to visitor
      Returns:
      subclass supplied

    • visit

      public Object visit(IncludeFilter filter, Object extraData)
      Description copied from interface: FilterVisitor
      Visit Filter.INCLUDE (often used during data structure transformations).
      Specified by:
      visit in interface FilterVisitor
      Parameters:
      filter - the filter to be visited
      extraData - Value object provided to visitor
      Returns:
      subclass supplied

    • visit

      public Object visit(PropertyIsBetween filter, Object extraData) throws RuntimeException
      Writes the SQL for the PropertyIsBetween Filter.
      Specified by:
      visit in interface FilterVisitor
      Parameters:
      filter - the Filter to be visited.
      Throws:
      RuntimeException - for io exception with writer

    • visit

      public Object visit(PropertyIsLike filter, Object extraData)
      Writes the SQL for the Like Filter. Assumes the current java implemented wildcards for the Like Filter: . for multi and .? for single. And replaces them with the SQL % and _, respectively.
      Specified by:
      visit in interface FilterVisitor
      Parameters:
      filter - the Like Filter to be visited.
      TODO:
      REVISIT: Need to think through the escape char, so it works right when Java uses one, and escapes correctly with an '_'.

    • processLikeLeftOperand

      protected void processLikeLeftOperand(Object extraData, boolean matchCase, Expression att, Class attributeType) throws IOException
      Throws:
      IOException

    • visit

      public Object visit(And filter, Object extraData)
      Write the SQL for an And filter
      Specified by:
      visit in interface FilterVisitor
      Parameters:
      filter - the filter to visit
      extraData - extra data (unused by this method)

    • visit

      public Object visit(Not filter, Object extraData)
      Write the SQL for a Not filter
      Specified by:
      visit in interface FilterVisitor
      Parameters:
      filter - the filter to visit
      extraData - extra data (unused by this method)

    • visit

      public Object visit(Or filter, Object extraData)
      Write the SQL for an Or filter
      Specified by:
      visit in interface FilterVisitor
      Parameters:
      filter - the filter to visit
      extraData - extra data (unused by this method)

    • visit

      protected Object visit(BinaryLogicOperator filter, Object extraData)
      Common implementation for BinaryLogicOperator filters. This way they're all handled centrally.
      Parameters:
      filter - the logic statement to be turned into SQL.
      extraData - extra filter data. Not modified directly by this method.

    • visit

      public Object visit(PropertyIsEqualTo filter, Object extraData)
      Write the SQL for this kind of filter
      Specified by:
      visit in interface FilterVisitor
      Parameters:
      filter - the filter to visit
      extraData - extra data (unused by this method)

    • visit

      public Object visit(PropertyIsGreaterThanOrEqualTo filter, Object extraData)
      Write the SQL for this kind of filter
      Specified by:
      visit in interface FilterVisitor
      Parameters:
      filter - the filter to visit
      extraData - extra data (unused by this method)

    • visit

      public Object visit(PropertyIsGreaterThan filter, Object extraData)
      Write the SQL for this kind of filter
      Specified by:
      visit in interface FilterVisitor
      Parameters:
      filter - the filter to visit
      extraData - extra data (unused by this method)

    • visit

      public Object visit(PropertyIsLessThan filter, Object extraData)
      Write the SQL for this kind of filter
      Specified by:
      visit in interface FilterVisitor
      Parameters:
      filter - the filter to visit
      extraData - extra data (unused by this method)

    • visit

      public Object visit(PropertyIsLessThanOrEqualTo filter, Object extraData)
      Write the SQL for this kind of filter
      Specified by:
      visit in interface FilterVisitor
      Parameters:
      filter - the filter to visit
      extraData - extra data (unused by this method)

    • visit

      public Object visit(PropertyIsNotEqualTo filter, Object extraData)
      Write the SQL for this kind of filter
      Specified by:
      visit in interface FilterVisitor
      Parameters:
      filter - the filter to visit
      extraData - extra data (unused by this method)

    • visitBinaryComparisonOperator

      protected void visitBinaryComparisonOperator(BinaryComparisonOperator filter, Object extraData) throws RuntimeException
      Common implementation for BinaryComparisonOperator filters. This way they're all handled centrally. DJB: note, postgis overwrites this implementation because of the way null is handled. This is for filters and filters are handled. They will come here with "property = null". NOTE: SELECT * FROM WHERE isnull; -- postgresql SELECT * FROM
      WHERE isnull(); -- oracle???
      Parameters:
      filter - the comparison to be turned into SQL.
      Throws:
      RuntimeException - for io exception with writer

    • encodeBinaryComparisonOperator

      protected void encodeBinaryComparisonOperator(BinaryComparisonOperator filter, Object extraData, Expression left, Expression right, Class leftContext, Class rightContext)
      Encode a BinaryComparisonOperator to SQL
      Parameters:
      filter - the comparison operator to be turned to SQL
      extraData - extraData
      left - left parameter of the binary operator
      right - right parameter of the binary operator
      leftContext - expression type of the right parameter used as context for the left parameter
      rightContext - expression type of the left parameter used as context for the right parameter

    • writeBinaryExpressionMember

      protected void writeBinaryExpressionMember(Expression exp, Class context) throws IOException
      Writes out an expression, wrapping it in parenthesis if it's a binary one
      Throws:
      IOException

    • getExpressionType

      public Class getExpressionType(Expression expression)
      Returns the expression type, if can be found, or null otherwise. Based on feature type inspection for attribute references, and function return types otherwise. Cannot yet handle arithmetics or literals (the latter are actually not useful and should be ignored, expression in geotools are free to use a string where a number is needed due to the on the fly conversion, here we are concerned only with types that are a reliable reference).
      Returns:
      The expression return type, or null if cannot be computed

    • writeBinaryExpression

      protected void writeBinaryExpression(Expression e, Class context) throws IOException
      Throws:
      IOException

    • getFunctionReturnType

      protected Class getFunctionReturnType(Function f)

    • isBinaryExpression

      protected boolean isBinaryExpression(Expression e)

    • visit

      public Object visit(PropertyIsNull filter, Object extraData) throws RuntimeException
      Writes the SQL for the Null Filter.
      Specified by:
      visit in interface FilterVisitor
      Parameters:
      filter - the null filter to be written to SQL.
      Throws:
      RuntimeException - for io exception with writer

    • visit

      public Object visit(PropertyIsNil filter, Object extraData)
      Specified by:
      visit in interface FilterVisitor

    • visit

      public Object visit(Id filter, Object extraData)
      Encodes an Id filter
      Specified by:
      visit in interface FilterVisitor
      Parameters:
      filter - the
      Throws:
      RuntimeException - If there's a problem writing output

    • visit

      public Object visit(BBOX filter, Object extraData)
      Specified by:
      visit in interface FilterVisitor

    • visit

      public Object visit(Beyond filter, Object extraData)
      Specified by:
      visit in interface FilterVisitor

    • visit

      public Object visit(Contains filter, Object extraData)
      Specified by:
      visit in interface FilterVisitor

    • visit

      public Object visit(Crosses filter, Object extraData)
      Specified by:
      visit in interface FilterVisitor

    • visit

      public Object visit(Disjoint filter, Object extraData)
      Specified by:
      visit in interface FilterVisitor

    • visit

      public Object visit(DWithin filter, Object extraData)
      Specified by:
      visit in interface FilterVisitor

    • visit

      public Object visit(Equals filter, Object extraData)
      Specified by:
      visit in interface FilterVisitor

    • visit

      public Object visit(Intersects filter, Object extraData)
      Specified by:
      visit in interface FilterVisitor

    • visit

      public Object visit(Overlaps filter, Object extraData)
      Specified by:
      visit in interface FilterVisitor

    • visit

      public Object visit(Touches filter, Object extraData)
      Specified by:
      visit in interface FilterVisitor

    • visit

      public Object visit(Within filter, Object extraData)
      Specified by:
      visit in interface FilterVisitor

    • visitBinarySpatialOperator

      protected Object visitBinarySpatialOperator(BinarySpatialOperator filter, Object extraData)

    • visitBinarySpatialOperator

      protected Object visitBinarySpatialOperator(BinarySpatialOperator filter, PropertyName property, Literal geometry, boolean swapped, Object extraData)
      Handles the common case of a PropertyName,Literal geometry binary spatial operator.

    • visitBinarySpatialOperator

      protected Object visitBinarySpatialOperator(BinarySpatialOperator filter, Expression e1, Expression e2, Object extraData)
      Handles the more general case of two generic expressions.

      The most common case is two PropertyName expressions, which happens during a spatial join.

    • visitBinaryTemporalOperator

      protected Object visitBinaryTemporalOperator(BinaryTemporalOperator filter, Object extraData)

    • visitBinaryTemporalOperator

      protected Object visitBinaryTemporalOperator(BinaryTemporalOperator filter, PropertyName property, Literal temporal, boolean swapped, Object extraData)
      Handles the common case of a PropertyName,Literal geometry binary temporal operator.

      Subclasses should override if they support more temporal operators than what is handled in this base class.

    • visitBinaryTemporalOperator

      protected Object visitBinaryTemporalOperator(BinaryTemporalOperator filter, Expression e1, Expression e2, Object extraData)
      Handles the general case of two expressions in a binary temporal filter.

      Subclasses should override if they support more temporal operators than what is handled in this base class.

    • visitNullFilter

      public Object visitNullFilter(Object extraData)
      Encodes a null filter value. The current implementation does exactly nothing.
      Specified by:
      visitNullFilter in interface FilterVisitor
      Parameters:
      extraData - extra data to be used to evaluate the filter
      Returns:
      the untouched extraData parameter

    • visit

      public Object visit(PropertyName expression, Object extraData) throws RuntimeException
      Writes the SQL for the attribute Expression.
      Specified by:
      visit in interface ExpressionVisitor
      Parameters:
      expression - the attribute to turn to SQL.
      Throws:
      RuntimeException - for io exception with writer

    • cast

      protected String cast(String encodedProperty, Class target) throws IOException
      Gives the opportunity to subclasses to force the property to the desired type. By default it simply writes out the property as-is (the property must be already escaped).
      Throws:
      IOException

    • visit

      public Object visit(Literal expression, Object context) throws RuntimeException
      Export the contents of a Literal Expresion
      Specified by:
      visit in interface ExpressionVisitor
      Parameters:
      expression - the Literal to export
      Throws:
      RuntimeException - for io exception with writer

    • evaluateLiteral

      public Object evaluateLiteral(Literal expression, Class<?> target)

    • writeLiteral

      protected void writeLiteral(Object literal) throws IOException
      Writes out a non null, non geometry literal. The base class properly handles null, numeric and booleans (true|false), and turns everything else into a string. Subclasses are expected to override this shall they need a different treatment (e.g. for dates)
      Throws:
      IOException

    • escapeLiteral

      public String escapeLiteral(String literal)
      Escapes the string literal.

    • visitLiteralGeometry

      protected void visitLiteralGeometry(Literal expression) throws IOException
      Subclasses must implement this method in order to encode geometry filters according to the specific database implementation
      Throws:
      IOException

    • visitLiteralTimePeriod

      protected void visitLiteralTimePeriod(Period expression)

    • visit

      public Object visit(Add expression, Object extraData)
      Specified by:
      visit in interface ExpressionVisitor

    • visit

      public Object visit(Divide expression, Object extraData)
      Specified by:
      visit in interface ExpressionVisitor

    • visit

      public Object visit(Multiply expression, Object extraData)
      Specified by:
      visit in interface ExpressionVisitor

    • visit

      public Object visit(Subtract expression, Object extraData)
      Specified by:
      visit in interface ExpressionVisitor

    • visit

      protected Object visit(BinaryExpression expression, String operator, Object extraData) throws RuntimeException
      Writes the SQL for the Math Expression.
      Parameters:
      expression - the Math phrase to be written.
      operator - The operator of the expression.
      Throws:
      RuntimeException - for io problems

    • visit

      public Object visit(Function function, Object extraData) throws RuntimeException
      Writes sql for a function expression. By default it will write the call by using the same arguments provided to the GeoTools function, subclasses should override on a case by case basis if this behavior is not the desired one.
      Specified by:
      visit in interface ExpressionVisitor
      Parameters:
      function - a function expression
      Throws:
      RuntimeException - If an IO error occurs.
      See Also:

    • visitInFunction

      protected void visitInFunction(Function function, boolean encodeAsExpression, boolean negate, Object extraData)
      Encodes a "in" function (as recognized by InFunction.isInFunction(Expression)

    • getParameter

      protected Expression getParameter(Function function, int idx, boolean mandatory)
      Returns the n-th parameter of a function, throwing an exception if the parameter is not there and has been marked as mandatory

    • getFunctionName

      protected String getFunctionName(Function function)
      Maps the function to the native database function name

    • visit

      public Object visit(NilExpression expression, Object extraData)
      Description copied from interface: ExpressionVisitor
      Used to visit a Expression.NIL, also called for null where an expression is expected.

      This is particularly useful when doing data transformations, as an example when using a StyleSymbolizer Expression.NIL can be used to represent the default stroke color.

      Specified by:
      visit in interface ExpressionVisitor
      Returns:
      implementation specific

    • visit

      public Object visit(After after, Object extraData)
      Specified by:
      visit in interface FilterVisitor

    • visit

      public Object visit(AnyInteracts anyInteracts, Object extraData)
      Specified by:
      visit in interface FilterVisitor

    • visit

      public Object visit(Before before, Object extraData)
      Specified by:
      visit in interface FilterVisitor

    • visit

      public Object visit(Begins begins, Object extraData)
      Specified by:
      visit in interface FilterVisitor

    • visit

      public Object visit(BegunBy begunBy, Object extraData)
      Specified by:
      visit in interface FilterVisitor

    • visit

      public Object visit(During during, Object extraData)
      Specified by:
      visit in interface FilterVisitor

    • visit

      public Object visit(EndedBy endedBy, Object extraData)
      Specified by:
      visit in interface FilterVisitor

    • visit

      public Object visit(Ends ends, Object extraData)
      Specified by:
      visit in interface FilterVisitor

    • visit

      public Object visit(Meets meets, Object extraData)
      Specified by:
      visit in interface FilterVisitor

    • visit

      public Object visit(MetBy metBy, Object extraData)
      Specified by:
      visit in interface FilterVisitor

    • visit

      public Object visit(OverlappedBy overlappedBy, Object extraData)
      Specified by:
      visit in interface FilterVisitor

    • visit

      public Object visit(TContains contains, Object extraData)
      Specified by:
      visit in interface FilterVisitor

    • visit

      public Object visit(TEquals equals, Object extraData)
      Specified by:
      visit in interface FilterVisitor

    • visit

      public Object visit(TOverlaps contains, Object extraData)
      Specified by:
      visit in interface FilterVisitor

    • setSqlNameEscape

      public void setSqlNameEscape(String escape)
      Sets the SQL name escape string.

      The value of this string is prefixed and appended to table schema names, table names and column names in an SQL statement to support mixed-case and non-English names. Without this, the DBMS may assume a mixed-case name in the query should be treated as upper-case and an SQLCODE of -204 or 206 may result if the name is not found.

      Typically this is the double-quote character, ", but may not be for all databases.

      If a name contains the escape string itself, the escape string is duplicated.

      For example, consider the following query:

      SELECT Geom FROM Spear.ArchSites May be interpreted by the database as: SELECT GEOM FROM SPEAR.ARCHSITES If the column and table names were actually created using mixed-case, the query needs to be specified as: SELECT "Geom" from "Spear"."ArchSites"

      Parameters:
      escape - the character to be used to escape database names

    • getSqlNameEscape

      public String getSqlNameEscape()
      Returns:
      the sqlNameEscape

    • escapeName

      public String escapeName(String name)
      Surrounds a name with the SQL escape string.

      If the name contains the SQL escape string, the SQL escape string is duplicated.

    • setFieldEncoder

      public void setFieldEncoder(FilterToSQL.FieldEncoder fieldEncoder)
      Set custom field encoder
      Parameters:
      fieldEncoder - the field encoder

    • getDistanceInNativeUnits

      protected double getDistanceInNativeUnits(DistanceBufferOperator operator)
      Rough evaluation of distance in the units of the current SRID, assuming that the SRID maps to a known EPSG code. Will use a rather imprecise transformation for distances over degrees, but better than nothing.

    • visit

      public Object visit(NativeFilter filter, Object data)
      Specified by:
      visit in interface FilterVisitor

      • Copyright © 1996–2025 Geotools. All rights reserved.