1 前言
动态 SQL 是MyBatis 强大特性之一,主要包含 sql、if、choose、where、set、trim、foreach 等标签,本文仅介绍前6个标签的用法,对于 foreach 标签的用法,将在下一个专题介绍。
- <sql>:定义 SQL 片段,通过 <sql id="sql_id"></sql> 定义SQL片段,<include refid="sql_id"/> 引用定义好的片段
- <if>:条件语句,用法:<if test="expression"></if>
- <choose>:单选,用法:<choose> <when test=""></when> <otherwise></otherwise> </choose>
- <where>:添加 where 关键字,去掉多余的 and 和 or
- <set>:解决 SQL 语句中可能出现过多逗号的问题
- trim:截取并拼接,用法:<trim prefix="" suffix="" prefixOverrides="" suffixOverrides=""></trim>,prefix:在操作的 SQL 语句前加入某些内容,suffix:在操作的 SQL 语句后加入某些内容,prefixOverrides:把操作 SQL 语句前的某些内容去掉,suffixOverrides:把操作 SQL 语句后的某些内容去掉。
2 实验环境
(1)导入 JAR 包
其中,前2个 jar 包下载地址见 → log4j-1.2.17.jar、 mybatis-3.4.1.jar,将 jar 包放入 lib 目录下,并选中所有 jar 包,右键,选择【Add to Build Path】。
(2) 工作目录
注意:src 和 conf 目录下的 com.mapper 包必须同名。
(3)配置文件
log4j.xml
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE log4j:configuration SYSTEM "log4j.dtd">
<log4j:configuration xmlns:log4j="http://jakarta.apache.org/log4j/">
<appender name="STDOUT" class="org.apache.log4j.ConsoleAppender">
<param name="Encoding" value="UTF-8" />
<layout class="org.apache.log4j.PatternLayout">
<param name="ConversionPattern" value="%-5p %d{MM-dd HH:mm:ss,SSS} %m (%F:%L)\n" />
</layout>
</appender>
<logger name="java.sql">
<level value="debug" />
</logger>
<logger name="org.apache.ibatis">
<level value="info" />
</logger>
<root>
<priority value="debug" />
<appender-ref ref="STDOUT" />
</root>
</log4j:configuration>
注意:log4j.xml文件名不能随意更改。
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>
<!-- 设置或引入资源文件 -->
<properties resource="jdbc.properties"></properties>
<!-- 设置连接数据库的环境,default用于设置默认使用的数据库环境 -->
<environments default="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>
</environments>
<!-- 引入映射文件 -->
<mappers>
<package name="com.mapper"/>
</mappers>
</configuration>
jdbc.properties
# K = V
jdbc.driver=com.mysql.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/users
jdbc.username=root
jdbc.password=0.
3 案例分析
首先在 MySQL 中创建数据库:users,再在此数据库中创建表:students。
students 包含 sid(int)、sname(varchar)和 sex(varchar) 3个字段,其中,sid 设置了自增,students 表中数据如下:
首先介绍下公共的文件,主要包含 Student.java、StudentMapper.java,不同的是 StudentMapper.xml、Test.java,将在各节分别介绍。
Student.java
package com.bean;
public class Student {
private Integer sid;
private String sname;
private String sex;
public Student() {}
public Student(Integer sid, String sname, String sex) {
this.sid = sid;
this.sname = sname;
this.sex = sex;
}
public Integer getSid() {
return sid;
}
public void setSid(Integer sid) {
this.sid = sid;
}
public String getSname() {
return sname;
}
public void setSname(String sname) {
this.sname = sname;
}
public String getSex() {
return sex;
}
public void setSex(String sex) {
this.sex = sex;
}
@Override
public String toString() {
return "Student [sid=" + sid + ", sname=" + sname + ", sex=" + sex + "]";
}
}
StudentMapper.java
package com.mapper;
import java.util.List;
import com.bean.Student;
public interface StudentMapper {
public Student getStudentById(Integer sid);
public List<Student> getStudents(Student student);
public void updateStudent(Student student);
}
3.1 <sql> 标签
StudentMapper.xml
<?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.mapper.StudentMapper">
<sql id="selectColumn">select sid,sname,sex from students</sql>
<!-- public Student getStudentById(Integer sid); -->
<select id="getStudentById" resultType="com.bean.Student">
<include refid="selectColumn"/> where sid=#{sid}
</select>
</mapper>
注意:<sql> 标签标示一个 SQL 语句片段,通过 <include> 标签使用标示的 SQL 语句片段。
Test.java
package com.test;
import java.io.IOException;
import java.io.InputStream;
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 com.bean.Student;
import com.mapper.StudentMapper;
public class Test {
public static void main(String[] args) throws IOException {
InputStream is=Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(is);
SqlSession sqlSession=sqlSessionFactory.openSession(true); //自动提交事务
//getMapper:会通过动态代理动态生成StudentMapper的代理实现类
StudentMapper mapper=sqlSession.getMapper(StudentMapper.class);
Student student=mapper.getStudentById(1001);
System.out.println(student);
}
}
运行结果:
DEBUG 06-14 20:54:49,039 ==> Preparing: select sid,sname,sex from students where sid=?
Student [sid=1001, sname=张三, sex=1]
3.2 <if> 标签
StudentMapper.xml
<?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.mapper.StudentMapper">
<!-- public List<Student> getStudents(Student student); -->
<select id="getStudents" resultType="com.bean.Student">
select sid,sname,sex from students where
<if test="sid!=null">
sid=#{sid} and
</if>
<if test="sname!=null and sname!=''">
sname=#{sname} and
</if>
<if test="sex!=null and sex!='' and (sex==1 or sex==0)">
sex=#{sex}
</if>
</select>
</mapper>
注意:<if> 标签中 test 属性指定了标签内语句块能够执行的条件。
Test.java
package com.test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
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 com.bean.Student;
import com.mapper.StudentMapper;
public class Test {
public static void main(String[] args) throws IOException {
InputStream is=Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(is);
SqlSession sqlSession=sqlSessionFactory.openSession(true); //自动提交事务
//getMapper:会通过动态代理动态生成StudentMapper的代理实现类
StudentMapper mapper=sqlSession.getMapper(StudentMapper.class);
List<Student> students=mapper.getStudents(new Student(1001,"","1"));
System.out.println(students);
}
}
运行结果:
DEBUG 06-14 20:50:48,261 ==> Preparing: select sid,sname,sex from students where sid=? and sex=?
[Student [sid=1001, sname=张三, sex=1]]
可以看到:由于 sname="",where 后面只有 sid 和 sex 两个查询条件。
3.3 <choose> 标签
MyBatis 中没有 else if 和 else 标签,而 if 标签只有两个分支,不能满足用户多分支条件查询的需求,而 choose 标签能够解决这一问题,其内部只有第一个符合条件的语句块会执行。
StudentMapper.xml
<?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.mapper.StudentMapper">
<!-- public List<Student> getStudents(Student student); -->
<select id="getStudents" resultType="com.bean.Student">
select sid,sname,sex from students where
<choose>
<when test="sid!=null">
sid=#{sid}
</when>
<when test="sname!=null and sname!=''">
sname=#{sname}
</when>
<otherwise>
sex=#{sex}
</otherwise>
</choose>
</select>
</mapper>
注意:<choose> 标签中最多只有一个语句会执行,并且只有第一个符合条件的会执行;<when> 标签中 test 属性指定了标签内语句块能够执行的条件;当 <when> 标签内的条件都不满足时,会执行 <otherwise> 标签内语句,另外 <otherwise> 是非必须的,即可以省去。
Test.java
package com.test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
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 com.bean.Student;
import com.mapper.StudentMapper;
public class Test {
public static void main(String[] args) throws IOException {
InputStream is=Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(is);
SqlSession sqlSession=sqlSessionFactory.openSession(true); //自动提交事务
//getMapper:会通过动态代理动态生成StudentMapper的代理实现类
StudentMapper mapper=sqlSession.getMapper(StudentMapper.class);
List<Student> students=mapper.getStudents(new Student(null,"李四",""));
System.out.println(students);
}
}
运行结果:
DEBUG 06-14 21:14:24,168 ==> Preparing: select sid,sname,sex from students where sname=?
[Student [sid=1002, sname=李四, sex=0]]
可以看到:where 后面只有 sname 这一个查询条件。
3.4 <where> 标签
3.2节 StudentMapper.xml 中部分代码如下:
select sid,sname,sex from students where
<if test="sid!=null">
sid=#{sid} and
</if>
<if test="sname!=null and sname!=''">
sname=#{sname} and
</if>
<if test="sex!=null and sex!='' and (sex==1 or sex==0)">
sex=#{sex}
</if>
若最后一个条件不符合条件,则会在末尾多个 and,不符合 SQL 语法,因此会报错。
方案一(添加条件【1=1】)
可以通过如下方法解决此问题:
select sid,sname,sex from students where 1=1
<if test="sid!=null">
and sid=#{sid}
</if>
<if test="sname!=null and sname!=''">
and sname=#{sname}
</if>
<if test="sex!=null and sex!='' and (sex==1 or sex==0)">
and sex=#{sex}
</if>
Test.java 同3.3节。
运行结果:
DEBUG 06-14 22:08:56,654 ==> Preparing: select sid,sname,sex from students where 1=1 and sname=?
[Student [sid=1002, sname=李四, sex=0]]
可以看到:where 后面多了个条件【1=1】。
方案二(使用 <where> 标签)
StudentMapper.xml
<?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.mapper.StudentMapper">
<!-- public List<Student> getStudents(Student student); -->
<select id="getStudents" resultType="com.bean.Student">
select sid,sname,sex from students
<where>
<if test="sid!=null">
and sid=#{sid}
</if>
<if test="sname!=null and sname!=''">
and sname=#{sname}
</if>
<if test="sex!=null and sex!='' and (sex==1 or sex==0)">
and sex=#{sex}
</if>
</where>
</select>
</mapper>
注意:<where> 标签用于添加 where 关键字,并删除第一个可能多余的 and 和 or。
Test.java 同3.3节。
运行结果:
DEBUG 06-14 22:04:00,454 ==> Preparing: select sid,sname,sex from students WHERE sname=?
[Student [sid=1002, sname=李四, sex=0]]
可以看到:替换了多余的 and。
3.5 <set> 标签
StudentMapper.xml
<?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.mapper.StudentMapper">
<!-- public void updateStudent(Student student); -->
<update id="updateStudent">
update students
<set>
<if test="sname!=null and sname!=''">
sname=#{sname},
</if>
<if test="sex!=null and sex!='' and (sex==1 or sex==0)">
sex=#{sex}
</if>
</set>
where sid=#{sid}
</update>
</mapper>
注意:<set> 标签用于添加 set 关键字,并删除最后一个可能多余的逗号,如:以上代码中,若只有第一个 <if> 执行,将会多一个逗号。
Test.java
package com.test;
import java.io.IOException;
import java.io.InputStream;
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 com.bean.Student;
import com.mapper.StudentMapper;
public class Test {
public static void main(String[] args) throws IOException {
InputStream is=Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(is);
SqlSession sqlSession=sqlSessionFactory.openSession(true); //自动提交事务
//getMapper:会通过动态代理动态生成StudentMapper的代理实现类
StudentMapper mapper=sqlSession.getMapper(StudentMapper.class);
mapper.updateStudent(new Student(1001,"张三三",""));
}
}
更新后数据库中 students 表中数据如下:
控制台输出如下:
DEBUG 06-14 22:35:50,138 ==> Preparing: update students SET sname=? where sid=?
可以看到:where 后面去掉了多余的逗号。
3.6 <trim> 标签
<trim> 标签用于截取并拼接SQL语句,用法如下:
<trim prefix="" suffix="" prefixOverrides="" suffixOverrides=""></trim>
- prefix:在操作的 SQL 语句前添加某些内容
- suffix:在操作的 SQL 语句后添加某些内容
- prefixOverrides:把操作 SQL 语句前的某些内容去掉
- suffixOverrides:把操作 SQL 语句后的某些内容去掉
<where> 标签能够删除第一个可能多余的 and 和 or,但不能删除最后一个可能多余的 and 和 or;<set> 标签够删除最后一个可能多余的逗号,但不能删除第一个可能多余的逗号;<trim> 标签能够灵活控制删除第一个或最后一个(或两者都兼顾)可能多余的任意字符串,有如下几个等价关系:
<trim prefix="where" prefixOverrides="and|or"></trim> <==> <where></where>
<trim prefix="set" suffixOverrides=","></trim> <==> <set></set>
StudentMapper.xml
<?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.mapper.StudentMapper">
<!-- public List<Student> getStudents(Student student); -->
<select id="getStudents" resultType="com.bean.Student">
select sid,sname,sex from students
<trim prefix="where" suffixOverrides="and" prefixOverrides="and">
<if test="sid!=null">
sid=#{sid} and
</if>
<if test="sname!=null and sname!=''">
and sname=#{sname} and
</if>
<if test="sex!=null and sex!='' and (sex==1 or sex==0)">
sex=#{sex} and
</if>
</trim>
</select>
</mapper>
注意:若只有第2个 <if> 语句块被执行,将会多2个 and。
Test.java
package com.test;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
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 com.bean.Student;
import com.mapper.StudentMapper;
public class Test {
public static void main(String[] args) throws IOException {
InputStream is=Resources.getResourceAsStream("mybatis-config.xml");
SqlSessionFactory sqlSessionFactory=new SqlSessionFactoryBuilder().build(is);
SqlSession sqlSession=sqlSessionFactory.openSession(true); //自动提交事务
//getMapper:会通过动态代理动态生成StudentMapper的代理实现类
StudentMapper mapper=sqlSession.getMapper(StudentMapper.class);
List<Student> students=mapper.getStudents(new Student(null,"张三三",""));
System.out.println(students);
}
}
运行结果:
DEBUG 06-16 22:56:54,508 ==> Preparing: select sid,sname,sex from students where sname=?
[Student [sid=1001, sname=张三三, sex=1]]
可以看到:sname 前后2个多余的 and 都被删除了。