Java MyBatis3（8）动态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,'15010998046','4567899@qq.com'),(2,'李四','ls',19,'15019087600','567657642@qq.com'),(3,'王五','ww',20,'15010898065','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);
    }
}

测试结果

switchwhen标签

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标签来避免由于更换数据库带来的一些麻烦。