MyBatis(5)-------动态SQL

写个堆排

已于 2023-11-01 18:22:21 修改

阅读量1.7k

点赞数

文章标签： mybatis sql java

于 2022-11-11 10:10:12 首次发布

本文链接：https://blog.csdn.net/weixin_61518137/article/details/127801154

版权

前面博客的参数都是必传参数，非必传参数一般是想传就传，不想传就可以不传，根据用户传递参数的不同从而生成不同的SQL语句

1)之前咱们的写的MyBatis代码中的参数都是必填参数，参数要是不进行传递就会报错，GetUserByID(int userID)，这个userID要是不进行传递，那么代码就会出现问题

2)要是不进行传递，那么就是查找所有用户，难道我们要维护两份代码吗？

Select * from user和select * from user where UserID=#{userID}

此时想要实现的效果是前端如果传递userID就成功地查询出用户对应的userID的记录

如果不进行传递userID就直接查询出所有的用户记录

1)应用场景:比如说我想要服兵役，肯定要在注册的时候去填写一些个人信息，在这里面姓名，年龄，家庭住址，手机号是必需进行填写的，但是比如说一些备注，邮箱即可以写也可以不写，那么当用户将不确定的字段进行传入的时候，那么这个时候我们的后台程序是如何进行处理的呢？

2)我们针对刚才的User表进行插入操作，只不过在这一次，密码是必填字段，咱们的班级ID和用户名都是非必填字段，填不添加都可以，也就是班级ID和用户名如果你要是进行传递的话，就插入对应的你传入的值，如果你没有进行指定，那么我们就会插入一个null，*属于必填项，非*属于非必填项

1)使用if标签:if标签不可以放在最后面，况且里面的属性要加分号，这是XML文件里面的代码:

if标签就是用来进行动态判断这个标签中的内容有没有值，如果if语句中的判断条件不满足，那么会隐藏if标签里面的内容

第一种写法:

<if test="方法传递过来的参数!=null">数据库中的列名，</if>

第二种写法是在values括号里面的:

<if test="方法中传递过来的参数!=null>#{方法中传递过来的参数}</if>

所以我们对象的属性名一定要等于字段名

1)我们的UserController里面的代码:

@Controller
public class UserController {
    @Autowired
    UserService userService;
    @RequestMapping("InsertAll")
    @ResponseBody
    public int InsertAll(User user)
    {
        if(user==null||user.equals(""))
        {
            return -1;
        }
        return userService.InsertAll(user);
    }
}

2)我们写的UserService里面的代码和接口中的代码:

@Service
public class UserService {
        @Autowired
        private UserMapper mapper;
    public int InsertAll(User user) {
        return mapper.InsertAll(user);
    }
}

3)上面的代码和以前写的静态SQL的插入操作的代码都是一样的，所以我们最终的区别就是XML文件里面的数据库SQL代码:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD com.example.demo.Mapper1 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.demo.Mapper.UserMapper">
    <insert id="InsertAll" useGeneratedKeys="true" keyColumn="userID" keyProperty="userID">
        insert into user(
        <if test="classID!=null">classID,</if>
        <if test="username!=null">username,</if>
        password)
        values(
        <if test="classID!=null">#{classID},</if>
        <if test="username!=null">#{username},</if>
        #{password});
    </insert>
</mapper>

@SpringBootTest
class UserControllerTest {
    @Autowired
    UserController userController;
    @Test
    void insertAll() {
        User user1=new User();
        user1.setUsername("喜羊羊");
        user1.setPassword("杨村密码");
        user1.setClassID(1);
        User user2=new User();
        user2.setPassword("博客系统");
       int len1= userController.InsertAll(user1);//传递的是一个具体的对象
       int len2= userController.InsertAll(user2);
        System.out.println(len1);
        System.out.println(len2);
    }
}

插入null和动态SQL有什么区别呢？进一步理解一下，约定classID是必传项

最终生成的SQL是:

insert into user(userID,username,password,classID) values(1,null,null,10)

  <insert id="insert" useGeneratedKeys="true" keyProperty="userID" keyColumn="userID">
      insert into user(
        <if test="userID!=null">
            userID,
        </if>
        <if test="username!=null">
            username,
        </if>
        <if test="password!=null">
            password,
        </if>
            classID
       )values(
          <if test="userID!=null">
              #{userID},
          </if>
          <if test="username!=null">
              #{username},
          </if>
          <if test="password!=null">
              #{password},
          </if>
             #{classID});
    </insert>

2)使用trim标签实现插入用户功能:

适用于所有参数都是必传项，就是帮助程序员去除最前面的符号和最后面的符号，就是为了去除前后多余的字符；

之前写的if标签，不能放在最后边，因为if标签里面的字段最后出现了'，'，SQL语法就会出现报错，如果说有多个字段是非必选项，我们就要结合trim标签结合于if标签来进行使用了；

2.1)还有的解决场景就是说如果说我们的如果说我们表中的字段如果全部是非必填项，我们就要使用这个标签了，所有的动态都是动态生成的，如果此时还是用if标签就会出现问题；可能极端情况下最后在values前面会多一个"，"；

