Mybatis标签&工具类
各类标签使用
1 sql标签重用代码块:
<sql id="edocEntrycolumn">
id,cid,title,summary,uploadUser,createDate
</sql>
当需要引用此代码块时
<include refid="edocEntrycolumn"/>
2 if标签标准用法:
<if test="cid != null and cid != 0">
cid = #{cid}
</if>
属性说明:
test:需要判断的的参数,&&、||需要使用and和or代替
3 where+if 标签:
<where>
<!--html语法字符-->
id < 10
<if test="cid != null and cid != 0">
and cid = #{cid}
</if>
<if test="summary != null">
and summary like concat('%',#{summary},'%')
</if>
</where>
where标签主要功能:
1.where可以自动剔除首个and
2.如果有查询条件,自动添加where关键字,如果没有任何条件,也不会触发where关键字
注意:
1.动态sql语句方便处理多参数查询,条件能够动态拼接,但是在企业开发中,不可能都是动态的,因为一旦条件确实,会导致全表扫描,影响性能,所以必须要保证有一个固定条件
2.在sql中符号需要使用(Html语法字符)的形式表现
3.1 trim+if替换where+if:
<trim prefix=" where " prefixOverrides="and|or " suffix=";">
<if test="cid != null and cid != 0">
and cid = #{cid}
</if>
<if test="summary != null">
and summary like concat('%',#{summary},'%')
</if>
</trim>
属性说明:
prefix前缀字符串,prefixOverrides去除开头匹配的字符串
suffix后缀字符串,suffixOverrides去除结尾匹配的字符串
4 set+if标签:
<set>
<if test="cid != null and cid != 0">
cid = #{cid},
</if>
<if test="title != null and title != ''">
title = #{title},
</if>
<if test="summary != null ">
summary = #{summary},
</if>
<if test="uploadUser != null">
uploadUser = #{uploadUser},
</if>
</set>
set+if标签主要功能(一般适用于update操作):
1.一般适用于update操作
2.可以自动增加set关键字
3.自动过滤末尾“,”符号
4.1 trim+if替换set+if
<trim prefix=" set " prefixOverrides=",">
<if test="_parameter.cid != null and _parameter.cid != 0">
,cid = #{cid}
</if>
<if test="_parameter.title != null and _parameter.title.trim() != ''">
,title = #{title}
</if>
<if test="_parameter.summary != null ">
,summary = #{summary}
</if>
<if test="_parameter.uploadUser != null">
,uploadUser = #{uploadUser}
</if>
</trim>
方法:使用prefixOverrides干掉前置的第一个逗号,prefix中添加set标签
5 choose+when标签
<where>
<!-- 一般配合where条件使用,实现多个条件中一个条件 -->
<choose>
<when test="title != null">
title = #{title}
</when>
<when test="summary != null">
summary = concat('%',#{summary},'%')
</when>
<when test="uploadUser != null">
uploadUser = #{uploadUser}
</when>
<otherwise>
createDate > now()
</otherwise>
</choose>
</where>
类似于Java中switch+case的用法,只选择其中一条执行
foreach的4种写法:
属性说明:
1.collection:指向一个可遍历的集合,可以是传进来的集合起得别名,或者param1,list这种单参数的默认写法,
2.item:类似jstl中foreach的用法,代表了被循环遍历数组中每次遍历的值
3.index:index就是index
4.open:开头
5.close:结尾
6.sql语句中的分隔符
写法一:
起别名①
<!--写法1:起别名①-->
<where>
<foreach collection="ids" item="id" index="index" open=" id in (" close=")" separator=",">
#{id}
</foreach>
</where>
起别名②
<!--写法1:起别名②-->
<where>
<foreach collection="param1" item="id" index="index" open=" id in (" close=")" separator=",">
#{id}
</foreach>
</where>
写法二:不起别名
<where>
<foreach collection="list" item="id" index="index" open=" id in (" close=")" separator=",">
#{id}
</foreach>
</where>
写法三:不使用where
<foreach collection="ids" item="id" index="index" open=" where " separator=" or ">
id = #{id}
</foreach>
直接select * from table_name where ? or ?or ...;的写法
<foreach collection="ids" item="id" index="index" open=" where id in " separator=" , ">
#{id}
</foreach>
写法四:使用trim+foreach
<trim prefix="where id in">
<foreach collection="ids" item="id" index="index" open=" (" close=")" separator=" , ">
id = #{id}
</foreach>
</trim>
动态添加
insert+if标签实现
<insert id="insertEdocEntryUseTrimIf">
<trim prefix=" insert into edoc_entry (" suffix=")" suffixOverrides=",">
<if test="cid != null">
cid,
</if>
<if test="title != null">
title,
</if>
<if test="summary != null">
summary,
</if>
<if test="uploadUser != null">
upload_user,
</if>
create_date,
</trim>
<trim prefix=" values (" suffix=")" suffixOverrides=",">
<if test="cid != null">
#{cid},
</if>
<if test="title != null">
#{title},
</if>
<if test="summary != null">
#{summary},
</if>
<if test="uploadUser != null">
#{uploadUser},
</if>
now(),
</trim>
</insert>
批量处理
mybatis的批量处理
1.批量插入,借助mysql的特性:插入values后可以跟多个(?,?,...),(?,?,...)...
2.批量更新,不可以批量执行update,单个update执行
3.批量删除,借助in的语法,使用foreach,实现:delete from table where id in (?, ?, ?,....)
工具类
package com.kgc.mybatis.dynamicsql.util;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.InputStream;
/**
* Created on 2021-06-15.
* <p>
* Author: zhukang
* <p>
* Description: mybatis工具类
*/
public class MybatisUtil {
// mybatis核心配置参数
private static final String MYBATIS_CONFIG_RESOURCE = "mybatis-config.xml";
// mybatis的SqlSession工厂对象
private static SqlSessionFactory sqlSessionFactory;
// 静态代码块
static {
// 读取核心配置文件
try (InputStream inputStream = Resources.getResourceAsStream(MYBATIS_CONFIG_RESOURCE)) {
// 创建SqlSessionFactory工厂对象
sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* @return :
* @author : zhukang
* @date : 2021-06-15
* @description : 获取数据库操作对象SqlSession
*/
public static SqlSession getSqlSession() {
return sqlSessionFactory.openSession();
}
/**
* @return :
* @author : zhukang
* @date : 2021-06-15
* @description : 关闭SqlSession
*/
public static void closeSqlSession(SqlSession sqlSession) {
sqlSession.commit();
sqlSession.close();
}
}
Q.E.D.