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();    }}