Mybatis动态SQL语句学习笔记

最新推荐文章于 2024-09-01 15:12:06 发布

帅气的浩浩

最新推荐文章于 2024-09-01 15:12:06 发布

阅读量298

点赞数

分类专栏： mybatis 文章标签： mybatis

本文链接：https://blog.csdn.net/weixin_43693472/article/details/108117539

版权

mybatis 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

1 动态sql语句查询拼接if/where

应用场景：在日常生活中，我们在搜索物品的时候，会添加不同的条件去查询物品，那么就需要根据这些条件自动去拼接动态的生成SQL语句。

条件在变化的过程中，如何实现动态sql语句查询。

需求：用户可能按照登录名/用户名/地址动态查询用户数据，甚至不带条件查询全部用户数据。

因为拼接的代码只是UserMapper.xml里面修改，所以我们先把前面其他文件中相同的代码在前面先写好。

public class MybatisDemo {
    @Test
    public void selectById() {
        // 1.得到数据库会话对象
        SqlSession sqlSession = MybatisSqlSessionFactoryUtils.getSqlSession();
        // 2.得到数据访问层接口的代理对象。默认选择使用接口的映射文件作为代理对象！
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User u = new User();
        u.setLoginName("t");
        u.setUserName("唐");
        u.setAddress("大");

        // List<User> users = userMapper.selectByDynamic(u);
        List<User> users = userMapper.selectByDynamic02(u);
        System.out.println(users);

        MybatisSqlSessionFactoryUtils.close(sqlSession);
    }
   
}

//UserMapper.java
public interface UserMapper {
    List<User> selectByDynamic(User u);
    List<User> selectByDynamic02(User u);
}

<!--UserMapper.xml-->
<mapper namespace="com.itheima.dao.UserMapper">
    
    <!-- 自定义结果集  -->
    <resultMap id="userResultMap" type="User">
        <!-- column数据库字段列名 property实体类字段名称 -->
        <id column="id" property="id"></id>
        <!-- 配置关联名称不一样字段的映射 -->
        <result column="create_date" property="createDate"></result>
    </resultMap>
</mapper>

1.1 方式一恒等式解决动态sql拼接问题

使用if标签判断查询条件不能为空以及不能为空字符（空字符表示用户可能在搜索框没有输入）。

中间&+amp;&+amp;表示&&（xml规定）

如果第一个if语句判断为false，那么就不会执行下面的语句，那么就会导致拼接失败，变成了

 select * from tb_user where ？

所以我们可以使用让一个恒等式使得不管怎么用，where后面拼接的值永远为真。

<!--
     List<User> selectByDynamic(User u);
     1、动态sql语句：条件可能是 用户名 登录名 地址 甚至么有条件！
     方式一：恒等式解决动态sql拼接问题（拓展方式）
    -->
