需要的数据库建表语句:

#创建用户表

CREATE TABLE user (

    id BIGINT(20) PRIMARY KEY NOT NULL COMMENT '主键',

    name VARCHAR(30) DEFAULT NULL COMMENT '姓名',

    age INT(11) DEFAULT NULL COMMENT '年龄',

    email VARCHAR(50) DEFAULT NULL COMMENT '邮箱',

    manager_id BIGINT(20) DEFAULT NULL COMMENT '直属上级id',

    create_time DATETIME DEFAULT NULL COMMENT '创建时间',

    CONSTRAINT manager_fk FOREIGN KEY (manager_id)

        REFERENCES user (id)

)  ENGINE=INNODB CHARSET=UTF8;

#初始化数据：

INSERT INTO user (id, name, age, email, manager_id

    , create_time)

VALUES (1087982257332887553, '大boss', 40, 'boss@baomidou.com', NULL

        , '2019-01-11 14:20:20'),

    (1088248166370832385, '王天风', 25, 'wtf@baomidou.com', 1087982257332887553

        , '2019-02-05 11:12:22'),

    (1088250446457389058, '李艺伟', 28, 'lyw@baomidou.com', 1088248166370832385

        , '2019-02-14 08:31:16'),

    (1094590409767661570, '张雨琪', 31, 'zjq@baomidou.com', 1088248166370832385

        , '2019-01-14 09:15:15'),

    (1094592041087729666, '刘红雨', 32, 'lhm@baomidou.com', 1088248166370832385

        , '2019-01-14 09:48:16');

使用的是springboot项目:

配置文件:

spring:

  datasource:

    driver-class-name: com.mysql.cj.jdbc.Driver

    url: jdbc:mysql://127.0.0.1:3306/mybatis-plus?serverTimezone=UTC&useUnicode=true&useSSL=false&characterEncoding=utf8

    username: root

    password: root

logging:

  level:

    root: debug

  pattern:

    console: '%p%m%n'

#mapper的存放路径

