SpringBoot集成MyBatis Plus示例

1,381 阅读3分钟

前面的话

  • 本文基于Oracle数据库,说明了如何基于SpringBoot + MyBatis Plus实现数据库的CRUD操作,包含关键特性的配置,如:分页、主键序列、timestamp类型字段自动填充等

依赖版本

<parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>2.3.4.RELEASE</version>
    <relativePath/>
</parent>

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

重要配置项

spring:
  datasource:
    url: <jdbc url>
    username: <username>
    password: <password>
    
mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl # 在控制台标准输出SQL执行日志

示例代码

测试数据库表定义

-- Person表
create table PERSON
(
    ID NUMBER not null
        constraint PERSON_PK
            primary key,
    NAME VARCHAR2(100) not null,
    CREATED_TIME TIMESTAMP(6) not null,
    UPDATED_TIME TIMESTAMP(6)
)
    /

-- Person表主键序列
CREATE SEQUENCE PERSON_ID_SEQ
    MINVALUE 1
    NOMAXVALUE
    INCREMENT BY 1
    START WITH 1 NOCACHE;

分页配置

import com.baomidou.mybatisplus.core.incrementer.IKeyGenerator;
import com.baomidou.mybatisplus.extension.incrementer.OracleKeyGenerator;
import com.baomidou.mybatisplus.extension.plugins.PaginationInterceptor;
import com.baomidou.mybatisplus.extension.plugins.pagination.optimize.JsqlParserCountOptimize;

@Configuration
public class MybatisPlusConfig {

    /**
     * 分页拦截器.
     */
    @Bean
    public PaginationInterceptor paginationInterceptor() {
        PaginationInterceptor paginationInterceptor = new PaginationInterceptor();
        paginationInterceptor.setCountSqlParser(new JsqlParserCountOptimize(true));
        return paginationInterceptor;
    }

    /**
     * OracleKeyGenerator.
     */
    @Bean
    public IKeyGenerator keyGenerator() {
        return new OracleKeyGenerator();
    }
}

timestamp类型字段自动填充处理类

import com.baomidou.mybatisplus.core.handlers.MetaObjectHandler;

@Component
public class TimestampMetaObjectHandler implements MetaObjectHandler {

    private static final String CREATED_TIME_FIELD = "createdTime";
    private static final String UPDATED_TIME_FIELD = "updatedTime";

    @Override
    public void insertFill(MetaObject metaObject) {
        this.setInsertFieldValByName(CREATED_TIME_FIELD, new Timestamp(System.currentTimeMillis()), metaObject);
    }

    @Override
    public void updateFill(MetaObject metaObject) {
        this.setUpdateFieldValByName(UPDATED_TIME_FIELD, new Timestamp(System.currentTimeMillis()), metaObject);
    }
}

Person实体类

@Data
@Accessors(chain = true)
@TableName(value = "PERSON")
// 默认主键序列字段为Long类型,需要显式修改
@KeySequence(value = "PERSON_ID_SEQ", clazz = Integer.class)
public class PersonEntity {

    @TableId(value = "ID", type = IdType.INPUT)
    private Integer id;

    @TableField(value = "NAME")
    private String name;

    // 使用fill属性定义何时执行字段填充逻辑
    @TableField(value = "CREATED_TIME", fill = FieldFill.INSERT)
    private Timestamp createdTime;

    // 使用fill属性定义何时执行字段填充逻辑
    @TableField(value = "UPDATED_TIME", fill = FieldFill.UPDATE)
    private Timestamp updatedTime;
}

PersonMapper接口

  • 开箱即用的SQL基本操作在父接口BaseMapper中定义
import com.baomidou.mybatisplus.core.mapper.BaseMapper;

@Mapper
public interface PersonMapper extends BaseMapper<PersonEntity> {

}

PersonDAO类

  • 基于PersonMapper接口提供更丰富的封装功能
  • 开箱即用的SQL扩展操作在父类ServiceImpl中定义
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;

@Service
public class PersonDAO extends ServiceImpl<PersonMapper, PersonEntity> {

    public IPage<PersonEntity> getPersonListByPage(Integer pageNumber, Integer pageSize) {
        // pageNumber从1开始计数
        IPage<PersonEntity> page = new Page<>(pageNumber, pageSize);
        LambdaQueryWrapper<PersonEntity> queryWrapper = new QueryWrapper<PersonEntity>().lambda().orderByDesc(PersonEntity::getCreatedTime);
        return baseMapper.selectPage(page, queryWrapper);
    }
}

SpringBoot启动类配置

@SpringBootApplication
@MapperScan("<Mapper接口定义所在包名>")
public class DemoApplication {
    ...
}

集成测试类

插入单条记录

@RunWith(SpringRunner.class)
@SpringBootTest(classes = DemoApplication.class)
@Slf4j
// SQL需要在事务中执行
@Transactional
// 禁用rollback以便在数据库中看到测试效果
@Rollback(value = false)
public class PersonDAOTest {

    @Autowired
    private PersonMapper personMapper;
    
    @Autowired
    private PersonDAO personDAO;

