Mybatis实践教程（六）-动态sql

阳光穿透心脏的1/2处 2022-01-16 02:53 226阅读 0赞

> **什么是动态sql？**
> 
> 在Mybatis的mapper文件中根据实际传递的参数条件来拼接条件。
> 
> 在Mybatis中动态sql，通过if标签来判断该条件是否有值，如果有值则增加该条件的查询

下面介绍动态查询和动态修改

**动态查询**

根据不同的查询条件查询用户信息

我们在之前的代码继续扩展增加selectUserListBytDynamic方法

UserService接口

package com.example.springbootmybatis.service;
    
    import com.example.springbootmybatis.domain.User;
    
    import java.util.List;
    import java.util.Map;
    
    public interface UserService {
    
        List<User> getAllUsers();
    
        User selectUserById(Integer id);
    
        List<User> getAllUsersByCond(Map<String,Object> map);
    
        int insertUser(User user);
    
        List<User> selectUserListBytDynamic(User user);
    }

UserServiceImpl实现类

package com.example.springbootmybatis.service.impl;
    
    import com.example.springbootmybatis.domain.User;
    import com.example.springbootmybatis.mapper.UserMapper;
    import com.example.springbootmybatis.service.UserService;
    import org.springframework.stereotype.Service;
    
    import javax.annotation.Resource;
    import java.util.List;
    import java.util.Map;
    
    
    @Service
    public class UserServiceImpl implements UserService {
    
        @Resource
        private UserMapper userMapper;
    
        @Override
        public List<User> getAllUsers() {
            return userMapper.selectUserList();
        }
    
        @Override
        public User selectUserById(Integer id) {
            return userMapper.selectUserById(id);
        }
    
        @Override
        public List<User> getAllUsersByCond(Map<String, Object> map) {
            return userMapper.getAllUsersByCond(map);
        }
    
        @Override
        public int insertUser(User user) {
            return userMapper.insertUser(user);
        }
    
        @Override
        public List<User> selectUserListBytDynamic(User user) {
            return userMapper.selectUserListBytDynamic(user);
        }
    }

UserMapper接口

package com.example.springbootmybatis.mapper;
    
    import com.example.springbootmybatis.domain.User;
    
    import java.util.List;
    import java.util.Map;
    
    public interface UserMapper {
    
        List<User> selectUserList ();
    
        User selectUserById(Integer id);
    
        List<User> getAllUsersByCond(Map<String,Object> map);
    
        int insertUser(User user);
    
        List<User> selectUserListBytDynamic(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 namespace="com.example.springbootmybatis.mapper.UserMapper">
        
        <resultMap id="userResult" type="com.example.springbootmybatis.domain.User">
            <id property="id" column="id"/>
            <result property="userName" column="user_name"/>
            <result property="age" column="age"/>
            <result property="mobilePhone" column="mobile_phone"/>
    
        </resultMap>
    
        <sql id="common">
            id,user_name,age,mobile_phone,address
        </sql>
        
        <select id="selectUserList" resultMap="userResult">
            select <include refid="common"></include> from t_user
        </select>
    
        
        <select id="selectUserById" resultMap="userResult">
            select * from t_user
            <where>
                id =#{id}
            </where>
        </select>
    
        
        <select id="getAllUsersByCond" parameterType="map" resultMap="userResult">
            select * from t_user
            <where>
                user_name like concat('%', #{userName}, '%')
            </where>
        </select>
    
    
        
        <insert id="insertUser" parameterType="User">
               insert into t_user(user_name, age, address, mobile_phone)
                   values(#{userName}, #{age}, #{address}, #{mobilePhone})
       </insert>
    
        
        <select id="selectUserListBytDynamic" parameterType="User"
                resultMap="userResult">
            select <include refid="common"></include> from t_user
            <where>
                <if test="userName!=null">
                    user_name like concat('%', #{userName}, '%')
                </if>
                <if test="age!=null and age!=0">
                    and age =#{age}
                </if>
                <if test="address!=null and address !=''">
                    and address like concat ('%',#{address},'%')
                </if>
                <if test="mobilePhone!=null and mobilePhone !=''">
                    and mobile_phone like concat ('%',#{mobilePhone},'%')
                </if>
            </where>
    
        </select>
    </mapper>

测试类

/**
     * 动态查询sql
     */
    @Test
    public void selectUserListBytDynamic() {
        User user = new User();
        user.setUserName("hello");
        user.setAddress("address");
        List<User> userList = userService.selectUserListBytDynamic(user);
        for (User userResult : userList) {
            System.out.println(userResult.toString());
        }
    }

**动态修改**

根据传递的参数修改用户信息

比如修改id为2的user\_name，address

UserService接口增加代码

int updateByDynamic(User user);

UserServiceImpl类实现的方法

@Override
    public int updateByDynamic(User user) {
        return userMapper.updateByDynamic(user);
    }

UserMapper接口增加的代码

int updateByDynamic(User user);

UserMapper.xml增加的sql

<update id="updateByDynamic" parameterType="User">
         update t_user
         <set>
             <if test="userName!=null">
                 user_name =#{userName},
             </if>
             <if test="age!=null and age!=0">
                 age =#{age},
             </if>
             <if test="address!=null and address !=''">
                 address =#{address},
             </if>
             <if test="mobilePhone!=null and mobilePhone !=''">
                mobile_phone =#{mobilePhoe}
             </if>
         </set>
         where id = #{id}
     </update>

<set>标签可以去除多余的逗号

测试类增加的代码

/**
     * 动态修改sql
     */
    @Test
    public void updateByDynamic(){
        User user = new User();
        user.setId(2L);
        user.setUserName("update user");
        user.setAddress("update address");
        userService.updateByDynamic(user) ;
    }

执行测试类运行结果，表示动态sql执行成功

22:57:15.718 [main] DEBUG c.e.s.m.U.updateByDynamic - [debug,143] - ==>  Preparing: update t_user SET user_name =?, address =? where id = ? 
    22:57:15.898 [main] DEBUG c.e.s.m.U.updateByDynamic - [debug,143] - ==> Parameters: update user(String), update address(String), 2(Long)
    22:57:16.007 [main] DEBUG c.e.s.m.U.updateByDynamic - [debug,143] - <==    Updates: 1