Mybatis的动态SQL

1 简介

动态SQL是Mybatis强大特性之一。极大的简化我们拼装SQL的操作。
动态SQL元素和使用JSTL或其他类似基于XML的文本处理器相似。
Mybatis采用功能强大的基于OGNL的表达式来简化操作。
- if
- choose（when、otherwise）
- trim（where、set）
- foreach

2 环境搭建

导入相关jar包的Maven坐标：

<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.4.1</version>
</dependency>
<dependency>
    <groupId>log4j</groupId>
    <artifactId>log4j</artifactId>
    <version>1.2.17</version>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.21</version>
</dependency>
<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.13</version>
    <scope>test</scope>
</dependency>

sql脚本

DROP TABLE IF EXISTS `employee`;
CREATE TABLE `employee`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `last_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `gender` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `email` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 2 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

db.propreties

jdbc.url=jdbc:mysql://192.168.134.100:3306/test?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true
jdbc.driverClass=com.mysql.cj.jdbc.Driver
jdbc.username=root
jdbc.password=123456

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>
   <level value="debug" />
   <appender-ref ref="STDOUT" />
 </root>
</log4j:configuration>

Employee.java

package com.sunxiaping.domain;

import org.apache.ibatis.type.Alias;

@Alias("emp")
public class Employee {

    private Integer id;

    private String lastName;

    private String email;

    private String gender;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getLastName() {
        return lastName;
    }

    public void setLastName(String lastName) {
        this.lastName = lastName;
    }

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    @Override
    public String toString() {
        return "Employee{" +
                "id=" + id +
                ", lastName='" + lastName + '\'' +
                ", email='" + email + '\'' +
                ", gender='" + gender + '\'' +
                '}';
    }
}

EmployeeMapper.java

package com.sunxiaping.mapper;

public interface EmployeeMapper {

}

EmployeeMapper.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.sunxiaping.mapper.EmployeeMapper">
    
</mapper>

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"></properties>
    <settings>
        <!-- 开启自动驼峰命名规则映射 -->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
        <!-- 开启对jdbcType的NULL的支持 -->
        <setting name="jdbcTypeForNull" value="NULL"/>
        <!-- 开启延迟加载 -->
        <setting name="lazyLoadingEnabled" value="true"/>
        <!-- 开启按需加载 -->
        <setting name="aggressiveLazyLoading" value="false"/>
    </settings>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driverClass}"/>
                <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.sunxiaping.mapper"/>
    </mappers>

</configuration>

3 if判断

示例：
EmployeeMapper.java

package com.sunxiaping.mapper;

import com.sunxiaping.domain.Employee;

import java.util.List;

public interface EmployeeMapper {

    List<Employee> getEmpsByConditionIf(Employee employee);
}

EmployeeMapper.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.sunxiaping.mapper.EmployeeMapper">
    <select id="getEmpsByConditionIf" resultType="com.sunxiaping.domain.Employee">
        SELECT id as id,last_name as lastName,gender as gender,email as email
        FROM employee
        WHERE 1 = 1
        <!--
            test：条件判断表达式OGNL
        -->
        <if test="id != null">
            AND id = #{id,jdbcType=INTEGER}
        </if>
        <if test="lastName != null and lastName != ''">
            AND last_name LIKE #{lastName,jdbcType=VARCHAR}
        </if>
        <if test="gender != null and gender != ''">
            AND gender = #{gender,jdbcType=VARCHAR}
        </if>
        <if test="email != null and email != ''">
            AND email = #{email,jdbcType=VARCHAR}
        </if>
    </select>
</mapper>

测试：

package com.sunxiaping;

import com.sunxiaping.domain.Employee;
import com.sunxiaping.mapper.EmployeeMapper;
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 org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;


public class EmployeeTest {
    SqlSessionFactory sqlSessionFactory = null;
    SqlSession sqlSession = null;
    EmployeeMapper employeeMapper = null;
   

    @Before
    public void before() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        sqlSession = sqlSessionFactory.openSession(true);
        employeeMapper = sqlSession.getMapper(EmployeeMapper.class);
        
    }

    @After
    public void after() {
        if (null != sqlSession) {
            sqlSession.close();
        }
    }

    @Test
    public void testFindById() {
        Employee example = new Employee();
        example.setId(1);
        example.setGender("男");
        List<Employee> employeeList = employeeMapper.getEmpsByConditionIf(example);
        System.out.println("employeeList = " + employeeList);
    }

}

4 where查询条件

如果查询条件中有多个AND或OR语句，Mybatis推荐使用where标签，其内部会自动将多余的AND或OR去掉。当然，where标签强制类似AND xxx=xxx，AND xxx=xxx的方式。
示例：
EmployeeMapper.java

package com.sunxiaping.mapper;

import com.sunxiaping.domain.Employee;

import java.util.List;

public interface EmployeeMapper {

    List<Employee> getEmpsByConditionWhere(Employee employee);
}

EmployeeMapper.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.sunxiaping.mapper.EmployeeMapper">
    <select id="getEmpsByConditionWhere" resultType="com.sunxiaping.domain.Employee">
        SELECT id as id,last_name as lastName,gender as gender,email as email
        FROM employee
        <where>
            <if test="id != null">
                id = #{id,jdbcType=INTEGER}
            </if>
            <if test="lastName != null and lastName != ''">
                AND last_name LIKE #{lastName,jdbcType=VARCHAR}
            </if>
            <if test="gender != null and gender != ''">
                AND gender = #{gender,jdbcType=VARCHAR}
            </if>
            <if test="email != null and email != ''">
                AND email = #{email,jdbcType=VARCHAR}
            </if>
        </where>
    </select>
</mapper>

测试：

package com.sunxiaping;

import com.sunxiaping.domain.Employee;
import com.sunxiaping.mapper.EmployeeMapper;
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 org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;


public class EmployeeTest {
    SqlSessionFactory sqlSessionFactory = null;
    SqlSession sqlSession = null;
    EmployeeMapper employeeMapper = null;
   
    @Before
    public void before() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        sqlSession = sqlSessionFactory.openSession(true);
        employeeMapper = sqlSession.getMapper(EmployeeMapper.class);
    }

    @After
    public void after() {
        if (null != sqlSession) {
            sqlSession.close();
        }
    }

    @Test
    public void testFindById() {
        Employee example = new Employee();
        example.setGender("男");
        List<Employee> employeeList = employeeMapper.getEmpsByConditionWhere(example);
        System.out.println("employeeList = " + employeeList);
    }
}

5 trim自定义字符串截取

上面的where标签有限制，一旦使用了类似xxx=xxx AND,xxx=xxx AND的情况，where标签就失效了，这个时候可以使用trim自定义字符串截取，当前，trim标签也可以模拟出where标签的效果。
示例：
EmployeeMapper.java

package com.sunxiaping.mapper;

import com.sunxiaping.domain.Employee;

import java.util.List;

public interface EmployeeMapper {

    List<Employee> getEmpsByConditionTrim(Employee employee);
}

EmployeeMapper.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.sunxiaping.mapper.EmployeeMapper">
    <select id="getEmpsByConditionTrim" resultType="com.sunxiaping.domain.Employee">
        SELECT id as id,last_name as lastName,gender as gender,email as email
        FROM employee
        <!--
            trim标签 
                prefix：前缀，trim标签体中是整个字符串拼串后的结果，prefix就是给整个字符串加一个前缀
                prefixOverrides：前缀覆盖。去掉整个字符串前面多余的字符
                suffix：后缀，prefix就是给整个字符串加一个后缀
                suffixOverrides：后缀覆盖。去掉整个字符串后面多余的字符
        -->
        <trim prefix="WHERE" suffixOverrides="AND">
            <if test="id != null">
                id = #{id,jdbcType=INTEGER} AND
            </if>
            <if test="lastName != null and lastName != ''">
                 last_name LIKE #{lastName,jdbcType=VARCHAR} AND
            </if>
            <if test="gender != null and gender != ''">
                 gender = #{gender,jdbcType=VARCHAR} AND
            </if>
            <if test="email != null and email != ''">
                 email = #{email,jdbcType=VARCHAR}
            </if>
        </trim>
    </select>
</mapper>

测试：

package com.sunxiaping;

import com.sunxiaping.domain.Employee;
import com.sunxiaping.mapper.EmployeeMapper;
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 org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;


public class EmployeeTest {
    SqlSessionFactory sqlSessionFactory = null;
    SqlSession sqlSession = null;
    EmployeeMapper employeeMapper = null;
   
    @Before
    public void before() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        sqlSession = sqlSessionFactory.openSession(true);
        employeeMapper = sqlSession.getMapper(EmployeeMapper.class);
    }

    @After
    public void after() {
        if (null != sqlSession) {
            sqlSession.close();
        }
    }

    @Test
    public void testFindById() {
        Employee example = new Employee();
        example.setGender("男");
        List<Employee> employeeList = employeeMapper.getEmpsByConditionTrim(example);
        System.out.println("employeeList = " + employeeList);
    }

}

6 choose分支选择

有点类似Java中的switch-case语句。
示例：如果查询条件中有id，就用id查询;如果查询条件中有lastName，就用lastName查询;如果查询条件中有gender，就用gener查询;否则，用emial查询。
EmployeeMapper.java

package com.sunxiaping.mapper;

import com.sunxiaping.domain.Employee;

import java.util.List;

public interface EmployeeMapper {

    List<Employee> getEmpsByConditionChoose(Employee employee);
}

EmployeeMapper.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.sunxiaping.mapper.EmployeeMapper">
    <select id="getEmpsByConditionChoose" resultType="com.sunxiaping.domain.Employee">
        SELECT id as id,last_name as lastName,gender as gender,email as email
        FROM employee
        <where>
            <!--
                choose标签：分支选择，有点类似Java中的switch-case
            -->
            <choose>
                <when test="id != null">
                    id = #{id,jdbcType=INTEGER}
                </when>
                <when test="lastName != null and lastName != ''">
                    last_name = #{lastName,jdbcType=VARCHAR}
                </when>
                <when test="gender != null and gender != ''">
                    gender = #{gender,jdbcType=VARCHAR}
                </when>
                <otherwise>
                    email = #{email,jdbcType=VARCHAR}
                </otherwise>
            </choose>
        </where>
    </select>
</mapper>

7 set更新

set标签一般结合if标签实现动态更新。Mybatis中的set标签会自动将最右边多出的“,”去掉。
示例：
EmployeeMapper.java

package com.sunxiaping.mapper;

import com.sunxiaping.domain.Employee;

public interface EmployeeMapper {

    void updateEmployee(Employee employee);
}

EmployeeMapper.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.sunxiaping.mapper.EmployeeMapper">

    <update id="updateEmployee">
        UPDATE employee
        <set>
            <if test="lastName != null and lastName != ''">
                last_name = #{lastName,jdbcType=VARCHAR},
            </if>
            <if test="gender != null and gender != ''">
                gender = #{gender,jdbcType=VARCHAR},
            </if>
            <if test="email != null and email != ''">
                email = #{email,jdbcType=VARCHAR}
            </if>
        </set>
        <where>
            <if test="id != null">
                id = #{id,jdbcType=INTEGER}
            </if>
        </where>

    </update>
</mapper>

8 foreach遍历

foreach标签类似于jstl中的<c:forEach>标签，都是迭代一个集合中的对象。
示例：
EmployeeMapper.java

package com.sunxiaping.mapper;

import com.sunxiaping.domain.Employee;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface EmployeeMapper {

    List<Employee> findEmpsByConditionForeach(@Param("ids") List<Integer> ids);
}

EmployeeMapper.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.sunxiaping.mapper.EmployeeMapper">

    <select id="findEmpsByConditionForeach" resultType="com.sunxiaping.domain.Employee">
        SELECT id as id,last_name as lastName,gender as gender,email as email
        FROM employee
        <!--
            foreach 遍历集合
                collection：指定要遍历的集合
                item：将当前遍历的元素赋值给指定的变量
                separator：每个元素之间的分隔符
                open：遍历出所有的结果拼接一个开始的字符
                close：遍历出所有的结果拼接一个结束的字符
                index：
                    如果遍历的是List，那么index就是索引，item就是当前的值。
                    如果遍历的是Map，那么index就是Map的key，item就是Map中的值。
        -->   
        <foreach collection="ids" item="id" open="WHERE id IN (" separator="," close=")">
            #{id,jdbcType=INTEGER}
        </foreach>
    </select>
</mapper>

9 MySQL下的foreach批量插入的两种方式

第一种批量插入数据的SQL方式如下：

INSERT INTO 表名(字段1,字段2,...) VALUES (值1,值2,...),(值1,值2,...);

示例：
EmployeeMapper.java

package com.sunxiaping.mapper;

import com.sunxiaping.domain.Employee;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface EmployeeMapper {

    void batchInsertEmps(@Param("emps") List<Employee> employees);
}

EmployeeMapper.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.sunxiaping.mapper.EmployeeMapper">
    <insert id="batchInsertEmps">
        <foreach collection="emps" item="emp" open="INSERT INTO employee (last_name,gender,email) VALUES" separator=",">
             (#{emp.lastName,jdbcType=VARCHAR},#{emp.gender,jdbcType=VARCHAR},#{emp.email,jdbcType=VARCHAR})
        </foreach>
    </insert>
</mapper>

第二种批量插入数据的SQL方式如下：

INSERT INTO 表名(字段1,字段2,...) VALUES (值1,值2,...);
INSERT INTO 表名(字段1,字段2,...) VALUES (值1,值2,...);

但是，在JDBC操纵MySQL的时候，需要在jdbc.url上加上“allowMultiQueries=true”参数，当然这个参数也可以用于批量删除、批量更新。
示例：
db.properties

jdbc.url=jdbc:mysql://192.168.134.100:3306/test?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true&useSSL=false&serverTimezone=GMT%2B8&allowPublicKeyRetrieval=true&allowMultiQueries=true
jdbc.driverClass=com.mysql.cj.jdbc.Driver
jdbc.username=root
jdbc.password=123456

EmployeeMapper.java

package com.sunxiaping.mapper;

import com.sunxiaping.domain.Employee;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface EmployeeMapper {

    void batchInsertEmps(@Param("emps") List<Employee> employees);
}

EmployeeMapper.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.sunxiaping.mapper.EmployeeMapper">
    <insert id="batchInsertEmps">
        <foreach collection="emps" item="emp" separator=";">
            INSERT INTO employee (last_name,gender,email) VALUES (#{emp.lastName,jdbcType=VARCHAR},#{emp.gender,jdbcType=VARCHAR},#{emp.email,jdbcType=VARCHAR})
        </foreach>
    </insert>
</mapper>

10 Oracle下的foreach批量插入的两种方式

第一种批量插入数据的SQL方式如下：

begin
	INSERT INTO 表名(字段1,字段2,...) VALUES (值1,值2,...);
	INSERT INTO 表名(字段1,字段2,...) VALUES (值1,值2,...);
end;

示例：
EmployeeMapper.java

package com.sunxiaping.mapper;

import com.sunxiaping.domain.Employee;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface EmployeeMapper {

    void batchInsertEmps(@Param("emps") List<Employee> employees);
}

EmployeeMapper.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.sunxiaping.mapper.EmployeeMapper">
    <insert id="batchInsertEmps">
        <foreach collection="emps" item="emp" open="begin" close="end;" >
            INSERT INTO employee (id,last_name,gender,email) VALUES (#{employee_seq.nextval},#{emp.lastName,jdbcType=VARCHAR},#{emp.gender,jdbcType=VARCHAR},#{emp.email,jdbcType=VARCHAR}); 
        </foreach>
    </insert>
</mapper>

第二种批量插入数据的SQL方式如下：

INSERT INTO 表名(字段1,字段2,...) 
	SELECT 别名1,别名2,... FROM (
    	SELECT 值1 别名1,值2 别名2,... FROM dual
       	UNION
        SELECT 值1 别名1,值2 别名2<?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.sunxiaping.mapper.EmployeeMapper">
    <insert id="batchInsertEmps">
        <foreach collection="emps" item="emp" open="INSERT INTO employee (id,last_name,gender,email) SELECT employee.nextval,lastName,gender,email FROM (" close=")" separator="UNION">
            SELECT #{emp.lastName,jdbcType=VARCHAR} lastName,#{emp.gender,jdbcType=VARCHAR} gender,#{emp.email,jdbcType=VARCHAR} email FROM dual
        </foreach>
    </insert>
</mapper>,... FROM dual
    )

示例：
EmployeeMapper.java

package com.sunxiaping.mapper;

import com.sunxiaping.domain.Employee;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface EmployeeMapper {

    void batchInsertEmps(@Param("emps") List<Employee> employees);
}

EmployeeMapper.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.sunxiaping.mapper.EmployeeMapper">
    <insert id="batchInsertEmps">
        <foreach collection="emps" item="emp" open="INSERT INTO employee (id,last_name,gender,email) SELECT employee.nextval,lastName,gender,email FROM (" close=")" separator="UNION">
            SELECT #{emp.lastName,jdbcType=VARCHAR} lastName,#{emp.gender,jdbcType=VARCHAR} gender,#{emp.email,jdbcType=VARCHAR} email FROM dual
        </foreach>
    </insert>
</mapper>

11 内置参数

Mybatis不只是方法传递过来的参数可以用来判断，取值…，Mybatis默认还有两个默认的参数：_parameter和_databaseId。
_parameter：代表整个参数。
- 如果是单个参数：_parameter就是这个参数。
- 如果是多个参数：参数会封装到一个Map中，_parameter就是代表这个Map。
_databaseId：如果在全局配置文件中配置了databaseIdProvider标签，那么_databaseId就是代表当前数据库的别名。
示例：
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"></properties>
    <settings>
        <!-- 开启自动驼峰命名规则映射 -->
        <setting name="mapUnderscoreToCamelCase" value="true"/>
        <!-- 开启对jdbcType的NULL的支持 -->
        <setting name="jdbcTypeForNull" value="NULL"/>
        <!-- 开启延迟加载 -->
        <setting name="lazyLoadingEnabled" value="true"/>
        <!-- 开启按需加载 -->
        <setting name="aggressiveLazyLoading" value="false"/>
    </settings>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="${jdbc.driverClass}"/>
                <property name="url" value="${jdbc.url}"/>
                <property name="username" value="${jdbc.username}"/>
                <property name="password" value="${jdbc.password}"/>
            </dataSource>
        </environment>
    </environments>

    <databaseIdProvider type="DB_VENDOR">
        <!--
            为不同的数据库厂商起别名
        -->
        <property name="MySQL" value="mysql"/>
        <property name="Oracle" value="oracle"/>
        <property name="SQL Server" value="sqlserver"/>
    </databaseIdProvider>
    <mappers>
        <package name="com.sunxiaping.mapper"/>
    </mappers>

</configuration>

EmployeeMapper.java

package com.sunxiaping.mapper;

import com.sunxiaping.domain.Employee;

import java.util.List;

public interface EmployeeMapper {

   List<Employee> findByBuiltParameter();
}

EmployeeMapper.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.sunxiaping.mapper.EmployeeMapper">
    
    <select id="findByBuiltParameter" resultType="com.sunxiaping.domain.Employee">
        <if test="_databaseId == 'mysql'">
            SELECT id as id,last_name as lastName,email as email ,gender as gender
            FROM employee
            LIMIT 0,5
        </if>
        <if test="_databaseId == 'oracle'">
            SELECT * FROM
            (SELECT e.*,rownum as r1 FROM employee e WHERE rownum &lt; 5)
            WHERE r1 &gt; 1
        </if>


    </select>
</mapper>

12 bind绑定

bind标签可以从OGNL表达式中创建一个变量并将其绑定到上下文。
示例：
EmployeeMapper.java

package com.sunxiaping.mapper;

import com.sunxiaping.domain.Employee;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface EmployeeMapper {

   List<Employee> findByLastNameLike(@Param("lastName") String lastName);
}

EmployeeMapper.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.sunxiaping.mapper.EmployeeMapper">

    <select id="findByLastNameLike" resultType="com.sunxiaping.domain.Employee">
        <bind name="_lastName" value="'%' + lastName + '%'"></bind>
        SELECT id as id,last_name as lastName,email as email ,gender as gender
        FROM employee
        WHERE last_name like #{_lastName,jdbcType=VARCHAR}
    </select>
</mapper>

测试：

package com.sunxiaping;

import com.sunxiaping.domain.Employee;
import com.sunxiaping.mapper.EmployeeMapper;
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 org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.io.IOException;
import java.io.InputStream;
import java.util.List;


public class EmployeeTest {
    SqlSessionFactory sqlSessionFactory = null;
    SqlSession sqlSession = null;
    EmployeeMapper employeeMapper = null;

    @Before
    public void before() throws IOException {
        String resource = "mybatis-config.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        sqlSession = sqlSessionFactory.openSession(true);
        employeeMapper = sqlSession.getMapper(EmployeeMapper.class);
    }

    @After
    public void after() {
        if (null != sqlSession) {
            sqlSession.close();
        }
    }

    @Test
    public void testFindById() {

        List<Employee> employeeList = employeeMapper.findByLastNameLike("a");
        System.out.println("employeeList = " + employeeList);
    }

}

13 抽取可重用的SQL片段

sql标签可以抽取可重用的SQL片段，方便后面引用。
示例：
EmployeeMapper.java

package com.sunxiaping.mapper;

import com.sunxiaping.domain.Employee;
import org.apache.ibatis.annotations.Param;

import java.util.List;

public interface EmployeeMapper {

   List<Employee> findByLastNameLike(@Param("lastName") String lastName);
}

EmployeeMapper.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.sunxiaping.mapper.EmployeeMapper">
    <!--
        抽取可重用的SQL片段，方便后面引用
    -->
    <sql id="emp_sql" >
        id as id,last_name as lastName,email as email ,gender as gender
    </sql>

    <select id="findByLastNameLike" resultType="com.sunxiaping.domain.Employee">
        <bind name="_lastName" value="'%' + lastName + '%'"></bind>
        <!--
            include标签，引用外部定义的可重用的SQL片段
        -->
        SELECT <include refid="emp_sql"/>
        FROM employee
        WHERE last_name like #{_lastName,jdbcType=VARCHAR}
    </select>
</mapper>

posted @ 2020-09-13 09:02 许大仙阅读(340) 评论(0) 编辑收藏举报

刷新页面返回顶部

Loading

不为往事扰，余生只爱笑。

Mybatis的动态SQL

1 简介

2 环境搭建

3 if判断

4 where查询条件

5 trim自定义字符串截取

6 choose分支选择

7 set更新

8 foreach遍历

9 MySQL下的foreach批量插入的两种方式

10 Oracle下的foreach批量插入的两种方式

11 内置参数

12 bind绑定

13 抽取可重用的SQL片段

公告