Mybatis动态SQL

Mybatis，在带注解的映射器接口类中使用动态SQL，可使用script元素，即：<script></script>。

案例代码

Model

package com.what21.mybatis.model;import lombok.Data;@Datapublic class Employee { // ID private Integer id; // 名称 private String name; // 邮箱 private String email; // 性别 private String gender; // 年龄 private Integer age;}

Mapper<映射器接口>

package com.what21.mybatis.demo03.mapper;import com.what21.mybatis.model.Employee;import org.apache.ibatis.annotations.*;import java.util.List;public interface EmployeeMapper { @Select(value = "select * from `employee` where id = #{id}") @ResultType(Employee.class) public Employee findEmployeeById(Integer id); @Update(value = "<script>" + "update `employee` " + " <set>" + " <if test='name != null'>" + " `name`=#{name}," + " </if>" + " <if test='email != null'>" + " `email`=#{email}," + " </if>" + " <if test='gender != null'>" + " `gender`=#{gender}," + " </if>" + " <if test='age != null'>" + " `age`=#{age} " + " </if>" + " </set>" + "where id = #{id}" + "</script>") public int updateEmployee(Employee employee); @Delete(value = "<script>" + " delete from `employee` where id in" + " <foreach collection='idList' item='id' open='(' separator=',' close=')'>" + " #{id}" + " </foreach>" + "</script>") public int deleteEmployeeByIds(@Param("idList") List<Integer> idList);}

配置文件：

<?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> <settings> <!--建议显示的指定需要配置的值，防止版本更新带来的问题--> <!--开启驼峰命名规则--> <setting name="mapUnderscoreToCamelCase" value="true"/> </settings> <typeAliases> <typeAlias type="com.what21.mybatis.model.Employee" alias="employee"/> </typeAliases> <plugins> <plugin interceptor="com.github.pagehelper.PageInterceptor"> <!--默认值为 false，当该参数设置为 true 时，如果 pageSize=0 或者 RowBounds.limit = 0 就会查询出全部的结果--> <!--如果某些查询数据量非常大，不应该允许查出所有数据--> <property name="pageSizeZero" value="true"/> </plugin> </plugins> <environments default="development"> <environment id="development"> <transactionManager type="jdbc"/> <dataSource type="POOLED"> <property name="driver" value="com.mysql.jdbc.Driver"/> <property name="url" value="jdbc:mysql://localhost:3306/demo?characterEncoding=UTF8&useSSL=false&serverTimezone=UTC&rewriteBatchedStatements=true"/> <property name="username" value="root"/> <property name="password" value="root"/> </dataSource> </environment> </environments> <mappers> <mapper class="com.what21.mybatis.demo03.mapper.EmployeeMapper" /> </mappers></configuration>

测试类<main方法测试>：

package com.what21.mybatis.demo03;import com.what21.mybatis.demo03.mapper.EmployeeMapper;import com.what21.mybatis.model.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 java.io.IOException;import java.io.Reader;import java.util.Arrays;import java.util.List;public class MybatisDemoMain { /** * 创建sessionFactory * * @param mybatisConfig * @return */ private static SqlSessionFactory getSessionFactory(String mybatisConfig) { SqlSessionFactory sessionFactory = null; try { Reader reader = Resources.getResourceAsReader(mybatisConfig); sessionFactory = new SqlSessionFactoryBuilder().build(reader); } catch (IOException e) { e.printStackTrace(); } return sessionFactory; } public static void main(String[] args) { String resource = "com/what21/mybatis/demo03/mybatisConfig.xml"; // 从SessionFactory中获取SqlSession SqlSession sqlSession = getSessionFactory(resource).openSession(); // Mapper EmployeeMapper employeeMapper = sqlSession.getMapper(EmployeeMapper.class); // select操作 Employee findEmployee = employeeMapper.findEmployeeById(2); System.out.println(findEmployee); findEmployee.setName("学习测试demo"); findEmployee.setEmail("leran@demo.com"); findEmployee.setAge(34); findEmployee.setGender("1"); int updateOperate = employeeMapper.updateEmployee(findEmployee); System.out.println("update操作结果：" + updateOperate); List<Integer> idsList = Arrays.asList(findEmployee.getId()); // delete操作 int deleteOperate = employeeMapper.deleteEmployeeByIds(idsList); // SQL正确，没有删除任何一条数据，返回结果为：0 System.out.println("delete操作结果：" + deleteOperate); // 提交 //sqlSession.commit(); }}