SpringBoot中使用Mybatis-Plus

存在问题：1.多数据库源分页2.联表查询

参考mybatis-plus官网示例，数据库使用比SQLite更为轻便的H2，支持内存模式，即服务关闭后数据清空不会持久保存，非常方便于测试

pom添加依赖

<!-- 内嵌数据库h2 -->
<dependency>
    <groupId>com.h2database</groupId>
    <artifactId>h2</artifactId>
    <scope>runtime</scope>
</dependency>

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

properties中配置H2数据库源

spring:
  datasource:
    driver-class-name: org.h2.Driver
    schema: classpath:db/schema-h2.sql
    data: classpath:db/data-h2.sql
    url: jdbc:h2:mem:test
    username: root
    password: test

准备好用的sql脚本

--schema-h2.sql=>建表
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)
);

---

--data-h2.sql=>添加测试数据
DELETE FROM user;
INSERT INTO user (id, name, age, email) VALUES
(1, 'Jone', 18, 'test1@baomidou.com'),
(2, 'Jack', 20, 'test2@baomidou.com'),
(3, 'Tom', 28, 'test3@baomidou.com'),
(4, 'Sandy', 21, 'test4@baomidou.com'),
(5, 'Billie', 24, 'test5@baomidou.com');

具体类

• user表对应的pojo

  用到了lombok对不起

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

• dao

  BaseMapper是mybatis-plus的api

  @Repository
  @Mapper
  public interface MybatisPDao extends BaseMapper<User> {}
  

• application注册dao

  配置MapperScan

  @SpringBootApplication
  @MapperScan("sb.mybatisp")
  public class MybatispApplication {
      public static void main(String[] args) {
          SpringApplication.run(MybatispApplication.class, args);
      }
  }
  

• 测试类

  通过Wrapper对象配置sql操作条件，showWrapper()测试方法中举了一些条件示例，详细的可以点进源码查看具体的sql织入逻辑

  @SpringBootTest
  class MybatisPTests {
  
      @Autowired
      MybatisPDao mybatisPDao;
  
      @Test
      public void testSelect() {
          List<User> users = mybatisPDao.selectList(null);
          users.forEach(System.out::println);
      }
  
      @Test
      public void testInsert() {
          User user = new User();
          user.setName("akali");
          user.setAge(20);
          user.setEmail("akali@winning.com.cn");
          mybatisPDao.insert(user);
          testSelect();
      }
  
      @Test
      public void testUpdate() {
          User user = new User();
          user.setName("ashe");
          mybatisPDao.update(user, new UpdateWrapper<User>().eq("name", "Jack"));
          testSelect();
      }
  
      @Test
      public void testDelete() {
          mybatisPDao.delete(new QueryWrapper<User>().eq("name", "Tom"));
          testSelect();
      }
  
      /**仅供参考查询调写法不可运行*/
      @Test
      public void showWrapper() {
          QueryWrapper<Object> wrapper = new QueryWrapper<>();
          wrapper.like("name", "abc");
          wrapper.lt("age", 20);
          wrapper.between("age", 10, 30);
          wrapper.isNotNull("address");
          wrapper.or().eq("address", "China");
          wrapper.orderByAsc("id");
          wrapper.groupBy("name");
          wrapper.and(w -> w.isNotNull("email")).and(w -> w.gt("score", 60));
      }
  }
  

自己写sql不用纯api

类似安卓中的Room数据库操作，自己写sql

• dao中写sql

  @Repository
  @Mapper
  public interface MybatisPDao extends BaseMapper<User> {
      
      //就这么来，跟Room很像
      @Select("select name,age,email mail from user where age>#{age}")
      List<User> users(int age);
  }
  

• 测试

  @Test
  public void users() {
      List<User> users = mybatisPDao.users(20);
      users.forEach(System.out::println);
  }
  

分页

• 配置

  @Configuration
  public class MyBatisPConfig {
  
      @Bean
      public MybatisPlusInterceptor mybatisPlusInterceptor() {
          MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();
          //具体要操作的数据库类型，此处有个问题，多数据库源分页配置待寻找方法解决
          interceptor.addInnerInterceptor(new PaginationInnerInterceptor(DbType.H2));
          return interceptor;
      }
  }
  

• 测试

  @Test
  public void testPage() {
      Page<User> userPage = mybatisPDao.selectPage(new Page<>(1, 2), null);
      userPage.getRecords().forEach(System.out::println);
  }