本文详细介绍MyBatis中的动态SQL元素，包括if、where、trim等标签的使用方法及注意事项，通过具体示例展示了如何根据条件动态生成SQL语句。
动态 SQL 元素和使用 JSTL 或其他类似基于 XML 的文本处理器相似。主要由以下几种元素。
- if
- where
- trim
- choose (when, otherwise)
- set
- foreach
- bind
需要注意的是特殊字符需要转义，
<if test="hotelName!=null && hotelName!=""">
常用的有以下字符
完整项目
1.目录结构
2.代码：
Employee.java
package com.mybatis.bean;
public class Employee {
private Integer id;
private String lastName;
private String email;
private String gender;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getGender() {
return gender;
}
public void setGender(String gender) {
this.gender = gender;
}
public Employee(Integer id, String lastName, String email, String gender) {
super();
this.id = id;
this.lastName = lastName;
this.email = email;
this.gender = gender;
}
public Employee() {};
@Override
public String toString() {
return "Employee [id=" + id + ", lastName=" + lastName + ", email=" + email + ", gender=" + gender + "]";
}
}
EmployeeMapper.xml：书写动态sql
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!-- namespace:名称空间,指定接口的全类名
id: 唯一标识符
resultType:返回的对象类型
如果数据库的某些字段与对象的属性不相同，可以把数据库的这些字段取别名（如数据库字段 last_name与对象属性lastName不一致）
-->
<mapper namespace="com.mybatis.dao.EmployeeMapper">
<!--
if:判断
choose
trim 字符串截取（where（封装查询条件），set（封装修改条件））
foreach
-->
<!-- 1.测试if与where -->
<!-- 查询员工，要写，携带了哪个字段查询条件就带上这个字段的值 -->
<select id="getEmployeeByConditionIf" resultType="com.mybatis.bean.Employee">
select * from tbl_employee
<!-- where 1=1 -->
<where>
<!-- test:判断表达式(OGNL)
OGNL表达式参照官方文档。
c:if test="从参数中取值判断"
遇见特殊符号应该去写转义字符
"" :单引号''，表示为空
&& :可以表示and
-->
<if test="id !=null">
id=#{id}
</if>
<if test="lastName!=null and lastName!=""">
and last_name like #{lastName}
</if>
<if test="email !=null && email.trim()!=""">
and email=#{email}
</if>
<!-- ognl会进行字符串与数字的转换判断 "0"==0 -->
<if test="gender ==0 or gender=1">
and gender=#{gender}
</if>
</where>
</select>
<!-- 2.测试trim -->
<select id="getEmployeeByConditionTrim" resultType="com.mybatis.bean.Employee">
select * from tbl_employee
<!-- where 1=1 -->
<!-- 后面多出来的and 或者or where标签不能解决
prefix="":前缀：trim标签体重视整个字符串 拼串后的结果。
prifix给拼串后的整个字符串加一个前缀
prifixOverrides="":前缀覆盖：去掉整个字符串前面多余的字符
suffix="":后缀：给拼串后的的整个字符串加一个后缀
suffixOverrides="":后缀覆盖：去掉整个字符串后面多余的字符
-->
<trim prefix="where" prefixOverrides="" suffix="" suffixOverrides="and">
<!-- test:判断表达式(OGNL)
OGNL表达式参照官方文档。
c:if test="从参数中取值判断"
遇见特殊符号应该去写转义字符
"" :单引号''，表示为空
&& :可以表示and
-->
<if test="id !=null">
id=#{id} and
</if>
<if test="lastName!=null and lastName!=""">
last_name like #{lastName} and
</if>
<if test="email !=null && email.trim()!=""">
email=#{email} and
</if>
<!-- ognl会进行字符串与数字的转换判断 "0"==0 -->
<if test="gender ==0 or gender==1">
gender=#{gender}
</if>
</trim>
</select>
<!-- 3. 测试choose -->
<select id="getEmployeeByConditionChoose" resultType="com.mybatis.bean.Employee">
select * from tbl_employee
<where>
<!-- choose(when otherwise)分支选择；相当于带了break的swtich-case
如果带了id就用id查，如果带了lastName就用lastName查，只会进入其中一个 -->
<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="1"
</otherwise>
</choose>
</where>
</select>
<!-- 4.测试set -->
<!-- 第一种 :使用set -->
<select id="updateSqlSet" resultType="com.mybatis.bean.Employee">
<!-- update tbl_employee
<set>
<if test="lastName !=null">
last_name=#{lastName},
</if>
<if test="email !=null">
email=#{email},
</if>
<if test="gender !=null">
gender=#{gender}
</if>
</set>
where id=#{id} -->
<!-- 第二种 :使用trim-->
update tbl_employee
<trim prefix="set" prefixOverrides=",">
<if test="lastName !=null">
last_name=#{lastName},
</if>
<if test="email !=null">
email=#{email},
</if>
<if test="gender !=null">
gender=#{gender}
</if>
</trim>
where id=#{id}
</select>
<!-- 5.测试foreach -->
<select id="getEmployeeByConditionForeach" resultType="com.mybatis.bean.Employee" >
<!-- select * from tbl_employee where id in(1,2,3) -->
select * from tbl_employee where id in
<!--
collection:指定要遍历的集合；
list类型的参数会特殊处理封装在map中，map的key就叫list
item:将遍历出的元素赋值给指定的变量
open:遍历出所有结果拼接一个开始的字符
close:遍历出所有的结果拼接一个结束的字符
index:索引。遍历list的时候index就是索引，item就是当前值
遍历map的时候index表示的就是map的key,item就是map的值
#{变量名}就能取出变量的值也就是当前遍历出的元素
-->
<foreach collection="ids" item="item_id" separator="," open="(" close=")">
#{item_id}
</foreach>
</select>
<!-- 6.使用foreach进行批量保存 -->
<!-- MySQL下批量保存:可以foreach遍历 mysql支持value是(),(),()语法 -->
<!-- 第一种 -->
<insert id="addBatchSave" >
insert into tbl_employee(last_name,email,gender) values
<foreach collection="emps" item="emppp" separator=",">
(#{emppp.lastName},#{emppp.email},#{emppp.gender})
</foreach>
</insert>
<!-- 第二种（了解）:这种方式需要数据库allowMulitQueries=true,大多情况使用第一种
<property name="url" value="jdbc:mysql://localhost:3306/mybatis?allowMultiQueries=true"/>
-->
<!-- <insert id="addBatchSave" >
<foreach collection="emps" item="emppp" separator=";">
insert into tbl_employee(last_name,email,gender) values
(#{emppp.lastName},#{emppp.email},#{emppp.gender})
</foreach>
</insert> -->
<!-- 7.oracle数据库 批量插入
oracle不支持value(),(),()
oracle支持的批量方式
employee_seq.nextval:由序列生成id,百度序列怎么作为id值
第1种.多个insert放在begin - and 里面
begin
insert into employee(employee_id,employee_lastname,employee_email,employee_gender)
values(employee_seq.nextval,'zhangsan',zhang@qq.com','1')
insert into employee(employee_id,employee_lastname,employee_email,employee_gender)
values(employee_seq.nextval,'lisi','lisi@qq.com','1')
end;
</insert>
第2种.利用中间表插入(自己创建数据插入dual虚表 需要为虚表的数据起别名，以便对应插入
insert into employee(employee_id,employee_lastname,employee_email,employee_gender)
select (employee_sql.nextval,lastName,email,gender) from (
select 'zhangsan1' lastName,'zhang1@qq.com' email,'1' gender from dual
union
select 'zhangsan2' lastName,'zhang2@qq.com' email,'0' gender from dual
union
select 'zhangsan3' lastName,'zhang3@qq.com' email,'1' gender from dual
)
-->
<!-- 8.oracle下foreach 批量保存 两种方式 -->
<!-- 基于对应操作7下的两种方式来保存 -->
<!-- oracle第一种批量保存方式 -->
<!-- begin和end 也可以这样写
<foreach collection="emps" item="empp" open="begin" close="end;">
-->
<!-- <insert id="addBatchSave" databaseId="oracle">
begin
<foreach collection="emps" item="empp" >
insert into employee(employee_id,employee_lastname,employee_email)
values(employee_seq.nextval,#{empp.lastName},#{empp.email},#{empp.gender})
</foreach>
end;
</insert> -->
<!-- oracle第二种批量保存方式 -->
<insert id="addBatchSave" databaseId="oracle" >
insert into employee(employee_id,employee_lastname,employee_email,employee_gender)
select (employee_sql.nextval,lastName,email,gender) from (
<foreach collection="emps" item="empp" separator="union">
select #{lastName} lastName,#{email} email,#{gender} gender from dual
</foreach>
</insert>
<!-- 9.两个内置参数:
不只是方法传递过来的参数可以被用来判断，取值....
mybatis默认还有两个内置参数:
_parameter:代表整个参数
单个参数:_parameter就是这个参数
多个参数:参数会被封装为一个map:_parameter就是代表这个map
_parameter.lastName:表示传进来的对象的lastName值
_datebaseId:如果配置了datebaseIdProvide标签。
_datebase就是代表当前数据库的别名oracle
-->
<!-- public List<Employee> getEmpsTestInnerParameter(Employee employee); 在这个方法中传入的参数employee -->
<!-- <select id="getEmpsTestInnerParameter" resultType="com.mybatis.bean.Employee" >
<if test="_databaseId == 'mysql' ">
select * from tbl_employee
<if test="_parameter !=null">
where last_name=#{_parameter.lastName}
</if>
</if>
<if test="_databaseId == 'oracle'">
select * from employee
<if test="_parameter !=null">
where last_name=#{_parameter.lastName}
</if>
</if>
</select> -->
<!-- 10.bind:可以将OGNL表达式的值绑定到一个变量中，方便后来引用这个变量的值 -->
<!-- <bind name="" value="" /> -->
<!-- value=" '%'+lastName+'%' "表示含有传入的参数是lastName的某个字符
value=" '_'+lastName+'%' "表示含有传入的参数是lastName的第二个字符
-->
<select id="getEmpsTestInnerParameter" resultType="com.mybatis.bean.Employee" >
<bind name="_lastName111" value="'_'+lastName+'%'"/>
<if test="_databaseId == 'mysql' ">
select * from tbl_employee
<if test="_parameter !=null">
where last_name like #{_lastName111}
</if>
</if>
<if test="_databaseId == 'oracle'">
select * from employee
<if test="_parameter !=null">
where last_name like #{_lastName111}
</if>
</if>
</select>
<!-- 11.抽取可重用的sql片段，方便后面引用
1.sql抽取，经常将要查询的列名，或者插入用的列名抽取出来方便引用
2.insert来引用已经抽取的
3，include还可以自定义一些property,sql标签内部就能使用自定义的属性
include-property:取值的正确方式${prop}: id,last_name,email,gender,#{testColumn}
#{不能使用这种方式}
-->
<sql id="insertColumn">
<if test="_databaseId=='mysql'">
id,last_name,email,gender
</if>
<if test="_databaseId=='oracle'">
id,last_name,email,department_id
</if>
</sql>
<insert id="addEmployeeBysql" databaseId="mysql">
insert into tbl_employee(
<!-- 引用外部定义的sql -->
<include refid="insertColumn" ></include>
)
values (#{id},#{lastName},#{email},#{gender})
</insert>
</mapper>
接口EmployeeMapper.java
package com.mybatis.dao;
import java.util.List;
import org.apache.ibatis.annotations.Param;
import com.mybatis.bean.Employee;
public interface EmployeeMapper {
public List<Employee> getEmpsTestInnerParameter(Employee employee);
public List<Employee> getEmployeeByConditionIf(Employee employee);
public List<Employee> getEmployeeByConditionTrim(Employee employee);
public List<Employee> getEmployeeByConditionChoose(Employee employee);
public void updateSqlSet(Employee employee);
//查询员工'id'在给定集合中
public List<Employee> getEmployeeByConditionForeach(@Param("ids")List<Integer> Integer);
//使用foreach,进行批量保存
public void addBatchSave(@Param("emps")List<Employee> employee);
//11.测试sql可重复片段
public void addEmployeeBysql(Employee employee);
}
测试类testMybatis.java
package test;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.io.IOException;
import java.io.InputStream;
import java.io.Reader;
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 org.junit.Test;
import com.mybatis.bean.Employee;
import com.mybatis.dao.EmployeeMapper;
public class testMybatis{
private SqlSessionFactory getSqlSessionFactory() throws IOException {
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory SqlSessionFactory =new SqlSessionFactoryBuilder().build(inputStream);
return SqlSessionFactory;
}
@Test
public void testSQL() throws IOException {
SqlSessionFactory ssf=getSqlSessionFactory();
SqlSession ss=ssf.openSession();
try {
EmployeeMapper mapper=ss.getMapper(EmployeeMapper.class);
//1.测试if与where,我们将and写在前面
//select * from tbl_employee WHERE id=? and last_name like ? and gender=? email为空，所以没有没有作为条件
/*Employee employee =new Employee(3,"%m%",null,"1");
List<Employee> emps=mapper.getEmployeeByConditionIf(employee);
for(Employee emp:emps){
System.out.println(emp);
}*/
/*测试if时:
* 因为if里面中的符合test条件后执行的语句中有and的存在，而当我们测试的时候，设置某个值为null(我们设置id为null,那么条件语句是where and email..)，可能会导致出错，两种解决方式：
第一种：直接在where后面加上1=1;
第二种：直接去掉where，加上where标签，并且把if标签全部放在where属性中
*/
//2.测试Trim，我们将and放在结尾
//select * from tbl_employee WHERE last_name like ? and email=?
/*Employee employee =new Employee(null,"%m%","88@qq.com",null);
List<Employee> emps=mapper.getEmployeeByConditionIf(employee);
for(Employee emp:emps){
System.out.println(emp);
}*/
//3.测试choose
/*Employee employee=new Employee(null,null,null,null);
List<Employee> emps=mapper.getEmployeeByConditionChoose(employee);
for(Employee emp:emps) {
System.out.println(emp);
}*/
//4.测试set
/*Employee employee=new Employee(7,"angel","33@qq.com","0");
mapper.updateSqlSet(employee);
System.out.println(employee);*/
/*List<Employee> emps=mapper.getEmployeeByConditionForeach(Arrays.asList(2,3,5));
for(Employee emp:emps){
System.out.println(emp);
}*/
//5.测试foreach
/*List<Employee> list=mapper.getEmployeeByConditionForeach(Arrays.asList(2,3));
for(Employee emp:list) {
System.out.println(emp);
}*/
//6.测试使用foreach进行批量保存
/*List<Employee> emp1=new ArrayList();
emp1.add(new Employee(null,"wangwu1","wang@qq.com","1"));
emp1.add(new Employee(null,"zaoliu1","zao@qq.com","1"));
mapper.addBatchSave(emp1);*/
//9.测试两个内置参数datebaseId和parameter
/*List<Employee> list=mapper.getEmpsTestInnerParameter(null);
for (Employee employee:list) {
System.out.println(employee);
}*/
//10.测试bind
/*Employee emp4=new Employee();
emp4.setLastName("a");
List<Employee> list=mapper.getEmpsTestInnerParameter(emp4);
for(Employee emp:list) {
System.out.println(emp);
}*/
//11.测试sql可重复片段
Employee emp5=new Employee(null,"ke","ke@qq.com","1");
mapper.addEmployeeBysql(emp5);
ss.commit();
}
finally {
ss.close();
}
}
}
全局配置文件：mybatis-config.xml
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
<!-- 在控制台打印sql语句 -->
<settings>
<setting name="logImpl" value="STDOUT_LOGGING"/>
<setting name="mapUnderscoreToCamelCase" value="true"/>
</settings>
<environments default="dev_mysql" >
<environment id="dev_mysql">
<transactionManager type="JDBC"/>
<dataSource type="POOLED">
<property name="driver" value="com.mysql.jdbc.Driver"/>
<property name="url" value="jdbc:mysql://localhost:3306/mybatis?allowMultiQueries=true"/>
<property name="username" value="root"/>
<property name="password" value="shapolang"/>
</dataSource>
</environment>
<environment id="dev_oracle">
<transactionManager type="JDBC"></transactionManager>
<dataSource type="POOLED">
<property name="driver" value="${oracle.driver}"></property>
<property name="url" value="${oracle.url}"></property>
<property name="username" value="${oracle.username" ></property>
<property name="password" value="${oracle.password}"></property>
</dataSource>
</environment>
</environments>
<!-- MyBatis 可以根据不同的数据库厂商执行不同的语句 -->
<databaseIdProvider type="DB_VENDOR">
<property name="MySQL" value="mysql"/>
<property name="Oracle" value="oracle"/>
<property name="SQL Server" value="sqlserver"/>
</databaseIdProvider>
<!-- 写好的sql映射文件一定要注册到全局配置文件中 -->
<mappers>
<mapper resource="com/mybatis/bean/EmployeeMapper.xml"/>
</mappers>
</configuration>
测试方法11.打印结果
==> Preparing: insert into tbl_employee( id,last_name,email,gender ) values (?,?,?,?)
==> Parameters: null, ke(String), ke@qq.com(String), 1(String)
<== Updates: 1
扫一扫
576
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