2.2)像下面这个代码:
insert into user (
 <if test="username!=null"> username,</if>
 <if test="password!=null"> password,</if>
 <if test="sex!=null">sex,</if>
 )values(
<if test="username!=null"> #{username},</if>
 <if test="password!=null">#{password},</if>
 <if test="sex!=null">#{sex},</if>
)
在极端情况下面，只进行传递了username，那么很明显按照上面的动态SQL生成的SQL语句应该是:

insert into user (username，)values ("A",)那么我们是不是应该把这个username去掉呢？这就是当所有的语句都是非必填项的时候，出现的问题

1)prefix:表示整个语句块，以prefix的值为前缀

2)suffix:表示整个语句块，以suffix的值作为后缀

3)prefixOverrides:表示整个语句块要去除掉的前缀，去掉前面符合要求的字符，如果最终的语句块包含了我们指定的字符，那么直接进行去掉，没有就算了

4)suffixOverrides:表示整个语句块要除掉的后缀，通常的应用场景就是去掉最后一个if标签里面的"，"；是我们的SQL语句的语法不会出现问题，如果最后面有指定的字符，那么我们最终就会删除，如果没有我们指定的字符"，"那么我们是不会做任何事情的;下面是xml文件里面的代码:

   <insert id="GetAll">
       insert into user
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="userID!=null">
                  userID,
            </if>
            <if test="classID!=null">
                classID,
            </if>
            <if test="username!=null">
                username,
            </if>
            <if test="password!=null">
                password,
            </if>
        </trim>

values
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="userID!=null">
                #{userID},
            </if>
            <if test="classID!=null">
                #{classID},
            </if>
            <if test="username!=null">
                #{username},
            </if>
            <if test="password!=null">
                #{password},
            </if>
        </trim>
    </insert>

我们再来写一段代码并进行测试一下:

<insert id="InsertAll" useGeneratedKeys="true" keyColumn="userID" keyProperty="userID">
      insert into user
        <trim prefix="(" suffix=")" suffixOverrides=",">
              <if test="userID!=null">userID,</if>
              <if test="classID!=null">classID,</if>
              <if test="username!=null">username,</if>
              <if test="password!=null">password,</if>
        </trim>
        <trim prefix="values(" suffix=")" suffixOverrides=",">
            <if test="userID!=null">#{userID},</if>
            <if test="classID!=null">#{classID},</if>
            <if test="username!=null">#{username},</if>
            <if test="password!=null">#{password},</if>
        </trim>

    </insert>

下面是我们写的单元测试的代码进行测试:


@SpringBootTest
class UserControllerTest {
    @Autowired
    UserController userController;
    @Test
    void insertAll() {
        User user1=new User();
        user1.setUsername("唐三");
        user1.setPassword("缠绕");
        user1.setClassID(1);
        user1.setUserID(666);
        User user2=new User();
        user2.setPassword("小舞");
        user2.setUsername("八段摔");
        user2.setUserID(667);
       int len1= userController.InsertAll(user1);
       int len2= userController.InsertAll(user2);
        System.out.println(len1);
        System.out.println(len2);
    }
}

3)使用Where标签来实现查询功能:实现查询的where替换掉；

默认功能:where可以默认去掉最前面的and，会自动在前面加上where字段

1)Where标签会自动生成where，如果有查询条件会自动生成where，如果没有查询条件就会忽略Where

2)如果有查询条件，会判断第一个查询条件前面有没有and，如果有那么会直接进行删除，所以写代码的时候要把and拼接在最前面

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD com.example.demo.Mapper1 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.demo.Mapper.MyBatisMapper">
    <resultMap id="BaseMap" type="com.example.demo.DatasourceClass.UserDemo">
        <result property="userID" column="userID"></result>
        <result property="classID" column="classID"></result>
        <result property="username" column="username"></result>
        <result property="password" column="password"></result>
    </resultMap>
    <insert id="insertUserDemo">
