Tag: Object ognl JDBC Prefix error condition object item. class
mybatis Dynamic SQL statements are based on OGNL expressions. It is convenient to implement some logic in the SQL statement. Generally speaking, mybatis dynamic SQL statements have the following main categories:
1. If statement (simple conditional judgment)
2. Choose (when,otherwize), equivalent to switch in the Java language, is similar to choose in Jstl.
3. Trim (add prefix to the included content, or suffix, prefix, suffix)
4. Where (mainly used to simplify the Where condition judgment in SQL statements, can intelligently handle and OR, do not have to worry about unnecessary syntax errors)
5. Set (when used primarily for updates)
6. foreach (especially useful when implementing MyBatis in statement queries)
Here are the different ways to deal with these
1. Mybaits If statement processing
Program code
<select id= "dynamiciftest" parametertype= "blog" resulttype= "blog" >
SELECT * from T_blog where 1 = 1
<if test= "Title! = NULL" >
and title = #{title}
</if>
<if test= "Content! = NULL" >
and content = #{content}
</if>
<if test= "Owner! = NULL" >
and owner = #{owner}
</if>
</select>
The meaning of this statement is very simple, if you provide the title parameter, then must satisfy Title=#{title}, also if you provide content and owner, they also need to meet the appropriate conditions, and then return to meet these conditions of all the blog, This is a very useful feature, in the past we use other types of framework or direct use of JDBC, if we want to achieve the same selection effect, we need to spell SQL statement, which is extremely troublesome, compared to the above dynamic SQL is much simpler
2.2. Choose (when,otherwize), equivalent to switch in the Java language, is similar to choose in Jstl
Program code
<select id= "dynamicchoosetest" parametertype= "blog" resulttype= "blog" >
SELECT * from T_blog where 1 = 1
<choose>
<when test= "Title! = NULL" >
and title = #{title}
</when>
<when test= "Content! = NULL" >
and content = #{content}
</when>
<otherwise>
and owner = "Owner1"
</otherwise>
</choose>
</select>
The When element means that when the condition in the when is satisfied with the output of the content, and the switch effect in Java is similar to the order of the conditions, when the when the conditions are met, will jump out of choose, that is, all the when and otherwise conditions , only one will output, and when all of my conditions are not satisfied, I output the contents of otherwise. So the meaning of the above statement is very simple, when the title!=null output and Titlte = #{title}, no longer judge the condition, when the title is empty and content!=null output and content = #{content}, Outputs the contents of the otherwise when all conditions are not met.
3.trim (add prefix to the included content, or suffix, prefix, suffix)
Program code
<select id= "dynamictrimtest" parametertype= "blog" resulttype= "blog" >
SELECT * FROM T_blog
<trim prefix= "where" prefixoverrides= "and |or" >
<if test= "Title! = NULL" >
title = #{title}
</if>
<if test= "Content! = NULL" >
and content = #{content}
</if>
<if test= "Owner! = NULL" >
or owner = #{owner}
</if>
</trim>
</select>
The main function of the trim element is that you can add some prefixes to the content you include, or you can add some suffixes to it, and the corresponding properties are prefix and suffix; you can overwrite certain contents of the header containing the content, that is, ignore or overwrite some of the contents of the trailer. The corresponding properties are prefixoverrides and suffixoverrides; Because trim has such functionality, we can also use trim to replace the where element's functionality very simply .
4. Where (mainly used to simplify the determination of where conditions in SQL statements, intelligently handle and or conditions
Program code
<select id= "dynamicwheretest" parametertype= "blog" resulttype= "blog" >
SELECT * FROM T_blog
<where>
<if test= "Title! = NULL" >
title = #{title}
</if>
<if test= "Content! = NULL" >
and content = #{content}
</if>
<if test= "Owner! = NULL" >
and owner = #{owner}
</if>
</where>
</select>
The purpose of the where element is to output a where element where it is written, and another benefit is that you do not need to consider what the conditional output inside the where element looks like, and MyBatis will intelligently handle it for you. If all the conditions are not met then MyBatis will find all the records, if the output is and start, MyBatis will be the first and ignore, of course, if the or start, MyBatis will ignore it, in addition, in the WHERE element you do not need to consider the question of spaces, MyBatis will be smart to help you add. As in the example above, if title=null, and content! = NULL, then the entire statement of the output would be a select * from t_blog where content = #{content} instead of select * from T_blog w Here and content = #{content}, because MyBatis will intelligently ignore the first and or OR.
5.set (when used primarily for updates)
Program code
<update id= "Dynamicsettest" parametertype= "Blog" >
Update T_blog
<set>
<if test= "Title! = NULL" >
title = #{title},
</if>
<if test= "Content! = NULL" >
Content = #{content},
</if>
<if test= "Owner! = NULL" >
Owner = #{owner}
</if>
</set>
WHERE id = #{id}
</update>
The set element is mainly used in the update operation, its main function and the where element is actually similar, mainly in the containing statement before the output of a set, and then if the inclusion of the statement is terminated with a comma, the comma will be ignored, if the set contains the content is empty, then an error occurs. With the set element, we can dynamically update those fields that have been modified .
6. foreach (especially useful when implementing MyBatis in statement queries)
The main use of foreach is in the build in condition, which can iterate a collection in an SQL statement. The properties of the Foreach element are mainly item,index,collection,open,separator,close. The item represents the alias of each element in the collection when it iterates, and index specifies a name that represents the position at which each iteration occurs during the iteration, and open indicates what the statement begins with, and separator indicates what symbol is used as the delimiter between each iteration. Close means the end, the most critical and error-prone when using foreach is the collection property, which must be specified, but in different cases the value of the property is not the same, there are 3 main cases:
If a single parameter is passed in and the parameter type is a list, the collection property value is List
If a single parameter is passed in and the parameter type is an array, the value of the collection property is array
If the parameters passed in are multiple, we need to encapsulate them into a map, of course, the single parameter can also be encapsulated as a map, in fact, if you pass in the parameter, in the MyBatis will also wrap it into a map, the map key is the parameter name, So this time the collection property value is the key to the incoming list or array object in its own encapsulated map
1.1. Type of single parameter list
Program code
<select id= "Dynamicforeachtest" resulttype= "Blog" >
SELECT * from T_blog where ID in
<foreach collection= "list" index= "index" item= "Item" open= "(" separator= "," close= ")" >
#{item}
</foreach>
</select>
The value of the above collection is list, the corresponding Mapper is this
Program code
Public list<blog> dynamicforeachtest (list<integer> IDs);
Test Code
Program code
@Test
public void Dynamicforeachtest () {
sqlsession session = Util.getsqlsessionfactory (). Opensession ();
Blogmapper blogmapper = Session.getmapper (Blogmapper.class);
list<integer> ids = new arraylist<integer> ();
Ids.add (1);
Ids.add (3);
Ids.add (6);
list<blog> blogs = blogmapper.dynamicforeachtest (IDS);
for (Blog blog:blogs)
SYSTEM.OUT.PRINTLN (blog);
Session.close ();
}
2. Parameters of the array type
Program code
<select id= "Dynamicforeach2test" resulttype= "Blog" >
SELECT * from T_blog where ID in
<foreach collection= "Array" index= "index" item= "Item" open= "(" separator= "," close= ")" >
#{item}
</foreach>
</select>
Correspondence Mapper
Program code
Public list<blog> dynamicforeach2test (int[] IDs);
3. Parameters of the Map type
Program code
<select id= "Dynamicforeach3test" resulttype= "Blog" >
SELECT * from T_blog where title like "%" #{title} "%" and ID in
<foreach collection= "IDs" index= "index" item= "Item" open= "(" separator= "," close= ")" >
#{item}
</foreach>
</select>
The mapper should be such an interface:
Program code
Public list<blog> dynamicforeach3test (map<string, object> params);
through the above method, we can complete the general MyBatis dynamic SQL statement. The most common is if where foreach these, must focus on mastering.
Source: http://www.yihaomen.com/article/java/328.htm
From for notes (Wiz)
MyBatis Combat Course (mybatis in action) Eight: MyBatis dynamic SQL statement
