Dynamic SQL statement of mybatis

In some cases, security judgment is required in the where condition of an SQL statement. For example, if the input parameter is null during a query based on a condition, the query result may be null, if the parameter is null, all information is found. Use the Oracle sequence and MySQL functions to generate IDS. In this case, we can use dynamic SQL.
MySQL syntax and functions (such as Concat) are used below ).

3.1 selectkey label
In the insert statement, sequence is often used in Oracle, and functions are used in MySQL to automatically generate the primary key of the inserted Table. In addition, a method is required to return the generated primary key. You can use the selectkey tag of mybatis to achieve this effect. The following example uses the MySQL Database User-Defined Function nextval ('student ') to generate a key and set it to the studentid attribute in the passed object class. Therefore, after executing this method, you can obtain the generated key through this object class.

<! -- Insert an automatic primary key for students --> <insert id = "createstudentautokey" parametertype = "liming. student. manager. data. model. studententity "keyproperty =" studentid "> <selectkey keyproperty =" studentid "resulttype =" string "Order =" before "> select nextval ('student ') </selectkey> insert into (student_id, student_name, student_sex, role, role, class_id, place_id) values (# {studentid}, # {studentname}, # {studentsex }, # {studentbirthday },
Studententity entity = new studententity (); entity. setstudentname (""); entity. setstudentsex (1); entity. setstudentbirthday (dateutil. parse ("1985-05-28"); entity. setclassid ("20000001"); entity. setplaceid ("70000001"); this. dynamicsqlmapper. createstudentautokey (entity); system. out. println ("new student ID:" + entity. getstudentid ());
# {Studentphoto, javatype = byte [], jdbctype = blob, typehandler = org. apache. ibatis. type. blobtypehandler },#{ classid },#{ placeid}) </Insert>
Call the interface method and obtain the automatically generated key
Studententity entity = new studententity (); entity. setstudentname (""); entity. setstudentsex (1); entity. setstudentbirthday (dateutil. parse ("1985-05-28"); entity. setclassid ("20000001"); entity. setplaceid ("70000001"); this. dynamicsqlmapper. createstudentautokey (entity); system. out. println ("new student ID:" + entity. getstudentid ());
Selectkey statement attribute configuration details:
Attribute description Value
The attribute to be set for the keyproperty selectkey statement to generate the result.
The type of the result generated by resulttype. mybatis allows the use of basic data types, including string and Int.
Order
1: before: the primary key is selected first, then the keyproperty is set, and then the insert statement is executed;
2: After, run the insert statement before running the selectkey statement.
Before
After
Statementtype mybatis supports statement, prepared, and callable statements, corresponding to statement, preparedstatement, and callablestatement responses
Statement, prepared, callable
3.2 If tag
The IF tag can be used in many types of SQL statements. We use the query as an example. First, let's look at a common query:
<! -- Query student list, like name --> <select id = "getstudentlistlikename" parametertype = "studententity" resultmap = "studentresultmap"> select * From student_tbl st where St. student_name like Concat ('%', # {studentname}), '%') </SELECT>
However, if studentname or studentsex is null, this statement may report an error or the query result is null. In this case, the IF dynamic SQL statement is used to determine the condition first. If the value is null or equal to a null string, we will not judge the condition and increase flexibility.
The parameter is the entity class studententity. All attributes of the object class are judged. If not empty, the judgment condition is executed.
<! -- 2 if (Judgment parameter)-use the attribute of the object class not empty as the where condition --> <select id = "getstudentlist_if" resultmap = "resultmap_studententity" parametertype = "liming. student. manager. data. model. studententity "> select St. student_id, St. student_name, St. student_sex, St. student_birthday, St. student_photo, St. class_id, St. place_id from student_tbl st where <if test = "studentname! = NULL "> St. student_name like Concat ('%', # {studentname, jdbctype = varchar}), '%') </If> <if test = "studentsex! = NULL and studentsex! = ''"> And St. student_sex =#{ studentsex, jdbctype = integer} </If> <if test = "studentbirthday! = NULL "> and St. student_birthday =#{ studentbirthday, jdbctype = date} </If> <if test =" classid! = NULL and classid! = ''"> And St. class_id = # {classid, jdbctype = varchar} </If> <if test = "classentity! = NULL and classentity. classid! = NULL and classentity. classid! = ''"> And St. class_id = # {classentity. classid, jdbctype = varchar} </If> <if test = "placeid! = NULL and placeid! = ''"> And St. place_id = # {placeid, jdbctype = varchar} </If> <if test = "placeentity! = NULL and placeentity. placeid! = NULL and placeentity. placeid! = ''"> And St. place_id = # {placeentity. placeid, jdbctype = varchar} </If> <if test = "studentid! = NULL and studentid! = ''"> And St. student_id =#{ studentid, jdbctype = varchar} </If> </SELECT>
It is more flexible to use. For a new object class, we need to restrict that condition. We only need to attach the corresponding value to the where condition. On the contrary, if we do not assign a value, we can not judge it in the WHERE clause.
    public void select_test_2_1() {          StudentEntity entity = new StudentEntity();          entity.setStudentName("");          entity.setStudentSex(1);          entity.setStudentBirthday(DateUtil.parse("1985-05-28"));          entity.setClassId("20000001");          //entity.setPlaceId("70000001");          List<StudentEntity> list = this.dynamicSqlMapper.getStudentList_if(entity);          for (StudentEntity e : list) {              System.out.println(e.toString());          }      }  
3.3 If + Where condition judgment
When many if tags are used in the WHERE clause, such combinations may cause errors. Take the query statement in 3.1 as an example. When the Java code is called as follows:
    @Test      public void select_test_2_1() {          StudentEntity entity = new StudentEntity();          entity.setStudentName(null);          entity.setStudentSex(1);          List<StudentEntity> list = this.dynamicSqlMapper.getStudentList_if(entity);          for (StudentEntity e : list) {              System.out.println(e.toString());          }      }  
If the studentname parameter is null in the preceding example, the student_name column is not judged, and the Redundant Error SQL statement with the "Where and" keyword is directly exported.
In this case, we can use the where dynamic statement to solve the problem. This "where" label will know that if it contains a returned value, it inserts a 'where '. In addition, if the returned content of a tag starts with "and" or ", it will be removed.
The preceding example is modified:
<! -- 3 select-where/If (Judgment parameter) -Use the attribute of the object class not empty as the where condition --> <select id = "getstudentlist_whereif" resultmap = "resultmap_studententity" parametertype = "liming. student. manager. data. model. studententity "> select St. student_id, St. student_name, St. student_sex, St. student_birthday, St. student_photo, St. class_id, St. place_id from student_tbl st <where> <if test = "studentname! = NULL "> St. student_name like Concat ('%', # {studentname, jdbctype = varchar}), '%') </If> <if test = "studentsex! = NULL and studentsex! = ''"> And St. student_sex =#{ studentsex, jdbctype = integer} </If> <if test = "studentbirthday! = NULL "> and St. student_birthday =#{ studentbirthday, jdbctype = date} </If> <if test =" classid! = NULL and classid! = ''"> And St. class_id = # {classid, jdbctype = varchar} </If> <if test = "classentity! = NULL and classentity. classid! = NULL and classentity. classid! = ''"> And St. class_id = # {classentity. classid, jdbctype = varchar} </If> <if test = "placeid! = NULL and placeid! = ''"> And St. place_id = # {placeid, jdbctype = varchar} </If> <if test = "placeentity! = NULL and placeentity. placeid! = NULL and placeentity. placeid! = ''"> And St. place_id = # {placeentity. placeid, jdbctype = varchar} </If> <if test = "studentid! = NULL and studentid! = ''"> And St. student_id =#{ studentid, jdbctype = varchar} </If> </where> </SELECT>
3.4 If + set update statement
If the If label is not used in the update statement, any null parameter may cause an error.
When the If label is used in the update statement, if the previous if statement is not executed, it may lead to unnecessary comma errors. You can use the set tag to dynamically configure the set keyword and remove any irrelevant commas appended to the end of the condition.
After you use the if + set label to modify the value, if the value of an item is null, the original value of the database is maintained instead of being updated. Example:
<! -- 4 If/set (Judgment parameter)-update attributes of the object class that are not empty --> <update id = "updatestudent_if_set" parametertype = "liming. student. manager. data. model. studententity "> Update student_tbl <set> <if test =" studentname! = NULL and studentname! = ''"> Student_tbl.student_name =#{ studentname}, </If> <if test = "studentsex! = NULL and studentsex! = ''"> Student_tbl.student_sex =#{ studentsex}, </If> <if test = "studentbirthday! = NULL "> student_tbl.student_birthday =#{ studentbirthday}, </If> <if test =" studentphoto! = NULL "> student_tbl.student_photo = # {studentphoto, javatype = byte [], jdbctype = blob, typehandler = org. apache. ibatis. type. blobtypehandler}, </If> <if test = "classid! = ''"> Student_tbl.class_id =#{ classid} </If> <if test = "placeid! = ''"> Student_tbl.place_id =#{ placeid} </If> </set> where student_tbl.student_id =#{ studentid}; </update>
3.5 If + trim replace where/set label
Trim is a more flexible tag for storing redundant keywords. It can implement the where and set effects.
3.5.1trim replaces where
<! -- 5.1 if/trim replaces where (Judgment parameter) -Use the attribute of the object class not null as the where condition --> <select id = "getstudentlist_if_trim" resultmap = "resultmap_studententity"> select St. student_id, St. student_name, St. student_sex, St. student_birthday, St. student_photo, St. class_id, St. place_id from student_tbl st <trim prefix = "where" prefixoverrides = "and | or"> <if test = "studentname! = NULL "> St. student_name like Concat ('%', # {studentname, jdbctype = varchar}), '%') </If> <if test = "studentsex! = NULL and studentsex! = ''"> And St. student_sex =#{ studentsex, jdbctype = integer} </If> <if test = "studentbirthday! = NULL "> and St. student_birthday =#{ studentbirthday, jdbctype = date} </If> <if test =" classid! = NULL and classid! = ''"> And St. class_id = # {classid, jdbctype = varchar} </If> <if test = "classentity! = NULL and classentity. classid! = NULL and classentity. classid! = ''"> And St. class_id = # {classentity. classid, jdbctype = varchar} </If> <if test = "placeid! = NULL and placeid! = ''"> And St. place_id = # {placeid, jdbctype = varchar} </If> <if test = "placeentity! = NULL and placeentity. placeid! = NULL and placeentity. placeid! = ''"> And St. place_id = # {placeentity. placeid, jdbctype = varchar} </If> <if test = "studentid! = NULL and studentid! = ''"> And St. student_id =#{ studentid, jdbctype = varchar} </If> </trim> </SELECT>
3.5.2 trim instead of Set
<! -- 5.2 if/trim replace set (Judgment parameter)-update the property of the object class not empty --> <update id = "updatestudent_if_trim" parametertype = "liming. student. manager. data. model. studententity "> Update student_tbl <trim prefix =" set "suffixoverrides =", "> <if test =" studentname! = NULL and studentname! = ''"> Student_tbl.student_name =#{ studentname}, </If> <if test = "studentsex! = NULL and studentsex! = ''"> Student_tbl.student_sex =#{ studentsex}, </If> <if test = "studentbirthday! = NULL "> student_tbl.student_birthday =#{ studentbirthday}, </If> <if test =" studentphoto! = NULL "> student_tbl.student_photo = # {studentphoto, javatype = byte [], jdbctype = blob, typehandler = org. apache. ibatis. type. blobtypehandler}, </If> <if test = "classid! = ''"> Student_tbl.class_id =#{ classid}, </If> <if test = "placeid! = ''"> Student_tbl.place_id =#{ placeid} </If> </trim> where student_tbl.student_id =#{ studentid} </update>
3.6 choose (when, otherwise)
Sometimes we don't want to apply all the conditions, but just want to select one from multiple options. When the if tag is used, if the expression in test is true, the conditions in the IF tag are executed. Mybatis provides the choose element. If tags are related to (and), while choose is or (OR. The choose tag is used to determine in sequence whether the test condition in its internal when tag is true. If one is true, the choose ends. When all the when conditions in the choose are not satisfied, the SQL statement in otherwise is executed. Similar to the switch statement in Java, choose is switch, when is case, and otherwise is default.
For example, in the following example, all the conditions that can be restricted are written and used. Choose selects an SQL statement from top to bottom where TEST OF THE when label is true. For security considerations, we use where to package choose and place more keywords than errors.
<! -- 6 choose (Judgment parameter) -Use the first non-null attribute of the object class as the where condition in sequence --> <select id = "getstudentlist_choose" resultmap = "resultmap_studententity" parametertype = "liming. student. manager. data. model. studententity "> select St. student_id, St. student_name, St. student_sex, St. student_birthday, St. student_photo, St. class_id, St. place_id from student_tbl st <where> <choose> <when test = "studentname! = NULL "> St. student_name like Concat ('%', # {studentname, jdbctype = varchar}), '%') </when> <when test = "studentsex! = NULL and studentsex! = ''"> And St. student_sex =#{ studentsex, jdbctype = integer} </when> <when test = "studentbirthday! = NULL "> and St. student_birthday =#{ studentbirthday, jdbctype = date} </when> <when test =" classid! = NULL and classid! = ''"> And St. class_id = # {classid, jdbctype = varchar} </when> <when test = "classentity! = NULL and classentity. classid! = NULL and classentity. classid! = ''"> And St. class_id = # {classentity. classid, jdbctype = varchar} </when> <when test = "placeid! = NULL and placeid! = ''"> And St. place_id = # {placeid, jdbctype = varchar} </when> <when test = "placeentity! = NULL and placeentity. placeid! = NULL and placeentity. placeid! = ''"> And St. place_id = # {placeentity. placeid, jdbctype = varchar} </when> <when test = "studentid! = NULL and studentid! = ''"> And St. student_id =#{ studentid, jdbctype = varchar} </when> <otherwise> </choose> </where> </SELECT>
3.7 foreach
Dynamic SQL statements are required. The primary statement is to iterate a set, which is usually used as the in condition. The list instance uses "list" as the key, and the array instance uses "array" as the key.
The foreach element is very powerful. It allows you to specify a set and declare collection items and index variables. They can be used in the element body. It also allows you to specify open and closed strings and place separators between iterations. This element is intelligent and does not occasionally append redundant separators.
Note: You can pass a list instance or array as the parameter object to mybatis. When you do this, mybatis automatically wraps it in a map and uses the name as the key. The list instance uses "list" as the key, and the array instance uses "array" as the key.
This part is about the xml configuration file and XML ing file. The next section will discuss Java APIs in detail, so you can get the most effective ing you have created.
3.7.1 how to write a parameter as an array example
Interface Method Declaration: public list <studententity> getstudentlistbyclassids_foreach_array (string [] classids); dynamic SQL statement: <! -7.1 foreach (loop array parameter)-as the condition for in where --> <select id = "getstudentlistbyclassids_foreach_array" resultmap = "resultmap_studententity"> select St. student_id, St. student_name, St. student_sex, St. student_birthday, St. student_photo, St. class_id, St. place_id from student_tbl st where St. class_id in <foreach collection = "array" item = "classids" open = "(" separator = "," Close = ") ">#{ classids} </foreach> </SELECT>
Test code to query the students in the 20000001 and 20000002 classes:
Interface Method Declaration: public list <studententity> getstudentlistbyclassids_foreach_array (string [] classids); dynamic SQL statement: <! -7.1 foreach (loop array parameter)-as the condition for in where --> <select id = "getstudentlistbyclassids_foreach_array" resultmap = "resultmap_studententity"> select St. student_id, St. student_name, St. student_sex, St. student_birthday, St. student_photo, St. class_id, St. place_id from student_tbl st where St. class_id in <foreach collection = "array" item = "classids" open = "(" separator = "," Close = ") ">#{ classids} </foreach> </SELECT>
3.7.2 how to write a list Parameter
Interface Method declaration:
Public list <studententity> getstudentlistbyclassids_foreach_list (list <string> classidlist );
Dynamic SQL statement:
<! -- 7.2 foreach (Cyclic list <string> parameter)-as the in condition in where --> <select id = "getstudentlistbyclassids_foreach_list" resultmap = "resultmap_studententity"> select St. student_id, St. student_name, St. student_sex, St. student_birthday, St. student_photo, St. class_id, St. place_id from student_tbl st where St. class_id in <foreach collection = "list" item = "classidlist" open = "(" separator = "," Close = ") ">#{ classidlist }</foreach> </SELECT>
Test code to query the students in the 20000001 and 20000002 classes:
    @Test      public void test7_2_foreach() {          ArrayList<String> classIdList = new ArrayList<String>();          classIdList.add("20000001");          classIdList.add("20000002");          List<StudentEntity> list = this.dynamicSqlMapper.getStudentListByClassIds_foreach_list(classIdList);          for (StudentEntity e : list) {              System.out.println(e.toString());          }      }
Reproduced http://limingnihao.iteye.com/blog/782190