Package com.randomnoun.common.jexl.sql

Class SqlGenerator

java.lang.Object
com.randomnoun.common.jexl.visitor.GJDepthFirst<Object,EvalContext>
com.randomnoun.common.jexl.eval.Evaluator
com.randomnoun.common.jexl.sql.SqlGenerator
All Implemented Interfaces:
GJVisitor<Object,EvalContext>
public class SqlGenerator extends Evaluator
This class traverses a TopLevelExpression object and translates the AST graph into a text representation that forms the 'WHERE' clause of an SQL query.

Only a subset of expressions are supported by the SQL Generator; an EvalException is thrown if an invalid expression is supplied.

The context passed into this class contains:

  • internal parameters that effect how the SQL is generated (such as the database vendor)
  • evaluatable functions (run at translation time)
  • translatable functions (converted into SQL at translation time)
  • database columns used in the SQL
  • variables (which are substituted at translation time into the value of that variable)
  • positional parameters (which are substituted at execution time into JDBC positional parameters)

Variables

It's probably worth going into the difference between the two different types of variables. If I have a variable that I know the value of at translation time; e.g. the variable messageVar is MT500, I can generate standalone SQL that uses this variable: 
   // [1] parse the expression into an AST
   String exprString = "externalMessageType == messageVar";
   ExpressionParser parser = new ExpressionParser(new StringReader(exprString));
   TopLevelExpression expr = parser.TopLevelExpression();

   // [2] set up an evaluation context to define variables
   EvalContext context = new EvalContext();

   // [3] set up database columns used in the query
   //     note the first 'externalMessageType' in this line refers to the name
   //     of the column in exprString, and the second 'externalMessageType' refers
   //     to the name of the column in the table we're querying. It's clearer
   //     if these are set to the same value, but there's no other reason they have to.
   context.setVariable("externalMessageType", new SqlColumn("externalMessageType"));

   // [4] set up variables used in the query
   context.setVariable("messageVar", "MT500");

   // [5] generate the SQL
   SqlGenerator generator = new SqlGenerator();
   String result = (String) generator.visit(expr, context);

The result here being externalMessageType = 'MT500'.

If, however, I won't know messageVar until execution time, I can still generate the SQL using a PositionalParameter object: 

   // parse the expression and setup context as per [1], [2] and [3] above

   // [4] set up an evaluation context to define variables
   context.setVariable("messageVar", new PositionalParameter("messageVar"));

   // [5] generate the SQL as above
   SqlGenerator generator = new SqlGenerator();
   String result = (String) generator.visit(expr, context);
The result here being externalMessageType = ? instead. The value of the '?' in the result is supplied by the program at execution time, using standard JDBC positional parameters. But, you're probably thinking, how do I know which positional parameter to use? After all, I could have had more than one PositionalParameter's in the context, and they may appear in any order or any number of times in the result String. To determine how to supply these values to JDBC, the context variable VAR_PARAMETERS is set to a List of Strings, each being the name of the positional parameter above. In the example above, if we then executed 
   List parameList = (List) context.getVariable(SqlGenerator.VAR_PARAMETERS);
this will set paramList to a one-element list, containing the String "messageVar" (corresponding to the single '?' in the result String). We can iterate over this list to create the actual parameters to pass into JDBC when running the query.

Static dates

If a date is inserted into an SQL query, we need to know which database we're running on, since each vendor seems to have their own way of representing fixed dates. This is set using the VAR_DATABASE_TYPE variable in the ExpressionContext, which is set to either DATABASE_ORACLE, DATABASE_DB2, DATABASE_SQLSERVER or DATABASE_MYSQL.

Evaluatable functions

Any functions which implement EvalFunction are evaluated at translation time, (i.e. are not translated into SQL). This is useful for things like nextWorkingDay(), which need to execute before the SQL is passed through to the database.

Translatable functions

Any functions which implement SqlFunction are translated into SQL and evaluated by the database. This is useful for things like between() and like(), which are converted into relational operators (< and >) and LIKE operators.

If a function implements both EvalFunction and SqlFunction, then this class will always attempt to translate the function into SQL using the SqlFunction interface.

Database columns

If a column is to be referenced from within the SQL, it must be set up in the EvalContext as well (as per the 'externalMessageType' lines in the previous example).

Column types

If not specified, the type of the column is VARCHAR, although this can be set in the constructor: 
   SqlColumn arrivalDate = new SqlColumn("arrivalDate", SqlColumn.DATE);

(All java.sql.Types.* constants are supported here).

Column tables

If our resultset JOINs many tables, and we wish to retrieve a value from a specific table, that can also be placed in the SqlColumn constructor. 

   // e.g. SELECT tableA.id, tableB.id FROM tableA, tableB WHERE tableA.x = tableB.x
   SqlColumn tableA_id = new SqlColumn("id", "tableA");
   SqlColumn tableB_id = new SqlColumn("id", "tableB");

FixedDate values

Another custom data type is the 'fixed date', which is a date represented as a string, in YYYYMMDD format. It is treated internally as identical to a VARCHAR column, but allows operations on these columns to be aware that the information being stored in it is a date.

Other references:

  • DB2 date syntax: http://www-106.ibm.com/developerworks/db2/library/techarticle/0211yip/0211yip3.html
  • SQL date syntax: http://msdn.microsoft.com/library/en-us/tsqlref/ts_ca-co_2f3o.asp
  • Oracle date syntax: http://www-db.stanford.edu/~ullman/fcdb/oracle/or-time.html
Author:
knoxg