作者：蕶ok薍 | 来源：互联网 | 2023-08-08 16:36
MyBatis的动态SQL使用OGNL表达式http:commons.apache.orgpropercommons-ognllanguage-guide.html在XML中的一些
MyBatis 的动态 SQL 使用 OGNL 表达式
http://commons.apache.org/proper/commons-ognl/language-guide.html
在 XML 中的一些特殊字符需要转义，参考 HTML ISO-8859-1 手册
http://www.w3school.com.cn/tags/html_ref_entities.html
https://www.cnblogs.com/jhxxb/p/10637219.html
<select id="selectMyUserByIF" resultType="myUser" parameterType="myUser">
select * from myuser where
<if test="id != null">
id = #{id}
if>
<if test="name != null && name != """>
and name like #{name}
if>
<if test="age == 0 or age == 1">
and age = #{age}
if>
select>
入参没有ID属性时会报错，会拼接成 where and，解决：可以在 where 后添加一个恒成立，如 1=1。或者使用 where 标签，会自动去除 SQL 中多出来的 and 或 or
二、WHERE
<select id="selectMyUserByIF" resultType="myUser" parameterType="myUser">
select * from myuser
<where>
<if test="id != null">
id = #{id}
if>
<if test="name != null and name != ''.toString()">
and name like #{name}
if>
<if test="age == 0 or age == 1">
and age = #{age}
if>
where>
select>
where 标签只能去除第一个 and 或 or，所以不能把条件符号写多个或写在后面
三、SET
<update id="updateMyUser" parameterType="myUser">
update myuser
<set>
<if test="name != null">
name = #{name},
if>
<if test="age != null">
age = #{age}
if>
set>
where id = #{id}
update>
四、TRIM
<select id="selectMyUserByIF" resultType="myUser" parameterType="myUser">
select * from myuser
<trim prefix="where" prefixOverrides="AND | OR" suffixOverrides="and">
<if test="id != null">
id = #{id} and
if>
<if test="name != null and name.trim() != ''">
name like #{name}
if>
trim>
select>
trim 标签可以替代 where 标签
<trim prefix="WHERE" prefixOverrides="AND |OR ">
...
trim>
trim 标签也可以替代 set 标签
<trim prefix="set" suffixOverrides=",">
...
trim>
五、CHOOSE
<select id="selectMyUserByIF" resultType="myUser" parameterType="myUser">
select * from myuser
<where>
<choose>
<when test="id != null">
id = #{id}
when>
<when test="name != null and name.trim() != ''">
name like #{name}
when>
<otherwise>
age = 22
otherwise>
choose>
where>
select>
六、FOREACH
Map 入参
<select id="selectMyUserByArray" resultType="myUser">
select * from myuser where id in
<foreach collection="ids" item="item" index="index" separator="," open="(" close=")">
#{item}
foreach>
select>
测试代码
public static void main(String[] args) {
SqlSession session = null;
try {
InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
session = sqlSessionFactory.openSession();
MyUserMapper mapper = session.getMapper(MyUserMapper.class);
Map ids = new HashMap<>(10);
ids.put("ids",Arrays.asList(7,8,9,10));
ids.put("b",8);
System.out.println(mapper.selectMyUserByArray(ids));
} catch (IOException e) {
e.printStackTrace();
} finally {
if (session != null) {
session.close();
}
}
}
List 入参
<select id="selectMyUserByArray" resultType="myUser">
select * from myuser where id in
<foreach collection="list" item="item" index="index" separator="," open="(" close=")">
#{item}
foreach>
select>
Array 入参
<select id="selectMyUserByArray" resultType="myUser">
select * from myuser
<foreach collection="array" item="item" index="index" separator="," open="where id in (" close=",20)">
#{item}
foreach>
select>
七、内置参数
<select id="selectMyUserById" resultType="myUser">
<if test="_databaseId == 'mysql'">
select * from myuser
<if test="_parameter != null">
where id = #{_parameter}
if>
if>
<if test="_databaseId == 'oracle'">
select * from oracle_myuser
<if test="_parameter != null">
where name = #{_parameter.name}
if>
if>
select>
八、BIND
<select id="selectMyUserByNameLike" resultType="myUser" parameterType="string">
select * from myuser where name like #{name}
select>
例 SQL 映射想变为模糊查询有几种方式
1.传入参数时加上 %
2.改用 ${name} 来取值。name 取值会报错，用 _parameter 取值
<select id="selectMyUserByNameLike" resultType="myUser">
select * from myuser where name like '%${_parameter}%'
select>
3.使用 bind 标签
<select id="selectMyUserByNameLike" resultType="myUser">
<bind name="_name" value="'%'+_parameter+'%'"/>
select * from myuser where name like #{_name}
select>
九、SQL 复用
<sql id="columnNames">
<if test="_databaseId=='mysql'">
${id},name,age
if>
<if test="_databaseId=='oracle'">
xxx,xxx,xxx
if>
sql>
<select id="selectMyUserByNameLike" resultType="myUser" parameterType="string">
select
<include refid="columnNames">
<property name="id" value="id"/>
include>
from myuser where name like #{name}
select>
可以使用其他语言来写动态 SQL：MyBatis-Velocity、MyBatis-FreeMarker 和自带的 XML
http://www.mybatis.org/velocity-scripting/
http://www.mybatis.org/freemarker-scripting/
https://www.jianshu.com/p/cecc187410be
https://mybatis.org/mybatis-dynamic-sql/
京公网安备 11010802041100号