Realization of multiple conditional free combination query with Ibatis

Ibatis is a semi-automatic tool, one of its advantages is to be able to generate dynamic SQL statements, the use of this feature can be achieved on the data of multiple conditional free combination query. I have some of my own application experience a summary, and share with you.

Ibatis uses the bean to map fields in the database so that you can manipulate the bean directly to get the data. While doing the query, I took advantage of some of the features in sqlmap to generate SQL so that most of the properties in the bean could be retrieved as possible combinations of conditions.

It feels good. See the following example:

<select id= "getrecords" parameterclass= "Bean" resultclass= "Bean" cachemodel= "Product-cache" >
Select
ID as ID,
project_id as ProjectID,
Main_road as Mainroad,
Cross_road1 as CrossRoad1,
Cross_road2 as CrossRoad2,
Length as length,
LOCATION as LOCATION,
HOLE as HOLE,
Lead_unit as Leadunit,
Propose_unit as Proposeunit,
Cast (year (launch_time) as char (4)) + ' +cast ' (Replicate (' 0 ', 2-len (month (launch_time))) + CAST (month (launch_time) as char (2)) as char (2)) + '-' +cast (Replicate (' 0 ', 2-len (Day (launch_time)) + CAST (day (Launch_time) as char (2)) as char (2)) as Launchtime,
Cast (year (apply_time) as char (4)) + ' +cast ' (Replicate (' 0 ', 2-len (month (apply_time))) + CAST (month (apply_time) as char ( 2) as char (2)) + '-' +cast (Replicate (' 0 ', 2-len (Day (apply_time)) + CAST (day (Apply_time) as char (2)) as char (2)) as Applyti Me
Cast (year (eva_time) as char (4)) + ' +cast (Replicate (' 0 ', 2-len (month (eva_time))) + CAST (month (eva_time) as char (2)) as char (2)) + '-' +cast (Replicate (' 0 ', 2-len (eva_time)) + CAST (day (Eva_time) as char (2)) as char (2)) as Evatime,
Eva_remark as Evaremark,
Status as status,
Prj_remark as Prjremark,
Cha_remark as Charemark
From
Pipe_business
<!--add Dynamic query part-->
<dynamic prepend= "WHERE" >
<isnotnull property= "id" >
<isgreaterthan prepend= "and" property= "id" comparevalue= "0" >id = #id #</isgreaterthan> </isNot Null>
<isnotempty prepend= "and" property= "ProjectID" >project_id like #projectId #</isnotempty> <isnot Empty prepend= "and" property= "Mainroad" >main_road like #mainRoad #</isnotempty <isnotempty " and "property=" CrossRoad1 ">cross_road1 like #crossRoad1 #</isnotempty>
<isnotempty prepend= "and" property= "CrossRoad2" >cross_road2 like #crossRoad2 #</isnotempty>
<isnotnull property= "Length" >
<isgreaterthan prepend= "and" property= "Length" comparevalue= "0" >length between #length #-100 and #length #+100 </isGreaterThan>
</isNotNull>
<isnotempty prepend= "and" property= "location" >location like #location #</isnotempty>
<isnotnull property= "Hole" >
<isgreaterthan prepend= "and" property= "hole" comparevalue= "0" >hole = #hole #</isgreaterthan>
</isNotNull>
<isnotempty prepend= "and" property= "Leadunit" >lead_unit like '% $leadUnit $% ' </isNotEmpty>
<isnotempty prepend= "and" property= "Proposeunit" >propose_unit like '% $proposeUnit $% ' </isNotEmpty>
<isnotempty prepend= "and" property= "Launchtime" >year (Launch_time) = year (CAST (#launchTime # as DateTime)) and Month (Launch_time) = month (CAST (#launchTime # as DateTime) </isNotEmpty>
<isnotempty prepend= "and" property= "Applytime" >year (Apply_time) = year (CAST (#applyTime # as DateTime)) and month ( Apply_time) between month (cast (#applyTime # as DateTime)-3 and month (CAST (#applyTime # as DateTime)) ></isnotempty >
<isnotempty prepend= "and" property= "Evatime" > Year (Eva_time) = year (CAST (#evaTime # as DateTime)) and month (Eva_ti ME) = month (CAST (#evaTime # as DateTime) </isnotempty><!-
<isnotempty prepend= "and" property= "Evaremark" >eva_remark like '% $evaRemark $% ' </isNotEmpty>
<isnotempty prepend= "and" property= "status" >status like #status #</isnotempty>
<isnotempty prepend= "and" property= "Prjremark" >prj_remark like '% $prjRemark $% ' </isNotEmpty>
<isnotempty prepend= "and" property= "Charemark" >cha_remark like '% $chaRemark $% ' </isNotEmpty>
</dynamic>
ORDER BY ID
</select>