Dynamic SQL (if, choose (when, otherwise), trim (where, set), set, foreach)

Why do I need dynamic SQL? Sometimes you need to dynamically stitch SQL statements based on the actual parameters that are passed in. The most common is: where and if tags1. Refer to the official documentation? If: Character judgment? Choose (When, otherwise): branch selection? Trim (where, set): String intercept, where the where label encapsulates the query condition, and the set label encapsulates the modification condition? foreach 2.if Case:1) Add a method to the Employeemapper interface://Which field is carried, the value of which field is taken on the query criteria public list<employee> getemployeebyconditionif (Employee    Employee); 2). If you want to write the following SQL statement, as long as it is not empty, as a query condition, as shown below, this is actually problematic, so we will write a dynamic SQL statement: <select id= "getemployeebyconditionif" Resulttype= "Com.neuedu.entity.Employee" >select *from tbl_employee where id = #{id} and user_name = #{username} and Emai L = #{email} and gender = #{gender} </select>3) are rewritten as dynamic SQL with the If label as follows: <select id= "getemployeebyconditionif" Resulttype= "Com.neuedu.entity.Employee" >select *from tbl_employee where <!--test: Judge expression (OGNL) OGNL reference ppt or official document. C:if test takes a value from a parameter to meet a special symbol, you should write the escape character: Reference w3cschool>>html>>iso8859-<if test= "id! = NULL" >id = #{id } </if> <if test= "UserName! = null && userName! = '" ">and user_name = #{username} </if> < If test= "Email! = null and Email.trim ()! =" "" ">and email = #{email} </if> <!--OGNL will be judged by the conversion of strings and numbers;" 0 "==0," 1 " ==1--<if test= "gender = = 0 or gender = 1" >and gender = #{gender} </if></select> 4). Test code: @Testpublic void Testgetemployee () {Employeemapper mapper = Opensession.getmapper (Employeemapper.class); Employee Employee = new Employee (), Employee.setid (1), Employee.setusername ("Zhang San Feng"), Employee.setemail ("[email  Protected] "); Employee.setgender (1); list<employee> list = mapper.getemployeebyconditionif (Employee);  SYSTEM.OUT.PRINTLN (list);}  #测试结果没问题 but carefully, the SQL statement above is problematic, and when we don't pass the ID value to the dynamic SQL statement, the SQL statement will have a problem assembling! WORKAROUND: 1. Add 1=1 to the Where, and xxx for later conditions 2.mybatis you can use the Where tab to include all the query criteria. MyBatis will remove the SQL that is assembled in the Where tab, the extra and or or the!//needs to be noted: the WHERE tag only removes the first and or 3. That is, the where label sometimes does not solve the problem, how to do? We can use trim tags here!2.trim Tags: You can customize the interception rules for strings<select id= "getemployeebyconditionif" resulttype= "Com.neuedu.entity.Employee" >select *from tbl_employee<! --After the extra and or or where tag does not solve prefix= "": prefix: Trim tag weight is the result of the entire string after the puzzle. Prefix adds a prefix to the entire string after the puzzle prefixoverrides= "": Prefix Overwrite: Remove the extra characters in front of the entire string suffix= "": Suffix suffix the entire string after the spell and a suffix suffixoverrides= ""   : suffix overwrite: Removes extra characters from the entire string--<trim prefix= "where" suffixoverrides= "and" > <if test= "id! = NULL" >id = #{id} and </if> <if test= "UserName! = null && UserName! =" ">user_name = #{username} and </if> < If test= "Email! = null and Email.trim ()! =" "" ">email = #{email} and </if> <!--OGNL will be judged by the conversion of strings and numbers;" 0 "==0," 1 "==1--<if test=" gender==0 or gender==1 "> gender = #{gender} </if></trim></select>3.choose Tags: branch selection, similar to switch...case with break in JavaChoose (When, otherwise): If with ID, ID check, if bring username with username, will only enter one!   Case Demo: 1. Add a method to the Employeemapper interface: Public list<employee> getemployeebyconditionchoose (employee employee); 2.sql mapping File <!--public list<employee> Getemployeebyconditionchoose (employee employee); --><select id= "Getemployeebyconditionchoose" resulttype= "Com.neuedu.entity.Employee" >select *from Tbl_ employee<where><!--if with ID, ID check, if bring username with username, will only enter one! --><choose><when test= "id! = NULL" >id = #{id}</when><when test= "UserName! = null" >user_name Like #{username}</when><when test= "Email! = NULL" > email = #{email}</when><otherwise>1=1</ Otherwise></choose></where></select>set tag in 4.trim (where, set): String intercept, where the where label encapsulates the query condition, the set label encapsulates the modify condition set element dynamically pre-sets the Set keyword, and also eliminates extraneous commas.     1). Add an updated method in Employeemapper as follows: public void Updateemp (employee employee), 2) in the SQL mapping file, fill in the appropriate SQL statement as follows " The set label can remove the comma following the field ": <update id=" updateemp ">update tbl_employee <set><if test=" UserName! = null "> user_name = #{username},</if><if test= "Email! = NULL" >email = #{email},</if><if test= "Gender! = Null ">gender = #{gender},</if></set>where id = #{id}</update> Test class code: @Testpublic void Testgetemployee () {Employeemapper mapper = Opensession.getmapper (Employeemapper.class); Employee Employee = new Employee (), Employee.setid (1), employee.setusername ("haha"), Employee.setemail ("[email  protected]); Employee.setgender (1); mapper.updateemp (employee);} Of course the above set label can also be replaced with a trim tag, as follows: <update id= "updateemp" >update tbl_employee <trim prefix= "Set" Suffixoverrides= "," ><if test= "UserName! = null" >user_name = #{username},</if><if test= "Email! = NULL ">email = #{email},</if><if test=" Gender! = NULL ">gender =#{gender},</if></trim>where id = #{id}</update> 5.foreach: Traverse element  Another common operation for dynamic SQL is the need to traverse a collection, usually when building in conditional statements! The foreach element allows you to specify a collection, declare collection items and index variables, and specify opening and closing matching strings and placing separators between iterations. Case study: 1. Add a method to the Employeemapper interface as follows: Public list<employee> Getempsbyconditionforeach (@Param ("IDs") list< integer> IDs); 2. Write the corresponding code in MyBatis's SQL mapping file:<!--public list<employee> Getempsbyconditionforeach (list<integer> IDs); --><select id= "Getempsbyconditionforeach" resulttype= "Com.neuedu.entity.Employee" >select * from Tbl_ Employee where ID in<!--collection: Specifies the collection to traverse item: Assigns the currently traversed element to the specified variable separator: The delimiter between each element open: Traverse out all the demerit stitching a starting character close: Traverse out all results stitching an end character--><foreach collection= "IDs" open= "(" close= ")" separator= "," item= "id" >#{id}</foreach></select> 3. The test class code is: @Testpublic void Testgetemployee () {Employeemapper mapper = Opensession.getmapper (Employeemapper.class); list<integer> aslist = arrays.aslist (1,2,3,4); list<employee> emps = Mapper.getempsbyconditionforeach (aslist); for (Employee Employee:emps) { System.out.println (Employee);}} The foreach tag can also be used to save data in bulk, as follows: 1. Add Bulk Insert methods to the Employeemapper interface class: public void Addemps (@Param ("Emps") list<employee> E     MPS); 2. Add a response statement to the SQL mapping file for employeemapper.xml:<!--public void Addemps (@Param ("Emps") list<employee> Emps); --><!--MySQL under batch Save: can be foreach traversal, MySQL support values (), (), () syntax--><insert id= "Addemps" >insert into Tbl_ Employee (user_name,gender,email,d_id) Values<foreach collection= "Emps" item= "emp" separator= "," > (#{ Emp.username},#{emp.gender},#{emp.email},#{emp.depart.id}) </foreach></insert> 3. Test code: @Testpublic void Testgetemployee () {Employeemapper mapper = Opensession.getmapper (Employeemapper.class); list<employee> emps = new arraylist<employee> () Emps.add (new Employee (0, 1, "Allen", "[email  Protected] ", New Department (1)), Emps.add (new Employee (0, 0," Tom "," [email protected] ", New Department (2)));  Emps.add (New Employee (0, 1, "Mux", "[email protected]", New Department (1))); Mapper.addemps (Emps);}

　　

Dynamic SQL (if, choose (when, otherwise), trim (where, set), set, foreach)