Ibatis's learning path: ibatis's & lt; [CDATA] & gt; dynamic attributes and # $ applications

Ibatis <! [CDATA]>, dynamic attributes and #, $ applications
<! [CDATA []> correct use
Ibatis, as a semi-automated OR Mapping tool, is becoming more and more people tend to use it in projects. Because SQL statements often conflict with xml specifications, the validity of xml ing files is affected. Many people know how to use it <! [CDATA []> mark to avoid conflicts. However, when there are dynamic statements in SQL configuration, some details need special attention. Otherwise, it will be laborious and laborious.

When using ibatis, you often need to configure the SQL statement to be executed. All the friends who have used ibatis know that, inevitably, some incompatible and conflicting characters will be encountered. Most people also know that <! [CDATA []> mark to prevent the legality of xml ing files caused by characters that conflict with xml specifications in SQL. However, when using dynamic statements in ibatis, you still need to pay attention to some details. The following is an example:

Environment: oracle, ibatis, java


Error Example 1: The symbol "<=" may affect the validity of the xml ing file.

<Select id = "find" parameterClass = "java. util. Map" resultClass = "java. lang. Long">
Select id
From tableA,
TableB B
<Dynamic prepend = "WHERE">
<IsNotNull prepend = "AND" property = "startDate">
A. act_time >=# startDate #
And a. act_time <= # endDate #
And a. id = B. id
</IsNotNull>
</Dynamic>
</Select>

Error Example 2: Use the entire SQL statement <! [CDATA []> mark to avoid conflicts. It is generally feasible, but because the SQL configuration contains dynamic statements (where part ), the entire SQL statement is invalid because the system cannot identify the dynamic part.

<Select id = "find" parameterClass = "java. util. Map" resultClass = "java. lang. Long">
<! [CDATA [
Select id
From tableA,
TableB B
<Dynamic prepend = "WHERE">
<IsNotNull prepend = "AND" property = "startDate">
A. act_time >=# startDate #
And a. act_time <= # endDate #
And a. id = B. id
</IsNotNull>
</Dynamic>
]>
</Select>

Correct practice: narrow down the scope and only adjust the validity of the conflicting characters.

<Select id = "find" parameterClass = "java. util. Map" resultClass = "java. lang. Long">
Select id
From tableA,
TableB B
<Dynamic prepend = "WHERE">
<IsNotNull prepend = "AND" property = "startDate">
A. act_time >=# startDate #
<! [CDATA [and a. act_time <= # endDate #]>
And a. id = B. id
</IsNotNull>
</Dynamic>
</Select>

Correct use of dynamic
Dynamic can remove the characters in the first prepend = "and" (and here) to help you implement some useful functions. The details are as follows:

1. Use dynamic

1.1 xml

Select * from Person table <dynamic prepend = "where"> <isNotNull property = "name" prepend = "and"> name = # name # </isNotNull> <isNotNull property =" sex "prepend =" and "> sex = # sex # </isNotNull> </dynamic>

Result 1.2

When neither name nor sex is null, the following SQL statement is output:

select    Person where (and) name= ? , and sex= ?

Obviously, the and before name is automatically removed, which is very convenient.

2. Do not use dynamic

2.1 xml
If I remove dynamic, it will become quite disgusting, as shown below:

select * from Person                     <isNotNull property="name" prepend="and">                    name=#name#                </isNotNull>                <isNotNull property="sex" prepend="and">                    sex=#sex#                </isNotNull>   

Result 2.2
When neither name nor sex is null, the following SQL statement is output:

select    Person where and name= ? , and sex= ?

The preceding and statements are incorrect.

3. Summary

Dynamic automatically removes the content in the first prepend = "and (and here) to facilitate some operations.


# Differences with $In Ibatis, We need to reference parameters when using SqlMap for SQL queries. The symbols # And $ encountered in parameter references are distinguished as, # can be compiled to match the types, $ does not match the data type, for example:

Select * from table where id = # id #. If the field id is of the dense type, # id # indicates the 'id' type. If the id is of the integer type, then # id # is the id type.

Select * from table where id = $ id $. If the field id is an integer, the SQL statement will not encounter errors, then the SQL statement should be written as select * from table where id = '$ id $'