mybatis-plus:

  mapper-locations: mapper/*

创建的实体类

public class User extends Model<User> {

    //主键

    private Long id;

    //姓名

    @TableField(condition = SqlCondition.LIKE)

    private String name;

    //邮箱

    private String email;

    //年龄

    private Integer age;

    //直属上级

    private Long managerId;

    //创建时间

    private LocalDateTime createTime;

    @Override

    public String toString() {

        return "User{" +

                "id=" + id +

                ", name='" + name + '\'' +

                ", email='" + email + '\'' +

                ", age=" + age +

                ", managerId=" + managerId +

                ", createTime=" + createTime +

                '}';

    }

    public Long getId() {

        return id;

    }

    public void setId(Long id) {

        this.id = id;

    }

    public String getName() {

        return name;

    }

    public void setName(String name) {

        this.name = name;

    }

    public String getEmail() {

        return email;

    }

    public void setEmail(String email) {

        this.email = email;

    }

    public Integer getAge() {

        return age;

    }

    public void setAge(Integer age) {

        this.age = age;

    }

    public Long getManagerId() {

        return managerId;

    }

    public void setManagerId(Long managerId) {

        this.managerId = managerId;

    }

    public LocalDateTime getCreateTime() {

        return createTime;

    }

    public void setCreateTime(LocalDateTime createTime) {

        this.createTime = createTime;

    }

}

mapper接口

@Repository

public interface UserMapper extends BaseMapper<User> {

    //@Select("select * from user ${ew.customSqlSegment}")

   // List<User> selectAll(@Param(Constants.WRAPPER) QueryWrapper<User> Wrapper);

    IPage<User> selectUserByPage(Page<User>page,@Param(Constants.WRAPPER) QueryWrapper<User> Wrapper);

    List<User> selectAll(@Param(Constants.WRAPPER) QueryWrapper<User> Wrapper);

}

分页插件的配置:

@Configuration

public class MybatisPlusConfig {

    @Bean

    public PaginationInterceptor paginationInterceptor() {

        return new PaginationInterceptor();

    }

}

service层接口:

public interface UserService extends IService<User> {

}

service层实现类:

@Service

public class UserService extends ServiceImpl<UserMapper, User> implements com.qingmu.mybatisplus.service.UserService {

}

启动类进行的配置

@SpringBootApplication

@MapperScan("com.qingmu.mybatisplus.dao")

public class MybatisplusApplication {

    public static void main(String[] args) {

        SpringApplication.run(MybatisplusApplication.class, args);

    }

}

mapper.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.qingmu.mybatisplus.dao.UserMapper">

    <select id="selectAll" resultType="com.qingmu.mybatisplus.entity.User">

select * from user ${ew.customSqlSegment}

</select>

    <select id="selectUserByPage" resultType="com.qingmu.mybatisplus.entity.User">

select * from user ${ew.customSqlSegment}

</select>

</mapper>

测试方法:

@SpringBootTest

public class UserTest {

    @Autowired

    private UserMapper userMapper;

    @Test

    public void select() {

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

        users.forEach(user -> System.out.println(user.toString()));

    }

    @Test

    public void insertTest() {

        User user = new User();

        user.setName("网台风");

        user.setEmail(null);

        user.setAge(18);

        user.setManagerId(1196025318649888768L);

        user.setCreateTime(LocalDateTime.now());

        int insert = userMapper.insert(user);

        System.out.println(insert);

    }

    @Test

    public void insertTest2() {

        User user = new User();

        user.setName("网台风");

        user.setEmail(null);

        user.setAge(18);

        user.setManagerId(1196025318649888768L);

        user.setCreateTime(LocalDateTime.now());

        boolean insert = user.insert();

        System.out.println(insert);

    }

    @Test

    public void selectById() {

        User user = userMapper.selectById(1196025318649888768L);

        System.out.println(user.toString());

    }

    @Test

    public void selectIds() {

        List<Long> longList = Arrays.asList(1196025318649888768L, 1197173809883381760L);

        List<User> users = userMapper.selectBatchIds(longList);

        users.forEach(user -> System.out.println(user.toString()));

    }

    @Test

    public void selectByMap() {

        HashMap<String, Object> map = new HashMap<>();

        map.put("name", "王天风");

        List<User> users = userMapper.selectByMap(map);

        for (User user : users) {

            System.out.println(user.toString());

        }

    }

    //名字中包含雨,并且年龄小于40

    @Test

    public void selectByWraper() {

        QueryWrapper<User> wrapper = new QueryWrapper<>();

        wrapper.like("name", "雨");

        wrapper.lt("age", 40);

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

        for (User user : users) {

            System.out.println(user.toString());

        }

    }

    //名字中包含雨年并且龄大于等于20且小于等于40并且email不为空

    @Test

    public void test2() {

        QueryWrapper<User> wrapper = new QueryWrapper<>();

        wrapper.like("name", "雨")

                .between("age", 20, 40)

                .isNotNull("email");

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

        for (User user : users) {

            System.out.println(user.toString());

        }

    }

    //名字为王姓或者年龄大于等于25，按照年龄降序排列，年龄相同按照id升序排列

    @Test

    public void test3() {

        QueryWrapper<User> wrapper = new QueryWrapper<>();

        wrapper.likeRight("name", "王")

                .or().ge("age", 25)

                .orderByDesc("age").orderByAsc("id");

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

        for (User user : users) {

            System.out.println(user.toString());

        }

    }

    //创建日期为2019年2月14日并且直属上级为名字为王姓

    @Test

    public void test4() {

        QueryWrapper<User> wrapper = new QueryWrapper<>();

        wrapper.apply("date_format(create_time,'%Y-%m-%d')={0}", "2019-02-14")

                .inSql("manager_id", "select id from user where name like '王%'");

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

        for (User user : users) {

            System.out.println(user.toString());

        }

    }

    //名字为王姓并且（年龄小于40或邮箱不为空）

    @Test

    public void test5() {

        QueryWrapper<User> wrapper = new QueryWrapper<>();

        wrapper.likeRight("name", "王")

                .and(wq -> wq.lt("age", 40).or().isNotNull("email"));

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

        for (User user : users) {

            System.out.println(user.toString());

        }

    }

    //6.名字为王姓或者（年龄小于40并且年龄大于20并且邮箱不为空）

    @Test

    public void test6() {

        QueryWrapper<User> wrapper = new QueryWrapper<>();

        wrapper.likeRight("name", "王")

                .or(wq -> wq.lt("age", 40).ge("age", 20).isNotNull("email"));

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

        for (User user : users) {

            System.out.println(user.toString());

        }

    }

    //7、（年龄小于40或邮箱不为空）并且名字为王姓

    @Test

    public void test7() {

        QueryWrapper<User> wrapper = new QueryWrapper<>();

        wrapper.nested(qw -> qw.lt("age", 40).isNotNull("email")).likeRight("name", "王");

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

        for (User user : users) {

            System.out.println(user.toString());

        }

    }

    //年龄为30、31、34、35

    @Test

    public void test8() {

        QueryWrapper<User> wrapper = new QueryWrapper<>();

        wrapper.in("age", Arrays.asList(18, 31, 30, 34, 35));

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

        for (User user : users) {

            System.out.println(user.toString());

        }

    }

    @Test

    public void test9() {

        QueryWrapper<User> wrapper = new QueryWrapper<>();

        wrapper.in("age", Arrays.asList(18, 31, 30, 34, 35)).last("limit 1");

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

        for (User user : users) {

            System.out.println(user.toString());

        }

    }

    //不列出全部字段

    @Test

    public void test10() {

        QueryWrapper<User> wrapper = new QueryWrapper<>();

        wrapper.select("id", "name").in("age", Arrays.asList(18, 31, 30, 34, 35)).last("limit 1");

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

        for (User user : users) {

            System.out.println(user.toString());

        }

    }

    //不列出全部字段

    @Test

    public void test11() {

        QueryWrapper<User> wrapper = new QueryWrapper<>();

        wrapper.select(User.class, info -> !info.getColumn().equals("create_time")

                && !info.getColumn().equals("manager_id")).in("age", Arrays.asList(18, 31, 30, 34, 35)).last("limit 1");

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

        for (User user : users) {

            System.out.println(user.toString());

        }

    }

    //condition条件

    @Test

    public void testCondition() {

        String name = "王";

        String email = "";

        condition(name, email);

    }

    private void condition(String name, String email) {

        QueryWrapper<User> wrapper = new QueryWrapper<>();

        //需要六行,太麻烦

//        if(StringUtils.isNotBlank(name)){

//            wrapper.like("name",name);

//        }

//        if(StringUtils.isNotBlank(name)){

//            wrapper.like("name",name);

//        }

        wrapper.like(StringUtils.isNotBlank("name"), "name", name)

                .like(StringUtils.isNotBlank("email"), "email", email);

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

        for (User user : users) {

            System.out.println(user.toString());

        }

    }

    // 实体作为条件构造器构造方法的参数

    @Test

    public void test12() {

        User user1 = new User();

        user1.setName("刘红雨");

        user1.setAge(32);

        QueryWrapper<User> wrapper = new QueryWrapper<>(user1);

//    wrapper.select(User.class, info -> !info.getColumn().equals("create_time")

//            && !info.getColumn().equals("manager_id")).in("age", Arrays.asList(18, 31, 30, 34, 35)).last("limit 1");

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

        for (User user : users) {

            System.out.println(user.toString());

        }

    }

    //Alleq

    @Test

    public void test13() {

        QueryWrapper<User> wrapper = new QueryWrapper<>();

        HashMap<String, Object> map = new HashMap<>();

        map.put("name", "王天风");

//        map.put("age", "25");

        map.put("age", null);

        wrapper.allEq(map);

        //如果为false,则会忽略为null的字段

//        wrapper.allEq(map,false);

        wrapper.allEq((k, v) -> !k.equals("name"), map);

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

        for (User user : users) {

            System.out.println(user.toString());

        }

    }

    @Test

    public void test14() {

        QueryWrapper<User> wrapper = new QueryWrapper<>();

        wrapper.select("avg(age) avg_age", "min(age) min_age", "max(age) max_age").groupBy("manager_id")

                .having("sum(age)<{0}", 500);

        List<Map<String, Object>> users = userMapper.selectMaps(wrapper);

        System.out.println(users);

    }

    @Test

    public void test15() {

        QueryWrapper<User> wrapper = new QueryWrapper<>();

        wrapper.select("avg(age) avg_age", "min(age) min_age", "max(age) max_age").groupBy("manager_id")

                .having("sum(age)<{0}", 500);

        List<Object> users = userMapper.selectObjs(wrapper);

        System.out.println(users);

    }

    @Test

    public void test16() {

        QueryWrapper<User> wrapper = new QueryWrapper<>();

        wrapper.select("avg(age) avg_age", "min(age) min_age", "max(age) max_age").groupBy("manager_id")

                .having("sum(age)<{0}", 500);

        Integer count = userMapper.selectCount(wrapper);

        System.out.println(count);

    }

    @Test

    public void test17() {

        QueryWrapper<User> wrapper = new QueryWrapper<>();

        wrapper.select("avg(age) avg_age", "min(age) min_age", "max(age) max_age").groupBy("manager_id")

                .having("sum(age)<{0}", 500);

        User user = userMapper.selectOne(wrapper);

        System.out.println(user);

    }

    @Test

    public void test18() {

        QueryWrapper<User> wrapper = new QueryWrapper<>();

        wrapper.select("avg(age) avg_age", "min(age) min_age", "max(age) max_age").groupBy("manager_id")

                .having("sum(age)<{0}", 500);

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

        System.out.println(users);

    }

    //分页

    @Test

    public void selectPage() {

        QueryWrapper<User> wrapper = new QueryWrapper<>();

        wrapper.ge("age", 26);

        Page<User> page = new Page<>(1, 2);

//        IPage<User> userIPage = userMapper.selectPage(page, wrapper);

//        System.out.println("总条数"+userIPage.getTotal());

//        System.out.println("总页数"+userIPage.getPages());

        IPage<Map<String, Object>> userIPage = userMapper.selectMapsPage(page, wrapper);

        System.out.println("总条数" + userIPage.getTotal());

        System.out.println("总页数" + userIPage.getPages());

    }

    @Test

    public void selectPage2() {

        QueryWrapper<User> wrapper = new QueryWrapper<>();

        wrapper.ge("age", 26);

        Page<User> page = new Page<>(1, 2);

//        IPage<User> userIPage = userMapper.selectPage(page, wrapper);

//        System.out.println("总条数"+userIPage.getTotal());

//        System.out.println("总页数"+userIPage.getPages());

        IPage<User> userIPage = userMapper.selectUserByPage(page, wrapper);

        System.out.println("总条数" + userIPage.getTotal());

        System.out.println("总页数" + userIPage.getPages());

    }

    //更新

    @Test

    public void updateById() {

        User user = new User();

        user.setId(1087982257332887553L);

        user.setAge(26);

        int i = userMapper.updateById(user);

        System.out.println(i);

    }

    @Test

    public void updateWrapper1() {

        UpdateWrapper<User> wrapper = new UpdateWrapper<>();

        wrapper.eq("name", "李艺伟").eq("age", 28);

        User user = new User();

        user.setName("利益");

        int update = userMapper.update(user, wrapper);

        System.out.println("影响记录条数:" + update);

    }

    @Test

    public void updateWrapper2() {

        User user1 = new User();

        user1.setName("利益");

        UpdateWrapper<User> wrapper = new UpdateWrapper<>(user1);

//        wrapper.eq("name","李艺伟").eq("age",28);

        User user = new User();

        user.setName("利益");

        int update = userMapper.update(user, wrapper);

        System.out.println("影响记录条数:" + update);

    }

    @Test

    public void updateWrapper3() {

        UpdateWrapper<User> wrapper = new UpdateWrapper<>();

        wrapper.eq("name", "李艺伟")

                .eq("age", 28)

                .set("age", 29);

//        User user = new User();

//        user.setName("利益");

        int update = userMapper.update(null, wrapper);

        System.out.println("影响记录条数:" + update);

    }

    @Test

    public void updateLamda(){

        LambdaUpdateWrapper<User> lambdaUpdate = Wrappers.<User>lambdaUpdate();

        lambdaUpdate.eq(User::getName,"李艺伟").eq(User::getAge,"29").set(User::getAge,"30");

        int update = userMapper.update(null, lambdaUpdate);

        System.out.println(update);

    }

    @Test

    public void delete(){

        int i = userMapper.deleteById(1197191875728003073L);

        System.out.println(i);

    }

    @Test

    public void delete2(){

        HashMap<String, Object> map = new HashMap<>();

        map.put("name","网台风");

        map.put("age",18);

        int i = userMapper.deleteByMap(map);

        System.out.println(i);

    }

    @Test

    public void delete3(){

        int i = userMapper.deleteBatchIds(Arrays.asList(1197191725035073537L,1197191393303359489L));

        System.out.println(i);

    }

}

service层接口测试方法:

@SpringBootTest

@RunWith(SpringRunner.class)

public class ServiceTest {

    @Autowired

    private UserService userService;

    @Test

    public void getOne() {

        User one = userService.getOne(Wrappers.<User>lambdaQuery().gt(User::getAge, "20"),false);

        System.out.println(one);

    }

    @Test

    public void Batch() {

        User user = new User();

        user.setName("蓄力");

        user.setAge(18);

        User user2 = new User();

        user.setName("蓄力2");

        user.setAge(28);

        List<User> users = Arrays.asList(user, user2);

        boolean b = userService.saveBatch(users);

        System.out.println(b);

    }

    @Test

    public void chain(){

        List<User> users = userService.lambdaQuery().gt(User::getAge, 25).like(User::getName, "雨").list();

        users.forEach(System.out::println);

    }

    @Test

    public void chain2(){

        boolean update = userService.lambdaUpdate().eq(User::getAge, 25).set(User::getAge, 26).update();

        System.out.println(update);

    }

    @Test

    public void chain3(){

        boolean update = userService.lambdaUpdate().eq(User::getAge, 24).remove();

        System.out.println(update);

    }

}

最新文章

  1. D3.js学习（二）
  2. Spring jar下载地址：
  3. JAVA异常处理机制的简单原理和应用
  4. 响应式web设计之CSS3 Media Queries
  5. 曲演杂坛--特殊字符/生僻字与varchar
  6. ocp 1Z0-051 141题---感觉有问题
  7. TortoiseSVN提交文件的时候卡死
  8. android小知识之圆角ListView
  9. 安装MySQL -- SuSE Linux Enterprise Server 11 SP3
  10. Struts2 语法--验证方式:
  11. 我的python学习笔记一
  12. Linux系统bash shell之历史命令
  13. 教你快速打造PHP MVC框架
  14. Ajax会自动将返回的对象属性首字母转化为小写
  15. HDUOJ-2089 不要62
  16. DELL 服务器报CPU 1 has an internal error (IERR）
  17. 次小生成树(POJ1679/CDOJ1959)
  18. Appium 自带的定位工具 Inspector
  19. scrapy的request的meta参数是什么意思？
  20. java序列化/反序列化之xstream、protobuf、protostuff 的比较与使用例子

热门文章

  1. web-never give up
  2. day46_9_5前端（3）
  3. Mybatis日志（七）
  4. CF1185F Two Pizzas
  5. CF-579 D. &quot;Or&quot; Game
  6. Java连载37-面向对象的封装性
  7. Shell基本运算符之布尔运算符、逻辑运算符
  8. CodeForce 222C Reducing Fractions
  9. 大咖云集！Kubernetes and Cloud Native Meetup 深圳站开始报名！
  10. Spring Boot自定义配置实现IDE自动提示