Mapper的xml文件基础语法笔记，增删改查，遍历

文章目录1、sql 标签2、select 标签3、where标签4、insert 标签5、update 标签6、delete 标签7、set标签8、include 标签9、if标签10、trim标签11、foreach标签12、choose标签xml文件值比较处理1、sql 标签表示单纯的一条sql语句可以直接写一条可以执行的sql语句也可以写一条给其他sql语句引用的sql语句sqlidBase_Column_List!--mbg.generated--id, busi_object_id, busi_object_code, app_id, title, page_id, form_data, page_data, creator_id, updator_id, created_time, updated_time/sql2、select 标签表示这是一条查询语句NcdpFormDefineselectByBusiObjectId(LongbusiObjectId);//mapper接口中对应的方法selectidselectByBusiObjectIdparameterTypejava.lang.LongresultMapBaseResultMapselect * from ncdp_form_define where busi_object_id #{busiObjectId,jdbcTypeBIGINT}/select3、where标签如果where内如果没有任何语句where则直接去除比如下面这个例子如果if判定为false这时where里面没有语句则不会生成wherewhereiftestdepartmentChecks ! nullbu.department_id inforeachcollectiondepartmentChecksitemitemopen(close)separator,#{item}/foreach/if/wherewhere标签可以自动添加where同时处理sql语句中第一个and关键字如下面的sql语句第一个AND会被优化剔除!-- 代码块 2-4 --!-- 根据条件查询用户 --selectidqueryUserByWhereparameterTypeuserresultTypeuserSELECT id , gender ,nickname ,mobile ,avatar From dts_user!-- where标签可以自动添加where同时处理sql语句中第一个and关键字 --whereiftestgender ! nullAND gender #{gender}/ififtestmobile ! null and mobile ! AND mobile LIKE %${mobile}%/if/where/select4、insert 标签intinsert(NcdpFormDefinerecord);insertidinsertparameterTypecom.iwhalecloud.bassc.ncdp.entity.form.NcdpFormDefine!--mbg.generated--insert into ncdp_form_define (id, busi_object_id, busi_object_code, app_id, title, page_id, form_data, page_data, creator_id, updator_id, created_time, updated_time) values (#{id,jdbcTypeBIGINT}, #{busiObjectId,jdbcTypeBIGINT}, #{busiObjectCode,jdbcTypeVARCHAR}, #{appId,jdbcTypeBIGINT}, #{title,jdbcTypeVARCHAR}, #{pageId,jdbcTypeVARCHAR}, #{formData,jdbcTypeVARCHAR}, #{pageData,jdbcTypeVARCHAR}, #{creatorId,jdbcTypeBIGINT}, #{updatorId,jdbcTypeBIGINT}, #{createdTime,jdbcTypeTIMESTAMP}, #{updatedTime,jdbcTypeTIMESTAMP})/insert5、update 标签intupdateByPrimaryKeySelective(LcdpAppGroupInforecord);updateidupdateByPrimaryKeySelectiveparameterTypecom.iwhalecloud.bassc.ncdp.entity.form.NcdpFormDefine!--mbg.generated--update ncdp_form_definesetiftestbusiObjectId ! nullbusi_object_id #{busiObjectId,jdbcTypeBIGINT},/ififtestbusiObjectCode ! nullbusi_object_code #{busiObjectCode,jdbcTypeVARCHAR},/ififtestappId ! nullapp_id #{appId,jdbcTypeBIGINT},/ififtesttitle ! nulltitle #{title,jdbcTypeVARCHAR},/ififtestpageId ! nullpage_id #{pageId,jdbcTypeVARCHAR},/ififtestformData ! nullform_data #{formData,jdbcTypeVARCHAR},/ififtestpageData ! nullpage_data #{pageData,jdbcTypeVARCHAR},/ififtestcreatorId ! nullcreator_id #{creatorId,jdbcTypeBIGINT},/ififtestupdatorId ! nullupdator_id #{updatorId,jdbcTypeBIGINT},/ififtestcreatedTime ! nullcreated_time #{createdTime,jdbcTypeTIMESTAMP},/ififtestupdatedTime ! nullupdated_time #{updatedTime,jdbcTypeTIMESTAMP},/if/setwhere id #{id,jdbcTypeBIGINT}/update6、delete 标签表示这是一条删除语句intdeleteByPrimaryKey(LonggroupId);deleteiddeleteByPrimaryKeyparameterTypejava.lang.Long!--mbg.generated--delete from ncdp_form_define where id #{id,jdbcTypeBIGINT}/delete7、set标签感觉没啥用很垃圾。目前没发现它的用处intupdateByPrimaryKeySelective(LcdpAppGroupInforecord);updateidupdateByPrimaryKeySelectiveparameterTypecom.iwhalecloud.bassc.ncdp.entity.form.NcdpFormDefine!--mbg.generated--update ncdp_form_definesetiftestbusiObjectId ! nullbusi_object_id #{busiObjectId,jdbcTypeBIGINT},/ififtestbusiObjectCode ! nullbusi_object_code #{busiObjectCode,jdbcTypeVARCHAR},/ififtestappId ! nullapp_id #{appId,jdbcTypeBIGINT},/ififtesttitle ! nulltitle #{title,jdbcTypeVARCHAR},/ififtestpageId ! nullpage_id #{pageId,jdbcTypeVARCHAR},/ififtestformData ! nullform_data #{formData,jdbcTypeVARCHAR},/ififtestpageData ! nullpage_data #{pageData,jdbcTypeVARCHAR},/ififtestcreatorId ! nullcreator_id #{creatorId,jdbcTypeBIGINT},/ififtestupdatorId ! nullupdator_id #{updatorId,jdbcTypeBIGINT},/ififtestcreatedTime ! nullcreated_time #{createdTime,jdbcTypeTIMESTAMP},/ififtestupdatedTime ! nullupdated_time #{updatedTime,jdbcTypeTIMESTAMP},/if/setwhere id #{id,jdbcTypeBIGINT}/update8、include 标签sql语句设置一个idBase_Column_Listinclude通过这个id引用这个sql语句引用过程为字符串拼接NcdpFormDefineselectByBusiObjectId(LongbusiObjectId);//mapper接口中对应的方法sqlidBase_Column_List!--mbg.generated--id, busi_object_id, busi_object_code, app_id, title, page_id, form_data, page_data, creator_id, updator_id, created_time, updated_time/sqlselectidselectByBusiObjectIdparameterTypejava.lang.LongresultMapBaseResultMapselectincluderefidBase_Column_List/from ncdp_form_define where busi_object_id #{busiObjectId,jdbcTypeBIGINT}/select9、if标签表示updatedTime 不为null时标签中的内容拼接进sql语句iftestupdatedTime ! nullupdated_time,/if表示list不为空同时不为 nulliftestlist ! null and list.size()!0type 为 1 则为真iftesttype 1.toString()10、trim标签实际上就是表示剪切的意思对字符串进行稍微的处理prefix 给sql语句拼接的前缀suffix 给sql语句拼接的后缀prefixOverrides 去除sql语句前面的关键字或者字符该关键字或者字符由prefixOverrides属性指定假设该属性指定为AND当sql语句的开头为ANDtrim标签将会去除该ANDsuffixOverrides 去除sql语句后面的关键字或者字符该关键字或者字符由suffixOverrides属性指定intinsertSelective(LcdpAppGroupInforecord);insertidinsertSelectivekeyColumngroup_idkeyPropertygroupIdparameterTypecom.iwhalecloud.bassc.ncdp.entity.appManage.LcdpAppGroupInfouseGeneratedKeystrueinsert into ncdp_app_group_infotrimprefix(suffix)suffixOverrides,iftestorgId ! nullorg_id,/ififtestgroupName ! nullgroup_name,/ififtestsort ! nullsort,/ififtestcreatorId ! nullcreator_id,/ififtestcreatedTime ! nullcreated_time,/ififtestupdateId ! nullupdate_id,/ififtestupdateTime ! nullupdate_time,/if/trimtrimprefixvalues (suffix)suffixOverrides,iftestorgId ! null#{orgId,jdbcTypeBIGINT},/ififtestgroupName ! null#{groupName,jdbcTypeVARCHAR},/ififtestsort ! null#{sort,jdbcTypeSMALLINT},/ififtestcreatorId ! null#{creatorId,jdbcTypeBIGINT},/ififtestcreatedTime ! null#{createdTime,jdbcTypeTIMESTAMP},/ififtestupdateId ! null#{updateId,jdbcTypeBIGINT},/ififtestupdateTime ! null#{updateTime,jdbcTypeTIMESTAMP},/if/trim/insert11、foreach标签collection 表示传入的对象名默认为list如果需要更改需要加注解Param如果传入的是数组 collection“list” 改为 collection“array” 即可item 表示遍历的每个对象index表示索引open该语句以什么开始close以什么结束separator在每次进行迭代之间以什么符号作为分隔符voidupdateSort(ListLcdpAppGroupInfolist);updateidupdateSortparameterTypejava.util.Listforeachcollectionlistitemitemindexindexopencloseseparator;update ncdp_app_group_infosetsort #{item.sort}/setwhere group_id #{item.groupId}/foreach/update12、choose标签在这个例子中MyBatis会根据传入参数判断哪个条件满足然后只执行相应的块里的SQL片段。如果没有条件满足则执行块的内容。selectidgetUserDetailsBasedOnConditionparameterTypemapresultTypeUserSELECT * FROM userschoosewhentestusername ! nullWHERE username #{username}/whenwhentestemail ! nullWHERE email #{email}/whenotherwise!-- 如果以上条件都不满足可以定义一个默认行为或者干脆不做任何事情 --/otherwise/choose/selectxml文件值比较处理直接用转义包裹更直观![CATA[email#{email}]]原符号’替换符号lt;lt;gt;gt;amp;apos;quot;