目 录CONTENT

文章目录

Mybatis快速入门——Mybatis的动态SQL(5)

Eric
2022-02-04 / 0 评论 / 0 点赞 / 148 阅读 / 26,621 字 / 正在检测是否收录...
温馨提示:
本文最后更新于 2022-02-04,若内容或图片失效,请留言反馈。部分素材来自网络,若不小心影响到您的利益,请联系我们删除。

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 top.open1024.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 top.open1024.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="top.open1024.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="top.open1024.mapper"/>
    </mappers>

</configuration>

3 if判断

  • 示例:

  • EmployeeMapper.java

package top.open1024.mapper;

import top.open1024.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="top.open1024.mapper.EmployeeMapper">
    <select id="getEmpsByConditionIf" resultType="top.open1024.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 top.open1024;

import top.open1024.domain.Employee;
import top.open1024.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 top.open1024.mapper;

import top.open1024.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="top.open1024.mapper.EmployeeMapper">
    <select id="getEmpsByConditionWhere" resultType="top.open1024.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 top.open1024;

import top.open1024.domain.Employee;
import top.open1024.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 top.open1024.mapper;

import top.open1024.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="top.open1024.mapper.EmployeeMapper">
    <select id="getEmpsByConditionTrim" resultType="top.open1024.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 top.open1024;

import top.open1024.domain.Employee;
import top.open1024.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 top.open1024.mapper;

import top.open1024.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="top.open1024.mapper.EmployeeMapper">
    <select id="getEmpsByConditionChoose" resultType="top.open1024.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 top.open1024.mapper;

import top.open1024.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="top.open1024.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 top.open1024.mapper;

import top.open1024.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="top.open1024.mapper.EmployeeMapper">

    <select id="findEmpsByConditionForeach" resultType="top.open1024.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 top.open1024.mapper;

import top.open1024.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="top.open1024.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 top.open1024.mapper;

import top.open1024.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="top.open1024.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 top.open1024.mapper;

import top.open1024.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="top.open1024.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="top.open1024.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 top.open1024.mapper;

import top.open1024.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="top.open1024.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="top.open1024.mapper"/>
    </mappers>

</configuration>
  • EmployeeMapper.java
package top.open1024.mapper;

import top.open1024.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="top.open1024.mapper.EmployeeMapper">
    
    <select id="findByBuiltParameter" resultType="top.open1024.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 top.open1024.mapper;

import top.open1024.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="top.open1024.mapper.EmployeeMapper">

    <select id="findByLastNameLike" resultType="top.open1024.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 top.open1024;

import top.open1024.domain.Employee;
import top.open1024.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 top.open1024.mapper;

import top.open1024.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="top.open1024.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="top.open1024.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>
0

评论区