Java SSM框架之MyBatis3（八）MyBatis之动态SQL

前言：

　　mybatis框架中最具特色的便是sql语句中的自定义，而动态sql的使用又使整个框架更加灵活。

创建User表

/*Table structure for table `user` */

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (

  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

  `name` varchar(20) NOT NULL,

  `username` varchar(20) NOT NULL,

  `age` int(3) NOT NULL,

  `phone` varchar(11) NOT NULL,

  `email` varchar(50) NOT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

/*Data for the table `user` */

insert  into `user`(`id`,`name`,`username`,`age`,`phone`,`email`) values (1,'张三','zs',18,'','4567899@qq.com'),(2,'李四','ls',19,'','567657642@qq.com'),(3,'王五','ww',20,'','2323248@qq.com');

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;

/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

if标签

User实体

package cn.cnki.ref.pojo;

public class User {

    private int id;

    private String name;

    private String username;

    private int age;

    private String phone;

    private String email;

    //无参构造函数必须有，ORM框架调用的就是无参构造函数

    public User() {

    }

    public User(int id, String name, String username, int age, String phone, String email) {

        super();

        this.id = id;

        this.name = name;

        this.username = username;

        this.age = age;

        this.phone = phone;

        this.email = email;

    }

    public int getId() {

        return id;

    }

    public void setId(int id) {

        this.id = id;

    }

    public String getName() {

        return name;

    }

    public void setName(String name) {

        this.name = name;

    }

    public String getUsername() {

        return username;

    }

    public void setUsername(String username) {

        this.username = username;

    }

    public int getAge() {

        return age;

    }

    public void setAge(int age) {

        this.age = age;

    }

    public String getPhone() {

        return phone;

    }

    public void setPhone(String phone) {

        this.phone = phone;

    }

    public String getEmail() {

        return email;

    }

    public void setEmail(String email) {

        this.email = email;

    }

    @Override

    public String toString() {

        return "User [id=" + id + ", name=" + name + ", username=" + username + ", age=" + age + ", phone=" + phone

                + ", email=" + email + "]";

    }

}

UserMapper

package cn.cnki.ref.mapper;

import cn.cnki.ref.pojo.User;

import java.util.List;

public interface UserMapper {

    public List<User> getUser(User user);

}

UserMapper.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关系映射  -->

<mapper namespace="cn.cnki.ref.mapper.UserMapper">

    <select id="getUser" resultType="cn.cnki.ref.pojo.User">

        select * from user where 1=1

        <if test="id != null">and id=#{id}</if>

        <if test="age != null">and age=#{age}</if>

        <if test="name != null">and name=#{name}</if>

    </select>

</mapper>

UserController

package cn.cnki.ref.controller;

import cn.cnki.ref.mapper.UserMapper;

import cn.cnki.ref.pojo.User;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.web.bind.annotation.GetMapping;

import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController

public class UserController {

    @Autowired

    private UserMapper UserMapper;

    @GetMapping("/getUser")

    public List<User> getUser() {

        User user = new User(1, "张三", null, 18, null, null);

        List<User> users = UserMapper.getUser(user);

        System.out.println(users);

        return users;

    }

}

测试

http://localhost:8080/getUser

choose (when, otherwise)标签

　　choose标签是按顺序判断其内部when标签中的test条件出否成立，如果有一个成立，则 choose 结束。当 choose 中所有 when 的条件都不满则时，则执行 otherwise 中的sql。类似于Java 的 switch 语句，choose 为 switch，when 为 case，otherwise 则为 default。

　　例如下面例子，同样把所有可以限制的条件都写上，方面使用。choose会从上到下选择一个when标签的test为true的sql执行。安全考虑，我们使用where将choose包起来，放置关键字多于错误。

<!--  choose(判断参数) - 按顺序将实体类 User 第一个不为空的属性作为：where条件 -->

<select id="getUserList_choose" resultMap="resultMap_user" parameterType="com.yiibai.pojo.User">

    SELECT *

      FROM User u

    <where>

        <choose>

            <when test="username !=null ">

                u.username LIKE CONCAT(CONCAT('%', #{username, jdbcType=VARCHAR}),'%')

            </when >

            <when test="sex != null and sex != '' ">

                AND u.sex = #{sex, jdbcType=INTEGER}

            </when >

            <when test="birthday != null ">

                AND u.birthday = #{birthday, jdbcType=DATE}

            </when >

            <otherwise>

            </otherwise>

        </choose>

    </where>

</select>

where标签

<select id="getUser" resultType="cn.cnki.ref.pojo.User">

        select * from user

        <where>

            <if test="id != null">and id=#{id} </if>

            <if test="age != null">and age=#{age} </if>

            <if test="name != null">and name=#{name}</if>

        </where>

    </select>

输出sql语句

trim标签

 <select id="getUser" resultType="cn.cnki.ref.pojo.User">

        select * from user

        <trim prefix="where" suffixOverrides="and">

            <if test="id != null">id=#{id} and</if>

            <if test="age != null">age=#{age} and</if>

            <if test="name != null">name=#{name} and</if>

        </trim>

    </select>

trim标签下的四个属性：

　　prefix：在标签开始添加上该字符串

　　suffixOverrides：在标签末尾去除上该字符串

　　suffix：在标签末尾添加上该字符串

　　prefixOverrides：在标签开始去除上该字符串

set标签

　　用在update语句中，如果字段参数不为null，则修改此参数

UserMapper

public interface UserMapper {

    public int updateUserById(User user);

}

UserMapper.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关系映射  -->

<mapper namespace="cn.cnki.ref.mapper.UserMapper">

    <update id="updateUserById">

        update user

        <set>

            <if test="name != null">name=#{name},</if>

            <if test="age != null">age=#{age},</if>

            <if test="username != null">username=#{username},</if>

            <if test="email != null">email=#{email},</if>

            <if test="phone != null">phone=#{phone}</if>

        </set>

        <where>

            id=#{id}

        </where>

    </update>

</mapper>

UserController

package cn.cnki.ref.controller;

import cn.cnki.ref.mapper.UserMapper;

import cn.cnki.ref.pojo.User;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.web.bind.annotation.GetMapping;

import org.springframework.web.bind.annotation.RestController;

import java.util.List;

@RestController

public class UserController {

    @Autowired

    private UserMapper UserMapper;

    @GetMapping("/updateUserById")

    public int updateUserById() {

        User user = new User(1, null, null, 21, "12545564454", "14548445@qq.com");

        return  UserMapper.updateUserById(user);

    }

}

测试结果

switch\when标签

1描述需求：

　　满足id！=null查询id，

　　否则，看满足age否，

　　接着，看name是否满足，

　　最后，按age>10查询

2.改mapper映射

<select id="getUser" resultType="cn.cnki.ref.pojo.User">

        select * from user where

        <choose>

            <when test="id != null">id=#{id} </when>

            <when test="age != null">age=#{age}</when>

            <when test="name != null">name=#{name} </when>

            <otherwise>age>10</otherwise>

        </choose>

    </select>

3.测试结果

foreach标签

1.需求描述：

　　查出多个id的user

2.添加mapper接口

 public List<User> listById(List<Integer> ids);

3添加mapper映射

<select id="listById" resultType="cn.cnki.ref.pojo.User" parameterType="list">

        select * from user where id in

        <foreach collection="list" item="id" separator="," open="(" close=")">

            #{id}

        </foreach>

    </select>

4.测试

 @GetMapping("/listById")

    public List<User> listById() {

        List<User> users = UserMapper.listById(Arrays.asList(1,2));

        System.out.println(users);

        return users;

    }

5.foreach标签下的所有属性：

　　collection：获取的集合名，如果是list集合，springboot会把它的key值默认封装为list

　　item：遍历的单个属性值

　　separator：拼接隔离的字符串

　　open：在循环的开始拼接的字符串

　　close：在循环的结束拼接的字符串

　　index：索引，在map中作为key

bind

bind标签可以使用OGNL表达式创建一个变量并将其绑定到上下文中。

使用concat函数连接字符串，在MySQL中，这个函数支持多个参数，但是在Oracle中只支持两个参数。由于不同数据库之间的语法差异，如果更换了数据库，有些SQL语句可能就需要重写。针对这种情况，可以使用bind标签来避免由于更换数据库带来的一些麻烦。

巴特西