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