Mybatis 常用语法
like
subject_name LIKE CONCAT('%', #{subjectName,jdbcType=VARCHAR}, '%')
或
subject_name LIKE CONCAT('%', TRIM(#{subjectName,jdbcType=VARCHAR}), '%')
in
<if test="status != null">
and status in
<foreach item="item" collection="status" separator="," open="(" close=")" index="index">
#{item, jdbcType=TINYINT}
</foreach>
</if>
if
<if test="subjectNo != null and subjectNo != '' ">
and subject_no = #{subjectNo,jdbcType=VARCHAR}
</if>
choose
<choose>
<!-- 周 -->
<when test="dateType == 'WEEK' ">
concat(DATE_FORMAT(statistic_time ,'%X-%V'),'(周)') as statisticTime,
</when>
<!-- 月 -->
<when test="dateType == 'MONTH' ">
DATE_FORMAT(statistic_time ,'%Y-%m') as statisticTime,
</when>
<!-- 年 -->
<when test="dateType == 'YEAR' ">
DATE_FORMAT(statistic_time ,'%Y') as statisticTime,
</when>
<!-- 默认 日 -->
<otherwise>
DATE_FORMAT(statistic_time ,'%Y-%m-%d') as statisticTime,
</otherwise>
</choose>
CDATA
<![CDATA[
a < 1
]]>
头引入
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
结果map映射
<resultMap id="itemPublish" type="itemPublish" >
<id column="id" property="id" jdbcType="BIGINT" />
<result column="platform_id" property="platformId" jdbcType="BIGINT" />
<result column="seller_id" property="sellerId" jdbcType="BIGINT" />
<result column="shop_id" property="shopId" jdbcType="BIGINT" />
<result column="supply_seller_id" property="supplySellerId" jdbcType="BIGINT" />
<result column="supply_shop_id" property="supplyShopId" jdbcType="BIGINT" />
<result column="operator_id" property="operatorId" jdbcType="BIGINT" />
<result column="item_name" property="itemName" jdbcType="VARCHAR" />
<result column="cid" property="cid" jdbcType="BIGINT" />
<result column="second_cid" property="secondCid" jdbcType="BIGINT" />
<result column="brand_id" property="brandId" jdbcType="BIGINT" />
<result column="unit" property="unit" jdbcType="VARCHAR" />
<result column="origin" property="origin" jdbcType="VARCHAR" />
<result column="ad" property="ad" jdbcType="VARCHAR" />
<result column="industry_label" property="industryLabel" jdbcType="VARCHAR" />
<result column="category_attributes" property="categoryAttributes" jdbcType="VARCHAR" />
<result column="spec_attributes" property="specAttributes" jdbcType="VARCHAR" />
<result column="sale_status" property="saleStatus" jdbcType="INTEGER" />
<result column="packing_list" property="packingList" jdbcType="VARCHAR" />
<result column="describe_url" property="describeUrl" jdbcType="VARCHAR" />
<result column="created" property="created" jdbcType="TIMESTAMP"/>
<result column="modified" property="modified" jdbcType="TIMESTAMP" />
<result column="shop_cid" property="shopCid" jdbcType="BIGINT" />
<result column="listing_time" property="listingTime" jdbcType="TIMESTAMP" />
<result column="delisting_time" property="delistingTime" jdbcType="TIMESTAMP"/>
<result column="operator" property="operator" jdbcType="INTEGER" />
<result column="add_source" property="addSource" jdbcType="INTEGER" />
<result column="store_status" property="storeStatus" jdbcType="INTEGER" />
<result column="yn" property="yn" jdbcType="INTEGER" />
<result column="sku_type" property="skuType" jdbcType="INTEGER" />
<result column="attributes" property="attributes" jdbcType="INTEGER" />
<result column="model_code" property="modelCode" jdbcType="INTEGER" />
<result column="bar_code" property="barCode" jdbcType="INTEGER" />
<result column="product_code" property="productCode" jdbcType="INTEGER" />
<result column="sku_status" property="skuStatus" jdbcType="INTEGER" />
<result column="weight" property="weight" jdbcType="INTEGER" />
<result column="picture_url" property="pictureUrl" jdbcType="VARCHAR" />
<result column="alt_images" property="altImages" jdbcType="VARCHAR" />
<result column="sort_number" property="sortNumber" jdbcType="INTEGER" />
<result column="copy_status" property="copyStatus" jdbcType="INTEGER" />
<result column="publishuserId" property="publishuserId" jdbcType="BIGINT" />
<result column="cash_coupon_support" property="cashCouponSupport" jdbcType="INTEGER" />
<result column="meet_coupon_support" property="meetCouponSupport" jdbcType="INTEGER" />
<result column="vip_discount_support" property="vipDiscountSupport" jdbcType="INTEGER" />
<result column="detail_page_qrcode" property="detailPageQrcode" jdbcType="VARCHAR" />
</resultMap>
标签定义
<sql id="Example_Where_Clause" >
<where >
<foreach collection="oredCriteria" item="criteria" separator="or" >
<if test="criteria.valid" >
<trim prefix="(" suffix=")" prefixOverrides="and" >
<foreach collection="criteria.criteria" item="criterion" >
<choose >
<when test="criterion.noValue" >
and ${criterion.condition}
</when>
<when test="criterion.singleValue" >
and ${criterion.condition} #{criterion.value}
</when>
<when test="criterion.betweenValue" >
and ${criterion.condition} #{criterion.value} and #{criterion.secondValue}
</when>
<when test="criterion.listValue" >
and ${criterion.condition}
<foreach collection="criterion.value" item="listItem" open="(" close=")" separator="," >
#{listItem}
</foreach>
</when>
</choose>
</foreach>
</trim>
</if>
</foreach>
</where>
</sql>
insert语句
<insert id="itemPublish" parameterType="item" useGeneratedKeys="true" keyProperty="id">
insert into item (platform_id, seller_id,
shop_id, supply_seller_id, supply_shop_id, operator_id,
item_name, cid,second_cid, brand_id,
unit, origin, ad,
category_attributes, spec_attributes, sale_status,
packing_list, describe_url, created,
modified, shop_cid, listing_time,
delisting_time, operator,
add_source, store_status, yn,copy_status,publishuserId,industry_label,
cash_coupon_support,meet_coupon_support,vip_discount_support,detail_page_qrcode
)
values ( #{platformId,jdbcType=BIGINT}, #{sellerId,jdbcType=BIGINT},
#{shopId,jdbcType=BIGINT}, #{supplySellerId,jdbcType=BIGINT}, #{supplyShopId,jdbcType=BIGINT}, #{operatorId,jdbcType=INTEGER},
#{itemName,jdbcType=VARCHAR}, #{cid,jdbcType=BIGINT},#{secondCid}, #{brandId,jdbcType=BIGINT},
#{unit,jdbcType=VARCHAR}, #{origin,jdbcType=VARCHAR}, #{ad,jdbcType=VARCHAR},
#{categoryAttributes,jdbcType=VARCHAR}, #{specAttributes,jdbcType=VARCHAR}, #{saleStatus,jdbcType=INTEGER},
#{packingList,jdbcType=VARCHAR}, #{describeUrl,jdbcType=VARCHAR}, now(),
now(),#{shopCid,jdbcType=BIGINT},#{listingTime,jdbcType=TIMESTAMP},
#{delistingTime,jdbcType=TIMESTAMP}, #{operator,jdbcType=INTEGER},
#{addSource,jdbcType=INTEGER},#{storeStatus,jdbcType=INTEGER},1,#{copyStatus,jdbcType=INTEGER}, #{publishuserId,jdbcType=BIGINT},#{industryLabel,jdbcType=VARCHAR},
#{cashCouponSupport,jdbcType=INTEGER},#{meetCouponSupport,jdbcType=INTEGER},#{vipDiscountSupport,jdbcType=INTEGER},#{detailPageQrcode,jdbcType=VARCHAR}
)
</insert>
<insert id="insertSelective" parameterType="com....ItemImport" >
insert into item_import_tmp
<trim prefix="(" suffix=")" suffixOverrides="," >
<if test="id != null" >
id,
</if>
<if test="platformId != null" >
platform_id,
</if>
<if test="itemName != null" >
item_name,
</if>
<if test="batchNo != null" >
batch_no,
</if>
<if test="state != null" >
state,
</if>
<if test="failNum != null" >
fail_num,
</if>
<if test="failReason != null" >
fail_reason,
</if>
<if test="created != null" >
created,
</if>
<if test="modified != null" >
modified,
</if>
<if test="yn != null" >
yn,
</if>
<if test="itemInfo != null" >
item_info,
</if>
</trim>
<trim prefix="values (" suffix=")" suffixOverrides="," >
<if test="id != null" >
#{id,jdbcType=BIGINT},
</if>
<if test="platformId != null" >
#{platformId,jdbcType=BIGINT},
</if>
<if test="itemName != null" >
#{itemName,jdbcType=VARCHAR},
</if>
<if test="batchNo != null" >
#{batchNo,jdbcType=VARCHAR},
</if>
<if test="state != null" >
#{state,jdbcType=INTEGER},
</if>
<if test="failNum != null" >
#{failNum,jdbcType=INTEGER},
</if>
<if test="failReason != null" >
#{failReason,jdbcType=VARCHAR},
</if>
<if test="created != null" >
#{created,jdbcType=TIMESTAMP},
</if>
<if test="modified != null" >
#{modified,jdbcType=TIMESTAMP},
</if>
<if test="yn != null" >
#{yn,jdbcType=INTEGER},
</if>
<if test="itemInfo != null" >
#{itemInfo,jdbcType=LONGVARCHAR},
</if>
</trim>
</insert>
<!-- 批量添加item_picture信息 -->
<insert id="insertItemPictureList" parameterType="java.util.List">
insert into item_picture( platform_id, item_id, shop_id, seller_id, picture_url,alt_images, sort_number, created, modified, yn)
values
<foreach collection="list" item="itemPicture" index="index" separator=",">
(#{itemPicture.platformId,jdbcType=BIGINT},
#{itemPicture.itemId,jdbcType=BIGINT},
#{itemPicture.shopId,jdbcType=BIGINT},
#{itemPicture.sellerId,jdbcType=BIGINT},
#{itemPicture.pictureUrl,jdbcType=VARCHAR},
#{itemPicture.altImages,jdbcType=VARCHAR},
#{itemPicture.sortNumber,jdbcType=INTEGER},
now(),now(), 1)
</foreach>
</insert>
删除语句
<delete id="deleteByPrimaryKey" parameterType="java.lang.Long" >
delete from item_import_tmp
where id = #{id,jdbcType=BIGINT}
</delete>
<delete id="deleteByExample" parameterType="com.jd.ecc.b2b.item.domain.item.domain.ItemImportExample" >
delete from item_import_tmp
<if test="_parameter != null" >
<include refid="Example_Where_Clause" />
</if>
</delete>
修改语句
<update id="updateSaleStatus" parameterType="map">
UPDATE item
<set>
store_status = #{storeStatus},
<if test="saleStatus != null">
sale_status = #{saleStatus}
</if>
</set>
WHERE id = #{itemId}
AND platform_id = #{platformId}
</update>
<update id="updateItem" parameterType="itemPublish">
update item set
operator_id=#{operatorId,jdbcType=INTEGER},item_name=#{itemName,jdbcType=VARCHAR},cid=#{cid,jdbcType=BIGINT},second_cid=#{secondCid},brand_id=#{brandId,jdbcType=BIGINT},
unit=#{unit,jdbcType=VARCHAR},origin=#{origin,jdbcType=VARCHAR},ad=#{ad,jdbcType=VARCHAR},category_attributes=#{categoryAttributes,jdbcType=VARCHAR},
spec_attributes=#{specAttributes,jdbcType=VARCHAR},sale_status=#{saleStatus},store_status = #{storeStatus},
packing_list=#{packingList,jdbcType=VARCHAR},describe_url=#{describeUrl,jdbcType=VARCHAR},shop_cid=#{shopCid,jdbcType=BIGINT},
operator=#{operator,jdbcType=INTEGER},industry_label=#{industryLabel},
cash_coupon_support=#{cashCouponSupport},meet_coupon_support=#{meetCouponSupport},vip_discount_support=#{vipDiscountSupport}
<if test="publishuserId != null">
,publishuserId=#{publishuserId}
</if>
where id=#{id,jdbcType=BIGINT} and platform_id=#{platformId} and yn=1
</update>
查询语句
<select id="queryItems" parameterType="sellSupplyItemInfoVo" resultMap="SellSupplyItemMap">
select DISTINCT i.id itemId,i.platform_id, i.item_name, sii.supply_seller_id, sii.supply_shop_id,ip.picture_url,
category.cname1, category.cname2, category.cname3, category.cname4
from shop_item_library sii
JOIN item i
on sii.item_id=i.id
and i.store_status = 30
and sii.supply_status = 0
JOIN item_picture ip
on i.id = ip.item_id
LEFT JOIN item_sku isk
on i.id = isk.item_id
LEFT JOIN item_brand ib
on i.brand_id = ib.id
JOIN
(
SELECT c4.c_name cname1,c3.c_name cname2,c2.c_name cname3,c1.c_name cname4,c1.cid cid
FROM item_category c1
LEFT JOIN item_category c2 ON c1.parent_cid = c2.cid
LEFT JOIN item_category c3 ON c2.parent_cid = c3.cid
LEFT JOIN item_category c4 ON c3.parent_cid = c4.cid
WHERE
c1.platform_id = #{platformId}
<if test="cid != null and cid != ''">
And c1.cid= #{cid}
</if>
) category ON i.cid = category.cid
where sii.supply_seller_id = #{supplySellerId}
AND i.platform_id = #{platformId}
<if test="productCode != null and productCode != ''">
AND isk.product_code LIKE CONCAT('%', #{productCode},'%' )
</if>
<if test="modelCode != null and modelCode != ''">
AND isk.model_code LIKE CONCAT('%', #{modelCode},'%' )
</if>
<if test="itemName != null and itemName != ''">
AND i.item_name LIKE CONCAT('%', #{itemName},'%' )
</if>
</select>
<select id="selectByExample" resultMap="BaseResultMap" parameterType="com....ItemImportExample" >
select
<if test="distinct" >
distinct
</if>
'true' as QUERYID,
<include refid="Base_Column_List" />
from item_import_tmp
<if test="_parameter != null" >
<include refid="Example_Where_Clause" />
</if>
<if test="orderByClause != null" >
order by ${orderByClause}
</if>
</select>
时间判断
<if test="endTime != null">
AND up.audit_time < DATE_ADD(#{endTime}, INTERVAL 1 DAY)
</if>
一次更新多条
<update id="batchUpdate" parameterType="list">
update item_category
<trim prefix="set" suffixOverrides=",">
<trim prefix="cate_code_path =case" suffix="end,">
<foreach collection="list" item="i" index="index">
<if test="i.cateCodePath!=null">
when (cid = #{i.cid} and platform_id = #{i.platformId}) then #{i.cateCodePath}
</if>
</foreach>
</trim>
<trim prefix="cate_name_path =case" suffix="end," >
<foreach collection="list" item="i" index="index">
<if test="i.cateNamePath!=null">
when (cid = #{i.cid} and platform_id = #{i.platformId}) then #{i.cateNamePath}
</if>
</foreach>
</trim>
</trim>
where
<foreach collection="list" separator="or" item="i" index="index">
(cid = #{i.cid} and platform_id = #{i.platformId})
</foreach>
</update>
一对多查询
<!-- 一对多：user、role -->
<resultMap id="userIncludeRolesMap" extends="userMap" type="com.abc.entity.SysUser">
<collection property="roleList" columnPrefix="role_" resultMap="com.abc.dao.SysRoleMapper.roleMap">
</collection>
</resultMap>
<select id="selectUserIncludeRoles" resultMap="userIncludeRolesMap">
select
u.*,
r.rid as role_rid,
r.rname as role_rname,
r.rdesc as role_rdesc,
r.rval as role_rval,
r.created as role_created,
r.updated as role_updated
from sys_user u
left join sys_user_role ur on u.uid = ur.user_id
left join sys_role r on ur.role_id = r.rid
<where>
<if test="nick != null and nick != ''">
u.`nick` like concat('%',#{nick},'%')
</if>
</where>
order by u.uid
</select>
多表关联查询
<select id="getPermsByUserId" resultType="com.abc.entity.SysPerm">
SELECT p.pname,p.pval FROM sys_perm p, sys_role_perm rp, sys_user_role ur
WHERE p.pval = rp.perm_val AND ur.role_id = rp.role_id
AND ur.user_id = #{userId}
</select>
<select id="getPermsByRoleId" resultType="com.abc.entity.SysPerm">
SELECT p.pval,p.ptype,p.leaf FROM sys_perm p, sys_role_perm rp
WHERE p.pval = rp.perm_val AND rp.role_id = #{roleId}
</select>
【推荐】国内首个AI IDE，深度理解中文开发场景，立即下载体验Trae
【推荐】编程新体验，更懂你的AI，立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包，你的智能百科全书，全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell：AI 加持，快人一步
· 开发者必知的日志记录最佳实践
· SQL Server 2025 AI相关能力初探
· Linux系列：如何用 C#调用 C方法造成内存泄露
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 记一次.NET内存居高不下排查解决与启示
· 阿里最新开源QwQ-32B，效果媲美deepseek-r1满血版，部署成本又又又降低了！
· Manus重磅发布：全球首款通用AI代理技术深度解析与实战指南
· 开源Multi-agent AI智能体框架aevatar.ai，欢迎大家贡献代码
· 被坑几百块钱后，我竟然真的恢复了删除的微信聊天记录！
· AI技术革命，工作效率10个最佳AI工具
2018-01-11 zTree使用总结
2018-01-11 RabbitMQ+SpringBoot
2018-01-11 RabbitMQ+Zookeeper+Dubbo+Nginx+Mysql+Redis搭建
2018-01-11 Spring boot集成RabbitMQ
2018-01-11 Springboot中引入Thymeleaf
2018-01-11 git使用说明
2018-01-11 Eclipse快捷键