Skip to main content

2WaySQL

Build a list screen whose search conditions are all optional, and the WHERE clause of the SQL changes depending on whether a condition was supplied. Assembling SQL by concatenating strings in Java means you can no longer paste the resulting SQL into a database tool to check how it behaves.

The 2WaySQL approach that im_mirage adopts solves this with "SQL comments." Conditional branches and bind variable specifications are written as comments, so the file remains valid SQL and can be executed directly in a database tool. At run time, im_mirage interprets the comments and reshapes the statement into SQL that matches the conditions.

Syntax​

SyntaxPurpose
/*IF condition*/ ... /*END*/Conditional branch
/*BEGIN*/ ... /*END*/Optional block
/*parameterName*/dummyValueBind variable
IN /*parameterName*/('dummy')Expand the elements of a list into an IN clause
/*FOR element in list*/ ... /*END*/Loop
/*$parameterName*/dummyValueEmbed a value directly (without binding)

Bind Variables and Dummy Values​

A bind variable is expressed as a comment holding the parameter name, followed immediately by a dummy value.

status = /*status*/'dummy'

At run time, 'dummy' is replaced with a placeholder and the parameter value is bound. The dummy value only matters when you execute the statement directly in a database tool. Because the value is not being concatenated into the string, this is not a path for SQL injection.

Conditional Branches and Optional Blocks​

/*IF*/ emits what is inside it only when the condition holds. /*BEGIN*/ withholds the whole block from the output if none of the /*IF*/ conditions inside it hold.

SELECT
  order_id,
  customer_name,
  amount,
  status
FROM
  foo_order
/*BEGIN*/
WHERE
  /*IF status != null*/
  status = /*status*/'dummy'
  /*END*/
/*END*/
ORDER BY
  order_id

If status is null, the WHERE disappears along with it and the query returns all rows. Without the enclosing /*BEGIN*/, a bare WHERE would be left behind when no condition holds, causing a syntax error.

When you add more conditions, write AND at the start of the second and subsequent /*IF*/ blocks. /*BEGIN*/ strips a leading AND or OR left at the start of the block.

/*BEGIN*/
WHERE
  /*IF status != null*/
  status = /*status*/'dummy'
  /*END*/
  /*IF customerName != null*/
  AND customer_name = /*customerName*/'dummy'
  /*END*/
/*END*/

Do Not Nest /*IF*/ Inside /*BEGIN*/​

The parser itself supports nested /*IF*/, but when you nest them inside /*BEGIN*/, the AND or OR at the start of the inner /*IF*/ can be stripped. This happens when the outer /*IF*/ is the first condition to hold in that block, and it produces invalid SQL such as WHERE a = ? b = ?. If a condition listed earlier holds, the output is correct, so the statement passes or fails depending on the combination of parameters.

Do not nest multiple conditions; list them as siblings, and express any dependency between conditions in the condition expression.

/*IF status != null*/
status = /*status*/'dummy'
/*END*/
/*IF status != null && customerName != null*/
AND customer_name = /*customerName*/'dummy'
/*END*/

Nesting where the inner block does not start with AND, OR, or a comma does not cause this problem.

IN Clauses​

To expand the elements of a list into an IN clause, place ('dummy') immediately after the comment holding the parameter name. At run time, it expands into (?, ?, ?), with one placeholder per element.

SELECT
  order_id,
  customer_name
FROM
  foo_order
/*BEGIN*/
WHERE
  /*IF orderIds != null && orderIds.size() > 0*/
  AND order_id IN /*orderIds*/('dummy')
  /*END*/
/*END*/

When the list is null, and also when it is empty, the bind portion is not emitted. Without a guard, a bare IN is left behind and the SQL breaks, so stop both null and an empty list with /*IF*/. /*IF orderIds != null*/ alone lets an empty list through. && in the condition expression short-circuits, so size() is not evaluated even when orderIds is null.

The calling side passes a JavaBean that holds the list as a property, or a Map.

public List<OrderEntity> findByOrderIds(final List<String> orderIds) {
    final Map<String, Object> parameters = new HashMap<String, Object>();
    parameters.put("orderIds", orderIds);
    return super.sqlManager.getResultList(OrderEntity.class, SQL_PATH.concat("find-by-order-ids.sql"), parameters);
}
An Empty List Returns All Rows

When the IN clause guard is the only condition inside /*BEGIN*/, as in the example above, the WHERE disappears along with it when the list is null or empty. This is not an SQL error, but instead of "no matches," you get every row, with no filtering.

If an empty list should produce an empty result, do not rely on the guard in the SQL; check for an empty list and null in the calling repository or service, and return an empty list without running the SQL. When you pass the list of IDs a user may access to an IN clause to filter by permission, an empty list turns into exposing every row, so this check is essential.

Loops​

/*FOR*/ repeats what is inside it once per element of a list. Separate the element and the list with in or IN, surrounded by a single-byte space on each side. If the separator does not match, the result is TwoWaySQLException: For expression is invalid.

SELECT
  order_id,
  customer_name
FROM
  foo_order
