Mybatis系列(五)动态SQL

Mybatis系列(五)动态SQL

前言

动态 SQL是MyBatis强大特性之一。极大的简化我们拼装SQL的操作。下面介绍Mybatis各个标签的使用。

一、if

SQL文件:

/*
 Navicat Premium Data Transfer

 Source Server         : mysql
 Source Server Type    : MySQL
 Source Server Version : 50529
 Source Host           : localhost:3306
 Source Schema         : mybatis

 Target Server Type    : MySQL
 Target Server Version : 50529
 File Encoding         : 65001

 Date: 17/02/2021 21:48:04
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for t_teacher
-- ----------------------------
DROP TABLE IF EXISTS `t_teacher`;
CREATE TABLE `t_teacher`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `class_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `birth` date NULL DEFAULT NULL,
  `address` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 5 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;

-- ----------------------------
-- Records of t_teacher
-- ----------------------------
INSERT INTO `t_teacher` VALUES (1, '张三', '语文', '2021-01-01', '北京市');
INSERT INTO `t_teacher` VALUES (2, '李四', '数学', '2021-02-17', '上海市');
INSERT INTO `t_teacher` VALUES (3, '王五', '英语', '2021-02-17', '河南省');

SET FOREIGN_KEY_CHECKS = 1;

TeacherMapper.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.cetc.dao.TeacherMapper">
    <select id="getTeacherListByCondition" resultType="com.cetc.bean.Teacher">
        select * from t_teacher where

        <if test="name != null &amp;&amp; !name.equals('')">
            name = #{name} and
        </if>

        <if test="className != null and className != &quot;&quot;">
            class_name = #{className} and
        </if>

        <if test="address != null and address != ''">
            address like #{address}
        </if>
    </select>
</mapper>

TeacherMapper.java:

package com.cetc.dao;

import com.cetc.bean.Teacher;

import java.util.List;

public interface TeacherMapper {
    List<Teacher> getTeacherListByCondition(Teacher teacher);
}

Test:

@Test
    public void testMybatisSelect(){
        //获取与数据库的一次会话
        SqlSession sqlSession = sqlSessionFactory.openSession();
        try {
            Teacher teacher = new Teacher();
            teacher.setName("张三");
            teacher.setClassName("语文");
            teacher.setAddress("%北%");
            TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);
            List<Teacher> teachers = teacherMapper.getTeacherListByCondition(teacher);
            if (teachers != null && teachers.size() > 0 ){
                for (Teacher t : teachers) {
                    System.out.println(t);
                }
            }
        }finally {
            sqlSession.close();
        }
    }

测试结果:

DEBUG 02-17 21:46:52,623 ==>  Preparing: select * from t_teacher where name = ? and class_name = ? and address like ?   (BaseJdbcLogger.java:145) 
DEBUG 02-17 21:46:52,815 ==> Parameters: 张三(String), 语文(String), %%(String)  (BaseJdbcLogger.java:145) 
DEBUG 02-17 21:46:52,898 <==      Total: 1  (BaseJdbcLogger.java:145) 
Teacher(id=1, name=张三, className=语文, birth=Fri Jan 01 00:00:00 CST 2021, address=北京市)

二、where、choose、when

  1. 上面的示例存在一个问题,就是当所有的条件都为空时,SQL语句会多出来一个where,造成了SQL语法错误mtabtis提供了where标签来解决此问题,它可以将条件中多出来的and自动去除,但条件是多出的and必须在每个条件sql前面
  2. 上面的示例也无法实现非if时执行哪一句SQL,when...otherwise...解决了这个问题,它可以otherWise可以指定非if情况下按照谁执行。

请参照下面的示例

mapper.xml:

<select id="getTeacherListByCondition" resultType="com.cetc.bean.Teacher">
        select * from t_teacher
        <where>
            <choose>
                <!--当仅剩下这一个条件时,where标签能将这个多出来的and自动去除-->
                <when test="name != null &amp;&amp; !name.equals('')">
                    and name = #{name}
                </when>
                <when test="className != null and className != &quot;&quot;">
                    and class_name = #{className}
                </when>

                <!--以上条件都不符合时,按照地址来查-->
                <otherwise>
                    address like #{address}
                </otherwise>
            </choose>
        </where>
 </select>

Test.java:

@Test
    public void testMybatisSelect01(){
        //获取与数据库的一次会话
        SqlSession sqlSession = sqlSessionFactory.openSession();
        try {
            Teacher teacher = new Teacher();
            teacher.setName("张三");
            teacher.setClassName("语文");
            teacher.setAddress("%北%");
            TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);
            List<Teacher> teachers = teacherMapper.getTeacherListByCondition(teacher);
            if (teachers != null && teachers.size() > 0 ){
                for (Teacher t : teachers) {
                    System.out.println(t);
                }
            }
        }finally {
            sqlSession.close();
        }
    }

测试结果:

DEBUG 02-17 22:13:52,177 ==>  Preparing: select * from t_teacher WHERE name = ?   (BaseJdbcLogger.java:145) 
DEBUG 02-17 22:13:52,588 ==> Parameters: 张三(String)  (BaseJdbcLogger.java:145) 
DEBUG 02-17 22:13:52,662 <==      Total: 1  (BaseJdbcLogger.java:145) 
Teacher(id=1, name=张三, className=语文, birth=Fri Jan 01 00:00:00 CST 2021, address=北京市)

三、trim

针对以where标签去除不了SQL后面多余的and,trim标签很好的解决了这种问题。

TeacherMapper.xml:

<select id="getTeacherListByTrimCondition" resultType="com.cetc.bean.Teacher">
        select * from t_teacher

        <!--
        prefix="":指定在sql语句的前面加上哪个前缀
        prefixOverrides="":去除掉前缀以指定内容的值
        suffix="":指定在sql语句的前面加上哪个后缀
        suffixOverrides="":去除掉后缀以指定内容开头的值
        -->
        <trim prefix="where" prefixOverrides="and | or"  suffixOverrides="and | or" suffix=";">
            <if test="name != null &amp;&amp; !name.equals('')">
                name = #{name} and
            </if>

            <if test="className != null and className != &quot;&quot;">
                class_name = #{className} and
            </if>

            <if test="address != null and address != ''">
                address like #{address}
            </if>
        </trim>
    </select>

TeacherMapper.java:

List<Teacher> getTeacherListByTrimCondition(Teacher teacher);

Test.java:

@Test
    public void testMybatisSelect01(){
        //获取与数据库的一次会话
        SqlSession sqlSession = sqlSessionFactory.openSession();
        try {
            Teacher teacher = new Teacher();
            teacher.setName("张三");
            teacher.setAddress("%北%");
            TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);
            List<Teacher> teachers = teacherMapper.getTeacherListByTrimCondition(teacher);
            if (teachers != null && teachers.size() > 0 ){
                for (Teacher t : teachers) {
                    System.out.println(t);
                }
            }
        }finally {
            sqlSession.close();
        }
    }

测试结果:

DEBUG 02-17 22:32:08,223 ==>  Preparing: select * from t_teacher where name = ? and address like ? ;   (BaseJdbcLogger.java:145) 
DEBUG 02-17 22:32:08,454 ==> Parameters: 张三(String), %%(String)  (BaseJdbcLogger.java:145) 
DEBUG 02-17 22:32:08,539 <==      Total: 1  (BaseJdbcLogger.java:145) 
Teacher(id=1, name=张三, className=语文, birth=Fri Jan 01 00:00:00 CST 2021, address=北京市)

四、set

针对于update,我们可以动态的根据条件改变某个字段。

TeacherMapper.xml:

<update id="updateTeacherById">
        update t_teacher

        <set>
            <if test="name != null &amp;&amp; !name.equals('')">
                name = #{name} ,
            </if>

            <if test="className != null and className != &quot;&quot;">
                class_name = #{className} ,
            </if>
        </set>

        <where>
            id = #{id}
        </where>
    </update>

TeacherMapper.java:

int updateTeacherById(Teacher teacher);

Test.java:

@Test
    public void testMybatisSelect02(){
        //获取与数据库的一次会话
        SqlSession sqlSession = sqlSessionFactory.openSession();
        try {
            Teacher teacher = new Teacher();
            teacher.setId(1);
            teacher.setName("张三Test");
            teacher.setClassName("生物学");
            TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);
            int i = teacherMapper.updateTeacherById(teacher);
            System.out.println(i > 0 ? "修改成功" : "修改失败");

        }finally {
            sqlSession.close();
        }
    }

测试结果:

DEBUG 02-17 22:51:43,021 ==>  Preparing: update t_teacher SET name = ? , class_name = ? WHERE id = ?   (BaseJdbcLogger.java:145) 
DEBUG 02-17 22:51:43,109 ==> Parameters: 张三Test(String), 生物学(String), 1(Integer)  (BaseJdbcLogger.java:145) 
DEBUG 02-17 22:51:43,111 <==    Updates: 1  (BaseJdbcLogger.java:145) 
修改成功

五、foreach

动态 SQL 的另外一个常用的必要操作是需要对一个集合进行遍历,通常是在构建IN语句的时候。

  1. 当迭代列表、集合等可迭代对象或者数组

    index是当前迭代的次数,item的值是本次迭代获取的元素

  2. 当使用字典(或者Map.Entry对象的集合)时

    index是键,index是值

  1. 当迭代列表、集合等可迭代对象或者数组

TeacherMapper.xml

<select id="getTeacherListByIds" resultType="com.cetc.bean.Teacher">
        select * from t_teacher
        <where>
            id in
        </where>
        <foreach collection="ids" item="item" open="(" separator="," close=")">
            #{item}
        </foreach>
    </select>

TeacherMapper.java:

    List<Teacher> getTeacherListByIds(@Param("ids") List<Integer> ids);

Test.java:

 @Test
    public void testMybatisSelect03(){
        //获取与数据库的一次会话
        SqlSession sqlSession = sqlSessionFactory.openSession();
        try {
            TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);
            List<Integer> ids = Arrays.asList(1, 2, 3, 4, 5);
            List<Teacher> teachers = teacherMapper.getTeacherListByIds(ids);
            if (teachers != null && teachers.size() > 0){
                for (Teacher teacher : teachers) {
                    System.out.println(teacher);
                }
            }
        }finally {
            sqlSession.close();
        }
    }

测试结果:

DEBUG 02-17 23:13:39,502 ==>  Preparing: select * from t_teacher WHERE id in ( ? , ? , ? , ? , ? )   (BaseJdbcLogger.java:145) 
DEBUG 02-17 23:13:39,571 ==> Parameters: 1(Integer), 2(Integer), 3(Integer), 4(Integer), 5(Integer)  (BaseJdbcLogger.java:145) 
DEBUG 02-17 23:13:39,598 <==      Total: 3  (BaseJdbcLogger.java:145) 
Teacher(id=1, name=张三Test, className=生物学, birth=Fri Jan 01 00:00:00 CST 2021, address=北京市)
Teacher(id=2, name=李四, className=数学, birth=Wed Feb 17 00:00:00 CST 2021, address=上海市)
Teacher(id=3, name=王五, className=英语, birth=Wed Feb 17 00:00:00 CST 2021, address=河南省)
  1. 当使用字典(或者Map.Entry对象的集合)时:

TeacherMapper.xml:

<update id="batchUpdateByMapParam">
        update t_teacher
        <set>
            <foreach collection="map" index="key" item="value" >
                ${key} = #{value},
            </foreach>
        </set>

        <where>
            id in
        </where>
        <foreach collection="ids"  item="value" open="(" separator="," close=")">
            #{value}
        </foreach>
    </update>

TeacherMapper.java:

    int batchUpdateByMapParam(@Param("map") Map<String , Object> map , @Param("ids") List<Integer> ids);

Test.java:

@Test
    public void testMybatisSelect04(){
        //获取与数据库的一次会话
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        try {
            TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);
            Map<String , Object> map = new HashMap<>();
            map.put("name" , "test01");
            map.put("class_name" , "地理");
            map.put("address" , "郑州市");

            List<Integer> ids = Arrays.asList(1, 2);
            int i = teacherMapper.batchUpdateByMapParam(map, ids);
            System.out.println(i > 0 ? "修改成功" : "修改失败");
        }finally {
            sqlSession.close();
        }
    }

六、bind

bind元素可以从 OGNL 表达式中创建一个变量并将其绑定到上下文

TeacherMapper.xml:

<select id="getTeacherListByCondition" resultType="com.cetc.bean.Teacher">
        <bind name="myAddress" value="'%'+address+'%'"/>
        select * from t_teacher
        <where>
            <choose>
                <!--当仅剩下这一个条件时,where标签能将这个多出来的and自动去除-->
                <when test="name != null &amp;&amp; !name.equals('')">
                    and name = #{name}
                </when>
                <when test="className != null and className != &quot;&quot;">
                    and class_name = #{className}
                </when>

                <!--以上条件都不符合时,按照地址来查-->
                <otherwise>
                    address like #{myAddress}
                </otherwise>
            </choose>
        </where>
    </select>

TeacherMapper.java:

    List<Teacher> getTeacherListByCondition(Teacher teacher);

Test.java:

@Test
    public void testMybatisSelect(){
        //获取与数据库的一次会话
        SqlSession sqlSession = sqlSessionFactory.openSession();
        try {
            Teacher teacher = new Teacher();
            teacher.setAddress("郑");
            TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);
            List<Teacher> teachers = teacherMapper.getTeacherListByCondition(teacher);
            if (teachers != null && teachers.size() > 0 ){
                for (Teacher t : teachers) {
                    System.out.println(t);
                }
            }
        }finally {
            sqlSession.close();
        }
    }

测试结果:

DEBUG 02-18 10:41:39,988 ==>  Preparing: select * from t_teacher WHERE address like ?   (BaseJdbcLogger.java:145) 
DEBUG 02-18 10:41:40,075 ==> Parameters: %%(String)  (BaseJdbcLogger.java:145) 
DEBUG 02-18 10:41:40,114 <==      Total: 2  (BaseJdbcLogger.java:145) 
Teacher(id=1, name=test01, className=地理, birth=Fri Jan 01 00:00:00 CST 2021, address=郑州市)
Teacher(id=2, name=test01, className=地理, birth=Wed Feb 17 00:00:00 CST 2021, address=郑州市)

七、sql

抽取可重用的sql片段

接着上个例子进行扩展,如下:

TeacherMapper.xml:

<!--抽取可重用的sql-->
<sql id="commonSql">
        select * from t_teacher
</sql>

    <select id="getTeacherListByCondition" resultType="com.cetc.bean.Teacher">
        <bind name="myAddress" value="'%'+address+'%'"/>
        <!--引用重用的sql片段-->
            <include refid="commonSql"/>
        <where>
            <choose>
                <!--当仅剩下这一个条件时,where标签能将这个多出来的and自动去除-->
                <when test="name != null &amp;&amp; !name.equals('')">
                    and name = #{name}
                </when>
                <when test="className != null and className != &quot;&quot;">
                    and class_name = #{className}
                </when>

                <!--以上条件都不符合时,按照地址来查-->
                <otherwise>
                    address like #{myAddress}
                </otherwise>
            </choose>
        </where>
    </select>

八、OGNL表达式

OGNL( Object Graph Navigation Language )对象图导航语言,这是一种强大的
表达式语言,通过它可以非常方便的来操作对象属性。 类似于我们的EL,SpEL等

在这里插入图片描述

九、多数据库支持

若在 mybatis 配置文件中配置了 databaseIdProvider , 则可以使用 “_databaseId”变量,这样就可以根据不同的数据库厂商构建特定的语句
在这里插入图片描述
在这里插入图片描述
扩展:
mybatis还提供两个参数可用于判断:

  1. _parameter:可以代表传入的参数,可以代表传入的单个参数或者多个参数,当要判断所有的参数不为空时,可以使用下面的语句判断:
<if test="_parameter != null">
.......
</if>
  1. 另一个就是_databaseId,作用就像上面多数据库支持时使用那样
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值