2WaySQL

What's 2WaySQL?

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

Parameter

You can specify the parameter with /*parameter name*/ comment. Here is the most simple example of parameter comment usage:

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

Mirage replaces /*author*/ to a place holder and removes a word ('Naoki Takezoe') after replaced SQL comment. Actually, Mirage 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 replaces /*$orderByColumn*/ with given parameter string. This is mere string replacement, not place holder. So Mirage checks whether the semicolon is included in the string. If semicolon is included, Mirage 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.