条件构造器与 CRUD 进阶

@SpringBootTest
class QueryWrapperTest {

    @Autowired
    private UserMapper userMapper;

    @Test
    void testComparison() {
        QueryWrapper<User> wrapper = new QueryWrapper<>();

        wrapper.ge("age", 18)      // age >= 18
               .lt("age", 30)      // age < 30
               .isNotNull("email"); // email IS NOT NULL

        List<User> users = userMapper.selectList(wrapper);
    }
}

@Test
void testLike() {
    QueryWrapper<User> wrapper = new QueryWrapper<>();

    wrapper.like("name", "张")          // LIKE '%张%'
           .likeRight("email", "test")   // LIKE 'test%'
           .notLike("name", "admin");    // NOT LIKE '%admin%'

    List<User> users = userMapper.selectList(wrapper);
}

@Test
void testIn() {
    QueryWrapper<User> wrapper = new QueryWrapper<>();

    wrapper.in("age", Arrays.asList(18, 20, 22))           // IN (18, 20, 22)
           .notIn("id", 1, 2, 3)                            // NOT IN (1, 2, 3)
           .inSql("id", "SELECT id FROM user WHERE age > 25"); // IN (子查询)

    List<User> users = userMapper.selectList(wrapper);
}

@Test
void testOrderBy() {
    QueryWrapper<User> wrapper = new QueryWrapper<>();

    wrapper.orderByDesc("age")   // ORDER BY age DESC
           .orderByAsc("id")     // ORDER BY id ASC
           .last("LIMIT 10");    // LIMIT 10（拼接 SQL 片段，有 SQL 注入风险！）

    List<User> users = userMapper.selectList(wrapper);
}

@Test
void testLogic() {
    QueryWrapper<User> wrapper = new QueryWrapper<>();

    wrapper.eq("name", "张三")
           .and(w -> w.gt("age", 20).or().isNotNull("email"));

    // 生成 SQL: WHERE name = '张三' AND (age > 20 OR email IS NOT NULL)
    List<User> users = userMapper.selectList(wrapper);
}

@Test
void testSelect() {
    QueryWrapper<User> wrapper = new QueryWrapper<>();

    wrapper.select("id", "name", "age")  // 只查这三个字段
           .gt("age", 18);

    List<User> users = userMapper.selectList(wrapper);
}

@Test
void testLambdaQueryWrapper() {
    LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();

    wrapper.eq(User::getName, "张三")         // name = '张三'
           .gt(User::getAge, 20)              // age > 20
           .likeRight(User::getEmail, "test") // email LIKE 'test%'
           .orderByDesc(User::getCreateTime); // ORDER BY create_time DESC

    List<User> users = userMapper.selectList(wrapper);
}

@Test
void testLambdaUpdateWrapper() {
    LambdaUpdateWrapper<User> wrapper = new LambdaUpdateWrapper<>();

    wrapper.eq(User::getName, "张三")           // WHERE name = '张三'
           .set(User::getAge, 25)               // SET age = 25
           .set(User::getEmail, "zs@test.com"); // SET email = 'zs@test.com'

    int rows = userMapper.update(null, wrapper);
    // 注意：第一个参数 null 表示不传实体对象，更新字段由 wrapper.set() 决定
}

UPDATE user SET age = 25, email = 'zs@test.com', update_time = NOW()
WHERE name = '张三' AND deleted = 0

@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User>
    implements UserService {

    // 批量插入示例
    public void batchInsertUsers(List<User> users) {
        saveBatch(users, 500);  // 每批 500 条
    }

    // 批量更新示例
    public void batchUpdateUsers(List<User> users) {
        updateBatchById(users, 500);
    }
}

@Service
public class UserServiceImpl extends ServiceImpl<UserMapper, User>
    implements UserService {

    public IPage<User> searchUsers(UserQueryRequest request) {
        LambdaQueryWrapper<User> wrapper = new LambdaQueryWrapper<>();

        // 动态拼接条件：只有传入的参数才会加入查询
        wrapper.like(StringUtils.isNotBlank(request.getName()),
                     User::getName, request.getName())
               .eq(request.getAge() != null,
                    User::getAge, request.getAge())
               .between(request.getMinAge() != null && request.getMaxAge() != null,
                        User::getAge, request.getMinAge(), request.getMaxAge())
               .orderByDesc(User::getCreateTime);

        Page<User> page = new Page<>(request.getPageNum(), request.getPageSize());
        return userMapper.selectPage(page, wrapper);
    }
}

@Mapper
public interface UserMapper extends BaseMapper<User> {

    // 简单自定义 SQL
    @Select("SELECT * FROM user WHERE age > #{minAge} AND deleted = 0")
    List<User> selectByMinAge(@Param("minAge") Integer minAge);

    // 多表关联查询
    @Select("SELECT u.*, d.name as dept_name " +
            "FROM user u LEFT JOIN dept d ON u.dept_id = d.id " +
            "WHERE u.status = #{status} AND u.deleted = 0")
    @Results({
        @Result(property = "id", column = "id"),
        @Result(property = "deptName", column = "dept_name")
    })
    List<UserVO> selectUserWithDept(@Param("status") Integer status);

    // XML 映射文件中的复杂查询
    IPage<UserVO> selectUserPage(Page<UserVO> page, @Param("query") UserQueryRequest query);
}

<?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.xiaoye.mybatisplus.mapper.UserMapper">

    <select id="selectUserPage" resultType="com.xiaoye.mybatisplus.vo.UserVO">
        SELECT u.*, d.name as dept_name
        FROM user u
        LEFT JOIN dept d ON u.dept_id = d.id
        WHERE u.deleted = 0
        <if test="query.name != null and query.name != ''">
            AND u.name LIKE CONCAT('%', #{query.name}, '%')
        </if>
        <if test="query.minAge != null">
            AND u.age >= #{query.minAge}
        </if>
        <if test="query.maxAge != null">
            AND u.age &lt;= #{query.maxAge}
        </if>
        ORDER BY u.create_time DESC
    </select>
</mapper>