2WaySQL

What's 2WaySQL?

2WaySQL is just a plain old SQL template. You can specify parameters and conditions using SQL comments. So the template is executable using SQL client tools. This feature is based on the Japanese O/R mapping framework S2JDBC.

Parameter

You can specify parametes with the /*parameter_name*/ comment. This comment block might not contain spaces or DB specific optimizer hints, otherwise the Mirage-SQL parser won't recognize it as a parameter.

Here is the most simple example of parameter comment usage:

SELECT * FROM BOOK
WHERE AUTHOR = /*author*/'Naoki Takezoe'
ORDER BY BOOK_ID ASC

Mirage-SQL replaces all parameters like /*author*/ with a place holders (default to ?) and removes the word ('Naoki Takezoe') after replaced SQL comment. Actually, Mirage-SQL would execute the following SQL.

SELECT * FROM BOOK
WHERE AUTHOR = ?
ORDER BY BOOK_ID ASC

String Replacement

/*$parameter name*/ makes string replacement. See the following example:

SELECT * FROM BOOK
ORDER BY /*$orderByColumn*/BOOK_ID ASC

Mirage-SQL replaces /*$orderByColumn*/ with given parameter string. This is mere string replacement, not place holder. So Mirage-SQL checks whether the semicolon is included in the string. If semicolon is included, Mirage-SQL throws an exception.

IF, ELSE, END

You can assemble SQL dynamically using IF, ELSE and END comment. The following SQL is a example of IF comment usage. IF condition is written as OGNL expression.

SELECT * FROM BOOK
/*IF author != null */
  WHERE AUTHOR = /*author*/'Naoki Takezoe'
/*END*/
ORDER BY BOOK_ID ASC

ELSE comment is a little special. You have to write it as single line comment (not block comment) because it becomes an executable SQL. See the following example. When you execute it on SQL client tool, ELSE statement is disabled because it's written as single line comment.

SELECT * FROM BOOK
WHERE
/*IF published == true*/
       PUBLISH_DATE <= SYSDATE
--ELSE PUBLISH_DATE >  SYSDATE
/*END*/
ORDER BY BOOK_ID ASC

BEGIN, END

At first, see the following SQL:

SELECT * FROM BOOK
WHERE
/*IF minPrice != null*/
      PRICE >= /*minPrice*/1000
/*END*/
/*IF maxPrice != null*/
  AND PRICE <= /*maxPrice*/2000
/*END*/

When salaryMin is null and salaryMax is not null, this SQL becomes invalid as follows:

SELECT * FROM BOOK
WHERE
AND PRICE <= ?

Of course, when both of salaryMin and salaryMax are null, it's similar to the above.

SELECT * FROM BOOK
WHERE

In these cases, add BEGIN and END comment as follows:

SELECT * FROM BOOK
/*BEGIN*/
  WHERE
  /*IF minProce != null*/
        PRICE >= /*minPrice*/1000
  /*END*/
  /*IF maxPrice != null*/
    AND PRICE <= /*maxPrice*/2000
  /*END*/
/*END*/

BEGIN - END comment cuts the enclosed range when the all inner expressions are false. And it also removes AND if it's in a first approved expression.

As a result, the conditional expression can be written well by BEGIN - END comment and IF - END comment combination.