[Toc]
一. 入参是Map,取key和value
dao接口:
int updateByBatch(@Param("content") Map<String, Integer> alreadySoldNumMap);
mapper文件:
<update id="updateByBatch" parameterType="java.util.Map">
update COUPON_CATEGORY
<trim prefix="set" suffixOverrides=",">
<trim prefix="ALREADY_SOLD_NUM = case" suffix="end,">
<foreach collection="content.keys" item="key" index="index">
when ID=#{key} then ALREADY_SOLD_NUM+#{content[${key}]}
</foreach>
</trim>
</trim>
where
<!-- 循环key-->
<foreach collection="content.keys" separator="or" item="key" index="index">
ID=#{key}
</foreach>
</update>
content.keys 得到所有的key; content.values 得到所有的value; 这种方式#{content[${key}]}获取map中的value，传递的map中的key只能是String类型， 如果是其他类型，得到的value是null。#{content[${key}]}还可以写成${content[key]}方式。 来自https://blog.csdn.net/shiqijiamengjie/article/details/77448829
1. 如果固定key:
<select id="selectRule" parameterType="Map" resultType="com.ourangel.weixin.domain.Rule">
SELECT
ruleId,msgType,event,respId,reqValue,firstRespId,createDate,yn
FROM oal_tb_rule
WHERE yn = 1
<if test="_parameter.containsKey('msgType')">
AND msgType = #{msgType,jdbcType=VARCHAR})
</if>
<if test="_parameter.containsKey('event')">
AND event = #{event,jdbcType=VARCHAR})
</if>
</select>
2. 如果value是list:
入参:
HashMap<String,Object> map = new HashMap<String, Object>();
map.put("creator", "creator");
map.put("createdate", "createdate");
String[] ids = {"1","2"};
map.put("ids", ids );
mapper:
<update id="publishT00_notice" parameterType="Map">
update test
set createdate = #{createdate},
creator = #{creator}
where id in
<foreach collection="ids" item="id" separator="," open="(" close=")">
#{id}
</foreach>
</update>
或者:
<select id="getOrgCodeLinkByMultiOrgIds" resultType="com.jieshun.jht.jportal.organize.entity.OrgInfo" parameterType="map">
<foreach collection="content.keys" item="key" separator="UNION ALL" >
SELECT
#{key} AS id,
CONCAT(',',GROUP_CONCAT(ORG_CODE),',') AS orgCodeLink
FROM JBP_ORG_INFO
WHERE ID IN
<foreach collection="content[key]" item="id" open="(" separator="," close=")">
#{id}
</foreach>
</foreach>
</select>
二. collection的使用:
1:
<resultMap id="customerInfoMap" type="com.jieshun.jht.jplatform.entity.CustomerInfo">
<collection property="customerServ" ofType="com.jieshun.jht.jplatform.entity.CustomerReference" >
// customerServ 是实体类中集合的属性名
<result column="serviceNo" jdbcType="VARCHAR" property="serviceNo" />
// column 是数据库中的字段名 ; property 是实体类的属性名
<result column="serviceName" jdbcType="VARCHAR" property="serviceName" />
</collection>
</resultMap>
2:
<resultMap id="queryDetailMap" type="com.jieshun.jht.jplatform.entity.Role">
<id column="ROLE_ID" property="roleId" />
<result column="ROLE_NO" property="roleNo" />
<result column="ROLE_NAME" property="roleName" />
<result column="ROLE_TYPE" property="roleType" />
<result column="ROLE_DESC" property="roleDesc" />
<collection property="permissions" column="ID" ofType="com.jieshun.jht.jplatform.entity.Function" resultMap="permissionsMap"/>
</resultMap>
<resultMap id="permissionsMap" type="com.jieshun.jht.jplatform.entity.Function" >
<id column="ID" property="id" />
<result column="FUNC_NO" property="funcNo" />
<result column="FUNC_NAME" property="funcName" />
</resultMap>
<select id="queryRoleByRoleNo" resultMap="queryDetailMap">
SELECT
R.ROLE_ID,
R.ROLE_NAME,
R.ROLE_DESC,
R.ROLE_NO,
R.ROLE_TYPE,
FC.ID,
FC.FUNC_NO,
FC.FUNC_NAME
FROM
JPF_ROLE R
LEFT JOIN JPF_ROLE_PERM RP ON R.ROLE_ID = RP.ROLE_ID
LEFT JOIN JPF_FUNCTION FC ON RP.FUNC_ID = FC.ID AND FC.FUNC_STATUS = 'NORMAL'
WHERE
R.ROLE_NO = #{roleNo} AND ROLE_STATUS !='DELETE'
</select>
3:
<!--传多个参数-->
<resultMap id="customerInfoMap" type="com.jieshun.jht.jplatform.entity.CustomerInfo">
<result column="id" property="id"/>
<collection property="customerServ" column="{id=id_name,age=age}" select="querySerByAcct" ofType="com.jieshun.jht.jplatform.entity.CustomerReference" />
</resultMap>
<select id="querySerByAcct" resultType="com.jieshun.jht.jplatform.entity.CustomerReference">
SELECT
js.SERVICE_NO AS serviceNo,
js.SERVICE_NAME AS serviceName
FROM JPF_CUSTOMER_SERVICE jcs
INNER JOIN JPF_SERVICE AS js ON jcs.SERVICE_ID = js.ID
WHERE jcs.CUSTOMER_ID = #{id_name}
</select>
三. chose when otherwise
四. foreach 的使用
UPDATE
JPF_CUSTOMER_SERVICE
SET
OPEN_ID = #{openId},
OPEN_TIME = SYSDATE()
WHERE CUSTOMER_ID = #{customerId}
AND SERVICE_ID IN
<foreach collection="serviceIds" item="serivceId" index="index" open="(" separator="," close=")">
#{serivceId}
</foreach>
item 相当于foreach中的变量 ; collection : 入参变量名 ; index: 循环的下标 ; separator : 一次循环的结束符合 ; open : 循环的开始符号 ; close : 循环的结束符号
五. 关于批量插入(返主键)
<insert id="batchCreateGateway" useGeneratedKeys="true" keyProperty="gatewayId" parameterType="java.util.List">
INSERT INTO ITR_BAS_GATEWAY_INFO
(
GATEWAY_NO,GATEWAY_NAME,MASTER_CUSTOMER_ID,OPERATE_FLAG,STATUS,
PROJECT_NO,CREATE_TIME,CREATE_ID,CREATE_ACC,UPDATE_TIME,UPDATE_ID
)VALUES
<foreach collection="list" item="gateway" index="index" separator=",">
(#{gateway.gatewayNo},#{gateway.gatewayName},#{gateway.masterCustomerId},#{gateway.operateFlag},#{gateway.status},
#{gateway.projectNo},SYSDATE(),#{gateway.accountId},#{gateway.accountNo},SYSDATE(),#{gateway.accountId}
)
</foreach>
</insert>
dao层:
public void batchCreateGateway(List<GatewayOperateInput> gatewayInputs);
解释:
1. 如果用了@Param注解,则不会返回主键
2. 需要返回主键时,加上 useGeneratedKeys=“true” keyProperty=“gatewayId” keyProperty表示list中对象的主键字段,则会将返回的主键值注入到List入参中
3. 如果入参是List,Mybatis会默认自动转化成map,key是"list",value是你的List入参
六. 指定表名操作
<select id="getByCode" resultType="com.wwy.us.mdm.coredata.entity.BaseDO">
select id, is_latest, status
from ${tableName}
where system_code = #{sysCode}
</select>
“$” 符号不会预编译, 会直接填值, 如果用了 “#” 则会当成字符串处理(加上单引号)