MyBatis map File SQL statement learning Experience

Existing database tables:

CREATE TABLE' dept_p ' (' dept_id ' )varchar( +) not NULL, ' Dept_name 'varchar( -)DEFAULT NULL, ' parent_id 'varchar( +)DEFAULT NULLCOMMENT'self-correlating, many-to-one', ' state 'int( One)DEFAULT NULLCOMMENT'1 Enable 0 deactivation', ' create_by 'varchar( +)DEFAULT NULLCOMMENT'sign-in person number', ' create_dept 'varchar( +)DEFAULT NULLCOMMENT'department number of the person to whom the login belongs', ' Create_time 'timestamp  not NULL DEFAULT Current_timestamp  on UPDATE Current_timestamp, ' update_by 'varchar( +)DEFAULT NULL, ' Update_time 'timestamp  not NULL DEFAULT '0000-00-00 00:00:00',  PRIMARY KEY(' dept_id ')) ENGINE=InnoDBDEFAULTCHARSET=Utf8

As follows:

Here is the mapping file (that is, the XML file for MyBatis):

<?XML version= "1.0" encoding= "UTF-8"?><!DOCTYPE mapperpublic "-//mybatis.org//dtd Mapper 3.0//en" "Http://mybatis.org/dtd/mybatis-3-mapper.dtd "><Mappernamespace= "Cn.jinshang.mapper.DeptMapper"><!--the idea of SQL writing needs to show all the department's information, and it needs to show the ID of the parent department and the name of the parent department . -<SelectID= "Finddeptlist"Resultmap= "DEPTRM">SELECT * FROM (SELECT * to Dept_p) dleft JOIN (select dept_id as p_id,dept_name as P_name from dept_p) PON d.parent_id= P.p_idorder by dept_id</Select><!--Encapsulating Result Sets -<Resultmaptype= "Dept"ID= "DEPTRM"automapping= "true"><ID Property= "DeptID"column= "dept_id"/><ID Property= "Deptname"column= "Dept_name"/><ID Property= "State"column= "State"/><ID Property= "Createtime"column= "Create_time"/><ID Property= "UpdateTime"column= "Update_time"/><!--Package Parent Department one-on-one -<Association Property= "Parentdept"Javatype= "Dept"><ID Property= "DeptID"column= "p_id"/><result Property= "Deptname"column= "P_name"/></Association></Resultmap><!--Modify Status -<UpdateID= "Updatestate">update dept_p set state=#{state} where dept_id in (<foreachCollection= "Deptids"Item= "DeptID"Separator=",">#{deptid}</foreach>)</Update><!--Bulk Delete -<DeleteID= "Deletedepts">Delete from dept_p where dept_id in (<foreachCollection= "Array"Item= "DeptID"Separator=",">#{deptid}</foreach>)</Delete><!--adding departmental information simplifies adding only valid data -<InsertID= "Savedept">INSERT INTO dept_p (dept_id,dept_name,parent_id,state,create_time,update_time) VALUES (#{deptid},#{deptname},# {parentdept.deptid},#{state},#{createtime},#{updatetime})</Insert><!--Query information by Department ID -<SelectID= "Finddeptbyid"Resultmap= "DEPTRM">SELECT * FROM (SELECT * to Dept_p) dleft JOIN (select dept_id as p_id,dept_name as P_name from dept_p) PON d.parent_id =p.p_idwhere Dept_id=#{deptid}</Select></Mapper>

We note that the <foreach> tag is not used when checking data on the database.

　　This is because when we query the data, we query the result set with one or more records directly with the SELECT * from Dept_p. The MyBatis will automatically parse the result set and complete the mapping to Pojo. does it have a circular mechanism here???????????

We use the <foreach> tag when we modify and delete the database.

　　This is because when you modify and delete the table above, in the SQL statement in () to fill in the specific one or more values, and this one or more values we can not write dead, we put these values in an array, this time we need to use a loop to get the values in the array.

　　In layman's terms, our statements are written when queried, but when you modify and delete records in a table, the SQL statement can be written by using the <foreach> tag to give the condition.

　　It is important to note that although we use the <foreach> tag when we modify or delete it, the entire SQL statement is just a statement. We loop through <foreach> not the entire SQL statement, but only a small part of it.

MyBatis Map File SQL statement learning experience