Mysql中使用Mybatis-Plus中sql语句写法操作

package com.lx;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.conditions.update.UpdateWrapper;
import com.lx.bean.UserBean;
import com.lx.mapper.UserMapper;
import lombok.extern.slf4j.Slf4j;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;
import org.springframework.util.StringUtils;

import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @description:
 * @author: Liu Wei Ping
 * @date: 2022/11/1
 **/
@Slf4j
@RunWith(SpringRunner.class)
@SpringBootTest(properties = "local")
public class MybatisPlusTest {

    继承了BaseMapper ,所有方法来自父类，可扩展
    @Autowired
    private UserMapper userMapper;


    /**
     * 测试MybatisPlus新增操作
     * 面向对象的方式操作数据库!!!!
     */
    @Test
    public void testInsert() {
        UserBean userBean = new UserBean();
        userBean.setName("无名英雄永垂不朽");
        userBean.setAge(3);
        userBean.setSex("男女");
        int rows = userMapper.insert(userBean);
        System.out.println("入库成功影响:"+rows+"行");
    }

    //速度对比,1.手写sql  2.动态生成sql
    @Test
    public void testInsert2() {
        UserBean UserBean = new UserBean();
        UserBean.setName("无名英雄永垂不朽");
        UserBean.setAge(3);
        UserBean.setSex("男女");
        Long start = System.currentTimeMillis();
        //int rows = userMapper.insertUserBean(UserBean);
        int rows = userMapper.insert(UserBean);
        Long end = System.currentTimeMillis();
        System.out.println("执行时间:"+(end-start)+"毫秒");
        //System.out.println("入库成功影响:"+rows+"行");
    }


    /**
     * 需求1:按照指定的id进行查询   id 1,3,5,9
     *sql: select * from UserBean where id=1 and id=3 and id=xxxxx...
     *使用in关键字
     *     select * from UserBean where id in (1,3,5,9);
     *实际业务
     *      用户查询的参数1,3,5,9都是由前台web动态传参,所以在Controler中
     *      一般采用数组的形式动态接收.
     *
     */
    @SuppressWarnings("unchecked")
    @Test
    public void test01() {
        //一般都是用对象类型进行定义
        Integer[] ids = {1,3,5,9};
        //数组怎么转化为集合??
        List idList =  Arrays.asList(ids);
        //List idList =  org.assertj.core.util.Arrays.asList(ids);
        /*List<Integer> idList = new ArrayList<>();
        idList.add(1);
        idList.add(3);
        idList.add(5);
        idList.add(9);*/
        List<UserBean> UserBeanList = userMapper.selectBatchIds(idList);
        System.out.println(UserBeanList);
    }

    /**
     * 根据指定的条件查询
     * 查询name="王昭君" sex=女的用户 默认使用and方式进行关联
     * 需要将查询的字段与值封装到MAP中
     * SELECT id,name,age,sex FROM UserBean WHERE sex = ? AND name = ?
     */
    @Test
    public void test02() {
        Map<String, Object> columnMap = new HashMap<>();
        columnMap.put("name", "王昭君");   //默认使用=号
        columnMap.put("sex", "女");
        List<UserBean> UserBeanList = userMapper.selectByMap(columnMap);
        System.out.println(UserBeanList);
    }

    /**
     * 查询age>18  and sex=男
     *逻辑运算符   > gt,  < lt, = eq,  >= ge, <= le
     */
    @Test
    public void test03() {
        //定义条件构造器  负责拼接where条件的
        QueryWrapper<UserBean> queryWrapper = new QueryWrapper<>();
        queryWrapper.gt("age", 18)
                .eq("sex", "男");
        List<UserBean> UserBeanList = userMapper.selectList(queryWrapper);
        System.out.println(UserBeanList);
    }


    /**
     * name like %精% and sex="女"
     *   以精字结尾
     * name like %精
     *             精%
     */
    @Test
    public void test04() {
        //定义条件构造器  负责拼接where条件的
        QueryWrapper<UserBean> queryWrapper = new QueryWrapper<>();
        queryWrapper.likeLeft("name", "精")
                .eq("sex", "女");
        List<UserBean> UserBeanList = userMapper.selectList(queryWrapper);
        System.out.println(UserBeanList);
    }


    /**
     * 条件:查询年龄在18-35之间的女性用户.
     * sql: select * from UserBean where age > 18 and age < 35
     */
    @Test
    public void test05() {
        //定义条件构造器  负责拼接where条件的
        QueryWrapper<UserBean> queryWrapper = new QueryWrapper<>();
        queryWrapper.between("age", 18, 35)
                .eq("sex", "女");
        List<UserBean> UserBeanList = userMapper.selectList(queryWrapper);
        System.out.println(UserBeanList);
    }


