Mybatis动态SQL标签
概述:
-
常用标签:
-
<select><insert><update><delete>
-
<resultMap>`、`<parameterMap>`、`<sql>`、`<include>`、`<selectKey>
-
trim|where|set|foreach|if|choose|when|otherwise|bind
-
详解标签:
< sql>标签(复用可重复使用代码)
该标签可定义能复用的sql语句片段,在执行sql语句标签中直接引用即可。
这样既可以提高编码效率,还能有效简化代码,提高可读性。
<!--定义sql片段-->
<sql id="orderAndItem">
o.order_id,o.cid,o.address,o.create_date,o.orderitem_id,i.orderitem_id,i.product_id,i.count
</sql>
<select id="findOrderAndItemsByOid" parameterType="java.lang.String" resultMap="BaseResultMap">
select
<!--引用sql片段-->
<include refid="orderAndItem" />
from ordertable o
join orderitem i on o.orderitem_id = i.orderitem_id
where o.order_id = #{orderId}
</select>
< foreach>标签-理解1
1.dao层
//批量删除
public void deleteRoleManagers(List<Integer> list);
//批量删除2
public void deleteRoles(Map<String,Object> map);
2.xml文件
<delete id="deleteRoleManagers" >
delete from user_role
where ID in
<foreach collection="list" item="id" open="(" separator="," close=")">
#{id}
</foreach>
</delete>
<delete id="deleteRoles">
delete from user_role
where ID in
<foreach collection="ids" item="id" open="(" separator="," close=")">
#{id}
</foreach>
</delete>
3.测试代码
@Test
public void deleteRoleManagers(){
RoleManagerDao dao=ctx.getBean("roleManagerDao",RoleManagerDao.class);
//User user=new User(0,"chang",27,new Date(),"hu",new Date(),"jian");
List<Integer> list=new ArrayList<Integer>();
list.add(14);
list.add(15);
dao.deleteRoleManagers(list);
System.out.println(list);
}
@Test
public void deleteRoles(){
RoleManagerDao dao=ctx.getBean("roleManagerDao",RoleManagerDao.class);
Map<String,Object> map=new HashMap<String,Object>();
List<Integer> list=new ArrayList<Integer>();
list.add(12);
list.add(13);
map.put("ids", list);
dao.deleteRoles(map);
System.out.println(map);
}
< foreach>标签-理解2
在上面这几个参数之中,index和item里面可以不变,直接复用。然后根据传入参数的类型修改一下collection类型就行。
- 单参数List的类型:
<select id="dynamicForeachTest" resultType="Blog">
select * from t_blog where id in
<foreach collection="list" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</select>
上述collection的值为list,对应的Mapper是这样的
public List<Blog> dynamicForeachTest(List<Integer> ids);
2.单参数array数组的类型:
Xml代码
<select id="dynamicForeach2Test" resultType="Blog">
select * from t_blog where id in
<foreach collection="array" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</select>
上述collection为array,对应的Mapper代码:
public List<Blog> dynamicForeach2Test(int[] ids);
3.自己把参数封装成Map的类型
Xml代码
<select id="dynamicForeach3Test" resultType="Blog">
select * from t_blog where title like "%"#{title}"%" and id in
<foreach collection="ids" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</select>
上述collection的值为ids,是传入的参数Map的key,对应的Mapper代码:
public List<Blog> dynamicForeach3Test(Map<String, Object> params);
choose when otherwise 标签
这个确定只会返回一个成立的条件,当有一个满足条件时,就跳出这个when标签了。可以直接理解为if else if end
通常与标签一起使用。
Set标签(基本用不到)
与where标签类似的,在update语句里也会碰到多个字段相关的问题。 在这种情况下,就可以使用set标签。
<update id="updateProduct" parameterType="Product" >
update product_
<set>
<if test="name != null">name=#{name},</if>
<if test="price != null">price=#{price}</if>
</set>
where id=#{id}
</update>
Trim标签(基本用不到)
自定义功能,定制想要的东西
t_
name=#{name},
price=#{price}
where id=#{id}
##### Trim标签(基本用不到)
> 自定义功能,定制想要的东西