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

什么是动态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用于实体对象User与表映射-->
    <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>

    <!--   根据id查询单条用户信息-->
    <select id="selectUserById" resultMap="userResult">
        select * from t_user
        <where>
            id =#{id}
        </where>
    </select>

    <!-- 模糊查询 like查询-->
    <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>

    <!--动态查询sql-->
    <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

<!-- 动态修改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