    @Test
    public void testInsert() {
        PersonEntity personEntity = new PersonEntity().setName("Alex");
        int insert = personMapper.insert(personEntity);
        log.info("insert {} record", insert); // 
    }

    ...
}

MyBatis Plus调试输出为:

Creating a new SqlSession
Registering transaction synchronization for SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@33de9d85]
JDBC Connection [io.opentracing.contrib.jdbc.TracingConnection@7e8f97dd] will be managed by Spring
==>  Preparing: SELECT PERSON_ID_SEQ.NEXTVAL FROM DUAL 
==> Parameters: 
<==    Columns: NEXTVAL
<==        Row: 2
<==      Total: 1
==>  Preparing: INSERT INTO PERSON ( ID, NAME, CREATED_TIME ) VALUES ( ?, ?, ? ) 
==> Parameters: 2(Integer), Alex(String), 2021-08-12 11:50:41.309(Timestamp)
<==    Updates: 1
Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@33de9d85]

可以看到:

  • 在执行INSERT SQL前,先执行了对主键序列字段的SELECT操作
  • CREATED_TIME数据库字段得到了有效填充

更新已有记录

@Test
public void testUpdate() {
	PersonEntity personEntity = new PersonEntity().setId(2).setName("AlexLu");
	int update = personMapper.updateById(personEntity);
	log.info("update {} record", update);
}

MyBatis Plus调试输出为:

Creating a new SqlSession
Registering transaction synchronization for SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1f591b2e]
JDBC Connection [io.opentracing.contrib.jdbc.TracingConnection@464017f5] will be managed by Spring
==>  Preparing: UPDATE PERSON SET UPDATED_TIME=?, NAME=? WHERE ID=? 
==> Parameters: 2021-08-12 11:55:24.762(Timestamp), AlexLu(String), 2(Integer)
<==    Updates: 1
Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@1f591b2e]

可以看到:

  • UPDATED_TIME数据库字段得到了有效填充

批量插入记录

@Test
public void testSaveBatch() {
    List<PersonEntity> entityList = new ArrayList<>();
    for (int i = 0; i < 2; i++) {
            entityList.add(new PersonEntity().setName("Name" + i));
    }
    boolean result = personDAO.saveBatch(entityList);
    log.info("batch insert success: {}", result);
}
  • 调研批量插入底层实现与单条插入的区别 MyBatis Plus调试输出为:
JDBC Connection [io.opentracing.contrib.jdbc.TracingConnection@2f006edf] will be managed by Spring
==>  Preparing: SELECT PERSON_ID_SEQ.NEXTVAL FROM DUAL 
==> Parameters: 
<==    Columns: NEXTVAL
<==        Row: 4
<==      Total: 1
==>  Preparing: INSERT INTO PERSON ( ID, NAME, CREATED_TIME ) VALUES ( ?, ?, ? ) 
==> Parameters: 4(Integer), Name0(String), 2021-08-12 12:02:25.194(Timestamp)
==>  Preparing: SELECT PERSON_ID_SEQ.NEXTVAL FROM DUAL 
==> Parameters: 
<==    Columns: NEXTVAL
<==        Row: 5
<==      Total: 1
==> Parameters: 5(Integer), Name1(String), 2021-08-12 12:02:25.278(Timestamp)

分页查询记录

@Test
public void testPageQuery() {
    IPage<PersonEntity> personListByPage = personDAO.getPersonListByPage(1, 2);
    log.info("total: {}", personListByPage.getTotal());
    List<PersonEntity> records = personListByPage.getRecords();
    records.forEach(r -> log.info("record: {}", r));
}

MyBatis Plus调试输出为:

Creating a new SqlSession
Registering transaction synchronization for SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2f006edf]
JDBC Connection [io.opentracing.contrib.jdbc.TracingConnection@c6db00d] will be managed by Spring
JsqlParserCountOptimize sql=SELECT  ID,UPDATED_TIME,NAME,CREATED_TIME  FROM PERSON ORDER BY CREATED_TIME DESC
==>  Preparing: SELECT COUNT(1) FROM PERSON 
==> Parameters: 
<==    Columns: COUNT(1)
<==        Row: 11
==>  Preparing: SELECT * FROM ( SELECT TMP.*, ROWNUM ROW_ID FROM ( SELECT ID,UPDATED_TIME,NAME,CREATED_TIME FROM PERSON ORDER BY CREATED_TIME DESC ) TMP WHERE ROWNUM <=?) WHERE ROW_ID > ? 
==> Parameters: 2(Long), 0(Long)
<==    Columns: ID, UPDATED_TIME, NAME, CREATED_TIME, ROW_ID
<==        Row: 13, null, Name9, 2021-08-12 12:02:25.848, 1
<==        Row: 12, null, Name8, 2021-08-12 12:02:25.777, 2
<==      Total: 2
Releasing transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@2f006edf]

可以看出:

  • Oracle的分页查询,使用到了ROWNUM关键词,实现逻辑为:确认分页记录所在的边界ROWNUM,并加以限制进行查询

参考