    /**
     * 条件:查询年龄大于100岁的,并且按照年龄降序排列,如果年龄相同按照Id降序排列.
     *sql中的排序默认根据id升序排列
     *SELECT id,name,age,sex FROM UserBean WHERE (age > ?) ORDER BY age DESC,id DESC
     */
    @Test
    public void test06() {
        //定义条件构造器  负责拼接where条件的
        QueryWrapper<UserBean> queryWrapper = new QueryWrapper<>();
        queryWrapper.gt("age",100)
                .orderByDesc("age","id");
        List<UserBean> UserBeanList = userMapper.selectList(queryWrapper);
        System.out.println(UserBeanList);
    }


    /**
     * 子查询: insql
     * 条件: 查询age < 100岁的用户,并且性别与name="孙尚香"的性别相同的的用户数据.
     *sql: SELECT * FROM UserBean WHERE age < 100 AND
     *     sex IN(SELECT sex FROM UserBean WHERE NAME="孙尚香")

     */
    @Test
    public void test07() {
        //定义条件构造器  负责拼接where条件的
        QueryWrapper<UserBean> queryWrapper = new QueryWrapper<>();
        String name = "'孙尚香'";
        queryWrapper.lt("age", 100)
                .inSql("sex", "select sex from UserBean where name="+name);
        List<UserBean> UserBeanList = userMapper.selectList(queryWrapper);
        System.out.println(UserBeanList);
    }


    /**
     * 需求:有时查询可能不需要查询全部字段,会挑选其中几个进行查询.
     *1.指定字段进行查询
     * 查询age>100岁 只查询Id,name字段
     *
     *2.只返回查询的字段
     */
    @Test
    public void test08() {
        //定义条件构造器  负责拼接where条件的
        QueryWrapper<UserBean> queryWrapper = new QueryWrapper<>();
        queryWrapper.select("id","name").gt("age", 100);
        List<UserBean> UserBeanList = userMapper.selectList(queryWrapper);
        System.out.println(UserBeanList);

        //只想返回特定的字段
        List<Map<String,Object>> mapList = userMapper.selectMaps(queryWrapper);
        System.out.println(mapList);

        //只返回第一列字段的值  主键值
        List<Object> list = userMapper.selectObjs(queryWrapper);
        System.out.println(list);
    }

    /**
     *   根据属性中不为null的元素查询数据
     * condition:布尔类型的判断 只有当判断为true时,条件生效
     */
    @Test
    public void test09() {
        String name = "黑熊精";
        String sex = "男";
        //定义条件构造器  负责拼接where条件的
        QueryWrapper<UserBean> queryWrapper = new QueryWrapper<>();
        //当name属性不为null的时候会动态的拼接name属性值
        queryWrapper.eq(!StringUtils.isEmpty(name), "name", name)
                .eq(!StringUtils.isEmpty(sex), "sex", sex);
        List<UserBean> UserBeanList = userMapper.selectList(queryWrapper);
        System.out.println(UserBeanList);
    }

    /**
     * 根据对象中不为null的属性进行查询
     */
    @Test
    public void test10() {
        UserBean userBean = new UserBean();
        userBean.setName("黑熊精").setSex("男");
        QueryWrapper<UserBean> queryWrapper = new QueryWrapper<UserBean>(userBean);
        List<UserBean> UserBeanList = userMapper.selectList(queryWrapper);
        System.out.println(UserBeanList);
    }

    //根据主键查询
    @Test
    public void test11() {

        UserBean UserBean = userMapper.selectById(1111);
        //根据条件返回一个对象信息
        QueryWrapper<UserBean> queryWrapper = new QueryWrapper<>();
        queryWrapper.eq("id", "3");
        UserBean UserBean2 = userMapper.selectOne(queryWrapper);
        System.out.println(UserBean);
        System.out.println(UserBean2);
    }


    /**
     * MP的删除用法
     *  案例:
     *      1.将name字段值为null的数据进行
     *      2.将name字段信息 无名英雄永垂不朽删除
     *
     */
    @Test
    public void test12() {
        QueryWrapper<UserBean> queryWrapper = new QueryWrapper<UserBean>();
        //queryWrapper.isNull("name");
        queryWrapper.eq("name", "无名英雄永垂不朽");
        userMapper.delete(queryWrapper);
        System.out.println("数据库删除成功!!!!");
    }


    /**
     * 数据库更新操作
     *
     * 1.将大日如来的年龄改为10001 性别 改为男
     * entity: 数据修改后的值使用对象封装
     * updateWrapper: 修改的条件构造器
     *
     *
     */
    @Test
    public void test13() {
        UserBean UserBean = new UserBean();
        UserBean.setAge(10001).setSex("男");
        UpdateWrapper<UserBean> updateWrapper = new UpdateWrapper<>();
        updateWrapper.eq("name","大日如来");
        userMapper.update(UserBean, updateWrapper);
    }


    //2.根据主键1915 将name改为"人民英雄" age=100   性别=男/女
    //对象中只有主键充当where条件,其他的字段信息充当set条件
    @Test
    public void test14() {
        UserBean UserBean = new UserBean();
        UserBean.setId(Integer.valueOf(1915))
                .setName("人民英雄")
                .setAge(100)
                .setSex("男/女");

        userMapper.updateById(UserBean);
    }


}