MyBatis XML 映射文件实战——从基础到动态 SQL

虽然 MyBatis-Plus 帮我们省掉了基础 CRUD 的 XML但复杂查询还是得手写 SQL。这篇把 XML 映射文件的核心用法讲透。一、XML 映射文件结构?xml version1.0 encodingUTF-8 ?!DOCTYPEmapperPUBLIC-//mybatis.org//DTD Mapper 3.0//ENhttp://mybatis.org/dtd/mybatis-3-mapper.dtdmappernamespacecom.zhang.seckill.mapper.OrderMapper!-- 映射内容 --/mapper二、CRUD1. 查询selectidgetByIdresultTypecom.zhang.seckill.model.entity.OrderSELECT * FROM seckill_order WHERE id #{id}/select2. 插入返回自增主键insertidinsertuseGeneratedKeystruekeyPropertyidINSERT INTO seckill_order ( order_no, user_id, product_id, amount, status ) VALUES ( #{orderNo}, #{userId}, #{productId}, #{amount}, #{status} )/insert3. 更新updateidupdateStatusUPDATE seckill_order SET status #{status} WHERE order_no #{orderNo}/update4. 删除deleteiddeleteByIdDELETE FROM seckill_order WHERE id #{id}/delete三、resultMapresultMapidOrderDetailMaptypecom.zhang.seckill.model.vo.OrderDetailVO!-- 主键 --idcolumnidpropertyid/!-- 普通字段 --resultcolumnorder_nopropertyorderNo/resultcolumnamountpropertyamount/resultcolumnstatuspropertystatus/!-- 一对一关联 --associationpropertyuserjavaTypeUseridcolumnuser_idpropertyid/resultcolumnusernamepropertyusername//association!-- 一对多关联 --collectionpropertyitemsofTypeOrderItemidcolumnitem_idpropertyid/resultcolumnproduct_namepropertyproductName/resultcolumnquantitypropertyquantity//collection/resultMapselectidgetOrderDetailresultMapOrderDetailMapSELECT o.*, u.username, oi.id as item_id, oi.product_name, oi.quantity FROM seckill_order o LEFT JOIN user u ON o.user_id u.id LEFT JOIN order_item oi ON o.id oi.order_id WHERE o.id #{id}/select四、动态 SQL1. ifselectidsearchOrdersresultTypeOrderSELECT * FROM seckill_order WHERE 11iftestorderNo ! null and orderNo ! AND order_no LIKE CONCAT(%, #{orderNo}, %)/ififtestuserId ! nullAND user_id #{userId}/ififteststatus ! nullAND status #{status}/ififteststartTime ! nullAND create_time #{startTime}/ififtestendTime ! nullAND create_timelt; #{endTime}/if/select2. where!-- where 标签自动处理 AND/OR 前缀 --selectidsearchOrders2resultTypeOrderSELECT * FROM seckill_orderwhereiftestorderNo ! nullAND order_no #{orderNo}/ififtestuserId ! nullAND user_id #{userId}/if/where/select3. setupdateidupdateSelectiveUPDATE seckill_ordersetifteststatus ! nullstatus #{status},/ififtestpayTime ! nullpay_time #{payTime},/ififtesttradeNo ! nulltrade_no #{tradeNo},/if/setWHERE id #{id}/update4. foreach!-- 批量插入 --insertidbatchInsertINSERT INTO seckill_order (order_no, user_id, status) VALUESforeachcollectionlistitemitemseparator,(#{item.orderNo}, #{item.userId}, #{item.status})/foreach/insert!-- IN 查询 --selectidgetByIdsresultTypeOrderSELECT * FROM seckill_order WHERE id INforeachcollectionidsitemidopen(separator,close)#{id}/foreach/select5. chooseselectidsearchByConditionresultTypeOrderSELECT * FROM seckill_orderwherechoosewhentestorderNo ! nullAND order_no #{orderNo}/whenwhentestuserId ! nullAND user_id #{userId}/whenotherwiseAND status 0!-- 默认查待支付订单 --/otherwise/choose/where/select五、分页查询selectidpageOrdersresultTypeOrderSELECT * FROM seckill_orderwhereiftestquery.userId ! nullAND user_id #{query.userId}/if/whereORDER BY create_time DESC LIMIT #{offset}, #{size}/selectpublicListOrderpageOrders(Param(query)OrderQueryquery,Param(offset)intoffset,Param(size)intsize);六、代码生成!-- MyBatis-Plus 代码生成器自动生成的 XML 映射文件 --mappernamespacecom.zhang.seckill.mapper.OrderMapper!-- 通用查询映射结果 --resultMapidBaseResultMaptypeOrderidcolumnidpropertyid/resultcolumnorder_nopropertyorderNo/resultcolumnuser_idpropertyuserId/resultcolumnstatuspropertystatus//resultMap!-- 通用查询结果列 --sqlidBase_Column_Listid, order_no, user_id, status/sql/mapper 觉得有用的话点赞 关注【张老师技术栈】吧