目录
简化sql语句动态拼串操作
一、if标签
if test=""中称为OGNL表达式
<resultMap id="myCountMap" type="com.bookStore.bean.Count">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="gender" column="gender"/>
<result property="email" column="email"/>
<result property="count" column="count"/>
<result property="adress" column="adress"/>
<result property="birthday" column="birthday"/>
</resultMap>
<!--if测试-->
<!--if test=""中称为OGNL表达式-->
<!--where标签可以去除if标签中多余的and(只能去除语句开头拼接的and)-->
<select id="getCountByCondition" resultMap="myCountMap">
select *
from count
<where>
<if test="id != null">
id>#{id}
</if>
<if test="name != null and !name.equals('')">
and name like #{name}
</if>
<if test="birthday!=null">
and birthday>#{birthday}
</if>
</where>
</select>
</mapper>
/*
* if测试*/
@Test
public void test2(){
try(final SqlSession sqlSession = sqlSessionFactory.openSession();) {
final CountMapper mapper = sqlSession.getMapper(CountMapper.class);
final Count countBefore = new Count();
countBefore.setId(1);
countBefore.setName("%尔%");
final List<Count> counts = mapper.getCountByCondition(countBefore);
for (Count count : counts) {
System.out.println("count = " + count);
}
sqlSession.commit();
}
}
1、where标签
where 元素只会在子元素返回任何内容的情况下才插入 “WHERE” 子句。而且,若子句的开头为 “AND” 或 “OR”,where 元素也会将它们去除。
where标签可以去除if标签中多余的and(但只能去除语句开头拼接的and)
2、trim标签
<select id="getCountByCondition" resultMap="myCountMap">
select *
from count
/*prefix:为下边的sql整体添加一个前缀
prefixOverrides:去除整体字符前多余的字符
suffix:为整体添加一个后缀
suffixOverrides:后边多的xxx可以去除掉
*/
<trim prefix="where" prefixOverrides="and" suffixOverrides="and">
<if test="id != null">
id>#{id} and
</if>
<if test="name != null and !name.equals('')">
name like #{name} and
</if>
<if test="birthday!=null">
birthday>#{birthday} and
</if>
</trim>
</select>
二、foreach标签
<select id="getCountByIdIn" resultMap="myCountMap">
select *
from count
where id in
/*collection:指定要遍历的集合的key
open:以什么开始
close:以什么结束
index:索引,如果遍历的是一个list,index指定变量的索引,如果遍历的是map,那么index就保存的是当前遍历的元素的key
item:每次遍历出的元素起一个变量名
separator:每次遍历的元素的分隔符*/
<foreach collection="ids" close=")" item="id_item" open="(" separator=",">
#{id_item}
</foreach>
</select>
/*
* foreach测试*/
@Test
public void test3(){
try(final SqlSession sqlSession = sqlSessionFactory.openSession();) {
final CountMapper mapper = sqlSession.getMapper(CountMapper.class);
final ArrayList<Integer> ids = new ArrayList<>();
ids.add(2);
ids.add(3);
final List<Count> counts = mapper.getCountByIdIn(ids);
for (Count count : counts) {
System.out.println("count = " + count);
}
sqlSession.commit();
}
}
三、choose标签
<select id="getCountByIdChoose" resultMap="myCountMap">
select *
from count
<where>
<choose>
<when test="id!=null">
id=#{id}
</when>
<when test="name!=null and !name.equals('')">
and name=#{name}
</when>
<otherwise>
1=1
</otherwise>
</choose>
</where>
</select>
/*
* choose测试*/
@Test
public void test4(){
try(final SqlSession sqlSession = sqlSessionFactory.openSession();) {
final CountMapper mapper = sqlSession.getMapper(CountMapper.class);
final Count countBefore = new Count();
countBefore.setId(1);
countBefore.setName("xxx");
final List<Count> counts = mapper.getCountByIdChoose(countBefore);
for (Count count : counts) {
System.out.println("count = " + count);
}
sqlSession.commit();
}
}
分支选择从多个条件中选择一个使用
四、set标签 - if结合set实现动态更新
<update id="updateCount" keyProperty="id">
update count
<set>
<if test="name != null and !name.equals('')">name=#{name},</if>
<if test="gender!=null">gender=#{gender},</if>
<if test="email != null and !email.equals('')">email=#{email},</if>
<if test="count!=null">count=#{count},</if>
<if test="adress!=null and !adress.equals('')">adress=#{adress},</if>
<if test="birthday!=null">birthday=#{birthday}</if>
</set>
where id=#{id}
</update>
五、bind 和include
/*绑定一个表达式的值到一个变量*/
<bind name="likeName" value="'%'+name+'%'"/>
六、OGNL和其他两个参数
1、OGNL
OGNL:对象导航图语言(Object Graph Navigation Language),简称OGNL,是应用于Java中的一个开源的表达式语言(Expression Language),它被集成在Struts2等框架中,作用是对数据进行访问,它拥有类型转换、访问对象方法、操作集合对象等功能。
- 访问对象属性:person.name
- 调用方法:person.getName()
- 调用静态属性、方法:@java.lang.Math@PI @java.util.UUID@randomUUID()
- 调用构造器:new com.blog.bean.Person('admin').name
- 运算符:+,-,*,/,%
- 逻辑运算符:in,not in,>,>=,<,<=,==,!=
2、其他两个参数(基本用不上)
在Mybatis中,不仅传入的参数可以用来判断,还有两个属性可以判断
_parameter:代表传入来的参数
(1)传入了单个参数,_parameter就代表这个参数
(2)传入了多个参数,_parameter代表多个参数封装成的map
_databbaseId:代表数据库厂商的id,在全局配置中由databaseIdProvider指定