谷粒学院day0&day1——项目介绍与mybatis plus入门

mysql -u root -p

DROP TABLE IF EXISTS user;
CREATE TABLE user
(
id BIGINT(20) NOT NULL COMMENT '主键ID',
name VARCHAR(30) NULL DEFAULT NULL COMMENT '姓名',
age INT(11) NULL DEFAULT NULL COMMENT '年龄',
email VARCHAR(50) NULL DEFAULT NULL COMMENT '邮箱',
PRIMARY KEY (id)
);

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.2.1.RELEASE</version>
    <relativePath/> <!-- lookup parent from repository -->
</parent>

    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter</artifactId>
</dependency>

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-test</artifactId>
    <scope>test</scope>
    <exclusions>
        <exclusion>
            <groupId>org.junit.vintage</groupId>
            <artifactId>junit-vintage-engine</artifactId>
        </exclusion>
    </exclusions>
</dependency>

<!--mybatis-plus-->
<dependency>
    <groupId>com.baomidou</groupId>
    <artifactId>mybatis-plus-boot-starter</artifactId>
    <version>3.0.5</version>
</dependency>

<!--mysql-->
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>

<!--lombok用来简化实体类-->
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
</dependency>

spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/mybatis_plus?serverTimezone=GMT%2B8
spring.datasource.username=root
spring.datasource.password=123456

@Data
public class User {
    private Long id;
    private String name;
    private Integer age;
    private String email;
}

public interface UserMapper extends BaseMapper<User> {
}

public interface BaseMapper<T> {
    int insert(T var1);

    int deleteById(Serializable var1);

    int deleteByMap(@Param("cm") Map<String, Object> var1);

    int delete(@Param("ew") Wrapper<T> var1);

    int deleteBatchIds(@Param("coll") Collection<? extends Serializable> var1);

    int updateById(@Param("et") T var1);

    int update(@Param("et") T var1, @Param("ew") Wrapper<T> var2);

    T selectById(Serializable var1);

    List<T> selectBatchIds(@Param("coll") Collection<? extends Serializable> var1);

    List<T> selectByMap(@Param("cm") Map<String, Object> var1);

    T selectOne(@Param("ew") Wrapper<T> var1);

    Integer selectCount(@Param("ew") Wrapper<T> var1);

    List<T> selectList(@Param("ew") Wrapper<T> var1);

    List<Map<String, Object>> selectMaps(@Param("ew") Wrapper<T> var1);

    List<Object> selectObjs(@Param("ew") Wrapper<T> var1);

    IPage<T> selectPage(IPage<T> var1, @Param("ew") Wrapper<T> var2);

    IPage<Map<String, Object>> selectMapsPage(IPage<T> var1, @Param("ew") Wrapper<T> var2);
}

@MapperScan("package com.wangzhou.mpdemo1010.mapper")

 @Autowired
 private UserMapper userMapper;

 @Test
 public void findAll() {
     List<User> users= userMapper.selectList(null);
     System.out.println(users);
 }

spring.datasource.url=jdbc:mysql://localhost:3306/mybatis-plus?serverTimezone=GMT%2B8

No qualifying bean of type 'com.wangzhou.mpdemo1010.mapper.UserMapper' available

@MapperScan("com.wangzhou.mpdemo1010.mapper")

[User(id=1, name=wz, age=18, email=wz@qq.com), User(id=2, name=wd, age=28, email=wd@qq.com)]

#mybatis日志
mybatis-plus.configuration.log-impl=org.apache.ibatis.logging.stdout.StdOutImpl

Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@16d07cf3] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@1198531054 wrapping com.mysql.cj.jdbc.ConnectionImpl@2180e789] will not be managed by Spring
==>  Preparing: SELECT id,name,age,email FROM user 
==> Parameters: 
<==    Columns: id, name, age, email
<==        Row: 1, wz, 18, wz@qq.com
<==        Row: 2, wd, 28, wd@qq.com
<==      Total: 2
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@16d07cf3]

 @Test
 public  void  insert() {
   User user = new User();
   user.setAge(19);
   user.setEmail("pp@163.com");
   user.setName("pp");
   userMapper.insert(user);
   System.out.println("insert:" + user);
}

