Ibatis Dynamic Multi-condition query and fuzzy query (ORACLE,MYSQL,SQL)

The first is the problem of fuzzy query, I started with the following conditions: SELECT * from the user where name like '% #value #% '. But how can not, as if also reported wrong. Later on the internet found a solution, is to use $ to replace the # number.

1> write: Like '% $value $% ' can be,<!--fuzzy query can't use #, #是用prepareStatement的? Insert parameter, $ is text replacement-->

2> also found another method, but that method I tried for a long time, is not, the method is: like '% ' | | #value # | | '% ', the query came out of the results are actually all. Later on the internet, it was said that this is the wording of Oracle,

3> if it is MySQL, it should be written as: name like CONCAT ('% ', #value: varchar#, '% '), but I have not tried, anyway, there is a way to succeed.

The first method I try to succeed, the back also did not try, interested friends can try

The second big problem is the multi-conditional combination query, at the beginning, I was also thinking about this problem, can not write a SQL for every query to formulate it, so too ... Later, referring to some documents, it was found that dynamic mapping was provided in the original ibatis. Examples are as follows:

<!--
Use secure stitching statements in Ibatis to dynamically query
Ibatis is one of the advantages of JDBC, safe and efficient
Description text in comments
-


<select id= "selectallproducts" parameterclass= "Product" resultmap= "Productresult" >
Select Id,note from Product
<dynamic prepend= "WHERE" >
<!--isnotnull Determine if the parameter exists, integer type--
<isnotnull property= "id" >
<!--Isgreaterthan to determine if the parameter is greater than or equal to Comparevalue,isgreaterequals
<isgreaterthan prepend= "and" property= "id" comparevalue= "0" >
id = #id #
</isGreaterThan>
</isNotNull>
<!--Isnotempty judgment string is not empty, IsEmpty can judge the string to be empty--
<isnotempty prepend= "and" property= "Note" >
<!--fuzzy query can't use #, #在是用prepareStatement的? Insert parameter, $ is text-to-
Note like '% $note $% '
</isNotEmpty>
</dynamic>
</select>

Use map to pass parameters

<select id= "selectallproducts" parameterclass= "Java.util.HashMap" resultmap= "Productresult" >
Select Id,note from Product
<dynamic prepend= "WHERE" >
<!--ispropertyavailable Determine if the property is valid--
<ispropertyavailable property= "id" >
<isnotnull property= "id" >
<!--Islessthan to determine if the parameter is less than comparevalue,islessequals is less than or equal to
<islessthan prepend= "and" property= "id" comparevalue= "ten" >
id = #id #
</isLessThan>
</isNotNull>
</isPropertyAvailable>
</dynamic>
</select>
<!--------------------------------------------------------------------several common properties--

<isPropertyAvailable> attributes are present
<isNotPropertyAvailable> property does not exist
<isNull> property value is null
<isEmpty> Judge Collection.size<1 or String.Length () <1
<isEqual> equals
<isNotEqual> Not equal to
<isGreaterThan> Greater than
<isGreaterEqual> greater than or equal
<isLessThan> less than
<isLessEqual> less than or equal to

For example, I have a start time startTime and an end time endtime I would like to query the input between StartTime and endtime the time period of all the data, please ask me in the Ibatis file in the How to write, I am the following the wording right?
<select id= "User.queryuser" >
Select .... from .... where .....
<dynamic>

1. <isnotempty prepend="and" property="{starttime,endtime}" >
2. <! [cdata[
3. F.time >= #startTime # and F.time <= #endTime #
4. ]]>
5. </isNotEmpty>

</dynamic>
</select>

1. <isnotempty property= "startTime" prepend="and" >
2. F.time >= #startTime #
3. </isNotEmpty>
4. <isnotempty property= "endTime" prepend="and" >
5. <! [cdata[
6. F.time <= #endTime #
7. ]]>
8. </isNotEmpty>

Here is a reference to an article, you can refer to http://hi.baidu.com/edmond80/blog/item/44b31afa42aef18b9f51467e.html

The IBatis Development guide tells us that when the Name property of the person object is not NULL, the name query condition is configured in the map file Person.xml as



<select id= "Getpersonsbyname" resultclass= "Com.unmi.Person" >

Select ID as id,name as name,passwd as passwd from person

<dynamic prepend= "WHERE" >

<isnotnull prepend= "and" property= "name" >

(Name like #name #)

</isNotNull>

</dynamic>

</select>

<select id= "Getpersonsbyname" resultclass= "Com.unmi.Person" > select ID as id,name as name,passwd as passwd from Pers On <dynamic prepend= "WHERE" > <isnotnull prepend= "and" property= "name" > (name like #name #) </isnotnull > </dynamic> </select>
Call the following code again



person person = new person ();

Person.setname ("Unmi");

List List = Sqlmap.queryforlist ("getpersonsbyname", person);

person person = new person (); Person.setname ("Unmi"); List List = Sqlmap.queryforlist ("getpersonsbyname", person);
The execution effect translates into an SQL statement that is



SELECT * from the person where name is like ' Unmi '

SELECT * from the person where name is like ' Unmi '
This is actually an exact matching query, which is consistent with the following statement, which is written as the equal sign



SELECT * from person where name = ' Unmi '

SELECT * from person where name = ' Unmi '
The reason why we want to use the like predicate is generally want to implement fuzzy query, such as name "Unmi" Start, end or contain ' Unmi ' record, as follows

SELECT * from the person where name is like ' unmi% ';

SELECT * from the person where name is like '%unmi ';

SELECT * from the person where name is like '%unmi% ';

SELECT * from the person where name is like ' unmi% '; SELECT * from the person where name is like '%unmi '; SELECT * from the person where name is like '%unmi% ';
That is, the like semantics in the person.xml should be how to express it? I used to take it for granted
(Name like #name #) written (name like '% #name #% ') or (name like% #name #%), can not pass, respectively error
Java.sql.SQLException:Invalid argument in JDBC Call:parameter index out of Range:1
And
Java.sql.SQLException:Unexpected token:% in statement [select ID ...
So what is the correct wording? Find a solution on the Web how does I use the like in my queries two ways
1. Change the # above (name like '% #name #% ') to $, i.e. (name like '% $name $% ')
2. is by | | The way the connection string is written (name like '% ' | | #name # | | ‘%‘)
But it can't be written (name like '% ' | | $name $| | ' % '), cannot be wrong again
Java.sql.SQLException:Column not FOUND:UNMI in statement [select ID ...
To summarize, the configuration of the fuzzy query with like in IBatis is as follows (two ways)


<select id= "Getpersonsbyname" resultclass= "Com.unmi.Person" >

Select ID as id,name as name,passwd as passwd from person

<dynamic prepend= "WHERE" >

<isnotnull prepend= "and" property= "name" >

(name like '% $name $% ')

<!--(name like '% ' | | #name #| | ' % ')--

</isNotNull>

</dynamic>

</select>

<select id= "Getpersonsbyname" resultclass= "Com.unmi.Person" > select ID as id,name as name,passwd as passwd from Pers On <dynamic prepend= "WHERE" > <isnotnull prepend= "and" property= "name" > (name like '% $name $% ') <!--(name L Ike '% ' | | #name #| | ' % ')-</isNotNull> </dynamic> </select>
I do not know the careful attention of you, this is also my organization in the text when the question arises:
1. Write (name like '% ' | | $name $| | ' % ') why not? What's the difference between # and $?
2. There is clearly written Unmi, why the error when the full capitalization of the Unmi it?
Specific similarities and differences we may also need to find from the source code, simply know, $name $ is a literal substitution, this form should pay attention to the vulnerability of SQL injection; #name # is a substitution with type. As for the Unmi to be converted to uppercase, but also need to study the study, for the above two questions can also play a bit.

Original address: http://blog.csdn.net/isprotect/article/details/8784310

Ibatis Dynamic Multi-condition query and fuzzy query (ORACLE,MYSQL,SQL)