/*BEGIN*/
WHERE
/*FOR keyword in keywords*/
OR customer_name LIKE /*keyword*/'%dummy%' ESCAPE '\'
/*END*/
/*END*/

If the body starts with AND, OR, or a comma, enclose it in /*BEGIN*/. A leading AND or OR is stripped only while the content of the enclosing block is still empty. Without /*BEGIN*/, the OR of the first element is left behind, producing broken SQL such as WHERE OR ....

Inside the block, the only thing you can refer to is the single element bound to the loop variable (keyword in the example above). To build an IN clause, use IN /*parameterName*/('dummy') from the previous section, not /*FOR*/.

Differences from 2WaySQL in JSSP

/*FOR*/ is available in im_mirage and IM-LogicDesigner. The 2WaySQL of the script development model (JSSP) does not support loop syntax.

The condition expression of /*IF*/ is evaluated as OGNL in im_mirage and as JavaScript in JSSP. The number of elements in a list is written orderIds.size() in im_mirage and orderIds.length in JSSP.

/*IF*/, /*BEGIN*/, and the way bind variables are written are shared, but on top of these differences, the way parameters are passed and the calling APIs also differ, so a JSSP SQL file cannot be carried over to the Java side as-is.

Embedding Values Directly​

/*$parameterName*/ does not bind the value; it concatenates it into the body of the SQL as-is. This syntax is for places where a bind variable cannot be used, such as the sort order.

ORDER BY /*$sortColumn*/order_id /*$sortOrder*/ASC

The only thing the parser rejects is a value that contains ;. OR 1=1 and UNION SELECT ... go into the SQL as-is. Use it only for dynamic table names, column names, and sort orders, and always validate the value against a whitelist. To pass a value as a parameter, use /*parameterName*/'dummy'.

Property traversal with dots goes only one level deep. /*$a.b.c*/ evaluates only as far as a.b, ignores .c and beyond, and embeds the string representation of a.b. No exception is raised when this happens.

The SET Clause of an UPDATE Statement​

When the columns to update change depending on conditions, do not leave removing commas to /*BEGIN*/. Whether a comma is removed depends on whether it sits on the same line as the /*IF*/ or on a separate line, and if no condition holds, the SET disappears along with it.

Always put a self-assignment of the primary key at the start of the SET clause, and list the remaining items with a leading comma.

UPDATE foo_order
SET
  order_id = /*orderId*/'dummy'
  , record_user_cd = /*recordUserCd*/'dummy'
  , record_date = /*recordDate*/'2000-01-01 00:00:00'
  /*IF status != null*/
  , status = /*status*/'dummy'
  /*END*/
  /*IF customerName != null*/
  , customer_name = /*customerName*/'dummy'
  /*END*/
WHERE
  order_id = /*orderId*/'dummy'

In an UPDATE statement run with sqlManager.executeUpdate, the audit fields are not set automatically. As in the example above, include the updater and update timestamp in the SET clause as well.

Do Not Write Comments in SQL Files​

Do not write -- line comments in an SQL file, nor block comments that are not 2WaySQL syntax. The 2WaySQL parser does not treat the content of -- line comments differently; it scans the whole file looking for syntax. Syntax notation such as /*IF*/, bind names, and ? written as an explanation are interpreted as real directives, causing run-time errors that seem unrelated to the comment, such as UnsupportedOperationException: not supported or 列インデックスは範囲外です.

Write the explanation of the SQL in the JavaDoc of the DAO method that uses the file.

Passing Parameters​

Make the placeholder names in the SQL match the property names or key names of the object you pass. An entity, any JavaBean, and a Map<String, Object> can all be passed.

In the condition expression of /*IF*/, write a property of the parameter, such as status != null.

Escaping in LIKE Searches​

For a value passed to the LIKE operator, the \, %, and _ characters contained in user input take effect as special characters. If a user enters % as-is, everything matches; if they enter _, it matches any single character.

Do not let the wildcards be sent from the screen; add them on the server side. On top of that, escape the special characters before adding the wildcards, and write an ESCAPE clause in the SQL.

AND customer_name LIKE /*customerName*/'%dummy%' ESCAPE '\'

Files per Database Dialect​

SQL syntax sometimes differs between database products. im_mirage automatically selects an SQL file with a different file name according to the database in use.

For a specified find-by-status.sql, it uses find-by-status_<dialect>.sql if that file exists on the classpath, and otherwise falls back to the original file.

DB productDialect name (file name suffix)
Oracleoracle
PostgreSQLpostgre
SQLServersqlserver
src/main/resources/META-INF/sql/jp/co/example/foo/infrastructure/dao/OrderDAO/
├── find-by-status.sql            … The base file
├── find-by-status_oracle.sql     … Added only when Oracle-specific syntax is required
└── find-by-status_sqlserver.sql  … Added only when SQLServer-specific syntax is required

The DAO side only needs to specify the path of the base file. Add a per-dialect file only when there is a syntax difference. Duplicating the file for every dialect when there is no difference means making the same change in several places every time you make a fix.