MyBatis 动态 SQL 简介
1)动态 SQL 是 MyBatis 强大特性之一。极大的简化我们拼装 SQL 的操作
2)动态 SQL 元素和使用 JSTL 或其他类似基于 XML 的文本处理器相似
3)MyBatis 采用功能强大的基于 OGNL 的表达式来简化操作
if:判断
choose (when, otherwise) :分支选择(switch case)
trim (where, set):字符段截取
foreach
4)OGNL( Object Graph Navigation Language )对象图导航语言,这是一种强大的 表达式语言,通过它可以非常方便的来操作对象属性。 类似于我们的 EL,SpEL 等
访问对象属性: person.name
调用方法: person.getName()
调用静态属性/方法: @java.lang.Math@PI
@java.util.UUID@randomUUID()
调用构造方法: new com.atguigu.bean.Person(‘admin’).name
运算符: +,-,*,/,%
逻辑运算符: in,not in,>,>=,<,<=,==,!=
注意:xml 中特殊符号如”,>,<等这些都需要使用转义字符
1、if where
1)If 用于完成简单的判断.
2)Where 用于解决 SQL 语句中 where 关键字以及条件中第一个 and 或者 or 的问题
if
<!--查询员工,要求携带了哪个字段查询条件就带上这个字段的值--->
<!--public List<Employee> getEmpsByConditionIf(Employee employee)-->
<select id="getEmpsByConditionIf" resultType="dao.Employee">
select id,last_name,email,gender from tbl_employee
where
<!--test:判断表达式(OGNL)
-->
<if test="id!=null">
id = #{id}
</if>
<!-- "相当于"
and相当于&&
-->
//<if test="lastName!=null && lastName!=''">
<if test="lastName!=null && lastName!=""">
and last_name = #{lastName}
</if>
<if test="email!=null and email.trim()!=''">
and email = #{email}
</if>
<!--ognl会进行字符串与数字的转换判断,即"0"==0-->
//<if test="gender==0 or gender==1">
<if test=""".equals(gender) or "f".equals(gender)">
and gender = #{gender}
</if>
</select>
查询的时候如果某些条件没带可能sql拼装会出问题
1、给where后面加上一个1=1,以后的条件都使用and ×××格式
2、mybatis使用where标签来将所有的查询条件包括在内,mybatis会将where标签中拼装的sql,多出来的and或者or去掉**(只会去掉第一个)**
if where
<!--查询员工,要求携带了那个字段查询条件就带上这个字段的值--->
<!--public List<Employee> getEmpsByConditionIf(Employee employee)-->
<select id="getEmpsByConditionIf" resultType="dao.Employee">
select id,last_name,email,gender from tbl_employee
<where>
<!--test:判断表达式(OGNL)
-->
<if test="id!=null">
and id = #{id}
</if>
<!-- "相当于"
and相当于&&
-->
//<if test="lastName!=null && lastName!=''">
<if test="lastName!=null && lastName!=""">
and last_name = #{lastName}
</if>
<if test="email!=null and email.trim()!=''">
and email = #{email}
</if>
<!--ongl会进行字符串与数字的转换判断,即"0"==0-->
//<if test="gender==0 or gender==1">
<if test=""m".equals(gender) or "f".equals(gender)">
and gender = #{gender}
</if>
</where>
</select>
2、trim
Trim 可以在条件判断完的 SQL 语句前后 添加或者去掉指定的字符
prefix: 前缀:添加前缀
prefixOverrides: 去掉前缀
suffix: 添加后缀
suffixOverrides: 去掉后缀
<select id="getEmpsByConditionTrim" resultType="dao.Employee">
select id,last_name,email,gender from tbl_employee
<trim prefix="where" suffixOverrides="and">
<if test="id!=null">
id = #{id} and
</if>
<if test="lastName!=null && lastName!=""">
last_name = #{lastName} and
</if>
<if test="email!=null and email.trim()!=''">
email = #{email} and
</if>
<if test=""m".equals(gender) or "f".equals(gender)">
gender = #{gender}
</if>
</trim>
</select>
3、choose
choose 主要是用于分支判断,类似于 java 中的 switch case,只会满足所有分支中的一个
choose (when, otherwise) :分支选择
<!--public List<Employee> getEmpsByConditionChoose(Employee employee)-->
<select id="getEmpsByConditionChoose" resultType="com.atguigu.mybatis.beans.Employee">
select id ,last_name, email,gender from tbl_employee
<where>
<choose>
<when test="id!=null">
id = #{id}
</when>
<when test="lastName!=null">
last_name = #{lastName}
</when>
<when test="email!=null">
email = #{email}
</when>
<otherwise>
gender = 'm'
</otherwise>
</choose>
</where>
</select>
测试
@Test
public void test() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession();
try {
EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
<!--当有多个条件的时候,只会选择一个进行查询-->
Employee employee = new Employee(null,"%e%",null,null);
List<Employee> list= mapper.getEmpByConditionChoose(employee);
for(Employee emp:list){
System.out.println(emp);
}
}finally {
openSession.close();
}
}
4、set
set 主要是用于解决修改操作中 SQL 语句中可能多出逗号的问题
使用set标签,根据传入的参数进行更新操作
<!--public void updateEmp(Employee employee);-->
<update id="updateEmpByConditionSet"> update tbl_employee
<set>
<if test="lastName!=null && lastName!=""">
last_name = #{lastName},
</if>
<if test="email!=null and email.trim()!=''">
email = #{email} ,
</if>
<if test=""m".equals(gender) or "f".equals(gender)">
gender = #{gender}
</if>
</set>
where id =#{id}
</update>
使用trim标签去除多余的,
<update id="updateEmpByConditionSet">
update tbl_employee
<trim prefix="set" suffixOverrides=",">
<if test="lastName!=null && lastName!=""">
last_name = #{lastName},
</if>
<if test="email!=null and email.trim()!=''">
email = #{email} ,
</if>
<if test=""m".equals(gender) or "f".equals(gender)">
gender = #{gender}
</if>
</trim>
where id =#{id}
</update>
5、foreach
1)遍历集合
foreach 主要用户循环迭代
collection: 要迭代的集合
list类型的参数会特殊处理封装在map中,map的key就叫list
item: 当前从集合中迭代出的元素,将当前遍历的元素赋值给指定的变量
open: 开始字符
close:结束字符
separator: 元素与元素之间的分隔符
index:索引
迭代的是 List 集合: index 表示的当前元素的下标
迭代的 Map 集合: index 表示的当前元素的 key
<!--public List<Employee> getEmpsByConditionForeach(@param("ids"List<Integer> ids));-->
<select id="getEmpsByConditionForeach" resultType="dao.Employee">
select id,last_name,email,gender from tbl_employee where id in
<foreach collection="ids" item="curr_id" open="(" close=")" separator="," >
#{curr_id}
</foreach>
</select>
@Test
public void test() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession();
try {
EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
List<Employee> list= mapper.getEmpByConditionForeach(Arrays.asList(1,2,3,4));
for(Employee emp:list){
System.out.println(emp);
}
}finally {
openSession.close();
}
}
2)mysql下foreach批量插入的两种方式
①
<!--public void addEmps(@param("emps")List<Employee> emps);-->
<!--批量保存-->
<!--MySQL下批量保存,可以使用foreach遍历
这是以因为MySQL支持values(),(),()语法
-->
<insert id="addEmps">
insert into tb2_employee(last_name,email,gender,d_id)
values
<foreach collection="emps" item="emp" separator=",">
(#{emp.lastName},#{emp.email},#{emp.gender},#{emp.dept.id})
</foreach>
</insert>
②
<!--public void addEmps(@param("emps")List<Employee> emps);-->
<!--批量保存-->
<insert id="addEmps">
<!--可能会出现异常,可能是mysql连接属性问题
在连接属性上加上allowMultiQueries=true
-->
<foreach collection="emps" item="emp" separator=";">
insert into tb2_employee(last_name,email,gender,d_id)
values #{emp.lastName},#{emp.email},#{emp.gender},#{emp.dept.id}
</foreach>
</insert>
测试
@Test
public void test() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession();
try {
EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
List<Employee> emps= new ArrayList<>();
emps.add(new Employee(null,"mary","mary@163.com","1",new Department(1)));
emps.add(new Employee(null,"make","make@163.com","1",new Department(1)));
mapper.addEmp(emps);
openSession.commit();
}finally {
openSession.close();
}
}
3)Oracle下foreach批量插入的两种方式
①多个insert放在begin-end里面
begin
insert into employees(employee_id,last_name,email)
values(employees_seq.nextval,‘test_01’,‘test_01@163.com’);
insert into employees(employee_id,last_name,email)
values(employees_seq.nextval,‘test_02’,‘test_02@163.com’);
end;
<insert>
<foreach collection="emps" item="emp" open="begin" close="end;">
insert into employees(employee_id,last_name,email)
values(employees_seq.nextval,#{emp.lastName},#{emp.email});
</foreach>
</insert>
②利用中间表
insert into employees(employee_id,last_name,email)
select employees_seq.nextval,lastName,email from(
select ‘test_03’ lastName,‘test_03@163.com’ email from dual
union
select ‘test_04’ lastName,‘test_04@163.com’ email from dual
union
select ‘test_05’ lastName,‘test_05@163.com’ email from dual
)
<insert>
insert into employees(employee_id,last_name,email)
select employees_seq.nextval,lastName,email from
<foreach collection="emps" item="emp" separator="union" open="(" close=")">
select #{emp.lastName} lastName,#{emp.email} email from dual
</foreach>
</insert>
内置参数:parameter&databaseId
不只是方法传递过来的参数可以被用来判断、取值等操作
mybatis默认两个内置参数:
_parameter:代表整个参数
单个参数:_parameter就是这个参数
多个参数:参数会被封装在一个map;_parameter就是代表这个map
_darabaseId:如果配置了databaseIdProvider标签
_databaseId就是代表当前数据库的别名(oracle)
bind:绑定
bind:可以将OGNL表达式的值绑定到一个变量中,方便后来引用该变量的值
<bind name="" value=""/>
抽取可重用的sql片段:
<sql id=""></sql>
<include refid=""></include>:调用外部定义的可重用的sql字段
include还可以自定义一些property,sql标签内部就能使用自定义的属性
include-property:取值的正确方式${prop},不能使用#{}