Parameterizing SQL on Mybatis

When parameterizing SQL on Mybatis, there are 2 ways.

1. Using #{PARAM}
2. Using ${PARAM}

When #{} is declared, it is transformed into PreparedStatement’s parameter. However, ${} is String substitution inside Mybatis. The following example shows the difference.

For the above example, the first sql parameterizes table name with #{} and the second sql with ${}

When the first sql is executed for Oracle, the following output comes out.

..
DEBUG: test.selectData - ==>  Preparing: SELECT COL2 FROM ? WHERE COL1 = ?
DEBUG: test.selectData - ==> Parameters: PARAM_TEST1(String), AAA(String)
...
### The error may exist in sqlmap.xml
### The error may involve test.selectData-Inline
### The error occurred while setting parameters
### SQL: SELECT COL2 FROM ? WHERE COL1 = ?
### Cause: java.sql.SQLSyntaxErrorException: ORA-00903: invalid table name

However, when the second sql is executed, the following output comes out. (Table name is substituted in advance)

...
DEBUG: test.selectData2 - ==>  Preparing: SELECT COL2 FROM PARAM_TEST1 WHERE COL1 = ?
DEBUG: test.selectData2 - ==> Parameters: AAA(String)
DEBUG: test.selectData2 - <==      Total: 0
...

 

Which way to choose?

Not every part of a sql can be parameterized on PreparedStatement. And the rule differ from each database. The basic rules are as follows.

• Using #{} is preferred. Some database (i.e. Oracle) uses a lot of resources when hard parsing SQL, which could cause severe overhead. If parameters are defined as ${}, every sql with different parameters is regarded as a different sql, which causes hard parsing
• Use ${} only if the database don’t support the parameter. But this exception could be allowed only if total set of parameters is limited