Mybatis動態SQL

Mybatis，在帶註解的映射器介面類中使用動態SQL，可使用script元素，即：<script></script>。

案例代碼

Model

package com.what21.mybatis.model;

import lombok.Data;

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

}