文章目录
MyBatis5_动态SQL
- 动态 SQL是MyBatis强大特性之一。极大的简化我们拼装SQL的操作。
- 动态 SQL 元素和使用 JSTL 或其他类似基于 XML 的文本处理器相似。
- MyBatis 采用功能强大的基于 OGNL 的表达式来简化操作。
1.if
<!--
if:判断。
choose (when, otherwise):分支选择,带了break的swtich-case如果带了id就用id查,
如果带了lastName就用lastName查,只会进入其中一个。
trim:字符串截取(where(封装查询条件),set(封装修改条件))。
foreach:遍历集合。
-->
<!-- 查询员工,要求携带了哪个字段查询条件就带上这个字段的值。 -->
<!-- public List<Employee> getEmpsByConditionIf(Employee employee); -->
<select id="getEmpsByConditionIf" resultType="com.ginger.mybatis.bean.Employee">
select * from employee where
<!--
test:判断表达式(OGNL)OGNL参照PPT或者官方文档,类似于Java中JSTL表达式的 c:if test,
从参数中取值进行判断,遇见特殊符号应该去写转义字符不然会报错:&&(&&)''("")。
-->
<if test="id!=null">
id=#{id}
</if>
<if test="lastName!=null && lastName!=""">
and last_name like #{lastName}
</if>
<if test="email!=null and email.trim()!=""">
and email=#{email}
</if>
<!-- gender 类型是字符串为什么不出错呢?ognl会进行字符串与数字的转换判断 "0"==0 -->
<if test="gender==0 or gender==1">
and gender=#{gender}
</if>
</select>
//接口
public List<Employee> getEmpsByConditionIf(Employee employee);
//测试
@Test
public void testDynamicSql() throws IOException{
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession();
try{
EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
//select * from tbl_employee where id=? and last_name like ?
Employee employee = new Employee(1, "小明", null, null);
List<Employee> emps = mapper.getEmpsByConditionIf(employee );
for (Employee emp : emps) {
System.out.println(emp);
}
}finally{
openSession.close();
}
}
2.choose (when, otherwise)
<!-- public List<Employee> getEmpsByConditionChoose(Employee employee); -->
<select id="getEmpsByConditionChoose" resultType="com.ginger.mybatis.bean.Employee">
select * from employee where
<!-- 如果带了id就用id查,如果带了lastName就用lastName查,只会进入其中一个 -->
<choose>
<when test="id!=null">
id=#{id}
</when>
<when test="lastName!=null">
last_name like #{lastName}
</when>
<when test="email!=null">
email = #{email}
</when>
<otherwise>
gender = 0
</otherwise>
</choose>
</select>
//接口
public List<Employee> getEmpsByConditionChoose(Employee employee);
//测试
@Test
public void testDynamicSql01() throws IOException{
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession();
try{
EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
Employee employee = new Employee(2, "小王", null, null);
List<Employee> emps = mapper.getEmpsByConditionChoose(employee);
for (Employee emp : emps) {
System.out.println(emp);
}
}finally{
openSession.close();
}
}
3.trim (where, set)
1.where
演示一个sql拼接的问题。
<!-- public List<Employee> getEmpsByConditionIf(Employee employee); -->
<select id="getEmpsByConditionIf" resultType="com.ginger.mybatis.bean.Employee">
select * from employee
where
<if test="id!=null">
id=#{id} <!-- 这里如果不传递参数 sql 语法会出错。 -->
</if>
<if test="lastName!=null && lastName!=""">
and last_name like #{lastName}
</if>
<if test="email!=null and email.trim()!=""">
and email=#{email}
</if>
<!-- gender 类型是字符串为什么不出错呢?ognl会进行字符串与数字的转换判断 "0"==0 -->
<if test="gender==0 or gender==1">
and gender=#{gender}
</if>
</select>
//接口
public List<Employee> getEmpsByConditionIf(Employee employee);
//测试
@Test
public void testDynamicSql() throws IOException{
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession();
try{
EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
Employee employee = new Employee(null, "小明", null, null);
List<Employee> emps = mapper.getEmpsByConditionIf(employee );
for (Employee emp : emps) {
System.out.println(emp);
}
}finally{
openSession.close();
}
}
测试结果
上面的语法报错一般是有两种办法解决。
查询的时候如果某些条件没带可能sql拼装会有问题
- 给 where 后面加上1=1,以后的条件都and xxx。
- mybatis 使用 where 标签来将所有的查询条件包括在内,mybatis就会将where标签中拼装的sql,多出来的 and 或者 or 去掉。
注意:where只会去掉第一个多出来的and或者or,如果and或者or出现在id = #{id} 后面where就不起作用了。
方式1
<!-- public List<Employee> getEmpsByConditionIf(Employee employee); -->
<select id="getEmpsByConditionIf" resultType="com.ginger.mybatis.bean.Employee">
select * from employee
where 1=1 <!-- 加上 1=1-->
<if test="id!=null">
and id=#{id} <!-- 在这里加上 and -->
</if>
<if test="lastName!=null && lastName!=""">
and last_name like #{lastName}
</if>
<if test="email!=null and email.trim()!=""">
and email=#{email}
</if>
<!-- gender 类型是字符串为什么不出错呢?ognl会进行字符串与数字的转换判断 "0"==0 -->
<if test="gender==0 or gender==1">
and gender=#{gender}
</if>
</select>
方式2
<!-- public List<Employee> getEmpsByConditionIf(Employee employee); -->
<select id="getEmpsByConditionIf" resultType="com.ginger.mybatis.bean.Employee">
select * from employee
<!-- where -->
<where> <!-- 使用 Mybatis 自带的 where 标签 -->
<if test="id!=null">
id=#{id}
</if>
<if test="lastName!=null && lastName!=""">
and last_name like #{lastName}
</if>
<if test="email!=null and email.trim()!=""">
and email=#{email}
</if>
<!-- gender 类型是字符串为什么不出错呢?ognl会进行字符串与数字的转换判断 "0"==0 -->
<if test="gender==0 or gender==1">
and gender=#{gender}
</if>
</where>
</select>
//接口
public List<Employee> getEmpsByConditionIf(Employee employee);
//测试
@Test
public void testDynamicSql() throws IOException{
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession();
try{
EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
Employee employee = new Employee(null, "小明", null, null);
List<Employee> emps = mapper.getEmpsByConditionIf(employee );
for (Employee emp : emps) {
System.out.println(emp);
}
}finally{
openSession.close();
}
}
测试结果
2.set
演示一个更新语句可能会出现的问题。
<!--public void updateEmp(Employee employee); -->
<update id="updateEmp">
update employee
set
<if test="lastName!=null">
last_name=#{lastName},
</if>
<if test="email!=null">
email=#{email},
</if>
<if test="gender!=null">
gender=#{gender}
</if>
where id=#{id}
</update>
//接口
public void updateEmp(Employee employee);
//测试
@Test
public void testDynamicSql03() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession();
try {
EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
Employee employee = new Employee(1, "小小王", null, null);
mapper.updateEmp(employee);
openSession.commit();
} finally {
openSession.close();
}
}
测试结果
上面的语法报错可以使用set标签和trim标签来解决。
set标签方式
<!--public void updateEmp(Employee employee); -->
<update id="updateEmp">
update employee
<!--set-->
<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}
</update>
//接口
public void updateEmp(Employee employee);
//测试
@Test
public void testDynamicSql03() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession();
try {
EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
Employee employee = new Employee(1, "小小王", null, null);
mapper.updateEmp(employee);
openSession.commit();
} finally {
openSession.close();
}
}
测试结果
trim标签方式
<!--public void updateEmp(Employee employee); -->
<update id="updateEmp">
update employee
<trim prefix="set" suffixOverrides=",">
<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}
</update>
//接口
public void updateEmp(Employee employee);
//测试
@Test
public void testDynamicSql03() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession();
try {
EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
Employee employee = new Employee(1, "小小王王", null, null);
mapper.updateEmp(employee);
openSession.commit();
} finally {
openSession.close();
}
}
3.trim
<!--public List<Employee> getEmpsByConditionTrim(Employee employee); -->
<select id="getEmpsByConditionTrim" resultType="com.ginger.mybatis.bean.Employee">
select * from employee
<!--
后面多出的 and 或者 or where 标签不能解决,而使用 trim 就可以解决。
trim标签体中是整个字符串拼串后的结果。
prefix 前缀:prefix给拼串后的整个字符串加一个前缀。
prefixOverrides 前缀覆盖:去掉整个字符串前面多余的字符串。
suffix 后缀:suffix给拼串后的整个字符串加一个后缀。
suffixOverrides 后缀覆盖:去掉整个字符串后面多余的字符串。
-->
<!-- 自定义字符串的截取规则 -->
<trim prefix="where" suffixOverrides="and">
<if test="id!=null">
id=#{id} and
</if>
<if test="lastName!=null && lastName!=""">
last_name like #{lastName} and
</if>
<if test="email!=null and email.trim()!=""">
email=#{email} and
</if>
<!-- gender 类型是字符串为什么不出错呢?ognl会进行字符串与数字的转换判断 "0"==0 -->
<if test="gender==0 or gender==1">
gender=#{gender}
</if>
</trim>
</select>
//接口
public List<Employee> getEmpsByConditionTrim(Employee employee);
//测试
@Test
public void testDynamicSql02() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession();
try {
EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
Employee employee = new Employee(null, "小王", null, null);
List<Employee> emps2 = mapper.getEmpsByConditionTrim(employee);
for (Employee emp : emps2) {
System.out.println(emp);
}
} finally {
openSession.close();
}
}
4.foreach
- 动态 SQL 的另外一个常用的必要操作是需要对一个集合进行遍历,通常是在构建 IN 条件语句的时候。
- 当迭代列表、集合等可迭代对象或者数组时。
- index是当前迭代的次数,item的值是本次迭代获取的元素。
- 当使用字典(或者Map.Entry对象的集合)时。
- index是键,item是值。
1.遍历集合
<!--public List<Employee> getEmpsByConditionForeach(List<Integer> ids); -->
<select id="getEmpsByConditionForeach" resultType="com.ginger.mybatis.bean.Employee">
select * from employee
<!--
collection指定要遍历的集合:
list类型的参数会特殊处理封装在map中,map的key就叫list。
item:将当前遍历出的元素赋值给指定的变量。
separator:每个元素之间的分隔符。
open:遍历出所有结果拼接一个开始的字符。
close:遍历出所有结果拼接一个结束的字符。
index:索引。遍历list的时候是index就是索引,item就是当前值。
遍历map的时候index表示的就是map的key,item就是map的值。
#{变量名}就能取出变量的值也就是当前遍历出的元素。
-->
<foreach collection="ids" item="item_id" separator="," open="where id in(" close=")">
<!--如果不使用@Param注解来指定map的键值,List 参数默认的键值就是list。-->
<!--<foreach collection="list" item="item_id" separator="," open="where id in(" close=")">-->
#{item_id}
</foreach>
</select>
//接口
public List<Employee> getEmpsByConditionForeach(@Param("ids")List<Integer> ids);
//或者不使用@Param注解来指定map的键值。
//public List<Employee> getEmpsByConditionForeach(List<Integer> ids);
//测试
@Test
public void testDynamicSql04() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession();
try {
EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
List<Employee> employees = mapper.getEmpsByConditionForeach(Arrays.asList(1, 2, 3, 4, 5));
employees.forEach(employee -> System.out.println(employee));
} finally {
openSession.close();
}
}
测试结果
2.mysql下批量插入两种方式
方式1
<!-- 批量保存 -->
<!--public void addEmps(@Param("emps")List<Employee> emps); -->
<!--MySQL下批量保存:可以foreach遍历 mysql支持values(),(),()语法-->
<insert id="addEmps">
insert into employee(id,last_name,email,gender,depa_id)
values
<foreach collection="emps" item="emp" separator=",">
<!--如果不使用@Param注解来指定map的键值,List 参数默认的键值就是list。-->
<!--<foreach collection="list" item="emp" separator=",">-->
(#{emp.id},#{emp.lastName},#{emp.email},#{emp.gender},#{emp.dept.id})
</foreach>
</insert>
//接口
public void addEmps(@Param("emps")List<Employee> emps);
//或者不使用@Param注解来指定map的键值。
//public void addEmps(List<Employee> emps);
//测试
@Test
public void testDynamicSql05() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession();
try {
EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
Employee employee1 = new Employee(6, "小姜", "xiaojiang@qq.com", "1", new Department(1));
Employee employee2 = new Employee(7, "小唐", "xiaotang@qq.com", "1", new Department(1));
Employee employee3 = new Employee(8, "小张", "xiaozhang@qq.com", "1", new Department(1));
mapper.addEmps(Arrays.asList(employee1, employee2, employee3));
openSession.commit();
} finally {
openSession.close();
}
}
测试结果
方式2
<!--
这种方式需要数据库连接属性 allowMultiQueries=true ,这种分号分隔多个 sql 可以用于其他的批量操作(删除,修改)。
-->
<insert id="addEmps">
<foreach collection="emps" item="emp" separator=";">
<!--如果不使用@Param注解来指定map的键值,List 参数默认的键值就是list。-->
<!--<foreach collection="list" item="emp" separator=",">-->
insert into employee(id,last_name,email,gender,depa_id)
values(#{emp.id},#{emp.lastName},#{emp.email},#{emp.gender},#{emp.dept.id})
</foreach>
</insert>
//接口
public void addEmps(@Param("emps")List<Employee> emps);
//或者不使用@Param注解来指定map的键值。
//public void addEmps(List<Employee> emps);
//测试
@Test
public void testDynamicSql05() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession();
try {
EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
Employee employee1 = new Employee(9, "小赵", "xiaozhao@qq.com", "1", new Department(1));
Employee employee2 = new Employee(10, "小陶", "xiaotao@qq.com", "1", new Department(1));
mapper.addEmps(Arrays.asList(employee1, employee2));
openSession.commit();
} finally {
openSession.close();
}
}
测试结果
3.oracle下批量插入两种方式
方式1
<!--
Oracle数据库批量保存:
Oracle不支持values(),(),();
Oracle支持的批量方式
1、多个insert放在begin - end里面
begin
insert into employees(employee_id,last_name,email)
values(employees_seq.nextval,'test_001','test_001@ginger.com');
insert into employees(employee_id,last_name,email)
values(employees_seq.nextval,'test_002','test_002@ginger.com');
end;
-->
<insert id="addEmps" databaseId="oracle">
<!-- oracle第一种批量方式 -->
<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>
这里我就不测试了,是可以通过的。
方式2
<!--
Oracle数据库批量保存:
Oracle不支持values(),(),();
Oracle支持的批量方式
2、利用中间表:
insert into employees(employee_id,last_name,email)
select employees_seq.nextval,lastName,email from(
select 'test_a_01' lastName,'test_a_e01' email from dual
union
select 'test_a_02' lastName,'test_a_e02' email from dual
union
select 'test_a_03' lastName,'test_a_e03' email from dual
)
-->
<!-- oracle第二种批量方式 -->
<insert id="addEmps" databaseId="oracle">
insert into employees(employee_id,last_name,email)
<foreach collection="emps" item="emp" separator="union"
open="select employees_seq.nextval,lastName,email from(" close=")">
select #{emp.lastName} lastName,#{emp.email} email from dual
</foreach>
</insert>
这里我就不测试了,是可以通过的。
5.内置参数_parameter和databaseId
Mapper主配置文件
<!--
4、environments:环境们,mybatis可以配置多种环境 ,default指定使用某种环境。可以达到快速切换环境。
environment:配置一个具体的环境信息;必须有两个标签;id代表当前环境的唯一标识。
transactionManager:事务管理器;
type:事务管理器的类型;JDBC(JdbcTransactionFactory)|MANAGED(ManagedTransactionFactory)
自定义事务管理器:实现TransactionFactory接口.type指定为全类名。
dataSource:数据源;
type:数据源类型;UNPOOLED(UnpooledDataSourceFactory)
|POOLED(PooledDataSourceFactory)
|JNDI(JndiDataSourceFactory)
自定义数据源:实现DataSourceFactory接口,type是全类名。
-->
<environments default="dev_mysql">
<environment id="dev_mysql">
<transactionManager type="JDBC"></transactionManager>
<dataSource type="POOLED">
<property name="driver" value="${jdbc.driver}" />
<property name="url" value="${jdbc.url}" />
<property name="username" value="${jdbc.username}" />
<property name="password" value="${jdbc.password}" />
</dataSource>
</environment>
<environment id="dev_oracle">
<transactionManager type="JDBC" />
<dataSource type="POOLED">
<property name="driver" value="${orcl.driver}" />
<property name="url" value="${orcl.url}" />
<property name="username" value="${orcl.username}" />
<property name="password" value="${orcl.password}" />
</dataSource>
</environment>
</environments>
<!--
5、databaseIdProvider:支持多数据库厂商的;
type="DB_VENDOR":VendorDatabaseIdProvider。
作用就是得到数据库厂商的标识(驱动getDatabaseProductName()),mybatis就能根据数据库厂商标识来执行不同的sql;
MySQL,Oracle,SQL Server,xxxx。
-->
<databaseIdProvider type="DB_VENDOR">
<!-- 为不同的数据库厂商起别名 -->
<property name="MySQL" value="mysql"/>
<property name="Oracle" value="oracle"/>
<property name="SQL Server" value="sqlserver"/>
</databaseIdProvider>
Mapper映射文件
<!--
两个内置参数:
不只是方法传递过来的参数可以被用来判断,取值。
mybatis默认还有两个内置参数:
_parameter:代表整个参数
单个参数:_parameter就是这个参数
多个参数:参数会被封装为一个map;_parameter就是代表这个map
_databaseId:如果配置了databaseIdProvider标签。
_databaseId就是代表当前数据库的别名oracle
-->
<!--public List<Employee> getEmpsTestInnerParameter(Employee employee); -->
<select id="getEmpsTestInnerParameter" resultType="com.ginger.mybatis.bean.Employee">
<!-- mysql环境 -->
<if test="_databaseId=='mysql'">
select * from employee
<if test="_parameter!=null"> <!-- _parameter在这里就代表employee这个对象。 -->
where last_name like #{lastName}
<!--where last_name like #{_parameter.lastName} 也可以这样取出参数 -->
</if>
</if>
<!-- oracle环境 -->
<if test="_databaseId=='oracle'">
select * from tbl_employee
<if test="_parameter!=null"> <!-- _parameter在这里就代表employee这个对象。 -->
where last_name like #{_parameter.lastName}
</if>
</if>
</select>
//接口
public List<Employee> getEmpsTestInnerParameter(Employee employee);
//测试 这里我只测试Mysql环境
@Test
public void testDynamicSql06() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession();
try {
EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
List<Employee> employees = mapper.getEmpsTestInnerParameter(new Employee(null,"%小%",null,null));
employees.forEach(emp -> System.out.println(emp));
} finally {
openSession.close();
}
}
测试结果
6.bind
bind 元素可以从 OGNL 表达式中创建一个变量并将其绑定到上下文。
提个需求上面使用模糊查询 last_name 使用mapper.getEmpsTestInnerParameter(new Employee(null,"%小%",null,null));
但是有的时候不想再传参的时候使用%号那怎么办呢?
在#{}前后面拼接上%是否可行?肯定会报错,#{}表示一个占位符。
<!--public List<Employee> getEmpsTestInnerParameter(Employee employee); -->
<select id="getEmpsTestInnerParameter" resultType="com.ginger.mybatis.bean.Employee">
<!-- mysql环境 -->
<if test="_databaseId=='mysql'">
select * from employee
<if test="_parameter!=null"> <!-- _parameter在这里就代表employee这个对象。 -->
where last_name like '%#{lastName}%'
</if>
</if>
</select>
可以使用${}在前后面拼接上%是可以的,但是这样做不安全。不推荐
<!-- public List<Employee> getEmpsTestInnerParameter(Employee employee); -->
<select id="getEmpsTestInnerParameter" resultType="com.ginger.mybatis.bean.Employee">
<!-- mysql环境 -->
<if test="_databaseId=='mysql'">
select * from employee
<if test="_parameter!=null"> <!-- _parameter在这里就代表employee这个对象。 -->
where last_name like '%${lastName}%'
</if>
</if>
</select>
@Test
public void testDynamicSql06() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession();
try {
EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
List<Employee> employees = mapper.getEmpsTestInnerParameter(new Employee(null,"小",null,null));
employees.forEach(emp -> System.out.println(emp));
} finally {
openSession.close();
}
}
测试结果
如果我就要使用#{},那怎么办呢? 可以使用bind标签。
<!-- public List<Employee> getEmpsTestInnerParameter(Employee employee); -->
<select id="getEmpsTestInnerParameter" resultType="com.ginger.mybatis.bean.Employee">
<!-- mysql环境 -->
<if test="_databaseId=='mysql'">
<!-- bind:可以将OGNL表达式的值绑定到一个变量中,方便后来引用这个变量的值 -->
<bind name="_lastName" value="'%'+lastName+'%'"/>
<if test="_databaseId=='mysql'">
select * from employee
<if test="_parameter!=null">
where last_name like #{_lastName}
</if>
</if>
</if>
</select>
//接口
public List<Employee> getEmpsTestInnerParameter(Employee employee);
//测试
@Test
public void testDynamicSql06() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession();
try {
EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
List<Employee> employees = mapper.getEmpsTestInnerParameter(new Employee(null,"小",null,null));
employees.forEach(emp -> System.out.println(emp));
} finally {
openSession.close();
}
}
测试结果
7.sql
<!--
抽取可重用的sql片段,方便后面引用。
1、sql抽取:经常将要查询的列名,或者插入用的列名抽取出来方便引用。
2、include来引用已经抽取的sql。
3、include还可以自定义一些property,sql标签内部就能使用自定义的属性。
include-property:取值的正确方式${prop},#{不能使用这种方式否则出错}
-->
<sql id="insertColumn">
<if test="_databaseId=='mysql'">
id,last_name,email,gender,depa_id
</if>
<if test="_databaseId=='oracle'">
employee_id,last_name,email
</if>
</sql>
<!-- 批量保存 -->
<!--public void addEmps(@Param("emps")List<Employee> emps); -->
<!--MySQL下批量保存:可以foreach遍历 mysql支持values(),(),()语法-->
<insert id="addEmps">
<!-- insert into employee(id,last_name,email,gender,depa_id) -->
insert into employee(
<include refid="insertColumn"></include>
)
values
<foreach collection="emps" item="emp" separator=",">
<!--如果不使用@Param注解来指定map的键值,List 参数默认的键值就是list。-->
<!--<foreach collection="list" item="emp" separator=",">-->
(#{emp.id},#{emp.lastName},#{emp.email},#{emp.gender},#{emp.dept.id})
</foreach>
</insert>
<!--
Oracle数据库批量保存:
Oracle不支持values(),(),();
Oracle支持的批量方式
1、多个insert放在begin - end里面
begin
insert into employees(employee_id,last_name,email)
values(employees_seq.nextval,'test_001','test_001@ginger.com');
insert into employees(employee_id,last_name,email)
values(employees_seq.nextval,'test_002','test_002@ginger.com');
end;
-->
<insert id="addEmps" databaseId="oracle">
<!-- oracle第一种批量方式 -->
<foreach collection="emps" item="emp" open="begin" close="end;">
<!--insert into employees(employee_id,last_name,email)-->
insert into employees(
<include refid="insertColumn"></include>
)
values(employees_seq.nextval,#{emp.lastName},#{emp.email});
</foreach>
</insert>
//接口
public void addEmps(@Param("emps")List<Employee> emps);
//或者不使用@Param注解来指定map的键值。
//public void addEmps(List<Employee> emps);
//测试 这里我只测试Mysql环境
@Test
public void testDynamicSql05() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession();
try {
EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
Employee employee1 = new Employee(11, "小钱", "xiaoqian@qq.com", "1", new Department(1));
Employee employee2 = new Employee(12, "小林", "xiaolin@qq.com", "1", new Department(1));
mapper.addEmps(Arrays.asList(employee1, employee2));
openSession.commit();
} finally {
openSession.close();
}
}
测试结果
在 include 标签中添加自定义属性在 sql 标签中获取。
<sql id="insertColumn">
<if test="_databaseId=='mysql'">
id,last_name,email,gender,depa_id,${testColumn}
</if>
<if test="_databaseId=='oracle'">
employee_id,last_name,email
</if>
</sql>
<!-- 批量保存 -->
<!--public void addEmps(@Param("emps")List<Employee> emps); -->
<!--MySQL下批量保存:可以foreach遍历 mysql支持values(),(),()语法-->
<insert id="addEmps">
<!-- insert into employee(id,last_name,email,gender,depa_id) -->
insert into employee(
<include refid="insertColumn">
<property name="testColumn" value="test"></property>
</include>
)
values
<foreach collection="emps" item="emp" separator=",">
<!--如果不使用@Param注解来指定map的键值,List 参数默认的键值就是list。-->
<!--<foreach collection="list" item="emp" separator=",">-->
(#{emp.id},#{emp.lastName},#{emp.email},#{emp.gender},#{emp.dept.id})
</foreach>
</insert>
//接口
public void addEmps(@Param("emps")List<Employee> emps);
//或者不使用@Param注解来指定map的键值。
//public void addEmps(List<Employee> emps);
//测试 肯定会出错,因为没有这个列,我就是想演示一下在 include 自定义属性在 sql 标签中获取。
@Test
public void testDynamicSql07() throws IOException {
SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
SqlSession openSession = sqlSessionFactory.openSession();
try {
EmployeeMapperDynamicSQL mapper = openSession.getMapper(EmployeeMapperDynamicSQL.class);
Employee employee1 = new Employee(13, "小郭", "xiaoguo@qq.com", "0", new Department(2));
mapper.addEmps(Arrays.asList(employee1));
openSession.commit();
} finally {
openSession.close();
}
}
测试结果