前面的话
- 本文基于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,并加以限制进行查询