<select id="selectByDynamic" resultMap="userResultMap">
    select * from tb_user where 1=1
    <if test="loginName != null &amp;&amp; loginName != ''">
        and loginName like CONCAT('%' , #{loginName} , '%')
    </if>
    <if test="userName != null &amp;&amp; userName != ''">
        and userName like CONCAT('%' , #{userName} , '%')
    </if>
    <if test="address != null &amp;&amp; address != ''">
        and address like CONCAT('%' , #{address} , '%')
    </if>
</select>

1.2 方式二：使用if结合where标签解决动态sql拼接问题。

那么在mybatis中还有另外一种方法可以解决这个问题，就是使用if结合where标签解决动态sql拼接问题。

注意：第一个条件的and可以加可以不加！where标签会自动识别去掉不合理的sql语句拼接。

<select id="selectByDynamic02" resultMap="userResultMap">
    select * from tb_user
    <where>
        <if test="loginName != null &amp;&amp; loginName != ''">
             loginName like CONCAT('%' , #{loginName} , '%')
        </if>
        <if test="userName != null &amp;&amp; userName != ''">
            and userName like CONCAT('%' , #{userName} , '%')
        </if>
        <if test="address != null &amp;&amp; address != ''">
            and address like CONCAT('%' , #{address} , '%')
        </if>
    </where>
</select>

2 动态修改SQL语句的执行

需求：可能修改用户名密码地址

动态修改SQL语句其实很简单，只需要把where标签换成set标签，去掉and，每个SQL语句后面加逗号，最好一个不加即可。

public class MybatisDemo {
 @Test
    public void updateByDynamic() {
        // 1.得到数据库会话对象
        SqlSession sqlSession = MybatisSqlSessionFactoryUtils.getSqlSession();
        // 2.得到数据访问层接口的代理对象。默认选择使用接口的映射文件作为代理对象！
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User u = new User();
        u.setId(4);
        u.setUserName("猪刚鬣");
        u.setAddress("大唐净坛");
        u.setPassWord("chan");
        
        int count = userMapper.updateByDynamic(u);
        System.out.println("修改了：" + count);
        sqlSession.commit();
        MybatisSqlSessionFactoryUtils.close(sqlSession);
    }
}

public interface UserMapper {
    int updateByDynamic(User u);
}

配置自定义结果集，然后使用update标签和set标签、if标签加上SQL语句即可。

<mapper namespace="com.itheima.dao.UserMapper">
    
    <!-- 自定义结果集  -->
    <resultMap id="userResultMap" type="User">
        <!-- column数据库字段列名 property实体类字段名称 -->
        <id column="id" property="id"></id>
        <!-- 配置关联名称不一样字段的映射 -->
        <result column="create_date" property="createDate"></result>
    </resultMap>
    
    <update id="updateByDynamic">
        update tb_user
        <set>
            <if test="userName !=null &amp;&amp; userName !=''">
                userName = #{userName} ,
            </if>
            <if test="passWord !=null &amp;&amp; passWord !=''">
                passWord = #{passWord} ,
            </if>
            <if test="address !=null &amp;&amp; address !=''">
                address = #{address}
            </if>
        </set>
        where id = #{id}
    </update>
</mapper>

3 动态插入和删除SQL语句foreach

3.1 foreach遍历集合参数(插入)

因为有的时候我们要添加数据是多个对象的，所以需要用到list集合去存入，存入时我们可以用foreach去对每一个对象进行遍历。

public class MybatisDemo {
    @Test
    public void insertBatch() {
        // 1.得到数据库会话对象
        SqlSession sqlSession = MybatisSqlSessionFactoryUtils.getSqlSession();
        // 2.得到数据访问层接口的代理对象。默认选择使用接口的映射文件作为代理对象！
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

        // 定义一个集合存储3个用户对象一起保存起来
        //     public User(String loginName, String passWord, String userName, Date createDate, String address) {
        List<User> users = new ArrayList<>();
        users.add(new User("cangcang", "123456" , "苍老师" , new Date() , "东京"));
        users.add(new User("songdao", "34567" , "松岛老师" , new Date() , "大阪"));
        users.add(new User("jiateng", "1234" , "加藤老师" , new Date() , "北海道"));

        int count = userMapper.addUsers(users);
        System.out.println("添加了数据：" + count);

        sqlSession.commit();// 事务提交
        MybatisSqlSessionFactoryUtils.close(sqlSession);
    }
}

public interface UserMapper {
    int addUsers(List<User> users);
}

UserMapper.xml

<mapper namespace="com.itheima.dao.UserMapper">

    <!--   int addUsers(List<User> users);
        users = [u1 , u2 , u3] 每一次都去遍历一个用户对象
                 u
    -->
    <insert id="addUsers">
            insert into tb_user(loginName , userName , passWord , create_date , address) values
        <!--
        foreach遍历传输进来的集合：
            collection:两个取值：list 表示集合 array 表示数组
            item:遍历集合中使用的变量名字
            separator:每次遍历后添加分隔符
        -->
        <foreach collection="list" item="u" separator=",">
            (#{u.loginName}, #{u.userName} , #{u.passWord} ,#{u.createDate} , #{u.address} )
        </foreach>
    </insert>
</mapper>

3.2 foreach遍历数组参数（删除）

用做删除操作的时候比较简单，我们只需要将要删除的内容的id存放到一个数组中，然后用foreach遍历这个数组中的每一个元素，进行删除操作即可。

public class MybatisDemo {
 @Test
    public void deleteBatch() {
        // 1.得到数据库会话对象
        SqlSession sqlSession = MybatisSqlSessionFactoryUtils.getSqlSession();
        // 2.得到数据访问层接口的代理对象。默认选择使用接口的映射文件作为代理对象！
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);

        // 定义一个数组存储3个id值，批量删除
        int count = userMapper.deleteByIds(new int[]{1,3,5,7,9});
        System.out.println("删除了数据：" + count);

        sqlSession.commit();// 事务提交
        MybatisSqlSessionFactoryUtils.close(sqlSession);
    }
}

public interface UserMapper {
    int deleteByIds(int[] ids);
}

UserMapper.xml

<mapper namespace="com.itheima.dao.UserMapper">
<delete id="deleteByIds">
        delete from tb_user  where id in
        <!--
            collection :代表当前遍历的是数组 array
            open:以什么开始
            close:以什么结尾
            item:遍历使用的游标变量
            separator：每次去到一个数据后用什么隔开
        -->
        <foreach collection="array" open="(" close=")" item="id" separator=",">
            #{id}
        </foreach>
    </delete>
</mapper>

4 sql和include标签

sql和include标签：简化映射文件中的脚本代码的书写。

sql标签：可以放置需要重复利用的脚本代码。

include标签：引用上面定义的SQL语句

< !-- include可以把sql语句的脚本关联过来使用通过refid去寻找–>
< include refid=“whereSQL”>< /include>

我们前面用了动态查询拼接的例子，我们可以发现文件里面的重复代码过多，冗余度过高，所以我们可以来做一个简化。

<mapper namespace="com.itheima.dao.UserMapper">
    
    <!-- 自定义结果集  -->
    <resultMap id="userResultMap" type="User">
        <!-- column数据库字段列名 property实体类字段名称 -->
        <id column="id" property="id"></id>
        <!-- 配置关联名称不一样字段的映射 -->
        <result column="create_date" property="createDate"></result>
    </resultMap>

    <!--
     List<User> selectByDynamic(User u);
     1、动态sql语句：条件可能是 用户名 登录名 地址 甚至么有条件！
     方式一：恒等式解决动态sql拼接问题（拓展方式）
    -->
    <select id="selectByDynamic" resultMap="userResultMap">
        select * from tb_user where 1=1
        <if test="loginName != null &amp;&amp; loginName != ''">
            and loginName like CONCAT('%' , #{loginName} , '%')
        </if>
        <if test="userName != null &amp;&amp; userName != ''">
            and userName like CONCAT('%' , #{userName} , '%')
        </if>
        <if test="address != null &amp;&amp; address != ''">
            and address like CONCAT('%' , #{address} , '%')
        </if>
    </select>

 <!--
     List<User> selectByDynamic02(User u);
     2、动态sql语句：条件可能是 用户名 登录名 地址 甚至么有条件！
     方式二：使用if结合where标签解决动态sql拼接问题。
     注意：第一个条件的and可以加可以不加！where标签会自动识别去掉不合理的sql语句拼接。
    -->
    <select id="selectByDynamic02" resultMap="userResultMap">
        select * from tb_user
        <where>
            <if test="loginName != null &amp;&amp; loginName != ''">
                 loginName like CONCAT('%' , #{loginName} , '%')
            </if>
            <if test="userName != null &amp;&amp; userName != ''">
                and userName like CONCAT('%' , #{userName} , '%')
            </if>
            <if test="address != null &amp;&amp; address != ''">
                and address like CONCAT('%' , #{address} , '%')
            </if>
        </where>
    </select>
</mapper>

那么我们就可以用sql标签放置相同的代码：

<!-- sql标签可以放置相同的脚本代码-->
<sql id="whereSQL">
    <if test="loginName != null &amp;&amp; loginName != ''">
        and loginName like CONCAT('%' , #{loginName} , '%')
    </if>
    <if test="userName != null &amp;&amp; userName != ''">
        and userName like CONCAT('%' , #{userName} , '%')
    </if>
    <if test="address != null &amp;&amp; address != ''">
        and address like CONCAT('%' , #{address} , '%')
    </if>
</sql>

然后只需要使用include可以把sql语句的脚本关联过来使用，通过refid去寻找：

<select id="selectByDynamic" resultMap="userResultMap">
    select * from tb_user where 1=1
    <include refid="whereSQL"></include>
</select>

<select id="selectByDynamic02" resultMap="userResultMap">
    select * from tb_user
    <where>
        <include refid="whereSQL"></include>
    </where>
</select>