==>  Preparing: INSERT INTO user ( id, name, age, email ) VALUES ( ?, ?, ?, ? ) 
==> Parameters: 1428131805257474049(Long), pp(String), 19(Integer), pp@163.com(String)
<==    Updates: 1

@TableId(type = IdType.AUTO) //自增策略
private Long id;

@Getter
public enum IdType {
    /**
     * 数据库ID自增
     */
    AUTO(0),
    /**
     * 不设置主键生成策略
     */
    NONE(1),
    /**
     * 用户输入ID
     * 该类型可以通过自己注册自动填充插件进行填充
     */
    INPUT(2),

    /* 以下3种类型、只有当插入对象ID 为空，才自动填充。 */
    /**
     * 全局唯一ID (idWorker)，mp自带策略，生成19位随机数,数字类型使用这种策略
     */
    ID_WORKER(3),
    /**
     * 全局唯一ID (UUID)
     */
    UUID(4),
    /**
     * 字符串全局唯一ID (idWorker 的字符串表示)，mp自带策略，生成19位随机数,字符串类型使用这种策略
     */
    ID_WORKER_STR(5);

    private int key;

    IdType(int key) {
        this.key = key;
    }
}

    @Test
    public void findAll() {
       List<User> users= userMapper.selectList(null);
       System.out.println(users);
    }

java.sql.SQLSyntaxErrorException: Unknown database 'mybatis-plus'

update user set age = 20 where id = 1;

    @Test
    public  void  update() {
        User user = new User();
        user.setId(1l);
        user.setAge(20);
        //返回更新的行数
        int row = userMapper.updateById(user);
        System.out.println(row);
    }

 @TableField(fill = FieldFill.INSERT)
 private Date createTime;
 @TableField(fill = FieldFill.INSERT_UPDATE)
 private Date updateTime;

@Component // @Component注解将MyMetaObjectHandler交给spring管理
public class MyMetaObjectHandler implements MetaObjectHandler {
    // 使用mp执行insert操作，这个方法会自动执行
    @Override
    public void insertFill(MetaObject metaObject) {
        // 根据这个方法的名字即可知道对应功能。第一个参数filed是要填充的字段，第二个参数val是填充的值，第三个参数是元数据
        // 元数据是指描述其他数据的数据：表名，字段名(id,name...)等
        this.setFieldValByName("createTime", new Date(), metaObject);
        this.setFieldValByName("updateTime", new Date(), metaObject);

    }

    // 使用mp执行update操作。这个方法会自动执行
    @Override
    public void updateFill(MetaObject metaObject) {
        this.setFieldValByName("updateTime", new Date(), metaObject);
    }
}

 Error updating database.  Cause: java.sql.SQLException: Field 'id' doesn't have a default value

@Version
private Integer version;

@Configuration
@MapperScan("com.wangzhou.mpdemo1010.mapper")
public class MpConfig {

    // 乐观锁插件
    @Bean
    public MybatisPlusInterceptor mybatisPlusInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new OptimisticLockerInnerInterceptor());
        return interceptor;
    }
}

@TableField(fill = FieldFill.INSERT)

this.setFieldValByName("version", 1, metaObject);

 @Test
 public void testOptimisticLocker() {
    //先查询
    User user = this.userMapper.selectById(1428131805257474051l);
    //后修改
    user.setAge(99);
    this.userMapper.updateById(user);
 }

  //单个id查询
    @Test
    public void testSelectDemo() {
        User user = this.userMapper.selectById(1l);
        System.out.println(user);
    }

    //多个id查询
    @Test
    public void testSelectDemo1() {
        List users = this.userMapper.selectBatchIds(Arrays.asList(1l, 2l, 3l));
        System.out.println(users);
    }

    //根据条件查询
    @Test
    public void  testSelectDemo2() {
        Map map = new HashMap<String, Object>();
        map.put("name", "独孤求败");
        map.put("age" , 19);
        this.userMapper.selectByMap(map);
    }

//   在新版mp中已过时
//    @Bean
//    public PaginationInterceptor paginationInterceptor() {
//        return new PaginationInterceptor();
//    }

    //分页插件
    @Bean
    public MybatisPlusInterceptor paginationInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.H2));
        return interceptor;
    }

