一、动态SQL
动态 SQL 是 MyBatis 的强大特性之一。如果你使用过 JDBC 或其它类似的框架，你应该能理解根据不同条件拼接 SQL 语句有多痛苦，例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态 SQL，可以彻底摆脱这种痛苦。
- if
- choose (when, otherwise)
- trim (where, set)
- foreach
新建数据库表
SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; -- ---------------------------- -- Table structure for t_teacher -- ---------------------------- DROP TABLE IF EXISTS `t_teacher`; CREATE TABLE `t_teacher` ( `id` int(11) NOT NULL AUTO_INCREMENT, `teacher_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL, `class_name` varchar(255) DEFAULT NULL, `address` varchar(255) DEFAULT NULL, `birth_date` date DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8; -- ---------------------------- -- Records of t_teacher -- ---------------------------- BEGIN; INSERT INTO `t_teacher` VALUES (1, 'stark', '三年二班', '浦东', '2022-02-13'); INSERT INTO `t_teacher` VALUES (2, 'steve', '三年三班', '静安', '2022-02-12'); INSERT INTO `t_teacher` VALUES (3, 'clint', '三年四班', '黄埔', '2022-02-11'); INSERT INTO `t_teacher` VALUES (4, 'banner', '三年二班', '静安', '2022-02-16'); INSERT INTO `t_teacher` VALUES (5, 'thor', '三年四班', '浦东', '2022-02-16'); INSERT INTO `t_teacher` VALUES (6, 'strange', '三年三班', '黄埔', '2022-02-16'); COMMIT; SET FOREIGN_KEY_CHECKS = 1; 复制代码
工程搭建
创建一个maven项目mybatis-dynamic-sql，加入相关依赖
<dependencies> <dependency> <groupId>org.mybatis</groupId> <artifactId>mybatis</artifactId> <version>3.5.7</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.16</version> </dependency> <dependency> <groupId>junit</groupId> <artifactId>junit</artifactId> <version>4.12</version> </dependency> <dependency> <groupId>ch.qos.logback</groupId> <artifactId>logback-classic</artifactId> <version>1.2.3</version> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.16.18</version> <scope>provided</scope> </dependency> </dependencies> 复制代码
在resource目录下新增数据库信息配置文件db.properties
jdbc_driver=com.mysql.cj.jdbc.Driver jdbc_url=jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf8&autoReconnect=true&useSSL=false&serverTimezone=Asia/Shanghai jdbc_username=root jdbc_password=root 复制代码
在resource目录下新增MyBatis全局配置文件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="db.properties" /> <settings> <setting name="mapUnderscoreToCamelCase" value="true"/> </settings> <!--设置默认指向的数据库--> <environments default="dev"> <!--配置环境，不同的环境不同的id名字--> <environment id="dev"> <!-- 采用JDBC方式对数据库事务进行commit/rollback --> <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> </environments> </configuration> 复制代码
在resource目录下新增logback.xml日志配置文件
<?xml version="1.0" encoding="UTF-8"?> <configuration> <appender name="console" class="ch.qos.logback.core.ConsoleAppender"> <encoder> <pattern>[%thread] %d{HH:mm:ss.SSS} %-5level %logger{36} - %msg%n</pattern> </encoder> </appender> <root level="debug"> <appender-ref ref="console"/> </root> </configuration> 复制代码
新建entity包，增加Teacher实体类，对应t_teacher表
@Data public class Teacher { private Integer id; private String teacherName; private String className; private String address; private Date birthDate; } 复制代码
在mapper包中新增TeacherMapper接口，并增加一个方法
public interface TeacherMapper { Teacher getTeacherById(Integer id); } 复制代码
在resources目录下新建mappers文件夹，新增SQL映射文件 TeacherMapper.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.citi.mapper.TeacherMapper"> <select id="getTeacherById" resultType="com.citi.entity.Teacher"> select * from t_teacher where id = #{id} </select> </mapper> 复制代码
在MyBatis全局配置文件mybatis-config.xml中注册TeacherMapper.xml
<mappers> <mapper resource="mappers/TeacherMapper.xml"/> </mappers> 复制代码
在test包下新增TeacherMapperTest类，对getTeacherById方法测试
public class TeacherMapperTest { SqlSessionFactory sqlSessionFactory = null; SqlSession openSession = null; @Before public void setUp() throws Exception { String resource = "mybatis-config.xml"; InputStream inputStream = Resources.getResourceAsStream(resource); sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream); openSession = sqlSessionFactory.openSession(); } @Test public void getTeacherById() { TeacherMapper teacherMapper = openSession.getMapper(TeacherMapper.class); Teacher teacher = teacherMapper.getTeacherById(1); System.out.println(teacher); } } 复制代码
执行该测试方法
if，判断条件
if标签可以对WHERE关键字后面的条件进行判断，通过test属性进行条件判断
在TeacherMapper中新增一个方法，根据条件获取Teacher列表
List<Teacher> getTeacherList(Teacher teacher); 复制代码
在TeacherMapper.xml中新增SQL语句
<select id="getTeacherList" resultType="com.citi.entity.Teacher"> select * from t_teacher where <!--test="",编写判断条件 id!= null，取出传入的JavaBean属性中ID的值，判断是否为空--> <if test="id!=null"> id > #{id} and </if> <if test="className!=null"> class_name like #{className} and </if> <if test="birthDate!=null"> birth_date < #{birthDate} </if> </select> 复制代码
如果判断的test=true则加入if标签内的查询条件，test=false就忽略该if标签下的查询条件。一些判断的字符如大于、小于、&&等可以参考HTML ISO 8859-1
在TeacherMapperTest中新增测试方法
@Test public void getTeacherList() { TeacherMapper teacherMapper = openSession.getMapper(TeacherMapper.class); Teacher teacher = new Teacher(); teacher.setId(1); teacher.setClassName("三年%"); teacher.setBirthDate(new Date()); List<Teacher> teacherList = teacherMapper.getTeacherList(teacher); System.out.println(teacherList); } 复制代码
执行测试，查看控制台输出的SQL语句
