一、if标签
作用:利用传来的参数写表达式,表达式为真则插入if内的SQL语句。
(1)Mapper文件
<?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">
<mapper namespace="com.wsh.mapper.UserMapper">
<resultMap id="EmployeeResult" type="Employee">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="deptId" column="dept_id"/>
</resultMap>
<select id="selectEmployeeList" resultMap="EmployeeResult">
select
*
from
employee
where
1 = 1
<if test="id == 1">and id = #{id}</if>
</select>
</mapper>
(2)接口文件
public interface UserMapper {
public List<Employee> selectEmployeeList(Long id);
}
(3)JAVA程序
public void test() throws IOException {
//读取配置文件创建SqlSession工厂
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//利用SqlSession工厂创建SqlSession实例
SqlSession sqlSession = sqlSessionFactory.openSession();
try {
//利用SqlSession创建代理对象
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<Employee> employees = mapper.selectEmployeeList(1L);
for (Employee employee : employees) {
System.out.println(employee.toString());
}
}finally {
sqlSession.close();
}
}
输出
2022-04-05 11:06:25,496 [main] DEBUG [com.wsh.mapper.UserMapper.selectEmployeeList] - ==> Preparing: select * from employee where 1 = 1 and id = ?
2022-04-05 11:06:25,521 [main] DEBUG [com.wsh.mapper.UserMapper.selectEmployeeList] - ==> Parameters: 1(Long)
2022-04-05 11:06:25,538 [main] DEBUG [com.wsh.mapper.UserMapper.selectEmployeeList] - <== Total: 1
Employee(id=1, name=张三, deptId=1, department=null)
二、where标签
作用:对where标签内得到的字符串进行处理。给得到的字符串移除前缀and或or 和 字符串不为空时加前缀where。
(1)Mapper文件
<?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">
<mapper namespace="com.wsh.mapper.UserMapper">
<resultMap id="EmployeeResult" type="Employee">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="deptId" column="dept_id"/>
</resultMap>
<select id="selectEmployeeList" resultMap="EmployeeResult">
select
*
from
employee
<where>
<if test="id == 1">and id = #{id}</if>
</where>
</select>
</mapper>
(2)接口文件
public interface UserMapper {
public List<Employee> selectEmployeeList(Long id);
}
(3)JAVA程序
public void test() throws IOException {
//读取配置文件创建SqlSession工厂
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//利用SqlSession工厂创建SqlSession实例
SqlSession sqlSession = sqlSessionFactory.openSession();
try {
//利用SqlSession创建代理对象
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<Employee> employees = mapper.selectEmployeeList(1L);
for (Employee employee : employees) {
System.out.println(employee.toString());
}
}finally {
sqlSession.close();
}
}
输出
2022-04-05 11:13:19,235 [main] DEBUG [com.wsh.mapper.UserMapper.selectEmployeeList] - ==> Preparing: select * from employee WHERE id = ?
2022-04-05 11:13:19,263 [main] DEBUG [com.wsh.mapper.UserMapper.selectEmployeeList] - ==> Parameters: 1(Long)
2022-04-05 11:13:19,285 [main] DEBUG [com.wsh.mapper.UserMapper.selectEmployeeList] - <== Total: 1
Employee(id=1, name=张三, deptId=1, department=null)
当List<Employee> employees = mapper.selectEmployeeList(2L);
输出
2022-04-05 11:14:51,003 [main] DEBUG [com.wsh.mapper.UserMapper.selectEmployeeList] - ==> Preparing: select * from employee
2022-04-05 11:14:51,027 [main] DEBUG [com.wsh.mapper.UserMapper.selectEmployeeList] - ==> Parameters:
2022-04-05 11:14:51,048 [main] DEBUG [com.wsh.mapper.UserMapper.selectEmployeeList] - <== Total: 5
Employee(id=1, name=张三, deptId=1, department=null)
Employee(id=2, name=李四, deptId=1, department=null)
Employee(id=3, name=王五, deptId=1, department=null)
Employee(id=4, name=赵六, deptId=2, department=null)
Employee(id=5, name=威爷, deptId=3, department=null)
三、set标签
作用:对set标签内得到的字符串进行处理。给得到的字符串移除后缀 ","和字符串不为空时加前缀set。
(1)Mapper文件
<?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">
<mapper namespace="com.wsh.mapper.UserMapper">
<update id="updateEmployee">
update employee
<set>
<if test="name != null">name = #{name},</if>
<if test="deptId != null">dept_id = #{deptId},</if>
</set>
where
id = #{id}
</update>
</mapper>
(2)接口文件
public interface UserMapper {
public void updateEmployee(Employee employee);
}
(3)JAVA程序
public void test() throws IOException {
//读取配置文件创建SqlSession工厂
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//利用SqlSession工厂创建SqlSession实例
SqlSession sqlSession = sqlSessionFactory.openSession();
try {
//利用SqlSession创建代理对象
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
Employee employee = new Employee();
employee.setId(1L);
employee.setName("Ben");
mapper.updateEmployee(employee);
sqlSession.commit();
}finally {
sqlSession.close();
}
}
输出
2022-04-05 11:25:57,247 [main] DEBUG [com.wsh.mapper.UserMapper.updateEmployee] - ==> Preparing: update employee SET name = ? where id = ?
2022-04-05 11:25:57,279 [main] DEBUG [com.wsh.mapper.UserMapper.updateEmployee] - ==> Parameters: Ben(String), 1(Long)
2022-04-05 11:25:57,281 [main] DEBUG [com.wsh.mapper.UserMapper.updateEmployee] - <== Updates: 1
四、trim标签
作用:自定义字符串处理规则。
(1)Mapper文件
<?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">
<mapper namespace="com.wsh.mapper.UserMapper">
<resultMap id="EmployeeResult" type="Employee">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="deptId" column="dept_id"/>
</resultMap>
<select id="selectEmployeeList" resultMap="EmployeeResult">
select
*
from
employee
<trim prefix="where" suffixOverrides="or">
<if test="id == 1">id = #{id} or</if>
<if test="id == 2">id = #{id} or</if>
</trim>
</select>
</mapper>
(2)接口文件
public interface UserMapper {
public List<Employee> selectEmployeeList(Long id);
}
(3)JAVA程序
public void test() throws IOException {
//读取配置文件创建SqlSession工厂
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//利用SqlSession工厂创建SqlSession实例
SqlSession sqlSession = sqlSessionFactory.openSession();
try {
//利用SqlSession创建代理对象
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<Employee> employees = mapper.selectEmployeeList(1L);
for (Employee employee : employees) {
System.out.println(employee.toString());
}
}finally {
sqlSession.close();
}
}
输出
2022-04-05 11:16:52,668 [main] DEBUG [com.wsh.mapper.UserMapper.selectEmployeeList] - ==> Preparing: select * from employee where id = ?
2022-04-05 11:16:52,693 [main] DEBUG [com.wsh.mapper.UserMapper.selectEmployeeList] - ==> Parameters: 1(Long)
2022-04-05 11:16:52,710 [main] DEBUG [com.wsh.mapper.UserMapper.selectEmployeeList] - <== Total: 1
Employee(id=1, name=张三, deptId=1, department=null)
五、choose标签
作用:分支选择,在多个条件中只执行一个。
(1)Mapper文件
<?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">
<mapper namespace="com.wsh.mapper.UserMapper">
<resultMap id="EmployeeResult" type="Employee">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="deptId" column="dept_id"/>
</resultMap>
<select id="selectEmployeeList" resultMap="EmployeeResult">
select
*
from
employee
<where>
<choose>
<when test="id == 1">id = #{id}</when>
<when test="id == 2">id = #{id}</when>
<otherwise>id = 3</otherwise>
</choose>
</where>
</select>
</mapper>
(2)接口文件
public interface UserMapper {
public List<Employee> selectEmployeeList(Long id);
}
(3)JAVA程序
public void test() throws IOException {
//读取配置文件创建SqlSession工厂
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//利用SqlSession工厂创建SqlSession实例
SqlSession sqlSession = sqlSessionFactory.openSession();
try {
//利用SqlSession创建代理对象
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
mapper.selectEmployeeList(2L);
}finally {
sqlSession.close();
}
}
输出
2022-04-05 11:32:51,426 [main] DEBUG [com.wsh.mapper.UserMapper.selectEmployeeList] - ==> Preparing: select * from employee WHERE id = ?
2022-04-05 11:32:51,450 [main] DEBUG [com.wsh.mapper.UserMapper.selectEmployeeList] - ==> Parameters: 2(Long)
2022-04-05 11:32:51,468 [main] DEBUG [com.wsh.mapper.UserMapper.selectEmployeeList] - <== Total: 1
六、foreach标签
作用:遍历传来的参数
(1)Mapper文件
<?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">
<mapper namespace="com.wsh.mapper.UserMapper">
<resultMap id="EmployeeResult" type="Employee">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="deptId" column="dept_id"/>
</resultMap>
<select id="selectEmployeeList" resultMap="EmployeeResult">
select
*
from
employee
<if test="param2 != null">
<foreach collection="param2" item="p" separator="," open="where id in (" close=")">
#{p}
</foreach>
</if>
</select>
</mapper>
(2)接口文件
public interface UserMapper {
public List<Employee> selectEmployeeList(Long id, List<Long> list);
}
(3)JAVA程序
public void test() throws IOException {
//读取配置文件创建SqlSession工厂
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//利用SqlSession工厂创建SqlSession实例
SqlSession sqlSession = sqlSessionFactory.openSession();
try {
//利用SqlSession创建代理对象
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
mapper.selectEmployeeList(1L, Arrays.asList(1L, 2L));
}finally {
sqlSession.close();
}
}
输出
2022-04-05 11:49:32,648 [main] DEBUG [com.wsh.mapper.UserMapper.selectEmployeeList] - ==> Preparing: select * from employee where id in ( ? , ? )
2022-04-05 11:49:32,673 [main] DEBUG [com.wsh.mapper.UserMapper.selectEmployeeList] - ==> Parameters: 1(Long), 2(Long)
2022-04-05 11:49:32,695 [main] DEBUG [com.wsh.mapper.UserMapper.selectEmployeeList] - <== Total: 2
注:foreach标签用于插入操作
(1)Mapper文件
<?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">
<mapper namespace="com.wsh.mapper.UserMapper">
<insert id="addEmployeeBatch" useGeneratedKeys="true" keyProperty="param2.id">
insert into employee (name, dept_id)
values
<foreach collection="param2" item="p" separator=",">
(#{p.name}, #{p.deptId})
</foreach>
</insert>
</mapper>
(2)接口文件
public interface UserMapper {
public void addEmployeeBatch(Long id, List<Employee> list);
}
(3)JAVA程序
public void test() throws IOException {
//读取配置文件创建SqlSession工厂
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//利用SqlSession工厂创建SqlSession实例
SqlSession sqlSession = sqlSessionFactory.openSession();
try {
//利用SqlSession创建代理对象
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
Employee employee1 = new Employee();
employee1.setName("Ben");
employee1.setDeptId(2L);
Employee employee2 = new Employee();
employee2.setName("Tom");
employee2.setDeptId(2L);
mapper.addEmployeeBatch(2L, Arrays.asList(employee1, employee2));
sqlSession.commit();
System.out.println(employee1.toString());
System.out.println(employee2.toString());
}finally {
sqlSession.close();
}
}
输出
2022-04-05 11:58:42,167 [main] DEBUG [com.wsh.mapper.UserMapper.addEmployeeBatch] - ==> Preparing: insert into employee (name, dept_id) values (?, ?) , (?, ?)
2022-04-05 11:58:42,194 [main] DEBUG [com.wsh.mapper.UserMapper.addEmployeeBatch] - ==> Parameters: Ben(String), 2(Long), Tom(String), 2(Long)
2022-04-05 11:58:42,195 [main] DEBUG [com.wsh.mapper.UserMapper.addEmployeeBatch] - <== Updates: 2
Employee(id=12, name=Ben, deptId=2, department=null)
Employee(id=13, name=Tom, deptId=2, department=null)
七、sql标签
作用:抽取可重用的SQL代码片段(包括动态SQL)来在别处插入引用。
(1)Mapper文件
<?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">
<mapper namespace="com.wsh.mapper.UserMapper">
<resultMap id="EmployeeResult" type="Employee">
<id property="id" column="id"/>
<result property="name" column="name"/>
<result property="deptId" column="dept_id"/>
</resultMap>
<sql id="test">
<if test="param2 != null">id = 1</if>
</sql>
<select id="selectEmployeeList" resultMap="EmployeeResult">
select
*
from
employee
<where>
<include refid="test"></include>
</where>
</select>
</mapper>
(2)接口文件
public interface UserMapper {
public List<Employee> selectEmployeeList(Employee employee1, Employee employee2);
}
(3)JAVA程序
public void test() throws IOException {
//读取配置文件创建SqlSession工厂
String resource = "mybatis-config.xml";
InputStream inputStream = Resources.getResourceAsStream(resource);
SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
//利用SqlSession工厂创建SqlSession实例
SqlSession sqlSession = sqlSessionFactory.openSession();
try {
//利用SqlSession创建代理对象
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
List<Employee> list = mapper.selectEmployeeList(null, new Employee());
for (Employee employee : list) {
System.out.println(employee.toString());
}
}finally {
sqlSession.close();
}
}
输出
2022-04-05 16:27:42,363 [main] DEBUG [com.wsh.mapper.UserMapper.selectEmployeeList] - ==> Preparing: select * from employee WHERE id = 1
2022-04-05 16:27:42,388 [main] DEBUG [com.wsh.mapper.UserMapper.selectEmployeeList] - ==> Parameters:
2022-04-05 16:27:42,407 [main] DEBUG [com.wsh.mapper.UserMapper.selectEmployeeList] - <== Total: 1
Employee(id=1, name=张三, deptId=1, department=null)