Tuesday, September 17, 2013

ibatis #value# vs $value$

In Ibatis, normally we use “#” to wrap the variables passed in. 

SELECT * FROM table WHERE field1= #value# 

This generates prepared statement like this

SELECT * FROM table WHERE field1=?

When JDBC executes the prepared statement, it replaces the “?” with the real variable value sequentially.

In 99% scenarios, this works fine. But it does not work for this sql.

SELECT * FROM table WHERE fieldA=”How are you?” AND field1=#value#

JDBC injects the variable value right after “How are you”. Let’s the say the variable value is “A”, the query actually executed is

SELECT * FROM table WHERE fieldA=”How are youA” AND field1=?

For sure, it fails. How to fix it? Using the $Value$. When $Value$ is used, Ibatis does not generate prepared statement. It generates the sql directly, like this

SELECT * FROM table WHERE fieldA=”How are you?” AND field1=’A’


Usually, prepared statement has better performance. But it is not always true. Here is one case I faced in my project, the sql needs to look up data in oracle partitions. The partitions are separated by months. The query is like this

SELECT * FROM partition_table where date between #start_date# and #end_date#

The date parameter would be important for oracle to determine which partition to use for the query. If “#” is used, ibatis would generate prepared statement and also the query plan is generated at the same moment without knowing which partition to use. If “$” is used, oracle does know which partition to use when it generates the query plan. The result is that  the performance of using “$”is way better than using “#” in this specific scenario.

No comments:

Post a Comment