mybatis动态SQL与批量插⼊
⼀前⾔
本篇⽂章需要有⼀定得mybatis⼊门基础才能学习；如果是初学者请参考以下链接进⾏系统学习
当然如果是资深读者，⼜没有经过系统得学习，可以直接⼊主本篇，带你⼀览动态标签使⽤⽅式；
动态SQL可以通过给定不同得条件，执⾏不同得SQL语句，实现动态SQL得⽅式就是通过mybatis提供得标签语法进⾏配置；
⼆动态SQL标签
2.1 if 标签
if 标签表⽰条件判断
customer_name 不为空时会执⾏当前if标签的内容；此时的sql 语句就是 select * from customer where and customer_name = #{customer_name}
gender不为空时会执⾏if语句；此时执⾏的sql语句就是 select * from customer where 1 = 1 and gender
= #{gender}
customer_name 和 gender 标签都不为空时；此时执⾏的sql语句就是 select * from customer where and customer_name = #{customer_name} and gender = #{gender}
<select id="getCustomer" resultType="com.ity.Customer" >
select
*
from `customer`
where 1=1
<if test="customer_name!=null and customer_name!='' ">
and `customer_name` = #{customer_name}
</if>
<if test="gender!=null and gender!=''">
and `gender` = #{gender}
</if>
</select>
2.2 where 标签
2.1 中我们在 where 后⾯使⽤ 1=1 的操作，表⽰永远为真，不是⼀个规范的数据库操作；我们通常在select 语句的条件部分会使⽤ where 标签与 if 标签搭配使⽤，此时就可以消除 1=1操作带来的负⾯影响
<select id="getCustomer" resultType="com.ity.Customer" >
select
*
from `customer`
<where>
<if test="customer_name!=null and customer_name!='' ">
and `customer_name` = #{customer_name}
</if>
<if test="gender!=null and gender!=''">
and `gender` = #{gender}
</if>
</where>
</select>
2.3 choose, when, otherwise标签
有时候需要⼀种场景就是 if else 形式的判断，如下⽰例中
当 customer_name 不为空会执⾏ when标签体的内容；此时sql 就是 select gender from customer;
当输⼊的条件customer_name 为空时会执⾏otherwise标签体的内容；此时sql就是 select * from customer;
<select id="getCustomer" resultType="com.ity.Customer" >
select
<choose>
<when test="customer_name=!null">
`gender`
</when>
<otherwise>
*
</otherwise>
</choose>
from `customer`
</select>
2.4 trim标签
先说下trim 属性代表的涵义
prefix 表⽰前缀，就是会为 trim 标签体加⼀个前缀；
prefixOverrides 表⽰会将匹配到的第⼀个字符串去除；
suffix 表⽰后缀，会为 trim 标签体加⼀个后缀内容；
suffixOverrides表⽰会将匹配到的最后⼀个字符串去除；
如下⽰例中执⾏的SQL语句就是 select * from customer where customer_name = #{customer_name}
<select id="getCustomer" resultType="com.ity.Customer" >
select
*
from `customer`
<trim prefix="where" prefixOverrides="and">
and `customer_name` = #{customer_name}
</trim>
</select>
2.5 set标签
set 标签只有在更新操作中能⽤到；如下⽰例中set标签会默认将最后⼀个多余的逗号去除；
<update id="updateCustomer" parameterType="com.ity.Customer">
update `customer`
<set>
<if test="customer_name!=null">
`customer_name` = #{customer_name},
</if>
<if test="gender!=null">
`gender` = #{gender},
</if>
<if test="telephone!=null">
`telephone` = #{telephone},
批量更新sql语句</if>
<if test="register_time!=null">
`register_time` = #{register_time},
</if>
</set>
<where>
id = #{id}
</where>
</update>
如果所有的参数都不为空执⾏的语句如下
update `customer` SET `customer_name` = ?, `gender` = ?, `telephone` = ?, `register_time` = ? WHERE id = ? set标签此时会等效于trim标签的如下形式
<trim prefix="SET" suffixOverrides=",">
...
</trim>
2.6 foreach 标签实现in
foreach 标签属性说明如下
collection 参数
open 前缀
separator 分割符
item 迭代中取得值，当⼊参数是Map.Entry或者map就是其中得值
index 迭代中得键，当⼊参数是Map.Entry或者map就是其中得键；若⼊参是组数就是索引；
<select id="getCustomerById" resultType="com.ity.Customer" >
select
*
from `customer`
<where>
<if test="ids!=null">
and id in
<foreach collection="ids" open="(" separator="," item="id" index="i"  close=")">
#{id}
</foreach>
</if>
</where>
</select>
最终得SQL语句执⾏形式如下
select * from `customer` WHERE id in ( ? , ? )
2.7 foreach标签实现批量插⼊
在xml中添加如下语句
<insert id="addCustomer" >
insert into `customer`(
`customer_name`,
`gender`,
`telephone`,
`register_time`
)values
<foreach collection="customers"  separator="," item="customer" index="i">
(
#{customer.customer_name},
#{der},
#{lephone},
#{ister_time}
)
</foreach>
</insert>
部分测试类如下在 for循环中创建3 个客户对象放⼊List，然后执⾏批量插⼊⽅法
@Test
public void testInsert(){
// 获得mapper的形式
CustomerMapper mapper = Mapper(CustomerMapper.class);
ArrayList<Customer> list = new ArrayList<>();
for (int i = 0; i < 3; i++) {
Customer customer = new Customer();
customer.setCustomer_name("知识追寻者");
customer.setGender("男");
customer.setTelephone("999"+i);
list.add(customer);
}
// 添加客户
mapper.addCustomer(list);
sqlSessionmit();
sqlSession.close();
}
最终执⾏得SQL语句格式如下
insert into `customer`( `customer_name`, `gender`, `telephone`, `register_time` )values ( ?, ?, ?, ? ) , ( ?, ?, ?, ? ) , ( ?, ?, ?, ? )
2.8 script标签
script标签⽤于注解版本得动态SQL，官⽅⽰例如下
@Update({"<script>",
"update Author",
"  <set>",
"    <if test='username != null'>username=#{username},</if>",
"    <if test='password != null'>password=#{password},</if>",
"    <if test='email != null'>email=#{email},</if>",
"    <if test='bio != null'>bio=#{bio}</if>",
"  </set>",
"where id=#{id}",
"</script>"})
void updateAuthorValues(Author author);
2.9 sql , include标签
sql 标签是个sql⽚段，如下⽰例中将查询条件抽出为⼀个sql⽚段，然后使⽤include标签实现引⽤；我们要进⾏复⽤sql⽚段时就很有⽤，减少代码量；
<sql id="condition">
<where>
<if test="customer_name!=null and customer_name!='' ">
and `customer_name` = #{customer_name}
</if>
<if test="gender!=null and gender!=''">
and `gender` = #{gender}
</if>
</where>
</sql>
<select id="getCustomer" resultType="com.ity.Customer" >
select
*
from `customer`
<include refid="condition">
</include>
</select>
2.10 bind标签
bind 元素可以从 OGNL 表达式中创建⼀个变量并将其绑定到上下⽂；使⽤bind标签好处就是在数据库替换过程中可以除去不同数据库语法得影响；如下⽰例中就可以实现在mysql，oracle，postgresql 中数据替换；如果使⽤ mysql 得concat 函数在oracle中不再适⽤；
<sql id="condition">
<where>
<if test="customer_name!=null and customer_name!='' ">
and customer_name like #{customer_name}
</if>
<if test="gender!=null and gender!=''">
and gender = #{gender}
</if>
</where>
</sql>
<select id="getCustomer" resultType="com.ity.Customer" >
<bind name="customer_name" value="'%'+customer_name+'%'"/>
select
*
from customer
<include refid="condition">
</include>
</select>
三源码
如果是初学者，有些地⽅看不懂可以查阅作者得mybatis 专栏说明有源码地址，源码中每个⽰例都有齐全得代码提供个⼈学习;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。