//分页查询
@Test
public void  testPage() {
    Page <User> page = new Page<>(1, 3);
    // mp底层会把分页查询到的结果封装到page中
    this.userMapper.selectPage(page, null);
    System.out.println("页数:" + page.getPages());
    System.out.println("当前页:" + page.getCurrent());
    System.out.println("当前数据的List集合:" + page.getRecords());
    System.out.println("每页记录数:" + page.getSize());
    System.out.println("记录总数：" + page.getTotal());
    System.out.println("是否有下一页：" + page.hasNext());
    System.out.println("是否有上一页：" + page.hasPrevious());
}

  //物理删除
    @Test
    public void testDelete() {
        int result = this.userMapper.deleteById(1l);
        System.out.println(result);
    }

//批量删除
@Test
public void  testBatchDelete() {
    int result = this.userMapper.deleteBatchIds(Arrays.asList(2l, 3l));
    System.out.println(result);
}

//注解表示其用于逻辑删除
@TableLogic
private Integer deleted;

#设置逻辑删除中删除与未删除的默认值
mybatis-plus.global-config.db-config.logic-delete-value=1
mybatis-plus.global-config.db-config.logic-not-delete-value=0

UPDATE user SET deleted=1 WHERE id=? AND deleted=0

说明:

只对自动注入的sql起效:

插入: 不作限制
查找: 追加where条件过滤掉已删除数据,且使用 wrapper.entity 生成的where条件会忽略该字段
更新: 追加where条件防止更新到已删除数据,且使用 wrapper.entity 生成的where条件会忽略该字段
删除: 转变为 更新
例如:

删除: update user set deleted=1 where id = 1 and deleted=0
查找: select id,name,deleted from user where deleted=0
字段类型支持说明:

支持所有数据类型(推荐使用 Integer,Boolean,LocalDateTime)
如果数据库字段使用datetime,逻辑未删除值和已删除值支持配置为字符串null,另一个值支持配置为函数来获取值如now()
附录:

逻辑删除是为了方便数据恢复和保护数据本身价值等等的一种方案，但实际就是删除。
如果你需要频繁查出来看就不应使用逻辑删除，而是以一个状态去表示。

  //性能分析插件
    /**
     * SQL 执行性能分析插件
     * 开发环境使用，线上不推荐。
     */
    @Bean
    @Profile({"dev","test"})// 设置 dev test 环境开启
    public MybatisPlusInterceptor performanceInterceptor() {
        MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
        interceptor.addInnerInterceptor(new IllegalSQLInnerInterceptor()); 
        return interceptor;
    }

# 环境设置为dev
spring.profiles.active=dev

    //复杂查询操作
    @Test
    public void testSelectQuery() {
        // user whose age>=30
        QueryWrapper wrapper = new QueryWrapper<User>();
        wrapper.ge("age", 30);
        List<User> users = userMapper.selectList(wrapper);
        for(User user : users) {
            System.out.println(user);
        }

        // user whose name.equals("风清扬")
        QueryWrapper wrapper2 = new QueryWrapper<User>();
        wrappe2r.eq("name","风清扬");
        List<User> users2 = userMapper.selectList(wrapper2);
        for(User user : users2) {
            System.out.println(user);
        }
        
        System.out.println("-----------");
        QueryWrapper wrapper3 = new QueryWrapper<User>();
        // user whose name have character “岳”
        wrapper3.like("name","岳");
        List<User> users3 = userMapper.selectList(wrapper3);
        for(User user : users3) {
            System.out.println(user);
        }
        
        
        System.out.println("-----------");
        QueryWrapper wrapper4 = new QueryWrapper<User>();
        // users order by id asc
        wrapper4.orderByAsc("id");
        List<User> users4 = userMapper.selectList(wrapper4);
        for(User user : users4) {
            System.out.println(user);
        }
        
        System.out.println("-----------");
        QueryWrapper wrapper5 = new QueryWrapper<User>();
        // splicing sql
        wrapper5.last("limit 2");
        List<User> users5 = userMapper.selectList(wrapper5);
        for(User user : users5) {
            System.out.println(user);
        }


        System.out.println("-----------");
        QueryWrapper wrapper6 = new QueryWrapper<User>();
        // assign the content selected
        wrapper6.select("id", "name");
        List<User> users6 = userMapper.selectList(wrapper6);
        for(User user : users6) {
            System.out.println(user);
        }
    }