这是添加语句
       insert into UserDemo
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="userID!=null">
                  userID,
            </if>
            <if test="classID!=null">
                classID,
            </if>
            <if test="username!=null">
                username,
            </if>
            <if test="password!=null">
                password,
            </if>
        </trim>values
        <trim prefix="(" suffix=")" suffixOverrides=",">
            <if test="userID!=null">
                #{userID},
            </if>
            <if test="classID!=null">
                #{classID},
            </if>
            <if test="username!=null">
                #{username},
            </if>
            <if test="password!=null">
                #{password},
            </if>
        </trim>
    </insert>
下面是查询语句
    <select id="SelectAll" resultMap="BaseMap">
        select userID,classID,username,password from UserDemo
        <where>
            <if test="username!=null">
                and username=#{username}
            </if>
            <if test="password!=null">
                and password=#{password}
            </if>
        </where>
    </select>
</mapper>                   
我们在浏览器上面输入:localhost:8080/Select?username=A
然后浏览器返回的结果是:
[{"userID":1,"classID":1,"username":"A","password":"12503487"}]
虽然我们在这里面username和password是非必传参数
但是当我们的username和password都不进行传递的时候,我们就会发现查询语句就变成了select * from user;
这是的where标签会自动去掉

以上的where标签我们也是可以使用<trim prefix="where" prefixOverrides="and">来进行替换，如果有前缀那么前缀会自动加上where，还会自动去掉最前面的if标签的and

1)and标签要放在最前面where语句不没有自动去除最后一个and的能力的，比如说现在写了一句这样的代码
<select id="SelectAll" resultType="com.example.demo.User>
select * from user
<where>
<if test="name!=null">
 username=#{name} and
</if>
<if test="password!=null">
 password=#{password} and
</if>
</where>
</select>
1)注意这么写是不行的，从上面的语句中可以看到，name是咱们实体类中的属性，username是数据库中字段的属性

2)执行代码，最后面的and不会被去掉

四)使用Set标签来进行更新用户数据(默认是有set的)

1)Set标签可以自动去掉最后边的分号，自动加上set关键字

2)set标签会自动给我们添加set关键字，注意我们的最外面还是要加上update标签；

<update id="updateUserDemo">
        update UserDemo
        <set>
            <if test="username!=null">
                username=#{username},
            </if>
            <if test="password!=null">
                password=#{password},
            </if>
        </set>
        where userID=#{userID}//这里面的userID是我们在前端页面必须传递的参数
    </update>
xml文件里面的代码

1)我们直接在浏览器上面输入：http://localhost:8080/update?userID=1&username=B

指定userID和username就可以进行修改数据库里面的值了

2)set标签也是可以用<trim prefix="set"， suffixOverrides=",">

3)比如说基于Spring，SpringBoot，SpringMVC搭建的个人小站和项目的源码地址，下面的Set标签已经把最后一个逗号去掉了，并且会自动加上Set关键字

上面这么写也是可以的

5)foreach标签，主要是批量删除

对集合进行遍历的时候可以进行使用这个标签

<foreach>标签里面有下面这几个属性；

collection:绑定方法参数里面的集合，也是相当于是方法中的参数

item:遍历集合时的每一个对象，是collection集合中的每一个元素，可以自定义名字

open: 语句块开头的字符串，前缀

close:语句快结束时的字符串后缀

separator:每一次遍历之间间隔的字符串

进行删除操作:比如说我想要删除userID为3，4，5的UserDemo对象，就可以写这样的一个SQL语句:
delete from userdemo where userID in(3,4,5);
前端给的是一个集合
后端的代码演示:

1)首先我们为了方便，我们直接在UserMapper接口里面写一个抽象方法进行验证:
@Mapper
public interface UserMapper { 
    int Delete(List<Integer> list);
}
2)在我们的userMapper.xml里面进行指定删除的SQL语句:
<delete id="Delete">
        delete from user where userID in

        <foreach collection="list" item="data" open="(" close=")" separator=",">
            #{data}
        </foreach>
在这里面我们要说明一下这几个字段，

collection:在这里面的参数表示的就是咱们的要删除的userID的一个集合，绑定方法参数的集合，比如list，map，或者数组对象

item:这里面表示的是咱们设置的别名，这里的Num表示的是咱们list集合中的每一个元素

for(int item:arr1);

open:表示接下来查询语句的前缀

close:表示接下来查询语句的后缀

separator:表式list中每一个元素之间的分隔符

3)使用单元测试来进行测试一下:
@SpringBootTest
class UserMapperTest {
    @Autowired
    UserMapper userMapper;
    @Test
    void delete() {
        List<Integer> list=new ArrayList<>();
        list.add(666);
        list.add(667);
        userMapper.Delete(list);
    }
}