环境搭建
- 目录结构
- MyBatis配置文件
<?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>
<properties resource="properties/datasource.properties"/>
<settings>
<!--开启懒加载-->
<setting name="lazyLoadingEnabled" value="true"/>
<setting name="aggressiveLazyLoading" value="false"/>
<setting name="logImpl" value="STDOUT_LOGGING"/>
</settings>
<environments default="mysql">
<!--定义开发时的环境配置
链接mysql数据库
-->
<environment id="mysql">
<transactionManager type="JDBC"/>
<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="oracle">
<transactionManager type="JDBC"/>
<dataSource type="UNPOOLED">
<property name="driver" value="oracle.jdbc.driver.OracleDriver"/>
<property name="url" value="jdbc:oracle:thin:@localhost:1521:mldn "/>
<property name="username" value="scott"/>
<property name="password" value="tiger"/>
</dataSource>
</environment>
</environments>
<databaseIdProvider type="DB_VENDOR">
<property name="MySql" value="mysql"/>
<property name="Oracle" value="oracle"/>
</databaseIdProvider>
<!--注册sql-->
<mappers>
<mapper resource="config/MyBatisDynamicSQLDAO.xml"/>
</mappers>
</configuration>
ONGL表达式
if判断
- 示例:使用If动态拼装sql语句
- 如果遇到要使用双引号或者大于号小于号这样的特殊符号可以使用转义字符
- OBGL支持逻辑运算表达式"与&",“或|” 也可以使用"and"代替与,使用"or"代替"或"
- 设置接口方法
public List<Employee> getEmps(Employee employee);
- 配置sql映射文件
<select id="getEmps" resultType="mao.shu.vo.Employee">
SELECT id, ename, age, job, dept_id
FROM employee
WHERE
<!-- 使用if进行判断
test为判断的表达式
id由外部方法传入的参数
-->
<if test="id!=null">
id = #{id}
</if>
<!--
and表示"&&" 逻辑判断符
"为转义字符,表示双引号"",也可以使用''单引号表示
-->
<if test="ename!=null and "".equals(ename)">
AND ename = #{ename} AND
</if>
<!-- or表示 "||"逻辑运算符-->
<if test="age==1 or age==2">
age = #{age} AND
</if>
</select>
- 测试代码
package mao.shu.dao;
import mao.shu.vo.Employee;
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.After;
import org.junit.Before;
import org.junit.Test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import static org.junit.Assert.*;
public class MyBatisDynamicSQLDAOTest {
private SqlSession sqlSession;
@Before
public void before() throws IOException {
String resource = "config/mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
this.sqlSession = sqlSessionFactory.openSession();
}
@After
public void after(){
this.sqlSession.commit();
this.sqlSession.close();
}
@Test
public void getEmps(){
MyBatisDynamicSQLDAO dynamicSQLDAO = this.sqlSession.getMapper(MyBatisDynamicSQLDAO.class);
Employee employee = new Employee();
employee.setId(1);
List<Employee> emps = dynamicSQLDAO.getEmps(employee);
System.out.println(emps);
}
}
- 测试结果
where查询条件
- 查询的时候如果第一个条件不存在,则可能会出现将后面 “and xxx” 拼接到sql语句中,造成sql语法错误.
-
使用<where>标签代替sql查询条件的"WHERE"
-
修改sql映射文件
<select id="getEmps" resultType="mao.shu.vo.Employee">
SELECT id, ename, age, job, dept_id
FROM employee
<where>
<if test="id!=null">
id = #{id}
</if>
<if test="ename!=null and !"".equals(ename)">
AND ename = #{ename}
</if>
<if test="age>=0 ">
AND age = #{age}
</if>
</where>
</select>
- <where>标签会根据条件自动消除掉sql语句中前面部分的"AND" 字符
trim自定义字符串截取
- <trim>标签可以使用自定义sql语句的拼凑和裁剪部分
- 示例:使用<trim>标签拼凑"WHERE"部分
<select id="testTrim" resultType="mao.shu.vo.Employee">
SELECT id, ename, age, job, dept_id
FROM employee
<!--
<trim>标签拼凑字符串
prefix:添加前缀
prefixOverrides:控制用字符串覆盖前缀
suffix:定制后缀字符串
suffixOverrides="AND":覆盖最后 AND 的字符串
-->
<trim prefix="WHERE" suffixOverrides="AND">
<if test="id!=null">
id = #{id} AND
</if>
<if test="ename!=null and !"".equals(ename)">
ename = #{ename} AND
</if>
<if test="age>=0">
age = #{age} AND
</if>
</trim>
</select>
- 测试代码
@Test
public void testTrim(){
MyBatisDynamicSQLDAO dynamicSQLDAO = this.sqlSession.getMapper(MyBatisDynamicSQLDAO.class);
Employee employee = new Employee();
employee.setAge(22);
List<Employee> emps = dynamicSQLDAO.testTrim(employee);
System.out.println(emps);
}
choose分支选择
- 分支选择类似于java中的swtitch-case,但是<choosse>分支只会选择一个.
- 示例:
<select id="testChoose" resultType="mao.shu.vo.Employee">
SELECT id, ename, age, job, dept_id
FROM employee
<where>
<choose>
<when test="id!=null">
id=#{id}
</when>
<when test="ename!=null and ''.equals(ename)">
ename LIKE #{ename}
</when>
<when test="age!=null and age>0">
age = #{age}
</when>
</choose>
</where>
</select>
- 测试方法
@Test
public void testChoose(){
MyBatisDynamicSQLDAO dynamicSQLDAO = this.sqlSession.getMapper(MyBatisDynamicSQLDAO.class);
Employee employee = new Employee();
employee.setId(1);
employee.setAge(22);
employee.setEname("xiemaoshu");
List<Employee> emps = dynamicSQLDAO.testChoose(employee);
System.out.println(emps);
}
- <choose>标签只会选择其中一个
set与if结合动态更新
- 使用<set>标签描述更新sql语句的设置部分,可以自动的取出多余的","号
- 示例:
- 定义接口更新方法
public void updateEmp(Employee employee);
- 定义sql映射文件
<select id="updateEmp" resultType="mao.shu.vo.Employee">
UPDATE employee
<set>
<if test="ename!=null and !''.equals(ename)">
ename=#{ename},
</if>
<if test="age!=null and age>0">
age=#{age},
</if>
<if test="job!=null and !''.equals(job)">
job=#{job},
</if>
</set>
<where>
id=#{id}
</where>
</select>
- 测试方法
@Test
public void updateEmp() {
MyBatisDynamicSQLDAO dynamicSQLDAO = this.sqlSession.getMapper(MyBatisDynamicSQLDAO.class);
Employee employee = new Employee();
employee.setId(1);
employee.setAge(22);
employee.setEname("xiemaoshu");
employee.setJob("流浪地球");
dynamicSQLDAO.updateEmp(employee);
}
- 被修改的数据
遍历集合
-
当接口方法定义了集合类型的参数,则可以使用<foreach>标签遍历集合,
-
默认情况下集合参数会被MyBatis封装到Map集合中,使用"list"或者 “collection”,可以获取到集合参数
-
当然也可以在接口方法参数上设置@Param 注解定义保存集合的key
-
示例:
-
接口定义的方法
public List<Employee> getEmpsForeach(@Param("ids") List<Integer> ids);
- sql映射文件
<select id="getEmpsForeach" resultType="mao.shu.vo.Employee">
SELECT id, ename, age, job, dept_id
FROM employee
<!--
遍历集合
collection:要便利的集合,ids是调用方法的参数
open:开始字符
close:结束字符
separator:分隔符,以逗号为","
-->
<foreach collection="ids" item="id" open=" WHERE id IN (" close=")" separator=",">
#{id}
</foreach>
</select>
- 测试方法
@Test
public void getEmpsForeach() {
MyBatisDynamicSQLDAO dynamicSQLDAO = this.sqlSession.getMapper(MyBatisDynamicSQLDAO.class);
List<Integer> ids = new ArrayList<>();
ids.add(1);
ids.add(4);
ids.add(3);
List<Employee> emps = dynamicSQLDAO.getEmpsForeach(ids);
for(Employee emp : emps){
System.out.println(emp);
}
}
MySQL下foreach两种插入方式
- 示例:定义批量添加方法
public void addBathEmp(@Param("emps") List<Employee> emps);
- 第一种方式以MySQL "values(),(),()"的方式
<insert id="addBathEmp" useGeneratedKeys="true">
INSERT INTO employee(ename, age, job, dept_id)
VALUES
<foreach collection="emps" item="emp" separator=",">
(#{emp.ename},#{emp.age},#{emp.job},#{emp.deptID})
</foreach>
</insert>
- 测试方法
@Test
public void addBathEmp() {
MyBatisDynamicSQLDAO dynamicSQLDAO = this.sqlSession.getMapper(MyBatisDynamicSQLDAO.class);
List<Employee> emps = new ArrayList<>();
for (int i = 0; i < 10; i++) {
Employee temp = new Employee();
temp.setJob("批量添加测试"+i);
temp.setEname("批量添加测试"+i);
temp.setAge(i+20);
Department tempDept = new Department();
tempDept.setDeptID(i%2==0?2:1);
temp.setDepartment(tempDept);
emps.add(temp);
}
dynamicSQLDAO.addBathEmp(emps);
}
- 添加的数据
- 第二种方式:每次遍历都拼凑一个完整的sql插入语句,使用";"分号分隔每条sql语句
- MySQL需要在链接时开启"allowMultiQueries=true",的属性,才允许一次执行多条sql语句
- 修改数据库连接的url
jdbc.url=jdbc:mysql://localhost:3306/mybatisdb?allowMultiQueries=true
- 修改sql映射文件
<insert id="addBathEmp" useGeneratedKeys="true">
<foreach collection="emps" item="emp" separator=";">
INSERT INTO employee(ename, age, job, dept_id)
VALUES
(#{emp.ename},#{emp.age},#{emp.job},#{emp.department.deptID})
</foreach>
</insert>
- 推荐使用第一种方式
Oracle使用foreach批量增加的两种方式
- 第一种方式使用"begin…end"完成批量操作
<!--
oracle数据库可以在"BEGIN"和"END"之间编写多条sql语句
-->
<insert id="addBathEmp" databaseId="oracle">
BEGIN
<foreach collection="emps" item="emp" >
INSERT INTO emp(empno, ename, job,deptno)
VALUES
(EMPLOYEE_SEQ.nextval,#{emp.ename},#{emp.job},#{emp.department.deptID});
</foreach>
END;
</insert>
- 测试方法
@Test
public void addBathEmp() {
MyBatisDynamicSQLDAO dynamicSQLDAO = this.sqlSession.getMapper(MyBatisDynamicSQLDAO.class);
List<Employee> emps = new ArrayList<>();
for (int i = 0; i < 10; i++) {
Employee temp = new Employee();
temp.setJob("test"+i);
temp.setEname("test"+i);
temp.setAge(i+20);
Department tempDept = new Department();
tempDept.setDeptID(i%2==0?20:10);
temp.setDepartment(tempDept);
emps.add(temp);
}
dynamicSQLDAO.addBathEmp(emps);
}
- 添加的数据
2. 使用中间表的方式,进行批量操作
- oracle中有一个 dual 表,这个表是一个中间表,可以用于显示数据使用
<!--oracle批量操作,使用中间表的方式-->
<insert id="addBathEmp" databaseId="oracle">
insert into emp(EMPNO, ename, job)
select EMPLOYEE_SEQ.nextval, ename, job
from (
<foreach collection="emps" item="emp" separator=" union">
select #{emp.ename} ename, #{emp.job} job
from dual
</foreach>
)
</insert>
内置参数parameter&databaseId
- 在MyBatis中内置了两个参数
- _parameter:描述方法传进来的参数,如果是多个参数则会被封装到"Map"集合中,则_parameter参数描述的是这个Map集合
- _databaseId:描述数据库厂商的别名
- 示例:使用内置参数
<select id="builtInParam" resultMap="builtInParam_resultMap" >
<if test="_databaseId=='oracle'">
<if test="_parameter!=null">
SELECT empno,ename,job
FROM EMP
</if>
</if>
<if test="_databaseId=='mysql'">
<if test="_parameter!=null">
SELECT id, ename, age, job, dept_id
FROM employee
</if>
</if>
</select>
bind绑定
-
bind:可以讲一个OGNL表达式的值绑定到一个变量中,方便后来引用这个变量.
-
<bind>中的属性
- name:定义的变量名
- value:变量表达式
-
示例:
<select id="builtInParam" resultMap="builtInParam_resultMap">
<if test="_databaseId=='oracle'">
SELECT empno,ename,job
FROM EMP
<if test="ename!=null">
<bind name="_ename" value="'%'+ename+'%'"/>
WHERE ename LIKE #{_ename}
</if>
</if>
</select>
sql抽取可重用的片段
- <sql>标签可以将一些重用的字段,保存起来,为其他便签提供一个id,其他标签就可以使用<include>标签引用<sql>标签中的内容.
- <sql>标签中可以使用<if>或其他标签进行逻辑判断,动态生成内部内容
- <include>标签也可以自定义一些变量,而在<sql>标签中使用"${}"的方式进行引用
<include refid="empField">
<!-- 传递自定义的参数-->
<property name="test" value="testField"/>
</include>
- 示例:
<!--定义重用字段-->
<sql id="empField">
<if test="_databaseId=='oracle'">
empno id, ename, job,deptno dept_id
</if>
<if test="_databaseId=='mysql'">
id, ename,job, dept_id
</if>
</sql>
<sql id="empTableName">
<if test="_databaseId=='oracle'">
emp
</if>
<if test="_databaseId=='mysql'">
employee
</if>
</sql>
- 引用<sql>字段
<select id="builtInParam" resultMap="builtInParam_resultMap">
SELECT
<include refid="empField"></include>
FROM
<include refid="empTableName"></include>
<if test="ename!=null">
<bind name="_ename" value="'%'+ename+'%'"/>
WHERE ename LIKE #{_ename}
</if>
</select>
1866
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
